oracle如何动态调用存储过程,zhouweifeng | ORACLE动态调用存储过程

ORACLE动态调用存储过程

SQL> create or replace procedure p_test(a in varchar2,b in varchar2,c out varchar2)

2 is

3 begin

4 c := a||b;

5 end p_test;

6 /

Procedure created

SQL> declare

2 v_sql varchar2(1000);

3 c varchar2(1000);

4 begin

5 v_sql:='begin p_test(:v1,:v2,:v3); end;';

6 execute immediate v_sql using in '1',in '2',out c;

7 dbms_output.put_line(c);

8 end;

9 /

12

PL/SQL procedure successfully completed

/************************************************/

Specifying Parameter Modes

With the USING clause, you need not specify a parameter mode for input bind arguments because the mode defaults to IN. With the RETURNING INTO clause, you cannot specify a parameter mode for output bind arguments because, by definition, the mode is OUT. An example follows:

DECLARE

sql_stmt VARCHAR2(200);

dept_id NUMBER(2) := 30;

old_loc VARCHAR2(13);

BEGIN

sql_stmt :=

'DELETE FROM dept WHERE deptno = :1 RETURNING loc INTO :2';

EXECUTE IMMEDIATE sql_stmt USING dept_id RETURNING INTO old_loc;

...

END;

When appropriate, you must specify the OUT or IN OUT mode for bind arguments passed as parameters. For example, suppose you want to call the following standalone procedure:

CREATE PROCEDURE create_dept (

deptno IN OUT NUMBER,

dname IN VARCHAR2,

loc IN VARCHAR2) AS

BEGIN

SELECT deptno_seq.NEXTVAL INTO deptno FROM dual;

INSERT INTO dept VALUES (deptno, dname, loc);

END;

To call the procedure from a dynamic PL/SQL block, you must specify the IN OUT mode for the bind argument associated with formal parameter deptno, as follows:

DECLARE

plsql_block VARCHAR2(500);

new_deptno NUMBER(2);

new_dname VARCHAR2(14) := 'ADVERTISING';

new_loc VARCHAR2(13) := 'NEW YORK';

BEGIN

plsql_block := 'BEGIN create_dept(:a, :b, :c); END;';

EXECUTE IMMEDIATE plsql_block

USING IN OUT new_deptno, new_dname, new_loc;

IF new_deptno > 90 THEN ...

END;