• 关于绑定变量、关于占位符

    对于绑定变量、对于占位符其中标红加粗的需要和《剑破冰山》中绑定变量一章一起阅读。


    在PLSQL中使用EXECUTE IMMEDIATE语句处理动态SQL语句。

    语法如下：
    EXECUTE IMMEDIATE dynamic_string
    [INTO {define_variable[, define_variable]... | record}]
    [USING [IN | OUT | IN OUT] bind_argument
        [, [IN | OUT | IN OUT] bind_argument]...]
    [{RETURNING | RETURN} INTO bind_argument[, bind_argument]...];

    dynamic_string是代表一条SQL语句或一个PL/SQL块的字符串表达式，
    define_variable是用于存放被选出的字段值的变量，
    record是用户定义或%ROWTYPE类型的记录，用来存放被选出的行记录。
    输入bind_argument参数是一个表达式，它的值将被传入(IN模式)或传出(OUT模式)或先传入再传出(IN OUT模式)到动态SQL语句或是PL/SQL块中。一个输出bind_argument参数就是一个能保存动态SQL返回值的变量。

    除了多行查询外，动态字符串可以包含任何SQL语句(不含终结符)或PL/SQL块(含终结符)。
    字符串中可以包括用于参数绑定的占位符。
    但是，不可以使用绑定参数为动态SQL传递模式对象。

    在用于单行查询时，INTO子句要指明用于存放检索值的变量或记录。
    对于查询检索出来的每一个值，INTO子句中都必须有一个与之对应的、类型兼容的变量或字段。
    在用于DML操作时，RETURNING INTO子句要指明用于存放返回值的变量或记录。
    对于DML语句返回的每一个值，INTO子句中都必须有一个与之对应的、类型兼容的变量或字段。

    我们可以把所有的绑定参数放到USING子句中。默认的参数模式是IN。
    对于含有RETURNING子句的DML语句来说，我们可以把OUT参数放到RETURNING INTO之后，并且不用指定它们的参数模式，因为默认就是OUT。
    如果我们既使用了USING又使用RETURNING INTO，那么，USING子句中就只能包含IN模式的参数了。

    运行时，动态字符串中的绑定参数会替换相对应的占位符。所以，每个占位符必须与USING子句和/或RETURNING INTO子句中的一个绑定参数对应。我们可以使用数字、字符和字符串作为绑定参数，但不能使用布尔类型(TRUE，FALSE和NULL)。要把空值传递给动态字符串，我们就必须使用工作区。

    动态SQL支持所有的SQL类型。所以，定义变量和绑定变量都可以是集合、LOB，对象类型实例和引用。
    作为一项规则，动态SQL是不支持PL/SQL特有的类型的。这样，它就不能使用布尔型或索引表。

    我们可以重复为绑定变量指定新值执行动态SQL语句。但是，每次都会消耗很多资源，因为EXECUTE IMMEDIATE在每次执行之前都需要对动态字符串进行预处理。

    1、动态SQL实例
    下面的PL/SQL块包含了几个动态SQL的例子：

    [sql] view plaincopy
    1. DECLARE  
    2.   sql_stmt      VARCHAR2(200);  
    3.   plsql_block   VARCHAR2(500);  
    4.   emp_id        NUMBER(4)     := 7566;  
    5.   salary        NUMBER(7, 2);  
    6.   dept_id       NUMBER(2)     := 50;  
    7.   dept_name     VARCHAR2(14)  := 'PERSONNEL';  
    8.   location      VARCHAR2(13)  := 'DALLAS';  
    9.   emp_rec       emp%ROWTYPE;  
    10. BEGIN  
    11.   EXECUTE IMMEDIATE 'CREATE TABLE bonus (id NUMBER, amt NUMBER)';  
    12.     
    13.   sql_stmt := 'INSERT INTO dept VALUES (:1, :2, :3)';  
    14.   EXECUTE IMMEDIATE sql_stmt  
    15.               USING dept_id, dept_name, location;  sql_stmt := 'SELECT * FROM emp WHERE empno = :id';  
    16.   EXECUTE IMMEDIATE sql_stmt  
    17.               INTO emp_rec  
    18.               USING emp_id;  plsql_block := 'BEGIN emp_pkg.raise_salary(:id, :amt); END;';  
    19.   EXECUTE IMMEDIATE plsql_block  
    20.               USING 7788, 500;  sql_stmt := 'UPDATE emp SET sal = 2000 WHERE empno = :1 RETURNING sal INTO :2';  
    21.   EXECUTE IMMEDIATE sql_stmt  
    22.               USING emp_id  
    23.               RETURNING INTO salary;  EXECUTE IMMEDIATE 'DELETE FROM dept WHERE deptno = :num'  
    24.               USING dept_id;  EXECUTE IMMEDIATE 'ALTER SESSION SET SQL_TRACE TRUE';  
    25. END;  

     

    下例中，过程接受一个数据表名(如"emp")和一个可选的WHERE子句(如"sal > 2000")。
    如果我们没有提供WHERE条件，程序会删除指定表中所有的行，否则就会按照给定的条件删除行：

    [sql] view plaincopy
    1. CREATE OR REPLACE PROCEDURE delete_rows(  
    2.   table_name   IN   VARCHAR2,  
    3.   condition    IN   VARCHAR2 DEFAULT NULL  
    4. ) AS  
    5.   where_clause   VARCHAR2(100) := ' WHERE ' || condition;  
    6. BEGIN  
    7.   IF condition IS NULL THEN  
    8.     where_clause    := NULL;  
    9.   END IF;  
    10.   
    11.   EXECUTE IMMEDIATE 'DELETE FROM ' || table_name || where_clause;  
    12. END;  


    2、USING子句的向后兼容

    当动态INSERT、UPDATE或DELETE语句有一个RETURNING子句时，
    输出绑定参数可以放到RETURNING INTO或USING子句的后面。
    在新的应用程序中要使用RETURNING INTO，而旧的应用程序可以继续使用USING，如下例：

    [sql] view plaincopy
    1. DECLARE  
    2.   sql_stmt   VARCHAR2(200);  
    3.   my_empno   NUMBER(4)     := 7902;  
    4.   my_ename   VARCHAR2(10);  
    5.   my_job     VARCHAR2(9);  
    6.   my_sal     NUMBER(7, 2)  := 3250.00;  
    7. BEGIN  
    8.   sql_stmt := 'UPDATE emp SET sal = :1 WHERE empno = :2 RETURNING ename, job INTO :3, :4';  
    9.   
    10.   /* Bind returned values through USING clause. */  
    11.   EXECUTE IMMEDIATE sql_stmt  
    12.               USING my_sal, my_empno, OUT my_ename, OUT my_job;  
    13.   dbms_output.put_line(my_ename || ',' || my_job);  
    14.     
    15.   /* Bind returned values through RETURNING INTO clause. */  
    16.   EXECUTE IMMEDIATE sql_stmt  
    17.               USING my_sal, my_empno  
    18.               RETURNING INTO my_ename, my_job;  
    19.   dbms_output.put_line(my_ename || ',' || my_job);  
    20. END;  


    3、指定参数模式

    使用USING子句时，我们不需要为输入参数指定模式，因为默认的就是IN；
    而RETURNING INTO子句中我们是不可以指定输出参数的模式的，因为定义中它就是OUT模式。
    在适当的时候，我们必须为绑定参数指定OUT或IN OUT模式。例如，假定我们想调用下面的过程：

    [sql] view plaincopy
    1. CREATE PROCEDURE create_dept(  
    2.   deptno   IN OUT   NUMBER,  
    3.   dname    IN       VARCHAR2,  
    4.   loc      IN       VARCHAR2  
    5. ) AS  
    6. BEGIN  
    7.   SELECT deptno_seq.NEXTVAL INTO deptno FROM DUAL;  
    8.   INSERT INTO dept VALUES (deptno, dname, loc);  
    9. END;  

    要从动态PL/SQL块调用过程，就必须为与形参关联的绑定参数指定IN OUT模式，如下：

    [sql] view plaincopy
    1. DECLARE  
    2.   plsql_block   VARCHAR2(500);  
    3.   new_deptno    NUMBER(2);  
    4.   new_dname     VARCHAR2(14)  := 'ADVERTISING';  
    5.   new_loc       VARCHAR2(13)  := 'NEW YORK';  
    6. BEGIN  
    7.   plsql_block := 'BEGIN create_dept(:a, :b, :c); END;';  
    8.   
    9.   EXECUTE IMMEDIATE plsql_block  
    10.               USING IN OUT new_deptno, new_dname, new_loc;  
    11.   
    12.   dbms_output.put_line(new_deptno);  
    13. END;  


    4、打开游标变量OPEN-FOR
    OPEN {cursor_variable | :host_cursor_variable} FOR dynamic_string
    [USING bind_argument[, bind_argument]...];
    OPEN-FOR的动态形式有一个可选的USING子句。
    在运行时，USING子句中的绑定变量可以替换动态SELECT语句中相对应的占位符

    5、使用批量动态SQL

    批量绑定能减少PL/SQL和SQL引擎之间的切换，改善性能。
    使用下面的命令、子句和游标属性，我们就能构建批量绑定的SQL语句，然后在运行时动态地执行：

    BULK FETCH 语句
    BULK EXECUTE IMMEDIATE 语句
    FORALL 语句
    COLLECT INTO 子句
    RETURNING INTO 子句
    %BULK_ROWCOUNT 游标属性


    动态批量绑定语法

    批量绑定能让Oracle把SQL语句中的一个变量与一个集合相绑定。
    集合类型可以是任何PL/SQL集合类型(索引表、嵌套表或变长数组)。
    但是，集合元素必须是SQL数据类型，如CHAR、DATE或NUMBER。
    有三种语句支持动态批量绑定：EXECUTE IMMEDIATE、FETCH和FOR ALL。

    批量EXECUTE IMMEDIATE这个语句能让我们把变量或OUT绑定参数批量绑定到一个动态的SQL语句，语法如下：
    EXECUTE IMMEDIATE dynamic_string
      [[BULK COLLECT] INTO define_variable[, define_variable ...]]
      [USING bind_argument[, bind_argument ...]]
      [{RETURNING | RETURN}
      BULK COLLECT INTO bind_argument[, bind_argument ...]];

    在动态多行查询中，我们可以使用BULK COLLECT INTO子句来绑定变量。
    在返回多行结果的动态INSERT、UPDATE或DELETE语句中，我们可以使用RETURNING BULK COLLECT INTO子句来批量绑定输出变量。

    批量FETCH这个语句能让我们从动态游标中取得数据，就跟从静态游标中取得的方法是一样的。语法如下：
    FETCH dynamic_cursor
      BULK COLLECT INTO define_variable[, define_variable ...];
    *如果在BULK COLLECT INTO中的变量个数超过查询的字段个数，Oracle就会产生错误。

    批量FORALL这个语句能让我们在动态SQL语句中批量绑定输入参数。
    此外，我们还可以在FORALL内部使用EXECUTE IMMEDIATE语句。语法如下：
    FORALL index IN lower bound..upper bound
      EXECUTE IMMEDIATE dynamic_string
      USING bind_argument | bind_argument(index)
        [, bind_argument | bind_argument(index)] ...
      [{RETURNING | RETURN} BULK COLLECT
        INTO bind_argument[, bind_argument ... ]];

    *动态字符串必须是一个INSERT、UPDATE或DELETE语句(不可以是SELECT语句)。


    例如

    [sql] view plaincopy
    1. DECLARE  
    2.   TYPE numlist IS TABLE OF NUMBER;  
    3.   TYPE namelist IS TABLE OF VARCHAR2(15);  
    4.   empcur   sys_refcursor;  
    5.   empnos   numlist;  
    6.   enames   namelist;  
    7.   sals     numlist;  
    8. BEGIN  
    9.   OPEN empcur FOR 'SELECT empno, ename FROM emp';  
    10.   FETCH empcur  
    11.     BULK COLLECT INTO empnos, enames;  
    12.   CLOSE empcur;  
    13.   
    14.   EXECUTE IMMEDIATE 'SELECT sal FROM emp'  
    15.     BULK COLLECT INTO sals;  
    16. END;  



    只有INSERT、UPDATE和DELETE语句才能拥有输出绑定参数。
    我们可以在EXECUTE IMMDIATE的BULK RETURNING INTO子句中进行绑定：

    [sql] view plaincopy
    1. DECLARE  
    2.   TYPE namelist IS TABLE OF VARCHAR2(15);  
    3.   enames      namelist;  
    4.   sal_amt   NUMBER       := 500;  
    5.   sql_stmt    VARCHAR(200);  
    6. BEGIN  
    7.   sql_stmt    := 'UPDATE emp SET sal = :1 RETURNING ename INTO :2';  
    8.   EXECUTE IMMEDIATE sql_stmt  
    9.               USING sal_amt  
    10.               RETURNING BULK COLLECT INTO enames;  
    11. END;  


    使用FORALL语句和USING子句，但这时的SQL语句不能是查询语句，如下例：

    [sql] view plaincopy
    1. DECLARE  
    2.   TYPE numlist IS TABLE OF NUMBER;  
    3.   TYPE namelist IS TABLE OF VARCHAR2(15);  
    4.   empnos   numlist;  
    5.   enames   namelist;  
    6. BEGIN  
    7.   empnos := numlist(7369, 7499, 7521);  
    8.   FORALL i IN 1 .. 3  
    9.     EXECUTE IMMEDIATE 'UPDATE emp SET sal = sal*2 WHERE empno = :1 RETURNING ename INTO :2'  
    10.                 USING empnos(i)  
    11.                 RETURNING BULK COLLECT INTO enames;  
    12. END;  


    6、动态SQL的技巧与陷阱

    1）使用绑定变量来改善性能
    我们可以使用绑定变量来改善性能，如下例所示，让Oracle为不同的emp_id值重用同一个游标。

    [sql] view plaincopy
    1. CREATE PROCEDURE fire_employee(emp_id NUMBER) AS  
    2. BEGIN  
    3.   EXECUTE IMMEDIATE 'DELETE FROM emp WHERE empno = :num'  
    4.               USING emp_id;  
    5. END;  


    2）让过程对任意模式对象起作用

    假设我们需要一个过程，让它接受数据表名，然后将指定的表从数据库中删除。
    我们可能会下面这样编写使用动态SQL的过程：

    [sql] view plaincopy
    1. CREATE PROCEDURE drop_table(table_name IN VARCHAR2) AS  
    2. BEGIN  
    3.   EXECUTE IMMEDIATE 'DROP TABLE :tab'  
    4.               USING table_name;  
    5. END;  


    但是，在运行的时候，这个过程会报一个表名无效的错误。
    原因是不可以使用绑定参数为动态SQL传递模式对象。
    解决方法是直接把参数嵌套到字符串中。把上面的EXECUTE IMMEDIATE语句修改一下：

    [sql] view plaincopy
    1. CREATE OR REPLACE PROCEDURE drop_table(table_name IN VARCHAR2) AS  
    2. BEGIN  
    3.   EXECUTE IMMEDIATE 'DROP TABLE ' || table_name;  
    4. END;  

    这样，我们就可以向动态SQL语句传递任意数据表名称了。


    3）使用重复占位符

    动态SQL语句中的占位符与USING子句中的绑定参数是位置关联的，而不是名称关联。
    所以，如果在SQL语句中同样的占位符出现两次或多次，
    那么，它的每次出现都必须与一个USING子句中的绑定参数相关联。
    例如下面的动态字符串：
    sql_stmt := 'INSERT INTO payroll VALUES (:x, :x, :y, :x)';
    我们可以为动态字符串编写对应的USING子句：
    EXECUTE IMMEDIATE sql_stmt USING a, a, b, a;

    但是，
    动态PL/SQL块中只有唯一的占位符才与USING子句中的绑定参数按位置对应。
    所以，如果一个占位符在PL/SQL块中出现两次或多次，
    那么所有这样相同的占位符都只与USING语句中的一个绑定参数相对应。
    比如下面的例子，第一个占位符(x)与第一个绑定参数(a)关联，第二个占位符(y)与第二个绑定参数(b)关联。

    [sql] view plaincopy
    1. DECLARE  
    2.   a   NUMBER := 4;  
    3.   b   NUMBER := 7;  
    4. BEGIN  
    5.   plsql_block := 'BEGIN calc_stats(:x, :x, :y, :x); END';  
    6.   EXECUTE IMMEDIATE plsql_block  
    7.               USING a, b;  
    8. END;  


    4）使用游标属性

    每个显式的游标都有四个属性：%FOUND、%ISOPEN、%NOTFOUND和%ROWCOUNT。它们都能返回与静态或动态SQL语句执行结果相关的有用信息。

    为处理SQL数据操作语句，Oracle会打开一个名为SQL的隐式游标。它的属性会返回最近一次执行的INSERT、UPDATE、DELETE或单行SELECT的相关信息。例如，下面函数就使用%ROWCOUNT返回从数据表中删除的行数：

    [sql] view plaincopy
    1. CREATE FUNCTION rows_deleted(table_name IN VARCHAR2, condition IN VARCHAR2)  
    2.   RETURN INTEGER AS  
    3. BEGIN  
    4.   EXECUTE IMMEDIATE 'DELETE FROM ' || table_name || ' WHERE ' || condition;  
    5.   RETURN SQL%ROWCOUNT;   -- return number of rows deleted  
    6. END;  


    5）传递空值
    下面，我们来为动态SQL传递空值，见下面的EXECUTE IMMEDIATE语句：
    EXECUTE IMMEDIATE 'UPDATE emp SET comm = :x' USING NULL;

    但是，这个语句会因为在USING子句中使用NULL而执行失败，
    因为USING语句中所传递的参数是不能为空的。
    所以，要想解决这个问题，直接使用字符串就可以了：

    [sql] view plaincopy
    1. DECLARE  
    2.   a_null   CHAR(1);   -- set to NULL automatically at run time  
    3. BEGIN  
    4.   EXECUTE IMMEDIATE 'UPDATE emp SET comm = :x'  
    5.               USING a_null;  
    6. END;  


    6)  远程操作

    如下例所示，PL/SQL子程序能够执行引用远程数据库对象的动态SQL语句：

    [sql] view plaincopy
    1. PROCEDURE delete_dept(db_link VARCHAR2, dept_id INTEGER) IS  
    2. BEGIN  
    3.   EXECUTE IMMEDIATE 'DELETE FROM dept@' || db_link || ' WHERE deptno = :num'  
    4.               USING dept_id;  
    5. END;  


    同样，远程过程调用(RPC)的目标也包括动态SQL语句。
    例如，假设下面返回数据表中记录个数的函数存放在Chicago的数据库上：

    [sql] view plaincopy
    1. CREATE FUNCTION row_count(tab_name VARCHAR2)  
    2.   RETURN INTEGER AS  
    3.   ROWS   INTEGER;  
    4. BEGIN  
    5.   EXECUTE IMMEDIATE 'SELECT COUNT(*) FROM ' || tab_name  
    6.                INTO ROWS;  
    7.   
    8.   RETURN ROWS;  
    9. END;  
    10.   
    11. --下面是一个从匿名SQL块调用远程函数的例子：   
    12.   
    13. DECLARE  
    14.   emp_count   INTEGER;  
    15. BEGIN  
    16.   emp_count    := row_count@chicago('emp');  
    17. END;  


    7) 使用调用者权限

    默认情况下，存储过程是使用定义者权限执行的，而不是调用者权限。
    这样的过程是绑定在它们所属的模式对象上的。假设下面用于删除数据库对象的过程存放在模式对象scott上：

    [sql] view plaincopy
    1. CREATE PROCEDURE drop_it(kind IN VARCHAR2, NAME IN VARCHAR2) AS  
    2. BEGIN  
    3.   EXECUTE IMMEDIATE 'DROP ' || kind || ' ' || NAME;  
    4. END;  


    我们把用户jones赋予允许他执行上面的存储过程的EXECUTE权限。
    当用户jones调用drop_it时，动态SQL就会使用用户scott的权限来执行语句：

    [sql] view plaincopy
    1. SQL> CALL scott.drop_it('TABLE', 'dept');  


    这由于数据表dept的前面并没有限定修饰词进行限制，语句执行时删除的就是scott上的数据表，而不是jones上的。

    但是，AUTHID子句可以让存储过程按它的调用者权限来执行，
    这样的存储过程就不会绑定在一个特定的schema对象上。例如下面的新版本drop_it就会按调用者权限执行：

    [sql] view plaincopy
    1. CREATE PROCEDURE drop_it(kind IN VARCHAR2, NAME IN VARCHAR2)  
    2. AUTHID CURRENT_USER AS  
    3. BEGIN  
    4.   EXECUTE IMMEDIATE 'DROP ' || kind || ' ' || NAME;  
    5. END;  


    8) 使用RESTRICT_REFERENCES

    从SQL语句调用函数时，我们必须要遵守一定的规则来避免负面印象。
    为了检测冲突，我们可以使用编译指示RESTRICT_REFERENCES。
    它能确保函数没有读和/或写数据表和/或打包变量。
    但是，如果函数体包含了动态INSERT、UPDATE或DELETE语句，
    那它就总与规则"write no database state" (WNDS)和"read no database state" (RNDS)相冲突。
    这是因为动态SQL语句是在运行时才被检验，而不是编译期。
    在一个EXECUTE IMMEDIATE语句中，只有INTO子句才能在编译期检验是否与RNDS冲突。

    9)  避免死锁

    有些情况下，执行DML语句会导致死锁。
    例如，下面的过程就能引起死锁，因为它尝试着删除自身。
    为了避免死锁，就不要用ALTER或DROP来操作正在使用的子程序或包。

    [sql] view plaincopy
    1. CREATE PROCEDURE calc_bonus (emp_id NUMBER) AS  
    2. BEGIN  
    3.   ...  
    4.   EXECUTE IMMEDIATE 'DROP PROCEDURE calc_bonus';  
  • 相关阅读:
    常用 Git 命令清单
    谈谈关于PHP的代码安全相关的一些致命知识
    九个Console命令，让 JS 调试更简单
    有趣的博客小工具
    利用fputcsv导出数据备份数据
    IT小天博客APP版本
    七牛云存储文件批量下载工具使用教程
    Mysql的常见几种错误:1045,1044
    我是一个线程
    两行代码快速创建一个iOS主流UI框架
  • 原文地址：https://www.cnblogs.com/JSD1207ZX/p/9386334.html
Copyright © 2020-2023  润新知