How to create a Macro
Create a macro to generate a DOB list for department 321:
CREATE MACRO DOB_Details AS
(SELECT first_name ,last_name ,DOB
WHERE dept_numbr =321
ORDER BY DOB asc;);
EXECUTE a Macro
To execute a macro, call it along with the exec command.
last_name first_name DOB
Ram Kumar 75/02/22
Laxman Sinha 79/04/06
DROP a Macro
To drop a macro, use following command .
DROP MACRO DOB_Details;
REPLACE a Macro
If we need to modify an existing macro , instead of dropping and re-creating it
We can use replace macro command as follows
REPLACE MACRO DOB_Details AS
(SELECT first_name,last_name ,DOB
WHERE dept_numbr = 321
ORDER BY DOB, first_name;);
Parametrized macros allow usage of variables . we can pass values to these variables. Advantage of using parametrized macros is , Values can be passed to these variables at run-time.
CREATE MACRO dept_list (dept INTEGER) AS
WHERE dept_numbr = :dept; );
To Execute the macro
EXEC dept_list (321);
Macros may have more than one parameter. Each name and its associated type are separated by a comma from the next name and its associated type. The order is important. The first value in the EXEC of the macro will be associated with the first value in the parameter list. The second value in the EXEC is associated with the second value in the parameter list, and so on.
CREATE MACRO emp_verify (dept_numbr INTEGER ,salary DEC(18,0))
WHERE dept_numbr = :dept
AND salary< :sal;) ;
To Execute this macro
EXEC emp_check (301, 50000);
Key points to note about Macros:
- Macros are a Teradata extension to SQL.
- Macros can only be executed with the EXEC privilege.
- Macros can provide column level security.
he has privileges for the underlying tables or views that the macro uses.