create or replace procedure proc_test(v_item in varchar2)
as
v_sql varchar2(2000):=''; --执行的sql
v_num number(9); --员工人数
v_dname varchar2(200); --部门名称
begin
--execute immediate用法1:立即执行sql
v_sql:='select * from emp';
execute immediate v_sql;
dbms_output.put_line('execute immediate用法1执行成功!');
--execute immediate用法2:立刻执行sql语句,并赋值给某个变量
v_sql:='select count(*) from emp';
execute immediate v_sql into v_num;
dbms_output.put_line('execute immediate用法2执行成功!员工总数:'||v_num);
--execute immediate用法3:立刻执行sql语句,并赋值给某个变量
v_sql:='select dname from dept where deptno = :1 ';
execute immediate v_sql into v_dname using v_item ;
dbms_output.put_line('execute immediate用法3执行成功!'||v_item||'部门名称为:'||v_dname);
--异常处理
exception
when OTHERS then
begin
dbms_output.put_line('sqlcode:'||sqlcode);
dbms_output.put_line('sqlerror:'||SUBSTR(SQLERRM, 1, 200));
dbms_output.put_line('error in line:'||DBMS_UTILITY.format_error_backtrace);
end;
end proc_test;
在scott用户中创建上面存储过程:
执行存储过程:
begin
proc_test(’20’);
end;
/
结果:
execute immediate用法1执行成功!
execute immediate用法2执行成功!员工总数:14
execute immediate用法3执行成功!20部门名称为:RESEARCH
版权声明:本文为S630730701原创文章,遵循 CC 4.0 BY-SA 版权协议,转载请附上原文出处链接和本声明。