函数和过程
声明和调用SQL函数和过程
--给定一个系的名字，返回该系的教师数目
CREATE FUNCTION dept_count (dept_name varchar(20))
RETURNS integer
BEGIN
DECLARE d_count integer;
select count (* ) INTO d_count
from instructor
where instructor.dept_name = dept_name
RETURN d_count;
END
------
--调用
select dept_name, budget
from department
where dept_count (dept_name ) > 12
--以表为值的函数可以被看作带参数的视图，它通过允许参数把视图的概念更加一般化
CREATE FUNCTION instructors_of (dept_name char(20))
RETURNS TABLE (
ID varchar(5),
name varchar(20),
dept_name varchar(20),
salary numeric(8,2))
RETURN TABLE
(select ID, name, dept_name, salary
from instructor
where instructor.dept_name = instructors_of.dept_name)
--??使用函数的参数时需要加上函数名作为前缀：instructor_of.dept_name
------
select *
from table (instructors_of ('Music'))
--IN和OUT分别表示待赋值的参数和为返回结果而在过程中设置值的参数
CREATE PROCEDURE dept_count_proc (IN dept_name varchar(20),OUT d_count integer)
BEGIN
select count(*) INTO d_count
from instructor
where instructor.dept_name = dept_count_proc.dept_name
END
------
--可以从一个SQL过程或者嵌入式SQL里使用call语句调用过程
DECLARE d_count integer;
CALL dept_count_proc('Physics', d_count);
支持过程和函数的语言构造
- 变量用过declare语句进行声明，可以是任意的合法SQL类型。使用set语句进行赋值
- 一个复合语句有begin…end的形式，begin和end之间会包含复杂的SQL语句
- 可以在复合语句中声明局部变量
- begin atomic…end的符合语句可以确保其包含的所有语句作为单一事务
DECLARE n integer DEFAULT 0;
WHILE n < 10 do
SET n = n + 1
END WHILE
REPEAT
SET n = n – 1
UNTIL n = 0
END REPEAT
------
--for循环，对查询到的所有结果重复执行
DECLARE n integer DEFAULT 0;
FOR r AS
select budget
from department
where building='Watson'
DO
SET n = n + r.budget
END FOR
--程序每次获得查询结果的一行并存入for循环变量（r）中
--leave语句可以跳出循环，iterate表示跳过剩余语句从循环的开始进入下一个元组（break和continue？）
-------
--if-then-else
IF 布尔表达式
THEN 复合语句
ELSEIF 布尔表达式
THEN 复合语句
ELSE 复合语句
END IF
-----
--SQL支持case语句
-----
--SQL支持发信号通知异常条件，声明句柄来处理异常
DECLARE out_of_classroom_seats CONDITION
DECLARE EXIT HANDLER FOR out_of_classroom_seats
BEGIN
…
END
触发器
- 之名什么条件下执行触发器。分为引起触发器被检测的事件和一个触发器执行必须满足的条件
- 指明触发器执行的动作
- 例如教材的例子，time_slot表记录了课程可能的每一个时段，section表记录了课程开设的时间和地点信息，其中开课时段time_slot_id和time_slot表中的的time_slot_id保持一致性，但是又无法用外键来约束。这时候可以用trigger的机制
CREATE TRIGGER timeslot_check1 AFTER INSERT ON section
REFERENCING NEW ROW AS nrow --建立一个临时变量存储新增的数据，是一个临时表
FOR EACH ROW --一个SQL插入语句可以像关系中插入多个元组。此可以现实地在每个被插入的行上进行迭代
WHEN (nrow.time_slot_id NOT IN ( --condition
SELECt time_slot_id
FROM time_slot))
BEGIN
ROLLBACK --action
END;
-----
CREATE TRIGGER timeslot_check2 AFTER DELETe ON timeslot
REFERENCING OLD ROW AS orow --建立临时变量存储删掉或修改行的旧数据
FOR EACH ROW
WHEN (
orow.time_slot_id NOT IN (
SELECT time_slot_id
FROM time_slot) AND
orow.time_slot_id IN (
select time_slot_id
FROM section))
BEGIN
rollback
END;
------
CREATE TRIGGER credits_earned AFTER UPDATe OF takes ON (grade)
REFERENCING NEW ROW AS nrow
REFERENCING OLD ROW AS orow
FOR EACH ROW
WHEN nrow.grade <> 'F' AND nrow.grade IS NOT NULL
AND (orow.grade = 'F' OR orow.grade IS NULL)
BEGIN ATOMIC
UPDATE student
SET tot_cred = tot_cred +
(SELECT credits
FROM course
WHERe course.course_id = nrow.course_id)
WHERe student.id = nrow.id
END;
------
--可以在event之前触发
CREATE TRIGGER setnull_trigger BEFORE UPDATE OF takes
REFERENCING NEW ROW AS nrow
FOR EACH ROW
WHEN (nrow.grade = '')
BEGIN ATOMIC
SET nrow.grade = null;
END;
-----
--可以设置为有效或者无效
ALTER TRIGGER 名字 DISABLE
或者
DISABLE TRIGGER 名字
何时不用trigger
- trigger时要特别小心，因为运行期间一个trigger错误会导致引发该trigger的action语句失败，而且一个trigger的动作可以引发另一个触发器，最坏情况下，会导致一个无限的触发链
- 如果有其他替代方法最好不用trigger，可以用存储过程来实现相应的功能
