Example : ABORT Statement
In the following example, the ABORT statement terminates macro execution if the row for the employee being deleted is not present in the employee table.
Statements in the body of the macro are entered as one multistatement request. Therefore, if the WHERE condition of the ABORT statement is met, the entire request is aborted and the accuracy of the count in the department table is preserved.
CREATE MACRO del_emp (num SMALLINT FORMAT '9(5)',
dname VARCHAR(12),
dept SMALLINT FORMAT '999') AS (
ABORT 'Name does not exist' WHERE :num NOT IN (
SELECT emp_no
FROM employee
WHERE UPPER(name) = UPPER(:dname))
;DELETE FROM employee
WHERE UPPER(name) = UPPER(:dname)
;UPDATE department
SET emp_count = emp_count - 1
WHERE dept_no = :dept; ) ;
Example : Valid ABORT Statements
The following ABORT statements are all syntactically correct.
ABORT
WHERE user= 'DBC';
ABORT
FROM table_1
WHERE table_1.x1 = 1;
ABORT
FROM table_11
WHERE table_1.x1 > 1;
ABORT
FROM table_1,table_2
WHERE table_1.x1 = table_2.x2;
Example : ABORT Statement With WHERE Clause
The following example uses an ABORT statement in the macro newemp. newemp inserts a row into the employee table for each new employee and then executes the SELECT statement to verify that the new information was entered correctly. The ABORT statement ensures that no new employee is inadvertently added to the executive office department, department 300.
CREATE MACRO newemp (
number INTEGER,
name VARCHAR(12),
dept INTEGER 100 TO 900,
position VARCHAR(12),
sex CHARACTER,
ed_lev BYTEINT ) AS (
ABORT 'Department number 300 not valid'
WHERE :dept = 300 ;
INSERT INTO employee (emp_no, name, dept_no, job_title, sex,
ed_lev)
VALUES (:number, :name, :dept, :position, :sex, :edlev) ;
SELECT *
FROM employee
WHERE emp_no = :number ; ) ;
Example : ABORT With a UDT In The WHERE Clause
The following examples show correct use of UDT expressions in the WHERE clause of an ABORT statement:
ABORT WHERE (tab1.euro_col < CAST(0.0 AS euro));
ABORT WHERE (tab1.cir_col.area() < 10.0);
Example : Using an SQL UDF in the WHERE Clause of an ABORT Statement
The following ABORT statement specifies an SQL UDF in its WHERE search condition.
ABORT FROM t1
WHERE a1 = test.value_expression(2, 3);