What’s in a Label
Did you know that any executable statement in your PL/SQL code can be “labeled”. To label a particular section of your code just add <<your_label_name_here>> in front of that section. The label name cannot be more than 30 characters and has to start with a letter. No need to end the label with a semi-colon (;). For example:
SQL> <<check_x>>
2 declare
3 x boolean := true;
4 begin
5 if x then
6 dbms_output.put_line('Correct ');
7 end if;
8 end;
9 /
Correct
PL/SQL procedure successfully completed.
If you move the label in front of “x boolean := true;” you will get an error, since “x boolean := true;” is not an executable statement:
SQL> declare
2 <<check_x>>
3 x boolean := true;
4 begin
5 if x then
6 dbms_output.put_line('Correct ');
7 end if;
8 end;
9 /
<<check_x>>
*
ERROR at line 2:
ORA-06550: line 2, column 5:
PLS-00103: Encountered the symbol "<"
when expecting one of the following:...
Labels can be the target of a GOTO:
SQL> <<check_x>>
2 declare
3 x boolean := true;
4 begin
5 if x then
6 dbms_output.put_line('Correct ');
7 else
8 goto do_nothing;
9 end if;
10 <<do_nothing>>
11 null;
12 end;
13 /
Correct
PL/SQL procedure successfully completed.
Labels can be the target of an EXIT:
SQL> begin
2 <<first_loop>>
3 for i in 1..4 loop
4 for j in 1..4 loop
5 exit first_loop;
6 end loop;
7 dbms_output.put_line('Hello');
8 end loop;
9 end;
10 /
PL/SQL procedure successfully completed.
Labels can be used to qualify references to variables with the same name:
SQL> <<label1>>
2 declare
3 x varchar2(10) := 'hello';
4 begin
5 declare
6 x varchar2(10) := ' there';
7 begin
8 dbms_output.put_line(label1.x || x);
9 end;
10 end;
11 /
hello there
PL/SQL procedure successfully completed.
Finally, I have never used PL/SQL labels or known a developer who uses them. So, what’s the purpose of this post? Well, I just want to double check whether there are still developers who use labels, and goto’s (Yak!). So, if you’re one of them, let us know…
Browse the relevant documentation.
Related articles:
- ColdFusion to Oracle via a Boolean
- The order of conditions matters
- How to find where an error was raised in PL/SQL
You missed what I think is a genuine use for labels: identifying nested loops.
<<dept>> FOR d_rec IN ( SELECT deptno FROM scott.dept ) LOOP do_whatever; <<emp>> FOR e_rec IN (SELECT empno FROM scott.emp WHERE deptno = d_rec.deptno ) LOOP blah(e-rec.empno); END LOOP emp; clean_up; END LOOP dept;And what’s wrong with GOTOs anyway? haven’t they been officially considered not harmful for sometime now? I take it you never raise exceptions either.
Cheers, APC
Unfortunately there are lots of developers insisting to use goto’s. It is a tradition some people cannot give up. It was imposible to get a degree for students using goto’s in their assignments in my college years.
I use labels in 3 situations: Loops, case statement and begin end block inside a procedure/function. I like that you can end the case or the begin with a label like this:
I don’t use GOTOs, but I do use labels for loops sometimes. It’s really a clarity thing. If I have nested loops, the labels are handy to see which loop has ended, although a comment would do the same. I also use labels if I’m planning to exit a loop prematurely.
Cheers
Tim…
> And what’s wrong with GOTOs anyway? haven’t they been officially considered not harmful for sometime now?
They have? Nobody tells me anything.
I take it you never raise exceptions either
I take it you use GOTO to raise exceptions
I’m not a developer, but the term GOTO reminds me of writing BASIC on my Commodore 64. I can’t believe it still exists, besides in Windows batch files.
Of course not. I was merely pointing out that raising an exception is a special case of GOTO. Particularly given a case like this:
.... do_something_a; do_something_b; EXCEPTION WHEN others THEN blah;If do_something_a raises an exception to be handle by the calling program then essentially there is an invisible GOTO that bypasses do_something_b.
Before I started in Oracle I worked on a particular flavour of COBOL that didn’t support conditional ERFORMs. GOTOs were a way of life. So I know bad use of GOTO create spaghetti code. I also know that well structured GOTOs have their place.
Of course, Oracle PL/SQL has a panoply of control structures and I have never had to use a GOTO since I stopped COBOLing. Indeed the other day I found myself with a LOOP that had more than one EXIT statement and felt unclean.
They have? Nobody tells me anything.
Actually I was thinking about the NTK newsletter and not some computer science pronouncement. I don’t think Dijkstra has revoked his fatwa. Still, if you’ve ever had to follow the possible paths through a function that has over a dozen RETURN statements you might find yourself pining for a nice GOTO exit_point.
GOTOs are like triggers: there’s nothing inherently wrong with the construct, it’s simply the potential for abuse that renders their use suspect. Simply avoiding the use of GOTO statement does not make our code well-structured.
[OT] I don’t think Dijkstra has revoked his fatwa.
I don’t think he will ever be able to revoke it given that he passed away few years back
Label could be an elegant solution to exit 2 nested loop
APC: I was merely pointing out that raising an exception is a special case of GOTO.
Ah! I see. when you raise an exception, you basically “go to” the corresponding exception block.
APC: Before I started in Oracle I worked on a particular flavour of COBOL that didn’t support conditional ERFORMs. GOTOs were a way of life.
I started my programming career with COBOL, that was in 1992. COBOL and GOTO were best buddies.
In PL/SQL, I just cannot see a real need to use labels and GOTO’s. Labels can be easily replaced with comments. GOTO’s can be easily replaced with many other programming controls that provide a much better clarity to the program logic.
Wow. I’m moving pl/sql IF statements into the trash bin along side GOTOs, now that I see I can use labeled CASE instead. (Nested IFs are such a pain to visually decipher.)