Hello,
I created a simple table.
create table test(t number);
I tried to insert values in it.
I created o simple PLSQL block to insert values.
declare
i integer;
begin
for me in 1..2 loop
insert into test(t) values(i);
end loop commit;
end;
I put on the same line two commands:
end loop commit;when I compiled this simple code I don't receive any error of syntax.
I executed that code and my table is empty, the commit command doesn't execute.
I don't know why is possible to write two commands on the same line.
Is this an error?
Can you explain to me what happened?
I tried to put other commands after end loop and the code compiled succesfully:
end loop rollback;
end loop continue;
end loop break;
Thanks a lot.
That's because Oracle Database interprets these as loop labels!
You can put whatever you want after "end loop" (provided it conforms to the database naming rules). The main reason is to help improve readability.
If you have multiple loops in a block of code, it can be tricky to tell how the starts and ends match up. Placing labels before and after makes it clearer to see what's going on. Particularly if your colleagues don't believe in indenting their code ;)
You can also use labels to qualify loop variables. Which can be useful if you have name clashes:
begin
<<outer>>
for i in 1 .. 2 loop
<<inner>>
for i in 1 .. 2 loop
dbms_output.put_line(outer.i || ',' || inner.i);
end loop inner;
end loop outer;
end;
/
1,1
1,2
2,1
2,2
Note though the compiler doesn't check that the start and end labels match. You need to verify this. So you can end up with nonsense like this if you're not careful:
begin
<<nice_loop_start_label>>
for i in 1 .. 2 loop
dbms_output.put_line(i);
end loop random_junk_end_label;
end;
/
1
2