oracle 使用输出变量动态调用 PL/SQL 过程

Dynamically call PL/SQL procedure with output variables

提问by Paul

I'm trying to use dynamic sql to execute a procedure and retrieve the output parameters:

我正在尝试使用动态 sql 来执行一个过程并检索输出参数：

v_sql := 'BEGIN ' || p_procname || '(''' || p_input1 || ''', ''' || p_input2 || ''', v_output1); END;';
DBMS_OUTPUT.PUT_LINE(v_sql);
EXECUTE IMMEDIATE v_sql;

I have the v_output1variable declared, but I get this error message:

我已经v_output1声明了变量，但收到此错误消息：

PLS-00201: identifier 'V_ISSUE' must be declared 

The procedure works if I replace the above code with the dbms_ouput from v_sql, so it's OK on the procedure side, the issue is somewhere in how I'm trying to call it dynamically.

如果我用 dbms_ouput from 替换上面的代码，该过程就可以工作v_sql，所以在过程方面没问题，问题在于我如何尝试动态调用它。

I don't know if what I'm trying to do will work this way. Is there a way to retrieve the output parameters from this procedure call through dynamic SQL?

我不知道我正在尝试做的事情是否会这样。有没有办法通过动态 SQL 从这个过程调用中检索输出参数？

回答by ThinkJet

Don't use string concatenation to pass parameters to dynamic SQL. It's considered a bad practice at all, not only for Oracle.

不要使用字符串连接将参数传递给动态 SQL。这被认为是一种糟糕的做法，不仅对 Oracle 而言。

It's a bad, but main point of failure is using name of local variable inside dynamic SQL string, because it not visible anywhere outside code block where this variable was introduced.

这是一个糟糕的，但主要的失败点是在动态 SQL 字符串中使用局部变量的名称，因为它在引入该变量的代码块之外的任何地方都不可见。

Your code should look like this:

您的代码应如下所示：

declare 
  v_sql varchar2(4000);
  p_procname varchar2(100);
  p_input1 number;
  p_input2 number;
  v_output1 number;   
begin

  v_sql := 'begin ' || p_procname || '(:p_input1, :p_input2, :v_output); end;';

  execute immediate v_sql 
  using in p_input1, in p_input2, out v_output1;

end;

回答by Paul

Found my answer here.

在这里找到了我的答案。

v_sql := 'BEGIN ' || p_procname || '(:a, :b, :c); END;';

EXECUTE IMMEDIATE v_sql
USING IN p_input1, IN p_input2, OUT v_output1;