动态SQL
在之前用户所编写的PL/SQL程序时有一个最大的特点：就是所操作的数据库对象（例如：表）必须存在，否则创建的子程序就会出问题，而这样的操作在开发之中被称为静态SQL操作，而动态SQL操作可以让用户在定义程序时不指定具体的操作对象，而在执行时动态的传入所需要的数据库对象，从而使程序变得更加的灵活。
创建一个功能，输入表名，输出表中有多少行，若没有这个表，则创建这个表。
首先禁用所有触发器
GRANT CREATE ANY TABLE TO SCOTT
create or replace function get_table_count_fun(p_table_name varchar2) return number as
v_sql_statement varchar2(200);--定义操作的SQL语句
v_count number;--保存表中记录
begin
select count(*) into v_count from user_tables where table_name=UPPER(p_table_name);
if v_count=0 then--数据表不存在
v_sql_statement:='CREATE TABLE '|| p_table_name ||'(id number ,name varchar2(30) not null)';--创建数据表，注意此处不要忽略空格，按照正常写create table语句的格式加空格，否则无法识别
execute immediate v_sql_statement;--执行动态SQL
end if;
v_sql_statement:=' select count(*) from '|| p_table_name;--查询数据表记录，注意加空格
execute immediate v_sql_statement into v_count;--执行动态SQL并保存数据记录
return v_count;
end;
/
declare
v1 varchar2(100);
begin
v1:=get_table_count_fun('t100');--查看t100表
dbms_output.put_line(v1);
end;
/
EXECUTE IMMEDIATE语句
在动态SQL之中EXECUTE IMMEDIATE是最为重要的执行命令，使用此语句可以方便的在PL/SQL程序之中执行DML(INSERT/UPDATE/DELETE/单列SELECT)、DDL(CREATE/ALTER/DROP)、DCL(GRANT/REVOKE)语句，EXECUTE IMMEDIATE语法定义如下：
EXECUTE IMMEDIATE 动态SQL字符串 [[BULK COLLECT]] INTO 自定义变量，...|记录类型|
[USING [IN | OUT |IN OUT|绑定参数,...]
[[RETURNING | RETURN][BULK COLLECT] INTO 绑定参数,...];
EXECUTE IMMEDIATE由以下三个主要字句组成：
INTO:保存动态SQL执行结果，如果返回多行记录可以通过BULK COLLECT设置批量保存;
USING:用来为动态SQL设置占位符设置内容；
RETURNING | RETURN:两者使用效果一样，是取得更新表记录被影响的数据，通过BULK COLLECT来设置批量绑定。
使用动态SQL创建表和PLSQL块
declare
v_sql_statement varchar2(200);
v_count number;--保存查找结果
begin
select count(*) into v_count from user_tables where table_name='VDATA_TAB';
if v_count=0 then--数据表不存在
v_sql_statement:='CREATE TABLE vdata_tab(
id number primary key,
url varchar2(50) not null)';--定义动态SQL
execute immediate v_sql_statement;
Else--数据表存在
v_sql_statement:='TRUNCATE TABLE vdata_tab';
execute immediate v_sql_statement;
end if;
v_sql_statement:='begin
for x in 1 .. 10 loop
insert into vdata_tab(id,url) values(x,''www.vdata.com.cn''||x);
end loop;
end;';
execute immediate v_sql_statement;
commit;--提交事物
end;
/
使用绑定变量
Declare
V_sql_statement varchar2(200);
V_deptno dept.deptno%type:=60;
V_dname dept.dname%type:=’VDATA’;
V_loc dept.loc%type:=’beijing’;
Begin
V_sql_statement:=’insert into dept(deptno,dname,loc) values(:dno,:dna,:dl)’;
Execute immediate v_sql_statement using v_deptno,v_dname,v_loc;
Commit;
End;
/
如果有字段为NULL,则不能直接绑定NULL,需要通过变量设置
如果在本程序中索要增加的部门位置为NULL，则以下的设置方式是错误的：EXECUTE IMMEDIATE v_sql_statement using v_deptno,v_dname,NULL;如果现在希望LOC的内容为NULL，可以将v_loc变量设置为NULL，其他执行部分不做改变。
查询数据
declare
v_sql_statement1 varchar2(2000);
v_empno emp.empno%type:=7369;
v_emprow emp%rowtype;
begin
v_sql_statement1:='select * from emp where empno=:eno';
execute immediate v_sql_statement1 into v_emprow using v_empno;
dbms_output.put_line('ename: '||v_emprow.empno||', ename: '||v_emprow.ename||', position: '||v_emprow.job);
end;
/
通过以上的操作可以发现，所有使用绑定变量的代码都只是针对基本的数据类型，例如字符串、数字等，但是这种方式不可能针对DDL操作，例如，将要创建或阶段的表名称使用绑定变量的话就是出现错误。
创建表时使用绑定变量
DECLARE
v_sql_statement VARCHAR2(200) ;
v_table_name VARCHAR2(200) := 'mldn' ;
v_id_column VARCHAR2(200) := 'id' ;
BEGIN
v_sql_statement := 'CREATE TABLE ' || v_table_name ||' (' || v_id_column ||' NUMBER PRIMARY KEY)' ;
EXECUTE IMMEDIATE v_sql_statement ;
END ;
更新数据，取得更新后的结果
Declare
V_sql_statement varchar2(200);--定义SQL操作语句
V_empno emp.empno%type:=7369;--要更新的雇员编号
V_salary emp.sal%type;--保存更新后的sal内容
V_job emp.job%type;--保存更新后的Job内容
Begin
V_sql_statement:=’update emp set sal=sal*1.2,job=”developer”’||’where empno=:eno returning sal.job into :salary,:job’;
Execute immediate v_sql_statement using v_empno returning into v_salary,v_job;
Dbms_output.put_line(‘salary: ‘||v_salary||’, new position: ‘||v_job);
End;
/
也可以使用RETURN接收影响数据行的数据。
在接收影响数据行数据时，也可以利用return进行操作，如下所示：
v_sql_statement:=’update emp set sal=sal*12,job=”developer” ’||’ where empno=:eno return sal,job into :salary,:job’;
Execute immediate v_sql_statement using v_empno return into v_salary,v_job;
使用return与returning相比没有任何区别
删除数据，取得删除前的结果
Declare
V_sql_statement varchar2(200);--定义SQL操作语句
V_emprow emp%rowtype;--保存emp类型
V_empno emp.empno%type:=7369;--删除的雇员编号
V_ename emp.ename%type;--删除的雇员姓名
V_sal emp.sal%type;--删除的雇员工资
Begin
V_sql_statement:=’delete from emp where empno=:eno returning ename,sal into :name,:sal;
Execute immediate v_sql_statement using v_empno returning into v_ename,v_sal;
Dbms_output.put_line(‘empno: ‘||v_empno||’, ename: ‘||v_ename||’, salary: ‘||v_sal);
End;
/
在使用USING或RETURNING语句时都可以设置参数模式（IN/OUT/IN OUT），其中对USING子句主要是使用变量定义的内容，所以默认的模式是IN模式。使用RETURNING子句时不需要设置内容，只需接收返回内容，所以其模式为OUT。
编写部门增加过程
Create or replace procedure dept_insert_proc(
P_deptno in out dept.deptno%type,--此处可以将p_deptno的内容回传
P_dname dept.dname%type,--默认为in模式
P_loc dept.loc%type) as --默认为in模式
Begin
Select max(deptno) into p_deptno from dept;--取得最大的deptno内容
P_deptno:=p_deptno+1;--让最大值部门编号+1，此处不考虑超过两位数字情况
Insert into dept(deptno,dname,loc) values (p_deptno,p_dname,p_loc);
End;
/
批量绑定
通过动态SQL进行查询或更新操作时，每次都是向数据库提交了一条操作语句，如果现在希望数据库可以一次性接收多条SQL，以及数据库可以一次性将操作结果返回到某一个集合中时，就可以采用批量处理操作完成，在进行批量处理操作中，注意依靠BULK COLLECT进行操作。
使用BULK COLLECT更新
DECLARE
TYPE ename_index IS TABLE OF emp.ename%TYPE INDEX BY PLS_INTEGER ;
TYPE job_index IS TABLE OF emp.job%TYPE INDEX BY PLS_INTEGER ;
TYPE sal_index IS TABLE OF emp.sal%TYPE INDEX BY PLS_INTEGER ;
v_ename ename_index ;
v_job job_index ;
v_sal sal_index ;
v_sql_statement VARCHAR2(200) ; --定义动态SQL
v_deptno emp.deptno%TYPE := 10 ; --查询10部门
BEGIN
v_sql_statement := 'UPDATE emp SET sal=sal*1.2 WHERE deptno=:dno ' ||
' RETURNING ename,job,sal INTO :ena, :ej, :es' ;-- 此时返回多行更新结果（注意空格，否则无法识别语句）
EXECUTE IMMEDIATE v_sql_statement USING v_deptno
RETURNING BULK COLLECT INTO v_ename,v_job,v_sal ;
FOR x IN 1 .. v_ename.COUNT LOOP
DBMS_OUTPUT.put_line('雇员姓名' || v_ename(x) || '，职位：' || v_job(x) || '，工资：' || v_sal(x)) ;
END LOOP ;
END ;
雇员姓名：CLARK，职位：MANAGER，工资：3528
雇员姓名：KING，职位：PRESIDENT，工资：7200
雇员姓名：MILLER，职位：CLERK，工资：1872
查询时使用BULK COLLECT
Declare
type ename_index is table of emp.ename%type index by pls_integer;
Type job_index is table of emp.job%type index by pls_integer;
Type sal_index is table of emp.sal%type index by pls_integer;
V_ename ename_index;
V_job job_index;
V_sal sal_index;
V_sql_statement varchar2(200);--定义动态SQL
V_deptno emp.deptno%type:=10;--查询10部门
Begin
V_sql_statement:=’select ename,job,sal from emp where deptno=:dno’;--此时返回多行更新结果
Execute immediate v_sql_statement
Bulk collect into v_ename,v_job,v_sal
Using v_deptno;
For x in 1..v_ename.count loop
Dbms_output.put_line(‘ename: ’||v_ename(x)||’ , position: ‘||v_job(x)||’, salary: ‘||v_sal(x));
End loop;
End;
/
FORALL
如果要向动态SQL之中设置多个绑定参数，则就必须利用FORALL语句完成，此语句的语法如下所示。
FORALL索引变量IN参数集合最小值..参数集合最大值
EXECUTE IMMEDIATE 动态SQL字符串
[USING 绑定参数|绑定参数（索引）, ...]
[[RETURNING | RETURN] BULK COLLECT INTO 绑定参数集合 , ...];
通过FORALL设置多个参数
Declare
Type empno_nested is table of emp.empno%type;--定义嵌套表
Type ename_index is table of emp.ename%type index by pls_integer;--定义索引表
Type job_index is table of emp.job%type index by pls_integer;--定义索引表
Type sal_index is table of emp.sal%type index by pls_integer;--定义索引表
V_ename ename_index;--保存删除后的姓名
V_job job_index;--保存删除后的职位
V_sal sal_index;--保存删除后的工资
V_empno empno_nested:=empno_nested(7369,7566,7788);--定义要删除雇员编号
V_sql_statement varchar2(200);--动态SQL
Begin
V_sql_statement:=’delete from emp where empno=:eno’||’
Returning ename,job,sal into :ena, :ej, :es’;--删除数据SQL
FORALL x in 1 .. v_empno.count --FORALL绑定多个变量
EXECUTE IMMEDIATE v_sql_statement using v_empno(x)
Returning bulk collect into v_ename,v_job,v_sal;
For x in 1 .. v_ename.count loop
Dbms_output.put_line(‘ename: ‘||v_ename(x)||’, position: ‘||’, salary: ‘||v_sal(x));
End loop;
End;
/
处理游标操作
动态SQL操作之中，除了可以处理单行查询操作之外，也可以利用游标完成多行数据的操作，而在游标定义时也同样可以使用动态绑定变量的方式，此时就需要在打开游标变量时增加USING子句操作。
在游标中使用动态SQL
DECLARE
emp_cur SYS_REFCURSOR ; -- 定义游标变量
v_emprow emp%ROWTYPE ; -- 定义emp行类型
v_deptno emp.deptno%TYPE := 10 ; -- 定义要查询雇员的部门编号
BEGIN
OPEN emp_cur FOR 'SELECT * FROM emp WHERE deptno=:dno '
USING v_deptno ;
LOOP
FETCH emp_cur INTO v_emprow ; -- 取得游标数据
EXIT WHEN emp_cur%NOTFOUND ; -- 如果没有数据则退出
DBMS_OUTPUT.put_line('雇员姓名：' || v_emprow.ename || '，雇员职位：' || v_emprow.job) ;
END LOOP ;
CLOSE emp_cur ;
END ;
FETCH
在FETCH语句之中利用BULK COLLECT一次性将多个数据保存到集合类型之中，语法如下所示。
FETCH 动态游标 BULK COLLECT INTO 集合变量 ...;
利用FETCH保存查询结果
Declare
Emp_cur sys_refcursor;--定义游标变量
Type emp_index is table of emp%rowtype index by pls_integer;--定义索引表
V_emprow emp_index;--定义emp行类型
V_deptno emp.deptno%type:=10;--定义要查询雇员的部门编号
Begin
Open emp_cur for ‘select * from emp where deptno=:dno’
Using v_deptno;
Fetch emp_cur bulk collect into v_emprow;
Close emp_cur
For x in 1 .. v_emprow.count loop
Dbms_output.put_line(‘empno: ‘||v_emprow(x).empno||’, ename: ‘||v_emprow(x).ename||’, position: ‘||v_emprow(x).job);
End loop;
End;
/
使用动态SQL可以在依赖对象不存在时创建子程序；
动态SQL主要利用EXECUTE IMMEDIATE语句执行DML/DDL/DCL等语句操作;
如果使用了绑定变量，则必须在EXECUTE IMMEDIATE中使用USING子句设置所需要的绑定变量；
使用RETURNING或RETURN语句可以接收查询或更新后的返回结果；
使用批处理可以一次性将数据库之中取回的多个数据保存在集合里，或者使用FORALL将多个绑定参数设置到动态SQL之中。
动态显示游标
输出HR或SCOTT下每张表对应的所有记录数
declare
emp_cur sys_refcursor;
v_emprow emp%rowtype;
v_deptno emp.deptno%type:=10;
begin
open emp_cur for 'select * from emp where deptno=:dno'
using v_deptno;
loop
fetch emp_cur into v_emprow;
exit when emp_cur%notfound;
dbms_output.put_line('ename: '||v_emprow.ename||' position: '||v_emprow.job);
end loop;
close emp_cur;
end;
/