When Should I Nest Pl/SQL Begin...End Blocks

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

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; 

multiple/nested begin/end, function statements in procedures in pl/sql

begin...end; alone does nothing. You only need a nested block if it has a declare or exception that makes sense at that level.

For example, the inner begin/end keywords here are pointless, and you can just remove them:

begin
    ...

    begin
        processing steps
    end;

    ...
end;

A nested block is useful if we want to handle some exception in a nested block only (for example, handle no_data_found exceptions for a particular lookup, or file operations for utl_file). This is often better than having exception handlers at the end, especially for procedures with a lot of processing steps:

begin
    ...

    begin
        processing steps
    exception
        when no_data_found then...
    end;

    ...
end;

Similarly, we might declare a local variable or constant etc with the scope of the nested block only. This is probably a less usual requirement than the exception example, but there are cases where it could be useful:

begin
    ...

    declare
        x constant integer := whatever();
    begin
        processing steps using x
    end;

    ...
end;

If the procedure is complex with a large number of steps, you might consider ways to break it up into smaller reusable modules.

BEGIN - END block atomic transactions in PL/SQL

Firstly, BEGIN..END are merely syntactic elements, and have nothing to do with transactions.

Secondly, in Oracle all individual DML statements are atomic (i.e. they either succeed in full, or rollback any intermediate changes on the first failure) (unless you use the EXCEPTIONS INTO option, which I won't go into here).

If you wish a group of statements to be treated as a single atomic transaction, you'd do something like this:

BEGIN
  SAVEPOINT start_tran;
  INSERT INTO .... ; -- first DML
  UPDATE .... ; -- second DML
  BEGIN ... END; -- some other work
  UPDATE .... ; -- final DML
EXCEPTION
  WHEN OTHERS THEN
    ROLLBACK TO start_tran;
    RAISE;
END;

That way, any exception will cause the statements in this block to be rolled back, but any statements that were run prior to this block will not be rolled back.

Note that I don't include a COMMIT - usually I prefer the calling process to issue the commit.

---

It is true that a BEGIN..END block with no exception handler will automatically handle this for you:

BEGIN
  INSERT INTO .... ; -- first DML
  UPDATE .... ; -- second DML
  BEGIN ... END; -- some other work
  UPDATE .... ; -- final DML
END;

If an exception is raised, all the inserts and updates will be rolled back; but as soon as you want to add an exception handler, it won't rollback. So I prefer the explicit method using savepoints.

Why do we need BEGIN END (execution section) in last portion for an Anonymus block?

An PL/SQL block has the structure DECLARE ... BEGIN ... END;. The DECLARE is optional if you want to declare any variables, etc. but the BEGIN ... END; is mandatory.

If we start with a simple block and build up:

BEGIN
  NULL;
END;
/

Is the simplest PL/SQL statement. You then declare a type:

DECLARE
  TYPE type_a IS TABLE OF NUMBER INDEX BY PLS_INTEGER;
BEGIN
  NULL;
END;
/

and a variable:

DECLARE
  TYPE type_a IS TABLE OF NUMBER INDEX BY PLS_INTEGER;
  output NUMBER := 1;
BEGIN
  NULL;
END;
/

Then you DECLARE a function; this includes its own PL/SQL BEGIN ... END; block that will be nested within the DECLARE section of the anonymous outer block:

DECLARE
  TYPE type_a IS TABLE OF NUMBER INDEX BY PLS_INTEGER;
  output NUMBER := 1;

  FUNCTION fun_2 RETURN type_a IS
    dum type_a;
  BEGIN
    SELECT employee_id
    BULK COLLECT INTO dum
    FROM employees;

    RETURN dum;
  END fun_2;
BEGIN
  NULL;
END;
/

Then you declare a procedure; again this includes its own PL/SQL BEGIN ... END; block that will be nested within the DECLARE section of the anonymous outer block after the function:

DECLARE
  TYPE type_a IS TABLE OF NUMBER INDEX BY PLS_INTEGER;
  output NUMBER := 1;

  FUNCTION fun_2 RETURN type_a IS
    dum type_a;
  BEGIN
    SELECT employee_id
    BULK COLLECT INTO dum
    FROM employees;

    RETURN dum;
  END fun_2;

  PROCEDURE proc_1 AS
  BEGIN
    NULL;
  END proc_1;
BEGIN
  NULL;
END;
/

Back to your question:

why is it needed?

Because, otherwise you have two BEGIN ... END; blocks for the function and procedure nested within the DECLARE section starting from the first line but no BEGIN ... END; that matches that DECLARE on the first line.

Using consistent indentation would help spot this.

Do nested blocks have any performance impact in PL/SQL procedures?

They don't appear to:

set timing on
set serveroutput on

declare
  x number := 0;
begin
  dbms_output.put_line('No inner blocks');
  for i in 1..1000000 loop
    x := x + 1;
  end loop;
  dbms_output.put_line(x);
end;
/

anonymous block completed
Elapsed: 00:00:00.095
No inner blocks
1000000

Runs in the same time, with a bit of variation each way, as:

declare
  x number := 0;
begin
  dbms_output.put_line('Nested inner blocks');
  for i in 1..1000000 loop
    begin
      begin
        begin
          begin
            x := x + 1;
          exception
            when others then
              raise;
          end;
        exception
          when others then
            raise;
        end;
      exception
        when others then
          raise;
      end;
    exception
      when others then
        raise;
    end;
  end loop;
  dbms_output.put_line(x);
end;
/

anonymous block completed
Elapsed: 00:00:00.090
Nested inner blocks
1000000

Of course it's possible the compiler is removing the redundant layers, but I'm not sure it really can with the exception handlers there as it would affect the result.

I haven't seen any limitation on how deep nested blocks can go - the documentation just says 'blocks can be nested'. The model you're using, catching a specific error and letting others propagate, is fine and pretty standard - though obviously in your contrived example it isn't necessary, but you know that.

Bind variable used in BEGIN/END gets cleared

Upgraded to SQL Developer 17.2.0.188 and it went away.

Appears to be this bug: https://stackoverflow.com/a/43501389/84206

NESTED IF's in PL/SQL

Have a look at this example for nested IF statements.

You need an END IF to end each.

DECLARE
  PROCEDURE p (
    sales  NUMBER,
    quota  NUMBER,
    emp_id NUMBER
  )
  IS
    bonus  NUMBER := 0;
  BEGIN
    IF sales > (quota + 200) THEN
      bonus := (sales - quota)/4;
      IF whatever_else_you_like THEN
        do_something_here;
      END if;
    ELSE
      IF sales > quota THEN
        bonus := 50;
      ELSE
        bonus := 0;
      END IF;
    END IF;     
    DBMS_OUTPUT.PUT_LINE('bonus = ' || bonus);     
    UPDATE employees
    SET salary = salary + bonus 
    WHERE employee_id = emp_id;
  END p;
BEGIN
  p(10100, 10000, 120);
  p(10500, 10000, 121);
  p(9500, 10000, 122);
END;
/

You can also have as many ELSIF statements as you need. eg.

DECLARE
  PROCEDURE p (sales NUMBER)
  IS
    bonus  NUMBER := 0;
  BEGIN 
    IF sales > 50000 THEN
      bonus := 1500;
    ELSIF sales > 35000 THEN
      bonus := 500;
    ELSE
      bonus := 100;
    END IF;

    DBMS_OUTPUT.PUT_LINE (
      'Sales = ' || sales || ', bonus = ' || bonus || '.'
    );
  END p;
BEGIN
  p(55000);
  p(40000);
  p(30000);
END;
/

Improving the readability of PL/SQL for identifying the LOOPs and Individual blocks

 <<outer_loop>>
 LOOP
   <<<block1>>
   BEGIN
       i := i + 1;
       j := 0;
       <<inner_loop>>
       LOOP
         -- Your Statements
         EXIT inner_loop WHEN (j > 5);
         EXIT outer_loop WHEN ((i * j) > 15);
       END LOOP inner_loop;
   END block1;
 END LOOP outer_loop;

Try using LABELS (embedded between angled brackets). This should help you!
Your can look for the label names, for where the block/loop starts or ends!

Actually, this kind of Label can be used for GOTO too

But dont over use it, as it also confuses you :)

Good Luck!

---

Related Topics