CREATE/REPLACE MACRO Examples | Teradata Vantage - CREATE MACRO and REPLACE MACRO Examples - Advanced SQL Engine - Teradata Database

SQL Data Definition Language Syntax and Examples

Product
Advanced SQL Engine
Teradata Database
Release Number
17.05
Published
January 2021
Language
English (United States)
Last Update
2021-01-22
dita:mapPath
ncd1596241368722.ditamap
dita:ditavalPath
hoy1596145193032.ditaval
dita:id
B035-1144
lifecycle
previous
Product Category
Teradata Vantage™

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 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; );