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 .. 123 124 125 126 127 128 < 129 > 130 131 132 133 134 135 .. 241 >> Next


permitted. A macro, table, or view may take the same name as the database in which it is created, but a macro named TEST and a table named TEST cannot co-exist in the same database.

Creating Macros

Use the following procedure to create a macro.

Step

Action

Identify the parameters.

Following the AS keyword, specify the SQL statements to perform the task, each terminated by a semicolon.

The entire set of statements is enclosed in parentheses.

Terminate the CREATE MACRO statement with a final semicolon.

1

2

3

Parameters are values that are entered by the user into the Rules for Using EXECUTE statement for use by the macro during execution. Use of

Parameters in Macros parameters is optional in a macro and, when required, are specified

as part of the CREATE MACRO statement.

In defining parameters for a macro, follow the macro name in your CREATE MACRO statement with the names and attributes of the appropriate parameters. Note that data type definitions are required for each parameter. Other attributes may include format specifications or default values. Define new formats via a format phrase and defaults by a default control phrase, as necessary.

In executing the macro, if you supply a parameter value in the EXECUTE statement that does not conform to the specified format, data type, or default value, execution halts and an error message is displayed.

8-66

Teradata RDBMS for UNIX SQL Reference
Teradata SQL Syntax Guide

CREATE MACRO

The following examples illustrate the use of CREATE MACRO:

Examples

The following statement creates a macro that first inserts a row for a Example 1 new employee in the Employee table, then executes a SELECT

statement to verify that the information was entered correctly:

CREATE MACRO NewEmp1 (number INTEGER, name VARCHAR(12), dept INTEGER DEFAULT 900, position VARCHAR(12) sex CHAR,

dob DATE FORMAT 'MMMbDDbYYYY', edlev BYTEINT )

AS ( INSERT INTO Employee (Empno, Name, DeptNo,

JobTitle, Sex, DOB, EdLev)

VALUES (:number, :name, :dept, :position,

:sex, :dob, :edlev) ;

-- The following select verifies the insert

SELECT * FROM Employee WHERE EmpNo = :number ; ) ;

Use the -- construct to include comments in the macro. Text appearing after -- and up to the end of the line, is not executed.

If this macro is executed in ANSI mode, the INSERT has not been committed. This is also true in Teradata mode, where the macro is executed as part of an explicit transaction, which is not common.

This example creates a macro that also inserts a row for a new Example 2 employee in the Employee table, but then executes an UPDATE

statement rather than a SELECT statement. The UPDATE statement changes the Department table by incrementing the employee count in the row containing a department number that matches the value of the :dept parameter.

CREATE MACRO NewEmp2 ( number INTEGER, name VARCHAR(12), dept INTEGER DEFAULT 900, position VARCHAR(12), sex CHAR,

dob DATE FORMAT 'MMMbDDbYYYY', edlev BYTEINT )

AS ( INSERT INTO Employee (Empno, Name, DeptNo,

JobTitle, Sex, DOB, EdLev)

VALUES (:number, :name, :dept, :position,

:sex, :dob, :edlev) ;

UPDATE Department SET EmpCount=EmpCount+1 WHERE DeptNo = :dept ; ) ;

If this macro is executed in ANSI mode, the INSERT and UPDATE have not been committed.

Teradata RDBMS for UNIX SQL Reference

8-67
Teradata SQL Syntax Guide

CREATE MACRO

Example 3

Example 4

Here, the INSERT statement is replaced by the DELETE statement, and the departmental count of employees in the Department table is decremented. The ABORT statement terminates macro execution if the row for the employee being deleted is not present in the Employee table.

This example shows a macro designed for use in ANSI mode, and for which the user wants to commit if the delete and update are successful. Note that the statements in the body of the macro are entered as one multi-statement request. Therefore, if the WHERE condition of the ROLLBACK statement is met, the entire request is aborted and the value in EmpCount is protected.

CM DelEmp (num SMALLINT FORMAT '9(5)', dname VARCHAR(12), dept SMALLINT FORMAT '999')

AS ( ABORT'Name does not exist' WHERE :num NOT IN

(SELECT EmpNo FROM Employee WHERE Name = :dname)

; DELETE FROM Employee WHERE Name = :dname ; UPDATE Department SET EmpCount = EmpCount - 1 WHERE DeptNo = :dept; COMMIT; ) ;

You can include a condition for halting execution of a macro by incorporating an ABORT (ROLLBACK) statement into the macro. If the specified condition is encountered during execution, the macro is aborted (the transaction in process is concluded, locks on the tables are released, changes made to data are backed out, and any spooled output is deleted). As an example, to restrict the NewEmp macro from being used to add employees to the Executive Office, department 300, incorporate an ABORT statement into the macro specification, as follows:
Previous << 1 .. 123 124 125 126 127 128 < 129 > 130 131 132 133 134 135 .. 241 >> Next