Oracle的动态SQL和动态游标举例

--动态sql和动态游标
declare
cnt number;
begin
execute immediate
'select count(*) from emp'
into cnt;
dbms_output.put_line(cnt);
end;

--创建存储过程执行插入语句
create or replace procedure myproc11
(empid in varchar2,empname in varchar2)
is
sql_str varchar(200):='insert into emp values(:a,:b)';
begin
execute immediate sql_str using empid,empname;
commit;
end;


--本过程有两个参数，第一个表示查询类型：0-精确查询 1-模糊查询；
--第二个参数表示查询条件
create or replace procedure myproc12
(query_type in number,empname in varchar2)
is
sql_str varchar(200):='select * from emp ';
cur pck1.myrefcur;
e emp%rowtype;
begin

if query_type=0 then
sql_str:=sql_str||'where emp_name=:1';
else
sql_str:=sql_str||'where emp_name like ''%''||:1||''%''';
end if;
dbms_output.put_line(sql_str);
open cur for sql_str using empname;

loop
fetch cur into e;
exit when cur%notfound;
dbms_output.put_line(e.emp_id||','||e.emp_name);
end loop;
close cur;
end;

--按姓名模糊查询记录总数
create or replace procedure myproc20
(ename in varchar2)
is
cnt number;
begin
execute immediate
'select count(*) from emp where emp_name like ''%''||:n||''%'''
into cnt using ename;

dbms_output.put_line(cnt);
end;