1.SQL存储过程和并发控制
【说明】
某企业内部信息系统部分简化后的关系模式如下：员工表：EMPLOYEES(Eid，Ename，Address，Phone，Jid):属性含义分别为：员工编码、员工姓名、家庭住址、联系电话、岗位级别编码。
岗位级别表：JOB_LEVELS(Jid，Jname，Jbase_salary):属性含义分别为：岗位级别编码、岗位名称、岗位基本工资。
员工工资表：SALARY(Eid，attendance_wage，merit_pay，overtime_wage，salary，tax，year，month):属性含义分别为：员工编码、考勤工资、绩效工资、加班工资、最终工资、税、年份、月份。
该企业在每月25日计算员工的工资。首先是根据考勤系统以及绩效系统中的数据，计算出员工的考勤、绩效和加班工资，存入到员工工资表；其次结合员工的岗位基本工资，计算出最终工资，完成对员工工资表记录的更新。最后依据员工工资表完成工资的发放。
【问题1】(6分)
下面是月底25日计算某员工最终工资的存储过程程序，请补全空缺处的代码。
CREATE PROCEDURE SalaryCalculation((a) empId char(8),IN iYear number(4),IN iMonth number(2)) #括号里面的数字表示参数有几位
DECLARE
attendance number(14,2); #14表示整数部分 + 小数部分 最多14位
merit number(14,2); #2表示小数部分最多2位
overtime number(14,2);
base number(14,2);
all_salary number(14,2);
BEGIN
SELECT attendance_wage,merit_pay,overtime_wage INTO (b)
FROM SALARY
WHERE Eid=empld FOR UPDATE;
SELECT Jbase_salary INTO :base FROM EMPLOYEES T1,(c)
WHERE T1.Jid=T2.Jid AND T1.Eid=empld;
all salary :=attendance+merit+overtime+base; #:= 是赋值运算符，和数据库中=号比较的用法区分开
UPDATE SALARY SET salary =:all_salary
WHERE (d) AND year=iYear AND month=iMonth;
（e）;
EXCEPTION WHEN OTHERS THEN
(f);
END;
【问题2】(5分)
为了防止对员工工资表的非法修改（包括内部犯罪），系统特意规定了员工工资表修改的业务规则：对员工工资表的修改只能在每月25日的上班时间进行。
下面是员工工资表修改业务规则对应的程序，请补全空缺处的代码。
CREATE TRIGGER CheckBusinessRule
(g) INSERT OR DELETE OR (h)on SALARY
FOR EACH (i)
BEGIN
IF (TO_CHAR(sysdate,'DD')<>(j))
OR(to_number(TO_CHAR(sysdate,'HH24))
(k) BETWEEN 8 AND 18)THEN
Raise_Error;//抛出异常
END IF;
END;
【问题3】(4分)
人事部门具有每月对员工进行额外奖罚的权限，该奖罚也反应到员工的最终工资上。假设当某月计算一位员工的最终工资时，同一时间人事部门对该员工执行了奖励2000元的事务操作，对应事务的部分调度序列如表4-1所示。
（1）请说明该事务调度存在哪种并发问题？
（2）采用2PL是否可以解决该并发问题？是否会产生死锁？
存储过程基本语法结构
CREATE [OR REPLACE] PROCEDURE 过程名(
参数模式 参数名 数据类型,
...
)
DECLARE / 或者AS / 或者IS #不同数据库声明部分关键词不一样
-- 声明部分：变量、常量、游标等
BEGIN
-- 执行部分：SQL语句、逻辑处理
[COMMIT;] -- 提交事务
EXCEPTION
-- 异常处理部分
WHEN 异常类型 THEN
处理语句/或[ROLLBACK;] -- -- 出现异常 → 全部撤销
END;CREATE PROCEDURE SalaryCalculation((a) empId char(8),IN iYear number(4),IN iMonth number(2))
空缺 (a)：IN
创建存储过程时，参数可以是三种模式中的任意一种，具体取决于需求：
IN 只读输入 传入值
OUT 只写输出 把计算结果返回给调用者
IN OUT 可读可写 传入初始值，过程处理后返回新值
判断为什么是IN，1️⃣从使用用途角度考虑
题目已经说明了，这个过程的目的是更新数据库中的数据，而不是把结果返回给调用者
V这个存储过程是"月底计算某员工最终工资"，调用时应该是：
SalaryCalculation('EMP001', 2024, 12);三个值都是调用者传入的输入，不需要过程返回任何结果给调用者。所以三个参数都应该是 IN。
2️⃣从题目内容判断：
WHERE Eid = empId FOR UPDATE; -- 第1处
WHERE T1.Jid = T2.Jid AND T1.Eid = empId; -- 第2处
WHERE Eid = empId AND year=iYear AND month=iMonth; -- 第3处empId 在这三处都是出现在等号右边，作为查询条件使用。它只是被读取，从未被赋新值。和 iYear、iMonth 完全一样，这不应该是要被返回的。
SELECT INTO
空缺(b)：:attendance，:merit，:overtime
SELECT attendance_wage,merit_pay,overtime_wage INTO (b)
FROM SALARY
这里是将查询到的结果赋予给变量，然后这些变量要参与后续的其他计算
SELECT 列1, 列2, 列3 INTO 变量1, 变量2, 变量3
FROM 表名
WHERE 条件;注意题目已经给出了英文的对应说明
员工工资表：SALARY(Eid，attendance_wage，merit_pay，overtime_wage，salary，tax，year，month):属性含义分别为：员工编码、考勤工资、绩效工资、加班工资、最终工资、税、年份、月份。
从员工工资表选取这三项付给题目中意思对应的三个变量
冒号：表示这三个变量，对于这个SQL语句而言是外部的变量。SQL语句是BEGIN才开始作用的，它并不知道我们在declare声明的变量是什么东西。在一些数据库中是不需要这么做的，但是考试环境还是要注明。
多表关联查询
空缺（c）JOB_LEVELS T2
SELECT Jbase_salary INTO :base
FROM EMPLOYEES T1, (c)
WHERE T1.Jid = T2.Jid AND T1.Eid = empld;
Jbase_salary（岗位基本工资）在 JOB_LEVELS 表中，这个表只有岗位的级别、岗位的名称、岗位的工资。
要找到员工的工资，必须先通过这个员工找到它的岗位才会获取岗位的通知，观察两张表发现jid是共有的属性。
然后根据题目可以得知T1是 EMPLOYEES这个表，那么连接条件中的T2肯定是JOB_LEVELS了。题目中的EMPLOYEES T1实际上是别名AS省略了，同理得答案，JOB_LEVELS T2
all_salary := attendance + merit + overtime + base;
这个语句是独立的一句，总工资等于考勤+绩效+加班+底薪，
每次调用这个过程的时候，其中的变量在前面的语句中被赋值，然后这句话单独计算出总工资，后面再拿总工资取写入修改表。
空缺（d）eid= empld
UPDATE SALARY SET salary =:all_salary
WHERE (d) AND year=iYear AND month=iMonth;
（e）;
要把where判断条件相符的行，其中的薪水更新为计算后的结果。
这里看到where的都是传入的条件来作判断，所以是SALARY中的eid= empld。
FOR UPDATE和COMMIT/ROLLBACK
（e）COMMIT
（f）ROLLBACK
（e）;
EXCEPTION WHEN OTHERS THEN
(f);
END;
在前面的行中，UPDATE 已经执行了，但修改还在事务中，没有永久保存。且FOR UPDATE 会加行锁。
锁只有 COMMIT 或 ROLLBACK 才能释放
所以 (e) 和 (f) 必然是这两个事务控制命令之一。
SELECT attendance_wage,merit_pay,overtime_wage INTO (b)
FROM SALARY
WHERE Eid=empld FOR UPDATE;
FOR UPDATE 是 SELECT 语句的最后一个子句，必须放在 WHERE 条件之后。这是语法规定，不是随意放的。
字面意思就是我要查这些数据，是为了更新它们，数据库需要上锁保护，以免被其它人修改了。OTHERS 是一个通配符，代表"所有没有在前面被显式捕获的异常"。
EXCEPTION WHEN OTHERS THEN
ROLLBACK;
触发器
CREATE [OR REPLACE] TRIGGER 触发器名
触发时机 触发操作 ON 表名
FOR EACH ROW | FOR EACH STATEMENT
BEGIN
-- 触发执行的代码
END;（g）before
（h）update
CREATE TRIGGER CheckBusinessRule
(g) INSERT OR DELETE OR (h)on SALARY
触发时机（本题考点）
BEFORE 在操作执行之前触发（可以阻止操作）
AFTER 在操作执行之后触发（用于日志、审计）
INSTEAD OF 替代操作执行（常用于视图）
触发操作
可以是 INSERT、DELETE、UPDATE 中的一种或多种组合
题目明确说了是为了防止非法修改，"只能在每月25日上班时间修改" = 不满足条件时，禁止修改，必须在修改发生之前检查条件，不满足就拦截。因此用before
BEFORE INSERT OR DELETE OR (h) ON SALARY
这里就是列出要在 什么 操作之前检查？列出的都是对表修改的操作，自然就是insert、delete、update
（i）row
FOR EACH (i)
FOR EACH ROW 每影响一行触发一次 UPDATE 10行 → 触发10次
FOR EACH STATEMENT 每条SQL语句触发一次 UPDATE 10行 → 触发1次
这是对工资表增删改的业务规则检查，必须对每一行操作都进行拦截判断，所以用 ROW
（j）‘25’
（k）not
BEGIN
IF (TO_CHAR(sysdate,'DD')<>(j))
OR(to_number(TO_CHAR(sysdate,'HH24))
(k) BETWEEN 8 AND 18)THEN
Raise_Error;//抛出异常
END IF;
END;
题目中的业务规则
"对员工工资表的修改只能在每月25日的上班时间进行。"
仔细看IF语句，如果其中的条件成立，那么就是异常终止
也就是说条件是今天不是25日，或者今天不是上班时间，根据题目信息上班时间是8点-18点。
TO_CHAR 格式化日期
TO_CHAR(sysdate, 'DD') -- 返回日期的"日"部分，如 '25'（字符串）
TO_CHAR(sysdate, 'HH24') -- 返回24小时制的小时，如 '14'（字符串）sysdate 是数据库的系统日期时间函数，返回当前日期和时间。
TO_NUMBER 字符内容转成数字
TO_NUMBER('14') -- 括号里面的字符串14返回成数字 14结果就是：
IF (TO_CHAR(sysdate,'DD')<> ‘25’) 返回的日期字符串不等于‘25’
OR(to_number(TO_CHAR(sysdate,'HH24))返回的时间不在8和18之间
not BETWEEN 8 AND 18) THEN 报错终止并行调度和两段锁分析
假设当某月计算一位员工的最终工资时，同一时间人事部门对该员工执行了奖励2000元的事务操作，对应事务的部分调度序列如表4-1所示。
（1）请说明该事务调度存在哪种并发问题？
（2）采用2PL是否可以解决该并发问题？是否会产生死锁？
丢失更新
两个事务同时读写同一数据，后提交的事务覆盖了先提交事务的修改，导致先提交事务的更新丢失。
计算最终工资的这个事务是用奖励2000元之前的数据，它在最后提交这个员工的薪水的时候，会把人事的奖罚的工资给覆盖掉。
（1）该事务存在丢失更新的问题。因为人事部门奖罚事务更新的最终工资被计算最终工资事务的写入而覆盖了。
在 2PL 下：
工资事务在 T1 读取 SALARY 表相关行时，会对这些行加共享锁（S锁）
奖罚事务在 T2 要更新同一行时，需要排他锁（X锁）
但共享锁未释放，排他锁无法加上 → 奖罚事务必须等待
直到工资事务完成计算、写入并提交释放锁后，奖罚事务才能执行
这样就保证了串行化，工资事务要么看到奖罚之后的数据，要么先执行完奖罚事务再看到，不会出现"读到一半旧数据"的情况。
所以：2PL 可以解决这个丢失更新问题。
（2）可以。可能会产生死锁。