如何使用execute immedate执行本地过程? [英] How to execute a local procedure using execute immedate?

问题描述

我有以下PL SQL块:

WHENEVER SQLERROR EXIT 1
SET SERVEROUTPUT ON   

DECLARE
v_sql VARCHAR2(500);
f1 VARCHAR2(20) := 'abc';
p_procname VARCHAR2 (30) := 'OPENLOG';

   PROCEDURE OPENLOG (file_name IN VARCHAR2)
   IS
   BEGIN
      NULL;
   END;    

BEGIN
DBMS_OUTPUT.PUT_LINE('Begin');
v_sql := 'BEGIN ' || p_procname || '(:a); END;';
EXECUTE IMMEDIATE v_sql USING IN f1;
END;
/

执行上面的代码块时，出现错误:

DECLARE
*
ERROR at line 1:
ORA-06550: line 1, column 7:
PLS-00201: identifier 'OPENLOG' must be declared
ORA-06550: line 1, column 7:
PL/SQL: Statement ignored
ORA-06512: at line 19

但是，如果OPENLOG过程是程序包的一部分，那么它将正常工作.

请告知如何使用动态SQL执行本地过程.

解决方案

正如阿马里洛(Amarillo)所说，您不能动态执行本地定义的过程，因为动态部分将使用的SQL范围中不存在该过程. /p>

您描述的情况是，所有过程都在匿名块的DECLARE部分中定义，并且您正在运行一个查询，该查询告诉您要执行的过程-大概还可以给您传递参数.您可以只使用if/else构造或case语句来执行适当的过程，例如:

DECLARE
  ...
BEGIN
  FOR data IN (SELECT procname, arg1, arg2, ... from <your_query>) LOOP
    CASE data.procname
      WHEN 'OPENLOG' THEN
        openlog(data.arg1);
      WHEN 'WRITELOG' THEN
        writelog(data.arg1, data.arg2);
      WHEN ...
        ...
      ELSE
         -- handle/report an invalid procedure name
         -- or skip the `ELSE` and let CASE_NOT_FOUND be thrown
    END CASE;
  END LOOP;
END;
/

您只需要一个WHEN条件，并为每个过程调用适当的过程.您还可以具有ELSE来捕获任何意外的过程名称，也可以抛出CASE_NOT_FOUND异常(ORA-06592)，这取决于发生的情况.

I have the below PL SQL Block:

WHENEVER SQLERROR EXIT 1
SET SERVEROUTPUT ON   

DECLARE
v_sql VARCHAR2(500);
f1 VARCHAR2(20) := 'abc';
p_procname VARCHAR2 (30) := 'OPENLOG';

   PROCEDURE OPENLOG (file_name IN VARCHAR2)
   IS
   BEGIN
      NULL;
   END;    

BEGIN
DBMS_OUTPUT.PUT_LINE('Begin');
v_sql := 'BEGIN ' || p_procname || '(:a); END;';
EXECUTE IMMEDIATE v_sql USING IN f1;
END;
/

When I execute the above block, I get the error:

DECLARE
*
ERROR at line 1:
ORA-06550: line 1, column 7:
PLS-00201: identifier 'OPENLOG' must be declared
ORA-06550: line 1, column 7:
PL/SQL: Statement ignored
ORA-06512: at line 19

However, if the procedure OPENLOG is part of a package, then it works fine.

Please advise how to execute a local procedure using dynamic SQL.

解决方案

As Amarillo said you can't execute a locally-defined procedure dynamically, as it doesn't exist in the SQL scope the dynamic section will be using.

The situation you describe is that all the procedures are defined in the anonymous block's DECLARE section and you are running a query that tells you which of them to execute - and presumably which also gives you the arguments to pass. You can just use an if/else construct or a case statement to execute the appropriate procedures, something like:

DECLARE
  ...
BEGIN
  FOR data IN (SELECT procname, arg1, arg2, ... from <your_query>) LOOP
    CASE data.procname
      WHEN 'OPENLOG' THEN
        openlog(data.arg1);
      WHEN 'WRITELOG' THEN
        writelog(data.arg1, data.arg2);
      WHEN ...
        ...
      ELSE
         -- handle/report an invalid procedure name
         -- or skip the `ELSE` and let CASE_NOT_FOUND be thrown
    END CASE;
  END LOOP;
END;
/

You just need one WHEN condition and appropriate procedure call for each procedure. You can also either have an ELSE to catch any unexpected procedure names or let the CASE_NOT_FOUND exception (ORA-06592) be thrown, depending on what you need to happen if that ever occurs.

这篇关于如何使用execute immedate执行本地过程?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！