Oracle系列之五——动态SQL
Oracle系列之五——动态SQL
一.简介
SQL语句包含静态SQL和动态SQL两种。静态SQL一般用来完成一些相对固定的任务，这种SQL在执行时已经编译完成。但是，一些PL/SQL程序要求必须在运行时建立和处理SQL语句，这种SQL语句只有在执行时才能确定，所以被称为动态SQL.
相对来说，静态SQL语句的性能要优于动态SQL语句；因而在编写PL/SQL块时，如果功能完成确定，则应该使用静态SQL;如果不能确定要执行的SQL语句，则使用动态SQL.
在PL/SQL块中编写动态SQL语句时，需要将SQL语句存放到字符串变量中，而且SQL语句可以包含占位符（以冒号开始）；
二.SQL的处理方法
根据动态SQL语句的不同，可以使用三种不同类型的动态SQL方法。
使用EXECUTE IMMEDIATE语句
Execute immediate语句可以处理大多数动态SQL操作，包括DDL语句（CREATE,ALTER,DROP）、DCL语句（grant,revoke）、DCL语句(INSERT,UPDATE,DELETE),以及单行SELECT语句.注意,execute immediate语句不能用于处理多行查询语句.
语法如下:
Execute immediate dynamin_string
[into {defined_variable[,define_variable]…|record}]
[using [in | out | in out] bind_argument [,in | out | in out ] bind_argument]….]
[{returning | return } into bind_argument [,bind_argument]…];
示例1,处理DDL操作:
create or replace procedure drop_table(table_name varchar2) is
sql_statement varchar2(100);
begin
sql_statement :=\'drop table \'|| table_name;
end;
示例2,处理DCL操作
create or replace procedure grant_sys_priv(priv varchar2,
username varchar2) is
sql_state varchar2(100);
begin
sql_state :=\'grant \'|| priv ||\' to \'|| username;
execute immediate sql_state;
end;
示例3,处理DML操作
在使用execute immediate语句处理DML语句时,如果DML语句即没有占位符,也没有returning语句,那么在execute immediate 语句之后不需要带有using和returing into 子句.如果DML操作包含占位符,则必须包含using 子句;如果DML语句带有returning语句,那么在EXECUTE IMMEDIATE语句之后需要带有returning into 子句.
--无占位符和returning子句
declare
sql_stat varchar2(100);
begin
sql_stat :=\'update emp set sal=sal*1.1\';
execute immediate sql_stat;
end;
--有占位符和无returning子句
declare
sql_stat varchar2(100);
begin
sql_stat :=\'update emp set sal=sal*(1+:percent/100)\';
execute immediate sql_stat using &1;
end;
--有占位符和returning子句
declare
sql_stat varchar2(100);
salary number(6,2);
begin
--dml语句中有returning子句
sql_stat :=\'update emp set sal=sal*(1+:percent/100) where empno=:no returning sal into :salary\';
execute immediate sql_stat
using &1,&2
--对应的,这必须有returning into 语句
returning into salary;
dbms_output.put_line(\'新工资:\'|| salary);
end;
--单行select语句
declare
sql_stat varchar2(100);
emp_record emp%rowtype;
begin
--占位符
sql_stat :=\'select * from emp where empno=:no\';
execute immediate sql_stat
into emp_record
--对应的,要用using子句
using &1;
dbms_output.put_line(\'姓名:\'|| emp_record.ename ||\' 工资:\'||
emp_record.sal);
end;
使用OPEN-FOR,FETCH和CLOSE语句
为了处理动态的多行查询操作,必须要使用OPEN-FOR语句打开游标,使用FETCH语句循环提取数据,最终使用CLOSE语句关闭游标.
declare
type emp_cursor_type is ref cursor;
emp_cursor emp_cursor_type;
emp_record emp%rowtype;
sql_stat varchar2(100);
begin
sql_stat :=\'select * from emp where deptno=:no\';
open emp_cursor for sql_stat
using &no;
loop
fetch emp_cursor
into emp_record;
exit when emp_cursor%notfound;
dbms_output.put_line(\'name:\'|| emp_record.ename ||\' ,sal:\'||
emp_record.sal);
end loop;
end;
使用批量动态SQL
批量动态SQL是ORACLE 9i新增加的特性.通过使用批量动态SQL,可以加快sql语句处理,进而提高PL/SQL程序的性能.
通过使用BULK子句,可以加快批量数据的处理速度,从而提高应用程序的性能.当使用Bulk子句时,实际上是动态SQL语句将变量绑定为集合元素.语法如下:
Execute immediate dynamin_string
[bulk collect into {defined_variable[,define_variable]…|record}]
[using [in | out | in out] bind_argument [,in | out | in out ] bind_argument]….]
[{returning | return } bulk collect into bind_argument [,bind_argument]…];
示例如下:
处理单行语句:
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);
--name emp.ename%type;
--salary emp.sal%type;
begin
sql_stat :=\'update emp set sal=sal*(1+:percent/100) where empno=:no returning ename,sal into :na,:sala\';
execute immediate sql_Stat
using &1, &2
returning bulk collect
into ename_table, sal_table;
for i in1 .. ename_table.count loop
dbms_output.put_line(\'姓名:\'|| ename_table(i)||\' 薪水:\'|| sal_table(i));
end loop;
end;
处理多行语句:
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\';
execute immediate sql_Stat
-- using &1, &2
bulk collect
into ename_table;--, sal_table;
for i in1 .. ename_table.count loop dbms_output.put_line(\'姓名:\'|| ename_table(i));--|| \'薪水:\' || sal_table(i)--);
end loop;
end;