本文内容
- 静态 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;
/
