Goto statement Oracle

Goto statement branches statements using labels


goto <label_name>;

Example :-

1) Simple goto example

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

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

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

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

Leave a Reply

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

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

Google photo

You are commenting using your Google 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 )

Connecting to %s