oracle 批量读,PL/SQL Challenge 每日一题：2016-11-23 批量读取数据

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

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

以往旧题索引：

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

原始出处：

http://www.plsqlchallenge.com/

作者：Steven Feuerstein

运行环境：SQLPLUS, SERVEROUTPUT已打开

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

我执行了这些语句：

CREATE TABLE plch_parts

(

partnum    INTEGER PRIMARY KEY,

partname   VARCHAR2 (100) UNIQUE

)

/

BEGIN

INSERT INTO plch_parts

VALUES (1, 'Mouse');

INSERT INTO plch_parts

VALUES (100, 'Keyboard');

INSERT INTO plch_parts

VALUES (500, 'Monitor');

INSERT INTO plch_parts

VALUES (501, 'No-res Webcam');

INSERT INTO plch_parts

VALUES (502, 'Ultraviolet Switch');

INSERT INTO plch_parts

VALUES (1000, 'Super Cable');

INSERT INTO plch_parts

VALUES (1001, 'USB17 Port');

INSERT INTO plch_parts

VALUES (1002, 'Bluetoothy Thing');

COMMIT;

END;

/

CREATE OR REPLACE PACKAGE plch_pkg AUTHID DEFINER

IS

TYPE array_t IS TABLE OF plch_parts.partname%TYPE

INDEX BY PLS_INTEGER;

END;

/

哪些选项在执行之后会显示 "Count=8" ?

(A)

DECLARE

l_partnames   plch_pkg.array_t;

CURSOR parts_cur

IS

SELECT partname

FROM plch_parts

ORDER BY partnum;

BEGIN

OPEN parts_cur;

LOOP

FETCH parts_cur BULK COLLECT INTO l_partnames LIMIT 2;

EXIT WHEN parts_cur%NOTFOUND;

END LOOP;

DBMS_OUTPUT.put_line ('Count='||l_partnames.COUNT);

CLOSE parts_cur;

END;

/

(B)

DECLARE

l_partnames1   plch_pkg.array_t;

l_partnames2   plch_pkg.array_t;

CURSOR parts_cur

IS

SELECT partname

FROM plch_parts

ORDER BY partnum;

BEGIN

OPEN parts_cur;

LOOP

FETCH parts_cur BULK COLLECT INTO l_partnames1 LIMIT 2;

FOR indx IN 1 .. l_partnames1.COUNT

LOOP

l_partnames2 (l_partnames2.COUNT + 1) := l_partnames1 (indx);

END LOOP;

EXIT WHEN parts_cur%NOTFOUND;

END LOOP;

DBMS_OUTPUT.put_line ('Count='||l_partnames2.COUNT);

CLOSE parts_cur;

END;

/

(C)

DECLARE

l_partnames   plch_pkg.array_t;

l_count INTEGER;

CURSOR parts_cur

IS

SELECT partname

FROM plch_parts

ORDER BY partnum;

BEGIN

SELECT COUNT (*) INTO l_count FROM plch_parts;

OPEN parts_cur;

LOOP

FETCH parts_cur BULK COLLECT INTO l_partnames;

EXIT WHEN l_partnames.COUNT = l_count;

END LOOP;

DBMS_OUTPUT.put_line ('Count='||l_partnames.COUNT);

CLOSE parts_cur;

END;

/

(D)

DECLARE

l_partnames   plch_pkg.array_t;

BEGIN

SELECT partname

BULK COLLECT INTO l_partnames

FROM plch_parts

ORDER BY partnum;

DBMS_OUTPUT.put_line ('Count='||l_partnames.COUNT);

END;

/