动态SQL番外篇
动态（dynamic）SQL
1.区分静态SQL和动态SQL
1）静态SQL
静态SQL指直接嵌入在PL/SQL块中的SQL语句，静态SQL用于完成特定或固定的任务。
select sal from emp where empno=4000;
2)动态SQL
动态SQL运行PL/SQL块时动态输入的SQL语句。如果在PL/SQL需要执行DDL语句，DCL语句，或者需要执行更加灵活的SQL语句（select中有不同的where条件），需要用到动态SQL。
编译动态SQL语句时，需要将SQL语句存放到字符串变量中，而且SQL语句可以包含占位符（以冒号开始）。
delete from emp where empno=:v_empno;
注意：能用静态SQL的一般不推荐使用动态SQL,静态SQL的语句性能较优。
2.用动态SQL处理非查询语句
3.使用动态SQL处理多行查询语句
4.用集合处理动态SQL语句
5.三种不同的动态SQL方法
1）使用execute immediate语句
除不能处理多行查询语句，其他的动态SQL包括DDL语句，DCL语句以及单行的SELECT查询都可以。
2）使用open-for,fetch,close
能处理动态的多行查询操作，必须使用open-for语句打开游标，使用fetch语句循环提取数据，最终使用close语句关闭游标。
3）使用批量动态SQL
通过使用批量动态SQL语句，可以加快SQL语句处理，进而提高PL/SQL的性能。
6.execute immediate语法
execute immediate dynamic_string
[into {define_variable[,define_variable...]}]
[using [in|out|in out] bind_argument]
[{return|returning} into bind_argument...]
{}中内容是必须存在的。
1）使用execute immediate语句处理DDL操作
v=drop table ||v_table_name;
//删除某个表
create or replace procedure pro_drop_table(v_table_name varchar2)
is
v_sql varchar2(100);
begin
v_sql :=’drop table ‘||v_table_name; —注意table后面的空格
execute immediate v_sql;
end;
2)处理DCL操作
//授予某个权限给某个用户
create or replace procedure pro_grant_priv(v_priv varchar2,
v_username varchar2)
is
v_sql varchar2(100);
begin
v_sql := ‘grant ‘||v_priv||’ to ‘||v_username;
execute immediate v_sql;
end;
##验证
create user test identified by test;
exec pro_grant_priv(‘create session’,'test’);
oracle>conn test/test;
3）处理DML操作
如果DML语句中包含占位符，那么execute immediate语句之后必须带有using语句。如果DML语句中带有returning子句，那么在execute immediate语句之后需要带有returning into子句
//给不同部门加薪
declare
v_sql varchar2(100);
begin
v_sql := ‘update emp set sal = sal*(1+:v_percent/100) where deptno=:v_deptno’;
execute immediate v_sql using &1,&2;
end;
7.使用open-for,fetch,close语句
##动态处理select语句返回多行数据
1）定义游标变量
type cursor_type is ref cursor;
cursor_variable cursor_type;
2）打开游标变量
open cursor_variable for dynamic_string [using bind_argument...];
3）循环提取数据
fetch cursor_variable into {var1,var2…|record_var};
var提取标量变量，record_var提取记录变量。
4）关闭游标
close cursor_variable;
//显示指定部门的所有雇员名和工资
create or replace procedure pro_info(v_deptno number)
is
type emp_cursor_type is ref cursor;
emp_cursor emp_cursor_type;
emp_record emp%rowtype;
v_sql varchar2(100);
begin
v_sql :=’select * from emp where deptno=:v_deptno’;
open emp_cursor for v_sql using v_deptno;
loop
fetch emp_cursor into emp_record;
exit when emp_cursor%notfound;
DBMS_OUTPUT.PUT_LINE(‘ename: ‘||emp_record.ename ||’salary: ‘||emp_record.sal);
end loop;
close emp_cursor;
end;
8.批量动态游标 –bulk
bulk加快批量数据的处理速度，使用bulk子句时，实际是动态SQL语句将变量绑定为集合元素。
集合元素必须使用SQL数据类型（char,number,varchar2,date,timestamp）,不能使用PL/SQL数据类型（binary_integer,boolean）。
1）动态BULK子句的语法：
execute immediate dynamic_string
[bulk collect into define_variable]
[using bind_argument...]
[{returning | return} bulk collect into return_variable...]
2)显示特定部门的所有雇员名
set serveroutput on;
declare
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 deptno=:v_deptno’;
execute immediate v_sql
bulk collect into ename_table using &v_deptno;
for i in 1..ename_table.count loop
dbms_output.put_line(ename_table(i));
end loop;
end;
声明：OSCHINA 博客文章版权属于作者，受法律保护。未经作者同意不得转载。
