解析 PL/SQL 语句中的占位符以立即执行

Question

我从几天开始学习这门语言。我试图使用一个包含 PL/SQL 块的字符串和一个占位字符串，其中有两个字段，我想用从 SELECT 语句检索到的一些数据替换它们。

我已经正确创建并填充了表employees。

问题是我需要"替换"这些占位符(变量 :name cmd2 中的占位符和:salary(，但是当我EXECUTE IMMEDIATE使用检索到的值时，出现此错误：ORA-01006: bind variable does not exist .

这是代码片段：

DECLARE
    cmd1 VARCHAR2(200) := 'SELECT * FROM employees';
    cmd2 VARCHAR2(200) := 'BEGIN DBMS_OUTPUT.PUT_LINE('':name has a salary of :salary;''); END;';
    str VARCHAR2(200);
    c1 SYS_REFCURSOR;
    emp employees%ROWTYPE;
BEGIN
    OPEN c1 FOR cmd1;
    LOOP
        FETCH c1 INTO emp;
        EXIT WHEN c1%NOTFOUND;
        -- It doesn't work
        EXECUTE IMMEDIATE cmd2 USING emp.name, emp.salary;
        -- It works, but just prints ':name has a salary of :salary;'
        EXECUTE IMMEDIATE cmd2;
    END LOOP;
END;

预期结果应为：

Name1 has a salary of 300;
Name2 has a salary of 700;
-- ...and so on

Answer 1

绑定变量位于字符串中，因此它们不被视为绑定。

尝试

cmd2 VARCHAR2(200) := q'[BEGIN DBMS_OUTPUT.PUT_LINE(:name || ' has a salary of ' || :salary); END;]';

Answer 2

问题出在PL/SQL定义cmd2：

cmd2 VARCHAR2(200) :=
   'BEGIN DBMS_OUTPUT.PUT_LINE('':name has a salary of :salary;''); END;';

您不能在字符串值中引用变量名称 - 它们只是那里的文本。 此更改将使其正常工作;

cmd2 VARCHAR2(200) := 
   'BEGIN DBMS_OUTPUT.PUT_LINE(:name||'' has a salary of ''||:salary); END;';

现在，第一次执行将成功，但第二次执行将失败，并显示：

ORA-01008: not all variables bound

因此，删除第二次执行，一切都会好起来的！

注意

您的示例不是动态 PL/SQL 的典型用例，因为静态 PL/SQL 也可以实现相同的效果：

BEGIN
    FOR r IN SELECT * FROM employees
    LOOP
      DBMS_OUTPUT.PUT_LINE(r.name || ' has a salary of ' || r.salary');
    END LOOP;
END;

动态 SQL 和 PL/SQL 只有在无法实现静态 SQL 时才应该真正使用，例如，因为表名、列名或过程名不是固定的。请参阅Oracle 文档中的一些示例。

Answer 3

绑定变量最好用于PLSQL块内的SQL语句。不应在语句DBMS_OUTPUT绑定变量。

在您的情况下

cmd2 VARCHAR2(200( ：= '开始DBMS_OUTPUT。PUT_LINE(''：名字有薪水 的 ：工资;''(;结束;';

这里BIND变量的应用是不正确的。这是不允许的。

请参阅下面的使用绑定变量的简单示例。

SQL> DECLARE
  2      cmd1 VARCHAR2(200) := 'SELECT * FROM EMP';
  3      cmd2 VARCHAR2(200) := 'SELECT * FROM EMP WHERE ENAME = :name  and sal =:salary'; --<--See how bind variables are used
  4      str VARCHAR2(200);
  5  
  6      c1 SYS_REFCURSOR;
  7  
  8      emp1 emp%ROWTYPE;
  9  BEGIN
 10      OPEN c1 FOR cmd1;
 11      LOOP
 12          FETCH c1 INTO emp1;
 13          EXIT WHEN c1%NOTFOUND;
 14  
 15          -- It doesn't work
 16          EXECUTE IMMEDIATE cmd2 USING emp1.ename, emp1.sal;
 17  
 18          
 19      END LOOP;
 20  END;
 21  /
PL/SQL procedure successfully completed.
SQL>