Sample Stored Procedures | Teradata Vantage - Sample Stored Procedure - Advanced SQL Engine - Teradata Database

SQL Stored Procedures and Embedded SQL

Product
Advanced SQL Engine
Teradata Database
Release Number
17.05
17.00
Published
June 2020
Language
English (United States)
Last Update
2021-01-23
dita:mapPath
xqq1557098602407.ditamap
dita:ditavalPath
lze1555437562152.ditaval
dita:id
B035-1148
lifecycle
previous
Product Category
Teradata Vantage™

This section provides a sample stored procedure that uses most of the features of Teradata Database stored procedures. The sample stored procedure is not recommended for real use.

The sample stored procedure includes multiple parameters, local variable declarations, cursors (FOR cursor and cursor declaration), condition handlers, nested compound statements, control statements, DML statements, and ANSI style comments.

Assumptions

  • The user has CREATE PROCEDURE privilege on the current default database.
  • The procedure is being created in the database owned by the user, so that the creator is also the immediate owner of the procedure.
  • The tables tBranch, tAccounts, tDummy, tDummy1, and Proc_Error_Tbl exist in the current default database.
  • The stored procedure GetNextBranchId also exists in the current default database.
  • The new stored procedure supports only 1000 accounts per branch.

Example Table Definitions

The following CREATE TABLE statements define two important tables, tAccounts and Proc_Error_Tbl, that are used in the sample stored procedure. Note that all tables and the stored procedures referenced in the stored procedure body must also be created.

This DDL defines the accounts table:

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) ;

This DDL defines the error table:

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 );

Stored Procedure Definition

The following CREATE PROCEDURE statement creates the sample stored procedure. The definition is also called the “source text” for the stored procedure.

This CREATE PROCEDURE statement creates a procedure named AddBranch that supports the internal functions of a bank:

  • Capture and add details of the new branch to the table tBranch.
  • Assign a BranchId to a new branch.
  • Add details of new accounts of a branch to the table tAccounts.
  • Update balances and interest in the accounts contained in the table tAccounts for the new branch.
    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.

Compiling the Procedure From an Input File

If you want to create the stored procedure AddBranch using the COMPILE command in BTEQ, you must submit the entire stored procedure definition in an input file.

The procedure is compiled with some warnings if any of the database objects referenced in the stored procedure body are missing or deleted.

Compilation results in errors and the stored procedure is not created if any database object referenced in the cursor SELECT statement is missing.

When CREATE PROCEDURE is executed from CLIv2, ODBC, or JDBC, an SPL compiler in the Teradata Database compiles the stored procedure.