Macros

A MACRO is a Teradata extension to ANSI SQL that contains prewritten SQL statements.
The actual text of the macro is stored in a global repository called the Data Dictionary (DD).

A macro allows you to name a set of one or more statements. When you need to execute those statements, simply execute the named macro. Macros provide a convenient shortcut for executing groups of frequently-run SQL statements.

Below is complete list of commands to manipulate macros.

CREATE MACRO macroname AS ( . . . );          Define a macro and store it in the DD.
EXEC macroname;                                             Execute statements within a macro.
SHOW MACRO macroname;                             Display a macro.
REPLACE MACRO macroname AS (. . . );        Apply changes to a macro or create a new one.
DROP MACRO macroname;                              Remove a macro definition from the DD.
EXPLAIN EXEC macroname;                            Display EXPLAIN text for the macro's execution.

Example for Creating Macro.

Create a macro to generate a birthday list for department 201:

   CREATE MACRO  birthday_list AS
                (SELECT   last_name
                         ,first_name
                         ,birthdate
                FROM     employee
                WHERE    department_number =201
                ORDER BY birthdate;);

To execute the birthday list macro:

   EXEC birthday_list;

last_name        first_name        birthdate
---------------   ---------------    ---------------
Morrissey       Jim                 43/04/29
Short           Michael             47/07/07

Notice that there is a semicolon before the closing parenthesis. This is a required element of macro syntax.

Use the DROP MACRO command to delete a macro.

   DROP MACRO birthday_list; 

This command removes the macro from the containing database and also removes its entry from the Data Dictionary.

You can modify a micro by using REPLACE Macro.

The above macro is modified as below with REPLACE Macro command.

REPLACE MACRO    birthday_list AS
   /*  Macro is being updated for sorting sequence */
                   (SELECT    last_name
                              ,first_name
                              ,birthdate
                   FROM       employee
                   WHERE      department_number = 201
                   ORDER BY   birthdate, last_name;);

Simple Parameterized Macros:

Parameterized macros allow substitutable variables. Values for these variables are supplied at runtime.

  CREATE MACRO dept_list (dept INTEGER)AS(
  SELECT   last_name
  FROM    employee
  WHERE   department_number = :dept;);

In parentheses following the macro name is the parameter list. It names each parameter followed by its data type. When a parameter is used in the body of a macro, it is always preceded by a colon.

 Macros with Multiple Parameters

CREATE MACRO emp_check (dept INTEGER
                            ,sal_amt DEC(9,2))
  AS
  (SELECT employee_number from employee
  WHERE department_number = :dept
  AND salary_amount < :sal_amt;);

EXEC emp_check (301, 50000);

Using a Parameterized Macro to Insert Data

CREATE MACRO new_dept
  ( dept INTEGER
  , budget DEC(10,2) DEFAULT 0
  , name CHAR(30)
  , mgr INTEGER)
 AS
 ( INSERT INTO department
  ( department_number
  , department_name
  , budget_amount
  , manager_employee_number)
      VALUES (  :dept
                  ,  :name
                  ,  :budget
                  ,  :mgr )
  ;
  SELECT department_number (TITLE ‘Number’)
  ,department_name (TITLE ‘Name’)
  ,budget_amount (TITLE ‘Budget’)
  ,manager_employee_number
  (TITLE ‘Manager’)
  FROM department
  WHERE department_number = :dept;

  );

EXECUTE the INSERT MACRO (With Positional Parameter Values):

• The macro consists of an INSERT statement followed by a SELECT.
• The SELECT simply reads the row just inserted.
• Input data must be in the order specified in the Macro parameter list.
• Input data must match the exact number of parameters specified in the list.
• Use the keyword NULL to explicitly pass a null to the macro.
• Use positional commas to implicitly pass a null, or a specified default value.

 EXEC new_dept    (505 ,610000.00 , 'Marketing Research', 1007);

Number          Name                       Budget         Manager
---------          -------------                ------------      ------------
505 Marketing Research   610000.00     1007



EXEC new_dept (102 , , 'Payroll', NULL);

Number          Name                  Budget         Manager
---------          -------------           ------------      ------------

 102               Payroll                 .00                ?

In the example above, the value after 102 has been omitted, hence the two commas in a row. In such cases the parameter value will be set to the default specified for in the CREATE MACRO statement. In this case it is 0.