Results 1 to 2 of 2

Thread: PL/SQL Question

  1. #1
    Join Date
    Dec 2004
    Posts
    1

    Unanswered: PL/SQL Question

    How do I do this?

    Specifications:

    Refer to the SAILORS database

    As we saw in the SAILORS database, a sailor may train at most one other sailor. Also, a sailor may be trained by any number of sailors (including none).

    In this assignment, you are asked to write a PL/SQL program that accepts as input a sailor’s sid and print the depth of the sailor in the is-trained-by tree. More specifically, your program’s output should look like the sample output below.

    Sample output for various inputs

    Input Output

    28 +++++ No such sailor found: 28
    29 +++++ Sailor 29 is at depth 0
    58 +++++ Sailor 58 is at depth 3

    Of course your program should work for any legal instance that may add/delete to/from the sample database

    Deliverables

    1. Your program must look like this:

    SET ECHO ON
    -- Author: <your Name>
    SET SERVEROUTPUT ON
    SET VERIFY OFF
    --------------------------
    < Your program code >


    2. Run your program four times using the inputs: 25, 29, 58, and 32. To do this use the spool option from sqlplus

    3. Send the spooled file using the Digital Drop Box from Blackboard.

    This is what I have so far and it won't work:

    SET ECHO ON
    SET SERVEROUTPUT ON
    SET VERIFY OFF
    ------------------------------------
    DECLARE

    S_SID sailors.sid%TYPE;
    S_Trainee Sailors.trainee%type;
    Savetrainee sailors.sid%type;
    Counterlevel Number NOT NULL := 0;

    Begin

    Select sid, trainee
    INTO S_SID, S_Trainee
    from sailors
    where sid = &&sailorID;

    loop

    Select sid, trainee
    INTO S_SID, S_Trainee
    from sailors
    where sid = S_Trainee;

    Exit When S_Trainee is null;
    CounterLevel := CounterLevel + 1;
    end loop;

    DBMS_OUTPUT.PUT_LINE('+++++ '||S_SID||'Is at depth'||CounterLevel);
    end;
    /
    undefine sailorID

    Thanks in advance, any suggestions?

  2. #2
    Join Date
    Apr 2004
    Posts
    246
    use a select with "connect by" & "start with" clauses - it's purpose is to return hierarchical information. No need for the pl/sql loop
    Give a man a fish, you feed him for a day. Club him over the head with a fish, he'll leave you alone.

Posting Permissions

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