17.00 - 17.05 - 例2:未払利息を更新するためのストアド プロシージャの作成 - Advanced SQL Engine - Teradata Database

Teradata Vantage™ - データベースの管理

Product
Advanced SQL Engine
Teradata Database
Release Number
17.00
17.05
Release Date
2020年6月
Content Type
管理
Publication ID
B035-1093-170K-JPN
Language
日本語 (日本)

貯蓄テーブルを作成するSQL

以下のコードSavings.sqlは、貯蓄テーブルを作成し、テーブルにテスト データを挿入します。

DROP TABLE Savings;
CREATE TABLE Savings
,NO BEFORE JOURNAL
,NO AFTER JOURNAL
(	 
AccountType               CHAR(1) NOT NULL	
,AccountNumber            CHAR(20) NOT NULL
,OpeningBalance           DECIMAL(9,3)	
,AccruedInterest          DECIMAL(9,3)	
,LastYearEnd              DATE	
,LastAccrualDate          DATE
)
UNIQUE PRIMARY INDEX( AccountType,AccountNumber );
INSERT INTO Savings ('S','1200121',10000.00,0,'2012-12-31','2013-01-01');
INSERT INTO Savings ('S','1200122',20000.00,0,'2012-12-31','2013-01-01');
INSERT INTO Savings ('S','1200123',30000.00,0,'2012-12-31','2013-01-01');
INSERT INTO Savings ('S','1200124',40000.00,0,'2012-12-31','2013-01-01');
INSERT INTO Savings ('S','1200125',50000.00,0,'2012-12-31','2013-01-01');
INSERT INTO Savings ('C','1200121', 1000.00,0,'2012-12-31','2013-01-01');
INSERT INTO Savings ('C','1200122', 2000.00,0,'2012-12-31','2013-01-01');
INSERT INTO Savings ('C','1200123', 3000.00,0,'2012-12-31','2013-01-01');
INSERT INTO Savings ('C','1200124', 4000.00,0,'2012-12-31','2013-01-01');
INSERT INTO Savings ('C','1200125', 5000.00,0,'2012-12-31','2013-01-01');

未払利息を更新するストアド プロシージャ

以下のストアド プロシージャSP_Accrue_Interest.sqlは、カーソルを使用して条件を満たすレコードを選択し、未払利息を更新します。

REPLACE PROCEDURE SP_Accrue_Interest(IN Arg_AccontType CHAR(20), OUT Proc_Msg VARCHAR(80))

BEGIN

DECLARE pv_AccountType   CHAR(1);
DECLARE pv_AccountNumber CHAR(20);
DECLARE pv_CalcBase      DEC(9,2);
DECLARE pv_Interest      DEC(9,2);
DECLARE pv_ProcCount     INT;

  DECLARE calc_cursor CURSOR FOR
  SELECT  AccountType, AccountNumber, (OpeningBalance+AccruedInterest)
          AS BaseAmount FROM Savings
  WHERE   (AccountType  = :Arg_AccontType
  OR      'A           '= :Arg_AccontType)
  AND     LastAccrualDate< DATE;

  OPEN    calc_cursor;

  FETCH   calc_cursor INTO pv_AccountType, pv_AccountNumber,pv_CalcBase;
	
  SET     pv_ProcCount = 0;
  SET     Proc_Msg     = 'Processed ' || pv_ProcCount || ' records';

  Calc_Loop:
  WHILE (SQLCODE =0) DO

    SET      pv_ProcCount = pv_ProcCount + 1;
    SET      Proc_Msg     = 'Processed ' || pv_ProcCount || ' records';

    SET      pv_Interest  = pv_CalcBase * 0.010;

    UPDATE   Savings
    SET      AccruedInterest   = AccruedInterest + :pv_Interest
             ,LastAccrualDate  = DATE
    WHERE    AccountType       = :pv_AccountType
    AND      AccountNumber     = :pv_AccountNumber;

    FETCH    calc_cursor INTO pv_AccountType, pv_AccountNumber,pv_CalcBase;

  END WHILE;
  CLOSE calc_cursor;
  END;

ストアド プロシージャを呼び出すBTEQスクリプト

以下のBTEQスクリプトBld_iudf.btqはストアド プロシージャを呼び出し、貯蓄テーブルからのSELECTを実行してこのストアド プロシージャをテストします。

.SET WIDTH		240

.logon TDSystem/MyUserId,MyPass

.REMARK 'Choosing database';

DATABASE Test_Database;

.REMARK 'Building & populating savings table';
.RUN FILE=./Savings.sql

.REMARK 'Building stored procedure';
.COMPILE FILE=./SP_Accrue_Interest.sql

.REMARK 'Testing stored procedure';

SELECT * FROM Savings	ORDER BY 1,2;

CALL SP_Accrue_Interest('C',"Messg");
SELECT * FROM Savings	ORDER BY 1,2;

CALL SP_Accrue_Interest('S',"Messg");
SELECT * FROM Savings	ORDER BY 1,2;

CALL SP_Accrue_Interest('C',"Messg");
SELECT * FROM Savings	ORDER BY 1,2;

CALL SP_Accrue_Interest('S',"Messg");
SELECT * FROM Savings	ORDER BY 1,2;

.REMARK 'Rebuilding & populating savings table';
.RUN FILE=./Savings.sql

SELECT * FROM Savings	ORDER BY 1,2;

CALL SP_Accrue_Interest('A',"Messg");
SELECT * FROM Savings	ORDER BY 1,2;

CALL SP_Accrue_Interest('A',"Messg");
SELECT * FROM Savings	ORDER BY 1,2;

.REMARK 'Finished testing stored procedure';

.LOGOFF
.EXIT 0

BTEQスクリプトを呼び出すLinuxコード

このLinuxコードは、BTEQスクリプトを呼び出します。

bteq bld_iudfs.btq 2>&1 | tee bld_iudfs.btq.i1.out

ストアド プロシージャの出力

BTEQ 14.00.00.02 Wed Apr 24 09:07:08 2013
+---------+---------+---------+---------+---------+---------+---------+---------+---------+
.SET WIDTH  240
+---------+---------+---------+---------+---------+---------+---------+---------+---------+
.logon TDSystem/MyUserId,
 *** Logon successfully completed.
 *** Teradata Database Release is 14.10i.00.434
 *** Teradata Database Version is 14.10i.00.434
 *** Transaction Semantics are BTET.
 *** Session Character Set Name is 'ASCII'.
 *** Total elapsed time was 1 second.
+---------+---------+---------+---------+---------+---------+---------+---------+---------+
.REMARK 'Choosing database';
 Choosing database
+---------+---------+---------+---------+---------+---------+---------+---------+---------+
DATABASE Felix_Test;
 *** New default database accepted.
 *** Total elapsed time was 1 second.

+---------+---------+---------+---------+---------+---------+---------+---------+---------+
.REMARK 'Populating savings table';
 Populating savings table
+---------+---------+---------+---------+---------+---------+---------+---------+---------+
.RUN FILE=./SavingsData.sql
+---------+---------+---------+---------+---------+---------+---------+---------+---------+
DELETE FROM Savings ALL;
 *** Delete completed. 10 rows removed.
 *** Total elapsed time was 2 seconds.

+---------+---------+---------+---------+---------+---------+---------+---------+---------+
INSERT INTO Savings ('S','1200121',10000.00,0,'2012-12-31','2013-01-01');
 *** Insert completed. One row added.
 *** Total elapsed time was 3 seconds.
+---------+---------+---------+---------+---------+---------+---------+---------+---------+
INSERT INTO Savings ('S','1200122',20000.00,0,'2012-12-31','2013-01-01');
 *** Insert completed. One row added.
 *** Total elapsed time was 1 second.
+---------+---------+---------+---------+---------+---------+---------+---------+---------+
INSERT INTO Savings ('S','1200123',30000.00,0,'2012-12-31','2013-01-01');
 *** Insert completed. One row added.
 *** Total elapsed time was 1 second.

+---------+---------+---------+---------+---------+---------+---------+---------+---------+
INSERT INTO Savings ('S','1200124',40000.00,0,'2012-12-31','2013-01-01');
 *** Insert completed. One row added.
 *** Total elapsed time was 1 second.

+---------+---------+---------+---------+---------+---------+---------+---------+---------+
INSERT INTO Savings ('S','1200125',50000.00,0,'2012-12-31','2013-01-01');
 *** Insert completed. One row added.
 *** Total elapsed time was 1 second.

+---------+---------+---------+---------+---------+---------+---------+---------+---------+
INSERT INTO Savings ('C','1200121', 1000.00,0,'2012-12-31','2013-01-01');
 *** Insert completed. One row added.
 *** Total elapsed time was 1 second.

+---------+---------+---------+---------+---------+---------+---------+---------+---------+
INSERT INTO Savings ('C','1200122', 2000.00,0,'2012-12-31','2013-01-01');
 *** Insert completed. One row added.
 *** Total elapsed time was 1 second.

+---------+---------+---------+---------+---------+---------+---------+---------+---------+
INSERT INTO Savings ('C','1200123', 3000.00,0,'2012-12-31','2013-01-01');
 *** Insert completed. One row added.
 *** Total elapsed time was 1 second.

+---------+---------+---------+---------+---------+---------+---------+---------+---------+
INSERT INTO Savings ('C','1200124', 4000.00,0,'2012-12-31','2013-01-01');
 *** Insert completed. One row added.
 *** Total elapsed time was 1 second.

+---------+---------+---------+---------+---------+---------+---------+---------+---------+
INSERT INTO Savings ('C','1200125', 5000.00,0,'2012-12-31','2013-01-01');
 *** Insert completed. One row added.
 *** Total elapsed time was 1 second.

+---------+---------+---------+---------+---------+---------+---------+---------+---------+
 *** Warning: EOF on INPUT stream.
+---------+---------+---------+---------+---------+---------+---------+---------+---------+
.REMARK 'Testing internal UDF';
 Testing internal UDF

+---------+---------+---------+---------+---------+---------+---------+---------+---------+-
SELECT * FROM Savings ORDER BY 1,2;
 *** Query completed. 10 rows found. 6 columns returned.
 *** Total elapsed time was 6 seconds.
AccountType  AccountNumber    OpeningBalance  AccruedInterest  LastYearEnd  LastAccrualDate
-----------  ---------------  --------------  ---------------  -----------  ---------------
C            1200121                1000.000             .000     12/12/31         13/01/01
C            1200122                2000.000             .000     12/12/31         13/01/01
C            1200123                3000.000             .000     12/12/31         13/01/01
C            1200124                4000.000             .000     12/12/31         13/01/01
C            1200125                5000.000             .000     12/12/31         13/01/01
S            1200121               10000.000             .000     12/12/31         13/01/01
S            1200122               20000.000             .000     12/12/31         13/01/01
S            1200123               30000.000             .000     12/12/31         13/01/01
S            1200124               40000.000             .000     12/12/31         13/01/01
S            1200125               50000.000             .000     12/12/31         13/01/01
+---------+---------+---------+---------+---------+---------+---------+---------+---------+
CALL SP_Accrue_Interest('C',"Messg");
 *** Procedure has been executed.
 *** Total elapsed time was 2 seconds.
Proc_Msg
--------------------------------------------------------------------------------
Processed           5 records
+---------+---------+---------+---------+---------+---------+---------+---------+---------+
SELECT * FROM Savings ORDER BY 1,2;
 *** Query completed. 10 rows found. 6 columns returned.
 *** Total elapsed time was 1 second.
AccountType  AccountNumber    OpeningBalance  AccruedInterest  LastYearEnd  LastAccrualDate
-----------  ---------------  --------------  ---------------  -----------  ---------------
C            1200121                1000.000           10.000     12/12/31         13/04/24
C            1200122                2000.000           20.000     12/12/31         13/04/24
C            1200123                3000.000           30.000     12/12/31         13/04/24
C            1200124                4000.000           40.000     12/12/31         13/04/24
C            1200125                5000.000           50.000     12/12/31         13/04/24
S            1200121               10000.000             .000     12/12/31         13/01/01
S            1200122               20000.000             .000     12/12/31         13/01/01
S            1200123               30000.000             .000     12/12/31         13/01/01
S            1200124               40000.000             .000     12/12/31         13/01/01
S            1200125               50000.000             .000     12/12/31         13/01/01
+---------+---------+---------+---------+---------+---------+---------+---------+---------+
CALL SP_Accrue_Interest('S',"Messg");
 *** Procedure has been executed.
 *** Total elapsed time was 1 second.
Proc_Msg
--------------------------------------------------------------------------------
Processed           5 records
+---------+---------+---------+---------+---------+---------+---------+---------+---------+
SELECT * FROM Savings ORDER BY 1,2;
 *** Query completed. 10 rows found. 6 columns returned.
 *** Total elapsed time was 1 second.
AccountType  AccountNumber    OpeningBalance  AccruedInterest  LastYearEnd  LastAccrualDate
-----------  ---------------  --------------  ---------------  -----------  ---------------
C            1200121                1000.000           10.000     12/12/31         13/04/24
C            1200122                2000.000           20.000     12/12/31         13/04/24
C            1200123                3000.000           30.000     12/12/31         13/04/24
C            1200124                4000.000           40.000     12/12/31         13/04/24
C            1200125                5000.000           50.000     12/12/31         13/04/24
S            120012                10000.000          100.000     12/12/31         13/04/24
S            120012                20000.000          200.000     12/12/31         13/04/24
S            120012                30000.000          300.000     12/12/31         13/04/24
S            12001                 40000.000          400.000     12/12/31         13/04/24
S            12001                 50000.000          500.000     12/12/31         13/04/24
+---------+---------+---------+---------+---------+---------+---------+---------+---------+
CALL SP_Accrue_Interest('C',"Messg");
 *** Procedure has been executed.
 *** Total elapsed time was 1 second.
Proc_Msg
--------------------------------------------------------------------------------
Processed           0 records
+---------+---------+---------+---------+---------+---------+---------+---------+---------+
SELECT * FROM Savings ORDER BY 1,2;
 *** Query completed. 10 rows found. 6 columns returned.
 *** Total elapsed time was 1 second.
AccountType  AccountNumber    OpeningBalance  AccruedInterest  LastYearEnd  LastAccrualDate
-----------  ---------------  --------------  ---------------  -----------  ---------------
C            1200121                1000.000           10.000     12/12/31         13/04/24
C            1200122                2000.000           20.000     12/12/31         13/04/24
C            1200123                3000.000           30.000     12/12/31         13/04/24
C            1200124                4000.000           40.000     12/12/31         13/04/24
C            1200125                5000.000           50.000     12/12/31         13/04/24
S            1200121               10000.000          100.000     12/12/31         13/04/24
S            1200122               20000.000          200.000     12/12/31         13/04/24
S            1200123               30000.000          300.000     12/12/31         13/04/24
S            1200124               40000.000          400.000     12/12/31         13/04/24
S            1200125               50000.000          500.000     12/12/31         13/04/24
+---------+---------+---------+---------+---------+---------+---------+---------+---------+
CALL SP_Accrue_Interest('S',"Messg");
 *** Procedure has been executed.
 *** Total elapsed time was 1 second.
Proc_Msg
--------------------------------------------------------------------------------
Processed           0 records
+---------+---------+---------+---------+---------+---------+---------+---------+---------+
SELECT * FROM Savings ORDER BY 1,2;
 *** Query completed. 10 rows found. 6 columns returned.
 *** Total elapsed time was 4 seconds.
AccountType  AccountNumber    OpeningBalance  AccruedInterest  LastYearEnd  LastAccrualDate
-----------  ---------------  --------------  ---------------  -----------  ---------------
C            1200121                1000.000           10.000     12/12/31         13/04/24
C            1200122                2000.000           20.000     12/12/31         13/04/24
C            1200123                3000.000           30.000     12/12/31         13/04/24
C            1200124                4000.000           40.000     12/12/31         13/04/24
C            1200125                5000.000           50.000     12/12/31         13/04/24
S            1200121               10000.000          100.000     12/12/31         13/04/24
S            1200122               20000.000          200.000     12/12/31         13/04/24
S            1200123               30000.000          300.000     12/12/31         13/04/24
S            1200124               40000.000          400.000     12/12/31         13/04/24
S            1200125               50000.000          500.000     12/12/31         13/04/24
+---------+---------+---------+---------+---------+---------+---------+---------+---------+
.REMARK 'Populating savings table';
 Populating savings table
+---------+---------+---------+---------+---------+---------+---------+---------+---------+
.RUN FILE=./SavingsData.sql
+---------+---------+---------+---------+---------+---------+---------+---------+---------+
DELETE FROM Savings ALL;
 *** Delete completed. 10 rows removed.
 *** Total elapsed time was 2 seconds.

+---------+---------+---------+---------+---------+---------+---------+---------+---------+
INSERT INTO Savings ('S','1200121',10000.00,0,'2012-12-31','2013-01-01');
 *** Insert completed. One row added.
 *** Total elapsed time was 1 second.

+---------+---------+---------+---------+---------+---------+---------+---------+---------+
INSERT INTO Savings ('S','1200122',20000.00,0,'2012-12-31','2013-01-01');
 *** Insert completed. One row added.
 *** Total elapsed time was 1 second.

+---------+---------+---------+---------+---------+---------+---------+---------+---------+
INSERT INTO Savings ('S','1200123',30000.00,0,'2012-12-31','2013-01-01');
 *** Insert completed. One row added.
 *** Total elapsed time was 1 second.

+---------+---------+---------+---------+---------+---------+---------+---------+---------+
INSERT INTO Savings ('S','1200124',40000.00,0,'2012-12-31','2013-01-01');
 *** Insert completed. One row added.
 *** Total elapsed time was 1 second.

+---------+---------+---------+---------+---------+---------+---------+---------+---------+
INSERT INTO Savings ('S','1200125',50000.00,0,'2012-12-31','2013-01-01');
 *** Insert completed. One row added.
 *** Total elapsed time was 1 second.

+---------+---------+---------+---------+---------+---------+---------+---------+---------+
INSERT INTO Savings ('C','1200121', 1000.00,0,'2012-12-31','2013-01-01');
 *** Insert completed. One row added.
 *** Total elapsed time was 1 second.

+---------+---------+---------+---------+---------+---------+---------+---------+---------+
INSERT INTO Savings ('C','1200122', 2000.00,0,'2012-12-31','2013-01-01');
 *** Insert completed. One row added.
 *** Total elapsed time was 1 second.

+---------+---------+---------+---------+---------+---------+---------+---------+---------+
INSERT INTO Savings ('C','1200123', 3000.00,0,'2012-12-31','2013-01-01');
 *** Insert completed. One row added.
 *** Total elapsed time was 1 second.

+---------+---------+---------+---------+---------+---------+---------+---------+---------+
INSERT INTO Savings ('C','1200124', 4000.00,0,'2012-12-31','2013-01-01');
 *** Insert completed. One row added.
 *** Total elapsed time was 1 second.

+---------+---------+---------+---------+---------+---------+---------+---------+---------+
INSERT INTO Savings ('C','1200125', 5000.00,0,'2012-12-31','2013-01-01');
 *** Insert completed. One row added.
 *** Total elapsed time was 1 second.

+---------+---------+---------+---------+---------+---------+---------+---------+---------+
 *** Warning: EOF on INPUT stream.
+---------+---------+---------+---------+---------+---------+---------+---------+---------+
SELECT * FROM Savings ORDER BY 1,2;
 *** Query completed. 10 rows found. 6 columns returned.
 *** Total elapsed time was 1 second.
AccountType  AccountNumber    OpeningBalance  AccruedInterest  LastYearEnd  LastAccrualDate
-----------  ---------------  --------------  ---------------  -----------  ---------------
C            1200121                1000.000             .000     12/12/31         13/01/01
C            1200122                2000.000             .000     12/12/31         13/01/01
C            1200123                3000.000             .000     12/12/31         13/01/01
C            1200124                4000.000             .000     12/12/31         13/01/01
C            1200125                5000.000             .000     12/12/31         13/01/01
S            1200121               10000.000             .000     12/12/31         13/01/01
S            1200122               20000.000             .000     12/12/31         13/01/01
S            1200123               30000.000             .000     12/12/31         13/01/01
S            1200124               40000.000             .000     12/12/31         13/01/01
S            1200125               50000.000             .000     12/12/31         13/01/01
+---------+---------+---------+---------+---------+---------+---------+---------+---------+
CALL SP_Accrue_Interest('A',"Messg");
 *** Procedure has been executed.
 *** Total elapsed time was 1 second.
Proc_Msg
--------------------------------------------------------------------------------
Processed          10 records
+---------+---------+---------+---------+---------+---------+---------+---------+---------+
SELECT * FROM Savings ORDER BY 1,2;
 *** Query completed. 10 rows found. 6 columns returned.
 *** Total elapsed time was 1 second.
AccountType  AccountNumber    OpeningBalance  AccruedInterest  LastYearEnd  LastAccrualDate
-----------  ---------------  --------------  ---------------  -----------  ---------------
C            1200121                1000.000           10.000     12/12/31         13/04/24
C            1200122                2000.000           20.000     12/12/31         13/04/24
C            1200123                3000.000           30.000     12/12/31         13/04/24
C            1200124                4000.000           40.000     12/12/31         13/04/24
C            1200125                5000.000           50.000     12/12/31         13/04/24
S            1200121               10000.000          100.000     12/12/31         13/04/24
S            1200122               20000.000          200.000     12/12/31         13/04/24
S            1200123               30000.000          300.000     12/12/31         13/04/24
S            1200124               40000.000          400.000     12/12/31         13/04/24
S            1200125               50000.000          500.000     12/12/31         13/04/24
+---------+---------+---------+---------+---------+---------+---------+---------+---------+
CALL SP_Accrue_Interest('A',"Messg");
 *** Procedure has been executed.
 *** Total elapsed time was 1 second.
Proc_Msg
--------------------------------------------------------------------------------
Processed           0 records
+---------+---------+---------+---------+---------+---------+---------+---------+---------+
SELECT * FROM Savings ORDER BY 1,2;
 *** Query completed. 10 rows found. 6 columns returned.
 *** Total elapsed time was 1 second.
AccountType  AccountNumber    OpeningBalance  AccruedInterest  LastYearEnd  LastAccrualDate
-----------  ---------------  --------------  ---------------  -----------  ---------------
C            1200121                1000.000           10.000     12/12/31         13/04/24
C            1200122                2000.000           20.000     12/12/31         13/04/24
C            1200123                3000.000           30.000     12/12/31         13/04/24
C            1200124                4000.000           40.000     12/12/31         13/04/24
C            1200125                5000.000           50.000     12/12/31         13/04/24
S            1200121               10000.000          100.000     12/12/31         13/04/24
S            1200122               20000.000          200.000     12/12/31         13/04/24
S            1200123               30000.000          300.000     12/12/31         13/04/24
S            1200124               40000.000          400.000     12/12/31         13/04/24
S            1200125               50000.000          500.000     12/12/31         13/04/24
+---------+---------+---------+---------+---------+---------+---------+---------+---------+
.REMARK 'Finishied testing internal UDF';
 Finishied testing internal UDF
+---------+---------+---------+---------+---------+---------+---------+---------+---------+
.LOGOFF
 *** You are now logged off from the DBC.
+---------+---------+---------+---------+---------+---------+---------+---------+---------+
.EXIT 0
 *** Exiting BTEQ...
 *** RC (return code) = 0