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

    #1
    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:

    Code:
    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');
    COMMIT;
    

    Heres my code so far:

    Code:
    ACCEPT p_1 PROMPT 'Please enter the region name (Child Name):'
    DECLARE
      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
      IS
        SELECT PARENT_NAME FROM REGION WHERE UPPER(CHILD_NAME) = UPPER(v_Child_Name);
      CURSOR c_get_everythingelse
        SELECT Parent_name,
          Child_name
        FROM REGION
        WHERE BEGIN DBMS_OUTPUT.PUT_LINE('<<<<< BEGIN OUTPUT >>>>>');
      --DBMS_OUTPUT.PUT_LINE('<'||V_CHILD_NAME||'>');
      -- Open first cursor
      OPEN c_get_tables;
      LOOP
        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)||'>');
        ELSE
          DBMS_OUTPUT.PUT_LINE('Line '||v_count||' 2 '||Upper(v_Parent_name));
          EXIT;
        END IF;
        EXIT
      WHEN c_get_tables%NOTFOUND;
        v_count := v_count + 1;
      END LOOP;
      CLOSE c_get_tables;
      DBMS_OUTPUT.PUT_LINE('<<<<< END OUTPUT >>>>>');
    END;
    
    I know its a mess but please bare with me....
     
  2. macrumors member

    #2
    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

    http://docs.oracle.com/cd/B19306_01/server.102/b14200/queries003.htm

    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

    lee1210

    #3
    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.

    -Lee

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

    #4
    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

    #5
    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

    END LOOP;

    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

    http://stackoverflow.com/questions/12720269/recursion-oracle-pl-sql-using-cursors

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

    http://www.techonthenet.com/oracle/questions/cursor2.php

    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

    lee1210

    #6
    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

    #7
    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

    #8
    BEGIN

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

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

    END LOOP;

    END;

    ----------------------------------------------------

    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