procedure

SQL> CREATE OR REPLACE PROCEDURE "ADMIN"."P_TESTSP"

  2  (

  3         i         number ,

  4         j         out number

  5  )

  6  is

  7         k         number(5):=100;

  8  begin

  9      j := i + k ;

 10      dbms_output.put_line(j);

 11  end P_testSP ;

 12  /

 

Procedure created

Call Procedure:

--定义输出变量

var x number;

或

declare x number;

--调用存储过程

exec p_testsp(99,:x);

或

execute p_testsp(99,:x);

或

call p_testsp(99,:x);

或

begin 

p_testsp(99,:x); 

end;

/

-- e.g.

SQL> declare c number;

  2  begin

  3  p_testsp(99,:c);

  4  end;

  5  /

199

PL/SQL procedure successfully completed.

SQL> var c number;

SQL> exec p_testsp(99,:c);

199

PL/SQL procedure successfully completed.

SQL> print c;

       199

Show Errors: 

显示创建对象过程中产生的错误信息。

SQL> show errors;

No errors.

SQL> create or replace procedure p_testsp01

  2  is

  3  begin

  4  dbms_output.put_line(1000);

  5  x;

  6  end p_testsp01;

  7  /

Warning: Procedure created with compilation errors.

SQL> show errors;

Errors for PROCEDURE P_TESTSP01:

LINE/COL ERROR

-------- -----------------------------------------------------------------

5/1      PL/SQL: Statement ignored

5/1      PLS-00306: wrong number or types of arguments in call to 'X'

DBMS_METADATA_GET_DDL:

使用dbms_metadata.get_ddl获取定义对象的sql语句。 

SQL>select dbms_metadata.get_ddl('PROCEDURE','P_TESTSP01') from dual;

 DBMS_METADATA.GET_DDL('PROCEDU

-----------------------------------------------------------------------------

---

 CREATE OR REPLACE PROCEDURE "ADMIN"."P_TESTSP01"

is

begin

dbms_output.put_line(1000);

end;

-- 如果该过程显示不全，设置long长度

SQL>set long 10000 

User_Source:

SQL> select TEXT from user_source a where a.name='P_TESTSP01';

TEXT

-----------------------------------------------------------------------------

---

procedure p_testsp01

is

begin

dbms_output.put_line(1000);

x;

end p_testsp01;

----------  The End  ----------