1、定义
存储过程和函数是事先经过编译并存储在数据库中的一段 SQL 语句的集合。
存储过程和函数是在数据库中预先定义并编译好的可复用代码块，可以用于完成特定的任务，如计
算、查询和变换等。
2、好处
提高代码的复用性。
减少数据在数据库和应用服务器之间的传输，提高效率。
减少代码层面的业务处理。
3、存储过程的创建、调用、查看、删除
# 创建存储过程
CREATE PROCEDRE procedure_name([proc_parameter[,...]])
begin
--SQL语句
end ;
# 调用存储过程
call procedure_name();
# 查看存储过程
--查询db_name数据库中的所有的存储过程
select name from mysql.proc where db='db_name';
--查询存储过程的状态信息
show procedure status;
--查询某个存储过程的定义
show create procedure test.pro_test1 \G;
# 删除存储过程
DROP PROCEDURE [IF EXISTS] sp_name ;案例：创建stu_group()存储过程，封装分组查询总成绩，并按照总成绩升序排序的功能。
DELIMITER $ --修改SQL的结束符
CREATE PROCEDURE stu_group();
begin
select gender, SUM(score) getsum from student group by gender order by getsum ASC;
end $
DELIMITER ; --将SQL的结束符改回
CALL stu_group();--delimiter
该关键字用来声明SQL 语句的分隔符 , 告诉 MySQL 解释器，该段命令是否已经结束了， mysql是否可以执行了。
默认情况下，delimiter 是分号 ; 。在命令行客户端中，如果有一行命令以分号结束，那么回车后， mysql将会执行该命令。
4、存储过程的语法
（1） 变量
declare：定义一个局部变量，该变量的作用范围只能在BEGIN...END中。
set：直接赋值使用SET，可以赋常量或者赋表达式。
DELIMITER $
CREATE procedure pro_test5()
begin
declare name VARCHAR(20);
set name='MYSQL';
select name;
end$
DELIMITER ;也可以使用select ... into方法进行赋值操作。
DELIMITER $
CREATE procedure pro_test5()
begin
declare countnum int;
select count(*) into countnum from city;
select countnum;
end$
DELIMITER ;（2）if条件判断
示例：根据定义的身高变量，判断当前身高的所属的身材类型。
180及以上------------身材高挑
170-180--------------标准身材
170以下--------------一般身材
delimiter $
create procedure pro_test6()
begin
declare height int default 175;
declare description varchar(50);
if height >= 180
then set description = '身材高挑';
elseif height >= 170 and height < 180
then set description = '标准身材';
else
set description = '一般身材';
end if;
select description ;
end$
delimiter ;（3）传递参数
create procedure pro_name([in/out/inout] 参数名 参数类型)
...
in：该参数可以作为输入，也就是需要调用方传入值，默认
out：该参数作为输出，也就是作为返回值
inout：既可作为输入参数，也可作为输出参数
in输入-------示例：根据定义的身高变量，判断当前身高的所属的身材类型。
delimiter $
create procedure pro_test5(in height int)
begin
declare description varchar(50) default '';
if height >= 180
then set description = '身材高挑';
elseif height >= 170 and height < 180
then set description = '标准身材';
else
set description = '一般身材';
end if;
select concat('身高 ',height, '对应的身材类型为：',description) ;
end$
delimiter ;out输出----- 示例：根据传入的身高变量，获取当前身高的所属的身材类型
delimiter $
create procedure pro_test5(in height int, out description varchar(100))
begin
if height >= 180
then set description = '身材高挑';
elseif height >= 170 and height < 180
then set description = '标准身材';
else
set description = '一般身材';
end if;
end$
delimiter ;
调用
call pro_test5(168, @description);
select @description;tips:
@description : 这种变量要在变量名称前面加上 “@”符号，叫做用户会话变量，代表整个会话过程他都是有作用的，这个类似于全局变量一样。
@@global.sort_buffffer_size : 这种在变量前加上 "@@" 符号 , 叫做 系统变量。
（4）case结构
示例：给定一个月份，然后计算出所在的季度
delimiter $
create procedure pro_test9(month int)
begin
declare result varchar(20);
case
when month >= 1 and month <= 3
then set result = '第一季度';
when month >= 4 and month <= 6
then set result = '第二季度';
when month >= 7 and month <= 9
then set result = '第三季度';
when month >= 10 and month <= 12
then set result = '第四季度';
end case;
select concat('您输入的月份为：', month,'该月份为 ：', result) as content;
end$
delimiter ;（5）while循环
示例：计算从1加到n的值
delimiter $
create procedure pro_test8(n int)
begin
declare total int default 0;
declare num int default 1;
while(num <= n) do
set total = total + num;
set num = num + 1;
end while;
select total;
end$
delimiter ;（6）repeat结构
有条件的循环控制语句，当满足条件的时候退出循环。
示例：计算 从1加到n的值
delimiter $
create procedure pro_test20(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$
delimiter ;（7）loop语句
LOOP 实现简单的循环，退出循环的条件需要使用其他的语句定义，通常可以使用 LEAVE 语句实现，具体语法如下：
[begin_label:] LOOP
statement_list
END LOOP [end_label]
如果不在 statement_list 中增加退出循环的语句，那么 LOOP 语句可以用来实现简单的死循环。
（8）leave语句
用来从标注的流程构造中退出，通常和 BEGIN ... END 或者循环一起使用。
示例：使用 LOOP 和 LEAVE 退出循环。
create procedure pro_test11(n int)
begin
declare total int default 0;
ins: LOOP
if n <= 0 then
leave ins;
end if;
set total = total + n;
set n = n - 1;
end LOOP ins;
select total;
end$
delimiter ;(9) 游标/光标
游标是用来存储查询结果集的数据类型，在存储过程和函数中可以使用光标对结果集进行循环的处理。光标的使用，包括光标的声明、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 ;
# 示例
--auto_increment，自增; comment,给字段或添加注释
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')
)engine=innodb default charset=utf8 ;
insert into emp(id, name, age, salary)
values(null,'张三', 55, 3800),
(null,'李四', 60, 4000),
(null,'王五', 38, 2800),
(null,'赵六', 42, 1800)-- 查询emp表中数据, 并逐行获取进行展示
delimiter$
create procedure pro_test01()
begin
declare e_id int(11);
declare e_name varchar(50);
declare e_age comment int(11);
declare e_salary int(11);
declare emp_result cursor for select * from emp;
open emp_result;
fetch emp_result into e_id, e_name, e_age, e_salary;
select concat('id=',e_id , ', name=',e_name, ', age=', e_age, ', 薪资为: ',e_salary);
fetch emp_result into e_id, e_name, e_age, e_salary;
select concat('id=',e_id , ', name=',e_name, ', age=', e_age, ', 薪资为: ',e_salary);
fetch emp_result into e_id, e_name, e_age, e_salary;
select concat('id=',e_id , ', name=',e_name, ', age=', e_age, ', 薪资为: ',e_salary);
fetch emp_result into e_id, e_name, e_age, e_salary;
select concat('id=',e_id , ', name=',e_name, ', age=', e_age, ', 薪资为: ',e_salary);
close emp_result;
end$
delimiter ;--通过循环结构 , 获取游标中的数据
delimiter$
create procedure pro_test01()
begin
declare e_id int(11);
declare e_name varchar(50);
declare e_age comment 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 id, name, age, salary;
select concat('id=',e_id , ', name=',e_name, ', age=', e_age, ', 薪资为: ',e_salary);
until has_data = 0
end repeat;
close emp_result;
end$
delimiter ;5、存储函数的语法
CREATE FUNCTION function_name([param type ... ])
RETURNS type
BEGIN
...
END;
案例：
定义一个存储函数, 请求满足条件的总记录数
create function count_city(countryId int)
returns int
begin
declare cnum int ;
select count(*) into cnum from city where country_id=countryId
return cnum;
end$
delimiter ;
# 调用
select count_city(1);
select count_city(2);6、区别
(1) 标识符不同，函数标识符是function，存储过程是procedure。
(2) 返回值不同，函数返回单个值或者表对象，而过程没有返回值，但是可以通过out参数返回多个值。
(3) 参数不同，函数的参数只能是IN类型，存储过程的参数可以是IN、OUT、INOUT三种类型。
(4)调用方式不同，函数使用select调用，存储过程需要使用call调用。SQL语句不可调用存储过程。
7、常见使用场景
存储过程：较长复杂的业务逻辑、更新和删除相关操作。
函数 :简单的计算型任务、字符串或日期拼接、返回单个值等。
435
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
