Oracle 动态SQL「建议收藏」

大家好，又见面了，我是你们的朋友全栈君。Oracle 动态SQL

一、动态SQL的简介

1、定义

静态SQL是指直接嵌入到PL/SQL块中的SQL语句。

动态SQL是指运行PL/SQL块是动态输入的SQL语句。

2、适用范围

如果在PL/SQL块中需要执行DDL语句(create,alter,drop等)、DCL语句(grant,revoke等)或更加灵活的SQL语句，需要用到动态SQL。

3、静态与动态SQL的比较

1）、静态SQL是在编写PL/SQL块是直接嵌入的SQL语句，而动态SQL是在运行PL/SQL块时动态数据的SQL语句。

2）、静态SQL性能优于动态SQL。

4、动态SQL的处理方法

1）、适用execute immediate

execute immediate可以处理多数动态SQL操作，包括DDL语句、DCL语句、DML语句、及单行select语句(不能处理多行查询)。

2）、适用open-for,fetch和colse语句

为了处理动态的多行查询操作，必须要使用open-for打开游标，使用fetch循环提取数据，最终使用close关闭游标。

3）、使用批量动态SQL

9i新增，通过使用批量动态SQL，可以加快SQL语句处理，进而提高PL/SQL程序的性能。

二、处理非查询语句及单行查询语句

1、定义

处理非查询语句(DDL\DCL\DML等)及单行查询语句使用execute immediate

语法：

execute immediate dynamic_string

[into [define_name1 [,define_name2]…]

[using [in|out|in out] bind_name1 [,[in|out|in out] bingd_nane2]…]

[[returning|return] into bind_name1[,bind_name2]…];

其中dynamic_string用于指定存放SQL语句或PL/SQL块的字符串变量；

define用于指定存放单位查询结果的变量；

bind_name(in)用于指定存放被传递给动态SQL值得变量；

bind_name(out)用于指定存放动态SQL返回值得变量；

2、处理DDL操作

create or replace procedure drop_table(table varchar2) is

v_sql varchar2(100);

begin

v_sql:=’drop table ‘|| table_name;

execute immediate v_sql;

end;

3、处理DCL操作

create or replace procudure grant_sys_priv(priv varchar2,username varchar2) is

v_sql varchar2(100);

begin

v_sql:=’grant ‘||priv||’ to ‘||username;

execute immediate v_sql;

end;

4、处理DML操作

1）、处理无占位符合和returning子句的DML语句

declate

v_sql varchar2(100);

begin

v_sql:=’update emp set sql=sql*1.1 where deptno=30′;

execute immediate v_sql;

end;

2）、处理包含占位符的DML语句

declate

v_sql varchar2(100);

begin

v_sql:=’update emp set sql=sql*(1+:percent/100) where deptno=:dno’;

execute immediate v_sql using &1,&2;

end;

3）、处理包含returning子句的DML语句

说明：使用execute immediate处理带有returning子句的DML语句时，只能处理作用在单行上的DML语句；

如果DML语句作用在多行上，则必须使用bulk子句，此种情况下之后讲述。

declare

salary number(6,2);

v_sal varchar2(100);

begin

v_sql:=’update emp set sal=sal*(1+:percent/100) where empno=:eno returning sal into :salary’;

execute immediate v_sql using &1,&2 returning into salary;

dbms_output.put_line(‘新工资:’||salary);

end;

5、处理单行查询

说明：execute immediate不能处理多行查询。

declare

v_sql varchar2(100);

wmp_record emp%rowtype;

begin

v_sql:=’select * from emp where empno=:eno’;

execute immediate v_sql into wmp_record using &1;

dbms_output.put_line(‘雇员’||emp_record.ename||’的工资’||emp_record.sal);

end;

三、处理多行查询语句

说明：需要通过游标来完成

declare

type empcurtyp is ref cursor;

emp_cv empcurtyp;

emp_record emp%rowtype;

v_sal varchar2(100);

begin

v_sql:=’select * from emp where deptno=:dno’;

open emp_cv for v_sql using &dno;

loop

fetch emp_cv into emp_record;

exit when emp_cv%notfound;

dbms_output.put_line(‘雇员名:’||emp_record.ename||’工资:’||emp.record.sal);

end loop;

close emp_cv;

wnd;

四、在动态SQL中使用bulk子句

1、概述

在动态SQL中使用BULK子句为9i新增特征，BULK子句实际是动态SQL语句将变量绑定为集合元素，

集合类型可以是PL/SQL所支持的索引表、嵌套表和VARRAY。但集合元素必须使用SQL数据类型

(例如number、char等)，而不能使用PL/SQL数据类型(例binary_integer、boolean等)。

9i开始有三种语句支持BULK子句，execute immediate、fetch和forall，下面逐一讲述。

2、在execute immediate语句中使用bulk子句

1）、概述

通过在execute immediate语句中使用bulk子句可以处理作用在多行上的动态DML返回子句，和多行查询语句。

2）、语法

execute immediate dynamic_name

[bulk collect into define_name[,define_name…]]

[using bind_name[,bind_name…]]

[{returning|return} bulk collect into return_name[,return_name…]];

其中dynamic_name用于指定存放动态SQL语句的字符串变量；

define_name用于指定存放查询结果的集合变量；

bind_name指定绑定变量(存放传递给动态SQL的数据)；

return_name指定接收returning子句返回结果的集合变量；

3）、处理作用在多行上的动态DML语句返回子句

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;

v_sql varchar2(100);

begin

v_sql:=’update emp set sal=sal*(1+:percent/100) where deptno=:dno returning ename,sal into :name,:salary’;

execute immediate v_sql using &percent,&dno returning bulk collect into ename_table,sal_table;

for i in 1..ename_table.count loop

dbms_output.put_line(‘雇员’||ename_table(i)||’的新工资为’||sal_table(i));

end loop;

end;

4）、处理多行查询语句

declare

type ename_table_type is table of emp.ename%type index by binary_integer;

ename_table ename_table_type;

v_sal varchar2(100);

begin

v_sal:=’select ename from emp where deptno=:dno’;

execute immediate v_sql bulk collect into ename_table using &dno;

for i in 1..ename_table.count loop

dbms_output.put_line(ename_table(i));

end loop;

end;

3、在fetch语句中使用bulk子句。

1）、概述

在原来的open-for,fetch,close语句处理动态多行查询语句时，默认每次提取单行数据，循环提取。

通过在fetch语句中引入bulk子句，可以一次提取所有数据。

2）、语法

fetch dynamic_cursor

bulk collect into define_name[,define_name…];

3）、处理多行查询语句

declare

type empcurtyp is ref cursor;

emp_cv empcurtyp;

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 job=:title’;

open emp_cv for v_sql using ‘&job’;

fetch emp_cv bulk collect into ename_table;

for i in 1..ename_table.count loop

dbms_output.put_line(ename_table(i));

end loop;

close emp_cv;

end;

4、在forall语句中使用bulk子句。

1）、概述

使用forall语句，可允许在动态SQL语句中为输入变量同时提供多个数据，

但forall语句只适用于动态的insert\update\delete语句，

不适用于动态select语句，并且forall语句是和execute immediate结合使用的。

2）、语法

forall index in lower bound…upper bound

execute immediate dynamic_name

using bind_name | bind_name(index) [,bind_name | bind_name(index)]…

[{returning | return} bulk collect into bind_name[,bind_name…]];

3）、处理作用在多行上的动态DML语句返回子句

declare

type ename_table_type is table of emp.ename%type;

type sal_table_type is table of emp.sal%type;

ename_table ename_table_type;

sal_table sal_table_type;

v_sal varchar2(100);

begin

ename_table:=ename_table_type(‘scott’,’smith’,’clark’);

v_sql:=’update emp set sal=sal*1.1 where ename=:1 returning sal into :2′;

forall i in 1..ename_table.count

execute immediate v_sql using ename_table(i) returning bulk collect into v_sal;

for j in 1..ename_table.count loop

dbms_output.put_line(‘雇员’||ename_table(i)||’新工资为’||sal_table(j));

end loop;

end;