Skip to Main Content
  • Questions
  • Write stored procedure inside a stored procedure

Breadcrumb

Question and Answer

Chris Saxon

Thanks for the question, Arun.

Asked: August 13, 2019 - 3:09 pm UTC

Last updated: August 13, 2019 - 4:37 pm UTC

Version: 11.2.0.4

Viewed 1000+ times

You Asked

Hi Team Ask TOM,
Recently, I came across some code where developers had written four or five stored procedures inside another stored procedure. The code compiles fine. I always thought that to write stored procedures like this, they should be in a package. I am not an expert in PL/SQL and cannot find anything on whether this is a good practice or not. What would be the pros and cons of doing so?

Thanks,
Arun

and Chris said...

You mean something like this?

create or replace procedure p as
  procedure do_this as
  begin
    dbms_output.put_line ( 'THIS' );
  end do_this;
  
  procedure do_that as
  begin
    dbms_output.put_line ( 'THAT' );
  end do_that;
  
begin
  
  do_this();
  do_that();
  
end p;
/


Personally, I'm not a fan. As you suggest, I'd prefer to make a package, like this:

create or replace package body pkg as 
  
  procedure do_this as
  begin
    dbms_output.put_line ( 'THIS' );
  end do_this;
  
  procedure do_that as
  begin
    dbms_output.put_line ( 'THAT' );
  end do_that;
  
  procedure p as
  begin
    
    do_this();
    do_that();
    
  end p;
  
end;
/


The upside of defining procedures in the declaration section (nested or local subprograms) is the same as subprograms generally: you define logic in a single place that you can reuse many times. If a procedure needs to run the same process several times, this can make your code easier to maintain.

The downside is the nested subprograms are only available to the procedure that defines them. This makes them harder to test and limits reuse. In the first example above, if you find you want to call do_this() in another procedure, you need to refactor your code.

Whereas if it's a package-level procedure, you can reuse it throughout the rest of the package. Or add it to the package spec to make it publicly availalbe should you wish to.

Rating

  (1 rating)

Is this answer out of date? If it is, please let us know via a Comment

Comments

Arun Gupta, August 13, 2019 - 4:42 pm UTC

Thanks much!! Now it is clear.

Arun

More to Explore

PL/SQL demos

Check out more PL/SQL tutorials on our LiveSQL tool.

PL/SQL docs

PL/SQL reference manual from the Oracle documentation library