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?
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.
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.
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.
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.
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;
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With