MySQL存储过程

存储过程和存储函数概述

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

存储过程和函数的区别在于函数必须有返回值,而存储过程没有
函数:是一个有返回值的过程
过程:是一个没有返回值的函数

1.创建存储过程

DELIMITER $		--修改分隔符语法

CREATE PROCEDURE test1()	--存储过程名称
BEGIN
			SELECT * from city; --SQL语句体
END 

调用存储过程

CALL test1();

查看存储过程

SELECT name FROM mysql.proc where db='demo_01'; --数据库名称

查看存储过程的状态信息

-- 查看存储过程的状态信息
SHOW PROCEDURE STATUS;

查看某个存储过程的定义

SHOW CREATE PROCEDURE test1;

删除存储过程

DROP PROCEDURE test1;

2.存储过程语法结构
变量

-- 声明变量语法
-- DECLARE 变量名 类型  默认值
CREATE PROCEDURE test2()
BEGIN
			DECLARE num int DEFAULT 10;
			SELECT num + 10 ;
END

call test2; --输出20

赋值

-- 赋值  SET
CREATE PROCEDURE test3()
BEGIN
			DECLARE num int DEFAULT 10;
			SET num = num+10;
			SELECT num ;
END

call test3;   --输出20

-- 赋值  select.....into
CREATE PROCEDURE test4()
BEGIN
			DECLARE num int;
			SELECT count(*) into num from city;--将city记录数赋值给num
			SELECT concat('num的值为:', num) ;
END

call test4;

if条件判断

-- 需求:根据定义的身高数量,判断当前身高的所属身材类型
-- 180及以上  身材高挑
-- 170-180  标准身材
-- 170以下  一般身材

CREATE PROCEDURE test5()
BEGIN
			DECLARE height int default 175;
			DECLARE description VARCHAR(30) DEFAULT '';
			if height >= 180 then --if判断,满足就执行下方的sql语句
			SET description='身材高挑';
			ELSEIF description >= 170 and description < 180 then 
			SET description='标准身材';
			ELSE
			set description='一般身材';
			end if;
			SELECT height,description;
END

call test5;

参数输入

语法格式:
CREATE PROCEDURE 存储过程名称([in/out/inout] 参数名 参数类型)
....

in:该参数可以作为默认,也就是需要调用存储过程时传参
out:该参数作为输出,也就是可以作为返回值
inout:既可以作为输入参数,也可以作为输出参数

in

-- 需求:根据输入的身高数量,判断当前身高的所属身材类型
-- 180及以上  身材高挑
-- 170-180  标准身材
-- 170以下  一般身材

CREATE PROCEDURE test6(in height int)
BEGIN
			DECLARE description VARCHAR(30) DEFAULT '';
			if height >= 180 then 
			SET description='身材高挑';
			ELSEIF description >= 170 and description < 180 then 
			SET description='标准身材';
			ELSE
			set description='一般身材';
			end if;
			SELECT height,description;
END

call test6(200);

out

-- 需求:根据输入的身高数量,判断当前身高的所属身材类型(返回值)
-- 180及以上  身材高挑
-- 170-180  标准身材
-- 170以下  一般身材

CREATE PROCEDURE test7(in height int, out description varchar(30))--height是输入的参数,description 是带有返回值的参数
BEGIN
			if height >= 180 then 
			SET description='身材高挑';
			ELSEIF description >= 170 and description < 180 then 
			SET description='标准身材';
			ELSE
			set description='一般身材';
			end if;
END

call test7(168, @description);--使用@定义用户会话变量接收返回值,@@就是系统变量
SELECT @description;--查询返回值

case when语句结构

格式一:
case 列名
			when 值1 when 选项1
			when 值2 when 选项2
			else 默认值
end;

需求:根据城市名称设置对应省份
CREATE PROCEDURE test8()
BEGIN
			SELECT city_id,city_name,
			(case city_name 
			when '西安' then '陕西省'
			when '北京' then '北京市'
			when '上海' then '上海市'
			when '深圳' then '广东省'
			when '广州' then '广东省'
			else '未知'
			end ) as province 
from city;
END

call test8()

格式二:
case 
	when 判断条件1 then 选项1
	when 判断条件2 then 选项2
	when 判断条件3 then 选项3
	else 默认值
end;

需求:判断城市名称为NewYork时,返回1,否则0
CREATE PROCEDURE test9()
BEGIN
			select city.city_name,
			(case 
					when city.city_name='NewYork' then 1 ELSE 0 END
       ) as count 
			from city inner join country on city.country_id =  country.country_id;
END

call test9()

while循环

格式:
while 循环条件 DO
	循环体
end while;

需求:while循环插入100条数据
CREATE PROCEDURE test10()
BEGIN
	DECLARE i int default 1;
	DECLARE sum int default 100 ;
	while i <= sum DO
		INSERT INTO `city` (`city_id`, `city_name`, `country_id`) VALUES (null, CONCAT('深圳',i), '3');
		set i = i+1;
		end while	;
END

call test10()

repeat循环

格式:
repeat 
	循环体
	until 退出条件
end repeat;


需求:批量删除100条数据
CREATE PROCEDURE test11()
BEGIN
	DECLARE i int default 1;
	DECLARE sum int default 100 ;

	REPEAT
		
		DELETE from city where city.city_name=CONCAT('深圳',i);
		set i = i+1;
		until i > sum
		end repeat;
	
END

call test11()

loop循环

loop 能实现简单的循环,退出循环的条件需要使用其他的语句定义,通常可以使用leave语句实现
格式:
[begin_lable:]loop  -- begin_lable 循环的别名

	循环体
end loop [end_lable]

需求:批量增加100条数据
CREATE PROCEDURE test12()
BEGIN
	DECLARE i int default 1;
	DECLARE sum int default 100 ;

	c:LOOP  -- 开始循环
		INSERT INTO `city` (`city_id`, `city_name`, `country_id`) VALUES (null, CONCAT('深圳',i), '3');
		set i = i+1; -- 每次累加
		if i > sum then -- 判断条件
			leave c; -- 退出循环
		end if;
	end loop c; -- 结束循环
	
END

call test12()