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

2020-02-04

ここではおなじみのemp表からデータを取得し、dbms_output.put_lineでコンソールに出力するというPL/SQLコードを、カーソルを使い分けてコーディングしてみました。
それぞれ特徴があります。
最近はサーバリソースに余裕が出てきたこともあるのか、4.BULK COLLECTを使用するケースが増えていると思います。
それぞれ特徴がありますので、その場その場の状況に応じて、使い分けましょう。

1. 最もスタンダードなOPEN～FETCHカーソルループ

最近はあまり使われることがないと思われる記述方法です。
ループ処理を細かく制御したい場合に使用します。
また、敢えてループせずにレコードを1件のみ取得したい場合に使用したりします。

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;
/

 

2. 最も実用的なカーソル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;
/

 

3. カーソルFORループ（暗黙カーソル）

FOR文中にSQLを記述します。暗黙カーソルになりますので、SQLが可変となる場合に使用します。列数が不定の場合に使用するのかもしれませんが、仕様がわかりづらくなると思いますので、極力避けたいところです（WHERE句が可変となる場合は暗黙カーソルでも対応可能です）。

--
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;
/

 

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

最近はこの記述がスタンダードかもしれません。DBへのアクセスが1回のみで全件メモリにデータが載ってくるので、性能面ではメリットがあります。
事前のレコード型・テーブル型変数の定義が少々手間なのがたまに傷です。

--
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;
/

上手く使い分けましょう。