Books
in black and white
Main menu
Share a book About us Home
Books
Biology Business Chemistry Computers Culture Economics Fiction Games Guide History Management Mathematical Medicine Mental Fitnes Physics Psychology Scince Sport Technics
Ads

Teradata RDBMS forUNIX SQL Reference - NCR

NCR Teradata RDBMS forUNIX SQL Reference - NCR, 1997. - 913 p.
Download (direct link): teradataforunix1997.pdf
Previous << 1 .. 144 145 146 147 148 149 < 150 > 151 152 153 154 155 156 .. 241 >> Next


EXECUTE

EXECUTE

Function

The EXECUTE statement executes a macro that was defined using the CREATE MACRO statement. A macro is a stored set of statements that is executed as an entity before any result is returned to the user.

EXECUTE is flagged as non-ANSI, when the SQL flagger is enabled.

Syntax

EXECUTE-EXEC-----

- macroname-

(

( -1- param_name = const_expr -- )

)

( const_expr )

)

CJ

FF07A023

where:

Syntax Element... Specifies . . .
macroname the name of the macro that is to be executed.

Teradata RDBMS for UNIX SQL Reference

8-137
Teradata SQL Syntax Guide

EXECUTE

Privileges

Rules for Executing Macros

Syntax Element... Specifies . . .
const_expr a constant or an expression involving constants that specifies a parameter value. Values are listed in left-to-right order according to the order in which parameter names were established in the CREATE MACRO statement. Values can be null e.g., (a, , b) This is referred to as a positional parameter list. If a value is not supplied for a parameter, a comma must be entered to account for the missing parameter. Any default value defined for that parameter is inserted automatically.
param_name = const_expr a parameter name (param_name) as defined in the CREATE MACRO statement, and supplies the value for that parameter. This is referred to as a named parameter list. The value may be a constant, or an expression involving constants. If a parameter is omitted, any default value defined for that parameter is inserted automatically.

To execute a macro, the user must have EXECUTE privilege on the macro. The creator or any owner of the macro may grant the EXECUTE privilege to another. In addition, the immediate owner of the macro (the database in which the macro resides) must have the necessary privileges on objects named in the statement(s) that are contained in the macro.

A data definition statement in a macro is not fully resolved until the macro is submitted for execution. Unqualified references to database objects are then resolved using the default database of the executing user. Therefore, object references in data definition statements in macros should be fully qualified (e.g., databasename.tablename) in the macro body.

• The number of commas must match the macro definition.

• Any value in the EXECUTE parameter list may be a constant or an expression involving constants. In this context, DATE, TIME, and USER are considered constants.

8-138

Teradata RDBMS for UNIX SQL Reference
Teradata SQL Syntax Guide

EXECUTE

Macros, NULL Inserts, and Access Logging

Examples

Example 1

Example 2

Example 3

• If an error message is returned when a macro is executed, it may be caused by a SQL statement in the macro.

• The number of parameters used in the calling sequence must be equal to the number of parameters defined.

• When, for example, two parameters are defined and used, if they are both null, the following statements are all valid, unless defaults have been used in the macro definition:

execute macro '(, 1); execute macro (,); execute macro (null, null);

When a macro attempts to update a row containing a null in a column defined as NO NULLS for that column, the EXECUTE MACRO statement that made the attempt is not logged in the access log even though the logging frequency is set to EACH and ALL objects are specified.

The following examples illustrate the use of EXECUTE:

The following statement uses a named parameter list to execute macro NewEmpl (refer to “CREATE MACRO” statement). Note that named parameters can be listed in any order.

EXEC NewEmpl

(number=10015, dept=500, name='Omura H', sex='M', Position=rProgrammer');

The row for new employee Omura is inserted in the Employee table.

This example uses a positional parameter list to execute macro NewEmp2 (refer to “CREATE MACRO” statement). Note that a value is not specified for the dept parameter, which has a macro-defined default value. A comma is entered in the dept position to maintain the integrity of the positional sequence.

EXEC NewEmp2 (10021, 'Smith T', , 'Manager', 'F',

'May 8, 1959', 16);

When the following statement is processed, the default value for the dept parameter (900) is inserted automatically. The row for new employee Smith is added to the Employee table, and then the Department table is updated by incrementing the value for Department 900 in the EmpCount column. The statement uses a named parameter list to execute a macro named NewHire. Note that the value of the DOH (Date of Hire) column is an expression involving the DATE constant.

EXEC NewHire (FlName='Toby Smith', Title='Programmer',

DOH=DATE -1);

Teradata RDBMS for UNIX SQL Reference

8-139
Teradata SQL Syntax Guide

EXPLAIN Modifier

EXPLAIN Modifier

The EXPLAIN modifier returns a summary of the steps the Teradata RDBMS would perform to resolve a request. The request itself is not entered for processing.
Previous << 1 .. 144 145 146 147 148 149 < 150 > 151 152 153 154 155 156 .. 241 >> Next