アプリケーション開発の手引き

カーソルを用いた複数行の選択

SQL では、アプリケーションが行のセットを取り出すことができるようにするため、 カーソル という手法を用います。

カーソルの概念を理解しやすくするために、データベース・マネージャーが結果表 を作成し、そこに SELECT ステートメントを実行して検索されたすべての行を保持する場合を考えてみてください。カーソルを用いて結果表の現在行 を識別して指示することにより、その表からの行をアプリケーションで使用できるようにします。カーソルを使用すると、アプリケーションは結果表から各行を順次取り出すことができ、最終的にはデータの終わり状態、すなわち NOT FOUND 状態、SQLCODE +100 (SQLSTATE 02000) になります。 SELECT ステートメントを実行した結果取り出された行のセットは、0、1、またはそれ以上の行で構成されます。これは探索条件を満たす行数によって決まります。

カーソル処理に必要な手順は以下のとおりです。

  1. DECLARE CURSOR ステートメントを用いてカーソルを指定する。
  2. OPEN ステートメントを用いて照会を実行し、結果表を作成する。
  3. FETCH ステートメントを用いて行を一度に 1 行ずつ取り出す。
  4. DELETE または UPDATE ステートメントを用いて行を処理する (必要な場合)。
  5. CLOSE ステートメントを用いて行を終了する。

アプリケーションは同時に複数のカーソルを使用することができます。各カーソルには DECLARE CURSOR、OPEN、CLOSE、および FETCH ステートメントのセットが必要です。

アプリケーションが一連の行を選択し、カーソルを用いて一度に 1 行ずつ処理する方法の例については、 例: カーソル・プログラムを参照してください。

カーソルの宣言と使用

DECLARE CURSOR ステートメントはカーソルを定義、命名し、 SELECT ステートメントを用いて取り出した行のセットを識別します。

アプリケーションはカーソルに名前を割り当てます。この名前は、その後に続く OPEN、FETCH、および CLOSE ステートメントで参照されます。照会とは、任意の有効な選択ステートメントです。

カーソル・ステートメントの宣言は、静的 SELECT ステートメントに関連する DECLARE ステートメントを示しています。

言語
ソース・コード例

C/C++
EXEC SQL DECLARE C1 CURSOR FOR
  SELECT PNAME, DEPT FROM STAFF 
  WHERE JOB=:host_var;

Java (SQLJ)
#sql iterator cursor1(host_var data type);
#sql cursor1 = { SELECT PNAME, DEPT FROM STAFF
                 WHERE JOB=:host_var };

COBOL
EXEC SQL DECLARE C1 CURSOR FOR 
  SELECT NAME, DEPT FROM STAFF 
    WHERE JOB=:host-var END-EXEC.

FORTRAN
 EXEC SQL DECLARE C1 CURSOR FOR
+  SELECT NAME, DEPT FROM STAFF 
+  WHERE JOB=:host_var
注:DECLARE ステートメントの位置は自由ですが、最初に使用するカーソルの位置よりも上に置く必要があります。

カーソルおよび作業単位に関する考慮事項

COMMIT または ROLLBACK 操作のカーソルのアクションは、カーソルがどのように定義されているかによって異なります。

読み取り専用カーソル

カーソルが読み取り専用であると判別され、かつ反復可能読み取り分離レベルを使用する場合、作業単位に必要なシステム表上に反復可能読み取りロックは依然として集められ保持されます。そのため、アプリケーションは読み取り専用カーソルの場合でも定期的に COMMIT ステートメントを発行することが重要です。

WITH HOLD オプション

アプリケーションが COMMIT ステートメントを発行してある作業単位を完了すると、 すべてのオープン・カーソル (WITH HOLD オプションを使用して宣言されるものを除く) は、データベース・マネージャーにより自動的にクローズされます。

WITH HOLD で宣言されたカーソルは、複数の作業単位間でアクセスするリソースを保持します。カーソルを WITH HOLD で宣言した場合の影響は、作業単位がどのように終了するかによって決まります。

作業単位が COMMIT ステートメントで終了する場合、 WITH HOLD で定義されたオープン・カーソルは OPEN のままです。カーソルは結果表の次の論理行の前に置かれます。さらに、WITH HOLD で定義された OPEN カーソルを参照する準備済みのステートメントも保存されます。 COMMIT の直前にある、特定のカーソルに関連する FETCH および CLOSE 要求だけが有効です。 UPDATE WHERE CURRENT OF および DELETE WHERE CURRENT OF ステートメントは、同じ作業単位内で取り出された行の場合に限り有効です。作業単位中にパッケージが再バインドされると、保留されたカーソルはすべてクローズします。

作業単位が ROLLBACK ステートメントで終了する場合、オープン・カーソルはすべてクローズされ、作業単位の間に獲得したロックはすべて解放され、その作業単位での処理に依存する準備済みステートメントはすべて消去されます。

たとえば、TEMPL 表に 1000 項目が入っており、すべての従業員の給与列を更新するとします。 100 行更新するたびに COMMIT ステートメントを発行するようにします。

  1. WITH HOLD オプションを用いてカーソルを宣言する。
    EXEC SQL DECLARE EMPLUPDT CURSOR WITH HOLD FOR
      SELECT EMPNO, LASTNAME, PHONENO, JOBCODE, SALARY
      FROM TEMPL FOR UPDATE OF SALARY
    
  2. カーソルをオープンし、一度に 1 行ずつ結果表からデータを取り出す。
    EXEC SQL OPEN EMPLUPDT
      .
      .
      .
     
    EXEC SQL FETCH EMPLUPDT
      INTO :upd_emp, :upd_lname, :upd_tele, :upd_jobcd, :upd_wage,
    
  3. 行を更新または削除する場合は、 WHERE CURRENT OF オプションを用いて UPDATE または DELETE ステートメントを使用する。たとえば、現在行を更新するには、プログラムは以下のようにすることができます。
    EXEC SQL UPDATE TEMPL SET SALARY = :newsalary
      WHERE CURRENT OF EMPLUPDT
    
  4. COMMIT を発行した後、別の行を更新する前に FETCH を発行しなければならない。

アプリケーションに SQLCODE -501 (SQLSTATE 24501) を検出し、処理するコードを組み込むようにしてください。この戻り値は、アプリケーションが次のいずれかである場合に、 FETCH または CLOSE ステートメントで戻されることがあります。

アプリケーションがある表を消去したために、その表に依存しているパッケージが無効になると、そのパッケージは動的に再バインドされます。このような場合、データベース・マネージャーがカーソルをクローズするため、 FETCH または CLOSE ステートメントに SQLCODE -501 (SQLSTATE 24501) が戻されます。こうした状況で SQLCODE -501 (SQLSTATE 24501) を処理する方法は、カーソルから行を取り出したいかどうかによって決まります。

WITH RELEASE オプション

アプリケーションが WITH RELEASE オプションを使ってカーソルをクローズすると、 DB2 はカーソルが保持している読み取りロックをすべて解放しようとします。そうすると、カーソルは書き込みロックしか保持できなくなります。アプリケーションが RELEASE オプションを使わないでカーソルをクローズすると、作業単位の完了時に読み取りおよび書き込みロックは解放されます。

例: カーソル・プログラム

以下のサンプル・プログラムは、カーソルを定義して使用する SQL ステートメントを示しています。カーソルは静的 SQL を用いて処理されます。このサンプルは、以下のプログラミング言語で入手可能です。

C
cursor.sqc

Java
Cursor.sqlj

COBOL
cursor.sqb

REXX 言語は、静的 SQL をサポートしないため、サンプルはありません。動的にカーソルを処理する REXX の例については、 例: 動的 SQL プログラムを参照してください。

カーソル・プログラムの動作の仕組み

  1. カーソルを宣言する。 DECLARE CURSOR ステートメントはカーソル c1 を照会に関連付けます。照会は、アプリケーションが FETCH ステートメントを用いて取り出す行を識別します。 staffjob フィールドは、結果表に指定されていなくても更新可能として定義されます。
  2. カーソルをオープンする。カーソル c1 がオープンすると、データベース・マネージャーは照会を実行し、結果表を作成します。カーソルは第 1 行目より前に 置かれます。
  3. 行を取り出す。 FETCH ステートメントはカーソルを次の行に置き、その行の内容をホスト変数に移動します。この行が現在 行になります。
  4. カーソルをクローズする。 CLOSE ステートメントを発行すると、カーソルに関連したリソースが解放されます。ただし、カーソルは再度オープンすることができます。

CHECKERR マクロ / 関数は、プログラム外部にあるエラー検査ユーティリティーです。エラー検査ユーティリティーの所在は、ご使用のプログラミング言語により異なります。

C
DB2 API を呼び出す C プログラムの場合、 utilapi.c 内の sqlInfoPrint 関数は、 utilapi.h 内の API_SQL_CHECK として再定義されます。 C 組み込み SQL プログラムの場合、 utilemb.sqc 内の sqlInfoPrint 関数は、 utilemb.h 内の EMB_SQL_CHECK として再定義されます。

Java
すべての SQL エラーは SQLException としてスローされ、アプリケーションの catch ブロックで処理されます。

COBOL
CHECKERRcheckerr.cbl という名前の外部プログラムです。

FORTRAN
CHECKERRutil.f ファイルにあるサブルーチンです。

このエラー検査ユーティリティーのソース・コードについては、 プログラム例での GET ERROR MESSAGE の使用を参照してください。

C の例: CURSOR.SQC

#include <stdio.h>
#include <stdlib.h>
#include <string.h>
#include "utilemb.h"
EXEC SQL INCLUDE SQLCA;
int main(int argc, char *argv[])
{
   EXEC SQL BEGIN DECLARE SECTION;
      char   pname[10];
      short  dept;
      char userid[9];
      char passwd[19];
   EXEC SQL END DECLARE SECTION;
   printf( "Sample C program: CURSOR \n" );
   if (argc == 1) 
   {
      EXEC SQL CONNECT TO sample;
	  EMB_SQL_CHECK("CONNECT TO SAMPLE");
   }
   else if (argc == 3) 
   { 
      strcpy (userid, argv[1]);
      strcpy (passwd, argv[2]);
      EXEC SQL CONNECT TO sample USER :userid USING :passwd;
      EMB_SQL_CHECK("CONNECT TO SAMPLE");
   }
   else 
   {
      printf ("\nUSAGE: cursor [userid passwd]\n\n");
      return 1;
   } /* endif */
   
   EXEC SQL DECLARE c1 CURSOR FOR  (1)
            SELECT name, dept FROM staff WHERE job='Mgr'
            FOR UPDATE OF job;
   EXEC SQL OPEN c1;  (2)
   EMB_SQL_CHECK("OPEN CURSOR");
   do 
   {
      EXEC SQL FETCH c1 INTO :pname, :dept;  (3)
      if (SQLCODE != 0) break;
      printf( "%-10.10s in dept. %2d will be demoted to Clerk\n",
         pname, dept );
   } while ( 1 );
   EXEC SQL CLOSE c1;  (4)
   EMB_SQL_CHECK("CLOSE CURSOR");
   EXEC SQL ROLLBACK;
   EMB_SQL_CHECK("ROLLBACK"); 
   printf( "\nOn second thought -- changes rolled back.\n" );
   EXEC SQL CONNECT RESET;
   EMB_SQL_CHECK("CONNECT RESET");
   return 0;
}
/* end of program : CURSOR.SQC */

Java の例: Cursor.sqlj

import java.sql.*;
import sqlj.runtime.*;
import sqlj.runtime.ref.*;
#sql iterator CursorByName(String name, short dept) ;
#sql iterator CursorByPos(String, short ) ;
class Cursor 
{   static
  {   try
    {   Class.forName ("COM.ibm.db2.jdbc.app.DB2Driver").newInstance ();
    } 
    catch (Exception e)
    {   System.out.println ("\n  Error loading DB2 Driver...\n");
      System.out.println (e);
      System.exit(1);
    }
  }
  public static void main(String argv[])
  {   try
    {   System.out.println ("  Java Cursor Sample");
      String url = "jdbc:db2:sample";       // URL is jdbc:db2:dbname
      Connection con = null;          
      // Set the connection
      if (argv.length == 0) 
      {   // connect with default id/password
        con = DriverManager.getConnection(url);  
      }
      else if (argv.length == 2)
      {   String userid = argv[0];
        String passwd = argv[1];
        // connect with user-provided username and password
        con = DriverManager.getConnection(url, userid, passwd); 
      }
      else 
      {   throw new Exception("\nUsage: java Cursor [username password]\n");
      } 
      // Set the default context
      DefaultContext ctx = new DefaultContext(con);            
      DefaultContext.setDefaultContext(ctx);
      // Enable transactions
      con.setAutoCommit(false);
      // Using cursors
      try
      {   CursorByName cursorByName;
        CursorByPos  cursorByPos;
        String name = null;
        short  dept=0;
        // Using the JDBC ResultSet cursor method
        System.out.println("\nUsing the JDBC ResultSet cursor method");
        System.out.println(" with a 'bind by name' cursor ...\n");
        #sql cursorByName = { 
              SELECT name, dept FROM staff WHERE job='Mgr' }; (1)
        while (cursorByName.next()) (2)
        {   name = cursorByName.name(); (3)
          dept = cursorByName.dept();
          System.out.print (" name= " + name);
          System.out.print (" dept= " + dept);
          System.out.print ("\n");
        }
        cursorByName.close(); (4)
        // Using the SQLJ iterator cursor method
        System.out.println("\nUsing the SQLJ iterator cursor method");
        System.out.println(" with a 'bind by position' cursor ...\n");
        #sql cursorByPos = { 
               SELECT name, dept FROM staff WHERE job='Mgr' }; (1) (2)
        while (true) 
        {   #sql { FETCH :cursorByPos INTO :name, :dept }; (3)
          if (cursorByPos.endFetch()) break;
          System.out.print (" name= " + name); 
          System.out.print (" dept= " + dept);
          System.out.print ("\n");
        }
        cursorByPos.close(); (4)
      }
      catch( Exception e )
      {   throw e; 
      } 
      finally
      {   // Rollback the transaction
        System.out.println("\nRollback the transaction...");
        #sql { ROLLBACK };
        System.out.println("Rollback done.");
      }
    }
    catch( Exception e )
    {   System.out.println (e);
    }
  }
}

COBOL の例: CURSOR.SQB

       Identification Division.
       Program-ID. "cursor".
       Data Division.
       Working-Storage Section.
           copy "sqlenv.cbl".
           copy "sql.cbl".
           copy "sqlca.cbl".
           EXEC SQL BEGIN DECLARE SECTION END-EXEC.
       01 pname             pic x(10).
       77 dept              pic s9(4) comp-5.
       01 userid            pic x(8).
       01 passwd.
         49 passwd-length   pic s9(4) comp-5 value 0.
         49 passwd-name     pic x(18).
           EXEC SQL END DECLARE SECTION END-EXEC.
       77 errloc          pic x(80).
       Procedure Division.
       Main Section.
           display "Sample COBOL program: CURSOR".
           display "Enter your user id (default none): " 
                with no advancing.
           accept userid.
           if userid = spaces
             EXEC SQL CONNECT TO sample END-EXEC
           else
             display "Enter your password : " with no advancing
             accept passwd-name.
      * Passwords in a CONNECT statement must be entered in a VARCHAR format
      * with the length of the input string.
           inspect passwd-name tallying passwd-length for characters
              before initial " ".
           EXEC SQL CONNECT TO sample USER :userid USING :passwd
              END-EXEC.
           move "CONNECT TO" to errloc.
           call "checkerr" using SQLCA errloc.
           EXEC SQL DECLARE c1 CURSOR FOR                               (1)
                    SELECT name, dept FROM staff
                    WHERE job='Mgr'
                    FOR UPDATE OF job END-EXEC.
           EXEC SQL OPEN c1 END-EXEC.                                   (2)
           move "OPEN CURSOR" to errloc.
           call "checkerr" using SQLCA errloc.
           perform Fetch-Loop thru End-Fetch-Loop
              until SQLCODE not equal 0.
           EXEC SQL CLOSE c1 END-EXEC.                                  (4)
           move "CLOSE CURSOR" to errloc.
           call "checkerr" using SQLCA errloc.
           EXEC SQL ROLLBACK END-EXEC.
           move "ROLLBACK" to errloc.
           call "checkerr" using SQLCA errloc.
           DISPLAY "On second thought -- changes rolled back.".
           EXEC SQL CONNECT RESET END-EXEC.
           move "CONNECT RESET" to errloc.
           call "checkerr" using SQLCA errloc.
       End-Main.
           go to End-Prog.
       Fetch-Loop Section.
           EXEC SQL FETCH c1 INTO :PNAME, :DEPT END-EXEC.               (3)
           if SQLCODE not equal 0
              go to End-Fetch-Loop.
           display pname, " in dept. ", dept,
              " will be demoted to Clerk".
       End-Fetch-Loop. exit.
       End-Prog.
           stop run.


[ ページのトップ | 前ページ | 次ページ | 目次 | 索引 ]