16.10 - Example 2: Creating a Stored Procedure to Update Accrued Interest - Teradata Database

Teradata Database Administration

Product
Teradata Database
Release Number
16.10
Release Date
April 2018
Content Type
Administration
Publication ID
B035-1093-161K
Language
English (United States)

SQL To Create a Savings Table

The following code, Savings.sql, creates a savings table and inserts test data into the table:

DROP TABLE Savings;
CREATE TABLE Savings
,FALLBACK
,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');

Stored Procedure to Update Accrued Interest

The following stored procedure, SP_Accrue_Interest.sql, uses a cursor to select qualified records and update accrued interest.

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 Script to Call the Stored Procedure

The following BTEQ script, Bld_iudf.btq, calls the stored procedure and performs SELECTs from the savings table to test the stored procedure.

.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

Linux Code To Call the BTEQ Script

This Linux code calls the BTEQ script:

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

Output of the Stored Procedure

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