16.20 - DECLARE CURSOR (ストアド プロシージャ形式) - Teradata Vantage NewSQL Engine

Teradata Vantage™ SQL ストアド プロシージャおよび埋め込みSQL

prodname
Teradata Database
Teradata Vantage NewSQL Engine
vrm_release
16.20
category
プログラミング リファレンス
featnum
B035-1148-162K-JPN

目的

カーソルに名前を定義して、割り当てます。

呼び出し

実行不可プログラム。

ストアド プロシージャのみ。

構文



cursor_name
宣言するカーソルの名前を指定します。
SCROLL
宣言したカーソルで、結果セット内の次の行を取り出せるようにするか、結果セット内の任意の場所からそのセットの最初の行を取り出せるようにするかを指定します。
  • SCROLLは、結果セットの次の行に順方向スクロールすることも、結果セットの最初の行に直接スクロールすることもできます。
  • デフォルトのNO SCROLLは、結果セットの次の行への順方向スクロールしかできません。
NO SCROLL
宣言したカーソルで、結果セット内の次の行を取り出せるようにするか、結果セット内の任意の場所からそのセットの最初の行を取り出せるようにするかを指定します。
  • SCROLLは、結果セットの次の行に順方向スクロールすることも、結果セットの最初の行に直接スクロールすることもできます。
  • デフォルトのNO SCROLLは、結果セットの次の行への順方向スクロールしかできません。
WITHOUT RETURN
プロシージャは、結果セットを返しません。
WITHOUT RETURNは、デフォルトです。
WITH RETURN
指定内容:
  • このカーソルは結果セット カーソルです。
  • 結果セットを現在のストアド プロシージャ(カーソルを開いたプロシージャ)と呼び出し側のプロシージャに返します。
WITH RETURN TO CALLER
指定内容:
  • このカーソルは結果セット カーソルです。
  • 結果セットを現在のストアド プロシージャ(カーソルを開いたプロシージャ)と呼び出し側のプロシージャに返します。
WITH RETURN ONLY
指定内容:
  • このカーソルは結果セット カーソルです。
  • 結果セットをストアド プロシージャの呼び出し側にのみ返します。
WITH RETURN ONLY TO CALLER
指定内容:
  • このカーソルは結果セット カーソルです。
  • 結果セットをストアド プロシージャの呼び出し側にのみ返します。
WITH RETURN TO CLIENT
指定内容:
  • このカーソルは結果セット カーソルです。
  • 結果セットをクライアント(BTEQなどのアプリケーション)と現在のストアド プロシージャ(カーソルを開いたプロシージャ)に返します。
WITH RETURN ONLY TO CLIENT
指定内容:
  • このカーソルは結果セット カーソルです。
  • 結果セットをクライアント(BTEQなどのアプリケーション)に返します。
cursor_specification
カーソルが読み取るまたは更新する行を検索するSELECT文。
READ ONLY
結果セット内の行の読み取りだけにカーソルを使用できるように指定します。
これは、デフォルトです。
UPDATE
結果セット内の行を更新または削除するためにカーソルを使用できるように指定します。
UPDATEが指定されると、次のいずれかも指定する必要があります。
  • WITH RETURN ONLY
  • WITH RETURN ONLY TO CALLER
  • WITH RETURN TO CLIENT
  • WITH RETURN ONLY TO CLIENT
statement_name
動的形式のDECLARE CURSOR文を特定します。

ANSI準拠

ONLYキーワードと、TO CALLERおよびTO CLIENTオプションは、ANSI/ISO SQL:2011規格に対するTeradataの拡張機能です。

許可

なし。

DECLARE CURSOR文とFOR文

FOR文でも、カーソルが定義されます。

一般ルール

  • ローカル宣言の後とハンドラー宣言の前にカーソル宣言を指定する必要があります。
  • カーソル名は、同じ複合文の宣言内で固有でなければなりません。
  • スクロール能力のある句を明示的に指定しない場合は、NO SCROLLがデフォルトとなり、カーソルは順方向スクロールのみが可能となります。
  • 更新能力のある句を明示的に指定しない場合は、FOR READ ONLYがデフォルトとなります。
  • 定位置カーソルを作成するには、FOR UPDATE句を明示的に指定します。これにより、カーソルを使用してその結果行に対して削除操作と更新操作ができるようになります。

結果セットを返す場合のルール

  • CREATE/REPLACE PROCEDURE文のDYNAMIC RESULT SETS句で、結果セットの数を指定します。
  • WITH RETURN句を指定すると、ストアド プロシージャは結果セットを現在のプロシージャ、クライアント、または宣言した各結果セット カーソルの呼び出し側に返します。
  • WITH RETURNを指定するということは、WITH RETURN TO CALLERを指定することと同じです。
  • WITH RETURN ONLY を指定するということは、WITH RETURN ONLY TO CALLERを指定することと同じです。
  • WITH RETURN ONLYを指定すると、カーソルを開くストアド プロシージャはカーソルを使用して結果セットから行を取り出すことができません。
  • WITH RETURNまたはWITH RETURN TO CALLERを指定すると、FOR UPDATEを指定できません。
  • TO CLIENTを指定すると、たとえネストされたストアド プロシージャから呼び出された場合でも、結果セットはクライアント アプリケーションに返されます。
  • WITH RETURN ONLY TO CLIENT句を指定すると、ストアド プロシージャは結果セット(複数可)を、対象のプロシージャを呼び出したストアド プロシージャまたは外部ストアド プロシージャではなく、クライアントに返します。
  • 複数のストアド プロシージャがWITH RETURNを指定すると、システムは開いた順に結果セットを返します。
  • 結果セットを現在のストアド プロシージャ、呼び出し側またはクライアントに返すために、結果セット カーソルは開いたままにします。結果セット カーソルが閉じられていると、システムは結果セットを返しません。
結果セットは、次のように返されます。
  • 結果セットを作成したストアド プロシージャではなく、呼び出し側の応答属性(応答モード、応答保持、LOB応答モード)を継承します。例えば、BTEQでCALLを実行すると、システムは結果セットを標識モードでストアド プロシージャに送信し、FieldモードでBTEQに送信します。
  • 呼び出し側またはセッションの照合ではなく、ストアド プロシージャの照合に基づきます。

例: ストアド プロシージャでのカーソルの使用

次の例は、ストアド プロシージャにおけるカーソルの正しい使い方を説明しています。6行目と10行目で宣言が行なわれています。

CREATE PROCEDURE spsample1()
BEGIN
  L1: BEGIN
      DECLARE vname CHARACTER(30);
      DECLARE vamt INTEGER;
      DECLARE empcursor CURSOR FOR
        SELECT empname, salary
        FROM empdetails
        ORDER BY deptcode;
      DECLARE deptcursor CURSOR FOR
        SELECT deptname
        FROM department;
      DECLARE CONTINUE HANDLER FOR SQLSTATE '42000'
        BEGIN
          OPEN empcursor;
          ...
        END;
        ...
        ...
  END L1;
END;

例: 暗黙的なFOR READ ONLYカーソルの使用

次の例は、暗黙的なFOR READ ONLYカーソルを説明しています。このストアド プロシージャでは、empcursorの宣言でFOR UPDATE句を指定していないため、デフォルトのFOR READ ONLYになります。

CREATE PROCEDURE sp1()
BEGIN
  DECLARE empcursor CURSOR FOR
    SELECT *
    FROM employee
    WHERE deptcode = 101
    ORDER BY empid;
   ...
END;

例: 明示的に宣言されたFOR READ ONLYカーソルの使用

次の例は、明示的に宣言されたFOR READ ONLYカーソルを説明しています。

CREATE PROCEDURE sp1()
BEGIN
  DECLARE empcursor CURSOR FOR
    SELECT *
    FROM employee
    WHERE deptcode = 101
  FOR READ ONLY;
    ...
END;

例: FOR UPDATEカーソルの使用

次の例は、FOR UPDATEカーソルを説明しています。

CREATE PROCEDURE sp1()
BEGIN
  DECLARE empcursor CURSOR FOR
    SELECT *
    FROM employee
    WHERE deptcode = 101
  FOR UPDATE;
      ...
END;

例: WITH RETURN ONLY TO CLIENTの使用

次の例では、WITH RETURN ONLY TO CLIENTの使用方法を説明しています。

DECLARE results1 CURSOR WITH RETURN ONLY TO CLIENT FOR
       SELECT store, item, on_hand 
       FROM inventory
       ORDER BY store, item;
OPEN results1;

例: プロシージャでの動的SQL文の使用

次の例では、WITH RETURN句なしで定義された動的SQL文をストアド プロシージャ内で使用しています。

CREATE PROCEDURE GetEmployeeSalary
(IN EmpName VARCHAR(100), OUT Salary DEC(10,2))
BEGIN
  DECLARE SqlStr VARCHAR(1000);
  DECLARE C1 CURSOR FOR S1;
  SET SqlStr = 'SELECT Salary FROM EmployeeTable WHERE EmpName = ?';
  PREPARE S1 FROM SqlStr;
  OPEN C1 USING EmpName;
  FETCH C1 INTO Salary;
  CLOSE C1;
END;

例: DECLARE CURSOR文の動的形式の使用

次の例では、動的形式のDECLARE CURSOR文を説明しています。カーソル文では、動的SELECTによって結果カーソルを指定しています。

DECLARE statement1_str VARCHAR(500);
DECLARE result_set CURSOR WITH RETURN ONLY FOR stmt1;

SET statement1_str = 'SELECT store, item, on_hand FROM inventory ORDER BY store, item;'
PREPARE stmt1 FROM statement1_str;
OPEN result_set;

例: 動的パラメータ マーカーの使用

次の例では、動的パラメータ マーカーの使用方法を説明しています。動的パラメータ マーカーのデータは、OPEN文に渡されます。

DECLARE Store_num INTEGER;
DECLARE statement1_str VARCHAR(500);
DECLARE result_set CURSOR WITH RETURN ONLY FOR stmt1;

SET statement1_str = 'SELECT store, item, on_hand'
               ' FROM inventory WHERE store = ? ORDER BY store, item;'
PREPARE stmt1 FROM statement1_str;
SET Store_num = 76;
OPEN result_set USING Store_num;

関連トピック

以下に関する詳細な情報