存储过程中动态执行Oracle的Sql

原文：http://my.oschina.net/u/1458120/blog/225922

1 动态执行Sql-Delete

--Create 
create or replace procedure pro_del(v_name VARCHAR2)
as
v_sql varchar(200);
begin
v_sql := 'delete from tb_user where name = ''' || v_name || '''';
execute immediate v_sql;
dbms_output.put_line('delete successfully!');
EXCEPTION
when others then
dbms_output.put_line('在pro_del过程中出错！');
dbms_output.put_line(SQLCODE || '::'||SQLERRM);  
end;

--Execute
set serveroutput on;
declare
v_n varchar2(20):= 'tang';
begin 
   pro_del(v_n);
dbms_output.put_line(v_n);
end;

--或者 
exec pro_del('tang')
 

2 动态执行Sql-Select

--Create创建
create or replace procedure pro_info(v_deptno number)
is
type emp_cursor_type is ref cursor;
emp_cursor emp_cursor_type;
emp_record emp%rowtype;

v_sql varchar2(100);

begin
v_sql:='select * from emp where deptno=:v_deptno';
dbms_output.put_line(v_sql||'---'||v_sql);
open emp_cursor for v_sql using v_deptno;

loop
fetch emp_cursor into emp_record;
exit when emp_cursor%notfound;
dbms_output.put_line('ename: '|| emp_record.ename ||',salary: ' ||emp_record.sal);

end loop;
close emp_cursor;
end;

--或者
create or replace procedure pro_info2(v_deptno number)
is
type emp_cursor_type is ref cursor;
emp_cursor emp_cursor_type;
emp_row emp%rowtype;
v_sql varchar2(200);
begin
  v_sql := 'select * from emp where deptno = ''' || v_deptno || '''';
  open emp_cursor for v_sql;
  loop
    FETCH emp_cursor into emp_row;
    exit when emp_cursor%notfound;
    dbms_output.put_line('ename:'|| emp_row.ename||'---'||emp_row.sal);
  end loop; 
  close emp_cursor;
end;

--Execute执行
set serveroutput on;
exec pro_info(20);
exec pro_info2(20);

3 动态执行Sql-Select2

--创建
create or replace procedure pro_info3(v_deptno number,emp_cursor out SYS_REFCURSOR)
is
emp_row emp%rowtype;
v_sql varchar2(200);
begin
  v_sql := 'select * from emp where deptno = ''' || v_deptno || '''';
  open emp_cursor for v_sql;
--不能在此进行loop emp_cursor,否则exec pro_info3时就取不到数据了
--也不能close emp_cursor
--  loop
--    FETCH emp_cursor into emp_row;
--    exit when emp_cursor%notfound;
--    dbms_output.put_line('ename:'|| emp_row.ename||'---'||emp_row.sal);
--  end loop; 
  --close emp_cursor;
end;

--执行
set serveroutput on;
declare
v_cursor sys_refcursor;
v_row emp%rowtype;
begin
    pro_info3(20,v_cursor);
    dbms_output.put_line('BEGIN!');
    LOOP
    fetch v_cursor into v_row;
    EXIT WHEN v_cursor%NOTFOUND;
    dbms_output.put_line(v_row.sal);
    END LOOP;
    dbms_output.put_line('DONE!');
end;