Oracle数据创建虚拟列和复合触发器的方法

下面是详细讲解“Oracle数据创建虚拟列和复合触发器的方法”的完整攻略。

创建虚拟列

1. 确定需要创建虚拟列的表，并确认虚拟列的计算公式。
2. 使用 ALTER TABLE 语句添加虚拟列，语法如下：

sql
ALTER TABLE table_name ADD (column_name data_type [GENERATED ALWAYS] AS (expression) [VIRTUAL])

其中，column_name 为要创建的虚拟列名称，data_type 为数据类型，expression 为计算公式。

如果使用 GENERATED ALWAYS AS 或 VIRTUAL AS 创建虚拟列，则可以省略 AS 关键字。

1. 示例1：在 employees 表中添加两个虚拟列 full_name 和 age，分别计算员工的全名和年龄。

sql
ALTER TABLE employees ADD (full_name VARCHAR2(100) GENERATED ALWAYS AS (first_name || ' ' || last_name) VIRTUAL,
age NUMBER GENERATED ALWAYS AS (TRUNC(MONTHS_BETWEEN(SYSDATE,hire_date)/12)) VIRTUAL);

其中，full_name 的数据类型为 VARCHAR2(100)，通过将 first_name 和 last_name 拼接形成员工的全名。

age 的数据类型为 NUMBER，通过计算当前时间和入职时间之间的月份数再取整得到员工的年龄。

1. 示例2：在 order_items 表中添加一个虚拟列 unit_price，它表示每个商品的单价。该表中包含商品数量和订单总金额两个真实列。

sql
ALTER TABLE order_items ADD (unit_price NUMBER(10,2) GENERATED ALWAYS AS (order_total/quantity) VIRTUAL);

其中，unit_price 的数据类型为 NUMBER(10,2)，通过将 order_total 除以 quantity 计算出每个商品的单价。

创建复合触发器

1. 确定需要创建触发器的表，并确认触发器的类型（BEFORE 或 AFTER）、触发时机（INSERT、UPDATE 或 DELETE）以及触发事件（行级触发器或语句级触发器）。
2. 使用 CREATE OR REPLACE TRIGGER 语句创建触发器，语法如下：

sql
CREATE OR REPLACE TRIGGER trigger_name
[BEFORE/AFTER] [INSERT/UPDATE/DELETE] [OF column_name] [ON table_name]
[FOR EACH ROW/STATEMENT]
[WHEN condition]
[DECLARE]
[BEGIN]
-- 触发器的触发代码
[EXCEPTION]
-- 异常处理代码
[END];

其中，trigger_name 为触发器名称，column_name 为要监测的列名，table_name 为要监测的表名，condition 为触发条件。

触发器的触发部分需要根据具体需求编写，可以是一个 SQL 语句或者一段 PL/SQL 代码。

1. 示例1：在 orders 表上创建一个触发器 orders_trigger，用于在每次插入订单时，将订单号码插入到另一个表 order_logs 中。

sql
CREATE OR REPLACE TRIGGER orders_trigger
AFTER INSERT ON orders
FOR EACH ROW
DECLARE
BEGIN
INSERT INTO order_logs (order_id) VALUES (:new.order_id);
END;

其中，AFTER INSERT ON orders 表示监测 orders 表的插入事件，:new.order_id 表示新插入的订单编号。

1. 示例2：在 employees 表上创建一个复合触发器 employees_trigger，用于每次插入或更新员工记录时，更新该员工所属部门的统计信息，包括部门人数、平均工资和最低工资。

```sql
CREATE OR REPLACE TRIGGER employees_trigger
AFTER INSERT OR UPDATE ON employees
FOR EACH ROW
DECLARE
v_dept_id departments.department_id%TYPE;
BEGIN
-- 获取该员工所属的部门
SELECT department_id INTO v_dept_id FROM departments WHERE department_name = :new.department_name;

   IF INSERTING THEN
       -- 更新部门人数
       UPDATE department_stats SET employee_count = employee_count + 1 WHERE department_id = v_dept_id;
       -- 更新平均工资和最低工资
       UPDATE department_stats SET avg_salary = (avg_salary * (employee_count - 1) + :new.salary) / employee_count,
                                      min_salary = LEAST(min_salary, :new.salary) WHERE department_id = v_dept_id;
   ELSIF UPDATING('salary') THEN
       -- 获取原先工资和当前工资
       DECLARE
           v_old_salary NUMBER := :old.salary;
           v_new_salary NUMBER := :new.salary;
       BEGIN
           -- 如果原先工资比当前工资高，退出触发器
           IF v_old_salary >= v_new_salary THEN
               RETURN;
           END IF;

           -- 更新平均工资和最低工资
           UPDATE department_stats SET avg_salary = (avg_salary * employee_count - v_old_salary + v_new_salary) / employee_count,
                                          min_salary = LEAST(min_salary, v_new_salary) WHERE department_id = v_dept_id;
       END;
   END IF;

END;
```

对于插入事件，该触发器会先查询该员工所属部门的ID，然后更新部门人数、平均工资和最低工资；对于更新事件，该触发器会先比较原先工资和当前工资，如果当前工资低于或等于原先工资，则不进行更新操作，否则更新平均工资和最低工资。