SQL——函数与过程

作者: 薛定谔与猫的故事 | 来源:发表于2018-04-06 10:29 被阅读0次

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

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

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

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,它继续从引发异常的语句的下一条语句开始执行。除了明确定义的条件,还有一些预定义的条件,比如:sqlexceptionsqlwarningnot 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

外部语言过程

相关文章

  • SQL——函数与过程

    函数与过程允许“业务逻辑”作为存储过程记录在数据库中,并在数据库中执行。例如,大学生通常有许多的规章制度,规定在一...

  • SQL函数与存储过程

    存储过程(Stored Procedure)是一种在数据库中将SQL语言的代码封装,以便外部程序调用的一种数据库对...

  • 浅谈存储过程

    【1】什么是过程?过程与函数的区别?什么是存储过程??? 存储过程:存储在数据库中的封装了若干条SQL语句的过程(...

  • sql 函数存储过程

    函数 1).字符串类 ---3 ?? SELECT gender,length(gender) from empl...

  • mysql性能优化

    SQL存储过程编写插入测试数据 生成0-100的随机数值 函数声明语法 函数调用语法 删除函数 SQL语句优化 1...

  • SQL存储过程和函数(1)

    定义: 存储过程和函数是在数据库中定义一些SQL语句的集合,然后直接调用这些存储过程和函数来执行已经定义好的SQL...

  • SQL之存储过程及函数

    存储过程和函数是在数据库中定义一些SQL语句的集合,然后直接调用这些存储过程和函数来执行已经定义好的SQL语句。存...

  • 存储过程与存储函数

    存储过程和函数是在数据库中定义一些SQL语句的集合,然后直接调用这些存储过程和函数来执行已经定义好的SQL语句。存...

  • mysql基础(三)

    存储过程和函数 存储过程和函数的引入 存储过程和函数是在数据库中定义一些 SQL 语句的集合,然后直接调用这些存储...

  • MySQL面试题 | 附答案解析(十二)

    (一)存储过程与函数 什么是存储过程?有哪些优缺点? 存储过程是一个预编译的SQL语句,优点是允许模块化的设计,就...

网友评论

    本文标题:SQL——函数与过程

    本文链接:https://www.haomeiwen.com/subject/odpjhftx.html