不明白动态sql的重复占位符

dingjun123

ls的不对，pl/sql中的动态sql是按位置的，不是名字
动态pl/sql才是按名字，不按位置

其他的程序编程接口有命名法绑定，那么是按名字的

newkid

我以前都没注意到，确实有点变态。

CREATE TABLE T1 (N1 NUMBER, N2 NUMBER,N3 NUMBER,N4 NUMBER);
  
BEGIN
   EXECUTE IMMEDIATE 'INSERT INTO T1(N1,N2,N3,N4) VALUES (:N1,:N2,:N2,:N1)' USING 1,2;
END;
/

*
ERROR at line 1:
ORA-01008: not all variables bound
ORA-06512: at line 2

BEGIN
   EXECUTE IMMEDIATE 'BEGIN INSERT INTO T1(N1,N2,N3,N4) VALUES (:N1,:N2,:N2,:N1); END;' USING 1,2;
END;
/


PL/SQL procedure successfully completed.

SELECT * FROM T1;

        N1         N2         N3         N4
---------- ---------- ---------- ----------
         1          2          2          1

nyfor

同意newkid,我也觉得变态.

ghost2876

原帖由 涅烨曦 于 2011-6-8 09:42 发表
楼主的问题在书中应该也有提到才对。

原因是，BEGIN calc_stats(:x, :x, :y, :x); END 是一个PL/SQL 代码段，而非 insert into t6 (a,b,c) values (:x,:y,:x) 这样的DML，标准SQL语句。

在EXECUTE IMMEDIATE 中，利用USING语句绑定变量时，Oracle遵循针对PL/SQL存储过程使用占位符名称匹配的原则，而针对SQL语句则采用占位符位置匹配的原则。

PL/SQL 用户指南与参考 中的 例子如下：

动态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)关联。
     DECLARE
     a   NUMBER := 4;
     b   NUMBER := 7;
  BEGIN
     plsql_block     := 'BEGIN calc_stats(:x,  :x, :y, :x); END';
  
    EXECUTE IMMEDIATE plsql_block
                  USING a, b;
     ...
  END;   

我觉得这个是问题的核心，即Oracle针对PL/SQL块(BEGIN END之间的存储过程或者SQL语句)，对绑定变量采用的是按照参数名称匹配的原则；而针对PL/SQL语句(INSERT、DELETE等)则是采用的按照参数位置匹配的原则。匹配的原则不同，产生了不同的执行结果。就比如下面：

1、成功的情况
create or replace procedure test_dynamicSQL(m number, n number) is
  v_sql varchar2(100);
begin
  v_sql := 'begin insert into t6 (a,b,c) values (:x,:y,:x); end;';
  execute immediate v_sql
    using m, n;
end;

然后执行：
BEGIN
  test_dynamicSQL(38, 2);
END;

2、失败的情况
create or replace procedure test_dynamicSQL(m number, n number) is
  v_sql varchar2(100);
begin
  v_sql := 'insert into t6 (a,b,c) values (:x,:y,:x)';
  execute immediate v_sql
    using m, n;
end;

然后执行：
BEGIN
  test_dynamicSQL(38, 2);
END;

情况1就可以成功，情况2就会失败。问题的差异应该就在这里。向各位前辈学习了。嘻嘻~~~

[ 本帖最后由 ghost2876 于 2011-7-23 12:47 编辑 ]