动态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 博客文章版权属于作者，受法律保护。未经作者同意不得转载。

相关推荐

1. queXML 1.4.0 发布，Web 问卷调查
2. Genymotion 2.0 发布，安卓的 x86 模拟器
3. Groovy&Grails-代码剪辑-日志跟踪
4. Fossil 1.27 发布 分布式版本控制系统
5. 应用之间调用