How to PL/SQL subroutine?

Hi,

Ive written an Oracle procedure that has the same code in several different places. In another language I would put the repeated code in a subroutine and put gosubs where I wanted to execute the code.

Ive been trying to find a similar mechanism in PL/SQL; but, all Ive found are vague references to creating a procedure or function?  

An example of how to do this would be very nice.

Oracle 10
Thanks
Ron
rmtyeAsked:
Who is Participating?
 
slightwv (䄆 Netminder) Commented:
Although embedded procedures will work as sujith80 has suggested, they can be pretty limiting.  Suppose you want to have another procedure use the printThis code.  You would have to duplicate it again.

You can create them as separate stand-alone procedures or 'package' them.

From what little you explained, I'd probably go with the package.
-----------------------
create or replace package test_pkg
is
      procedure printThis(arg in varchar2);
      procedure test_proc;

end test_pkg;
/

create or replace package body test_pkg
as
 procedure printThis(arg varchar2)
 as
 begin
  -- This is the sub program
  dbms_output.put_line(arg);
 end;

procedure test_proc
is
begin
-- this is the main procedure
-- do some work and call sub program
printThis('FIrst time');
-- do some other code
printThis('Second Time');
end;
end test_pkg;
/

exec test_pkg.test_proc;

exec test_pkg.printThis('Another way');
0
 
JimBrandleyCommented:
Ron - You can just move the common code into a new stored procedure and invoke that procedure from the first one.

Jim
0
 
JimBrandleyCommented:
Here's a simple example:
Inside the primary procedure,
   SecondaryProcedure( param1, param2 );
   -- do some work
   SecondaryProcedure( newParam1, newParam2 );

You can have in params to feed it, and out params to return results.

Jim
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

 
SujithData ArchitectCommented:
See the following example.

set serveroutput on
create or replace procedure test_proc
as
 procedure printThis(arg varchar2)
 as
 begin
  -- This is the sub program
  dbms_output.put_line(arg);
 end;
begin
-- this is the main procedure
-- do some work and call sub program
printThis('FIrst time');
-- do some other code
printThis('Second Time');
end;
/

To run this procedure:

exec test_proc
0
 
rmtyeAuthor Commented:
Is there anything like global variables or do I need to pass everything from the main procedure to the sub-procedure?

Thanks
Ron
0
 
slightwv (䄆 Netminder) Commented:
Yes. as long as you mean 'global' to everything in the single procedure ( sujith80's way ), or package (my way ):

Just like in any other programming language, you need to be careful with them.  Especially with the package approach.  You need to make sure that each procedure/function in the package works if called and really doesn't need the global variable set from another procedure/function.

Look for 'myVar' in both samples
-----------------------------------------
create or replace package test_pkg
is
      myVar      varchar2(10);

      procedure printThis(arg in varchar2);
      procedure test_proc;

end test_pkg;
/

show errors

create or replace package body test_pkg
as
 procedure printThis(arg varchar2)
 as
 begin
  -- This is the sub program
  dbms_output.put_line(arg);
  dbms_output.put_line('myVar: ' || myVar);
 end;

procedure test_proc
is
begin
--set variable
myVar := 'Hello';
-- this is the main procedure
-- do some work and call sub program
printThis('FIrst time');
-- do some other code
printThis('Second Time');
end;
end test_pkg;
/

exec test_pkg.test_proc;

------------------------------------------------------------


create or replace procedure test_proc
as
      myVar varchar2(10);
 procedure printThis(arg varchar2)
 as
 begin
  -- This is the sub program
  dbms_output.put_line(arg);
  dbms_output.put_line('myVar: ' || myVar);
 end;
begin
--set variable
myVar := 'Hello';
-- this is the main procedure
-- do some work and call sub program
printThis('FIrst time');
-- do some other code
printThis('Second Time');
end;
/

exec test_proc
0
 
slightwv (䄆 Netminder) Commented:
Just to add to the package method:  You can make the 'printThis' procedure local to the package by not declaring it in the package.  This way only other procedures/functions within the package body can see it.

-----------------------------------
create or replace package test_pkg
is
      myVar      varchar2(10);

      procedure test_proc;

end test_pkg;
/

show errors

create or replace package body test_pkg
as
...


now you can't do:  execute test_pkg.printThis('Hello');
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.