15.00 - Creating and Using Stored Procedures - Basic Teradata Query

Basic Teradata Query Reference

Product
Basic Teradata Query
Release Number
15.00
Content Type
Programming Reference
Publication ID
B035-2414-034K
Language
English (United States)

Creating and Using Stored Procedures

Types of Stored Procedures

BTEQ supports two types of stored procedures:

  • External stored procedures
  • SQL stored procedures
  • External Stored Procedures

    An external stored procedure is defined with a CREATE PROCEDURE or REPLACE PROCEDURE statement, along with EXTERNAL references for source files.

    For example:

       CREATE PROCEDURE xyz
       (IN data1 INTEGER, OUT data2 INTEGER)
       LANGUAGE CPP NO SQL PARAMETER STYLE TD_GENERAL
       EXTERNAL NAME 'CS!xyz!source.cpp';

    The C or C++ files can reside on the server or the client. The server calls for the transfer of client-resident files as needed to satisfy input requirements for the CREATE PROCEDURE or REPLACE PROCEDURE request. Source files must be encoded as ASCII (workstation) or EBCDIC (mainframe), regardless of the current session character set. See SQL External Routine Programming for more information.

    The retrieval of client-resident external stored procedure files is handled the same way that User-Defined Function files are handled. See “Specifying Files for User-Defined Functions” on page 61 for more details.

    SQL Stored Procedures

    An SQL (internal) stored procedure is created or replaced using the COMPILE command, which takes a single SQL Stored Procedure Language (SPL) file as input. The encoding of this file must match that of the current session character set.

    The remainder of this section describes the details of using regular SQL stored procedures.

    Command Execution

    Specify the following command to create SQL stored procedures on the Teradata server:

       .COMPILE FILE = example.spl;

    Note: The COMPILE command processes only SQL stored procedures.

    As BTEQ processes the COMPILE command, it uses the specified file’s content as the SQL stored procedure source text input. It sends the SQL stored procedure source text to the Teradata server for processing the SQL stored procedure definition.

    Creating or Replacing an SQL Stored Procedure

    To create an SQL stored procedure, invoke a text editor to create a file named example.spl, and enter the SQL stored procedure source text:

       CREATE PROCEDURE samplesp1 ()
       BEGIN
          /* SPL Statements*/
       DECLARE V1 INTEGER;
       SET V1 = 100;
       END;

    The preceding example creates a procedure without parameters.

    To replace this with another SQL stored procedure with parameters, create a file named example1.spl, and enter the SQL stored procedure source text:

       REPLACE PROCEDURE samplesp1 (IN pAccountNo INTEGER, OUT pAmount DECIMAL (10,2))
       BEGIN
          /* SPL Statements*/
          SELECT : Amount
          INTO : pAmount
          FROM : Account1 WHERE
          aCCTnO = :pAccountNo;
       END;

    Submit the COMPILE command with this file name.

    Executing Stored Procedures

    Execute stored procedures either interactively, or include them in BTEQ scripts or input stream files.

    Use the Teradata SQL CALL statement to execute a stored procedure interactively:

       CALL samplesp1 (8888, pAmount);

    If the procedure is created with parameters, specify the parameter arguments within the parenthesis.

    Including a Stored Procedure in a Script

    Use a text editor to include a stored procedure in a BTEQ script or input stream file. To execute the procedure samplesp1 from a script, for example, enter the Teradata SQL CALL statement after the LOGON statement in the script:

       .LOGON tdpid/userid, password
       CALL samplesp1 (8888, pAmount);
       .LOGOFF

    Command Execution Messages

    Executing the sample SQL stored procedure act, specified in the file example.spl, and containing one parameter, BTEQ produces the following command execution messages in the  SYSPRINT file:

       .logon mydbs/tdsp01
       Password: 
       *** Logon successfully completed.
        *** Transaction Semantics are BTET.
        *** Character Set Name is 'ASCII'.
       *** Total elapsed time was 1 second.
       
       BTEQ -- Enter your SQL request logon or BTEQ command: 
       .compile file example.spl
       
       
        *** Procedure has been created. . 
        *** Total elapsed time was 5 seconds.
       
       BTEQ -- Enter your SQL request logon or BTEQ command: 
       call samplesp1 (8888, pAmount);
       
       *** Procedure has been executed.
        *** Total elapsed time was 1 second.
       
       pAmount
       ---------
       10000.00
       
       BTEQ -- Enter your SQL request logon or BTEQ command: 
       sel * from account1;
       
       *** Query completed. One row found. 2 columns returned. 
        *** Total elapsed time was 1 second.
       
       accountno 	amount
       --------  ---------
       8888      10000.00
       
       .LOGOFF
       *** You are now logged off from the DBC.

    Using the Script File

    The following example shows how to create and execute an SQL stored procedure from the script file, when the script is running in ANSI mode. The example.spl file contains the stored procedure source text.

       .LOGON e/fml,notebook
       .COMPILE FILE = example.spl;
       COMMIT;
       CALL samplesp1 (8888, pAmount);
       .LOGOFF

    The next example is a script for a session running in Teradata mode:

       .LOGON e/fml,notebook
       BT;
       .COMPILE FILE = example.spl;
       ET;
       CALL samplesp1 (pAccountNo, 10000.00);
       .LOGOFF