存储过程与存储函数

【应知部分】：

1.存储过程与存储函数概述

存储过程与存储函数是MySQL自5.0版本之后开始支持的过程式数据库对象。它们作为数据库存储的重要功能，可以提高数据库的处理速度，同时也可以提高数据库编程的灵活性。

2. 存储过程的概念

概念：

存储过程是一组为了完成某特定功能的SQL语句集，其实质上就是一段存放在数据库中的代码，它可以由声明式语句（如CREATE、UPDATE和SELECT等语句）和过程式语句（如IF-THEN-ELSE控制结构语句）组成。这组语句经过编译后会存储在数据库中，用户只需通过指定存储过程的名字并给定参数（如果该存储过程带有参数），即可随时调用处理它，而不必重新编译，提高数据库操作语句的执行效率。

优点：

（1）增强SQL语言的功能与灵活性。

（2）良好的封装性。

（3）高性能。

（4）可减少网络流量。

（5）保证数据库的安全性和数据的完整性。

3. 存储过程体的作用

在存储过程体中可以使用各种SQL语句与过程式语句的组合，来封装数据库应用中复杂的业务逻辑和处理规则，以实现数据库应用的灵活编程。

构造存储过程体的语法元素：

（1）局部变量

（2）SET语句

（3）SELECT…INTO语句

（4）流程控制语句

（5）游标

4. 存储函数的概念

在MySQL中，存在一种与存储过程十分相似的过程式数据库对象——存储函数。它与存储过程一样，都是由SQL语句和过程式语句组成的代码片段，并且可以被应用程序和其他SQL语句调用。

存储函数与存储过程的区别：

（1）存储函数不能拥有输出参数。存储过程拥有输出参数。

（2）可以直接对存储函数调用，不需要使用CALL语句，存储过程调用需要。

（3）存储函数中必须包含RETURN语句，此语句不允许出现在存储过程中。

【应会部分】：

一、 创建存储过程

语法：CREATE PROCEDURE sp_name([proc_parameter[,…]])

[characteristic…]routine_body

其中，proc_parameter的格式为：

[IN|OUT|INOUT]param_name type

type的格式为：

Any valid MySQL data type

      characteristic的格式为：

      COMMENT ‘string’

      |LANGUAGE SQL

      |[NOT]DETERMINISTIC

      |{ CONTAINS SQL|NO SQL|READES SQL DATA|MODIFIES SQL DATA}

      |SQL SECURITY{DEFINER|INVOKER}

routine_body的格式为：

Valid SQL routine statement

主要语法说明：（详细说明见P141）

★sp_name：存储过程的名称，默认在当前数据库下创建。定义名字应该避免与MySQL内置函数相同。

★proc_parameter：存储过程参数列表。参数取名不要与数据表的列名相同。

★characteristic：存储过程的某些特征设定。

★routine_body：存储过程的主体部分，也称为存储过程体，其包含了在过程调用的时候必须执行的SQL语句。这个部分以BEGIN开始，以关键字END结束。

★DELIMITER命令的语法格式：

DELIMITER $$(略)

例题：

1）将MySQL结束符修改为两个感叹号“$$”

修改：mysql> delimiter $$

还原：mysql> delimiter ;

2）在数据库db_school中创建一个存储过程，用于实现给定表tb_student中一个学生的学号即可修改表tb_student中该学生的性别为一个指定的性别(使用该存储过程修改学生表中的学号、性别)。

mysql> use db_school;
mysql> delimiter $$
mysql> create procedure sp_update_sex(in sno int,in ssex nchar(2))

begin
update tb_student set sex=ssex where studentno=sno;
end $$

mysql> delimiter ;

注意：“ssex nchar(2)” 在Linux系统下的Mysql数据库管理系统中，由于字符集问题导致乱码的情况时有发生，大多是因为兼容性造成的，我们这里使用的是Unicode编码，能够表示全世界所有的字节。使用存储过程插入或更新的字符有可能出现乱码问题，为保证不出现乱码我们这里采用Unicode编码，即nchar或nvarchar。

查看存储过程状态你就会明白：

mysql>show procedure statusG

*************************** 1. row ***************************

                  Db: db_school

                Name: sp_update_sex

                Type: PROCEDURE

             Definer: root@localhost

            Modified: 2018-05-19 21:38:52

             Created: 2018-05-19 21:38:52

       Security_type: DEFINER

             Comment:

mysql>character_set_client: utf8

collation_connection: utf8_general_ci

Database Collation: latin1_swedish_ci

1 row in set (0.00 sec)

或查看指定的数据库“存储过程”。

show create procedure sp_update_sexG

*************************** 1. row ***************************

           Procedure: sp_update_sex

            sql_mode:

    Create Procedure: CREATE DEFINER=`root`@`localhost` PROCEDURE `sp_update_sex`(in sno int,in ssex nchar(1))

begin

update tb_student set sex=ssex where studentno=sno;

end

character_set_client: utf8

collation_connection: utf8_general_ci

Database Collation: latin1_swedish_ci

1 row in set (0.00 sec)

另外需要注意的是：如果你不是使用root用户创建的数据库存储过程，一定要去查看它是否有create routine权限。否则储存过程是无法创建的。

查看方法：以用户’root’@’localhost’为例

mysql> select Create_routine_priv from mysql.user

    -> where user='root' and host='localhost';

+-----------------------------------+

| Create_routine_priv     |

+-----------------------------------+

| Y                     |

+------------------------------------+

1 row in set (0.00 sec)

‘Y’说明拥有该权限。

【调用】储存过程怎样使用，我们这里使用以上创建的储存过程来验证。

mysql> select studentNo,sex

    -> from tb_student

    -> where studentNo='2013110101';

+------------------+---------+

| studentNo  | sex   |

+------------------+---------+

| 2013110101 | 男   |

+------------------+---------+

1 row in set (0.00 sec)这里我们使用存储过程把学号为“2013110101”的同学的性别更新为“女”。

mysql> call sp_update_sex('2013110101',n'女');

验证：

mysql> select studentNo,sex

      from tb_student

      where studentNo='2013110101';

+-------------------+----------+

| studentNo   | sex   |

+-------------------+----------+

| 2013110101  | 女   |

+-------------------+----------+

1 row in set (0.00 sec)

验证成功！

授予普通用户拥有“存储过程或函数”的相关权限：（复习授权）

User表中的列	权限名称	权限的范围
Alter_routine_priv	ALTER ROUTINE	修改存储过程和函数
Create_routine_priv	CREATE ROUTINE	创建存储过程和函数
Execute_priv	EXECUTE	执行存储过程和函数

mysql> use mysql

mysql> grant ALTER ROUTINE,CREATE ROUTINE,EXECUTE on `db_school`.*

to 'bob'@'localhost' identified by '123456';

或者：

mysql> use mysql


mysql> create user 'bob1'@'localhosat' identified by '123456';


mysql>update user

set Create_routine_priv='Y',

Alter_routine_priv='Y',

Execute_priv:='Y'

where user='bob1' and host='localhost';

其它权限如果需要也必须开启！

二、存储过程

1）局部变量

作用：在存储过程体中可以设置局部变量，用来存储存储过程体中的临时结果。在mysql5.5中可以使用DECLARE语句来声明局部变量并同时还可以对该局部变量赋予一个初始值。

其语法格式为：

DECLARE var_name[ ,…] type [DEFAULT value]

语法说明：

var_name：用于指定局部变量的名称

type：用于声明局部变量的数据类型

DEFAULT子句：用于为局部变量指定一个默认值。若没有指定默认为空。

注意：

（1）局部变量只能在存储过程体的BEGIN…END语句块中声明。

（2）局部变量必须在存储过程体的开头处声明。

（3）局部变量的作用范围仅限于声明它的BEGIN…END语句块，其它语句块中的语句不可以使用它。

（4）局部变量不同于用户变量，两者的区别是：局部变量声明时，在其前面没有使用“@”符号，并且它只能声明它的BEGIN…END语句块中的语句所使用；用户变量在声明时，会在前面使用“@”符号，同时声明的用户变量存在于整个会话之中。

2）SET语句

在MySQL5.5中可以使用SET语句为局部变量赋值，其语法格式是：

SET var_name=expr[,var_name=expr];

例题：使用存储过程计算两个值的和

mysql>use db_school   #存储过程是数据库对象，因此必须选择一个数据库

mysql> delimiter $$

mysql>create procedure sp_sum(in a int,in b int)

begin

declare c int;

set c=a+b;

select c;

end $$

mysql> delimiter ;

mysql> call sp_sum(5,6);

+---- -------+

| c      |

+-----------+

|   11  |

+-----------+

1 row in set (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

3）SELECT …INTO语句

在MySQL中，可以使用SELECT…INTO语句把选定列的值直接存储到局部变量中，其语法格式是：

SELECT col_name[,…]INTO var_name[,…]table_expr

语法说明如下：

★col_name:用于指定列名。

★var_name:用于指定要赋值的变量名。

★table_expr：表示使用SELECT语句中的FROM子句及后面的语法部分。

注意：存储过程体中的SELECT…INTO语句返回的结果集只能有一行数据。

例题：创建一个存储过程，用于得到某个学生的籍贯。

mysql> delimiter $$

mysql> create procedure sp_native1(in sno int)

      begin

      declare na nchar(20);

      select native into na from tb_student

      where studentno=sno;

      select na;

      end$$       

mysql> delimiter ;

调用：

mysql> call sp_native1 ('2013110201');

+----------------+

| na        |

+----------------+

| 内蒙古   |

+----------------+

1 row in set (0.00 sec)

 

Query OK, 0 rows affected (0.00 sec)

例题：创建一个存储过程，用于得到某个指定学生的籍贯。

mysql> delimiter $$

mysql> create procedure sp_native2(inout sno int)

      begin

      declare na nchar(20);

      select native into na from tb_student

      where studentno=sno;

      select na;

      end$$    

mysql> delimiter ;

注意：调用前这里必须先赋值。

mysql> set @sno=2013110101;

mysql> call sp_native2 (@sno);

+-------------+

| na     |

+-------------+

| 山西   |

+-------------+

1 row in set (0.01 sec)

 

Query OK, 0 rows affected (0.01 sec)

【理解】：参数类型in、out、inout的区别！具体内容请参阅P141。

4）流程控制语句

在MySQL 5.5中，可以在存储过程体中使用以下两类用于控制语句流程的过程式SQL语句。

（1）条件判断语句

常用的条件判断语句有IF-THEN-ELSE语句和CASE语句。其中，IF-THEN-ELSE语句可以根据不同的条件执行不同的操作，其语法格式为：

     IF search_condition THEN statement_list

         [ELESEIF search_condition THEN statement_list]…

         [ELSE statement_list]

     END IF

语法及使用说明如下：

★search_condition：用于指定判断条件。

★statement_list：用于表示包含了一条或多条的SQL语句。

★只有当判断search_condition为真时，才会执行相应的SQL语句。

★IF-THEN-ELSE语句不同于系统函数IF（）。

     CASE语句在存储过程中的使用具有两种语法格式，分别是：

     CASE case_value

          WHEN when_value THEN statement_list

          [WHEN when_value THEN statement_list]…

          [ELSE statement_list]

     END CASE

或

     CASE

         WHEN search_condition THEN statement_list

         [WHEN search_condition THEN statement_list]

         [ELSE statement_list]

     END CASE

语法说明如下：

★第一种语法格式中的case_value用于指定要判断的值或表达式，随后紧跟的是一系列WHEN-THEN语句块。其中，第一个WHEN-THEN语句块中的参数when_value用于指定要与case_value进行比较的值。倘若比较的值为真，则执行对应的statement_list中的SQL语句。如若每一个WHEN-THEN语句块中的参数when_value都不能与case_value相匹配，则会执行ELSE子句中指定的语句。该CASE语句最终会以关键字END CASE作为结束。

★第二种语法格式中的关键字CASE后面没有指定参数，而是在WHEN-THEN语句块中使用search_condition指定一个比较表达式。若表达式为真，则会执行对应的关键字THEN后面的语句。与第一种语法格式相比，这种语法格式能够实现更为复杂的条件判断，而且使用起来会更方便。

例题：新建存储过程用于实现插入不同的数据（1）。

use db_school

Create table table1(variable1 char(10));

 

DELIMITER $$

CREATE PROCEDURE proc1

(IN parameter1 INTEGER)

BEGIN

DECLARE variable1 CHAR(10);

IF parameter1 = 17 THEN

SET variable1 = 'birds';

ELSE

SET variable1 = 'beasts';

END IF;

INSERT INTO table1 VALUES (variable1);

END $$

DELIMITER ;

验证：

set @parameter1=18;

call proc1(@parameter1);

select * from table1;

例题：新建存储过程用于实现插入不同的数据（2）

Create table t(s1 int);

 

DELIMITER $$

CREATE PROCEDURE proc2(IN parameter int) 

begin

declare var int; 

set var=parameter+1; 

if var=0 then

insert into t values(17); 

end if; 

if parameter=0 then

update t set s1=s1+1; 

else

update t set s1=s1+2; 

end if; 

end $$ 

DELIMITER ;

验证：

Set @parameter=-1;

Call proc2(@parameter);

Select * from t;

Set @parameter=0;

Call proc2(@parameter);

Select * from t;

例题：新建存储过程用于实现插入不同的数据（3）CASE语句

Create table t1(s1 int);

 

DELIMITER $$ 

CREATE PROCEDURE proc3(in parameter int)

begin

declare var int; 

set var=parameter+1; 

case var 

when 0 then insert into t1 values(17); 

when 1 then insert into t1 values(18); 

else insert into t1 values(19); 

end case; 

end $$

DELIMITER ;

验证

set @parameter=0;

call proc3(@parameter);

select * from t1;

或者

DELIMITER  $$ 

CREATE PROCEDURE proc4(in parameter int) 

begin

declare var int; 

set var=parameter+1; 

case 

when var=0 then

insert into t1 values(30);

when var>0 then

insert into t1 values(40);

when var<0 then

insert into t1 values(50);

else

insert into t1 values(60);

end case;

end $$

DELIMITER ;

验证：

set @parameter=-1;

call proc3(@parameter);

select * from t1;

（2）循环语句

常用的循环语句有WHILE语句、REPEAT语句和LOOP语句。

WHILE语句语法格式如下：

while条件 do

--循环体

End while

例题：新建存储过程用于实现插入不同的数据（4）-WHILE语句。

DELIMITER  $$

CREATE PROCEDURE proc5() 

begin

declare var int; 

set var=0; 

while var<6 do 

insert into t values(var); 

set var=var+1; 

end while; 

end $$

DELIMITER ;

验证：

delete from t;

call proc5;

select * from t;

REPEAT语句语法格式如下：

repeat

--循环体

until循环条件

endrepeat;

注：它在执行操作后检查结果，而while则是执行前进行检查。

例题：新建存储过程用于实现插入不同的数据（5）-REPEAT语句。

DELIMITER $$ 

CREATE PROCEDURE proc6() 

begin  

declare v int; 

set v=0; 

repeat 

insert into t values(v); 

set v=v+1; 

until v>=5 

end repeat; 

end $$

DELIMITER ;

验证：

delete from t;

call proc6;

select * from t;

LOOP语句：

注：loop循环不需要初始条件，这点和while 循环相似，同时和repeat循环一样不需要结束条件, leave语句的意义是离开循环；

例题：新建存储过程用于实现插入不同的数据（6）-LOOP语句。

DELIMITER $$ 

CREATE PROCEDURE proc7() 

begin

declare v int; 

set v=0; 

LOOP_LABLE:loop 

insert into t values(v); 

set v=v+1; 

if v >=5 then

leave LOOP_LABLE; 

end if; 

end loop; 

end $$

DELIMITER ;

验证：

delete from t;

call proc7;

select * from t;

LABLES:标号：

标号可以用在begin repeat while 或者loop 语句前，语句标号只能在合法的语句前面使用。可以跳出循环，使运行指令达到复合语句的最后一步;

ITERATE(迭代)语句：

通过引用复合语句的标号,来从新开始复合语句。

例题：新建存储过程用于实现插入不同的数据（7）- ITERATE(迭代)语句。

DELIMITER $$ 

CREATE PROCEDURE proc10() 

begin

declare v int; 

set v=0; 

LOOP_LABLE:loop 

if v=3 then  

set v=v+1; 

ITERATE LOOP_LABLE; 

end if; 

insert into t values(v); 

set v=v+1; 

if v>=5 then

leave LOOP_LABLE; 

end if; 

end loop; 

end $$

DELIMITER ;

验证：

delete from t;

call proc10;

select * from t;

MySQL存储过程的查询:

（1） select name from mysql.proc where db=’数据库名’;

（2） show procedure status where db=’数据库名’;

（3） SHOW CREATE PROCEDURE 数据库.存储过程名 G

5）游标

游标（cursor）就是游动的标识，通俗的这么说，一条sql取出对应n条结果资源的接口/句柄，就是游标，沿着游标可以一次取出一行；

使用场景：

1.只能用于存储过程或存储函数中，不能单独在查询操作中使用；

2.在存储过程或存储函数中可以定义多个游标，但是一个BEGIN...END

语句块中每一个游标的名字必须是唯一的；

3.游标不是一条SELECT语句，是被SELECT语句检索出来的结果集；

游标使用步骤：

1）声明游标：使用游标前，必须申明（定义）它，定义要使用的SELECT语句；

DECLARE 游标名称 CURSOR FOR SELECT语句（返回一行或多行的数据）；

2）打开游标：使用游标前，打开游标

OPEN 游标名称；

3）读取数据：对于填有数据的游标，可根据需要取出数据；

FETCH 游标名 INTO 变量名1，...变量名n;

注：FETCH语句是将游标指向的一行数据赋给一些变量，这些变量的数目必须等于声明游标SELECT子句中选择列的数目，游标相当于一个指针，指向当前的一行数据；

关闭游标：CLOSE 游标名称

例题：在数据库db_school中创建一个存储过程，用于计算表tb_student中数据行的行数。

USE db_school;

Delimiter $$

Create procedure sp_sumrow(in rows int)

Begin

DECLARE sno CHAR;

DECLARE FOUND BOOLEAN DEFAULT TRUE;

DECLARE cur CURSOR FOR SELECT studentno from tb_student;

DECLARE CONTINUE HANDLER FOR NOT FOUND

SET FOUND=FALSE;

SET rows=0;

OPEN cur;

FETCH cur INTO sno;

WHILE FOUND DO

SET rows=rows+1;

FETCH cur INTO sno;

END WHILE;

CLOSE cur;

Select @rows;

End $$

Delimiter ;

验证：

Call sp_sumrow(@rows);

6）删除存储过程

mysql>drop procedure sp_sumrow;

三、存储函数

create function 存储函数名(参数数据类型)

RETURES 数据类型

存储函数体

Return values 返回数值给存储函数体

语法:

CREATE FUNCTION sp_name([func_parameter[,...]])

RETURNS type

[characteristic ...] routine_body

Return

 

存储函数语法：

Create function 函数([函数参数[,….]])

Returns 返回类型

Begin

If(

Return (返回的数据)

Else

Return(返回的数据)

end if;

end;

例1：在数据库db_school中创建一个存储函数，要求该函数根据给定的学号返回学生的姓名；

-- 创建存储函数

DELIMITER  $$

CREATE FUNCTION  f_studentname(id INT )

RETURNS CHAR(80) 

Begin

RETURN (SELECT Studentname FROM tb_student  WHERE studentno=id );

END

$$

DELIMITER ;

Select f_studentname(‘2013110101’);

例2：在数据库db_school中出创建一个存储函数，要求该函数根据给定的学号返回学生的性别，

如果数据库中没有给定的学号，则返回‘没有该学生’;

DELIMITER  $$

CREATE FUNCTION search_sex1(sno char(10))

Returns char(2)

deterministic

Begin

Declare ssex char(2);

Select sex INTO ssex from tb_student where studentno=sno;

IF SSEX IS NULL THEN

RETURN(SELECT '没有该学生');

ELSE IF SSEX='女' THEN

Return(Select '女');

ELSE

Return(Select '男');

END IF;

END IF;

end $$

Select search_sex1(‘2013110101’)$$

查看存储过程和函数

存储过程和函数创建以后，可以查看存储过程和函数的状态和定义。

通过SHOW STATUS语句来查看存储过程和函数的状态，也可以通过SHOW CREATE语句来查看存储过程和函数的定义。

通过查询information_schema数据库下的Routines表来查看存储过程和函数的信息

1、SHOW STATUS语句查看存储过程和函数的状态

MySQL中可以通过SHOW STATUS语句查看存储过程和函数的状态。其基本语法形式如下：

SHOW { PROCEDURE | FUNCTION } STATUS [ LIKE ' pattern ' ] ;

其中，PROCEDURE参数表示查询存储过程；FUNCTION参数表示查询存储函数；

LIKE ' pattern '参数用来匹配存储过程或函数的名称。

SHOW  FUNCTION STATUS LIKE '% search_sex1%';