15.10 - macro_name - Teradata Database

Teradata Database SQL Data Definition Language Syntax and Examples

prodname
Teradata Database
vrm_release
15.10
created_date
December 2015
category
Programming Reference
featnum
B035-1144-151K

The name of the new macro. If a fully qualified name is not specified, the default database or user is used.

For information about naming database objects, see SQL Fundamentals, B035-1141.

The name of a macro must be unique within its containing user or database.

database_name
The name of the containing database for macro_name if something other than the current database.
user_name
The name of the containing user for macro_name if something other than the current user.

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