PL/SQ - 动态PL/SQL

• 动态字符串必须是一个有效的PL/SQL块。这个块必须以DECLARE或者BEGIN关键字开始，用END关键字和分号结束。如果字符串不是以分号结尾的，是不会被识别成PL/SQL块。
• 在动态块中，我们只能访问属于全局作用范围的PL/SQL代码元素。动态PL/SQL块是局部包围块的作用范围之外执行。
• 在动态PL/SQL块中抛出的错误可以在运行EXECUTE IMMEDIATE语句的局部块中捕获并处理。

-- 执行动态PL/SQL的小工具
PROCEDURE dynPLSQL(blk IN VARCHAR2)
IS
BEGIN
    EXECUTE IMMEDIATE
        'BEGIN ' || RTRIM(blk, ';') || '; END;';
END;

--动态构建并执行的PL/SQL块并不是一个内置块，相反，它是按照一个被当前调用的过程或函数来处理的。
-- 因此当前块中的局部变量不会被动态PL/SQL块识别。
-- 我们只能引用全局定义的程序和数据结构。

-- 动态块是在调用块的上下文中执行的。在调用块中有一个异常处理部分
-- 可以处理动态块抛出的异常。

-- 芝加哥的保险公司：一个程序相当大，还在不断变大，就要大得无法编译了
-- 每一个行号都对应着保险政策中的一个细节
-- 对每一个行号，都有一个“process line”程序处理具体细节
-- 随着保险公司不断在政策中加入越来越多的额外条款，程序就变得越来越大
PROCEDURE process_line(line IN INTEGER)
IS
BEGIN
    IF line = 1 THEN process_line1;
    ELSIF line = 2 THEN process_line2;
    ...
    ELSIF line = 514 THEN process_line514;
    ...
    ELSIF line = 2057 THEN process_line2057;
    END IF;
END;

-- 上千行的代码现在缩成只有一个可执行语句了
PROCEDURE process_line (line IN INTEGER)
IS
BEGIN
    EXECUTE IMMEDIATE
        'BEGIN process_line' || line || '; END;'
END;

-- 作用于调用者的Schema，而不是定义者的Schema
PROCEDURE exec_DDL(ddl_string IN VARCHAR2)
    AUTHID CURRENT_USER
IS 
BEGIN
    EXECUTE IMMEDIATE ddl_string;
END;

在编写动态SQL时采取一些预防措施：

• 调用EXECUTE IMMEDIATE和OPEN FOR时，总是带上一个异常处理单元
• 在每个异常处理句柄里，记录下并/或者显示错误发生时的错误消息以及SQL语句
• 可以考虑在这些语句之前加上一个“跟踪机制”，就很容易对动态SQL的构造和执行进行观察

PROCEDURE exec_ddl(ddl_string IN VARCHAR2)
    AUTHID CURRENT_USER
IS 
BEGIN
    EXECUTE IMMEDIATE ddl_string;
EXCEPTION
    WHEN OTHERS
    THEN
        DBMS_OUTPUT.PUT_LINE('Dynamic SQL Failure: ' || DBMS_UTILITY.FORMAT_ERROR_STACK);
        DBMS_OUTPUT.PUT_LINE(' on statement: "' || ddl_string || '"');
        RAISE;
END;

绑定包括：拼接 和 绑定
绑定包括：使用占位符和USING子句
拼接：把值直接添加到SQL字符串而缩短了处理过程

只要有可以能使用绑定的方式，就不要依赖于拼接：

• 绑定通常更快速
• 绑定的编写和维护都很容易
• 绑定有助于避免隐式转换
• 绑定避免了发生代码注入的可能性

-- 代码注入，也叫SQL注入，可以严重的威胁程序的安全
-- 动态注入也开启了最大的可能性
PROCEDURE get_rows(
    table_in IN VARCHAR2, where_in IN VARCHAR2)
IS 
BEGIN
    EXECUTE IMMEDIATE
        'DECLARE l_row ' || table_in || '%ROWTYPE; 
        BEGIN
            SELECT * INTO l_row FROM ' || table_in  ||
            ' WHERE ' || where_in || '; END;';
END get_rows;

BEGIN
    --这个程序可以执行作为动态字符串传进去的任何语句
    -- 使用UTL_FILE获取任何一个系统文件的内容，随意创建或删除一个表或对象
    -- 具体做的只受限于属主Schema所具有的权限
    get_rows('EMPLOYEE',
        'employee_id = 7369; EXECUTE IMMEDAITE "CREATE PROCEDURE backdoor
        (str VARCHAR2) AS BEGIN EXECUTE IMMEDIATE str; END;"');
END;
/