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

posted @ 2014-05-05 00:56  明月镇魂  阅读(207)  评论(0编辑  收藏  举报