在动态plsq中，对于in out 类型的变量，在绑定时需要指明in out模式：

SQL> create or replace procedure create_dept(

2 deptid in out number,

3 dname in varchar2,

4 loc in varchar2)

5 as

6 deptno number :=1;

7 begin

8 select departments_seq.nextval into deptid from dual;

9 insert into dept values (deptid,dname,loc);

10 end;

11 /

过程已创建。

SQL> select * from dept;

DEPTNO DNAME LOC

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

10 ACCOUNTING NEW YORK

20 RESEARCH DALLAS

30 SALES CHICAGO

40 OPERATIONS BOSTON

SQL> declare

2 plsql_block varchar2(500);

3 new_deptid number(4) :=100;

4 new_dname varchar2(30) :='Advertising';

5 new_loc varchar2(30) :='ShangDong';

6 begin

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

8 DBMS_OUTPUT.PUT_LINE('Before execute: '||new_deptid);

9 EXECUTE IMMEDIATE plsql_block using in out new_deptid,new_dname,new_loc;

10 DBMS_OUTPUT.PUT_LINE('After execute: '||new_deptid);

11 end;

12 /

Before execute: 100

After execute: 1

PL/SQL 过程已成功完成。