Goto statement - sequential control, PL-SQL Programming

Assignment Help:

GOTO Statement

The GOTO statement branches to a label unconditionally. The label must be exclusive within its scope and should precede an executable statement or a PL/SQL block. If executed, the GOTO statement transfers control to the labeled statement or block. In the following illustration, you go to an executable statement farther down in a series of statements:


BEGIN
...
GOTO insert_row;
...
<>
INSERT INTO emp VALUES...
END;


In the next illustration, you go to a PL/SQL block farther up in a series of statements:


BEGIN
...
<>
BEGIN
UPDATE emp SET ...
...
END;
...
GOTO update_row;
...
END;


The label end_loop in the example below is illegal as it does not precede an executable statement:


DECLARE
done BOOLEAN;
BEGIN
...
FOR i IN 1..50 LOOP
IF done THEN
GOTO end_loop;
END IF;
...

<> -- illegal
END LOOP; -- not an executable statement
END;


To debug the last illustration, now add the NULL statement, as shown:

FOR i IN 1..50 LOOP
IF done THEN
GOTO end_loop;
END IF;
...
<>
NULL; -- an executable statement
END LOOP;


As the following illustration shows, a GOTO statement can branch to an enclosing block from the present block:

DECLARE
my_ename CHAR(10);
BEGIN
<>
SELECT ename INTO my_ename FROM emp WHERE...
BEGIN
...
GOTO get_name; -- branch to enclosing block
END;
END;



Restrictions

Some likely destinations of a GOTO statement are illegal. Particularly, a GOTO statement cannot branch into an IF statement, LOOP statement, or sub-block. For illustration, the following GOTO statement is illegal:



BEGIN
...
GOTO update_row; -- illegal branch into IF statement
...
IF valid THEN
...
<>
UPDATE emp SET...
END IF;
END;

A GOTO statement also cannot branch from one IF statement clause to another, as the following illustration shows:


BEGIN
...
IF valid THEN
...
GOTO update_row; -- illegal branch into ELSE clause
ELSE
...
<>
UPDATE emp SET...
END IF;
END;


The next illustration shows that a GOTO statement cannot branch from an enclose block into a sub-block:


BEGIN
...
IF status = ’OBSOLETE’ THEN
GOTO delete_part; -- illegal branch into sub-block
END IF;
...

BEGIN
...
<>
DELETE FROM parts WHERE...
END;
END;


A GOTO statement also cannot branch out of a subprogram, as the following illustration shows:


DECLARE
...
PROCEDURE compute_bonus (emp_id NUMBER) IS
BEGIN
...
GOTO update_row; -- illegal branch out of subprogram
END;
BEGIN
...
<>
UPDATE emp SET...
END;


Finally, the GOTO statement cannot branch from an exception handler into the present block. For illustration, the following GOTO statement is illegal:


DECLARE
...
pe_ratio REAL;
BEGIN
...
SELECT price / NVL(earnings, 0) INTO pe_ratio FROM ...
<>
INSERT INTO stats VALUES (pe_ratio, ...);
EXCEPTION
WHEN ZERO_DIVIDE THEN
pe_ratio := 0;
GOTO insert_row; -- illegal branch into current block
END;


Though, a GOTO statement can branch from an exception handler into the enclosing block.


Related Discussions:- Goto statement - sequential control

Fetching across commits, Fetching Across Commits The FOR UPDATE clause...

Fetching Across Commits The FOR UPDATE clauses acquire exclusive all row locks. All rows are locked when you open the cursor, and when you commit your transaction they are unl

Exception handling, set serveroutput on declare a number(5); b n...

set serveroutput on declare a number(5); b number(5); c number(5); begin a:=&a; b:=&b; c:=a/b; dbms_output.put_line(c); exception when zero_d

Exceptions - syntax, Exceptions An exception is the runtime error or wa...

Exceptions An exception is the runtime error or warning condition that can be predefined or user-defined. The Predefined exceptions are raised implicitly through runtime system

Predicate - sql, Predicate - SQL Consider the declarative sentence-a p...

Predicate - SQL Consider the declarative sentence-a proposition-that is used to introduce this topic:  "Student S1, named Anne, is enrolled on course C1." Recall that th

Deleting objects in pl sql, Deleting Objects You can use the DELETE st...

Deleting Objects You can use the DELETE statement to eradicate objects from an object table. To eradicate objects selectively, you use the WHERE clause, as shown below: BEG

Map and order methods, Map and Order Methods: The values of the scalar...

Map and Order Methods: The values of the scalar datatype like CHAR or REAL have a predefined order that allows them to be compared. While, the instances of an object type has

Theory of panspermia - origin of life, THEO R Y OF PANSPERMIA - Arrh...

THEO R Y OF PANSPERMIA - Arrhenius (1908) postulated the cosmic panspermia theory that claims that organisms existed throughout the universe, and their spores, etc., could

Using exception_init - user-defined exceptions, Using EXCEPTION_INIT T...

Using EXCEPTION_INIT To handle unnamed internal exceptions, you should use the OTHERS handler or the pragma EXCEPTION_INIT. The pragma is a compiler directive that can be th

Implicit cursors, Implicit Cursors The Oracle implicitly opens a curso...

Implicit Cursors The Oracle implicitly opens a cursor to process each SQL statement not related with an explicitly declared cursor. The PL/SQL lets you refer to the most recen

S, Question 1. Update stock levels when the order is cancelled At times, c...

Question 1. Update stock levels when the order is cancelled At times, customers make mistakes in submitting their orders and call to cancel the order. Brewbean’s wants to create a

Write Your Message!

Captcha
Free Assignment Quote

Assured A++ Grade

Get guaranteed satisfaction & time on delivery in every assignment order you paid with us! We ensure premium quality solution document along with free turntin report!

All rights reserved! Copyrights ©2019-2020 ExpertsMind IT Educational Pvt Ltd