转自乐沙弥的世界。感谢原作者！
--====================
-- PL/SQL --> 动态SQL
--====================
使用动态SQL是在编写PL/SQL过程时经常使用的方法之一。很多情况下，比如根据业务的需要，如果输入不同查询条件，则生成不同的执行
SQL查询语句，对于这种情况需要使用动态SQL来完成。再比如，对于分页的情况，对于不同的表，必定存在不同的字段，因此使用静态SQL则只
能针对某几个特定的表来形成分页。而使用动态的SQL，则可以对不同的表，不同的字段进行不同的分页。这些情况的处理通常都是用动态SQL来
完成。本文讲述了动态SQL的日常用法。
一、动态SQL和静态SQL
1.静态SQL
静态SQL通常用于完成可以确定的任务。比如传递部门号调用存储过程，返回该部门的所有雇员及薪水信息，则该语句为
SELECT ename，sal INTO lv_ename,lv_sal FROM scott.emp WHERE deptno=&dno;
对于上述类似的DML语句在第一次运行时进行编译，而后续再次调用，则不再编译该过程。即一次编译，多次调用，使用的相同的执行
计划。此种方式被称之为使用的是静态的SQL。
2.动态SQL
动态SQL通常是用来根据不同的需求完成不同的任务。比如分页查询，对于表emp分页，需要使用字段雇员姓名，薪水，雇用日期，且按
薪水降序生成报表，每页显示行数据。而对于表sales，需要使用字段雇员名称，客户名称，销售数量，销售日期，且按销售日期升序
排列。以上两种情况，可以创建存储过程来对其进行分页，通过定义变量，根据输入不同的表名，字段名，排序方法来生成不同的SQL
语句。对于输入不同的参数，SQL在每次运行时需要事先对其编译。即多次调用则需要多次编译，此称之为动态SQL。
动态SQL语句通常存放在字符串变量中，且SQL语句可以包含占位符(使用冒号开头)。
也可以直接将动态SQL紧跟在EXECUTEIMMEDIATE语句之后，如EXECUTE IMMEDIATE 'altertable emp enable row movement'
3.两者的异同
静态SQL为直接嵌入到PL/SQL中的代码，而动态SQL在运行时，根据不同的情况产生不同的SQL语句。
静态SQL为在执行前编译，一次编译，多次运行。动态SQL同样在执行前编译，但每次执行需要重新编译。
静态SQL可以使用相同的执行计划，对于确定的任务而言，静态SQL更具有高效性。但缺乏灵活性
动态SQL使用了不同的执行计划，效率不如静态SQL，但能够解决复杂的问题。
动态SQL容易产生SQL注入，为数据库安全带来隐患。
4.动态SQL语句的几种方法
a.使用EXECUTEIMMEDIATE语句
包括DDL语句，DCL语句，DML语句以及单行的SELECT 语句。该方法不能用于处理多行查询语句。
b.使用OPEN-FOR，FETCH和CLOSE语句
对于处理动态多行的查询操作，可以使用OPEN-FOR语句打开游标，使用FETCH语句循环提取数据，最终使用CLOSE语句关闭游标。
c.使用批量动态SQL
即在动态SQL中使用BULK子句，或使用游标变量时在fetch中使用BULK ，或在FORALL语句中使用BULK子句来实现。
d.使用系统提供的PL/SQL包DBMS_SQL来实现动态SQL，关于该方式请参考后续博文。
二、动态SQL的语法
下面是动态SQL常用的语法之一
EXECUTEIMMEDIATE dynamic_SQL_string
[INTOdefined_variable1, defined_variable2, ...]
[USING[IN | OUT | IN OUT]bind_argument1, bind_argument2,
...][{RETURNING| RETURN} field1, field2, ... INTO bind_argument1,
bind_argument2,...]
1.语法描述
dynamic_SQL_string：存放指定的SQL语句或PL/SQL块的字符串变量
defined_variable1：用于存放单行查询结果，使用时必须使用INTO关键字，类似于使用SELECT ename INTO v_name FROM scott.emp；
只不过在动态SQL时，将INTOdefined_variable1移出到dynamic_SQL_string语句之外。
bind_argument1：用于给动态SQL语句传入或传出参数，使用时必须使用USING关键字，IN表示传入的参数，OUT表示传出的参数，
IN OUT则既可以传入，也可传出。
RETURNING| RETURN 子句也是存放SQL动态返回值的变量。
2.使用要点
a.EXECUTEIMMEDIATE执行DML时，不会提交该DML事务，需要使用显示提交(COMMIT)或作为EXECUTEIMMEDIATE自身的一部分。
b.EXECUTEIMMEDIATE执行DDL,DCL时会自动提交其执行的事务。
c.对于多行结果集的查询，需要使用游标变量或批量动态SQL，或者使用临时表来实现。
d.当执行SQL时，其尾部不需要使用分号，当执行PL/SQL 代码时，其尾部需要使用分号。
f.动态SQL中的占位符以冒号开头，紧跟任意字母或数字表示。
三、动态SQL的使用(DDL，DCL，DML以及单行结果集)
1.使用EXECUTEIMMEDIATE处理DDL操作
下面是一个简单的DDL操作，将其封装在存储过程之中，通过传入表名来进行调用。
CREATE OR REPLACE PROCEDURE trunc_table(table_nameVARCHAR2) --创建存储过程trunc_table
IS
sql_statementVARCHAR2(100);
BEGIN
sql_statement := 'TRUNCATETABLE' || table_name; --为变量进行赋值，用于生成动态SQL语句
EXECUTE IMMEDIATEsql_statement; --使用EXECUTEIMMEDIATE执行动态SQL语句
END;
/
flasher@ORCL> create table tb2 --从scott.emp生产表tb2
2 as select empno,ename,sal,deptno from scott.emp;
flasher@ORCL> select count(1) from tb2;
COUNT(1)
----------
14
flasher@ORCL> exec trunc_table('tb2'); --调用存储过程来对表tb2进行truncate
flasher@ORCL> select count(1) from tb2; --表tb2被清空
COUNT(1)
----------
0
flasher@ORCL> insert into tb2 --重新为表tb2生成记录
2 select empno,ename,sal,deptno from scott.emp;
flasher@ORCL> commit;
2.使用EXECUTEIMMEDIATE处理DCL操作
下面使用sys帐户创建存储过程grant_sys_priv用于给用户授予权限
sys@ORCL> connsys/redhat@orcl as sysdba
CREATE OR REPLACE PROCEDURE grant_sys_priv(privVARCHAR2, username VARCHAR2)
IS
sql_statVARCHAR2(100);
BEGIN
sql_stat := 'GRANT' || priv || 'TO' || username;
EXECUTE IMMEDIATEsql_stat;
END;
/
sys@ORCL> exec grant_sys_priv('connect','usr1');
3.使用EXECUTEIMMEDIATE处理DML操作
在使用EXECUTEIMMEDIATE处理DML操作时，分为几种情况，即不带输入参数，带输入参数，既有输入也有输出参数或返回参数等不同情
况，下面分别对其描述。
a.没有参数传入传出的DML语句
下面的示例中，使用动态SQL删除一条记录，且未使用参数传入。
flasher@ORCL> select * from tb2 where empno=7900; --删除前
EMPNOENAME SAL DEPTNO
-------------------- ---------- ----------
7900JAMES 950 30
flasher@ORCL> DECLARE sql_statVARCHAR2(100);
2 BEGIN
3 sql_stat:='DELETEFROM flasher.tb2 WHEREempno=7900'; --使用动态SQL来删除记录
4 EXECUTE IMMEDIATEsql_stat;
5 END;
6 /
flasher@ORCL> SELECT * FROM tb2 where empno=7900; --验证删除情况
no rowsselected
b.有参数传入的DML语句(使用USING子句)
对于使用了参数传入的动态SQL，需要使用USING子句来指明传入的参数。在下面的示例中，为表tb2插入一条记录，在DML语句中使
用了四个占位符(占位符用以冒号开头，紧跟任意字母或数字表示)。因此在使用EXECUTEIMMEDIATE使用USING子句为其指定其参数。
DECLARE --声明变量
sql_statVARCHAR2(100);
lv_empnotb2.empno%TYPE := 7900;
lv_enametb2.ename%TYPE := 'JAMES';
lv_sal tb2.sal%TYPE := 950;
BEGIN
sql_stat := 'INSERTINTO tb2VALUES(:1,:2,:3,:4)'; --DML语句中使用了占位符
EXECUTE IMMEDIATEsql_stat USINGlv_empno, lv_ename,lv_sal,30; --为占位符指定参数或值
COMMIT;
END;
/
flasher@ORCL> select * from tb2 where empno=7900; --验证插入后的结果
EMPNOENAME SAL DEPTNO
-------------------- ---------- ----------
7900JAMES 950 30
c.处理包含returning子句的DML语句
下面的示例中，对表tb2进行更新，使用了两个占位符，一个是:percent，一个是:eno，因此在使用EXECUTEIMMEDIATE执行动态
DML时，需要使用USING子句且带两个输入参数。其次，动态DML中使用了RETURNINGsal INTO :salary，因此EXECUTE IMMEDIATE后
也必须使用RETURNINGINTO varialbe_name。
DECLARE
salaryNUMBER(6, 2);
sql_statVARCHAR2(100);
BEGIN
sql_stat := 'UPDATEtb2 SET sal = sal * (1 + :percent /100)' --更新sal列，使用占位符:percent
|| 'WHERE empno = :eno RETURNING sal INTO:salary'; --使用了占位符:eno，:salary，以及RETURNING子句
EXECUTE IMMEDIATEsql_stat USING &1, &2RETURNING INTO salary; --必须使用USING及RETURNING子句
COMMIT;
dbms_output.put_line('Newsalary: ' || salary);
END;
/
Enter value for 1: 10
Enter value for 2: 7900
old 7: EXECUTE IMMEDIATEsql_stat USING &1, &2RETURNING INTO salary;
new 7: EXECUTE IMMEDIATEsql_stat USING 10, 7900RETURNING INTO salary;
Newsalary: 1045
d.处理包含检索值的单行查询
下面的示例中，使用SELECT 查询获得单行结果集，使用了占位符:name，因此也需要使用USING子句为其传递参数
DECLARE
sql_statVARCHAR2(100);
emp_recordtb2%ROWTYPE;
BEGIN
sql_stat := 'SELECT* FROM tb2 WHERE ename =UPPER(:name)'; --动态SQL语句为单行DQL语句
EXECUTE IMMEDIATEsql_stat INTO emp_recordUSING '&name'; --使用USING子句为其传递参数
DBMS_OUTPUT.PUT_LINE('Thesalary is' || emp_record.sal || 'for '||emp_record.ename);
END;
/
Enter value for 1: james
old 6: EXECUTE IMMEDIATEsql_stat INTO emp_recordUSING '&1';
new 6: EXECUTE IMMEDIATEsql_stat INTO emp_recordUSING 'james';
Thesalary is 1045 for JAMES
四、动态SQL的使用(处理多行结果集的查询语句)
1.使用游标变量来循环提取数据，其主要流程为
定义游标变量
TYPE cursortype IS REF CURSOR;
cursor_variablecursortype;
打开游标变量
OPEN cursor_variable FOR dynamic_string
[USINGbind_argument[,bind_argument]...]
循环提取数据
FETCH cursor_variable INTO {var1[,var2]...| record_variable};
EXIT WHEN cursor_variable%NOTFOUND
关闭游标变量
CLOSE cursor_variable;
2.使用游标变量处理查询多行结果集
下面的示例中，首先定义了一个游标类型，接下来定义游标变量，以及存放结果集的变量，动态查询语句将获得多个结果集。
OPEN cursorname FOR SELECT ... 时，其SELECT 语句使用了字符串变量(动态SQL)，其后紧跟USING子句。
DECLARE --游标，变量的声明
TYPE emp_cur_type IS REF CURSOR;
emp_cv emp_cur_type;
emp_record tb2%ROWTYPE;
sql_stat VARCHAR2(100);
v_dno NUMBER := &dno;
BEGIN
sql_stat := 'SELECT* FROM tb2 WHERE deptno =:dno'; --动态多行结果集查询语句
OPEN emp_cv FOR sql_statUSINGv_dno; --OPEN 时使用动态查询语句以及USING子句来传递参数
LOOP
FETCH emp_cv INTO emp_record; --从结果集中提取记录
EXIT WHEN emp_cv%NOTFOUND;
dbms_output.put_line('Employeename：' || emp_record.ename || ', Salary：' || emp_record.sal);
END LOOP;
CLOSE emp_cv;
END;
/
Employee name：Henry, Salary：
Employee name：JONES, Salary：
Employee name：ADAMS, Salary：
Employee name：FORD, Salary：
五、动态SQL的使用(FORALL及BULK子句的使用)
1.动态SQL中使用BULK子句的语法
EXECUTEIMMEDIATEdynamic_string --dynamic_string用于存放动态SQL字符串
[BULKCOLLECT INTOdefine_variable[,define_variable...]] --存放查询结果的集合变量
[USINGbind_argument[,argument...]] --使用参数传递给动态SQL
[{RETURNING|RETURN} --返回子句
BULKCOLLECT INTOreturn_variable[,return_variable...]]; --存放返回结果的集合变量
使用bulkcollectinto子句处理动态SQL中的多行查询可以加快处理速度，从而提高应用程序的性能。当使用bulk子句时，集合类型可
以是PL/SQL所支持的索引表、嵌套表和VARRY，但集合元素必须使用SQL数据类型。常用的三种语句支持BULK子句，分别为EXECUTE
IMMEDIATE， FETCH 和FORALL。
2.使用EXECUTEIMMEDIATE 结合BULK子句处理DML语句返回子句
下面的例子，首先定义了两个索引表类型以及其变量，接下来使用动态SQL语句来更新tb2的薪水，使用EXECUTEIMMEDIATE配合BULK
COLLECTINTO 来处理结果集。
DECLARE
TYPE ename_table_type IS TABLE OF tb2.ename%TYPE INDEX BY BINARY_INTEGER; --定义类型用于存放结果集
TYPE sal_table_type IS TABLE OF tb2.sal%TYPE INDEX BY BINARY_INTEGER;
ename_tableename_table_type;
sal_tablesal_table_type;
sql_statVARCHAR2(120);
v_percentNUMBER :=&percent;
v_dno NUMBER :=&dno;
BEGIN
sql_stat := 'UPDATEtb2 SET sal = sal * (1 + :percent /100)' --动态DML语句
|| 'WHERE deptno = :dno'
|| 'RETURNING ename, sal INTO :name,:salary'; --使用了RETURNING子句，有返回值
EXECUTE IMMEDIATEsql_stat USINGv_percent, v_dno --执行动态SQL语句
RETURNING BULK COLLECT INTO ename_table, sal_table; --使用BULKCOLLECT
INTO到集合变量
FOR i IN 1..ename_table.COUNT --使用FOR循环读取集合变量的结果
LOOP
DBMS_OUTPUT.PUT_LINE('Employee' || ename_table(i) || 'Salary is:' || sal_table(i));
END LOOP;
END;
/
EmployeeHenry Salary is: 1694
EmployeeJONES Salary is: 3841.75
EmployeeADAMS Salary is: 1573
EmployeeFORD Salary is: 3872
3.使用EXECUTEIMMEDIATE 结合BULK子句处理多行查询
下面示例中，与前一个示例相同，只不过其动态SQL有查询语句组成，且返回多个结果集，同样使用了BULKCOLLECT INTO来传递结果。
DECLARE
TYPE ename_table_type IS TABLE OF tb2.ename%TYPE INDEX BY BINARY_INTEGER; --定义类型用于存放结果集
TYPE sal_table_type IS TABLE OF tb2.sal%TYPE INDEX BY BINARY_INTEGER;
ename_tableename_table_type;
sal_tablesal_table_type;
sql_statVARCHAR2(100);
BEGIN
sql_stat := 'SELECTename,sal FROM tb2 WHERE deptno =:dno'; --动态DQL语句，未使用RETURNING子句
EXECUTE IMMEDIATEsql_stat BULK COLLECT INTO ename_table,sal_tableUSING &dno; --使用BULKCOLLECT INTO
FOR i IN 1..ename_table.COUNT
LOOP
DBMS_OUTPUT.PUT_LINE('Employee' || ename_table(i) || 'Salary is:' || sal_table(i));
END LOOP;
END;
/
EmployeeHenry Salary is: 1694
EmployeeJONES Salary is: 3841.75
EmployeeADAMS Salary is: 1573
EmployeeFORD Salary is: 4259.2
4.使用FETCH子句结合BULK子句处理多行结果集
下面的示例中首先定义了游标类型，游标变量以及复合类型，复合变量，接下来从动态SQL中OPEN游标，然后使用FETCH将结果存放到复
合变量中。即使用OPEN，FETCH代替了EXECUTEIMMEDIATE来完成动态SQL的执行。
DECLARE
TYPE empcurtype IS REF CURSOR; --定义游标类型及游标变量
emp_cvempcurtype;
TYPE ename_table_type IS TABLE OF tb2.ename%TYPE INDEX BY BINARY_INTEGER; --定义结果集类型及变量
ename_tableename_table_type;
sql_stat VARCHAR2(120);
BEGIN
sql_stat := 'SELECTename FROM tb2 WHERE deptno =:dno'; --动态SQL字符串
OPEN emp_cv FOR sql_stat --从动态SQL中打开游标
USING &dno;
FETCH emp_cv BULK COLLECT --使用BULKCOLLECT
INTO提取结果集
INTO ename_table;
FOR i IN 1 .. ename_table.COUNT LOOP
DBMS_OUTPUT.PUT_LINE('EmployeeName is ' || ename_table(i));
END LOOP;
CLOSE emp_cv;
END;
/
Employee Name is Henry
Employee Name is JONES
Employee Name is ADAMS
Employee Name is FORD
5.使用FORALL语句中使用BULK子句
下面是FORALL子句的语法
FORALLindex IN lower bound..upperbound --FORALL循环计数
EXECUTEIMMEDIATEdynamic_string --结合EXECUTEIMMEDIATE来执行动态SQL语句
USINGbind_argument | bind_argument(index) --绑定输入参数
[bind_argument| bind_argument(index)]...
[{RETURNING| RETURN} BULK COLLECT INTObind_argument[,bind_argument...]]; --绑定返回结果集
FORALL子句允许为动态SQL输入变量，但FORALL子句仅支持DML(INSERT,DELETE,UPDATE)语句，不支持动态的SELECT语句。
下面的示例中，首先声明了两个复合类型以及复合变量，接下来为复合变量ename_table赋值，以形成动态SQL语句。紧接着使用FORALL
子句结合EXECUTEIMMEDIATE 来提取结果集。
DECLARE --定义复合类型及变量
TYPE ename_table_type IS TABLE OF tb2.ename%TYPE;
TYPE sal_table_type IS TABLE OF tb2.sal%TYPE;
ename_tableename_table_type;
sal_tablesal_table_type;
sql_statVARCHAR2(100);
BEGIN
ename_table := ename_table_type('BLAKE', 'FORD', 'MILLER'); --为复合类型赋值
sql_stat := 'UPDATEtb2 SET sal = sal * 1.1 WHERE ename =:1' --定义动态SQL语句
|| 'RETURNING sal INTO :2';
FORALLi IN 1..ename_table.COUNT --为FORALL 设定起始值
EXECUTE IMMEDIATEsql_stat USING ename_table(i) --使用EXECUTEIMMEDIATE 结合RETURNING BULK COLLECT INTO获取结果集
RETURNING BULK COLLECT INTO sal_table;
FOR j IN 1..ename_table.COUNT
LOOP
DBMS_OUTPUT.PUT_LINE('Thenew salary is' || sal_table(j) || 'for ' ||ename_table(j)) ;
END LOOP;
END;
/
Thenewsalary is 3135 for BLAKE
Thenewsalary is 4259.2 for FORD
Thenewsalary is 1760 for MILLER
2327
扫一扫
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
