1. Welcome to the new MacRumors forums. See our announcement and read our FAQ

pl/sql homework help

Discussion in 'Mac Programming' started by bigMAC28, Aug 7, 2013.

  1. macrumors member

    Hey guys, I have a homework assignment due at midnight central time and im stuck. I have to select the child name and parent name from this table and then output the child name, parent name, parent of the parent, parent of the parent's parent... and so on. Im stuck on the hard part lol. Any help would be appreciated.

    Here's the create table statement:

    CREATE TABLE region
        	PARENT_NAME  	VARCHAR2(30),
        	CHILD_NAME  	VARCHAR2(30)
    INSERT INTO region VALUES('N/A','Asia');
    INSERT INTO region VALUES('N/A','Australia');
    INSERT INTO region VALUES('N/A','Europe');
    INSERT INTO region VALUES('N/A','North America');
    INSERT INTO region VALUES('Asia','China');
    INSERT INTO region VALUES('Asia','Japan');
    INSERT INTO region VALUES('Australia','New South Wales');
    INSERT INTO region VALUES('New South Wales','Sydney');
    INSERT INTO region VALUES('Canada','Ontario');
    INSERT INTO region VALUES('China','Beijing');
    INSERT INTO region VALUES('England','London');
    INSERT INTO region VALUES('Europe','United Kingdom');
    INSERT INTO region VALUES('Illinois','Aurora');
    INSERT INTO region VALUES('Illinois','Cook County');
    INSERT INTO region VALUES('Illinois','Rockford');
    INSERT INTO region VALUES('Wisconsin','Madison'); 
    INSERT INTO region VALUES('Japan','Osaka');
    INSERT INTO region VALUES('Japan','Tokyo');
    INSERT INTO region VALUES('North America','Canada');
    INSERT INTO region VALUES('North America','United States');
    INSERT INTO region VALUES('Ontario','Ottawa');
    INSERT INTO region VALUES('Ontario','Toronto');
    INSERT INTO region VALUES('United States','Colorado');
    INSERT INTO region VALUES('United States','Illinois');
    INSERT INTO region VALUES('United States','Texas');
    INSERT INTO region VALUES('United Kingdom','England'); 
    INSERT INTO region VALUES('Texas','Rockford');
    INSERT INTO region VALUES('Colorado','Aurora');
    INSERT INTO region VALUES('Cook County','Chicago');

    Heres my code so far:

    ACCEPT p_1 PROMPT 'Please enter the region name (Child Name):'
      v_Child_Name REGION.CHILD_NAME%TYPE := '&p_1';
      v_Parent_Name VARCHAR2(40);
      v_column_name VARCHAR2(100);
      v_count       NUMBER := 0;
      -- First cursor
      CURSOR c_get_tables
      CURSOR c_get_everythingelse
        SELECT Parent_name,
      -- Open first cursor
      OPEN c_get_tables;
        v_count := v_count + 1;
        FETCH c_get_tables INTO v_Parent_Name;
        IF v_parent_name ='N/A' THEN --How else would i do this without hardcoding?--
          DBMS_OUTPUT.PUT_LINE('Line '||v_count||'1  <'||Upper(v_child_name)||'>');
          DBMS_OUTPUT.PUT_LINE('Line '||v_count||' 2 '||Upper(v_Parent_name));
        END IF;
      WHEN c_get_tables%NOTFOUND;
        v_count := v_count + 1;
      END LOOP;
      CLOSE c_get_tables;
      DBMS_OUTPUT.PUT_LINE('<<<<< END OUTPUT >>>>>');
    I know its a mess but please bare with me....
  2. macrumors member

    Well, not sure if I'm in time :p

    In fact, this one is not too difficult. It's Hierarchical Queries.

    Look at Oracle reference


    May be something like this (from the link above):

    SELECT last_name, employee_id, manager_id, LEVEL
    FROM employees
    START WITH employee_id = 100
    CONNECT BY PRIOR employee_id = manager_id
    ORDER SIBLINGS BY last_name;
  3. macrumors 68040


    I don't have a pl/sql or pl/pgsql environment available, but my feeling is that this approach is misguided. My inclination would be to do one select to get your child info, print it, then loop selecting from your table where the current parent is equal to the child column. Once you get no results, you exit your loop. I would prefer a null to N/A but whatever.

    I think one query to walk this isn't going to do it, or two. With a fixed depth I can imagine aliasing the table and self-joining, but that's fixed. You want N depth.


    Edit: IANADBA, and never stumbled over heriarchical queries. Huzzah for learning things.
  4. macrumors member

    No your not too late...i think i am supposed to use cursors and loops. The hint he gave me was to open your cursor,...,close your cursor,....,which tells me loop.
  5. Bach2011, Aug 7, 2013
    Last edited: Aug 7, 2013

    macrumors member

    What's the requirement?

    For cursor part, I always prefer FOR LOOP implicit cursor where possible.

    FOR c IN (select * from a) LOOP

    -- do whatever


    When you stated child/ parent/ parent of parent, I believe most professional Oracle developers will think about Hierarchical Queries. :rolleyes:

    I'm just too tired to play around tonight so that's my guess. If it doesn't require to use cursor or loop, I would not try to create something that Oracle has already provided.

    N/A and NULL are not the same, at least in financial world. NULL = unknown value and you cannot compare NULL values.

    Does this example look familiar to you? :D


    If still insist on looping, I would look further on recursive cursor. Or sth like this


    Just saw that Lee1210 is a DBA. I'm an OCP (Developer Track) and have worked on Oracle backend for financial applications for years. Well, I can be wrong....developers always create bugs. :p
  6. macrumors 68040


    IANA -> I am NOT a...
    I haven't done serious database programmability in years, and it generally makes me sad anyway because it marries you to a database platform.
  7. macrumors member

    Oops. I thought it's a typo :p I'm quite bad at acronyms.

    I still have fun with database programming. I worked on Sybase (and some on SQL Server) in the past, but don't like it much. To me, it is easier to move from Oracle to T-SQL. Oracle has provided many features that makes life easier.
  8. macrumors member


    FOR c IN (SELECT parent_name,
    FROM region
    START WITH parent_name = 'N/A'
    CONNECT BY PRIOR child_name = parent_name)

    DBMS_OUTPUT.PUT_LINE(c.parent_name||' '||c.child_name);




    N/A Asia
    Asia China
    China Beijing
    Asia Japan
    Japan Osaka
    Japan Tokyo
    N/A Australia
    Australia New South Wales
    New South Wales Sydney
    N/A Europe
    Europe United Kingdom
    United Kingdom England
    .... so on

Share This Page