MySQL存储过程和存储函数

MySQL中提供存储过程（procedure）与存储函数（function）机制，我们先将其统称为存储程序，一般的SQL语句需要先编译然后执行，存储程序是一组为了完成特定功能的SQL语句集，经编译后存储在数据库中，当用户通过指定存储程序的名字并给定参数（如果该存储程序带有参数）来调用才会执行。

存储程序优缺点

优点

通常存储过程有助于提高应用程序的性能。当创建，存储过程被编译之后，就存储在数据库中。但是，MySQL实现的存储过程略有不同。 MySQL存储过程按需编译。在编译存储过程之后，MySQL将其放入缓存中。 MySQL为每个连接维护自己的存储过程高速缓存。如果应用程序在单个连接中多次使用存储过程，则使用编译版本，否则存储过程的工作方式类似于查询。

1）性能：存储过程有助于减少应用程序和数据库服务器之间的流量，因为应用程序不必发送多个冗长的SQL语句，而只能发送存储过程的名称和参数。

2）复用：存储的程序对任何应用程序都是可重用的和透明的。存储过程将数据库接口暴露给所有应用程序，以便开发人员不必开发存储过程中已支持的功能。

3）安全：存储的程序是安全的。数据库管理员可以向访问数据库中存储过程的应用程序授予适当的权限，而不向基础数据库表提供任何权限。

缺点

1）如果使用大量存储过程，那么使用这些存储过程的每个连接的内存使用量将会大大增加。

此外，如果在存储过程中过度使用大量逻辑操作，则CPU使用率也会增加，因为数据库服务器的设计不偏于逻辑运算。

2）很难调试存储过程。只有少数数据库管理系统允许调试存储过程。不幸的是，MySQL不提供调试存储过程的功能。

数据库存储过程

一、存储过程的概念

存储过程是定义在服务器上的一段子程序代码，存储过程时数据库对象之一。

存储过程在服务器端运行，需要时调用，执行速度快，方便使用

确保数据库的安全，存储过程可以完成所有的数据库操作

降低网络负载，客户端不必提交sql语句

可以接受用户参数，也可以返回参数.

二、存储过程类型

系统存储过程【名字以sp为前缀，存储在master库中】

本地存储过程【存储在用户定义的数据库中】

扩展存储过程【名字都已xp为前缀，储存在master库中】

临时存储过程【名字以#开头的】

三、创建并调用存储过程

创建一个存储过程的语句：

delimiter $$ //这是定义一个结束符$$
create procedure [存储过程名称]([参数])
begin
......
end$$
delimiter ; //重新定义结束符为 ;

创建一个统计课程数量的存储过程：

create procedure count_course()
begin
select count(*) from course;
end$$

delimiter 重新定义结束符号

调用count_course存储过程

call count_course$$

四、存储过程的变量

1、变量定义

使用declare声明变量

declare number1 int default 20;

一句declare只声明一个变量

作用域在begin…end范围中

变量具有与sql语句相同的数据类型和长度，还可以指定默认值与字符集和排序规则

变量使用set赋值，也可以使用select into赋值

创建test（）存储过程函数显示男和女的人数：

create procedure test()
begin
declare boys int(10);
declare girls int(10);
select count(*) into boys from student where Ssex='男';
select count(*) into girls from student where Ssex='女';
select boys,girls;
end$$

注意在无参数的情况下返回变量值，可以用select语句

2、变量的作用域

一个函数可以有多个begin…end块，一个块里还可以嵌套多个begin…end块
在函数父作用块中定义的变量对所有子块可用
在单个begin…end块中，变量是局部变量，不能跨兄弟块使用
函数传入参数属于全局变量，可以在所有块中使用

创建一个显示成年人和未成年人数量的表以及最大年龄和最小年龄的存储过程函数:

delimiter $$
create procedure age_count()
begin
  begin
    declare adult int;
    declare minor int;
    select count(*) into adult from student where Sage>=18;
    select count(*) into minor from student where Sage<18;
    select adult,minor;
  end;
  begin
    declare age_max int;
    declare age_min int;
    select max(Sage) into age_max from student;
    select min(Sage) into age_min from student;
    select age_max,age_min;
  end;
end$$
delimiter ;

在每个嵌套块的结尾end要加上 ; sql结尾符

把变量提到父begin块后，变量可以在两个块之间交换使用

drop procedure age_count;
delimiter $$
create procedure age_count()
begin
  declare adult int;
  declare minor int;
  declare age_max int;
  declare age_min int;
  begin
    select count(*) into adult from student where Sage>=18;
    select count(*) into minor from student where Sage<18;
    select adult,age_min;
  end;
  begin
    select max(Sage) into age_max from student;
    select min(Sage) into age_min from student;
    select age_max,minor;
  end;
end$$
delimiter ;

五、存储过程参数

前面提到的函数都是没带参数的，只使用select返回结果集

函数可以带的参数分为：传入参数in 传出参数out 传入传出参数inout；

函数不指定参数类型的情况下，传进来的参数默认是in类型

drop procedure findname;
delimiter $$
create procedure findname(sno int)
begin
  declare name varchar(10);
  select Sname into name from student where Sno=sno limit 1;
  select name;
end$$
delimiter ;

使用 select into 语句赋值的时候要确保该语句只返回一条结果，或者加上 limit 1 来限制返回结果的行数
SQL变量名不能和列名一样

使用 out 类型的参数输出，结果应该与上题一致

drop procedure findname;
delimiter $$
create procedure findname(in sno int,out sname varchar(10))
begin
  select Sname into sname from student where Sno=sno limit 1;
end$$
delimiter ;

call findname(2,@name);
select * @name;

六、定义条件和定义处理的程序

定义条件：

事先定义好程序执行过程中可能遇到的问题

处理程序：

对已经定义好的问题作出相应处理，并保证存储函数在遇到警告或错误时能继续执行，避免程序异常停止工作

定义条件

declare [condition_name] condition for [错误码/错误值];
declare command_not_allowed condition for sqlstate '42000';//错误值
declare command_not_allowed condition for 42000;//错误码

处理程序

declare [handler_type] handler for [condition_name]
......

handler_type 错误处理方式

mysql提供了三个值

continue //不处理错误，存储函数继续往下执行
exit //遇到错误立即退出
undo //遇到错误撤销之前操作

condition_name 错误类型

condition_name 可以自定义错误类型，mysql也有自带的错误类型：

sqlstate_value：包含5个字符的字符串错误值；
condition_name：表示declare condition定义的错误条件名称；
SQLWARNING：匹配所有以01开头的sqlstate错误代码；
NOT FOUND：匹配所有以02开头的sqlstate错误代码；
SQLEXCEPTION：匹配所有未被SQLWARNING或NOT FOUND捕获的sqlstate错误代码；

七、流程控制

SQL 算术运算符

假设变量 a 的值是：10，变量 b 的值是：20，以下为各运算符执行结果：

SQL 比较运算符

假设变量 a 的值是：10，变量 b 的值是：20，以下为各运算符执行结果：

SQL 逻辑运算符：

这是在 SQL 所有的逻辑运算符的列表。

优先顺序

在较低级别的运算符之前先对较高级别的运算符进行求值。在下表中，1 代表最高级别，8 代表最低级别。

条件语句

（1）if语句

基本结构：

单条件语句

begin
if(...)
then
  ......
else
  ......
  end if;
end$$

多条件语句

begin
if(...)
then
  ......
elseif(...)
then
  ......
else 
  ......
end if;
end$$

if 语句需要有 end if 来结束 if 语句

判断Cno是否有空值

delimiter $$
CREATE PROCEDURE ifnull()
begin
  declare flag int;
  select count(*) into flag from student where Cno is null;
  if flag is null
    then select '没有空值' as '是否有空值';
    else select '仍有空值' as '是否有空值';
  end if;
end
delimiter ;

(2）case语句

case语句可以计算多个条件式，并将其中一个符合条件的结果报答是返回

case [测试表达式]
when [测试值1] then [结果表达式1]
when [测试值2] then [结果表达式2]
when [测试值3] then [结果表达式3]
......
else [结果表达式0]
end

DROP PROCEDURE IF EXISTS testCase;
DELIMITER //
CREATE PROCEDURE testCase(OUT result VARCHAR(255))
BEGIN
     DECLARE val VARCHAR(255);
     SET val = 'a';
     CASE val IS NULL
         WHEN 1 THEN SET result = 'val is true';
         WHEN 0 THEN SET result = 'val is false';
         ELSE SELECT 'else';
     END CASE;
END //
DELIMITER ;
set @result='';
CALL testCase(@result);

根据输入的课程名，添加一行课程类别

create procedure course_cate(cid varchar(10))
begin
update course set cate=
case(select cname from course where Cid=cid)
when '语文' then '文科'
when '数学' then '理科'
else 'x'
end case
end

数据库存储过程和函数（一）