CREATE TABLE world_region ( PARENT_NAME VARCHAR2(30) NOT NULL, CHILD_NAME VARCHA
ID: 3826968 • Letter: C
Question
CREATE TABLE world_region
(
PARENT_NAME VARCHAR2(30) NOT NULL,
CHILD_NAME VARCHAR2(30) NOT NULL
);
/
INSERT INTO world_region VALUES('***','Asia');
INSERT INTO world_region VALUES('***','Australia');
INSERT INTO world_region VALUES('***','Europe');
INSERT INTO world_region VALUES('***','North America');
INSERT INTO world_region VALUES('Asia','China');
INSERT INTO world_region VALUES('Asia','Japan');
INSERT INTO world_region VALUES('Australia','New South Wales');
INSERT INTO world_region VALUES('New South Wales','Sydney');
INSERT INTO world_region VALUES('Canada','Ontario');
INSERT INTO world_region VALUES('China','Beijing');
INSERT INTO world_region VALUES('England','London');
INSERT INTO world_region VALUES('Europe','United Kingdom');
INSERT INTO world_region VALUES('Illinois','Aurora');
INSERT INTO world_region VALUES('Illinois','Cook County');
INSERT INTO world_region VALUES('Illinois','Rockford');
INSERT INTO world_region VALUES('Wisconsin','Madison');
INSERT INTO world_region VALUES('Japan','Osaka');
INSERT INTO world_region VALUES('Japan','Tokyo');
INSERT INTO world_region VALUES('North America','Canada');
INSERT INTO world_region VALUES('North America','United States');
INSERT INTO world_region VALUES('Ontario','Ottawa');
INSERT INTO world_region VALUES('Ontario','Toronto');
INSERT INTO world_region VALUES('United States','Colorado');
INSERT INTO world_region VALUES('United States','Illinois');
INSERT INTO world_region VALUES('United States','Texas');
INSERT INTO world_region VALUES('United Kingdom','England');
INSERT INTO world_region VALUES('Texas','Rockford');
INSERT INTO world_region VALUES('Colorado','Aurora');
INSERT INTO world_region VALUES('Cook County','Chicago');
COMMIT;
Based on the REGION table, write a PL/SQL anonymous block that accepts a region name (CHILD_NAME) from the user input and displays all its parent regions.
Using different table/column names will receive 0 points.
You cannot assume that the maximum number of region names is 5.
Make sure that the region names in your output are in the same line and separated by “==>”.
Creating cleanly formatted output is a common programming requirement. The format of your output must match mine EXACTLY. If your output does not match mine EXACTLY (e.g., missing “USER INPUT:”, “MY OUTPUT:”, “[”, “]”, “(1)”, or “==>” in your output), you will lose some points.
Explanation / Answer
Using WHILE LOOP and not the CURSOR
Code:
DECLARE
PARENT_NAME VARCHAR2(30);
CHILD_NAME VARCHAR2(30);
CHILD VARCHAR2(30) = 'Beijing';
SELECT PARENT_NAME,CHILD_NAME
INTO PARENT_NAME, CHILD_NAME
FROM world_region
WHERE CHILD_NAME = @CHILD;
BEGIN
WHILE PARENT_NAME != '***' LOOP
dbms_output.put_line(PARENT_NAME || ' ==> ' || CHILD_NAME);
CHILD := PARENT_NAME;
SELECT PARENT_NAME,CHILD_NAME
INTO PARENT_NAME, CHILD_NAME
FROM world_region
WHERE CHILD_NAME = CHILD;
END LOOP;
END;
Sample output:
Asia==>China
China==>Beijing