dhk

 Profile Answers by dhk 

• Apr 20th, 2006

 

Hello friend

              while creating a procedure body we can call other procedure in that body

       for example

                 if your creating the procedure body p1 we can call the procedure p2 in the procedure body p1

 syntax

 create or replace procedure p1(a number) is

b number;

begin

p1(10);-- calling first procedure

end;

when you execute procedure p1

exec p1(10) -- it will execute both p1 and p2

              

Arun

• Oct 27th, 2006

 

Hi friendit is easyExamplecreate or replace procedure first_proc (v_name1 in varchar, v_age in number) isbegindbms_output.put_line(v_name||' is '||v_age||' years old!');end;create or replace procedure second_proc (v_name2 in varchar2, v_dob date) isdeclare m_age number;begin m_age := round(months_between(sysdate,v_dob)/12); first_proc(v_name2,m_age);end;to execute SQL> exec second_proc('Arun','12-JUL-1990');SQL> Arun is 16 years old!hope this helps

debasisdas

 Profile Answers by debasisdas 

• Apr 11th, 2011

 

I believe an example would be best way to explain this.

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]

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;
  <<abc>>
  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]