Mac pl/sql homework help

[bigMAC28]

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

Heres my code so far:

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

 

[Bach2011]

Well, not sure if I'm in time

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;

 

[lee1210]

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.

 

[bigMAC28]

Well, not sure if I'm in time

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;

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.

 

[Bach2011]

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.

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?

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.

 

[lee1210]

What's the requirement?
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.

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.

 

[Bach2011]

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.

Oops. I thought it's a typo 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.

 

[Bach2011]

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