Heiarchal Retrieval W/Cursors
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 3 of 3

Thread: Heiarchal Retrieval W/Cursors

  1. #1
    Join Date
    Feb 2010
    Posts
    1

    Heiarchal Retrieval W/Cursors

    Here is my problem:

    • Write a PLSQL anonymous block stored in file TASK3.SQL that does the
      following:
      • Displays in ascending last name order all employees who have other employees reporting
      • to them.
      • Asks the user for the last name and first name of one of those managers
      • Displays the level, last name and first name of the employees reporting to that manager.


      NOTE: To receive full credit, the code listing the employees reporting to a manager
      must be written with a CURSOR. You may receive up to 75% credit by writing the
      display as a simple SQL statement.



    here are my employees:
    EMPID, Last_Name, First_Name, Manager_id, Job_title, salary, dept_id

    1, 'King', 'Gary', NULL, 'OWNER', 80000, 100);
    2, 'Mont', 'Larry', 1, 'MANAGER', 50000, 200);
    3, 'Poppines', 'Mary', 1, 'MANAGER', 60000, 300);
    4, 'DOe', 'John', 2, 'SALESREP', 35000, 200);
    5, 'Que', 'Sue', 2, 'ACCTCLERK', 30000, 200);
    6, 'Phillapelli', 'Karen', 3, 'MECHANIC', 45000, 300);
    7, 'Jungle', 'George', 3, 'PARTSCLERK', 32000, 300);



    So this is what I have so far:

    CREATE OR REPLACE FUNCTION get_manid
    (p_last IN employee.last_name%TYPE,
    p_first IN employee.first_name%TYPE)
    RETURN NUMBER
    IS
    v_manager_id employee.manager_id%TYPE;
    BEGIN
    SELECT employee_id
    INTO v_manager_id
    FROM employee
    WHERE last_name LIKE p_last
    AND first_name LIKE p_first;

    RETURN v_manager_id;
    END get_manid;
    /

    DECLARE
    CURSOR emp_cursor IS
    SELECT LEVEL, last_name, first_name
    FROM employee
    START WITH employee_id = get_manid('&lastname', '&firstname')
    CONNECT BY PRIOR employee_id = manager_id;

    v_level NUMBER;
    v_last VARCHAR2(15);
    v_first VARCHAR2(15);

    BEGIN
    OPEN emp_cursor;

    LOOP
    FETCH emp_cursor INTO v_level, v_last, v_first;
    EXIT WHEN emp_cursor%NOTFOUND;
    DBMS_OUTPUT.PUT_LINE('LEVEL: '|| v_level
    ||' Last Name: ' || v_last
    ||' First Name: ' || v_first);
    END LOOP;
    CLOSE emp_cursor;
    END;
    /



    My problem: I can't ever get the right output. The way it is written right now shows all the employees and their levels. If I mess with it (I'm not sure what I'm doing), I can get it to show only the manager, which is not what I want. I want it to show, say if I chose the owner (KING), all the people that report to him (MONT POPPINS I believe) and their respective levels(2).

    Any suggestions?

  2. #2
    Join Date
    Jul 2002
    Location
    Lake Worth, FL
    Posts
    1,480

    Cool Check for errors

    Your code works for me, perhaps the issue lies between the chair and the keyboard.
    Code:
    SQL> CREATE OR REPLACE FUNCTION Get_manid
      2       (p_last   IN employees.last_name%TYPE,
      3        p_first  IN employees.first_name%TYPE)
      4  RETURN NUMBER
      5  IS
      6    v_manager_id     employees.manager_id%TYPE;
      7  BEGIN
      8    SELECT employee_id
      9    INTO      v_manager_id
     10    FROM      employees
     11    WHERE  last_name LIKE p_last
     12           AND first_name LIKE p_first;
     13
     14    RETURN v_manager_id;
     15  END get_manid;
     16  /
    
    Function created.
    
    SQL> set serverout on size 1000000;
    SQL> def lastname='King'
    SQL> def firstname='Steven'
    SQL> DECLARE
      2    CURSOR emp_cursor IS
      3      SELECT LEVEL,
      4             last_name,
      5             first_name
      6      FROM   employees
      7      START WITH employee_id = Get_manid('&lastname','&firstname')
      8      CONNECT BY PRIOR employee_id = manager_id;
      9    v_level  NUMBER;
     10    v_last   VARCHAR2(15);
     11    v_first  VARCHAR2(15);
     12  BEGIN
     13    OPEN emp_cursor;
     14
     15    LOOP
     16      FETCH emp_cursor INTO v_level,v_last,v_first;
     17
     18      EXIT WHEN emp_cursor%NOTFOUND;
     19
     20      dbms_output.Put_line('LEVEL: '
     21                           ||v_level
     22                           ||' Last Name: '
     23                           ||v_last
     24                           ||' First Name: '
     25                           ||v_first);
     26    END LOOP;
     27
     28    CLOSE emp_cursor;
     29  END;
     30  /
    old   7:     START WITH employee_id = Get_manid('&lastname','&firstname')
    new   7:     START WITH employee_id = Get_manid('King','Steven')
    
    LEVEL: 1 Last Name: King First Name: Steven
    LEVEL: 2 Last Name: Hartstein First Name: Michael
    LEVEL: 3 Last Name: Fay First Name: Pat
    LEVEL: 2 Last Name: Kochhar First Name: Neena
    LEVEL: 3 Last Name: Whalen First Name: Jennifer
    LEVEL: 3 Last Name: Mavris First Name: Susan
    LEVEL: 3 Last Name: Baer First Name: Hermann
    LEVEL: 3 Last Name: Higgins First Name: Shelley
    LEVEL: 4 Last Name: Gietz First Name: William
    LEVEL: 3 Last Name: Greenberg First Name: Nancy
    LEVEL: 4 Last Name: Faviet First Name: Daniel
    LEVEL: 4 Last Name: Chen First Name: John
    LEVEL: 4 Last Name: Sciarra First Name: Ismael
    LEVEL: 4 Last Name: Urman First Name: Jose Manuel
    LEVEL: 4 Last Name: Popp First Name: Luis
    LEVEL: 2 Last Name: De Haan First Name: Lex
    LEVEL: 3 Last Name: Hunold First Name: Alexander
    LEVEL: 4 Last Name: Ernst First Name: Bruce
    LEVEL: 4 Last Name: Austin First Name: David
    LEVEL: 4 Last Name: Pataballa First Name: Valli
    LEVEL: 4 Last Name: Lorentz First Name: Diana
    LEVEL: 2 Last Name: Raphaely First Name: Den
    LEVEL: 3 Last Name: Khoo First Name: Alexander
    LEVEL: 3 Last Name: Baida First Name: Shelli
    LEVEL: 3 Last Name: Tobias First Name: Sigal
    LEVEL: 3 Last Name: Himuro First Name: Guy
    LEVEL: 3 Last Name: Colmenares First Name: Karen
    LEVEL: 2 Last Name: Weiss First Name: Matthew
    LEVEL: 3 Last Name: Nayer First Name: Julia
    LEVEL: 3 Last Name: Mikkilineni First Name: Irene
    LEVEL: 3 Last Name: Landry First Name: James
    LEVEL: 3 Last Name: Markle First Name: Steven
    LEVEL: 3 Last Name: Taylor First Name: Winston
    LEVEL: 3 Last Name: Fleaur First Name: Jean
    LEVEL: 3 Last Name: Sullivan First Name: Martha
    LEVEL: 3 Last Name: Geoni First Name: Girard
    LEVEL: 2 Last Name: Fripp First Name: Adam
    LEVEL: 3 Last Name: Bissot First Name: Laura
    LEVEL: 3 Last Name: Atkinson First Name: Mozhe
    LEVEL: 3 Last Name: Marlow First Name: James
    LEVEL: 3 Last Name: Olson First Name: TJ
    LEVEL: 3 Last Name: Sarchand First Name: Nandita
    LEVEL: 3 Last Name: Bull First Name: Alexis
    LEVEL: 3 Last Name: Dellinger First Name: Julia
    LEVEL: 3 Last Name: Cabrio First Name: Anthony
    LEVEL: 2 Last Name: Kaufling First Name: Payam
    LEVEL: 3 Last Name: Mallin First Name: Jason
    LEVEL: 3 Last Name: Rogers First Name: Michael
    LEVEL: 3 Last Name: Gee First Name: Ki
    LEVEL: 3 Last Name: Philtanker First Name: Hazel
    LEVEL: 3 Last Name: Chung First Name: Kelly
    LEVEL: 3 Last Name: Dilly First Name: Jennifer
    LEVEL: 3 Last Name: Gates First Name: Timothy
    LEVEL: 3 Last Name: Perkins First Name: Randall
    LEVEL: 2 Last Name: Vollman First Name: Shanta
    LEVEL: 3 Last Name: Ladwig First Name: Renske
    LEVEL: 3 Last Name: Stiles First Name: Stephen
    LEVEL: 3 Last Name: Seo First Name: John
    LEVEL: 3 Last Name: Patel First Name: Joshua
    LEVEL: 3 Last Name: Bell First Name: Sarah
    LEVEL: 3 Last Name: Everett First Name: Britney
    LEVEL: 3 Last Name: McCain First Name: Samuel
    LEVEL: 3 Last Name: Jones First Name: Vance
    LEVEL: 2 Last Name: Mourgos First Name: Kevin
    LEVEL: 3 Last Name: OConnell First Name: Donald
    LEVEL: 3 Last Name: Grant First Name: Douglas
    LEVEL: 3 Last Name: Rajs First Name: Trenna
    LEVEL: 3 Last Name: Davies First Name: Curtis
    LEVEL: 3 Last Name: Matos First Name: Randall
    LEVEL: 3 Last Name: Vargas First Name: Peter
    LEVEL: 3 Last Name: Walsh First Name: Alana
    LEVEL: 3 Last Name: Feeney First Name: Kevin
    LEVEL: 2 Last Name: Russell First Name: John
    LEVEL: 3 Last Name: Tucker First Name: Peter
    LEVEL: 3 Last Name: Bernstein First Name: David
    LEVEL: 3 Last Name: Hall First Name: Peter
    LEVEL: 3 Last Name: Olsen First Name: Christopher
    LEVEL: 3 Last Name: Cambrault First Name: Nanette
    LEVEL: 3 Last Name: Tuvault First Name: Oliver
    LEVEL: 2 Last Name: Partners First Name: Karen
    LEVEL: 3 Last Name: King First Name: Janette
    LEVEL: 3 Last Name: Sully First Name: Patrick
    LEVEL: 3 Last Name: McEwen First Name: Allan
    LEVEL: 3 Last Name: Smith First Name: Lindsey
    LEVEL: 3 Last Name: Doran First Name: Louise
    LEVEL: 3 Last Name: Sewall First Name: Sarath
    LEVEL: 2 Last Name: Errazuriz First Name: Alberto
    LEVEL: 3 Last Name: Vishney First Name: Clara
    LEVEL: 3 Last Name: Greene First Name: Danielle
    LEVEL: 3 Last Name: Marvins First Name: Mattea
    LEVEL: 3 Last Name: Lee First Name: David
    LEVEL: 3 Last Name: Ande First Name: Sundar
    LEVEL: 3 Last Name: Banda First Name: Amit
    LEVEL: 2 Last Name: Cambrault First Name: Gerald
    LEVEL: 3 Last Name: Ozer First Name: Lisa
    LEVEL: 3 Last Name: Bloom First Name: Harrison
    LEVEL: 3 Last Name: Fox First Name: Tayler
    LEVEL: 3 Last Name: Smith First Name: William
    LEVEL: 3 Last Name: Bates First Name: Elizabeth
    LEVEL: 3 Last Name: Kumar First Name: Sundita
    LEVEL: 2 Last Name: Zlotkey First Name: Eleni
    LEVEL: 3 Last Name: Abel First Name: Ellen
    LEVEL: 3 Last Name: Hutton First Name: Alyssa
    LEVEL: 3 Last Name: Taylor First Name: Jonathon
    LEVEL: 3 Last Name: Livingston First Name: Jack
    LEVEL: 3 Last Name: Grant First Name: Kimberely
    LEVEL: 3 Last Name: Johnson First Name: Charles
    
    PL/SQL procedure successfully completed.
    
    SQL>
    "The person who says it cannot be done should not interrupt the person doing it." --Chinese Proverb

  3. #3
    Join Date
    Mar 2007
    Location
    Ft. Lauderdale, FL
    Posts
    3,554
    Quote Originally Posted by LKBrwn_DBA View Post
    perhaps the issue lies between the chair and the keyboard.
    interface issue?
    Pablo (Paul) Berzukov

    Author of Understanding Database Administration available at amazon and other bookstores.

    Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.

Tags for this Thread

Posting Permissions

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


Click Here to Expand Forum to Full Width