宏

1.简单参数宏

所谓参数宏，是指在宏中包含可以替代值得变量。下面是一个简单的参数宏定义：

CREATE MACRO dept_list(dept INTEGER)

AS

(SELECT last_name

FROM employee

  WHERE department_number=:dept);

运行dept_list的语句为：

EXEC dept_list(301);

其结果是返回部门编号为301的所有雇员的姓。

2.多参数宏

多参数宏包含多个参数，每个参数可以定义各自的类型和属性。

CREATE MACRO new_employee

                 (number INTEGER

                  ,MGR INTEGER

                  ,dept  INTEGER

                  ,job INTEGER

                  ,lastname CHAR(20)

                  ,firstname VARCHAR(30)

                  ,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

,:lastname

,:firstname

,:hired

,:birth

,:salary);

);