《数据库系统概念》7-函数、存储过程、触发器

一、函数和存储过程
a)通过函数和存储过程可以将业务逻辑保存在数据库，在需要的时候调用。比如学生在一个学期可以修的最大课程数、导师的最小授课数等，这些判断具有比较复杂的逻辑，虽然在数据库外也可以实现这样的控制，但用函数或存储过程在数据库的入口来把关，可以与应用程序独立开来，便于维护。但感觉将业务逻辑独立写在存储过程也不一定就能便于维护。
b)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 instructor
where dept count(dept name) > 12;
c)函数还可以返回表，称为表函数(table functions)，这相当于带参数的视图
create function instructors of (dept_name varchar(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 = instructor of.dept_name);
类似的功能也可以使用存储过程：
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
in和out表示数据的输入输出。存储过程还可以重载。
d)存储过程和函数的区别：
函数只能通过return语句返回单个值或者表对象。而存储过程不允许执行return，但是通过out参数返回多个值；
函数是可以嵌入在sql中使用的,可以在select中调用，而存储过程不行；
函数限制比较多，比如不能用临时表，只能用表变量．还有一些函数都不可用等等．而存储过程的限制相对就比较少；
一般来说，存储过程实现的功能要复杂一点，而函数的实现的功能针对性比较强。

二、SQL的语法结构
a)SQL也像Java、C等语言一样支持if、for等语法结构，用declare声明变量、用set赋值，但一段SQL要写在begin…end之间，使用begin atomic…end的话，内部的语句构成一个事务。
b)while和repeat
while boolean expression do
sequence of statements;
end while

repeat
sequence of statements;
until boolean expression
end repeat

c)for
declare n integer default 0;
for r as
select budget from department where dept name = ‘Music‘
do
set n = n− r.budget
end for

d)if
if boolean expression
then statement or compound statement
elseif boolean expression
then statement or compound statement else statement or compound statement
end if

三、触发器Trigger
a)触发器包含两个要素：被触发的时机、被触发后执行的动作。
在数据库自带的一致性约束机制无法满足业务需求时，可以用触发器来限制；也可以实现监控、报警、自动化等需求。
b)触发器的创建
create trigger timeslot_check1 after insert on section
referencing new row as nrow
for each row
when (nrow.time slot_id not in (
select time slot_id
from time_slot))
begin
rollback
end;
为在section表insert时创建的触发器，referencing new row as nrow会将被插入的行保存到nrow临时变量，然后使用for each row来遍历。
除了插入操作，删除的触发器写法为：
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;
临时保存的是删除前的旧行，那么update时新行、旧行都需要：
create trigger credits_earned after update of takes on (grade)
referencing new row as nrow
referencing old row as orow
for each row
when …
begin atomic
…
end;
只有takes.grade被更新时才会被触发
c)除了用after定义动作发生后的触发器，还可以使用before在动作发生前触发；除了针对行的触发器(for each row)，还有针对表的触发器，对应的语法有;refenencing old/new table as、for each statement
d)触发器虽然可以用来解决很多问题，但如果有替代方法，便不推荐使用触发器，因为触发器的错误只能在运行时发现，而且多个触发器的关联会造成维护的困难。


学习资料：Database System Concepts, by Abraham Silberschatz, Henry F.Korth, S.Sudarshan​