oracle取数据限定行数,PL/SQL Challenge 每日一题：2014-2-17 BULL COLLECT批量获取时限制行数...

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

以往旧题索引：

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

http://www.itpub.net/thread-1499223-1-1.html

原始出处：

http://www.plsqlchallenge.com/

作者：Steven Feuerstein

运行环境：SQLPLUS, SERVEROUTPUT已打开

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

我执行了这些语句:

CREATE TABLE plch_data

(

n   NUMBER,

v   VARCHAR2 (100)

)

/

BEGIN

INSERT INTO plch_data

SELECT LEVEL, 'Stuff' || LEVEL

FROM DUAL

CONNECT BY LEVEL < 17501;

COMMIT;

END;

/

哪些选项在执行之后会显示如下文本?

5000

5000

5000

2500

(A)

DECLARE

l_start        INTEGER := 1;

l_end          INTEGER;

l_total_rows   INTEGER;

TYPE data_t IS TABLE OF plch_data%ROWTYPE;

l_data         data_t;

BEGIN

SELECT COUNT (*) INTO l_total_rows FROM plch_data;

LOOP

l_end := l_start + 4999;

SELECT *

BULK COLLECT INTO l_data

FROM plch_data

WHERE n BETWEEN l_start AND l_end;

DBMS_OUTPUT.put_line (l_data.COUNT);

EXIT WHEN l_end >= l_total_rows;

l_start := l_end + 1;

END LOOP;

END;

/

(B)

DECLARE

CURSOR data_cur

IS

SELECT * FROM plch_data;

TYPE data_t IS TABLE OF plch_data%ROWTYPE;

l_data   data_t;

BEGIN

OPEN data_cur;

LOOP

FETCH data_cur BULK COLLECT INTO l_data LIMIT 5000;

EXIT WHEN l_data.COUNT = 0;

DBMS_OUTPUT.put_line (l_data.COUNT);

END LOOP;

CLOSE data_cur;

END;

/

(C)

DECLARE

CURSOR data_cur

IS

SELECT * FROM plch_data;

TYPE data_t IS TABLE OF plch_data%ROWTYPE;

l_data    data_t;

l_limit   PLS_INTEGER := 5000;

BEGIN

OPEN data_cur;

LOOP

FETCH data_cur BULK COLLECT INTO l_data LIMIT l_limit;

EXIT WHEN l_data.COUNT = 0;

DBMS_OUTPUT.put_line (l_data.COUNT);

l_limit := l_limit + 5000;

END LOOP;

CLOSE data_cur;

END;

/