?
Solved

How to PL/SQL subroutine?

Posted on 2007-08-06
7
Medium Priority
?
3,703 Views
Last Modified: 2008-01-09
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
0
Comment
Question by:rmtye
7 Comments
 
LVL 22

Expert Comment

by:JimBrandley
ID: 19642329
Ron - You can just move the common code into a new stored procedure and invoke that procedure from the first one.

Jim
0
 
LVL 22

Expert Comment

by:JimBrandley
ID: 19642341
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
 
LVL 27

Assisted Solution

by:sujith80
sujith80 earned 80 total points
ID: 19643490
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
NFR key for Veeam Backup for Microsoft Office 365

Veeam is happy to provide a free NFR license (for 1 year, up to 10 users). This license allows for the non‑production use of Veeam Backup for Microsoft Office 365 in your home lab without any feature limitations.

 
LVL 78

Accepted Solution

by:
slightwv (䄆 Netminder) earned 120 total points
ID: 19645336
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
 

Author Comment

by:rmtye
ID: 19647038
Is there anything like global variables or do I need to pass everything from the main procedure to the sub-procedure?

Thanks
Ron
0
 
LVL 78

Expert Comment

by:slightwv (䄆 Netminder)
ID: 19647133
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
 
LVL 78

Expert Comment

by:slightwv (䄆 Netminder)
ID: 19647148
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

Featured Post

Keep up with what's happening at Experts Exchange!

Sign up to receive Decoded, a new monthly digest with product updates, feature release info, continuing education opportunities, and more.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Working with Network Access Control Lists in Oracle 11g (part 2) Part 1: http://www.e-e.com/A_8429.html Previously, I introduced the basics of network ACL's including how to create, delete and modify entries to allow and deny access.  For many…
Using SQL Scripts we can save all the SQL queries as files that we use very frequently on our database later point of time. This is one of the feature present under SQL Workshop in Oracle Application Express.
This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.
This video explains at a high level about the four available data types in Oracle and how dates can be manipulated by the user to get data into and out of the database.
Suggested Courses

864 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question