この項では、Teradataストアド プロシージャのほとんどの機能を使用する、ストアド プロシージャのサンプルを紹介します。このストアド プロシージャのサンプルは、実際の使用にお勧めするものではありません。
ストアド プロシージャのサンプルには、複数のパラメータ、ローカル変数宣言、カーソル(FORカーソルおよびカーソル宣言)、条件ハンドラー、入れ子の複合文、制御文、DML文、およびANSIスタイルのコメントが含まれています。
前提条件
- ユーザーは現行のデフォルト データベースに対してCREATE PROCEDURE権限を持っている。
- このプロシージャは、ユーザーが所有するデータベースで作成されるため、作成者もプロシージャの直接所有者となる。
- テーブルtBranch、tAccounts、tDummy、tDummy1、および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が実行されると、データベース内のSPLコンパイラがストアド プロシージャをコンパイルします。