Results 1 to 2 of 2
  1. #1
    Join Date
    Dec 2002
    Posts
    3

    Unanswered: Questions on WHILE LOOP in PL/SQL

    I have developed the piece of script but the look up part wont work properly so i wonder is there a way of doing a lookup all the way from the bottom level to the top level. Thanks in advance.






    SET SERVEROUTPUT ON
    SET VERIFY OFF
    ------------------------------------


    ACCEPT sailorID NUMBER PROMPT 'Enter sailor id: '

    DECLARE
    L number;
    X number;
    p_sid sailors.sid%TYPE;
    p_trainee sailors.trainee%TYPE;

    BEGIN
    SELECT sid, trainee
    INTO p_sid, p_trainee
    FROM sailors
    WHERE sid = &sailorID;

    L := 0;
    X :=&SailorID;

    WHILE p_sid = X and p_trainee != 99 LOOP

    X := p_trainee;
    DBMS_OUTPUT.PUT_LINE('+++++ SailorID '||p_sid||' Train '||p_trainee);
    DBMS_OUTPUT.PUT_LINE('loop was executed');

    L := L+1;
    p_trainee := 97;
    p_trainee := p_trainee + 1;
    END LOOP;
    DBMS_OUTPUT.PUT_LINE('LEVEL: ' || L);
    -------------------------------------------------
    EXCEPTION
    WHEN NO_DATA_FOUND THEN
    DBMS_OUTPUT.PUT_LINE ('++++ Error !!!! '||&sailorID||' is not a
    valid ID');
    WHEN OTHERS THEN
    DBMS_OUTPUT.PUT_LINE('+++++ '||SQLCODE||' ... '||SQLERRM);
    END;
    .
    RUN
    Attached Files Attached Files

  2. #2
    Join Date
    Jul 2001
    Location
    Germany
    Posts
    189

    Define Cursor

    Hello,

    one way to develop a lookup is to declare a cursor. This is the standard
    way to declare a cursor:

    ACCEPT sailorID NUMBER PROMPT 'Enter sailor id: '

    DECLARE
    CURSOR cuLookup IS
    SELECT sid, trainee
    INTO p_sid, p_trainee
    FROM sailors
    WHERE sid = &sailorID;

    L number;
    X number;
    p_sid sailors.sid%TYPE;
    p_trainee sailors.trainee%TYPE;
    rLookup cuLookup%ROWTYPE;

    BEGIN

    L := 0;
    X :=&SailorID;

    OPEN cuLookup;
    FETCH cuLookup INTO rLookup;

    WHILE cuLookup%FOUND LOOP
    END LOOP;

    CLOSE(cuLookup);

    EXCEPTION
    WHEN OTHERS THEN
    IF cuLookup%ISOPEN THEN
    CLOSE cuLookup;
    END IF;
    END;


    There ar several other ways to do a cursor fetch f.e. you can use a direct declarion in a for statement. Look into the oracle manual to get further details.

    Hope that helps ?

    Manfred Peter
    (Alligator Company GmbH)
    http://www.alligatorsql.com

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •