Oracleの「BULK COLLECT INTO」構文について

###前提・実現したいこと
環境：Oracle11g

SQLの勉強中です。

プロシージャの中で
SELECT INTO ～で複数行を取得する処理を行いたいのですが、
INTOではクエリの結果が1行でなければエラーになる為、
BULK COLLECT INTO を使用してみたいのですが、今一つ使い方がわかりません。

###質問
１．BULK COLLECT INTO で格納するレコード型配列について。
どのサイトを見ても既にあるテーブルのROWTYPEから定義を作っているようですが、
レコード型配列の型宣言時に独自に型定義する方法は有りますでしょうか。
DUALテーブルの様に実体無しで宣言する方法を探しています。

２．BULK COLLECT INTOで格納されたレコードについて
格納したオブジェクトを取り出す際に、格納時に指定したソートキーの順番で取り出せると考えてよいでしょうか？
（配列なので問題ないと思いますが...）

SELECT結果をカーソルで扱えばいいじゃない、と言われそうではありますが、
ご存知の方は情報提供をお願い致します。

行動規範の内容に同意します

回答2件

ベストアンサー

ご質問1について

どのサイトを見ても既にあるテーブルのROWTYPEから定義を作っているようです

例えばこちらの解説ページはいかがでしょうか。RECORD型のTABLE型を宣言するやり方が記載されています。記事中の「コード・リスト5」がそうです。
上記記事のコードとは異なりますが、以下のような感じです。（emp表が必要です。）

sql
1DECLARE
2  TYPE  emp_row_t IS RECORD(
3    empno   emp.empno%TYPE,
4    ename   emp.ename%TYPE,
5    deptno  emp.deptno%TYPE
6  );
7  TYPE emp_list_t IS TABLE OF emp_row_t;
8  emp_list emp_list_t := emp_list_t();
9BEGIN
10  SELECT    e.empno,
11            e.ename,
12            e.deptno
13  BULK COLLECT INTO emp_list
14  FROM      emp e
15  ORDER BY  e.empno;
16  FOR i IN 1..emp_list.COUNT LOOP
17  DBMS_OUTPUT.PUT_LINE(
18    UTL_LMS.FORMAT_MESSAGE(
19      '(i = %d) empno = %s, ename = %s, deptno = %s',
20      i, TO_CHAR(emp_list(i).empno), emp_list(i).ename, TO_CHAR(emp_list(i).deptno)));
21  END LOOP;
22END;

(i = 1) empno = 7369, ename = SMITH, deptno = 20
(i = 2) empno = 7499, ename = ALLEN, deptno = 30
(i = 3) empno = 7521, ename = WARD, deptno = 30
(i = 4) empno = 7566, ename = JONES, deptno = 20
(i = 5) empno = 7654, ename = MARTIN, deptno = 30
(i = 6) empno = 7698, ename = BLAKE, deptno = 30
(i = 7) empno = 7782, ename = CLARK, deptno = 10
(i = 8) empno = 7839, ename = KING, deptno = 10
(i = 9) empno = 7844, ename = TURNER, deptno = 30
(i = 10) empno = 7900, ename = JAMES, deptno = 30
(i = 11) empno = 7902, ename = FORD, deptno = 20
(i = 12) empno = 7934, ename = MILLER, deptno = 10

BULK COLLECT自体は、カーソルの代わりに使うものではありません。組み合わせて使うことも可能です。
5行ずつフェッチする例です。

sql
1DECLARE
2  CURSOR cur_emp IS
3    SELECT    e.empno,
4              e.ename,
5              e.deptno
6    FROM      emp e
7    ORDER BY  e.empno;
8  TYPE emp_list_t IS TABLE OF cur_emp%ROWTYPE;
9  emp_list emp_list_t := emp_list_t();
10  i PLS_INTEGER := 1;
11BEGIN
12  OPEN cur_emp;
13  LOOP
14    FETCH cur_emp BULK COLLECT INTO emp_list LIMIT 5;
15    EXIT WHEN emp_list.COUNT = 0;
16    FOR j IN 1..emp_list.COUNT LOOP
17    DBMS_OUTPUT.PUT_LINE(
18      UTL_LMS.FORMAT_MESSAGE(
19        '(i = %d, j = %d) empno = %s, ename = %s, deptno = %s',
20        i, j, TO_CHAR(emp_list(j).empno), emp_list(j).ename, TO_CHAR(emp_list(j).deptno)));
21    END LOOP;
22    i := i + 1;
23  END LOOP;
24END;

(i = 1, j = 1) empno = 7369, ename = SMITH, deptno = 20
(i = 1, j = 2) empno = 7499, ename = ALLEN, deptno = 30
(i = 1, j = 3) empno = 7521, ename = WARD, deptno = 30
(i = 1, j = 4) empno = 7566, ename = JONES, deptno = 20
(i = 1, j = 5) empno = 7654, ename = MARTIN, deptno = 30
(i = 2, j = 1) empno = 7698, ename = BLAKE, deptno = 30
(i = 2, j = 2) empno = 7782, ename = CLARK, deptno = 10
(i = 2, j = 3) empno = 7839, ename = KING, deptno = 10
(i = 2, j = 4) empno = 7844, ename = TURNER, deptno = 30
(i = 2, j = 5) empno = 7900, ename = JAMES, deptno = 30
(i = 3, j = 1) empno = 7902, ename = FORD, deptno = 20
(i = 3, j = 2) empno = 7934, ename = MILLER, deptno = 10