概述
动态SQL可以在运行时生成和运行SQL语句,常用于编写通用的和灵活的程序 PL/SQL提供了两种方式来写动态SQL:
- 原生动态SQL,这是PL/SQL的用于构建和运行动态SQL语句的特性
- DBMS_SQL包,用于构建,运行和描述动态SQL语句
原生动态SQL代码比使用DBMS_SQL包更容易阅读和编写,但是你必须在编译时就要知道原生动态
SQL语句中的输入/输出变量的数量和数据类型,否则你就必须使用DBMS_SQL包,而且如果你想要
一个存储过程隐式的返回一个结果集,也必须使用DBMS_SQL包(如果不给存储过程使用 OUT REF CURSOR
类型的参数)
当你同时需要DBMS_SQL包和原生动态SQL时,你可以使用"DBMS_SQL.TO_REFCURSOR"函数和 "DBMS_SQL.TO_CURSOR_NUMBER"函数在它们之间转换
7.1 你什么时候需要动态SQL
在PL/SQL中,有如下情况时你需要动态SQL
- 在编译时还不知道SQL内容
例如,SELECT语句中有一个标识符(例如表名)在编译时还是未知的 - 静态SQL不支持的SQL 如果你不需要动态SQL,而使用静态SQL,有如下好处:
- 成功的编译会验证静态SQL引用的有效数据库对象以及对这些对象的必要的访问权限
- 成功的编译会创建数据库对象依赖
7.2 原生动态语句
大多数情况下使用 EXECUTE IMMEDIATE 语句来处理原生动态SQL
如果动态SQL语句是SELECT语句,而且会返回多行数据,那么原生动态SQL给你两个选择:
- 使用带有 BULK COLLECT INTO 子句的 EXECUTE IMMEDIATE 语句
- 搭配使用 OPEN FOR, FETCH 和 CLOSE 语句 在动态SQL执行了INSERT, UPDATE, DELETE和MERGE后,SQL游标属性依然和静态SQL是一样的
7.2.1 EXECUTE IMMEDIATE语句
原生动态SQL使用 EXECUTE IMMEDIATE 语句来处理动态SQL语句
如果动态语句是自包含的(即它没有绑定变量占位符,且仅有的返回可能会是一个错误),那么
EXECUTE IMMEDIATE语句就不需要子句
如果动态SQL语句中包含了绑定变量占位符,而每个占位符必须在合适的子句中对应一个绑定变量:
- 如果动态SQL语句是一个SELECT语句,而且最多返回一行数据,那么将输出变量放在INTO子句中,而将输入变量放在USING子句中
- 如果动态SQL语句是一个SELECT语句,而且会返回多行数据,那么输出变量就应该放在 BULK COLLECT INTO子句中,而将输入变量放在USING子句中
- 如果动态SQL语句是一个DML语句,且没有使用RETURNING INTO子句,那么就将所有输入变量放在USING子句中
- 如果动态SQL语句是一个DML语句,且使用了RETURNING INTO子句,那么就将输入变量放在USING子句中,而将输出变量放在RETURNING INTO子句中
- 如果动态SQL语句是一个匿名PL/SQL代码块或CALL语句,将所有绑定变量放在USING子句中如果动态SQL语句调用了一个子程序,需要确保:
- 子程序定义在模式(schema)层级,或定义在包头中
- 绑定变量需要和子程序的参数对应(类型要兼容)
- 绑定变量不能是NULL,为了突破这个限制,可以在你想使用NULL的地方使用未初始
- 化的变量
- 绑定变量不能拥有SQL不支持的数据类型(例如字符串做索引的关联数组),如果数
- 据类型是一个集合或记录,必须声明在包头中 示例程序(匿名PL/SQL代码块调用声明于模式中的子程序):
CREATE OR REPLACE PROCEDURE create_dept (
deptid IN OUT NUMBER,
dname VARCHAR2,
mgrid NUMBER,
locid NUMBER
) AUTHID DEFINER
AS
BEGIN
deptid := departments_seq.NEXTVAL;
INSERT INTO departments (
department_id,
department_name,
manager_id,
location_id
) VALUES (
deptid, dname, mgrid, locid
);
END;
/
DECLARE
plsql_block VARCHAR2(500);
new_deptid NUMBER(4);
new_dname VARCHAR2(30) := 'Advertising';
new_mgrid NUMBER(6) := 200;
new_locid NUMBER(4) := 1700;
BEGIN
plsql_block := 'BEGIN create_dept(:a, :b, :c, :d); END;';
EXECUTE IMMEDIATE plsql_block
USING IN OUT new_deptid, new_dname, new_mgrid, new_locid;
END;
/
示例程序(动态调用带BOOLEAN类型形参的子程序):
CREATE OR REPLACE p (
x BOOLEAN
) AUTHID DEFINER
AS
BEGIN
IF x THEN
DBMS_OUTPUT.PUT_LINE('x is true');
END IF;
END;
/
DECLARE
dyn_stmt VARCHAR2(200);
b BOOLEAN := TRUE;
BEGIN
dyn_stmt := 'BEGIN p(:x); END;';
EXECUTE IMMEDIATE dyn_stmt USING b;
END;
/
示例程序(动态调用带RECORD类型形参的子程序):
CREATE OR REPLACE PACKAGE pkg
AUTHID DEFINER
AS
TYPE rec IS RECORD (n1 NUMBER, n2 NUMBER);
PROCEDURE p (x OUT rec, y NUMBER, z NUMBER);
END pkg;
/
CREATE OR REPLACE PACKAGE BODY pkg
AS
PROCEDURE p (
x OUT rec,
y NUMBER,
z NUMBER
)
AS
BEGIN
x.n1 := y;
x.n2 := z;
END p;
END pkg;
/
DECLARE
r pkg.rec;
dyn_str VARCHAR2(3000);
BEGIN
dyn_str := 'BEGIN pkg.p(:x, 6, 8); END;';
EXECUTE IMMEDIATE dyn_str USING OUT r;
DBMS_OUTPUT.PUT_LINE('r.n1 = ' || r.n1);
DBMS_OUTPUT.PUT_LINE('r.n2 = ' || r.n2);
END;
/
示例程序(动态调用带关联数组类型形参的子程序):
CREATE OR REPLACE PACKAGE pkg AUTHID DEFINER AS
TYPE number_names IS TABLE OF VARCHAR(5)
INDEX BY PLS_INTEGER; -- 在这种情况下使用的关联数组必须以PLS_INTEGER来索引
PROCEDURE print_number_names (x number_names);
END pkg;
/
CREATE OR REPLACE PACKAGE BODY pkg AS
PROCEDURE print_number_names (
x number_names
)
IS
BEGIN
FOR i IN x.FIRST .. x.LAST
LOOP
DBMS_OUTPUT.PUT_LINE(x(i));
END LOOP;
END;
END pkg;
/
DECLARE
digit_names pkg.number_names;
dyn_stmt VARCHAR2(3000);
BEGIN
digit_names(0) := 'zero';
digit_names(1) := 'one';
digit_names(2) := 'two';
digit_names(3) := 'three';
digit_names(4) := 'four';
digit_names(5) := 'five';
digit_names(6) := 'six';
digit_names(7) := 'seven';
digit_names(8) := 'eight';
digit_names(9) := 'nine';
dyn_stmt := 'BEGIN pkg.print_number_names(:x); END;';
EXECUTE IMMEDIATE dyn_stmt USING digit_names;
END;
/
示例程序(动态调用带嵌套表类型形参的子程序):
CREATE OR REPLACE PACKAGE pkg AUTHID DEFINER AS
TYPE names IS TABLE OF VARCHAR2(10);
PROCEDURE print_names (x names);
END pkg;
/
CREATE OR REPLACE PACKAGE BODY pkg AS
PROCEDURE print_names (x names) IS
BEGIN
FOR i IN x.FIRST .. x.LAST
LOOP
DBMS_OUTPUT.PUT_LINE(x(i));
END LOOP;
END;
END pkg;
/
DECLARE
fruits pkg.names;
dyn_stmt VARCHAR2(3000);
BEGIN
fruits := pkg.names('apple', 'banana', 'cherry');
dyn_stmt := 'BEGIN pkg.print_names(:x); END;';
EXECUTE IMMEDIATE dyn_stmt USING fruites;
END;
/
示例程序(动态调用VARRAY类型形参的子程序):
CREATE OR REPLACE PACKAGE pkg AUTHID DEFINER AS
TYPE foursome IS VARRAY(4) OF VARCHAR2(5);
PROCEDURE print_foursome (x foursome);
END pkg;
/
CREATE OR REPLACE PACKAGE BODY pkg AS
PROCEDURE print_foursome (x foursome) IS
BEGIN
IF x.COUNT = 0
THEN
DBMS_OUTPUT.PUT_LINE('Empty');
ELSE
FOR i IN x.FIRST .. x.LAST
LOOP
DBMS_OUTPUT.PUT_LINE(x(i));
END LOOP;
END IF;
END;
END pkg;
/
DECLARE
directions pkg.foursome;
dyn_stmt VARCHAR2(3000);
BEGIN
directions := pkg.foursome('north', 'south', 'east', 'west');
dyn_stmt := 'BEGIN pkg.print_foursome(:x); END;';
EXECUTE IMMEDIATE dyn_stmt USING directions;
END;
/
示例程序(在USING子句中使用未初始化的变量来代替NULL):
CREATE TABLE employees_temp AS SELECT * FROM employees;
DECLARE
a_null CHAR(1); -- 未初始化的变量在运行时会被设置为NULL值
BEGIN
EXECUTE IMMEDIATE 'UPDATE employees_temp SET commission_pct = :x'
USING a_null;
END;
/
7.2.2 OPEN FOR, FETCH 和 CLOSE语句
如果动态SQL语句代表的是一个SELECT语句,而且返回了多行数据,那么你可以使用如下的原生
动态SQL来处理结果集:
- 使用OPEN FOR语句来将一个游标变量和动态SQL语句关联起来
在OPEN FOR语句中的USING子句中,为动态SQL语句中的绑定变量占位符指定对应的绑定变量
USING子句中不能使用NULL字面量,你可以使用一个未初始化的变量来代替它 - 使用FETCH语句来获取结果集数据(一次一条/一次多条/一次全部)
- 使用CLOSE语句来关闭游标变量 示例程序:
DECLARE
TYPE EmpCurTyp IS REF CURSOR;
v_emp_cursor EmpCurTyp;
emp_record employees%ROWTYPE;
v_stmt_str VARCHAR2(200);
v_e_job employees.job%TYPE;
BEGIN
-- 带有占位符的动态SQL语句
v_stmt_str := 'SELECT * FROM employees WHERE job_id = :j';
-- 打开游标变量,并在USING子句中指明绑定变量
OPEN v_emp_cursor FOR v_stmt_str USING 'MANAGER';
-- 使用FETCH语句从结果集中每次获取一条数据
LOOP
FETCH v_emp_cursor INTO emp_record;
EXIT WHEN v_emp_cursor%NOTFOUND;
END LOOP;
-- 关闭游标
CLOSE v_emp_cursor;
END;
/
示例程序:
CREATE OR REPLACE PACKAGE pkg AUTHID DEFINER
AS
TYPE rec IS RECORD (f1 NUMBER, f2 VARCHAR2(30));
TYPE mytab IS TABLE OF rec INDEX BY PLS_INTEGER;
END;
/
DECLARE
v1 pkg.mytab; -- 记录(record)的集合(collection)
v2 pkg.rec;
c1 SYS_REFCURSOR;
BEGIN
OPEN c1 FOR 'SELECT * FROM TABLE(:1)' USING v1;
FETCH c1 INTO v2;
CLOSE c1;
DBMS_OUTPUT.PUT_LINE('Values in record are ' || v2.f1 || ' and ' || v2.f2);
END;
/
7.2.3 在动态SQL语句中的重复的占位符名称
7.2.3.1 动态SQL语句不是匿名PL/SQL块或CALL语句
这种情况下,占位符名字的重复是无意义的,在USING子句中的绑定变量,靠位置与占位符
关联,而不是名字
例如,在下面这个动态SQL语句中, :x这个占位符名字的重复是无关紧要的
sql_stmt := 'INSERT INTO payroll VALUES (:x, :x, :y, :x)';
在与之对应的USING子句中,你必须提供4个绑定变量,它们是不同的
EXECUTE IMMEDIATE sql_stmt USING a, b, c, d;
7.2.3.2 动态SQL语句是匿名PL/SQL块或CALL语句
这种情况下,占位符名字的重复就是有意义的,每一个不同的占位符都必须在USING子句中
有一个对应的绑定变量
如果你重复了一个占位符名字,你不必重复对应的绑定变量,对这个重复占位符的引用,会
对应USING子句中的一个绑定变量
在下面的例子中,所有的对 :x 占位符的引用,都对应USING子句中的第一个绑定变量a,而
所有的对 :y 占位符的引用,都对应USING子句中的第二个绑定变量b
CREATE PROCEDURE calc_stats (
w NUMBER,
x NUMBER,
y NUMBER,
z NUMBER
) IS
BEGIN
DBMS_OUTPUT.PUT_LINE(w + x + y + z);
END;
/
DECLARE
a NUMBER := 4;
b NUMBER := 7;
plsql_block VARCHAR2(100);
BEGIN
plsql_block := 'BEGIN calc_stats(:x, :x, :y, :x); END;';
EXECUTE IMMEDIATE plsql_block USING a, b;
END;
/
7.3 DBMS_SQL包
DBMS_SQL包定义了一个称为SQL游标数字的实体,因为它是一个PL/SQL integer,所以你可以将它
在调用"边界"(boundaries)之间传递或存储它
如果符合如下情况之一,你就必须使用DBMS_SQL包:
- 直到运行时之前,你还无法确定SELECT列表
- 直到运行时之前,你还无法确定一个SELECT或DML语句中的哪些占位符需要绑定
- 你想要一个存储过程隐式的返回查询结果集(不通过OUT REF CURSOR参数),这需要用到DBMS_SQL.RETURN_RESULT存储过程 在如下情况下,你必须使用原生动态SQL,而不是DBMS_SQL包:
- 动态SQL语句获取数据并放入记录(record)中
- 在执行了一个动态SQL语句(INSERT, UPDATE, DELETE, MERGE, 单行SELECT语句)之后,你想使用SQL游标属性(%FOUND, %ISOPEN, %NOTFOUND 或 %ROWTYPE)
7.3.1 DBMS_SQL.RETURN_RESULT存储过程
DBMS_SQL.RETURN_RESULT存储过程可以让一个存储子程序隐式返回查询结果集给客户端
程序(间接调用子程序)或子程序的直接调用者,在它返回结果集后,只有接收者才能访问
这个结果集
DBMS_SQL.RETURN_RESULT存储过程有两个重载:
PROCEDURE RETURN_RESULT (rc IN OUT SYS_REFCURSOR,
to_client IN BOOLEAN DEFAULT TRUE);
PROCEDURE RETURN_RESULT (rc IN OUT INTEGER,
to_client IN BOOLEAN DEFAULT TRUE);
rc参数要么是一个打开的游标变量(SYS_REFCURSOR类型),要么是一个打开的游标的游标数
字(INTEGER),要想打开一个游标并获得它的游标数字,需要调用DBMS_SQL.OPEN_CURSOR函数
当to_client参数的值是TRUE(默认)时,DBMS_SQL.RETURN_RESULT存储过程会将查询结果返回
给客户端程序(子程序的间接调用者),如果to_client参数的值为false,那么将会把查询结果
返回给子程序的直接调用者
示例程序:
CREATE OR REPLACE PROCEDURE p AUTHID DEFINER
AS
c1 SYS_REFCURSOR,
c2 SYS_REFCURSOR
BEGIN
OPEN c1 FOR
SELECT first_name, last_name
FROM employees
WHERE employee_id = 176;
DBMS_SQL.RETURN_RESULT(c1);
-- 现在p无法访问结果集
OPEN c2 FOR
SELECT city, state_province
FROM locations
WHERE country_id = 'AU';
DBMS_SQL.RETURN_RESULT(c2);
-- 现在p无法访问结果集
END;
/
-- 匿名块调用存储过程p(在sqlplus中执行,结果集也会在sqlplus中打印输出)
BEGIN
p;
END;
/
7.3.2 DBMS_SQL.GET_NEXT_RESULT存储过程
DBMS_SQL.GET_NEXT_RESULT存储过程可以获得DBMS_SQL.RETURN_RESULT存储过程返回给
接收者的下一个结果集,这两个存储过程以相同的顺序返回结果集
它也有两个重载:
PROCEDURE GET_NEXT_RESULT(c IN INTEGER, rc OUT SYS_REFCURSOR);
PROCEDURE GET_NEXT_RESULT(c IN INTEGER, rc OUT INTEGER);
c参数是一个已打开的游标的游标数字,该游标由调用DBMS_SQL.RETURN_RESULT的子程序直接
或间接打开
为了打开一个游标并获得它的游标数字,可以调用DBMS_SQL.OPEN_CURSOR函数,它有一个可选
参数treat_as_client_for_results,当这个参数值为false(默认)时,调用者(打开游标的)不
会被当做接收查询结果的客户端,当值为true时,调用者(打开游标的)被当作客户端程序
rc参数的值要么是一个游标变量(SYS_REFCURSOR类型)要么是一个游标数字(INTEGER类型)
示例程序:
CREATE OR REPLACE PROCEDURE get_employee_info (
id IN VARCHAR2
) AUTHID DEFINER AS
rc SYS_REFCURSOR;
BEGIN
OPEN rc FOR
SELECT first_name, last_name, email, phone_number
FROM employees
WHERE employee_id = id;
DBMS_SQL.RETURN_RESULT(rc);
OPEN rc FOR
SELECT job_title, start_date, end_date
FROM job_history jh, jobs j
WHERE jh.employee_id = id AND
jh.job_id = j.job_id
ORDER BY start_date DESC;
DBMS_SQL.RETURN_RESULT(rc);
END;
/
-- 匿名块中调用
<<main>>
DECLARE
c INTEGER;
rc SYS_REFCURSOR;
n NUMBER;
first_name VARCHAR2(20);
last_name VARCHAR2(25);
email VARCHAR2(25);
phone_number VARCHAR2(20);
job_title VARCHAR2(35);
start_date DATE;
end_data DATE;
BEGIN
c := DBMS_SQL.OPEN_CURSOR(true);
DBMS.SQL.PARSE(c, 'BEGIN get_employee_info(:id); END;', DBMS_SQL.NATIVE);
DBMS_SQL.BIND_VARIABLE(c, ':id', 176);
n := DBMS_SQL.EXECUTE(c);
-- Get employee info
DBMS_SQL.GET_NEXT_RESULT(c, rc);
FETCH rc INTO first_name, last_name, email, phone_number;
DBMS_OUTPUT.PUT_LINE('Employee: '||first_name||' '||last_name);
DBMS_OUTPUT.PUT_LINE('Email: '||email);
DBMS_OUTPUT.PUT_LINE('Phone: '||phone_number);
-- Get employee job history
DBMS_OUTPUT.PUT_LINE('Titles:');
DBMS_SQL.GET_NEXT_RESULT(c, rc);
LOOP
FETCH rc INTO job_title, start_date, end_date;
EXIT WHEN rc%NOTFOUND;
DBMS_OUTPUT.PUT_LINE('- '||job_title||' ('||start_date||' - '||end_date||')');
END LOOP;
DBMS_SQL.CLOSE_CURSOR(c);
END main;
/
7.3.3 DBMS_SQL.TO_REFCURSOR函数
DBMS_SQL.TO_REFCURSOR函数将一个SQL游标数字转换为一个弱类型游标变量,你可以在原生
动态SQL语句中使用这个游标变量
在将游标数字传递给DBMS_SQL.TO_REFCURSOR之前,你必须先OPEN, PARSE并EXECUTE它
在你将游标数字转换为REF CURSOR类型的游标变量之后,DBMS_SQL操作只能将它当作REF CURSOR
类型的游标变量,例如,使用DBMS_SQL.IS_OPEN函数来查看已经转换过的SQL游标数字是否是打开
的,将导致一个错误
示例程序:
CREATE OR REPLACE TYPE vc_array IS TABLE OF VARCHAR2(200);
/
CREATE OR REPLACE TYPE numlist IS TABLE OF NUMBER;
/
CREATE OR REPLACE PROCEDURE do_query_1 (
placeholder vc_array,
bindvars vc_array,
sql_stmt VARCHAR2
) AUTHID DEFINER IS
TYPE curtype IS REF CURSOR;
src_cur curtype;
curid NUMBER;
bindnames vc_array;
empnos numlist;
depts numlist;
ret NUMBER;
isopen BOOLEAN;
BEGIN
-- Open SQL cursor number;
curid := DBMS_SQL.OPEN_CURSOR;
-- Parse SQL cursor number
DBMS_SQL.PARSE(curid, sql_stmt, DBMS_SQL.NATIVE);
bindnames := placeholders;
-- Bind variables
FOR i IN 1 .. bindnames.COUNT
LOOP
DBMS_SQL.BIND_VARIABLE(curid, bindnames(i), bindvars(i));
END LOOP;
-- Run SQL cursor number
ret := DBMS_SQL.EXECUTE(curid);
-- Switch from DBMS_SQL to native dynamic SQL
src_cur := DBMS_SQL.TO_REFCURSOR(curid);
FETCH src_cur INTO BULK COLLECT INTO empnos, depts;
-- This would cause an error because curid was converted to a REF CURSOR
-- isopen := DBMS_SQL.IS_OPEN(curid);
CLOSE src_cur;
END;
/
7.3.4 DBMS_SQL.TO_CURSOR_NUMBER函数
DBMS_SQL.TO_CURSOR_NUMBER函数可以将一个REF CURSOR类型的游标变量(无论是强类型还是
弱类型的)转换为一个SQL游标数字,你可以将它传递给DBMS_SQL子程序
在将REF CURSOR类型的游标变量传递给DBMS_SQL.TO_CURSOR_NUMBER函数之前,你必须先打开它
在将REF CURSOR类型的游标变量转换为SQL游标数字之后,你就不用使用原生动态SQL操作它了
示例程序:
CREATE OR REPLACE PROCEDURE do_query_2 (
sql_stmt VARCHAR2
) AUTHID DEFINER
IS
TYPE curtyp IS REF CURSOR;
src_cur curtyp;
curid NUMBER;
desctab DBMS_SQL.DESC_TAB;
colcnt NUMBER;
namevar VARCHAR2(50);
numvar NUMBER;
datevar DATE;
empno NUMBER := 100;
BEGIN
-- sql_stmt := 'SELECT ... FROM employees WHERE employee_id = :b1';
-- Open REF CURSOR variable
OPEN src_cur FOR sql_stmt USING empno;
-- Switch from native dynamic SQL to DBMS_SQL package
curid := DBMS_SQL.TO_CURSOR_NUMBER(src_cur);
DBMS_SQL.DESCRIBE_COLUMNS(curid, colcnt, desctab);
-- Define columns
FOR i IN 1 .. colcnt
LOOP
IF desctab(i).col_type = 2
THEN
DBMS_SQL.DEFINE_COLUMN(curid, i, numvar);
ELSIF desctab(i).col_type = 12
THEN
DBMS_SQL.DEFINE_COLUMN(curid, i, datevar);
-- statements
ELSE
DBMS_SQL.DEFINE_COLUMN(curid, i, namevar, 50);
END IF;
END LOOP;
-- Fetch rows with DBMS_SQL package
WHILE DBMS_SQL.FETCH_ROWS(curid) > 0
LOOP
FOR i IN 1 .. colcnt
LOOP
IF desctab(i).col_type = 1
THEN
DBMS_SQL.COLUMN_VALUE(curid, i, namevar);
ELSIF desctab(i).col_type = 2
THEN
DBMS_SQL.COLUMN_VALUE(curid, i, numvar);
ELSIF desctab(i).col_type = 12
THEN
DBMS_SQL.COLUMN_VALUE(curid, i, datevar);
-- statements
END IF;
END LOOP;
END LOOP;
DBMS_SQL.CLOSE_CURSOR(curid);
END;
/
