如何在另一个过程中执行一个过程 [英] How to execute one procedure within another procedure

问题描述

我正在编写一个程序，该程序将调用该程序，并且该程序将进一步调用另一个程序以执行不同的业务逻辑.所以我做了这样的事情.

Hi I am writing one procedure which will be called by the program and this procedure will further call to another procedure to perform different business logic. so I did something like this.

PROCEDURE calculator(service_id  IN NUMBER, amount IN  NUMBER) as

  p_proc_name varchar(100);
 begin
  select sc.procedure_name into p_proc_name from test.procedure sc where sc.service_config_id = service_id;

  begin
  execute immediate (p_proc_name) using 1;
  exception when NO_DATA_FOUND then 
  DBMS_OUTPUT.PUT_LINE('p_proc_name = ' || p_proc_name);
  end;


end sb_referal_calculator;

PROCEDURE f_service(amount  IN NUMBER) as
  cmpany_id NUMBER;
  service_date date;
  leases_days NUMBER;
  referal_amount Number;
  requested_quote_id number :=1;

  begin
    referal_amount :=0;
    DBMS_OUTPUT.PUT_LINE('service_date = ');


end f_service;

PROCEDURE d_service(amount  IN NUMBER) as
  cmpany_id NUMBER;
  service_date date;
  leases_days NUMBER;
  referal_amount Number;
  requested_quote_id number :=1;

  begin
    referal_amount :=0;
    DBMS_OUTPUT.PUT_LINE('service_date = ');

end d_service;

因此，此处calcultor过程将动态找到另一个过程名称，并尝试使用参数执行它.但它给出了一个错误. 这只是一个测试程序.

So here calcultor procedure will find the another procedure name dynamically and try to execute it with parameter. But it gives an error. It is just a test program.

Executing PL/SQL: CALL DBMS_DEBUG_JDWP.CONNECT_TCP( '10.1.26.70', '55891' )
Debugger accepted connection from database on port 55891.
ORA-00900: invalid SQL statement
ORA-06512: at "test.demo_pkg", line 38
ORA-06512: at line 8
Executing PL/SQL: CALL DBMS_DEBUG_JDWP.DISCONNECT()
Process exited.

我真的不知道此过程如何执行此任务.我记得它正在运行并且正在测试.但是真的不做我所做的事情并停止工作.

I really do not how this procedure will work to perform this task. I remembered it was running and I was doing testing. But really do not what i have did and stop working.

请纠正我我做错了什么.

Please correct me what i doing wrong.

谢谢

推荐答案

使用execute immediate时，它将在无法查看PL/SQL上下文的SQL上下文中运行动态语句.这里有几个影响.首先，您必须从PL/SQL调用过程，因此您需要创建一个匿名块(如Egor Skriptunoff所述)，确切的格式取决于表(以及表)的内容.最短的可能是:

When you use execute immediate it runs the dynamic statement in an SQL context that isn't able to see your PL/SQL context. That has several impacts here. Firstly, you have to call your procedure from PL/SQL so you need to create an anonymous block, as Egor Skriptunoff said, and exactly the format you need depends on what the table (and thus your vaiable) contains. The shortest it might be is:

execute immdiate 'begin ' || p_proc_name || ' end;' using 1;

但是，前提是该变量包含类似以下内容的值:

But that assumes the varible contains a value like:

test_pkg.d_service(:arg);

如果 only 仅包含该过程的 name ，且没有参数，也没有包限定符，即仅d_service，则它可能需要等于:

If it only contains the name of the procedure with no arguments and no package qualifier, i.e. just d_service, it might need to be as much as:

execute immdiate 'begin test_pkg.' || p_proc_name || '(:arg); end;' using 1;

或介于两者之间.

另一个影响是过程名称必须是公共的，因为它是在动态调用时从包外部有效地调用的；因此必须在包装规格中声明.从程序在体内出现的顺序来看，这可能已经是事实了.

The other impact is that the procedure name has to be public as it is effectively being called from outside the package when it's invoked dynamically; so it has to be declared in the package specification. That may already be the case here from the order the procedures are appearing in the body.

但是，如果您始终在同一程序包中调用过程，并且由于之后必须具有有限数量的可能值，则避免动态SQL并使用该值确定要调用的过程可能更简单:

But if you are always calling procedures in the same package, and since you must then have a limited number of possible values, it might be simpler to avoid dynamic SQL and use the value to decide which procedure to call:

case p_proc_name
  when 'f_service' then
    f_service(1);
  when 'd_service' then
    d_service(1);
  -- etc.
end case;

这也使您可以调用私有过程.

That also lets you call private procedures.

这篇关于如何在另一个过程中执行一个过程的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！