从另一个过程调用过程 [英] Calling Procedure from another procedure

问题描述

你好，

请任何人建议我，我们如何在一个过程中调用另一个过程.

如果能得到类似的例子，我会很高兴.

谢谢.

Hello,

Please can anyone suggest me,how can we call a another procedure in a procedure.

I would be glad if i get a similar example.

Thanks.

推荐答案

create procedure sp_proc2
as
begin
 declare @retcode int
--  here you can call another stored procedure
 exec @retcode = sp_proc1

end

通过这种方式，您可以调用

in this way you can call

您可以这样调用.

You can call like this.

EXEC sp_name @param, @output OUTPUT

检查以下示例.

http://www.aspfree.com/c/a/ASP.NET-Code/Call-Stored-procedure-from-in-other-stored-procedure-return-values/ [此处 [

Check the below example.

http://www.aspfree.com/c/a/ASP.NET-Code/Call-Stored-procedure-from-within-another-stored-procedure-return-values/[^]

For more info, go here[^].

方案是用户传递一个employee_no插入到EMP表中，但希望将该值与现有记录进行核对.如果不存在employee_no，那么记录将被插入到表中，但是如果记录已经存在，则employee_no将每次加1，并与现有记录进行核对，直到不再存在employee_no，最后将记录插入数据库中.表.

听到我们走吧.

[CODE]
创建或替换过程find(eno号，标出布尔值)是
var1 number(2);

The scenario is whan user passes an employee_no to be inserted into EMP table i wan that value to be check against the existing record. If the employee_no does not exists the record will be inserted into the table , but if the record alredy exists the employee_no will be incremented by 1 each time and checked against existing record till no more such employee_no exists and finally the record gets inserted into the database table.

So hear we go.

[CODE]
create or replace procedure find(eno number, flag out boolean) is
var1 number(2);

begin
  select count(*) into var1 from emp where empno = eno;
  if var1 = 0 then
    flag := FALSE;
  else
    flag := TRUE;
  end if;
end;

[/CODE]

上面的过程将检查数据库表中记录是否存在.所以我在这里所做的就是将Primay键列作为过程的参数传递.此过程还具有一个OUT参数，该参数告诉查询输出是什么.我只是根据PK列对记录进行计数，如果记录不存在(计数= 0)，则返回FALSE，否则返回TRUE.

现在，此过程在另一个过程中被称为下一个过程(INSERT_EMP).
我将单个数字参数传递给该过程，该参数又传递给第一个过程(FIND).因此，逻辑在FIND中进行了测试，该过程的输出返回到该程序.基于该值，实际交易完成.

[CODE]
创建或替换过程INSERT_EMP(emp_num number)是

[/CODE]

The above procedure will check for the existance of the record in the database table. So what i did here is pass the Primay key column as an argument to the procedure. This procedure also has an OUT parameter, that tells what the query output was. Simply i take a count of the record based on the PK column, if the record does not exists (count =0) return FALSE ,TRUE otherwise.

Now this procedure is called inside another procedure, the next one (INSERT_EMP).
I pass a single number parameter to the procedure, which in turn is passed into the first procedure (FIND). So the logic is tested inside FIND and the output of that procedure is returned back to this prodecure. Based on that value the actual transaction is completed.

[CODE]
create or replace procedure INSERT_EMP(emp_num number) is

 eno emp.empno%type;
  fg  boolean;
begin
  eno := emp_num;
  <>
  find(eno, fg);
  if fg = true then
    dbms_output.put_line(eno || '  Already exists...');
    eno := eno + 1;
    goto abc;
  else
    insert into emp (empno) values (eno);
    COMMIT;
    dbms_output.put_line('New empno inserted is : ' || eno);
  end if;
end;

[/CODE]

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