【SQL】カーソル

カーソル

カーソルは、SQLのクエリ結果セットを逐次処理するためのメカニズムです。カーソルを使用することで、複数行の結果セットを1行ずつ処理できます。PL/SQLには明示的カーソルと暗黙的カーソルの2種類があります。

カーソルの種類

• 明示的カーソル: 開発者が明示的に宣言し、制御します。
• 暗黙的カーソル: PL/SQLが自動的に処理するカーソル。SELECT INTO、INSERT、UPDATE、DELETEなどの文で自動的に使用されます。

1. 明示的カーソル

開発者が定義し、制御するカーソルです。カーソルを使用してクエリを実行し、結果セットを1行ずつ処理します。

• CURSOR

  DECLARE
      -- カーソルを宣言します。これはemployeesテーブルからemployee_idとlast_nameを選択します。
      CURSOR emp_cursor IS
          SELECT employee_id, last_name FROM employees;
      
      -- カーソルから取得した値を格納するための変数を宣言します。
      v_emp_id employees.employee_id%TYPE;
      v_last_name employees.last_name%TYPE;
  BEGIN
      -- カーソルを開きます。これにより、カーソルを使用してデータをフェッチできるようになります。
      OPEN emp_cursor;
      
      -- ループを開始します。カーソルから一行ずつデータをフェッチします。
      LOOP
          -- カーソルからデータをフェッチし、変数に格納します。
          FETCH emp_cursor INTO v_emp_id, v_last_name;
          
          -- フェッチしたデータがない場合、ループを終了します。
          EXIT WHEN emp_cursor%NOTFOUND;
          
          -- フェッチしたデータを出力します。
          DBMS_OUTPUT.PUT_LINE('Employee ID: ' || v_emp_id || ', Last Name: ' || v_last_name);
      END LOOP;
      
      -- カーソルを閉じます。これにより、カーソルの使用が終了します。
      CLOSE emp_cursor;
  END;
  

  • FOR句を使用したカーソル
    FOR句を使用することで、カーソルの開閉やフェッチ操作を自動化し、コードが簡潔になります。

  BEGIN
  -- カーソルを定義し、FORループで自動的に開閉を行います。
  FOR emp_record IN (SELECT employee_id, last_name FROM employees) LOOP
      -- フェッチしたデータを出力します。
      DBMS_OUTPUT.PUT_LINE('Employee ID: ' || emp_record.employee_id || ', Last Name: ' || emp_record.last_name);
  END LOOP;
  END;
  

• REF CURSOR: 動的カーソル（REF CURSOR）は、実行時にクエリを変更できるカーソルです。REF CURSORを使用することで、柔軟なクエリ実行が可能になります。

  DECLARE
  -- REF CURSORの型を宣言します。
  TYPE ref_cursor_type IS REF CURSOR;
  v_ref_cursor ref_cursor_type;
  v_emp_id employees.employee_id%TYPE;
  v_last_name employees.last_name%TYPE;
  v_query VARCHAR2(1000);
  v_department_id NUMBER; -- 部門IDを動的に指定するための変数
  BEGIN
  -- ここで部門IDを動的に設定します。例えば、外部から渡された値を使用します。
  v_department_id := 20; -- 例として部門ID 20を使用
  
  -- 動的にクエリを構築します。
  v_query := 'SELECT employee_id, last_name FROM employees WHERE department_id = :dept_id';
  
  -- REF CURSORを開き、クエリを実行します。
  OPEN v_ref_cursor FOR v_query USING v_department_id;  -- 動的に渡された部門IDを使用
  
  -- ループを開始し、REF CURSORからデータをフェッチします。
  LOOP
      FETCH v_ref_cursor INTO v_emp_id, v_last_name;
      EXIT WHEN v_ref_cursor%NOTFOUND;
      DBMS_OUTPUT.PUT_LINE('Employee ID: ' || v_emp_id || ', Last Name: ' || v_last_name);
  END LOOP;
  
  -- REF CURSORを閉じます。
  CLOSE v_ref_cursor;
  END;
  

• 違いと選択のポイント

  • 手動カーソル (CURSOR emp_cursor IS ...):

    カーソルの開閉を明示的に行う必要があります。
    カーソルの属性 (%FOUND, %NOTFOUND, %ROWCOUNT, %ISOPEN) を使用できます。
    より細かい制御が可能です。

  • 自動カーソル (FOR ... IN ...):

    カーソルの開閉やフェッチ操作が自動化されます。
    コードが簡潔で読みやすくなります。
    カーソル属性は使用できません。

  • 動的カーソル (REF CURSOR):

    実行時にクエリを変更できる柔軟性があります。
    より複雑で動的なクエリが必要な場合に適しています。
    カーソルの開閉とフェッチを手動で行う必要があります。

  選択は、使用する場面や必要な制御のレベルに依存します。簡単なループ処理には自動カーソルを使用し、より複雑な制御が必要な場合には手動カーソルや動的カーソルを使用することが一般的です。

カーソル属性

カーソルには、状態を確認するための特別な属性があります。

%FOUND: カーソルがフェッチに成功した場合にTRUEを返す。
%NOTFOUND: カーソルがフェッチに失敗した場合にTRUEを返す。
%ROWCOUNT: カーソルがフェッチした行数を返す。
%ISOPEN: カーソルが開いている場合にTRUEを返す。

• カーソル属性の使用例

DECLARE
    CURSOR emp_cursor IS
        SELECT employee_id, last_name FROM employees;
    v_emp_id employees.employee_id%TYPE;
    v_last_name employees.last_name%TYPE;
BEGIN
    OPEN emp_cursor; -- カーソルを開く
    
    LOOP
        FETCH emp_cursor INTO v_emp_id, v_last_name; -- データをフェッチ
        EXIT WHEN emp_cursor%NOTFOUND; -- データがない場合ループを終了
        DBMS_OUTPUT.PUT_LINE('Employee ID: ' || v_emp_id || ', Last Name: ' || v_last_name); -- データを出力
    END LOOP;
    
    DBMS_OUTPUT.PUT_LINE('Total rows processed: ' || emp_cursor%ROWCOUNT); -- フェッチした行数を出力
    
    CLOSE emp_cursor; -- カーソルを閉じる
END;

2. 暗黙的カーソル

暗黙的カーソルは、PL/SQLが自動的に処理するカーソルです。通常、SELECT INTO、INSERT、UPDATE、DELETEなどのSQL文が実行されるたびに暗黙的に使用されます。開発者が明示的に宣言したり制御する必要はありません。

• 特徴
  • PL/SQLが自動的に管理する。
  • SELECT INTO、INSERT、UPDATE、DELETEなどの文で暗黙的に使用される。
  • カーソルの開閉やフェッチはPL/SQLが自動的に行う。
• 暗黙的カーソルの使用

DECLARE
    v_emp_id employees.employee_id%TYPE;
    v_last_name employees.last_name%TYPE;
BEGIN
    -- 暗黙的カーソルを使用して1行のデータをフェッチ
    SELECT employee_id, last_name INTO v_emp_id, v_last_name
    FROM employees
    WHERE department_id = 10 AND ROWNUM = 1;
    
    -- フェッチしたデータを出力
    DBMS_OUTPUT.PUT_LINE('Employee ID: ' || v_emp_id || ', Last Name: ' || v_last_name);
END;