Oracle数据库之动态SQL
1. 静态SQLSQL与动态SQL
Oracle编译PL/SQL程序块分为两个种:一种为前期联编(early binding),即SQL语句在程序编译期间就已经确定,大多数的编译情况属于这种类型;另外一种是后期联编(late binding),即SQL语句只有在运行阶段才能建立,例如当查询条件为用户输入时,那么Oracle的SQL引擎就无法在编译期对该程序语句进行确定,只能在用户输入一定的查询条件后才能提交给SQL引擎进行处理。通常,静态SQL采用前一种编译方式,而动态SQL采用后一种编译方式。
所谓静态SQL指在PL/SQL块中使用的SQL语句在编译时是明确的,执行的是确定对象。而动态SQL是指在PL/SQL块编译时SQL语句是不确定的,如根据用户输入的参数的不同而执行不同的操作。编译程序对动态语句部分不进行处理,只是在程序运行时动态地创建语句、对语句进行语法分析并执行该语句。
2. 动态SQL介绍
Oracle数据库有两种动态SQL技术:使用DBMS_SQL包和本地动态SQL。本地动态SQL是在Oracle 8i之后引入的一种新的执行动态SQL的机制,与使用DBMS_SQL包相比,它更简单、运行速度更快、性能更高,所以DBMS_SQL包的方法已经很少用了。下面主要介绍的是本地动态SQL。
动态SQL语句是在运行时由程序创建的字符串,它们必须是有效的SQL语句或PL/SQL块,也可以包含用于数据绑定的占位符。占位符是未声明的标识符,名称并不重要,只需以冒号开头。如:
'DELETE FROM dept WHERE id = :1 RETURNING loc INTO :2'
'SELECT name, salary FROM employee WHERE salary > :s'
一般在下列的情况下我们才需要使用动态SQL:
在PL/SQL块中执行数据定义语句,数据控制语句或会话控制语句(如ALTER SESSION),因为在PL/SQL中,这样的语句是不允许静态执行的。
为了获取更多的灵活性。例如,想在运行时根据实际需求来为SELECT语句的WHERE子句选择不同的schema对象。
动态地使用包DBMS_SQL执行SQL语句,但是为了获得更好的性能,或是DBMS_SQL不支持的功能。
通常有三种执行不同类型的动态SQL方法:
使用EXECUTE IMMEDIATE语句。
除不能处理多行查询语句,其他的动态SQL包括DDL语句,DCL语句以及单行的SELECT查询都可以。
REF CURSOR动态游标,使用OPEN-FOR,FETCH,CLOSE。
能处理动态的多行查询操作,必须要使用OPEN-FOR语句打开游标,使用FETCH语句循环提取数据,最终使用CLOSE语句关闭游标。
使用批量BULK COLLECT执行动态SQL。
通过使用批量动态SQL语句,可以加快SQL语句处理,进而提高PL/SQL的性能。
3. 使用EXECUTE IMMEDIATE语句
语法:
EXECUTEIMMEDIATE dynamic_sql_stmt[{ into_clause | bulk_collect_into_clause } [ using_clause]
| using_clause [dynamic_returning_clause]
|dynamic_returning_clause
] ;
说明:
dynamic_sql_stmt:是代表一条SQL语句或一个PL/SQL块的字符串表达式。
into_clause:用于存放被选出的字段值的变量或被选出的行记录。格式如:
INTO { variable [, variable]... | record )
using_clause:SQL或PL/SQL字符串中包括用于参数绑定的占位符时,该子句为占位符绑定值,也可用于返回值。输入bind_argument参数是一个表达式,它的值将被输入(IN模式)或输出(OUT模式)或输入输出(IN OUT模式)到动态SQL语句或是PL/SQL块中。一个输出bind_argument参数就是一个能保存动态SQL返回值的变量。格式如:
USING [IN | OUT | IN OUT]bind_argument[[,] [[ IN | OUT | IN OUT] bind_argument ]...
dynamic_returning_clause:指明用于存放返回值的变量或记录。格式如:
{ RETURNING | RETURN } { into_clause | bulk_collect_into_clause }
注意:
可以把所有的绑定参数放到USING子句中,默认的参数模式是IN。对于含有RETURNING子句的DML语句来说,我们可以把OUT参数放到RETURNING INTO之后,并且不用指定它们的参数模式,因为默认就是OUT。如果我们既使用了USING又使用RETURNING INTO,那么,USING子句中就只能包含IN模式的参数了。
每个占位符必须与USING子句和/或RETURNING INTO子句中的一个绑定参数对应。
可以使用数字、字符和字符串作为绑定参数,但不能使用布尔类型(TRUE,FALSE和NULL)。
动态SQL是不支持PL/SQL特有的类型,所以不能使用布尔型或索引表。
示例1:
CREATE OR REPLACE PROCEDUREproc_test
(
table_nameIN VARCHAR2, --表名
field1 IN VARCHAR2, --字段名1
datatype1 IN VARCHAR2, --字段类型1
field2 IN VARCHAR2, --字段名2
datatype2 IN VARCHAR2 --字段类型2
) ASstr_sqlVARCHAR2(200);BEGINstr_sql := 'CREATE TABLE'||table_name||'('||field1||' '||datatype1||','||field2||' '||datatype2||')';EXECUTEIMMEDIATE str_sql;
EXCEPTIONWHEN others THENDBMS_OUTPUT.PUT_LINE(SQLCODE|| '-' ||SQLERRM);END proc_test;
示例2,使用USING子句:
CREATE OR REPLACE PROCEDUREproc_insert
(
idIN NUMBER,
nameIN VARCHAR2)ASstr_sqlVARCHAR2(200);BEGINstr_sql := 'INSERT INTO dinya_test VALUES (:1,:2)';EXECUTEIMMEDIATE str_sql USING id, name;
EXCEPTIONWHEN others THENDBMS_OUTPUT.PUT_LINE(SQLCODE|| '-' ||SQLERRM);END proc_insert;
示例3,USING向后兼容:
DECLAREstr_sqlVARCHAR2(200);
v_eidNUMBER(4) := 1;
v_enameVARCHAR2(20);
v_addressVARCHAR2(100);
v_salaryNUMBER(10, 4) := 5500.0000;BEGINstr_sql := 'UPDATE emp SET salary = :1 WHERE id = :2 RETURNING name, address INTO :3, :4';EXECUTEIMMEDIATE str_sql
USING v_salary, v_eid, OUT v_ename, OUT v_address;EXECUTEIMMEDIATE str_sql
USING v_salary, v_eid
RETURNINGINTOv_ename, v_address;
...END;
当动态INSERT、UPDATE或DELETE语句有一个RETURNING子句时,输出绑定参数可以放到RETURNING INTO或USING子句的后面。
示例4,使用RETURNING INTO子句:
DECLAREp_idNUMBER := 1;
v_countNUMBER;
v_stringVARCHAR2(200);BEGINv_string := 'SELECT COUNT(*) FROM table_name t WHERE t.id=:id';EXECUTE IMMEDIATE v_string USING p_id RETURNING INTOv_count;END;
4. REF CURSOR动态游标,使用OPEN-FOR,FETCH,CLOSE
语法结构如下:
using_clause ::=
说明:
cursor_variable是一个弱类型(没有返回类型)的游标变量。
dynamic_string是字符串表达式,代表一个多行查询。
在运行时,USING子句中的绑定变量可以替换动态SELECT语句中相对应的占位符。
示例:
DECLARETYPE empcurtypIS REF CURSOR;
emp_cv empcurtyp;
v_enameVARCHAR2(15);
v_salNUMBER := 1000;BEGIN
OPEN emp_cv FOR
'SELECT name, salary FROM employee WHERE salary > :s'USING v_sal;
LOOPFETCH emp_cv INTOv_ename, v_sal;EXIT WHEN emp_cv%NOTFOUND;
DBMS_OUTPUT.PUT_LINE('姓名:' || v_ename || ',工资:' ||v_sal);ENDLOOP;CLOSEemp_cv;END;
5. 批量动态SQL
批量绑定能减少PL/SQL和SQL引擎之间的切换,改善性能。批量绑定能让Oracle把SQL语句中的一个变量与一个集合相绑定。集合类型可以是任何PL/SQL集合类型(索引表、嵌套表或变长数组)。但是,集合元素必须是SQL数据类型,如VARCHAR2、DATE或NUMBER。有三种语句支持动态批量绑定:EXECUTE IMMEDIATE、FETCH和FORALL。
5.1 EXECUTE IMMEDIATE批量
示例1:
DECLARETYPE ename_table_typeIS TABLE OF employee.name%TYPE INDEX BYBINARY_INTEGER;
ename_table ENAME_TABLE_TYPE;
v_sqlVARCHAR2(100);BEGINv_sql := 'SELECT name FROM employee WHERE did = :v_deptno';EXECUTE IMMEDIATE v_sql BULK COLLECT INTO ename_table USING &v_deptno;FOR i INename_table.FIRST .. ename_table.LAST LOOP
dbms_output.put_line(ename_table(i));ENDLOOP;END;
示例2:
DECLARETYPE ename_table_typeIS TABLE OF employee.name%TYPE INDEX BYBINARY_INTEGER;
TYPE sal_table_typeIS TABLE OF employee.salary%TYPE INDEX BYBINARY_INTEGER;
ename_table ENAME_TABLE_TYPE;
sal_table SAL_TABLE_TYPE;
v_sqlVARCHAR2(200);BEGINv_sql := 'UPDATE employee SET salary = salary*(1 + :percent / 100)' ||
'where did=:dno' ||
'RETURING ename,sal INTO :name,:salary';EXECUTEIMMEDIATE v_sql
USING&percent,&dno
RETURNINGBULK COLLECT INTOename_table, sal_table;FOR i INename_table.FIRST .. ename_table.LAST LOOP
DBMS_OUTPUT.PUT_LINE('姓名:' || ename_table(i) || ',新工资:' ||sal_table(i));ENDLOOP;END;
5.2 FETCH批量
示例:
DECLARETYPE emp_cur_typeIS REF CURSOR;
TYPE num_listIS TABLE OF NUMBER;
TYPE name_listIS TABLE OF VARCHAR2(50);
emp_cur emp_cur_type;
emp_nums num_list;
enames name_list;
sals num_list;BEGIN
OPEN emp_cur FOR 'SELECT id, name FROM employee';FETCHemp_curBULK COLLECT INTOemp_nums, enames;CLOSEemp_cur;EXECUTE IMMEDIATE 'SELECT salary FROM employee'
BULK COLLECT INTOsals;END;
5.3 FORALL批量
示例:
DECLARETYPE num_listIS TABLE OF NUMBER;
TYPE name_listIS TABLE OF VARCHAR2(50);
emp_nums num_list;
enames name_list;BEGINemp_nums := num_list(1, 2, 3, 4, 5);
FORALL iINemp_nums.FIRST .. emp_nums.LASTEXECUTE IMMEDIATE 'UPDATE employee SET salary = salary * 1.1 WHERE id = :1' ||
'RETURNING name INTO :2'USING emp_nums(i)
RETURNINGBULK COLLECT INTOenames;
...END;