oracle套嵌游标写法,PL/SQL Challenge 每日一题：2017-1-18 嵌套游标

最先答对且答案未经编辑的puber将获得纪念章一枚(答案不可编辑但可发新贴补充或纠正)，其他会员如果提供有价值的分析、讨论也可获得纪念章一枚。

每两周的优胜者可获得itpub奖励的技术图书一本。

以往旧题索引：

http://www.itpub.net/forum.php?m ... eid&typeid=1808

原始出处：

http://www.plsqlchallenge.com/

作者：Steven Feuerstein

运行环境：SQLPLUS, SERVEROUTPUT已打开

注：本题给出答案时候要求给予简要说明才能得到奖品

我执行了下列语句:

CREATE TABLE plch_locations

(

location_id   NUMBER (4),

city          VARCHAR2 (30)

)

/

CREATE TABLE plch_departments

(

department_id     NUMBER (4),

department_name   VARCHAR2 (30),

location_id       NUMBER (4)

)

/

CREATE TABLE plch_employees

(

employee_id     NUMBER (6),

last_name       VARCHAR2 (25),

department_id   NUMBER (4)

)

/

BEGIN

INSERT INTO plch_locations VALUES (1, 'Chicago');

INSERT INTO plch_locations VALUES (2, 'Bangalore');

INSERT INTO plch_locations VALUES (3, 'Sao Paolo');

INSERT INTO plch_departments VALUES (10, 'Construction', 1);

INSERT INTO plch_departments VALUES (20, 'Destruction', 2);

INSERT INTO plch_departments VALUES (30, 'Weaving', 3);

INSERT INTO plch_employees VALUES (100, 'Jimminycricket', 10);

INSERT INTO plch_employees VALUES (200, 'Flibbidywidget', 20);

INSERT INTO plch_employees VALUES (300, 'Supercallafradja', 30);

COMMIT;

END;

/

用每个选项的代码取代下列代码中的"##REPLACE##"：

DECLARE

CURSOR all_in_one_cur

IS

SELECT l.city,

CURSOR (SELECT d.department_name,

CURSOR (SELECT e.last_name

FROM plch_employees e

WHERE e.department_id = d.department_id)

AS ename

FROM plch_departments d

WHERE l.location_id = d.location_id)

AS dname

FROM plch_locations l

ORDER BY l.city;

department_cur   SYS_REFCURSOR;

employee_cur     SYS_REFCURSOR;

l_city           plch_locations.city%TYPE;

l_dname          plch_departments.department_name%TYPE;

l_ename          plch_employees.last_name%TYPE;

BEGIN

OPEN all_in_one_cur;

##REPLACE##

END;

/

哪些选项使得代码块执行之后会显示下列文本:

Bangalore Destruction Flibbidywidget

Chicago Construction Jimminycricket

Sao Paolo Weaving Supercallafradja

(A)

LOOP

FETCH all_in_one_cur INTO l_city, department_cur;

EXIT WHEN all_in_one_cur%NOTFOUND;

LOOP

FETCH department_cur INTO l_dname, employee_cur;

EXIT WHEN department_cur%NOTFOUND;

LOOP

FETCH employee_cur INTO l_ename;

EXIT WHEN employee_cur%NOTFOUND;

DBMS_OUTPUT.put_line (l_city || ' ' || l_dname || ' ' || l_ename);

END LOOP;

END LOOP;

END LOOP;

(B)

LOOP

FETCH all_in_one_cur INTO l_city, department_cur;

EXIT WHEN all_in_one_cur%NOTFOUND;

LOOP

FETCH department_cur INTO l_dname, employee_cur;

EXIT WHEN department_cur%NOTFOUND;

LOOP

FETCH employee_cur INTO l_ename;

EXIT WHEN employee_cur%NOTFOUND;

DBMS_OUTPUT.put_line (l_city || ' ' || l_dname || ' ' || l_ename);

END LOOP;

CLOSE employee_cur;

END LOOP;

CLOSE department_cur;

END LOOP;

CLOSE all_in_one_cur;

(C)

LOOP

FETCH all_in_one_cur INTO l_city, department_cur;

EXIT WHEN all_in_one_cur%NOTFOUND;

OPEN department_cur;

LOOP

FETCH department_cur INTO l_dname, employee_cur;

EXIT WHEN department_cur%NOTFOUND;

OPEN employee_cur;

LOOP

FETCH employee_cur INTO l_ename;

EXIT WHEN employee_cur%NOTFOUND;

DBMS_OUTPUT.put_line (l_city || ' ' || l_dname || ' ' || l_ename);

END LOOP;

CLOSE employee_cur;

END LOOP;

CLOSE department_cur;

END LOOP;

CLOSE all_in_one_cur;