Goto statement Oracle

Goto statement branches statements using labels

Syntax:

goto <label_name>;

Example :-

1) Simple goto example

declare
  n integer;
begin
 select count(*) into n from emp where deptno = 5;
 if n > 0 then
   goto job1;
 else
   goto job2;
 end if;  
<<job1>>
dbms_output.put_line(‘job1 – Dept has employees’);
return;
<<job2>>
dbms_output.put_line(‘job2 – Dept has no employees’);
return;
end;
/

Above example there are two labels defined job1 and job2. Note that if return keyword is not specified it will execute both label one by one.

2) Implementing loop using goto

declare
n integer := 5;
begin
for i in 1..10 loop
if mod(i,5) = 0 then
goto print;
end if;
end loop;
<<print>>
dbms_output.put_line(‘Number is divisible by 5’);
end;

Things to remember

1) Use return or other program strategy to ensure to prevent multiple execution of labels

2) Goto statement must precede some executable or at least null keyword

3) A Goto statement cannot branch from one IF statement to another

4) A Goto statement cannot branch from one CASE statement to another

5) Label name must be unique in the block

Read also – For loop, While loop

Advertisements

Author: sqlandplsql

To learn more about the Oracle, SQL, PL SQL, Performance Tuning, Database Modeling, Ubuntu, MySQL etc .Suggestions, comments, feedbacks and referrals are highly appreciated.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s