Oracle数据库中的动态SQL是一种在运行时构建和执行SQL语句的技术。与传统的静态SQL（在编写程序时SQL语句就已经确定）不同，动态SQL允许开发者在程序执行过程中根据不同的条件或用户输入来构建SQL语句。这使得动态SQL在处理复杂查询、存储过程中灵活处理未知或变化的数据结构时非常有用。
1、 动态SQL的类型
Oracle中动态SQL主要有两种形式：
-
本地动态SQL（Native Dynamic SQL）：
- 使用
EXECUTE IMMEDIATE语句来执行单个的SQL语句或PL/SQL匿名块。 - 主要用于执行不需要返回结果的SQL语句，如INSERT、UPDATE、DELETE、DDL（数据定义语言）语句等。
- 也可以使用
INTO子句将查询结果存储在PL/SQL变量中。
- 使用
-
DBMS_SQL包：
- 提供了一套用于执行动态SQL语句的接口，允许执行更复杂的动态SQL，包括查询和DML操作。
- 可以处理游标和绑定变量，使得处理查询结果集和参数化查询成为可能。
- 使用步骤包括打开游标、绑定变量、执行SQL语句、处理结果集（如果有的话）、关闭游标。
2、 使用EXECUTE IMMEDIATE
EXECUTE IMMEDIATE语句非常适合执行简单的动态SQL语句，如：
2.1、从动态PL/SQL块调用子程序
Invoking Subprogram from Dynamic PL/SQL Block
In this example, the dynamic PL/SQL block is an anonymous PL/SQL block that invokes a subprogram created at schema level.
create or replace procedure sp_insert_dept
( deptid in out number,
dname in varchar2,
mgrid in number,
locid in 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);
commit;
end;
/
-- 定义输入参数并执行
DECLARE
plsql_block VARCHAR2(500);
new_deptid NUMBER(4);
new_dname VARCHAR2(30) := 'super';
new_mgrid NUMBER(6) := 200;
new_locid NUMBER(4) := 1700;
BEGIN
-- Dynamic PL/SQL block invokes subprogram:
plsql_block := 'BEGIN sp_insert_dept(:a, :b, :c, :d); END;';
/* Specify bind variables in USING clause.
Specify mode for first parameter.
Modes of other parameters are correct by default. */
EXECUTE IMMEDIATE plsql_block USING IN OUT new_deptid, new_dname, new_mgrid, new_locid;
END;
/
-- 执行结果
deptid = 280 new_dname= dbms_output.put_line new_mgrid= 202 new_locid= 3200
PL/SQL procedure successfully completed.
-- 检查表数据
HR@192.168.80.190:1521/racdb> select * from departments;
DEPARTMENT_ID DEPARTMENT_NAME MANAGER_ID LOCATION_ID
------------- ------------------------------ ---------- -----------
10 Administration 200 1700
20 Marketing 201 1800
30 Purchasing 114 1700
40 Human Resources 203 2400
50 Shipping 121 1500
。。。。。。。。中间省略 。。。。。。。。。。。。。
280 super 200 1700
2.2、用BOOLEAN形式参数动态调用子程序
Dynamically Invoking Subprogram with BOOLEAN Formal Parameter
In this example, the dynamic PL/SQL block is an anonymous PL/SQL block that invokes a subprogram that has a formal parameter of the PL/SQL data type BOOLEAN.
CREATE OR REPLACE PROCEDURE sp_test_boolean (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 sp_test_boolean(:x); END;';
EXECUTE IMMEDIATE dyn_stmt USING b;
END;
/
-- 执行结果
x is true
PL/SQL procedure successfully completed.
-- 注意执行成功的数据库版本oracle19c
-- 测试如果是oracle11g的环境会报错
ERROR at line 6:
ORA-06550: line 6, column 36:
PLS-00457: expressions have to be of SQL types
ORA-06550: line 6, column 3:
PL/SQL: Statement ignored
2.3、用RECORD形式参数动态调用子程序
Dynamically Invoking Subprogram with RECORD Formal Parameter
In this example, the dynamic PL/SQL block is an anonymous PL/SQL block that invokes a subprogram that has a formal parameter of the PL/SQL (but not SQL) data type RECORD. The record type is declared in a package specification, and the subprogram is declared in the package specification and defined in the package body.
CREATE OR REPLACE PACKAGE pkg_record_datatype
AUTHID DEFINER
AS
TYPE rec IS RECORD (n1 NUMBER, n2 NUMBER);
PROCEDURE sp_record_datatype (x OUT rec, y NUMBER, z NUMBER);
END pkg_record_datatype ;
/
CREATE OR REPLACE PACKAGE BODY pkg_record_datatype
AS
PROCEDURE sp_record_datatype (x OUT rec, y NUMBER, z NUMBER) AS
BEGIN
x.n1 := y;
x.n2 := z;
END sp_record_datatype ;
END pkg_record_datatype ;
/
DECLARE
r pkg_record_datatype.rec;
dyn_str VARCHAR2(3000);
BEGIN
dyn_str := 'BEGIN pkg_record_datatype.sp_record_datatype(:x, 100, 1008); 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;
/
执行结果 – 注意(oracle19c版本)，oracle11g依旧报错
r.n1 = 100
r.n2 = 1008
3、 使用DBMS_SQL包
DBMS_SQL包用于执行更复杂的动态SQL，包括查询和需要处理结果集的DML操作。以下是使用DBMS_SQL包的基本步骤：
3.1、DBMS_SQL.RETURN_RESULT Procedure
In this example, the procedure p invokes DBMS_SQL.RETURN_RESULT without the optional to_client parameter (which is TRUE by default). Therefore, DBMS_SQL.RETURN_RESULT returns the query result to the subprogram client (the anonymous block that invokes p). After p returns a result to the anonymous block, only the anonymous block can access that result.
CREATE OR REPLACE PROCEDURE sp_dbms_sql_test 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);
-- Now p cannot access the result.
OPEN c2 FOR
SELECT city, state_province
FROM locations
WHERE country_id = 'AU';
DBMS_SQL.RETURN_RESULT (c2);
-- Now p cannot access the result.
END;
/
BEGIN
sp_dbms_sql_test ;
END;
/
执行结果
ResultSet #1
FIRST_NAME LAST_NAME
-------------------- -------------------------
Jonathon Taylor
ResultSet #2
CITY STATE_PROVINCE
------------------------------ -------------------------
Sydney New South Wales
注意：Oracle12c中PL/SQL(DBMS_SQL)新特性之隐式语句结果，DBMS_SQL.RETURN_RESULT隐式返回查询结果，Oracle11g执行上面的示例会报错，不支持RETURN_RESULT。
动态SQL为Oracle数据库应用提供了极大的灵活性和功能，但使用时也需要注意SQL注入等安全问题。因此，在处理用户输入时，应该使用参数化查询或适当的输入验证来防止潜在的安全风险。
3.2、DBMS_SQL.GET_NEXT_RESULT
希望通过客户端应用来处理这些结果集，这可以通过DBMS_SQL包的 GET_NEXT_RESULT过程来解决
DECLARE
l_sql_cursor PLS_INTEGER;
l_ref_cursor SYS_REFCURSOR;
l_return PLS_INTEGER;
l_col_cnt PLS_INTEGER;
l_desc_tab DBMS_SQL.desc_tab;
l_count NUMBER;
l_EMPLOYEE_ID EMPLOYEES.EMPLOYEE_ID%TYPE;
l_FIRST_NAME EMPLOYEES.FIRST_NAME%TYPE;
l_LAST_NAME EMPLOYEES.LAST_NAME%TYPE;
BEGIN
-- 执行过程
l_sql_cursor := DBMS_SQL.open_cursor(treat_as_client_for_results => TRUE);
DBMS_SQL.parse(c => l_sql_cursor,
statement => 'BEGIN get_result_emp(30); END;',
language_flag => DBMS_SQL.native);
l_return := DBMS_SQL.execute(l_sql_cursor);
-- 循环遍历每个结果集
LOOP
-- 获取下个结果集
BEGIN
DBMS_SQL.get_next_result(l_sql_cursor, l_ref_cursor);
EXCEPTION
WHEN NO_DATA_FOUND THEN
EXIT;
END;
-- 检查结果集列数
l_return := DBMS_SQL.to_cursor_number(l_ref_cursor);
DBMS_SQL.describe_columns (l_return, l_col_cnt, l_desc_tab);
l_ref_cursor := DBMS_SQL.to_refcursor(l_return);
-- 根据列数处理结果集
CASE l_col_cnt
WHEN 1 THEN
DBMS_OUTPUT.put_line('The column is COUNT:');
FETCH l_ref_cursor
INTO l_count;
DBMS_OUTPUT.put_line('l_count=' || l_count);
CLOSE l_ref_cursor;
WHEN 3 THEN
DBMS_OUTPUT.put_line('The columns are EMPLOYEE_ID and FIRST_NAME and l_LAST_NAME:');
LOOP
FETCH l_ref_cursor
INTO l_EMPLOYEE_ID, l_FIRST_NAME,l_LAST_NAME;
EXIT WHEN l_ref_cursor%NOTFOUND;
DBMS_OUTPUT.put_line('l_EMPLOYEE_ID=' || to_char(l_EMPLOYEE_ID) || CHR(9) || 'l_FIRST_NAME=' || l_FIRST_NAME || CHR(9)|| 'l_LAST_NAME=' || l_LAST_NAME);
END LOOP;
CLOSE l_ref_cursor;
ELSE
DBMS_OUTPUT.put_Line('I wasn''t expecting that!');
END CASE;
END LOOP;
END;
/
执行结果
The columns are EMPLOYEE_ID and FIRST_NAME and l_LAST_NAME:
l_EMPLOYEE_ID=114 l_FIRST_NAME=Den l_LAST_NAME=Raphaely
l_EMPLOYEE_ID=115 l_FIRST_NAME=Alexander l_LAST_NAME=Khoo
l_EMPLOYEE_ID=116 l_FIRST_NAME=Shelli l_LAST_NAME=Baida
l_EMPLOYEE_ID=117 l_FIRST_NAME=Sigal l_LAST_NAME=Tobias
l_EMPLOYEE_ID=118 l_FIRST_NAME=Guy l_LAST_NAME=Himuro
l_EMPLOYEE_ID=119 l_FIRST_NAME=Karen l_LAST_NAME=Colmenares
The column is COUNT:
l_count=107
PL/SQL procedure successfully completed.
扫一扫
1746
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
