动态SQL总结

关于动态SQL的一些总结
目的
动态SQL用于减小程序消耗并获取较高的执行性能，且容易编码，现越来越广泛的应用于数据库程序开发中。

在SQL中，可以采用多种方式构造和执行动态SQL，比如EXECUTE IMMEDIATE、DBMS_SQL包等。

由于ORACLE手册中并未覆盖这些功能，现简单说明下关于EXECUTE IMMEDIATE的用法。

名词定义
Oracle数据库开发PL/SQL块，常用SQL分为：静态SQL语句和动态SQL语句。

静态SQL，指在PL/SQL块中使用的SQL语句在编译时是明确的，执行的是确定对象。

而动态SQL是指在PL/SQL块编译时SQL语句是不确定的，如根据用户输入的参数的不同而执行不同的操作。

编译程序对动态语句部分不进行处理，只是在程序运行时动态地创建语句、对语句进行语法分析并执行该语句。

处理范围
◆语句可变：允许用户在程序运行时，临时动态创建完整的语句
◆条件可变：对于查询语句而言，语句的输出是确定的，但是查询子句（如WHERE
子句，GROUP BY子句，HAVING子句等）可变
◆数据库对象等其他可变：对于别名，查询的表名、视图、过程名、DBLINK名等，
临时构建，均不确定。

语法
EXECUTE IMMEDIATEdynamic_string
[INTO {define_variable [ , define_variable ] … |record}]
[USING [IN | OUT | IN OUT ] bing_argument [, [IN | OUT | IN OUT ] bing_argument] … ]
[{RETURNING | RETURN} INTObind_argument [,bind_argument] … ]
◆dynamic_string用于指定存放SQL语句或PL/SQL块的字符串变量；define_variable
用于指定存放单行查询结果的变量；bind_argument用于存放传递的输入、输出变量。

◆dynamic_string语句需用单引号括起来，需要使用特殊符号的地方需注意转义，如使
用引号时，用’’代替’；需要使用变量的地方，可以直接用：号加变量名，也可以用 || 连接符将直接调用变量。

区别在于EXECUTE IMMEDIATE语句中，前者需要加USING传入变量，后者不用。

使用范例
1.一般执行语句：
Begin
EXECUTEIMMEDIATE'create table tab_test(test_code
varchar2(200),test_value varchar2(20),remark varchar2(200))';
End;
2.带传入参数的动态语句（USING语句）：
3.带传出参数的动态语句（INTO语句）：
Declare
Iv_sql VARCHAR2(2000);
Iv_count NUMBER(30);
Begin
iv_sql :='SELECT count(1) '||
' FROMti_ng_user t' ||
'WHERE t.stat_month = to_date(SYSDATE,''YYYYMM'')';
EXECUTEIMMEDIATE iv_sql INTO Iv_count;
End;
4.调用过程时，用到的绑定变量参数必须指定参数类型，默认为IN型，其他类型需
显式指定：
Declare
Iv_sql VARCHAR2(2000);
Iv_count NUMBER(30);
Iv_channelno NUMBER:=1;
Iv_date VARCHAR2(8):='20111205';
Iv_resultcode NUMBER;
Iv_resulterrinfo VARCHAR2(200);
Begin
iv_sql :='Begin '||
'P_sdr_sal_audit(:1,:2,:3,:4);'||
'End;';
EXECUTEIMMEDIATE iv_sql USINGIN Iv_channelno,IN Iv_date,
OUT Iv_resultcode,OUT Iv_resulterrinfo;
End;
注：需传入和传出参数时，用引入符号“：”来表明动态变量。

冒号后可跟自定义变量名，也可以跟数字。

只是用作位置标示，无实际意义。

5.INTO子句用在USING子句前：
Declare
Iv_sql VARCHAR2(2000);
Iv_count NUMBER(30);
Iv_depart VARCHAR2(10);
Begin
iv_sql :='SELECT count(1) '||
' FROM td_m_staff t '||
' WHERE t.depart_id = :1';
EXECUTEIMMEDIATE iv_sql INTO Iv_count USING Iv_depart;
End;
6.实现处理多行（EXECUTE语句只能处理单行查询，所以需借助游标进行多行处理）
DECLARE
TYPE EMPCURSOR ISREFCURSOR;
EMP EMPCURSOR;
EMP_RECORD TD_M_STAFF%ROWTYPE;
IV_SQL VARCHAR2(1000);
BEGIN
IV_SQL :='SELECT * FROM TD_M_STAFF T WHERE T.DEPART_ID = :1';
OPEN EMP FOR IV_SQL USING&DNO;
LOOP
FETCH EMP INTO EMP_RECORD;
EXITWHEN EMP%NOTFOUND;
DBMS_OUTPUT.PUT_LINE('员工名：'||emp_record.staff_name||',工资：
'||emp_record.staff_sal);
Endloop;
Close emp;
End;
注意说明
◆EXECUTE IMMEDIATE不支持返回多行记录，DBMS_SQL包可以实现返回多行记录。

◆当动态执行SQL语句时，结尾不要用分号；当动态执行PL/SQL块时，结尾加分号。

◆EXECUTE IMMEDIATE可用于处理DML和DDL语句。

但由于DML和DDL的事务执行方
式不同，前者是显示提交，后者为隐式提交。

所以在处理不同类型语句时，需注意提交方式。

◆DML语句即插，删，改，排，检五种操作，由于插，删，改，排在PL/SQL块中均
可直接执行。

动态SQL常用于执行DDL语句、不确定的DML语句和系统控制语句（在PL/SQL块中无法直接使用）。

◆由于静态SQL是在编写PL/SQL块时直接嵌入SQL语句，而动态SQL是运行PL/SQL
块时动态输入SQL语句；静态SQL的性能要优于动态SQL,所以在编写程序时，在功能确定的情况下，应尽量先使用静态SQL。