15.10 - SQL_statement - Teradata Database

Teradata Database SQL Data Definition Language Syntax and Examples

prodname
Teradata Database
vrm_release
15.10
created_date
December 2015
category
Programming Reference
featnum
B035-1144-151K

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.

SQL DCL and DDL statements for administration of row level security are not allowed in a macro.

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; );