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;
.
RUNHello,

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