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):
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
--------- ------------- ------------ ------------
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.
nice
ReplyDeletewhy should macros in teradata? what is the purpose of macros in teradata?
ReplyDeleteAny DBQL table where Macro execution history is stored.?
ReplyDelete