存储过程和存储函数

存储过程和函数概述

存储过程和函数是事先经过编译存储在数据库中的一段SQL语句的集合，调用存储过程和函数可以简化应用开发人员的工作量，减少数据在数据库和应用服务器之间的传输，对于提高数据处理的效率是有好处的。

存储过程和和函数的区别在于，函数必须有返回值，而存储过程没有。

创建和调用存储过程

delimiter：用来声明分隔符

mysql> delimiter $
mysql> select * from user;
    -> $
+----+----------+----------+------------+------------+
| id | username | password | telephone  | entryDate  |
+----+----------+----------+------------+------------+
|  3 | MrDu     | 654321   | 1354689646 | 2020-04-10 |
+----+----------+----------+------------+------------+
1 row in set (0.00 sec)

原本的分隔符；已经被替换成$。

创建和调用：

mysql> create procedure pro_test1()
    -> begin
    -> select 'spring-cloud';
    -> end;
    -> $
Query OK, 0 rows affected (0.04 sec)

mysql> call pro_test1()$
+--------------+
| spring-cloud |
+--------------+
| spring-cloud |
+--------------+
1 row in set (0.01 sec)

Query OK, 0 rows affected (0.01 sec)

查看存储过程

• 查看数据库中所有的存储过程

mysql> select name from mysql.proc where db='spring-cloud';

• 查询存储过程的状态信息

mysql> show procedure status;

mysql> show procedure status\G;

• 查询某个存储过程的定义

mysql> show create procedure pro_test1\G;
*************************** 1. row ***************************
           Procedure: pro_test1
            sql_mode: ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION
    Create Procedure: CREATE DEFINER=`root`@`localhost` PROCEDURE `pro_test1`()
begin
select 'spring-cloud';
end
character_set_client: latin1
collation_connection: latin1_swedish_ci
  Database Collation: utf8_general_ci
1 row in set (0.00 sec)

ERROR: 
No query specified

删除存储过程

mysql> drop procedure pro_test1;
Query OK, 0 rows affected (0.08 sec)

mysql> 

语法

• declare：可以定义一个局部变量，该变量的作用范围只能在begin-end块中。

declare var_name[,…] type [default value]

mysql> delimiter $
mysql> create procedure pro_test1()
    -> begin 
    -> declare num int default 10;
    -> select concat('num:',num);
    -> end$
Query OK, 0 rows affected (0.10 sec)

mysql> call pro_test1()$
+--------------------+
| concat('num:',num) |
+--------------------+
| num:10             |
+--------------------+
1 row in set (0.01 sec)

Query OK, 0 rows affected (0.01 sec)

• set：直接赋值使用set，可以赋常量或者表达式。

set var_name = expr [, var_name = expr] …

mysql> create procedure pro_test2()
    -> begin
    -> declare num int default 0;
    -> set num = num + 20;
    -> select num;
    -> end$
Query OK, 0 rows affected (0.04 sec)

mysql> call pro_test2();
    -> $
+------+
| num  |
+------+
|   20 |
+------+
1 row in set (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

• select … into:赋值

mysql> create procedure pro_test3()
    -> begin
    -> declare num int;
    -> select count(*) into num from user;
    -> select concat('records for user table:',num);
    -> end$
Query OK, 0 rows affected (0.06 sec)

mysql> call pro_test3()$
+---------------------------------------+
| concat('records for user table:',num) |
+---------------------------------------+
| records for user table:1              |
+---------------------------------------+
1 row in set (0.20 sec)

Query OK, 0 rows affected (0.20 sec)

• if条件判断

mysql> create procedure pro_test4()
    -> begin
    -> declare score int default 79;
    -> declare description varchar(50) default '';
    -> if score >= 80 then
    -> set description = 'good';
    -> elseif score <= 60 then
    -> set description = 'bad';
    -> else 
    -> set description = 'ordinary';
    -> end if;
    -> select concat('score:',score,'score type:',description);
    -> end$
Query OK, 0 rows affected (0.00 sec)

mysql> call pro_test4()$
+--------------------------------------------------+
| concat('score:',score,'score type:',description) |
+--------------------------------------------------+
| score:79score type:ordinary                      |
+--------------------------------------------------+
1 row in set (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

• 参数传递

语法格式：

create procedure procedure_name([in/out/inout] 参数名 参数类型)
...

in：该参数作为输入，也就是需要调用方传入值，默认
out：该参数作为输出，也就是可以作为返回值
inout：既可以作为输入参数，也可以作为输出参数

1. in输入参数例子：

mysql> create procedure pro_test5(in score int)
    -> begin
    -> declare description varchar(50) default '';
    -> if score >= 80 then
    -> set description = 'good';
    -> elseif score <= 60 then
    -> set description = 'bad';
    -> else
    -> set description = 'ordinary';
    -> end if;
    -> select concat('score:',score,'score type:',description);
    -> end$
Query OK, 0 rows affected (0.11 sec)

mysql> call pro_test5(90)$
+--------------------------------------------------+
| concat('score:',score,'score type:',description) |
+--------------------------------------------------+
| score:90score type:good                          |
+--------------------------------------------------+
1 row in set (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

mysql> call pro_test5(70)$
+--------------------------------------------------+
| concat('score:',score,'score type:',description) |
+--------------------------------------------------+
| score:70score type:ordinary                      |
+--------------------------------------------------+
1 row in set (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

mysql> call pro_test5(50)$
+--------------------------------------------------+
| concat('score:',score,'score type:',description) |
+--------------------------------------------------+
| score:50score type:bad                           |
+--------------------------------------------------+
1 row in set (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

2. out输出参数例子：

mysql> create procedure pro_test6(in score int,out description varchar(50))
    -> begin
    -> if score >= 80 then
    -> set description = 'good';
    -> elseif score <= 60 then
    -> set description = 'bad';
    -> else
    -> set description = 'ordinary';
    -> end if;
    -> end$
Query OK, 0 rows affected (0.05 sec)

mysql> call pro_test6(90,@description)$
Query OK, 0 rows affected, 1 warning (0.00 sec)

mysql> select @description$
+--------------+
| @description |
+--------------+
| good         |
+--------------+
1 row in set (0.00 sec)

mysql> call pro_test6(50,@description)$
Query OK, 0 rows affected (0.00 sec)

mysql> select @description$
+--------------+
| @description |
+--------------+
| bad          |
+--------------+
1 row in set (0.00 sec)

@description：在变量名称前加上’@'符号，叫做用户会话变量，代表整个会话过程他都是有作业的，类似于全局变量。

@@description：系统变量。

• 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例子：

mysql> create procedure pro_test7(mon int)
    -> begin
    -> declare result varchar(50);
    -> case
    ->  when mon >= 1 and mon <= 3 then
    ->   set result = 'first quarter';
    ->  when mon >= 4 and mon <= 6 then
    ->   set result = 'second quarter';
    ->  when mon >= 7 and mon <= 9 then
    ->   set result = 'third quarter';
    ->  else
    ->   set result = 'fourth quarter';
    -> end case;
    -> select concat('month:',mon,'quarter:',result);
    -> end$
Query OK, 0 rows affected (0.05 sec)

mysql> call pro_test7(5)$
+----------------------------------------+
| concat('month:',mon,'quarter:',result) |
+----------------------------------------+
| month:5quarter:second quarter          |
+----------------------------------------+
1 row in set (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

• 循环

1. while循环

语法结构：

while search_condition do
    statement_list
end while;

例子：从1加到n

mysql> create procedure pro_test8(n int)
    -> begin
    ->  declare total int default 0;
    ->  declare start int default 0;
    -> 
    ->  while start <= n do
    ->   set total = total + start;
    ->   set start = start + 1;
    ->  end while;
    ->  select concat('result=',total);
    -> end$
Query OK, 0 rows affected (0.05 sec)

mysql> call pro_test8(100)$
+-------------------------+
| concat('result=',total) |
+-------------------------+
| result=5050             |
+-------------------------+
1 row in set (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

2. repeat循环

有条件的循环控制语句，当满足条件时，退出循环；while时满足条件才执行。

语法结构：

repeat
    statement_list
    until search_condition
end repeat;

例子：从1加到n

mysql> create procedure pro_test9(n int)
    -> begin
    ->  declare total int default 0;
    ->  repeat
    ->   set total = total + n;
    ->   set n = n - 1;
    ->   until n <= 0
    ->  end repeat;
    ->  select total;
    -> end$
Query OK, 0 rows affected (0.04 sec)

mysql> call pro_test9(100)$
+-------+
| total |
+-------+
|  5050 |
+-------+
1 row in set (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

until后面不加’;’

3. loop循环

loop实现简单的循环，退出循环条件需要使用其他的语句定义，通常使用leave实现，如果不在statement_list中增加退出循环语句，那么loop循环就是一个死循环。

语法结构：

[begin_lable:] loop
    statement_list
end loop [end_lable]

例子：从1加到n

mysql> create procedure pro_test10(n int)
    -> begin
    ->  declare total int default 0;
    ->  a:loop
    ->   set total = total + n;
    ->   set n = n - 1;
    ->   
    ->   if n <= 0 then
    ->    leave a;
    ->   end if;
    ->  end loop a;
    ->  select total;
    -> end$
Query OK, 0 rows affected (0.04 sec)

mysql> call pro_test10(100)$
+-------+
| total |
+-------+
|  5050 |
+-------+
1 row in set (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

• 游标/光标

游标是用来存储查询结果集的数据类型，在存储过程和函数中可以使用游标对结果集进行循环处理。游标的使用包括游标的声明、open、fetch、close。

语法结构：

#声明游标
declare cursor_name cursor for select_statement;

#open游标
open cursor_name;

#fetch游标
fetch cursor_name into var_name[, var_name]...

#close游标
close cursor_name;

初始化一张员工表：

CREATE TABLE emp(
	id INT(11) NOT NULL auto_increment,
	name VARCHAR(50) NOT NULL COMMENT '名字',
	age INT(11) COMMENT '年龄',
	salary INT(11) COMMENT '薪水',
	PRIMARY KEY(id)
)DEFAULT charset=utf8;

INSERT INTO emp(id,name,age,salary) VALUES
(NULL,'金毛狮王',55,3800),
(NULL,'白眉鹰王',60,4000),
(NULL,'青翼蝠王',50,3000),
(NULL,'紫衫龙王',49,2000);

例子：

mysql> create procedure pro_test11()
    -> begin
    -> declare e_id int(11);
    -> declare e_name varchar(50);
    -> declare e_age int(11);
    -> declare e_salary int(11);
    -> declare has_data int default 1;
    -> 
    -> declare emp_result cursor for select * from emp;
    -> DECLARE EXIT HANDLER FOR NOT FOUND set has_data = 0;
    -> 
    -> open emp_result;
    -> 
    -> repeat
    ->  fetch emp_result into e_id,e_name,e_age,e_salary;
    ->  select concat('id=',e_id,',name=',e_name,',age=',e_age,',salary=',e_salary);
    ->  until has_data = 0
    -> end repeat;
    ->  
    -> close emp_result;
    -> end$
Query OK, 0 rows affected, 3 warnings (0.04 sec)

mysql> call pro_test11()$
+----------------------------------------------------------------------+
| concat('id=',e_id,',name=',e_name,',age=',e_age,',salary=',e_salary) |
+----------------------------------------------------------------------+
| id=1,name=????,age=55,salary=3800                                    |
+----------------------------------------------------------------------+
1 row in set (0.01 sec)

+----------------------------------------------------------------------+
| concat('id=',e_id,',name=',e_name,',age=',e_age,',salary=',e_salary) |
+----------------------------------------------------------------------+
| id=2,name=????,age=60,salary=4000                                    |
+----------------------------------------------------------------------+
1 row in set (0.01 sec)

+----------------------------------------------------------------------+
| concat('id=',e_id,',name=',e_name,',age=',e_age,',salary=',e_salary) |
+----------------------------------------------------------------------+
| id=3,name=????,age=50,salary=3000                                    |
+----------------------------------------------------------------------+
1 row in set (0.01 sec)

+----------------------------------------------------------------------+
| concat('id=',e_id,',name=',e_name,',age=',e_age,',salary=',e_salary) |
+----------------------------------------------------------------------+
| id=4,name=????,age=49,salary=2000                                    |
+----------------------------------------------------------------------+
1 row in set (0.01 sec)

Query OK, 0 rows affected (0.01 sec)

因为用的Linux，中文显示不出来，可以自行百度解决

存储函数

语法结构：

create function function_name([param type...])
returns type
begin
    ...
end;

例子：

mysql> create function fun1(e_age int)
    -> returns int
    -> begin
    ->  declare total int;
    ->  select count(id) into total from emp where age < e_age;
    ->  return total;
    -> end$
Query OK, 0 rows affected (0.04 sec)

mysql> select fun1(55)$
+----------+
| fun1(55) |
+----------+
|        2 |
+----------+
1 row in set (0.01 sec)