Basic Stored Procedure

Teradata Series Stored Procedures: The basics

Version: Creation date: Modified date: Author:

0.1 2005/05/17 João Miguel Lopes

Teradata Series Stored Procedures

0

1 2

3 4 5 6 7

Initial Considerations ..................................................................................................................................2 0.1 Target readers ..................................................................................................................................2 0.2 Teradata database version and hardware context ..................................................................................2 0.3 About Teradata .................................................................................................................................2 Introduction...............................................................................................................................................3 Structure...................................................................................................................................................4 2.1 Stored procedure parameters..............................................................................................................4 2.2 Declaration Area................................................................................................................................5 2.3 Condition and Control Statement Handling Area ....................................................................................6 2.4 Control and SQL Area.........................................................................................................................7 Dynamic SQL in stored procedures ...............................................................................................................9 Recursive stored procedures ...................................................................................................................... 10 Privileges................................................................................................................................................. 13 Stored procedures versus Macros ............................................................................................................... 14 Important considerations........................................................................................................................... 15

0 INITIAL CONSIDERATIONS This document describes the basics techniques, structure, syntax and semantics for stored procedure development in Teradata DBMS environment.

0.1

Target readers

Target readers for this document are analysts who are giving the first few steps in developing or modelling solutions for Terad ata database environments. Previous knowledge of Oracle database architecture/PL -SQL syntax is helpful but not necessary.

0.2

Teradata database version and hardware context

The examples provided for illustration purposes were developed against a demo version of V2R5 (limited to 1 Gb) on a single 1.6 Mhz Intel Centrino processor machine, equipped with 1Gb of RAM and an IDE hard drive, running Microsoft Windows XP Professional Service Pack 2 as operating system. Therefore, all explain plans presented for select SQL statements may differ if executed against different database versions and/or in different hardware contexts.

0.3

About Teradata

Teradata is a division of NCR Corporation and maintains global headquarters in Dayton, Ohio. Over the course of 25 years, Ter adata has emerged as a worldwide provider in enterprise data warehousing, with an impressive roster of clients. With over 750 customers in 2004, Teradata claims 65% market share within the top global retailers.

TERADATA S ERIES

Stored Procedures – The Basics

Page 2/15

Teradata Series Stored Procedures

1 INTRODUCTION By definition, stored procedures are written in SQL and consist of a set of control and condition handling statements that make SQL a computationally complete programming language. This type of objects is also known as Persistent Stored Modules in the ANSI SQL-99 standard (from which Teradata stored procedure facilities are a subset and conform for semantics). They provide a procedural interface to the Teradata d atabase engine and many of the same benefits as embedded SQL. In this document we will approach the basic aspects of stored procedures like its structure and most common techniques for development (dynamic SQL and recursion ) and with the intent to provide some knowledge of how Teradata SQL differs from Oracle’s PL/SQL. Target Pricing’s 1 stored procedures are used as example for comparison between Teradata and Oracle SQL. Teradata does not have the concept of objects like “package” and “function”, implementing only the stored procedure object. Therefore, when migrating from Oracle to Teradata all packages and functions must be c onverted to stored procedures.

1

The Target Pricing version considered in this document is 1.0 TERADATA S ERIES

Stored Procedures – The Basics

Page 3/15

Teradata Series Stored Procedures

2 STRUCTURE Typically a stored procedure is composed by 2 four distinct parts. The following diagram depicts those areas by the correct order. CREATE PROCEDURE proc_name (parameters)

Declaration Area (Local variables, cursor declarations …)

Condition Handler Area (Exceptions, error catch & throw …)

Control and SQL Area DML, DDL, and DCL statements supported by stored procedures, including dynamic SQL (insert, update, create, loop …)

END;

Note that, in Oracle, stored procedures have a similar structure . The only difference is that the condition handling area is located below the control and SQL area. This does not mean that stored procedures as a whole are similar between these two DMBSs … SQL code is quite different as we will have the opportunity to see in the next f ew sections.

2.1

Stored procedure parameters

Stored procedure parameters can be of three types: §

IN – input parameters are used for passing values into de procedure;

§

OUT – output parameters are used for passing values from the procedure;

§

INOUT – input/output parameters are used for passing values into the procedure and out.

Each stored procedure can have up to 256 parameters regardless of its data type (all data types supported by Teradata can be used for stored procedure parameters ). Unlike Oracle, param eters can’t be defined with a default value. To workaround this limitation one must declare a variable and initialize it with the desired default value. 2

If a stored procedure has one and only one SQL or control statement (including dynamic SQL) the declaration and handling statements are dispensable. TERADATA S ERIES

Stored Procedures – The Basics

Page 4/15

Teradata Series Stored Procedures

Stored procedure parameters and their attributes are stored in the ‘DBC.TVFields’ table of the data dictionary. Example REPLACE PROCEDURE get_comp_promo_flag (IN p_pz_key

INTEGER,

IN p_sku_key

INTEGER,

IN p_vip_key

INTEGER,

OUT o_cpf

VARCHAR(1))

Because Teradata’s DBMS forces the CALL statement (instruction used to execute a stored procedure) to describe the parameters with the EXACT same name, the definition of parameter names in accordance with the adopted nomenclature is of the utmost importance.

2.2

Declaration Area

The declaration area holds variable declaration and cursor definition statements. All variables are private 3 to the stored procedure and can only be referenced inside the procedure body. Syntax §

Variable declaration

§

Cursor declaration

Please note that all ‘DECLARE CURSOR’ statements must be specified after local variable declarations and before any condition handler declarations. Examples §

Variable declaration

DECLARE error_msg VARCHAR(30) DEFAULT ’NO ERROR’;

3

the concept of global variables, like those defined in package headers in Oracle does not natively exists in Teradata. TERADATA S ERIES

Stored Procedures – The Basics

Page 5/15

Teradata Series Stored Procedures

DECLARE sku_key, store_key INTEGER;

§

Cursor declaration

DECLARE DeptCursor CURSOR FOR SELECT DeptName from Department;

CREATE PROCEDURE sp1() BEGIN DECLARE empcursor CURSOR FOR SELECT * FROM employee WHERE deptcode = 101 FOR UPDATE;

For more information about cursors please refer to “ Teradata Series – Cursors”.

2.3

Condition and Control Statement Handling Area

The condition and control statement handling area is where one defines how to the procedure should handle runtime errors and/or ex ceptions. There are two types of condition handlers: §

‘CONTINUE’

§

‘EXIT’

The difference between the two types is that ‘CONTINUE’ passes control to the next statement within the compound statement and ‘EXIT’ passes control to the next statement in the stored procedure outside the compound statement that contains the handler. Unlike Oracle, Teradata has only three types of generic condition handlers: §

SQLEXCEPTION

§

SQLWARNING

§

NOT FOUND

Other forms of condition handling are called SQLSTATE -based and must be explicitly declared in the handling declaration statement. Best Practise! Handling conditions (especially SQLWARNING and SQLEXCEPTION) should always be defined to ensure Enabler’s policy regarding defensive programming) Syntax

TERADATA S ERIES

Stored Procedures – The Basics

Page 6/15

Teradata Series Stored Procedures

Examples DECLARE EXIT HANDLER FOR NOT FOUND set o_cpf='M';

DECLARE EXIT HANDLER FOR SQLSTATE ’42000’ INSERT INTO error_log (:SQLSTATE, CURRENT_TIMESTAMP, ...);

For more information about condition handling please refer to “ Teradata Series – Condition and Control Statement Handling”.

2.4

Control and SQL Area

The procedure body - set of statements constituting the main tas ks of the stored procedure - can be a single control statement or SQL statement, or a BEGIN … END compound statement (sometimes called a block) which can also be nested. In other words, this is the place where the real action takes place! In the next few sections we will describe some of the most relevant components that are a part of the procedure body.

2.4.1

Labels

2.4.2

Compound statements

Examples create procedure get_currency_key (IN

p_pz_key

INTEGER,

IN

p_sku_key INTEGER,

IN

p_vip_key INTEGER,

OUT o_ck

INTEGER)

BEGIN DECLARE CONTINUE HANDLER FOR SQLEXCEPTION set o_ck=-1; DECLARE CONTINUE HANDLER FOR NOT FOUND set o_ck=-2;

TERADATA S ERIES

Stored Procedures – The Basics

Page 7/15

Teradata Series Stored Procedures

select currency_key into :o_ck from ss_comp_pz_prices f where f.pz_key=:p_pz_key and f.sku_key=:p_sku_key and f.comp_store_key=:p_vip_key; END;

The procedure above receives three parameters and with them determines and returns the corresponding currency surrogate key.

TERADATA S ERIES

Stored Procedures – The Basics

Page 8/15

Teradata Series Stored Procedures

3 DYNAMIC SQL IN STORED PROCEDURE S Dynamic SQL is a method of invoking an SQL statement (whose request text can vary from execution to execution) by compiling and performing it at runtime from within a stored procedure. One may invoke DDL, DML or DCL statements, with some exceptions, as dynamic SQL in a stored procedure. As in Oracle, dynamic SQL statements are not validated at compile time. Th e validation is done only during execution of the stored procedure. Syntax

Example (...) BEGIN DECLARE sales_columns VARCHAR(128) DEFAULT '(item INTEGER,price DECIMAL(8,2),sold INTEGER)'; CALL DBC.SysExecSQL('CREATE TABLE ' || my_database ||'.' || my_table || my_columns) ; END; (...)

Please note that dynamic SQL syntax in stored procedure context is different from embedded SQL (the expression embedded SQL refers to SQL statements performed or declared from within a client application ). While in stored procedure one must use the ‘CALL DBC.SysExecSQL’ statement to order the execution of a query string, in embedded SQL applications this is done with the EXECUTE IMMEDIATE statement. Please refer to Teradata’s Documentation for details on developing embedd ed SQL applications.

TERADATA S ERIES

Stored Procedures – The Basics

Page 9/15

Teradata Series Stored Procedures

4 RECURSIVE STORED PROC EDURES A stored procedure can be recursive, referencing itself directly or indirectly. That is, the stored procedure body can contain a CALL statement invoking the procedure being defined. Such CALL statements c an also be nested. Please note that, w hen creating the stored procedure if it directly references or invokes itself, the procedure is created with an SPL compilation warning (not an error) because the referenced object (the procedure) does not exist. This behaviour of a self-referencing stored procedure is different from the general norm, where any reference to a missing object results in a compilation error (not warning) and the stored procedure is not created. No specific limit exists on the level of recursion, but the stored procedure nesting limit of 15 applies. The limit is further reduced, if there are any open cursors. Recursion is a powerful technique that must be used wisely. Its application, if not well defined, can create endless loops and ultima tely bring down the database performance. Therefore, when developing recursive stored procedures one must always guarantee that the operation is finite and all unnecessary memory structures, locks, etc. are released. Recursion types: Single We call simple to one side recursion (the procedures calls itself) .

SProc A

Mutual You can also create mutually recursive stored procedures, that is, procedures invoking each other in a stored procedure body. This is an indirect recursion.

SProc A

SProc B

Chain You can extend the mutual recursion process to have a recursion chain through multiple procedures (A calls B, B calls C, and C calls A).

TERADATA S ERIES

Stored Procedures – The Basics

Page 10/15

Teradata Series Stored Procedures

SProc A

SProc B

SProc C

Examples A stored procedure that directly references itself: CREATE PROCEDURE spCall(INOUT empcode INTEGER, INOUT basic

DECIMAL (6, 2))

BEGIN IF (empcode < 1005) THEN SELECT empbasic INTO basic FROM Employee WHERE empcode = empcode ; INSERT Temptab(empcode, basic); SET empcode = empcode + 1; CALL spCall(empcode, basic); END IF; IF (empcode = 1005) THEN SET empcode = empcode - 1; SELECT max(empbasic) INTO basic from Temptab;

When

the

stored

procedure

is

compiled, a compilation warning (SPL5000: appears, and the procedure ‘spCall’ is created

Table/view/trigger/procedure ‘spCall’ does not exist)

successfully. Mutual recursion between two stored procedures: First, and to avoid compilation errors, one must create an empty stored procedure. CREATE PROCEDURE db1.Sp1(INOUT p1 INTEGER) BEGIN END;

Now that the database object db1.Sp1 exists, we may create a second stored procedure that will invoke db1.Sp1: CREATE PROCEDURE db1.Sp2(INOUT p1 INTEGER) BEGIN IF (p1 > 0) THEN CALL db1.Sp1(p1- 1); END IF; END;

No errors found. Now just replace db1.Sp1 with the intended set of instructions: TERADATA S ERIES

Stored Procedures – The Basics

Page 11/15

Teradata Series Stored Procedures

REPLACE PROCEDURE db1.Sp1(INOUT p1 INTEGER) BEGIN IF (p1 > 0 ) THEN CALL db1.Sp2(p1 - 1); END IF; END;

TERADATA S ERIES

Stored Procedures – The Basics

Page 12/15

Teradata Series Stored Procedures

5 PRIVILEGES To create/drop/compile/execute a stored procedure the database user must have the necessary privileges. As in Oracle, to manage privileges on stored procedures one can use the ‘GRANT’ and ‘REVOKE’ statements. Syntax

Examples: §

This example shows how to grant/revoke the execution privilege on a stored procedure to a specific user.

GRANT EXECUTE PROCEDURE ON target_pricing_db.get_comp_promo_flag TO tp_dm; REVOKE EXECUTE PROCEDURE ON target_pricing_db.get_comp_promo_flag TO tp_dm;

§

This example shows how to grant all privileges except drop on a stored procedure to a specific user .

GRANT ALL BUT DROP PROCEDURE ON target_pricing_db.get_comp_promo_flag TO tp_dm WITH GRANT OPTION;

§

This example removes all privileges given above except the execute procedure.

REVOKE ALL BUT EXECUTE PROCEDURE ON target_pricing_db.get_comp_promo_flag TO tp_dm;

Be aware that the ‘EXECUTE PROCEDURE’ is granted automatically only to the creator of a stored procedure when the object is created. The database engine does not grant this privilege automatically to the owner of the stored procedure when the owner and cre ator are not the same.

TERADATA S ERIES

Stored Procedures – The Basics

Page 13/15

Teradata Series Stored Procedures

6 STORED PROCEDURES VER SUS MACROS As previously stated, Teradata doesn’t implement the concept of packages or functions like Oracle. But, it does implement a type of object that is unavailable in Oracle: Macros. This document does not provide extensive information about them but it is useful to enforce the difference between this type of object and stored procedures. Macros database objects are SQL statements that the server stores and executes by performing a single statement. Yo u automatically get a multi statement request without coding it if you write multiple SQL statements within a macro because macros are always performed as a single request. The macro code can also specify parameters that are replaced by data each time the macro is performed. The most common way of substituting for the parameters is to specify a USING. Macros are usually more efficient for repetitive queries than single DML statements. Unlike stored procedures, macros are not designed to return parameters to the requestor: they return only the answer set for the SQL statements they contain . Macros should be used as code snippets, thus enhancing productivity through reutilization of simple tasks. One may look at macros also as a substitute for unreferenced s ql scripts (sql files usually stored in the filesystem). The following table highlights the major differences between stored procedures and macros:

TERADATA S ERIES

Stored Procedures – The Basics

Page 14/15

Teradata Series Stored Procedures

7 IMPORTANT CONSIDERATI ONS §

To be able to compile a stored procedure a C/C++ compiler must be installed and available in each node. This happens because pre -compiled source code is stored and held by the database for execution.

§

Be aware for parameter naming conventions. Teradata forces the CALL statement to enumerate stored procedure parameters with the exa ct same name as defined in the create procedure clause. For instance, if a stored procedure named SP1 has a INOUT parameter named P1 then, when calling this procedure for execution we must type: ‘CALL SP1(P1)’. The attempt to use any other parameter name w ill fail.

TERADATA S ERIES

Stored Procedures – The Basics

Page 15/15