Oracle PL/SQL カーソルの使用例

#PL/SQL カーソルについて
PL/SQLにて、通常はSELECT文で抽出したデータを変数に代入して処理をするということがよくありますが、カーソルを使用すると、データベースの表から取り出した複数行を処理することができます。

##カーソル処理の流れ

CREATE [OR REPLACE] PROCEDURE
  ストアドプロシージャ名[(引数名 {IN | OUT | INOUT} データ型,...)]
IS
  カーソルの定義(CURSOR [カーソル名] IS [SELECT文])
BEGIN
  カーソルのオープン(OPEN [カーソル名])
  LOOP
    データの取得(FETCH [カーソル名] INTO [変数名])
  END LOOP;
  カーソルのクローズ(CLOSE [カーソル名])
END;

##カーソルの使用例

1. １件のデータを変数に格納する(カーソル処理とは異なるが、初歩的な事なので記述)
   必ずデータがある事を条件にINTO句で変数に挿入する

2. 配列(索引付き変数)の操作
   項目数分の変数を用意する必要があるが、項目単位の操作が可能(7.のバルクバインド処理にも使える)

3. レコード単位の操作
   FETCH INTO を使って1レコード単位で処理(通常、ループ文としてはあまり使えない)

4. 索引付きレコード単位の操作
   レコード＆項目の２元配列操作が可能

5. カーソル・ループの操作
   OPEN,FETCH,CLOSEが省略可能(３.より便利)

6. 暗黙カーソル
   直前のＳＱＬ実行結果が必要な場合に使える

7. バルクバインド処理(1.のデータを違う表にセット)
   ＳＱＬエンジンを１回しか使用しない為、処理が高速

8. WHILE文を使ったカーソル処理の例

CREATE PROCEDURE CURSOR_PROCEDURE(IX IN NUMBER,RESULT0 OUT CHAR,RESULT1 OUT CHAR,RESULT2 OUT CHAR,RESULT3 OUT CHAR,RESULT4 OUT CHAR,RESULT5 OUT CHAR,RESULT6 OUT CHAR)
IS
    CURSOR TABLE01_C IS SELECT HOGE,FUGA,ROWNUM A FROM TABLE01;

    TYPE TYP_TAB_1 IS TABLE OF TABLE01.HOGE%TYPE INDEX BY BINARY_INTEGER;             --pattern② 
    TYPE TYP_TAB_2 IS TABLE OF TABLE01.FUGA%TYPE INDEX BY BINARY_INTEGER;             --pattern② 
    TYPE TYP_REC_A IS RECORD(HOGE TABLE01.HOGE%TYPE,FUGA TABLE01.FUGA%TYPE,A NUMBER); --pattern③
    TYPE TYP_TAB_B IS TABLE OF TYP_REC_A INDEX BY BINARY_INTEGER;                     --pattern④

    TAB_1 TYP_TAB_1;    --pattern②
    TAB_2 TYP_TAB_2;    --pattern②
    REC_A TYP_REC_A;    --pattern③
    RECTAB_A TYP_TAB_B; --pattern④

    IX1 NUMBER:=1;      --pattern③
    IX2 NUMBER:=1;      --pattern④
    IX3 NUMBER;         --pattern⑥
    IX4 NUMBER:=1;      --pattern⑦
BEGIN

    /* ① １件のデータを変数に格納する(カーソル処理とは異なるが、初歩的な事なので記述) */
    SELECT NVL(MAX(HOGE||FUGA),'NO_DATA')INTO RESULT0 FROM TABLE01 WHERE HOGE || FUGA || SECTION = 
    (SELECT HOGE||FUGA||SECTION FROM (SELECT HOGE,FUGA,SECTION,ROWNUM A FROM TABLE01) WHERE A = IX);


    /* ② 配列(索引付き変数)の操作 */
    FOR TABLE01_R IN TABLE01_C LOOP
        TAB_1(TABLE01_R.A):=TABLE01_R.HOGE;
        TAB_2(TABLE01_R.A):=TABLE01_R.FUGA;
    END LOOP;
    IF NOT(TAB_1.EXISTS(IX))THEN
       RESULT1:='NO_DATA';
    ELSE
       RESULT1:=TAB_1(IX)||TAB_2(IX);
    END IF;


    /* ③ レコード単位の操作 */
    RESULT2:='NO_DATA';
    OPEN TABLE01_C;
    LOOP
        FETCH TABLE01_C INTO REC_A;
        IF IX=IX1 THEN
           RESULT2:=REC_A.HOGE||REC_A.FUGA;
        END IF;
        EXIT WHEN TABLE01_C%NOTFOUND;
        IX1:=IX1+1;
    END LOOP;
    CLOSE TABLE01_C;


    /* ④ 索引付きレコード単位の操作 */
    FOR TABLE01_R IN TABLE01_C LOOP
        RECTAB_A(TABLE01_R.A):=TABLE01_R;
    END LOOP;
    IF NOT(RECTAB_A.EXISTS(IX))THEN
        RESULT3:='NO_DATA';
    ELSE
        RESULT3:=RECTAB_A(IX).HOGE||RECTAB_A(IX).FUGA;
    END IF;


    /* ⑤ カーソル・ループの操作 */
    RESULT4:='NO_DATA';
    FOR TABLE01_R IN TABLE01_C LOOP
        IF IX2=IX THEN
           RESULT4:=TABLE01_R.HOGE||TABLE01_R.FUGA;
           EXIT;
        END IF;
        IX2:=IX2+1;
    END LOOP;


    /* ⑥ 暗黙カーソル */
    UPDATE TABLE01 SET HOGE=HOGE WHERE HOGE || FUGA || SECTION = 
    (SELECT HOGE||FUGA||SECTION FROM (SELECT HOGE,FUGA,SECTION,ROWNUM A FROM TABLE01) WHERE A = IX);
    IF SQL%FOUND THEN
       RESULT5:=SQL%ROWCOUNT;
    ELSE
       RESULT5:='NO_DATA';
    END IF;

    /* ⑦ バルクバインド処理(①のデータを違う表にセット) */
    DELETE FROM TABLE01_TEST;
    FORALL IX3 IN 1..TAB_1.COUNT INSERT INTO TABLE01_TEST VALUES(TAB_1(IX3),TAB_2(IX3));


    /* ⑧ WHILE文を使ったカーソル処理の例 */
    RESULT6:='NO_DATA';
    OPEN TABLE01_C;
    FETCH TABLE01_C INTO REC_A;
    WHILE TABLE01_C%FOUND LOOP
        IF IX=IX4 THEN
           RESULT6:=REC_A.HOGE||REC_A.FUGA;
           EXIT;
        END IF;
        FETCH TABLE01_C INTO REC_A;
        IX4:=IX4+1;
    END LOOP;
    CLOSE TABLE01_C;

EXCEPTION
    WHEN OTHERS THEN ROLLBACK;
    DBMS_OUTPUT.PUT_LINE('Error: '||SQLERRM||'('||SQLCODE||')');
END;
/