Tips5. CURSORでデータを取得するPL/SQLサンプルコード

共通化, 設計bulk correct, CURSOR, FOR, LOOP, PL/SQL, カーソル, パフォーマンス, 性能, 明示カーソル, 暗黙カーソル

ここではおなじみのemp表からデータを取得し、dbms_output.put_lineでコンソールに出力するというPL/SQLコードを、カーソルを使い分けてコーディングしてみました。
それぞれ特徴があります。

最もスタンダードなOPEN~FETCHカーソルループ

DECLARE
  CURSOR c_emp(p_dno IN VARCHAR2)
  IS
  SELECT empno, ename
  FROM   emp
  WHERE  deptno = p_dno
  ;
  rec_emp c_emp%ROWTYPE;
BEGIN
  OPEN c_emp('10');
  LOOP
    FETCH c_emp INTO rec_emp;  -- コーディングは面倒ですが細かな制御が可能
    EXIT WHEN c_emp%NOTFOUND;  -- データが取れなくなったらループを抜ける
    dbms_output.put_line(rec_emp.empno || ':' || rec_emp.ename);
  END LOOP;
  CLOSE c_emp;
EXCEPTION
  WHEN OTHERS THEN
    IF c_emp%ISOPEN THEN
      CLOSE c_emp;  -- 例外処理でカーソルクローズが必須!
    END IF;
    RAISE;
END;
/

最も実用的なカーソルFORループ(明示カーソル)

--
DECLARE
  CURSOR c_emp(p_dno IN VARCHAR2)
  IS
  SELECT empno, ename
  FROM   emp
  WHERE  deptno = p_dno
  ;
  rec_emp c_emp%ROWTYPE;
BEGIN
  FOR rec_emp IN c_emp('10')  -- 記述がシンプル。
  LOOP
    dbms_output.put_line(rec_emp.empno || ':' || rec_emp.ename);
  END LOOP;  -- クローズの必要も無い
END;
/

カーソルFORループ(暗黙カーソル)

--
DECLARE   -- カーソルの定義は不要
BEGIN  -- FOR文中にカーソルの定義を記述
  FOR rec_emp IN (   -- SQLが宣言部にないので再利用性はありません
    SELECT empno, ename
    FROM   emp
    WHERE  deptno = '10')
  LOOP
    dbms_output.put_line(rec_emp.empno || ':' || rec_emp.ename);
  END LOOP;
END;
/

性能を向上させるためにBULK COLLECTを使用

--
DECLARE
  TYPE emp_rtype IS RECORD (  -- レコード型の定義
    empno emp.empno%TYPE,
    ename emp.ename%TYPE
    );
  TYPE emp_ttype IS TABLE OF emp_rtype;  -- テーブル型の定義
  rec_emp emp_ttype;   -- テーブル型変数の宣言
BEGIN
  SELECT empno, ename
  BULK COLLECT INTO rec_emp   -- BULK COLLECTで一気に取込
  FROM emp
  WHERE deptno = '10'
  ;
  FOR i IN 1..rec_emp.count   -- テーブル型変数の件数分繰り返し
  LOOP
    dbms_output.put_line(rec_emp(i).empno || ':' || rec_emp(i).ename);
  END LOOP;
END;
/

その場その場の状況に応じて、使い分けましょう。