Oracle 11g Release 1 (11.1) PL/SQL_了解静态和动态 SQL

http://docs.oracle.com/cd/B28359_01/appdev.111/b28370/static.htm#BABGEDAE

http://docs.oracle.com/cd/B28359_01/appdev.111/b28370/dynamic.htm#CACDDACH

 

本文内容

• 静态 SQL
• 动态 SQL

 

静态 SQL

静态 SQL 是属于 PL/SQL 语言的 SQL。也就是：

• 除了解释执行计划的，数据操作语句（Data Manipulation Language，DML）
• 事务控制（Transaction Control Language，TCL）语句
• SQL 函数
• SQL 伪列
• SQL 运算符

静态 SQL 符合目前 ANSI/ISO SQL 标准。

示例 1：演示用 PL/SQL 操作数据

若操作数据库的数据，则无需任何特别的符号，你可以直接在 PL/SQL 程序里包含 DML 操作，如 INSERT、UPDATE 和 DELETE 语句。你也可以直接在 PL/SQL 程序里包含 COMMIT 语句。

CREATE TABLE employees_temp
  AS SELECT employee_id, first_name, last_name
  FROM employees;
DECLARE
  emp_id          employees_temp.employee_id%TYPE;
  emp_first_name  employees_temp.first_name%TYPE;
  emp_last_name   employees_temp.last_name%TYPE;
BEGIN
   INSERT INTO employees_temp VALUES(299, 'Bob', 'Henry');
   UPDATE employees_temp
     SET first_name = 'Robert' WHERE employee_id = 299;
   DELETE FROM employees_temp WHERE employee_id = 299
     RETURNING first_name, last_name
       INTO emp_first_name, emp_last_name;
   COMMIT;
   DBMS_OUTPUT.PUT_LINE( emp_first_name  || ' ' || emp_last_name);
END;
/

示例 2：演示在 PL/SQL 调用 SQL 函数——COUNT 函数

DECLARE
  job_count NUMBER;
  emp_count NUMBER;
BEGIN
  SELECT COUNT(DISTINCT job_id)
    INTO job_count
      FROM employees;

  SELECT COUNT(*)
    INTO emp_count
      FROM employees;
END;
/

示例 3：演示使用伪列——ROWNUM

CREATE TABLE employees_temp AS SELECT * FROM employees;
DECLARE
   CURSOR c1 IS SELECT employee_id, salary FROM employees_temp
      WHERE salary > 2000 AND ROWNUM <= 10;  -- 10 arbitrary rows
   CURSOR c2 IS SELECT * FROM
     (SELECT employee_id, salary FROM employees_temp
       WHERE salary > 2000 ORDER BY salary DESC)
     WHERE ROWNUM < 5;  -- first 5 rows, in sorted order
BEGIN
-- Each row gets assigned a different number
  UPDATE employees_temp SET employee_id = ROWNUM;
END;
/

 

动态 SQL

动态 SQL 是用编程的方式在运行时创建并执行 SQL 语句。这在编写通用或灵活的程序，像 ad hoc 查询系统，或在编写必须执行 DLL 语句的程序，或是在编译期间不确定整个 SQL 文本、数量，以及输入输出变量的数据类型时，很有用。

PL/SQL 提供两种方式编写动态 SQL：

• 本地动态 SQL（Native dynamic SQL），创建并执行动态 SQL 语句是 PL/SQL 语言的特点
• DBMS_SQL 包是创建、执行和描述动态 SQL 语言的 API

Native dynamic SQL 代码比其等价的使用 DBMS_SQL package 代码更容易读写，执行也快。特别是，当它通过编译器优化后。然而，若编写 Native dynamic SQL 代码，你必须在编译期间知道输入和输出变量的数据类型和数量。否则，只能使用 DBMS_SQL package。

当你同时需要 Native dynamic SQL 和 DBMS_SQL package 时，你可以通过 DBMS_SQL.TO_REFCURSOR 函数和 DBMS_SQL.TO_CURSOR_NUMBER 函数在它们之间进行切换。

何时使用静态 SQL 或动态 SQL

在 PL/SQL，下面情况需要动态 SQL：

• 编译期间无法确定 SQL 文本。例如，SELECT 语句包含一个事先不确定的标识符，如表名，或是 WHERE 子句中的一部分在编译期间不确定。
• 静态 SQL不支持的。也就是不能用静态 SQL 创建的任何 SQL。

若不需要动态 SQL，则使用静态 SQL 的好处如下：

• 成功的编译会验证静态 SQL 引用可靠的数据库对象，以及访问这些对象的必需权限。
• 成功的编译会创建模式对象依赖。

示例 4：演示从动态 PL/SQL 块调用一个子程序

-- Subprogram that dynamic PL/SQL block invokes:
CREATE PROCEDURE create_dept ( deptid IN OUT NUMBER,
                               dname  IN VARCHAR2,
                               mgrid  IN NUMBER,
                               locid  IN NUMBER
                             ) AS
BEGIN
deptid := departments_seq.NEXTVAL;
  INSERT INTO departments 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
 -- Dynamic PL/SQL block invokes subprogram:
  plsql_block := 'BEGIN create_dept(:a, :b, :c, :d); END;';

 /* Specify bind arguments 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;
/