Terdata 基础 第三课（参数宏）

1.宏不是ANSI标准支持的，但大部分RDBMS都支持宏。在Teradata中，在ANSI和BTET缺省模式下都可以创建和执行宏，只不过在ANSI模式下会给出警告信息

  1.1.参数宏：宏中可以包含可替代值的变量

  CREATE MACRO dept_list(dept INTEGER)
  AS
   ( SELECT last_name
   FROM employee
   WHERE department_number = :detpt );

  1.2.多参数宏

 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);
. 141 .
SELECT department_number (TITLE 'number')
,department_name (TITLE 'name')
,budget_amouunt (TITLE 'budget')
,manager_employee_number (TITLE 'manager')
FROM department
WHERE department_number = :dept;
);

    1.3.宏实现参照完整性

 CREATE MACRO new_employee
( number INTEGER
,MGR INTEGER
,dept INTEGER
,job INTEGER
,lastname CHAR (20)
,firstname VARCHAR (30)
. 143 .
,hired DATE
,birth DATE
,salary DECIMAL (10, 2))
AS
(ROLLBACK WORK `Invalid Hire'
WHERE (:hired - :birth) / 365 < 21;
ROLLBACK WORK `Invalid Department'
WHERE :dept NOT IN
(SELECT department_number
FROM department
WHERE department_number = :dept);
ROLLBACK WORK `Invalid Job Code'
WHERE :job NOT IN
(SELECT job_code
FROM job
WHERE job_code = :job);
INSERT INTO employee
( employee_number
,manager_employee_number
,department_number
,job_code
,last_name
,first_name
,hire_date
,birthdate
,salary_amount )
VALUES
( :number
, :mgr
, :dept
, :job
. 144 .
, :lastname
, :firstname
, :hired
, :birth
, :salary );
);
 
利用这个宏来录入新雇员时，必须满足如下条件：
! 受雇时应年满21岁
! 应有一个合法的部门编号
! 应有一个合法的工作代码