精通Oracle10编程SQL(14)开发动态SQL

/*
 *开发动态SQL
 */
--使用EXECUTE IMMEDIATE处理DDL操作
CREATE OR REPLACE PROCEDURE drop_table(table_name varchar2)
is
   sql_statement varchar2(100);
begin
   sql_statement:='DROP TABLE '||table_name;
   execute immediate sql_statement;
end;

--当执行以上命令建立了过程drop_table之后，就可以调用该过程动态的删除表了
create table empbak as select * from emp;

select * from empbak;

begin
     drop_table('empbak');
end;

--使用EXECUTE IMMEDIATE处理DCL操作
CREATE OR REPLACE PROCEDURE grant_sys_priv
(priv varchar2,username varchar2)
is 
   sql_stat varchar2(100);
begin
   sql_stat:='grant '||priv||' to '||username;
   execute IMMEDIATE sql_stat;
end;

--当建立了过程grant_sys_priv之后，就可以调用该过程为其他用户或角色授予系统权限了
begin
     grant_sys_priv('CREATE SESSION','SCOTT');
end;

--使用EXECUTE IMMEDIATE处理DML操作
--处理无占位符和RETURNING子句的DML语句
--当使用EXECUTE IMMEDIATE处理无占位符和RETURNING子句的DML语句时，不需要带有USING和INTO子句
--下面以给部门3的所有雇员增加10%的工资为例，说明这种处理方法
DECLARE
   sql_stat varchar2(100);
begin
   sql_stat:='UPDATE emp set sal=sal*1.1 where deptno=3';
   execute IMMEDIATE sql_stat;
end;

--禁止EMP表的所有触发器
alter table emp disable all triggers;

select * from emp;


--处理包含占位符的DML语句
--当使用EXECUTE IMMEDIATE处理包含占位符的DML语句时，需要使用USING子句为占位符提供输入数据。
--下面以给不同部门增加工资为例，说明这种处理方法
--其中USING后的第一个值是为第一个占位符提供的，第二个值是为第二个占位符提供的
--注意，占位符可以使用任意的名称
DECLARE
   sql_stat varchar2(100);
begin
   sql_stat:='update emp set sal=sal*(1+:percent/100)'||' where deptno=:dno';
   execute immediate sql_stat using &1,&2;
end;

--处理包含RETURNING子句的DML语句
--注意：当直接使用EXECUTE IMMEDIATE处理带有RETURING子句的DML语句时，只能处理作用在单行上的DML语句。
--如果DML语句作用在多行上，则必须要使用BULK子句
--下面以给特定雇员增加工资，并输出新工资为例，说明处理带有RETURNING子句的动态DML语句的方法
DECLARE
   salary NUMBER(6,2);
   sql_stat varchar2(100);
begin
   sql_stat:='UPDATE EMP set sal=sal*(1+:percent/100)'||' where empno=:eno returning sal into :salary';
   execute immediate sql_stat using &1,&2 returning into salary;
   dbms_output.put_line('新工资：'||salary);
end;

--使用EXECUTE IMMEDIATE处理单行查询
--在使用EXECUTE IMMEDIATE处理单行查询语句时，需要使用INTO子句接收返回数据。
--下面以输出特定雇员的雇员姓名和工资为例，说明使用EXECUTE IMMEDIATE处理单行查询语句的方法
DECLARE
   sql_stat varchar2(100);
   emp_record emp%ROWTYPE;
BEGIN
   sql_stat:='select * from emp where empno=:eno';
   execute immediate sql_stat into emp_record using &1;
   dbms_output.put_line('雇员'||emp_record.ename||'的工资为'||emp_record.sal);
END;

--处理多行查询语句
--为了动态地处理SELECT语句所返回的多行数据，需要使用OPEN-FOR，FETCH和CLOSE语句
--下面以动态输入部门号，显示特定部门的所有雇员姓名和工资为例，说明使用动态SQL处理多行查询的方法
DECLARE
   TYPE empcurtyp is ref cursor;
   emp_cv empcurtyp;
   emp_record emp%ROWTYPE;
   sql_stat varchar2(100);
begin
   sql_stat:='SELECT * FROM emp where deptno=:dno';
   open emp_cv for sql_stat using &dno;
   loop
      fetch emp_cv into emp_record;
      exit when emp_cv%NOTFOUND;
      dbms_output.put_line('雇员名：'||emp_record.ename||'，工资：'||emp_record.sal);
   end loop;
   close emp_cv;
end;

--在动态SQL中使用BULK子句
--使用BULK子句处理DML语句返回子句
--当处理作用在多行上的动态DML返回子句时，必须在EXECUTE IMMEDIATE语句后带有BULK子句。
--下面以动态修改特定部门的工资，并返回修改后的雇员名及其工资为例，说明使用BULK子句处理动态DML语句的方法
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*(1+:percent/100)'
      ||' where deptno=:dno'
      ||' returning ename,sal into :name,:salary';
   execute immediate sql_stat using &percent,&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;

--使用BULK子句处理多行查询
--下面以动态显示特定部门的所有雇员名为例，说明在EXECUTE IMMEDIATE语句中使用BULK子句处理多行查询的方法
DECLARE
   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 deptno=:dno';
   execute immediate sql_stat bulk collect into ename_table using &dno;
   for i in 1..ename_table.count loop
      dbms_output.put_line(ename_table(i));
   end loop;
end;

--在FETCH语句中使用BULK子句
--当使用OPEN-FOR，FETCH，CLOSE语句处理动态的多行查询语句时，因为在默认情况下FETCH语句每次只能提取单行数据，所以为了处理所有数据，
--需要使用循环语句
--通过在FETCH语句中引入BULK子句，一次就可以提取所有数据
--下面以动态返回特定岗位的雇员名为例，说明在FETCH语句中使用BULK子句的方法
DECLARE
   TYPE empcurtyp is ref cursor;
   emp_cv empcurtyp;
   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_cv for sql_stat using '&job';
   fetch emp_cv bulk collect into ename_table;
   for i in 1..ename_table.count loop
      dbms_output.put_line(ename_table(i));
   end loop;
   close emp_cv;
end;

select * from emp;

--在FORALL语句中使用BULK子句
--使用FORALL语句，可允许在动态SQL语句中为输入变量同时提供多个数据，但FORALL语句只适用于动态的INSERT，UPDATE和DELETE语句
--而不适用于动态的SELECT语句，并且FORALL语句和EXECUTE IMMEDIATE语句是结合使用的
--下面以修改多个雇员工资，并返回雇员新工资为例，说明结合使用FORALL语句和EXECUTE IMMEDIATE语句的方法
--当执行了以下PL/SQL块之后，会为雇员Test,SCOTT和EMPP增加10%的工资，并输出它们的新工资
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
   --注意：'SCOTT','EMPP','Test'必须对应emp表中的有且仅有一条记录
   ename_table:=ename_table_type('Test','SCOTT','EMPP');
   sql_stat:='update emp set sal=sal*1.1 where ename=:1'
       ||' returning sal into :2';
   forall i in 1..ename_table.count
      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;