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

Selecting objects in pl sql, Selecting Objects: Suppose that you have ...

Selecting Objects: Suppose that you have run the SQL*Plus script below that creates object type Person and object table persons, and that you have settled the table: CREATE

Cursor variables, Cursor Variables Similar to a cursor, cursor variable...

Cursor Variables Similar to a cursor, cursor variable points to the current row in the result set of a multi-row query. But, dissimilar a cursor, a cursor variable can be opene

Parameter and keyword description - records, Parameter and Keyword Descript...

Parameter and Keyword Description: record_type_name: This identifies the user-defined type specifier that is used in the subsequent declarations of the records. NOT N

Keyword & parameter description - expressions, Keyword & Parameter Descript...

Keyword & Parameter Description: boolean_expression: This is an expression which yields the Boolean value that is TRUE, FALSE, & NULL. character_expression: This

Left and right joins, Left and Right Joins LEFT OUTER JOIN can be used...

Left and Right Joins LEFT OUTER JOIN can be used when you want to retrieve the data from the main table (table1) even if there is no match in other tables (table_2, table_3...

Set operators - sql operators, Set Operators The Set operators combine...

Set Operators The Set operators combine the results of the two queries into one result. The INTERSECT returns all the distinct rows selected by both queries. The MINUS returns

Conditionals - sql, Conditionals - SQL At first sight SQL does not app...

Conditionals - SQL At first sight SQL does not appear to have a single operator for expressing logical implication. In this respect it would be in common with most programming

Processing transactions, Processing Transactions This part describes ho...

Processing Transactions This part describes how to do the transaction processing. You learn the fundamental techniques that safeguard the consistency of your database, involvin

Example of using aggregation on nested tables, Example of Using Aggregation...

Example of Using Aggregation on Nested Tables Example: How many students sat each exam WITH C_ER AS (SELECT CourseId, CAST (TABLE (SELECT DISTINCT StudentId, Mark FROM EXAM

Biconditional - sql, Biconditional - SQL The biconditional p ↔ q can b...

Biconditional - SQL The biconditional p ↔ q can be expressed in Tutorial D by p = q and the same is true of SQL. The question then arises as to whether, in SQL, p = q is equiv

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