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

Packaging cursors, Packaging Cursors   You can split a cursor specific...

Packaging Cursors   You can split a cursor specification from its body for placement in a package. In that way, you can change the cursor body without changing the cursor spec

Calling constructors in pl sql, Calling Constructors: The Calls to a c...

Calling Constructors: The Calls to a constructor are allowed wherever the function calls are allowed. Similarly to the functions, a constructor is called as a section of an ex

Dbms, DBMS: The answer to this question is of course given in of the t...

DBMS: The answer to this question is of course given in of the theory book. This book is concerned with SQL DBMSs and SQL databases in particular. Soon we will be looking a

Natural join - sql, Natural Join - SQL In the absence of NATURAL JOIN...

Natural Join - SQL In the absence of NATURAL JOIN Example has to be replaced by something rather more longwinded, as shown in Example. Example: Joining IS_CALLED and IS_EN

Short-circuit evaluation-pl/sql expressions , Short-Circuit Evaluation ...

Short-Circuit Evaluation When computing a logical expression, the PL/SQL uses short-circuit evaluation. That is, the PL/SQL stops computing the expression as soon as the result

Using cursor attributes - bulk bind performance improvement, Using Cursor A...

Using Cursor Attributes To process the SQL data manipulation statements, the SQL engine must opens an implicit cursor named SQL. This cursor's attributes (%FOUND, %NOTFOUND, %

Parameter and keyword description - fetch statement, Parameter and Keyword ...

Parameter and Keyword Description: cursor_name: This identifies an explicit cursor formerly declared within the present scope. cursor_variable_name: These identif

Develop a job management site, Lightweight system to provide and take info ...

Lightweight system to provide and take info from workers in the field and office, have basic design outlined already just require build and implementation Desired Skills CSS,

Parameter modes - pl sql, Parameter Modes: You do not require to speci...

Parameter Modes: You do not require to specify a parameter mode for the input bind arguments (those used, for illustration, in the WHERE clause) as the mode defaults to IN. Th

Controlling cursor variables, Controlling Cursor Variables You use 3 s...

Controlling Cursor Variables You use 3 statements to control the cursor variable: OPEN-FOR, FETCH, & CLOSE. At First, you OPEN a cursor variable FOR a multi-row query. Then, y

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