SQL——函数与过程

函数与过程允许“业务逻辑”作为存储过程记录在数据库中，并在数据库中执行。例如，大学生通常有许多的规章制度，规定在一个学期里每个学生能选多少课，在一年里一个全职的教师至少要上多少节课，一个学生最多可以在多少个专业中注册。

这样的业务逻辑能够被写成程序设计语言过程并完全存储在数据库之外，但把它们定义成数据库中的存储过程有几个优点：

• 允许多个应用访问这些过程
• 允许当业务规则发生改变时进行单个点的改变，而不变改变应用系统的其他部分
• 应用代码可以调用存储过程，而不是直接更新数据库关系。

SQL允许定义函数、过程和方法。定义可通过SQL的有关过程的组件，也可以通过外部的程序设计语言，如Java，C或C++。SQL标准所定义的语法，与大多数数据库实现的自身的非标准版本的语法不同。

声明和调用SQL函数与过程

声明方式：

#函数定义：
create function function_name(<parameters>) returns type
return value_type;

#用过Pascal语言的人会发现，SQL语言的数据定义、函数和过程定义方式与Pascal类型，同样的，这里的begin……end类似{} 
#过程定义 
create procedure procedure_name(<parameters>)
  begin
    ...
  end

例如：

#给定一个系，找出系里教师的数目
create function instr_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

#我们也可以将上面的例如改成SQL过程
#这里的关键字in和out分别表示待赋值的参数和作为返回结果而在过程中设置值得参数
create procedure instr_cout_pro(in dept_name varchar(20), out d_count integer)
  begin
    select count(*) into d_count
    from instructor
    where instructor.dept_name = dept_name
  end

#给定一个系，找出该系的所有教师
#SQL允许返回关系作为结果的函数
create function instructor_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 = dept_name
);

调用

函数和过程可以在过程或者嵌入式SQL语言中使用call语句调用

#SQL调用过程或函数
declare d_count integer;
call instr_count_pro('biology',d_count);
d_count = instr_count(biology);


#在动态SQL调用，在动态SQL中调用，需要有可调用语句
#例如，Java的jdbc提供CallableStatement 借口来允许调用SQL的存储过程或函数
#对于里面？的设置，有用过java对数据库操作的人比较清楚。
int d_count;
String dept_name="biology";
CallableStatement cst1 = conn.prepareCall("{?=instr_count(?)}");
CallableStatement cst2 = conn.prepareCall("{instr_count(?,?)}");
cst1.setInt(1,d_count);
cst1.setString(2,dept_name);
cst2.setString(1,dept_name);
cst2.setInt(2,d_count);

函数与过程的语言构造

SQL所支持的构造赋予了它与通用编程语言相当的几乎所有的功能。SQL标准中处理这些构造的部分称为持久存储模块（Persistent Storage Module， PSM）

变量通过declare语句进行声明，可以是任意的合法SQL类型。使用set语句进行赋值。
一个复合语句有begin……end的形式，在begin和end之间会包含复杂的SQL语句。一个形如begin atomic……end的语句可以确保其中包含的所有语句作为单一的事务来执行。

• while语句

while 布尔表达式 do
  语句序列；
end while

• repeat语句

#相当于通用编程中的do……while语句
repeat
  语句序列；
util 布尔表达式
end repeat

• for循环（类似于for……in语句）

#可用语句leave退出循环，iterate跳过剩余语句从循环的开始进入下一个元组
declare n integer default 0；
for r as
  select budget from department
  where dept_name = ‘biology’
do
  set n = n+ r.budget;
end for

• if-then-else

if 布尔表达式
  then 语句或者复合语句
else if 布尔表达式
  then 语句或者复合语句
else 语句或者复合语句
end if

• case语句
  在前面《SQL数据库的修改》有提到过……

SQL程序语言还支持发信号通知异常条件，以及声明句柄（handler）来处理异常：

declare out_of_classroom_seats condition
declare exit handler for out of_classroom_seats
begin
  语句序列；
end

在begin……end之间的语句可以执行signal out_of_classrome_seats来引发异常。这个句柄说明如果条件发生，将会采取动作终止begin end中的语句。另一个可选的动作是continue，它继续从引发异常的语句的下一条语句开始执行。除了明确定义的条件，还有一些预定义的条件，比如：sqlexception，sqlwarning和not found。

示例：
定义一个函数registerStudent，确认选课的学生没有超过该课所在教室的容量，然后完成学生对该课的注册。函数返回一个错误代码，这个值大于或等于0表示成功，返回负值表示出错，并用out参数的形式返回消息来说明失败的原因。

create function registerStudent(
  in s_id varchar(5),
  in s_course_id varchar(8),
  in s_sec_id varchar(8),
  in s_semester varchar(6),
  in s_year numeric(4,0),
  out errorMsg varchar(100)
)
  returns integer  
  begin
    #找出当前容量
    declare currEnrol int;
    select count(*) into currEnrol
    from takes
    where (course_id,sec_id,semester,year)=(s_course_id,s_sec_id,s_semester,s_year);
    #找出限制的最大容量
    declare limit int;
    select capacity into limit
    from classroom natrual join section
    where  (course_id,sec_id,semester,year)=(s_course_id,s_sec_id,s_semester,s_year);
    if(currEnrol<limit)
      begin
        insert into takes
        values(s_id,s_course_id,s_sec_id,s_semester,s_year,null);
        return 0;
     end
#string || str表示两个字符串的拼接
    set errorMsg = 'enrollment limit reached for course'||s_course_id||'section'||secid;
    return -1;
  end

外部语言过程