Example: INSERT Operation Followed by a SELECT Verification Operation
The following request creates a macro that first inserts a row for a new employee in the employee table, then performs a SELECT request to verify that the information was entered correctly:
CREATE MACRO new_emp1oyee ( number INTEGER, name VARCHAR(12), dept INTEGER DEFAULT 900, position VARCHAR(12) sex CHARACTER, 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 -- comment construct to include comments in the macro. Text appearing after -- and up to the end of the line is not performed.
If this macro is performed in ANSI session mode, the INSERT has not been committed. This is also true in Teradata session mode when the macro is performed as part of an explicit transaction.
Example: INSERT Operation Followed By an UPDATE Operation
This example creates a macro that also inserts a row for a new employee in the employee table, then performs an UPDATE request rather than a SELECT request. The UPDATE request 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 new_employee_2 ( (number INTEGER, name VARCHAR(12), dept INTEGER DEFAULT 900, position VARCHAR(12), sex CHARACTER, 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 performed in ANSI session mode, the INSERT and UPDATE requests have not been committed. This is also true in Teradata session mode when the macro is performed as part of an explicit transaction.
Example: Using REPLACE MACRO
The following statement replaces a macro.
REPLACE MACRO new_employee(name VARCHAR(12) NOT NULL, street CHARACTER(30), city CHARACTER(20), number INTEGER NOT NULL, dept SMALLINT DEFAULT 999) AS (INSERT INTO employee (name, street, city, empno, deptno) VALUES (:name, :street, :city, :number, :dept); UPDATE department SET empcount = empcount + 1 WHERE deptno = :dept ;);
Example: Macro Support for UDT Parameters
In this example, the p2 parameter of the macro is a VARCHAR, which is passed through to the macro. The conversion is performed within the macro itself using the NEW constructor expression. See “NEW” in Teradata Vantage™ - SQL Operators and User-Defined Functions, B035-1210.
CREATE MACRO m2 (p1 integer, p2 VARCHAR(100)) AS (INSERT t1(:p1, NEW structured_type(:p2));); USING (a INTEGER, b VARCHAR(100)) EXEC m2(:a, :b);
Example: Macro Support for UDT Parameters with Triggers
Suppose you have a macro named ins_mail_list that inserts the first name, last name, and home address of new customers into a table named mailing_addresses. The value of the customer home address is defined with a structured UDT named address.
The macro is defined as follows:
CREATE MACRO insert_mail_list (first VARCHAR(15), last VARCHAR(15), addr address) AS (INSERT INTO mailing_addresses VALUES (:first, :last, :addr););
The following trigger adds new California customers to a mailing list whenever a new customer row is inserted into the customer table. The insert into the mailing_addresses table is done using the triggered SQL action statement, the macro insert_mail_list, which accepts the UDT parameter address:
CREATE TRIGGER ca_mailing_list AFTER INSERT ON customer REFERENCING NEW AS newrow FOR EACH ROW WHEN (newrow.address.state() = 'CA') EXEC insert_mail_list(newrow.name.last(), newrow.name.first(), newrow.address);
Example: Passing a TOP n Value Into a Macro as a Parameter
This example passes the integer value of n for the TOP n operator into a macro as a parameter named p.
CREATE MACRO m (p INTEGER) AS (SELECT TOP :p x1 FROM t1;);
Example: Setting the Transaction Query Band Using a Parameter
The following macro sets the transaction query band using the parameter qbin:
CREATE MACRO qbmac (p1 INTEGER, p2 INTEGER, qbin VARCHAR(200) AS (SET QUERY_BAND = :qbin FOR TRANSACTION SELECT GetQueryBand();); *** Macro has been created. *** Total elapsed time was 1 second. EXEC qbmac (5,10,'music=classical;musician=david_tudor;'); *** Set QUERY_BAND accepted. *** Total elapsed time was 1 second. *** Query completed. One row found. One column returned. GetQueryBand() -------------------------------------------------------- =T> music=classical;musician=david_tudor;
Example: Invoking an SQL UDF From a Macro Definition
The following example invokes the SQL UDF common_value_expression within a SELECT request encapsulated within the definition of the macro m1.
CREATE MACRO m1 (a1 INTEGER, b1 INTEGER, c1 INTEGER) AS (SELECT test.common_value_expression(:a1 = :b1) FROM t1 WHERE t1.a1 = :c1; );
Example: Macro Support for UDT Parameters using the NEW Constructor Invocation Before Macro Invocation
The following example demonstrates macro support for UDT parameters.
The p2 parameter of the macro is a structured UDT.
The conversion from VARCHAR to the structured UDT is done using the NEW constructor invocation expression, which is performed before the macro is actually invoked.
CREATE MACRO m1 (p1 INTEGER, p2 structured_type) AS (INSERT t1(:p1, :p2);); USING (a INTEGER, b VARCHAR(100)) EXEC m1(:a, NEW structured_type(:b));
Example: Specifying an ABORT or ROLLBACK Condition
You can include a condition for halting execution of a macro by incorporating an ABORT or ROLLBACK request into its definition. 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 new_employee macro from being used to add employees to the Executive Office, department 300, incorporate an ABORT request into the macro specification, as follows:
CREATE MACRO personnel.new_employee (number (SMALLINT FORMAT '9(5)'), name (VARCHAR(12)), dept (SMALLINT FORMAT '999'), position (VARCHAR(12)), birthdate (DATE FORMAT 'MMMbDDbYYYY'), sex (CHARACTER(1)) education (BYTEINT)) AS ( (ABORT 'Department 300 not valid' WHERE :dept = 300; INSERT INTO employee (empno,name,deptno,jobtitle,dob,sex,edlev) VALUES (:number,:name,:dept,:position,:birthdate,:sex,:education); );
Specify the text of an optional error message following the ABORT keyword and enclose it in APOSTROPHE characters. This message displays on the terminal screen if the macro is aborted for the specified condition. In this example, the abort condition, :dept = 300, is specified in the WHERE clause of the ABORT request.
Example: DELETE With an ABORT Condition
This example shows a DELETE request, and then decrements the employee count for the department table. The ABORT request terminates macro execution if the row for the employee being deleted is not present in the employee table.
The example shows a macro designed for use in ANSI mode, and for which the user wants to commit if the delete and update operations are successful. Note that the requests in the body of the macro are entered as one multistatement request. Therefore, if the WHERE condition of the ROLLBACK statement is met, the entire request is aborted and the value in empcount is protected.
CREATE MACRO delete_employee (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 WORK; );