貯蓄テーブルを作成する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