IT技术博客

1.对于DML或DDL语句中，占位符和绑定变量是按位置进行绑定的，即使占位符是重复的，绑定变量也要为每个占位符提供参数。

准备脚本：

16:49:51 SCOTT@ orcl>DROP TABLE demo;
Table dropped.
Elapsed: 00:00:00.90
16:49:54 SCOTT@ orcl>CREATE TABLE demo(val1 NUMBER,val2 NUMBER,val3 NUMBER);
Table created.

动态SQL：

16:49:56 SCOTT@ orcl>declare
16:52:16   2   v_sql VARCHAR2(500);
16:52:16   3   v_val1 NUMBER:=1;
16:52:16   4  begin
16:52:16   5      v_sql := 'INSERT INTO demo (val1,val2,val3) VALUES(:x,:x,:x)';
16:52:16   6      EXECUTE IMMEDIATE  v_sql USING v_val1;
16:52:16   7  end;
16:52:18   8  /
declare
*
ERROR at line 1:
ORA-01008: not all variables bound
ORA-06512: at line 6

虽然三个占位符都是\:x,但也需要提供足够的绑定变量。

修复：

16:52:19 SCOTT@ orcl>declare
16:54:07   2   v_sql VARCHAR2(500);
16:54:07   3   v_val1 NUMBER:=1;
16:54:07   4  begin
16:54:07   5      v_sql := 'INSERT INTO demo (val1,val2,val3) VALUES(:x,:x,:x)';
16:54:07   6      EXECUTE IMMEDIATE  v_sql USING v_val1,v_val1,v_val1;
16:54:07   7  end;
16:54:08   8  /
PL/SQL procedure successfully completed.

备注：提供的参数不必一样，为了测试方便我使用的是同一个参数。

2.对于存储单元或匿名PL/SQL块中的占位符，它和绑定变量是按名字匹配的，相同的占位符按一个占位符处。

准备脚本：

16:54:21 SCOTT@ orcl>CREATE OR REPLACE PROCEDURE calc_stats (
17:03:27   2    w NUMBER,
17:03:27   3    x NUMBER,
17:03:27   4    y NUMBER,
17:03:27   5    z NUMBER )
17:03:27   6  IS
17:03:27   7  BEGIN
17:03:27   8    DBMS_OUTPUT.PUT_LINE(w + x + y + z);
17:03:27   9  END;
17:03:27  10  /

动态PL/SQL

17:03:27 SCOTT@ orcl>
17:03:27 SCOTT@ orcl>DECLARE
17:04:15   2    a NUMBER := 1;
17:04:15   3    b NUMBER := 2;
17:04:15   4    c NUMBER := 3;
17:04:15   5    plsql_block VARCHAR2(100);
17:04:15   6  BEGIN
17:04:15   7    plsql_block := 'BEGIN calc_stats(:x, :x, :y, :z); END;';
17:04:15   8    EXECUTE IMMEDIATE plsql_block USING a,b,c,d;
17:04:15   9  END;
17:04:16  10  /
  EXECUTE IMMEDIATE plsql_block USING a,b,c,d;
                                            *
ERROR at line 8:
ORA-06550: line 8, column 45:
PLS-00201: identifier 'D' must be declared
ORA-06550: line 8, column 3:
PL/SQL: Statement ignored

占位符😡重复了2次，所以真正的占位符只有3个，但绑定变量传了4个所以多余的绑定变量无法识别。

注意：即使绑定变量是重复的也是有问题的：

17:07:24 SCOTT@ orcl>DECLARE
17:07:25   2    a NUMBER := 1;
17:07:25   3    b NUMBER := 2;
17:07:25   4    c NUMBER := 3;
17:07:25   5    plsql_block VARCHAR2(100);
17:07:25   6  BEGIN
17:07:25   7    plsql_block := 'BEGIN calc_stats(:x, :x, :y, :z); END;';
17:07:25   8    EXECUTE IMMEDIATE plsql_block USING a,a,b,c;
17:07:25   9  END;
17:07:26  10  /
DECLARE
*
ERROR at line 1:
ORA-01006: bind variable does not exist
ORA-06512: at line 8

修复：

17:07:58 SCOTT@ orcl>DECLARE
17:08:00   2    a NUMBER := 1;
17:08:00   3    b NUMBER := 2;
17:08:00   4    c NUMBER := 3;
17:08:00   5    plsql_block VARCHAR2(100);
17:08:00   6  BEGIN
17:08:00   7    plsql_block := 'BEGIN calc_stats(:x, :x, :y, :z); END;';
17:08:00   8    EXECUTE IMMEDIATE plsql_block USING a,b,c;
17:08:00   9  END;
17:08:01  10  /
7
PL/SQL procedure successfully completed.

3.如果想给占位符传一个空值是不能直接使用NULL的，例如：

17:08:01 SCOTT@ orcl>DECLARE
17:11:48   2    V_SQL  VARCHAR2(500);
17:11:48   3    V_VAL1 NUMBER := 1;
17:11:48   4  BEGIN
17:11:48   5    V_SQL := 'INSERT INTO demo (val1,val2,val3) VALUES(:x,:x,:x)';
17:11:48   6    EXECUTE IMMEDIATE V_SQL
17:11:48   7      USING V_VAL1, V_VAL1, NULL;
17:11:48   8  END;
17:11:48   9  /
    USING V_VAL1, V_VAL1, NULL;
                          *
ERROR at line 7:
ORA-06550: line 7, column 27:
PLS-00457: expressions have to be of SQL types
ORA-06550: line 6, column 3:
PL/SQL: Statement ignored

正确的做法是定一个未初始化的变量，将变量做为绑定变量：

17:11:49 SCOTT@ orcl>DECLARE
17:13:34   2    V_SQL  VARCHAR2(500);
17:13:34   3    V_VAL1 NUMBER := 1;
17:13:34   4    V_NULL VARCHAR2(1);
17:13:34   5  BEGIN
17:13:34   6    V_SQL := 'INSERT INTO demo (val1,val2,val3) VALUES(:x,:x,:x)';
17:13:34   7    EXECUTE IMMEDIATE V_SQL
17:13:34   8      USING V_VAL1, V_VAL1, V_NULL;
17:13:34   9  END;
17:13:35  10  /
PL/SQL procedure successfully completed.