Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

When should I nest PL/SQL BEGIN...END blocks?

Tags:

sql

oracle

plsql

I've been somewhat haphazardly grouping subsections of code in BEGIN...END blocks when it seems right. Mostly when I'm working on a longer stored procedure and there's a need for a temporary variable in one spot I'll declare it just for that portion of the code. I also do this when I want to identify and handle exceptions thrown for a specific piece of code.

Any other reasons why one should nest blocks within a procedure, function or another larger block of PL/SQL?

like image 891
aw crud Avatar asked Feb 25 '10 14:02

aw crud


People also ask

What is the use of nested blocks?

The main advantage of a nested block is that a scope for all the declared variables and the executable statements are created in such a way that the control of the program is improved.

Where can you nest sub blocks in PL SQL?

A block can be nested into another block. This can be nested either in the execution part or in the exception handling part. These block can also be labeled. One outer block can contain many inner blocks.

What's the proper order of a typical PL SQL block?

A PL/SQL block consists of three sections: declaration, executable, and exception-handling sections. In a block, the executable section is mandatory while the declaration and exception-handling sections are optional.

Is begin mandatory in PL SQL?

This section is enclosed between the keywords BEGIN and END and it is a mandatory section. It consists of the executable PL/SQL statements of the program. It should have at least one executable line of code, which may be just a NULL command to indicate that nothing should be executed.


1 Answers

When you want to handle exceptions locally like this:

begin
   for emp_rec in (select * from emp) loop
      begin
         my_proc (emp_rec);
      exception
         when some_exception then
            log_error('Failed to process employee '||emp_rec.empno);
      end;
   end loop;
end;

In this example, the exception is handled and then we carry on and process the next employee.

Another use is to declare local variables that have limited scope like this:

declare
    l_var1 integer;
    -- lots of variables
begin
   -- lots of lines of code
   ...
   for emp_rec in (select * from emp) loop
      declare
         l_localvar integer := 0;
      begin
         -- Use l_localvar
         ...
      end
   end loop;

end;

Mind you, wanting to do this is often a sign that your program is too big and should be broken up:

declare
   l_var1 integer;
   -- lots of variables
   ...
   procedure local_proc (emp_rec emp%rowtype):
      l_localvar integer := 0;
   begin
      -- Use l_localvar
      ...
   end
begin
   -- lots of lines of code
   ...
   for emp_rec in (select * from emp) loop
      local_proc (emp_rec);
   end loop;

end; 
like image 183
Tony Andrews Avatar answered Oct 01 '22 10:10

Tony Andrews