在动态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 过程已成功完成。
SQL> select * from dept;
DEPTNO DNAME LOC
---------- -------------- -------------
10 ACCOUNTING NEW YORK
20 RESEARCH DALLAS
30 SALES CHICAGO
40 OPERATIONS BOSTON
1 Advertising ShangDong
来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/10972173/viewspace-708873/,如需转载,请注明出处,否则将追究法律责任。
转载于:http://blog.itpub.net/10972173/viewspace-708873/