PL/SQ - 动态SQL

动态SQL是指在运行时刻才构建执行的SQL语句
静态SQL指的是在代码编译时刻就已经包含在代码中的那些已经充分明确的固定的SQL语句
动态PL/SQL 是指整个PL/SQL 代码块都是动态构建，然后再编译执行的

在PL/SQL中用静态SQL只能执行查询以及DML语句
如果想创建一个表或删除一个索引，动态SQL可以

可以使用DBMS_SQL包执行动态SQL
一种执行动态构建的SQL语句的方法：原生态SQL(DNS)

======================================

DNS语句：简单
与DBMS_SQL不同，后者需要编写一打程序代码，以及很多规则要遵守
DNS是通过一个新的语句无缝集成到PL/SQL语言中
EXECUTE IMMEDIATE
这个语句会立即执行一个SQL语句，然后增强已有的OPEN FOR语句功能，我们就可以执行多行的动态查询

-- SQL_string : 包含了SQL语句或者PL/SQL代码块的字符串表达式
-- define_variable ：用于接收查询中某一列值的变量
-- record ： 用户自定义类型或者基于%ROWTYPE的记录，可以接收查询返回的一整行值
-- bind_argument ： 一个表达式，表达式的值将传给SQL语句或PL/SQL块
-- 也可以是一个标示符，可以用作PL/SQL块中调用的函数或过程的输入和或输出变量
-- INTO子句 ： 用于单行的查询，对于查询结果每一列的值，
--                 提供一个单独的变量或者兼容的记录类型中的一个字段
-- USING子句 ： 可以给SQL字符串提供绑定参数，可以用于动态SQL和动态PL/SQL
--                 缺省模式是IN，也是SQL语言唯一可用的参数模式
EXECUTE IMMEDIATE SQL_string
    [INTO {define_variable[, define_variable]... | record}]
    [USING [IN | OUT | IN OUT ] bind_argument
        [, [IN | OUT | IN OUT] bind_argument]...];
-- 当一个语句执行时
-- 运行引擎会把SQL语句中每一个占位符，用USING子句中对应的绑定参数替换

-- 创建一个索引
BEGIN
    EXECUTE IMMEDIATE 'CREATE INDEX emp_u_1 ON employees (last_name)';
END;

-- 创建一个可以执行任意DDL语句的存储过程
PROCEDURE exec_DDL (ddl_string IN VARCHAR2)
IS
BEGIN
    EXECUTE IMMEDIATE ddl_string;
END;

BEGIN
    exec_DDL('CREATE INDEX emp_u_1 ON employees (last_name)');
END;

-- 获得任意一个表中符合指定WHERE条件的记录数量
FUNCTION tabcount (table_in IN VARCHAR2)
    RETURN PLS_INTEGER
IS
    l_query VARCHAR2(32767) := 'SELECT COUNT(*) FROM ' || table_in;
    l_return PLS_INTEGER;
BEGIN
    EXECUTE IMMEDIATE l_query INTO l_return;
    RETURN l_return;
END;

BEGIN
    IF tabcount('employees') > 100
    THEN
        DBMS_OUTPUT.PUT_LINE('WE ARE GROWING FAST!');
    END IF;
END;

-- 更新employees表中任何一个数字型的列的值；返回被更新的记录条数
FUNCTION updVal(
    col IN VARCHAR2,
    val IN NUMBER,
    start_in IN DATE,
    end_in IN DATE
    )RETURN PLS_INTEGER
IS 
BEGIN
    -- 对UPDATE语句的分析结束后，PL/SQL引擎就会把几个占位符:the_value, :lo, :hi
    -- 用USING子句中的值替换
    EXECUTE IMMEDIATE
        'UPDATE employees SET' || col || '= :the_value 
            WHERE hire_date BETWEEN :lo AND :hi'
        USING val, start_in, end_in
    RETURN SQL%ROWCOUNT;
END;

-- 每周的每天上午9:00，都运行一个不同的存储过程DAYNAME_set_schedule
-- 每个程序都有四个参数：我们传入employee_id和当日第一次会议的时间
-- 返回员工的名字已经当日安排的约会的数量
PROCEDURE run_9am_procedure(
    id_in IN employees.employee_id%TYPE,
    hour_in IN INTEGER)
IS
    v_apptCount INTEGER;
    v_name VARCHAR2(100);
BEGIN
    EXECUTE IMMEDIATE
        'BEGIN' || TO_CHAR(SYSDATE, 'DAY') || 
        '_set_schedule(:id, :hour, :name, :appts); END;'
    USING IN id_in, IN hour_in, OUT v_name, OUT v_apptCount;
    
    DBMS_OUTPUT.PUT_LINE('Employee ' || v_name || ' has ' || v_apptCount ||
        ' appointments on ' || TO_CHAR(SYSDATE));
END;

 

---

--OPEN FOR语句实际上并不是为了支持DNS，第一次出现在ORACLE 7数据库中是为了支持游标变量；
--现在它以一种极其优雅的方式实现了多行的动态查询。

-- 使用DBMS_SQL，经历：分析、绑定、定义每个列、执行、获取，提取
-- 原生动态SQL，Oracle采用一个已经有的特性和语法：游标变量-并进行了扩展

-- cursor_variable : 弱类型的游标变量
-- :host_cursor_variable ： 一个PL/SQL宿主环境中声明的游标变量
-- SQL_string ： 包含将要动态执行的SQL_string语句
-- USING 子句 : 和EXECUTE IMMEDIATE语句中遵守相同的规则
OPEN {cursor_variable | :host_cursor_variable} FOR SQL_string
    [USING bind_argument [, bind_argument]...];


PROCEDURE show_parts_inventory(
    parts_table IN VARCHAR2,
    where_in IN VARCHAR2)
IS
    TYPE query_curtype IS REF CURSOR;
    dyncur query_curtype;
BEGIN
    OPEN dyncur FOR
        'SELECT * FROM ' || parts_table || ' WHERE ' || where_in;
    ...
-- 当我们执行一个OPEN FOR语句时，PL/SQL引擎会做如下这些事情
--    1. 用一个游标变量关联查询字符串中的查询语句
--    2. 对绑定参数求值，然后用这些值替换查询字符串中的占位符
--    3. 执行查询
--    4. 识别出结果集
--    5. 把游标位置置于结果集的第一行
--    6. 把已处理行计数器归0，这个计数器也就是%ROWCOUNT返回的值

-- NOTE: 查询语句中任何绑定参数，都是在游标变量被打开时才求值的

 

 

-- 重复的占位符
-- 当我们执行一个动态SQL字符串，必须为每一个占位符都提供一个参数，即便这些占位符是重复的
PROCEDURE updnumval(
    col_in IN VARCHAR2,
    start_in IN DATE, end_in IN DATE,
    val_in IN NUMBER)
IS
    dml_str VARCHAR2 (32767) :=
        'UPDATE emp SET ' || col_in || ' = :val 
        WHERE hiredate BETWEEN :lodate AND :hiredate AND :val IS NOT NULL';
BEGIN
    EXECUTE IMMEDIATE dml_str
    USING val_in, start_in, end_in, val_in;
END;
-- 如果我们执行的是一个动态PL/SQL块，我们必须为每一个唯一占位符提供一个参数
PROCEDURE updnumval(
    col_in IN VARCHAR2,
    start_in IN DATE, end_in IN DATE,
    val_in IN NUMBER)
IS
    dml_str VARCHAR2 (32767) :=
        'BEGIN 
            UPDATE emp SET ' || col_in || ' = :val 
            WHERE hiredate BETWEEN :lodate AND :hiredate AND :val IS NOT NULL;
        END;';
BEGIN
    EXECUTE IMMEDIATE dml_str
    USING val_in, start_in, end_in;
END;

 

---

 

 

-- 为国民健康管理公司Health$.Com开发一个内部管理系统。减少成本
-- 系统采用了分布式运行的，为下属于Health$.Com的每个营利性医院创建
-- 一套单独的表来维护的客户信息

-- 定义一个对象类型person和VARRY类型preexisting_conditions
CREATE OR REPLACE TYPE Person AS OBJECT(
    name VARCHAR2(50), dob DATE, income NUMBER);
CREATE OR REPLACE TYPE preexisting_conditions IS TABLE OF VARCHAR2(25);

-- 创建一个包来管理关键健康信息-Health$.Com实现利润的最大化所需的信息
PACKAGE health$
AS 
    PROCEDURE setup_new_hospital (hosp_name IN VARCHAR2);
    
    PROCEDURE add_profit_source(
        host_name IN VARCHAR2,
        pers IN Person,
        cond IN preexisting_conditions);
    
    PROCEDURE minimize_risk(
        host_name IN VARCHAR2,
        min_income IN NUMBER := 100000,
        max_preexist_cond IN INTEGER := 0);
        
    PROCEDURE show_profit_conters(hosp_name VARCHAR2);
END health$;

-- 新建一个医院，为这个医院创建一个表
FUNCTION tabname(hosp_name IN VARCHAR2)
    IS
BEGIN
    RETURN hosp_name || '_profit_center';
END;

PROCEDURE setup_new_hospital(hosp_name IN VARCHAR2)
    IS
BEGIN
    EXECUTE IMMEDIATE
        'CREATE TABLE' || tabname(hosp_name) || '(pers Person, 
        cond preexisting_conditions) NESTED TABLE cond STORE AS cond_st';
END;

-- 为医院添加一个“利润来源”（其实就是患者），包括既往病史
PROCEDURE add_profit_source(
        host_name IN VARCHAR2,
        pers IN Person,
        cond IN preexisting_conditions)
    IS
BEGIN
    EXECUTE IMMEDIATE
        'INSERT INTO' ||　tabname(hosp_name) || 
        '    VALUES (:revenue_generator, :revenue_inhibitors)'
    USING pers, cond;
END;

-- 对象和集合的使用都是透明的，可以插入数字和日期这样的标量类型
-- 语法和代码都是完全一样

-- 删除那些有太多病史或收入太少的患者，避免触到机构的营利底线
PROCEDURE minimize_risk(
        host_name IN VARCHAR2,
        min_income IN NUMBER := 100000,
        max_preexist_cond IN INTEGER := 1)
    IS
        cv RefCurTyp;
        human Person;
        known_bugs preexisting_conditions;
        v_table VARCHAR2(30) := tabname(hosp_name);
        v_rowid ROWID;
BEGIN
    /*Find all rows with more than the specified number of
        preconditions and deny them coverage.*/
    OPEN cv FOR
        'SELCET ROWID, pers, cond FROM ' || 
        v_table || ' alias WHERE (SELECT COUNT(*) FROM TABLE (alias.cond)) > '
        || max_preexist_cond || 'OR alias.pers.income < ' || min_income;
    LOOP
        FETCH cv INTO v_rowid, human, known_bugs;
        EXIT WHEN cv%NOTFOUND;
        EXECUTE IMMEDIATE
            'DELETE FROM ' || v_table || ' WHERE ROWID = :rid'
        USING v_rowid;
    END LOOP;
    CLOSE cv;
END;