在oracle中从存储过程分配输出参数 [英] Assigning output parameter from Stored Procedure in oracle

问题描述

在以下情况下请帮助我

我需要在另一个内部调用存储过程.内部SP将返回一个数字值，我需要将其存储在局部变量中.

I need to call a Stored Procedure inside another one. the inner SP would return a number value which I need to store it in a local variable.

粗略的代码

AssignSpResult NUMBER;

AssignSpResult = SPtobestanding(Param1，Param2，OutParam);

AssignSpResult = SPtobecalled(Param1,Param2, OutParam);

此语法很可能是错误的，因此请帮助更正

This syntax is most probably wrong, so please help correct it

推荐答案

下面是一个示例:

HR\XE> create or replace procedure Proc1(p_out out number)
  2    is
  3    begin
  4      p_out := 123;
  5*   end;
HR\XE> /

Procedure created.

HR\XE> create or replace procedure Proc2
  2    is
  3      l_val number;
  4    begin
  5      Proc1(l_val); 
  6      dbms_output.put_line('Here is a value returned by Proc1: ' || to_char(l_val));
  7    end;
  8  /

Procedure created.

HR\XE> set serveroutput on;
HR\XE> exec Proc2;

Here is a value returned by Proc1: 123                                            

PL/SQL procedure successfully completed

根据您的需求，使用函数返回数据的过程处理结果可能会更方便.这是一个示例:

Depending on your needs it might be more convenient to use functions to return a result of a procedural processing of data. Here is an example:

HR\XE> create or replace function F1 return number
  2    is
  3      l_ret_value number;
  4    begin
  5      l_ret_value := 123;
  6      return l_ret_value;
  7    end;
HR\XE> /

Function created.

HR\XE> create or replace procedure Proc3
  2    is
  3      l_val number;
  4    begin
  5      l_val := F1;
  6      dbms_output.put_line('Value returned by the F1 function: ' || 
                              To_Char(l_val));
  7    -- Or
  8      dbms_output.put_line('Value returned by the F1 function: ' || To_Char(F1));
  9   end;
HR\XE> /

Procedure created.

HR\XE> set serveroutput on;
HR\XE> exec proc3;

Value returned by the F1 function: 123
Value returned by the F1 function: 123

PL/SQL procedure successfully completed.

HR\XE>

这篇关于在oracle中从存储过程分配输出参数的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！