Oracle 动态SQL
一、动态SQL的简介
1、定义
静态SQL是指直接嵌入到PL/SQL块中的SQL语句。
动态SQL是指运行PL/SQL块是动态输入的SQL语句。
2、适用范围
如果在PL/SQL块中需要执行DDL语句(create,alter,drop等)、DCL语句(grant,revoke等)或更加灵活的SQL语句，需要用到动态SQL。
3、静态与动态SQL的比较
1）、静态SQL是在编写PL/SQL块是直接嵌入的SQL语句，而动态SQL是在运行PL/SQL块时动态数据的SQL语句。
2）、静态SQL性能优于动态SQL。
4、动态SQL的处理方法
1）、适用execute immediate
execute immediate可以处理多数动态SQL操作，包括DDL语句、DCL语句、DML语句、及单行select语句(不能处理多行查询)。
2）、适用open-for,fetch和colse语句
为了处理动态的多行查询操作，必须要使用open-for打开游标，使用fetch循环提取数据，最终使用close关闭游标。
3）、使用批量动态SQL
9i新增，通过使用批量动态SQL，可以加快SQL语句处理，进而提高PL/SQL程序的性能。
二、处理非查询语句及单行查询语句
1、定义
处理非查询语句(DDL\DCL\DML等)及单行查询语句使用execute immediate
语法：
execute immediate dynamic_string
[into [define_name1 [,define_name2]...]
[using [in|out|in out] bind_name1 [,[in|out|in out] bingd_nane2]...]
[[returning|return] into bind_name1[,bind_name2]...];
其中dynamic_string用于指定存放SQL语句或PL/SQL块的字符串变量；
define用于指定存放单位查询结果的变量；
bind_name(in)用于指定存放被传递给动态SQL值得变量；
bind_name(out)用于指定存放动态SQL返回值得变量；
2、处理DDL操作
create or replace procedure drop_table(table varchar2) is
v_sql varchar2(100);
begin
v_sql:='drop table '|| table_name;
execute immediate v_sql;
end;
3、处理DCL操作
create or replace procudure grant_sys_priv(priv varchar2,username varchar2) is
v_sql varchar2(100);
begin
v_sql:='grant '||priv||' to '||username;
execute immediate v_sql;
end;
4、处理DML操作
1）、处理无占位符合和returning子句的DML语句
declate
v_sql varchar2(100);
begin
v_sql:='update emp set sql=sql*1.1 where deptno=30';
execute immediate v_sql;
end;
2）、处理包含占位符的DML语句
declate
v_sql varchar2(100);
begin
v_sql:='update emp set sql=sql*(1+:percent/100) where deptno=:dno';
execute immediate v_sql using &1,&2;
end;
3）、处理包含returning子句的DML语句
说明：使用execute immediate处理带有returning子句的DML语句时，只能处理作用在单行上的DML语句；
如果DML语句作用在多行上，则必须使用bulk子句，此中情况下之后讲述。
declare
salary number(6,2);
v_sal varchar2(100);
begin
v_sql:='update emp set sal=sal*(1+:percent/100) where empno=:eno returning sal into :salary';
execute immediate v_sql using &1,&2 returning into salary;
dbms_output.put_line('新工资:'||salary);
end;
5、处理单行查询
说明：execute immediate不能处理多行查询。
declare
v_sql varchar2(100);
wmp_record emp%rowtype;
begin
v_sql:='select * from emp where empno=:eno';
execute immediate v_sql into wmp_record using &1;
dbms_output.put_line('雇员'||emp_record.ename||'的工资'||emp_record.sal);
end;
三、处理多行查询语句
说明：需要通过游标来完成
declare
type empcurtyp is ref cursor;
emp_cv empcurtyp;
emp_record emp%rowtype;
v_sal varchar2(100);
begin
v_sql:='select * from emp where deptno=:dno';
open emp_cv for v_sql 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;
wnd;
四、在动态SQL中使用bulk子句
1、概述
在动态SQL中使用BULK子句为9i新增特征，BULK子句实际是动态SQL语句将变量绑定为集合元素，
集合类型可以是PL/SQL所支持的索引表、嵌套表和VARRAY。但集合元素必须使用SQL数据类型
(例如number、char等)，而不能使用PL/SQL数据类型(例binary_integer、boolean等)。
9i开始有三种语句支持BULK子句，execute immediate、fetch和forall，下面逐一讲述。
2、在execute immediate语句中使用bulk子句
1）、概述
通过在execute immediate语句中使用bulk子句可以处理作用在多行上的动态DML返回子句，和多行查询语句。
2）、语法
execute immediate dynamic_name
[bulk collect into define_name[,define_name...]]
[using bind_name[,bind_name...]]
[{returning|return} bulk collect into return_name[,return_name...]];
其中dynamic_name用于指定存放动态SQL语句的字符串变量；
define_name用于指定存放查询结果的集合变量；
bind_name指定绑定变量(存放传递给动态SQL的数据)；
return_name指定接收returning子句返回结果的集合变量；
3）、处理作用在多行上的动态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;
v_sql varchar2(100);
begin
v_sql:='update emp set sal=sal*(1+:percent/100) where deptno=:dno returning ename,sal into :name,:salary';
execute immediate v_sql 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;
4）、处理多行查询语句
declare
type ename_table_type is table of emp.ename%type index by binary_integer;
ename_table ename_table_type;
v_sal varchar2(100);
begin
v_sal:='select ename from emp where deptno=:dno';
execute immediate v_sql 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;
3、在fetch语句中使用bulk子句。
1）、概述
在原来的open-for,fetch,close语句处理动态多行查询语句时，默认每次提取单行数据，循环提取。
通过在fetch语句中引入bulk子句，可以一次提取所有数据。
2）、语法
fetch dynamic_cursor
bulk collect into define_name[,define_name...];
3）、处理多行查询语句
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;
v_sql varchar2(100);
begin
v_sql:='select ename from emp where job=:title';
open emp_cv for v_sql 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;
4、在forall语句中使用bulk子句。
1）、概述
使用forall语句，可允许在动态SQL语句中为输入变量同时提供多个数据，
但forall语句只适用于动态的insert\update\delete语句，
不适用于动态select语句，并且forall语句是和execute immediate结合使用的。
2）、语法
forall index in lower bound...upper bound
execute immediate dynamic_name
using bind_name | bind_name(index) [,bind_name | bind_name(index)]...
[{returning | return} bulk collect into bind_name[,bind_name...]];
3）、处理作用在多行上的动态DML语句返回子句
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;
v_sal varchar2(100);
begin
ename_table:=ename_table_type('scott','smith','clark');
v_sql:='update emp set sal=sal*1.1 where ename=:1 returning sal into :2';
forall i in 1..ename_table.count
execute immediate v_sql using ename_table(i) returning bulk collect into v_sal;
for j in 1..ename_table.count loop
dbms_output.put_line('雇员'||ename_table(i)||'新工资为'||sal_table(j));
end loop;
end;
--