动态SQL详解-优快云博客

什么是动态sql

与静态sql不同，静态sql在每次执行的时候都会一样，动态sql可以让你在运行的时候构造sql字符串。字符串包含sql语句或pl/sql块。你可以用动态sql创建一般目的，灵活的应用程序。可以再pl/sql，java，c/c++中来使用动态sql

举个例子，假如你在数据仓库中运行报表程序，直到在运行的时候才知道表名，这些表以么个季度的开始月命名，例如inv_01_2003,inv_04_2003, inv_07_2003, inv_10_2003, inv_01_2004，这里，可以使用动态sql来指定运行时候的表名。

使用动态sql来编程

你可以使用execute immediate来处理动态sql，为了处理多行查询，使用open-for,fetch,和close语句。

可以再下面的方法中使用动态sql

在pl/sql块中放置动态sql语句

在dbms_sql包中调用存储过程。

为什么使用动态sql

动态sql和静态sql各有优劣，静态sql在编译的时候就知道了sql文本，提供下面的优势：

静态sql的性能一般比动态sql的要好

Successful compilation verifies that the SQL statements reference valid database objects and that the necessary privileges are in place to access the objects.

动态sql能提供下面的好处：

只有在运行的时候才能确定sql的文本

想要执行在纯静态sql中不支持的ddl语句或是别的语句

要写歌程序来处理数据定义的改变，无需重新编译。动态sql更加灵活，可以让你在不同的环境中重用代码。

下面描述了使用动态sql的典型场景：

在pl/sql中执行ddl和scl

执行动态查询

参照编译时不存在的对象

执行动态pl/sql

Optimizing Execution Dynamically

Performing Dynamic Operations Using Invoker's Rights

1在pl/sql中执行ddl和scl语句

session control language(scl)像是alter session 和set role

下面是一个在静态sql中无法执行的sql例子

-- Create an object t_emp and a datatype t_emplist as a table of type t_emp
CREATE TYPE t_emp AS OBJECT (id NUMBER, name VARCHAR2(20))
/
CREATE TYPE t_emplist AS TABLE OF t_emp
/
-- Create a table with a nested table of type t_emplist
CREATE TABLE dept_new (id NUMBER, emps t_emplist)
NESTED TABLE emps STORE AS emp_table;
-- Populate the dept_new table with data
INSERT INTO dept_new VALUES
(
10,
t_emplist
(
    t_emp(1, 'SCOTT'),
    t_emp(2, 'BRUCE')
)
);
-- Write a PL/SQL block that queries table dept_new and nested table emps
-- SELECT ... FROM ... TABLE is not allowed in static SQL in PL/SQL
DECLARE
v_deptid NUMBER;
v_ename   VARCHAR2(20);
BEGIN
EXECUTE IMMEDIATE 'SELECT d.id, e.name
                     FROM dept_new d, TABLE(d.emps) e
                     WHERE e.id = 1'
INTO v_deptid, v_ename;
END;
/

下面是一个参照运行时才知道的动态sql例子

Example 8-2 Dynamically Specifying a Table Name

CREATE OR REPLACE PROCEDURE query_invoice
  (p_month VARCHAR2, p_year VARCHAR2)
IS
  TYPE cur_typ IS REF CURSOR;
  v_inv_cursor cur_typ; -- Declare a cursor variable
  v_inv_query  VARCHAR2(200);
  v_inv_num    NUMBER;
  v_inv_cust   VARCHAR2(20);
  v_inv_amt    NUMBER;
BEGIN
  -- Write dynamic query receiving month and year as parameters
  -- and using these values to form the table name, for example, inv_APR_2004
  v_inv_query := 'SELECT num, cust, amt FROM inv_' 
                 || p_month 
                 || '_'
                 || p_year 
                 || ' WHERE v_inv_num = :g_id';
  -- Open a cursor variable
  OPEN v_inv_cursor FOR v_inv_query USING v_inv_num;
  -- Fetch row into variables
    LOOP
      FETCH v_inv_cursor 
        INTO v_inv_num, v_inv_cust, v_inv_amt;
      EXIT WHEN v_inv_cursor%NOTFOUND;
      -- process row here
    END LOOP;
  CLOSE v_inv_cursor;
END;
/

在一些情况下，你可能需要构建一个sql，这个sql能拼接hints和sql语句，这样在当前的数据库环境中能不用重新编译就能修改sql的hint。

Example 8-3 Concatenating Hints
CREATE OR REPLACE PROCEDURE query_emp
  (p_hint VARCHAR2) 
IS
  TYPE cur_typ IS REF CURSOR;
  v_emp_cursor cur_typ;
BEGIN
  OPEN v_emp_cursor FOR 'SELECT '
                        || p_hint 
                        ||' empno, ename, sal, job FROM emp WHERE empno = 7566';  -- process ...
  CLOSE v_emp_cursor;
END;
/
 
In Example 8-3, the user can pass values such as the following for p_hint:
p_hint = '/*+ ALL_ROWS */' 
p_hint = '/*+ FIRST_ROWS */' 
p_hint = '/*+ CHOOSE */' 
执行动态pl/sql块
你可以使用execute immediate语句来执行匿名pl/sql块，下面是个例子
  
  
   
    
   
   Example 8-4 Event Dispatching with Static SQL
   
   CREATE OR REPLACE PROCEDURE event_handler_1
  (p_handle NUMBER) 
IS
BEGIN   -- process event 1  RETURN; END;/CREATE OR REPLACE PROCEDURE event_handler_2
  (p_handle NUMBER)
IS
BEGIN   -- process event 2  RETURN; END;/CREATE OR REPLACE PROCEDURE event_handler_3
  (p_handle NUMBER)
IS
BEGIN   -- process event 3
  RETURN; 
END;
/

CREATE OR REPLACE PROCEDURE event_dispatcher
  (p_event_num NUMBER, p_handle NUMBER)
IS
BEGIN
  IF (p_event_num = 1) THEN
    EVENT_HANDLER_1(p_handle);
  ELSIF (p_event_num = 2) THEN
    EVENT_HANDLER_2(p_handle);
  ELSIF (p_event_num = 3) THEN
    EVENT_HANDLER_3(p_handle);
  END IF;
END;
/


  
  
 By using native dynamic SQL, you can write a smaller, more flexible event dispatcher as shown in the following example.
  
  
   
    
   
   Example 8-5 Event Dispatching with Native Dynamic SQL
   
   CREATE OR REPLACE PROCEDURE event_dispatcher
   (p_event_num NUMBER, p_handle NUMBER) 
IS
BEGIN
  EXECUTE IMMEDIATE
    'BEGIN
       EVENT_HANDLER_' || TO_CHAR(p_event_num) || '(:1);
     END;'
  USING p_handle;
END;
/
   
   在本地动态sql和dbms_sql包中的选择
   
   oracle数据库提供2个方法用来执行动态sql：本地动态sql和dbms_sql包。
   
   本地动态sql能让你在pl/sql代码中放入动态sql，这些动态语句包含
   
   查询和dml语句
   
   pl/sql匿名块
   
   ddl语句
   
   事务控制语句
   
   会话控制语句
   
   处理大多数的本地动态sql语句，使用execute immediate语句，处理多行select语句，使用open-for,fetch和close语句。
   
   dbms_sql包提供了api来执行动态sql语句。例如，dbms_sql包包含了存储过程来做下面的事情：
   
   1打开游标
   
   2解析游标
   
   3提供绑定
   
   本地动态sql相比dbms_sql包有下面的优点：
   
   1本地动态sql很容易使用
   
   2本地动态sql比dbms_sql快
   
   3支持用户定义类型
   
   4支持获取数据到records
   
   使用的一个比较
   
       
    DBMS_SQL
 Package Native Dynamic SQL
CREATE OR REPLACE PROCEDURE insert_into_table 
  (p_table_name VARCHAR2, p_deptnumber NUMBER, 
   p_deptname VARCHAR2,   p_location    VARCHAR2) 
IS
  v_cur_hdl         INTEGER;
  v_stmt_str        VARCHAR2(200);
  v_rows_processed  BINARY_INTEGER;

BEGIN
  v_stmt_str := 'INSERT INTO ' 
  || p_table_name 
  || ' VALUES (:g_deptno, :g_dname, :g_loc)';
  v_cur_hdl := DBMS_SQL.OPEN_CURSOR;
  DBMS_SQL.PARSE(v_cur_hdl, v_stmt_str, 
                 DBMS_SQL.NATIVE);
  DBMS_SQL.BIND_VARIABLE (v_cur_hdl,':g_deptno',  
                          p_deptnumber);
  DBMS_SQL.BIND_VARIABLE (v_cur_hdl, ':g_dname', 
                          p_deptname);
  DBMS_SQL.BIND_VARIABLE (v_cur_hdl, ':g_loc', 
                          p_location);
  v_rows_processed := DBMS_SQL.EXECUTE(v_cur_hdl);
  DBMS_SQL.CLOSE_CURSOR(v_cur_hdl);
END;
/
CREATE OR REPLACE PROCEDURE insert_into_table 
 (p_table_name VARCHAR2, p_deptnumber NUMBER,
  p_deptname VARCHAR2,   p_location VARCHAR2) 
IS
  v_stmt_str    VARCHAR2(200);



BEGIN
  v_stmt_str := 'INSERT INTO ' 
  || p_table_name 
  || ' VALUES (:g_deptno, :g_dname, :g_loc)';
  EXECUTE IMMEDIATE v_stmt_str 
    USING p_deptnumber, p_deptname, 
          p_location;








END;
/

   
   在动态sql中使用绑定变量
   
   Example 8-11 Using Native Dynamic SQL with Bind Variables
CREATE OR REPLACE PROCEDURE del_dept
  (p_department_id  departments.department_id%TYPE)
IS
BEGIN
  EXECUTE IMMEDIATE 'DELETE FROM departments WHERE department_id = :1' 
    USING p_department_id;
END;
/
SHOW ERRORS
动态sql，将数据取出放到记录中
DECLARE
  TYPE EmpCurTyp  IS REF CURSOR;
  v_emp_cursor    EmpCurTyp;
  emp_record      emp%ROWTYPE;
  v_stmt_str      VARCHAR2(200);
  v_e_job         emp.job%TYPE;
BEGIN
  v_stmt_str := 'SELECT * FROM emp WHERE job = :1';
  -- in a multi-row query
  OPEN v_emp_cursor FOR v_stmt_str USING 'MANAGER';
    LOOP
      FETCH v_emp_cursor INTO emp_record;
      EXIT WHEN v_emp_cursor%NOTFOUND;
    END LOOP;
  CLOSE v_emp_cursor;
  -- in a single-row query
  EXECUTE IMMEDIATE v_stmt_str INTO emp_record USING 'PRESIDENT';
END;
/
dbms_sql相比本地动态sql，有下面的优势
dbms_sql支持客户端程序
dbms_sql支持不确定的输入和输出
dbms_sql支持大于32KB的sql语句
dbms_sql让你重用sql语句

编写动态sql