16.20 - ストアド プロシージャのサンプル - 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

この項では、Teradata Databaseストアド プロシージャのほとんどの機能を使用する、ストアド プロシージャのサンプルを紹介します。このストアド プロシージャのサンプルは、実際の使用にお勧めするものではありません。

ストアド プロシージャのサンプルには、複数のパラメータ、ローカル変数宣言、カーソル(FORカーソルおよびカーソル宣言)、条件ハンドラー、入れ子の複合文、制御文、DML文、およびANSIスタイルのコメントが含まれています。

前提条件

  • ユーザーは現行のデフォルト データベースに対してCREATE PROCEDURE権限を持っている。
  • このプロシージャは、ユーザーが所有するデータベースで作成されるため、作成者もプロシージャの直接所有者となる。
  • テーブルtBranchtAccountstDummytDummy1、およびProc_Error_Tblが現在のデフォルト データベースに存在する。
  • 現行のテーブルに、ストアド プロシージャGetNextBranchIdもある。
  • 新しいストアド プロシージャは支店につき1000口座しかサポートしない。

テーブル定義の例

次のCREATE TABLE文は、ストアド プロシージャのサンプルで使用されている2つの重要なテーブルtAccountsおよびProc_Error_Tblを定義します。ストアド プロシージャ本体で参照されているすべてのテーブルおよびストアド プロシージャも作成する必要があります。

このDDLでは、口座テーブルを次のように定義しています。

CREATE MULTISET TABLE sampleDb.tAccounts, NO FALLBACK,
     NO BEFORE JOURNAL,
     NO AFTER JOURNAL
     (
     BranchId INTEGER,
     AccountCode INTEGER,
     Balance DECIMAL(10,2),
         AccountNumber INTEGER,
         Interest DECIMAL(10,2))
PRIMARY INDEX (AccountNumber) ;

このDDLでは、エラー テーブルを次のように定義しています。

CREATE MULTISET TABLE sampleDb.Proc_Error_Tbl ,NO FALLBACK ,
     NO BEFORE JOURNAL,
     NO AFTER JOURNAL
     (
     sql_state CHAR(5) CHARACTER SET LATIN CASESPECIFIC,
     time_stamp TIMESTAMP(6),
     Add_branch CHAR(15) CHARACTER SET LATIN CASESPECIFIC,
     msg VARCHAR(40) CHARACTER SET LATIN CASESPECIFIC)
PRIMARY INDEX ( sql_state );

ストアド プロシージャの定義

以下のCREATE PROCEDURE文は、サンプルのストアド プロシージャを作成します。この定義は、ストアド プロシージャの「ソース テキスト」とも呼ばれます。

このCREATE PROCEDURE文により、銀行の内部機能をサポートするAddBranchというプロシージャが作成されます。

  • 新しい支店の詳細を取り込んで、tBranchテーブルに追加する。
  • 新しい支店にBranchIdを割り当てる。
  • 支店の新しい口座の詳細をtAccountsテーブルに追加する。
  • 新しい支店のtAccountsテーブルに載っている口座の預金残高と利息を更新する。
    CREATE PROCEDURE AddBranch (
                           OUT oBranchId INTEGER,
                            IN iBranchName CHAR(15),
                            IN iBankCode INTEGER,
                            IN iStreet VARCHAR(30),
                            IN iCity VARCHAR(30),
                            IN iState VARCHAR(30),
                            IN iZip INTEGER
                            )
    Lmain: BEGIN
         -- Lmain is the label for the main compound statement
      
      -- Local variable declarations follow
       DECLARE hMessage CHAR(50) DEFAULT
                  ’Error: Database Operation ...’;
       DECLARE hNextBranchId INTEGER;
       DECLARE hAccountNumber INTEGER DEFAULT 10;
       DECLARE hBalance INTEGER;
                    
     --Condition Handler Declarations
       DECLARE CONTINUE HANDLER FOR SQLSTATE ’21000’
          
       -- Label compoun statements within handlers as HCS1 etc.
       HCS1: BEGIN
             INSERT INTO Proc_Error_Tbl
           (:SQLSTATE, CURRENT_TIMESTAMP, ’AddBranch’, hMessage);
          END HCS1;
       DECLARE CONTINUE HANDLER FOR SQLSTATE ’42000’
          HCS2: BEGIN
             SET hMessage = ’Table Not Found ... ’;
             INSERT INTO Proc_Error_Tbl
             (:SQLSTATE, CURRENT_TIMESTAMP, ’AddBranch’, hMessage);
          END HCS2;

       -- Get next branch-id from tBranchId table

       CALL GetNextBranchId  hNextBranchId);

       -- Add new branch to tBranch table
       INSERT INTO tBranch ( BranchId, BankId, BranchName, Street,                City,  State, Zip)
       VALUES ( hNextBranchId, iBankId, iBranchName, iStreet,              iCity, iState, iZip);

      -- Assign branch number to the output parameter;
      -- the value is returned to the calling procedure

       SET oBranchId = hNextBranchId;

       -- Insert the branch number and name in tDummy table
       INSERT INTO tDummy VALUES(hNextBranchId, iBranchName);

       -- Insert account numbers pertaining to the current branch
       SELECT max(AccountNumber) INTO hAccountNumber FROM tAccounts;

       WHILE (hAccountNumber <= 1000) 
       DO
          INSERT INTO tAccounts (BranchId, AccountNumber)
         VALUES ( hNextBranchId, hAccountNumber);
           -- Advance to next account number
          SET hAccountNumber = hAccountNumber + 1;
       END WHILE;
        
      -- Update balance in each account of the current branch-id
       SET hAccountNumber = 1;

       L1: LOOP
           UPDATE tAccounts SET Balance = 100000
             WHERE BranchId = hNextBranchId AND
              AccountNumber = hAccountNumber;

           -- Generate account number
           SET hAccountNumber = hAccountNumber + 1;

           -- Check if through with all the accounts
           IF (hAccountNumber > 1000) THEN
            LEAVE L1;
           END IF;
       END LOOP L1;

       -- Update Interest for each account of the current branch-id
       FOR fAccount AS cAccount CURSOR FOR
       -- since Account is a reserved word
          SELECT Balance AS aBalance FROM tAccounts
             WHERE BranchId = hNextBranchId
       DO

       -- Update interest for each account
          UPDATE tAccounts SET Interest = fAccount.aBalance * 0.10
             WHERE CURRENT OF cAccount;
       END FOR;

      -- Inner nested compound statement begins
      Lnest: BEGIN
        -- local variable declarations in inner compound statement
       DECLARE Account_Number, counter INTEGER;
       DECLARE Acc_Balance DECIMAL (10,2);

       -- cursor declaration in inner compound statement
       DECLARE acc_cur CURSOR FOR
             SELECT AccountCode, Balance FROM tAccounts
             ORDER BY AccountNumber;

      -- condition handler declarations in inner compound statement
        DECLARE CONTINUE HANDLER FOR NOT FOUND
        HCS3: BEGIN
                  DECLARE h_Message VARCHAR(50);
                  DECLARE EXIT HANDLER FOR SQLWARNING
                     HCS4: BEGIN
                         SET h_Message = 'Requested sample is larger
                          than table rows';
                         INSERT INTO Proc_Error_Tbl (:SQLSTATE,
              CURRENT_TIMESTAMP, 'AddBranch', h_Message);
                     END HCS4;
                   
           SET h_Message = 'Data not Found ...';
                 INSERT INTO Proc_Error_Tbl (:SQLSTATE,
             CURRENT_TIMESTAMP, 'AddBranch', h_Message);
             SELECT COUNT(*) INTO :counter FROM Proc_Error_Tbl
             SAMPLE 10;
           -- Raises a warning. This is a condition raised by
           -- a handler action statement. This is handled.
            END HCS3;

            DELETE FROM tDummy1;
            -- Raises “no data found” warning
            OPEN acc_cur;
            L2: REPEAT
            BEGIN
                FETCH acc_cur INTO Account_code, Acc_Balance;
                CASE
                  WHEN (Account_code  <= 1000) THEN
                   INSERT INTO dummy1 (Account_code, Acc_Balance);
                   ELSE
                   LEAVE L3;
                END CASE;
            END;
            UNTIL (SQLCODE = 0)
            END REPEAT L2;
        CLOSE acc_cur;
      END Lnest; --- end of inner nested block.

    END Lmain; -- This comment is part of stored procedure body
    -- End-of-Create-Procedure.

入力ファイルからのプロシージャのコンパイル

BTEQでCOMPILEコマンドを使用してストアド プロシージャAddBranchを作成する場合、入力ファイル内のストアド プロシージャの定義全体を実行する必要があります。

このプロシージャは、ストアド プロシージャ本体で参照されるいずれかのデータベース オブジェクトが欠落しているか削除される場合、コンパイルされていくらかの警告が発生します。

カーソルSELECT文で参照されている、なんらかのデータベース オブジェクトが欠落している場合、コンパイルがエラーになり、ストアド プロシージャは作成されません。

CLIv2、ODBC、またはJDBCからCREATE PROCEDUREが実行されると、Teradata Database内のSPLコンパイラがストアド プロシージャをコンパイルします。