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.
