Specifies an SQL request. Every request in the macro body must be terminated by a SEMICOLON character.
Parameter names referenced in the macro body must be prefaced by the COLON ( : )) character. The macro body can include EXECUTE requests to invoke other macros.
Example: Specifying an ABORT or ROLLBACK Condition
You can include a condition for halting execution of a macro by incorporating an ABORT or ROLLBACK request into its definition. If the specified condition is encountered during execution, the macro is aborted. The transaction in process is concluded, locks on the tables are released, changes made to data are backed out, and any spooled output is deleted.
As an example, to restrict the new_employee macro from being used to add employees to the Executive Office, department 300, incorporate an ABORT request into the macro specification, as follows:
CREATE MACRO personnel.new_employee (number (SMALLINT FORMAT '9(5)'), name (VARCHAR(12)), dept (SMALLINT FORMAT '999'), position (VARCHAR(12)), birthdate (DATE FORMAT 'MMMbDDbYYYY'), sex (CHARACTER(1)) education (BYTEINT)) AS ( (ABORT 'Department 300 not valid' WHERE :dept = 300; INSERT INTO employee (empno,name,deptno,jobtitle,dob,sex,edlev) VALUES (:number,:name,:dept,:position,:birthdate,:sex,:education); );
Specify the text of an optional error message following the ABORT keyword and enclose it in APOSTROPHE characters. This message displays on the terminal screen if the macro is aborted for the specified condition. In this example, the abort condition, :dept = 300, is specified in the WHERE clause of the ABORT request.
Example: DELETE With an ABORT Condition
This example shows a DELETE request, and then decrements the employee count for the department table. The ABORT request terminates macro execution if the row for the employee being deleted is not present in the employee table.
The example shows a macro designed for use in ANSI mode, and for which the user wants to commit if the delete and update operations are successful. Note that the requests in the body of the macro are entered as one multistatement request. Therefore, if the WHERE condition of the ROLLBACK statement is met, the entire request is aborted and the value in empcount is protected.
CREATE MACRO delete_employee (num SMALLINT FORMAT '9(5)', dname VARCHAR(12), dept SMALLINT FORMAT '999') AS (ABORT 'Name does not exist' WHERE :num NOT IN (SELECT empno FROM employee WHERE name = :dname); DELETE FROM employee WHERE name = :dname; UPDATE department SET empcount = empcount - 1 WHERE deptno = :dept; COMMIT WORK; );