MySQL高级之存储过程和存储函数

1 存储过程和函数概述

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

函数 ： 是一个有返回值的过程 ；
过程 ： 是一个没有返回值的函数 ；

2 创建存储过程

语法：

CREATE PROCEDURE procedure_name ([proc_parameter[,...]]) 
begin
-- SQL语句 
end ;

示例 ：

delimiter $ 
create procedure pro_test1() 
begin
  select 'Hello Mysql' ; 
end$ 
delimiter ;

知识小贴士
DELIMITER
该关键字用来声明SQL语句的分隔符 , 告诉 MySQL 解释器，该段命令是否已经结束了，mysql是否可以执行了。
默认情况下，delimiter是分号;。在命令行客户端中，如果有一行命令以分号结束，那么回车后，mysql将会执行该命令。

3 调用存储过程

call procedure_name() ;

4 查看存储过程

-- 查询db_name数据库中的所有的存储过程
select name from mysql.proc where db='db_name';
-- 查询存储过程的状态信息 
show procedure status; 
-- 查询某个存储过程的定义 
show create procedure test.pro_test1 \G; 

5 删除存储过程

DROP PROCEDURE [IF EXISTS] sp_name ；

6 语法

存储过程是可以编程的，意味着可以使用变量，表达式，控制结构 ， 来完成比较复杂的功能。

6.1 变量

• DECLARE
  通过 DECLARE 可以定义一个局部变量，该变量的作用范围只能在 BEGIN…END 块中。

DECLARE var_name[,...] type [DEFAULT value]

示例：

delimiter $
create procedure pro_test2() 
begin
declare num int default 5;
select num+ 10;
end$ 
delimiter ;

• SET
  直接赋值使用 SET，可以赋常量或者赋表达式，具体语法如下：

SET var_name = expr [, var_name = expr] ...

示例 :

DELIMITER $
CREATE PROCEDURE pro_test3()
BEGIN 
DECLARE NAME VARCHAR(20); 
SET NAME = 'MYSQL';
SELECT NAME ;
END$
DELIMITER ;

也可以通过select … into 方式进行赋值操作 :

DELIMITER $ 
CREATE PROCEDURE pro_test5() 
BEGINdeclare countnum int; 
select count(*) into countnum from city; 
select countnum; 
END$ 
DELIMITER ;

6.2 if条件判断

语法结构 :

if search_condition then statement_list 
	[elseif search_condition then statement_list] ... 
	[else statement_list] 
end if;

需求：

根据定义的身高变量，判定当前身高的所属的身材类型 
180 及以上 ----------> 身材高挑 
170 - 180 ---------> 标准身材 
170 以下 ----------> 一般身材

示例 :

delimiter $ 
create procedure pro_test6() 
begin
 declare height int default 175; 
 declare description varchar(50); 
 if height