GOTO and Labels

GOTO and Labels:
 

• The “GOTO” statements allows us to branch to a label unconditionally.
• The label, which is enclosed within double angle brackets must precede an executable SQL statement or a PL/SQL block.
• When executed, the GOTO statements transfers control to the labelled statement or block.

Syntax:
    GOTO LabelName;

Scenario: 
SQL>CREATE TABLE product_master
    (product_no varchar2(6) constraint pk_product_pk primary key,
    Description varchar2(20),
    Unit_measure varchar2(20),
    Qty_on_hand number(8),
    Reorder_lvl number(8),
    Cost_price number(10,2),
    Selling_price number(8,2));

SQL>DECLARE
    v_Qtyhand product_master.qty_on_hand%type;
    v_Relevel product_master.reorder_lvl%type;
    v_product_no product_master.product_no%type;
    BEGIN
        v_product_no:=‘&prodno’;
        SELECT qty_no_hand,reorder_lvl INTO
        v_Qtyhand,v_Relevel
        FROM product_master
        WHERE product_no=v_product_no;
        IF v_Qtyhand<v_Relevel> THEN
            GOTO updation;
        ELSE
            GOTO noupdation;
        END IF;
        <<updation>>
        UPDATE product_master SET
        qty_on_hand=qty_on_hand+reorder_lvl
        WHERE  product_no=v_product_no;
        RETURN;
        <<noupdation>>
        display(‘There are enogh product’);
        RETURN;
        END;
 
Note:

• Hence to keeps proper meaning within the sequence “RETURN” should be used…

SQL>DECLARE
        v_Counter NUMBER:=1;
    BEGIN
    LOOP
        INSERT INTO temp_table
        VALUES(v_Counter,’Loop Count’);
        v_Counter:=v_Counter+1;
        IF v_Counter>=50 THEN
        GOTO EndOfLoop;
        END IF;
        END LOOP;
        <<EndOfLoop>>
        INSERT INTO temp_table(Ind) VALUES(‘Done!’);
    END;