/*
*开发动态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;