CODEWITHSUNDEEP
oracle-databaseplsql
tahzibi.jafar
tahzibi.jafar

Reputation: 305

Block boundries in oracle pl/sql

The following code is compiled successfully in Oracle PL/SQL:

BEGIN

   NULL;
    
 -- some codes
        
END LOOP;

My question is, using END LOOP without any loop is an error or can has any special meaning?

Upvotes: 2

Views: 51

Answers (1)

Littlefoot
Littlefoot

Reputation: 143043

loop, in this context, doesn't represent end of a loop - it is a label. You could have used e.g.

SQL> begin
  2    null;
  3  end tahzibi;
  4  /

PL/SQL procedure successfully completed.

SQL>

and - as you can see - it works as well.

How come? That's - I believe - related to named PL/SQL blocks (we know as "functions" or "procedures") (your code represents an anonymous PL/SQL block).

A simple example:

SQL> create or replace procedure p_test is   --> procedure name is P_TEST
  2  begin
  3    null;
  4  end p_test;   --> best practice says that we should END it with its name; hence P_TEST after END keyword
  5  /

Procedure created.

SQL>

In anonymous PL/SQL block, that label isn't related to any name (obviously). Oracle allows it, but - at the same time - ignores it. You can use it, but don't have to - and we usually don't.

If you wanted to make some sense in it, then create a label in your code and end begin-end part of it with that label so that it improves readability, especially in complex PL/SQL procedures. Something like this:

SQL> begin
  2    <<compute_salary>>       --> this is the label
  3    begin
  4      null;
  5      -- here goes code that computes salaries and
  6      -- spreads thorugh 30 lines of code
  7    end compute_salary;      --> end that BEGIN-END block with the label name
  8  end;
  9  /

PL/SQL procedure successfully completed.

SQL>

Upvotes: 5

Related Questions