使用execute immediate处理DDL操作：
create or replace procedure drop_table(table_name in varchar2)
is
??sql_statement varchar2(100);
begin
??sql_statement := 'drop table '|| table_name;
??execute immediate sql_statement;
end;
/
调用：SQL> exec drop_table('demo'); 删除表
------------------------------------------------------------
DML操作：
使用无符号占位符和returing语句：
begin
??execute immediate 'update emp set sal = sal + 1000 where deptno = 30';
end;
/
有符号占位符：
begin
??execute immediate 'update emp set sal = (sal+:addsal) where deptno = :dno' using &1,&2; ?
end;
/
有符号占位符，有returing子句：
declare
salary number(6,2);
begin
??execute immediate 'update emp set sal = (sal+:addsal) where '||
?? 'empno = :dno returning sal into :salary' using &1,&2 returning into salary;
??dbms_output.put_line('修改后的工资：' || salary); ?
end;
/
处理单行查询：
declare
salary number(6,2);
begin
??execute immediate 'select sal from emp where empno = :eno' into salary using &1;
??dbms_output.put_line('雇员的工资：' || salary); ?
end;
/
------------------------------------------------------------
处理多行查询
declare
??type emp_cursor_typ is ref cursor;--定义游标变量类型
??emp_cursor emp_cursor_typ;--声明游标变量
??emp_record emp%rowtype;--记录
??sql_stat varchar2(100);
begin
??sql_stat := 'select * from emp where deptno=:dno';
??open emp_cursor for sql_stat using &dno;--打开游标
??loop
?? ?fetch emp_cursor into emp_record;--提取数据
?? ?exit when emp_cursor%notfound;
?? ?dbms_output.put_line('雇员名:'||emp_record.ename);
??end loop;
end;
/
批量提取：
declare
??type ename_table_type is table of emp.ename%type index by binary_integer;
??type sal_table_type is table of emp.sal%type index by binary_integer;
??ename_table ename_table_type;
??sal_table sal_table_type;
??sql_stat varchar2(100);
begin
??sql_stat := 'update emp set sal = sal + 500 where deptno=:dno' ||?
??' returning ename,sal into :name,:salary';
??execute immediate sql_stat using &dno returning bulk collect into?
??ename_table, sal_table;
??for i in 1..ename_table.count loop
?? ?dbms_output.put_line('雇员'||ename_table(i)||'的新工资为：'||sal_table(i));
??end loop;
end;
/
使用fetch的批量：
declare
??type emp_cursor_type is ref cursor;
??emp_cursor emp_cursor_type;
??type ename_table_type is table of emp.ename%type index by binary_integer;
??ename_table ename_table_type;
??sql_stat varchar2(100);
begin
??sql_stat := 'select ename from emp where job=:title';
??open emp_cursor for sql_stat using '&job';
??fetch emp_cursor bulk collect into ename_table;--批量提取
??for i in 1..ename_table.count loop
?? ?dbms_output.put_line(ename_table(i));
??end loop;
end;
/
forall中使用bulk子句：
declare
??type ename_table_type is table of emp.ename%type;
??type sal_table_type is table of emp.sal%type;
??ename_table ename_table_type;
??sal_table sal_table_type;
??sql_stat varchar2(100);
begin
??ename_table := ename_table_type('SCOTT','SMITH','CLARK');
??sql_stat := 'update emp set sal = sal + 500 where ename =:1 returning sal into :2';
??forall i in 1..ename_table.count ?--forall
?? ?execute immediate sql_stat using ename_table(i) returning bulk collect into sal_table;
?? ?for j in 1..ename_table.count loop
?? ? ?dbms_output.put_line('雇员:'||ename_table(j) ||'的新工资为：'||sal_table(j));
?? ?end loop;
end;
/
?