plsql - Block boundries in oracle plsql - Stack Overflow

admin管理员组

文章数量:1415491

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?

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?

• oracle-database
• plsql

Share Improve this question Follow edited Feb 21 at 17:59 tahzibi.jafar asked Feb 21 at 17:42 tahzibi.jafartahzibi.jafar 32711 gold badge22 silver badges1010 bronze badges 3

• It is an error. – Sushil Behera Commented Feb 21 at 18:07
• It is not, @SushilBehera. – Littlefoot Commented Feb 21 at 18:30
• 1 Not an error but it's clearly a mistake. – Paul W Commented Feb 21 at 18:32

Add a comment  | 

1 Answer 1

Sorted by: Reset to default 6

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>

本文标签： plsqlBlock boundries in oracle plsqlStack Overflow