Mysql存储过程_mysql 存储过程 declare table

学习技术需要掌握

作用、原理、安装步骤、工具使用、模拟演示、注意细节、优点缺点

一、mysql存储过程的作用和原理 

原理：mysql存储过程是一组为了完成特定功能的sql语句集，经过编译后存储在数据库中，需要使用时，给定参数调用执行即可。

作用：简化开发人员的工作，减少数据在数据库和应用服务器之间的传输，提高数据处理的效率

二、存储过程的基本操作

① 创建一个存储过程

delimiter $ ##分隔符;改成$
 
CREATE procedure pro_test1() 
BEGIN
	SELECT 'hello mysql';
END$
 
delimiter ; ##分隔符$改成;

解析： delimiter mysql的默认执行分割符是“;”分号， 因为我们的存储过程中有代码要使用分号，如果还是以分号为结束符的话,存储过程就会报错，所以最开始需要把分隔符分号换成$符号，最后改成分号；

存储过程的创建的语法

CREATE procedure pro_test1() 
BEGIN
    sql代码
END$

② 执行存储过程

CALL pro_test1();

③ 查询存储过程

1、可以在mysql.proc表查看

SELECT * FROM mysql.proc ;

2、查看存储过程的状态

SHOW procedure status

3、查看存储创建的语句

SHOW CREATE PROCEDURE pro_test1;

④ 删除存储过程

DROP procedure pro_test1;

三、存储过程的语句

① 定义变量

declare var_name type [default value]

delimiter $ ##分隔符;改成$
 
CREATE procedure pro_test3() 
BEGIN
 
		declare num int DEFAULT 10; ##声明一个int变量
		declare str varchar(100) DEFAULT "哈哈"; ##定义一个字符串
		
		select str;
		SELECT num;
 
END$
 
delimiter ; ##分隔符$改成;

变量类型为mysql的数据类型，如int、float、date、varchar(length)

 ② 给变量赋值，修改变量

delimiter $ ##分隔符;改成$
 
CREATE procedure pro_test4() 
BEGIN
 
		declare num int DEFAULT 10; ##声明一个变量
		set num = num + 10;  ##第一种 直接用set
		
		SELECT num;
 
		SELECT COUNT(*) into num FROM system_data;  ##第二种 使用into 
 
		SELECT num;
 
END$
 
delimiter ; ##分隔符$改成;

解析：有两种方式进行赋值：1、set命令 2、在sql语句中into命令

③ 条件判断语句

delimiter $ ##分隔符;改成$
 
CREATE procedure pro_test5() 
BEGIN
 
		declare height int default 175;
		declare msg varchar(50) default '';
		
		if height >= 180 then ## if 判断 then 结尾
			set msg = '身材高挑';
		elseif height >= 170 and height < 180 then  ## elseif 判断 then 结尾
			set msg = '标准身材';
		else 
			set msg = '一般身材';
		end if;                        ##end if; 结尾
 
		SELECT msg;
 
END$
 
delimiter ; ##分隔符$改成;

 解析：条件判断跟一般的编程语句不一样，一般的编程语句时用 if () {} 判断的，而存储过程使用的 if 判断条件 then end if; 所以要注意一下。

④ 传参

1、传入参数 输入一个人的身高，判断身材什么情况

delimiter $ ##分隔符;改成$
 
CREATE procedure pro_test6(in height int)  ##在存储过程名括号这里定义 in 变量名 变量类型
BEGIN
 
		##declare height int default 175;
		declare msg varchar(50) default '';
		
		if height >= 180 then 
			set msg = '身材高挑';
		elseif height >= 170 and height < 180 then 
			set msg = '标准身材';
		else 
			set msg = '一般身材';
		end if;
 
		SELECT msg;
 
END$
 
delimiter ; ##分隔符$改成;

调用

CALL pro_test6(170);  ##在括号里面调用

2、 输出参数

delimiter $ ##分隔符;改成$
 
CREATE procedure pro_test7(in height int, out msg varchar(100))  ##在存储过程名这里定义 in 变量名 变量类型 out为输出参数
BEGIN
 
		if height >= 180 then 
			set msg = '身材高挑';
		elseif height >= 170 and height < 180 then 
			set msg = '标准身材';
		else 
			set msg = '一般身材';
		end if;
 
		SELECT msg;
 
END$
 
delimiter ; ##分隔符$改成;

解析：out msg varchar(100) 为输出参数

调用：放入一个输出参数，然后在打印就可以显示，@msg表示一个用户会话变量

CALL pro_test7(180, @msg);
SELECT @msg;

⑤ case 条件判断

1、第一种方式， 输入一个月份，判断该月份属于第几季度

delimiter $
CREATE procedure pro_test8(in mon int) 
begin
	declare qua varchar(10);
	CASE 
		WHEN mon >=1 AND mon <=3 THEN ##格式是 when 条件 then 代码
			set qua = '第一季度';
		WHEN mon >=4 AND mon <= 6 THEN
			set qua = '第二季度';
		WHEN mon >= 7 AND mon <= 9 THEN
			set qua = '第三季度';
		WHEN mon >= 10 AND mon <= 12 THEN
			set qua = '第四季度';
		ELSE
			set qua = '错误';
	end case;  ##记得这里一定要加入分号
	
	SELECT qua;
 
end$
delimiter ;
 
 
call pro_test8(12); ##调用

解析：这种方式采用的就是 when 条件判断 then 代码 else 代码的方式

2、第二种方式  类似于编程语言中的switch，case 后面放变量

输入一个参数，判断这个变量是那个支付

delimiter $
create procedure pro_test9(in pay_type int)
BEGIN
	
	declare pay_type_msg varchar(20);
	
	case pay_type
		when 1 then 
			SET pay_type_msg = '微信支付';
		when 2 then 
			SET pay_type_msg = '支付宝支付';
		WHEN 3 then 
			SET pay_type_msg = '银行卡支付';
		ELSE
			set pay_type_msg = '其他方式支付';		
	end case;
 
	SELECT pay_type_msg;
	
end$
delimiter ;
 
CALL pro_test9(100);

⑥ while 循环

从1+n 的循环语句

delimiter $
 
CREATE PROCEDURE pro_test10(in n int)
BEGIN
 
	DECLARE sum int DEFAULT 0;
	DECLARE i int DEFAULT 0;
 
	WHILE i <= n DO    ##循环的条件判断
		set i = i + 1;
		set sum = sum + i;
		
	END while;         ##循环结束 一定需要分号结束
	
 
	SELECT sum;
	
END$
 
delimiter ;
 
 
call pro_test10(100);

解析：while循环使用格式

while 判断条件 do

代码

end while; ##记得这里一定需要分号

⑥ repeat 循环

统计1到n相加的循环语句

 
delimiter $
 
CREATE PROCEDURE pro_test11(in n int)
BEGIN
 
	DECLARE sum int DEFAULT 0;
 
	repeat 
		set sum = sum + n;
		set n = n - 1;
		
		until n = 0 ##这里不能写分号，不然语法报错
	END repeat;
 
	SELECT sum;
	
	
END$
 
delimiter ;
 
 
call pro_test11(100);

解析：

repeat 
        代码
        until n = 0 ##这里不能写分号，不然语法报错， 如果until满足条件则退出
END repeat;

⑦ loop循环

统计1到n相加的循环语句

 
delimiter $
 
CREATE PROCEDURE pro_test12(in n int)
BEGIN
 
	DECLARE sum int DEFAULT 0;
	
	c:LOOP ##申明一个loop循环 c表示循环的名称，后面停止和结束需要使用
		set sum = sum + n;
		set n = n - 1;
		
		if n <= 0 then 
			leave c; ##停止 循环
		end if;
	END LOOP c;  ##结束循环 c是前面定义的
		
 
	SELECT sum;
	
END$
delimiter ;
 
 
call pro_test12(10);

解析： loop需要使用 leave来停止

c : loop ##需要定义循环的标签，方便后面使用

四、游标

① 一个游标的代码 

测试表

create table user (
	id int auto_increment,
	name varchar(100),
	age int,
	
	primary key(id)
) ENGINE = INNODB

使用游标读取数据 

delimiter $
 
CREATE PROCEDURE pro_test12()
BEGIN
 
	declare id int(11);
	declare name VARCHAR(100) CHARACTER SET utf8mb4;
	declare age int(11);
	declare has_data tinyint default 1;
 
	declare user_result cursor for select * from user; ##定义游标变量 user_result; 必须申请在处理程序之前
  ### 捕获系统抛出的 not found 错误，如果捕获到，将 has_data 设置为 0  相当于try异常
	declare continue handler for not found set has_data = 0; ## 检测游标是否循环完，必须放到定义游标的下面
 
	
	##打开游标
	OPEN user_result;
	
	##循环游标
	WHILE has_data = 1 DO
			fetch user_result into id, name, age;  ##循环获取每行数据，注意这里的id,name,age的变量类型必须是跟表定义的类型一致
			select CONCAT('id=', id, 'name=', name, 'age=', age);
	END WHILE;
 
	##关闭游标
	CLOSE user_result;
 
END$
 
delimiter ;

 解析：读取游标必须要

①定义游标变量，且定义游标是否结束变量

②开启

③循环读取数据

④关闭游标

② 多个游标的代码 

delimiter $
 
CREATE PROCEDURE pro_test12()
BEGIN
 
	declare id int(11);
	declare name VARCHAR(100) CHARACTER SET utf8mb4;
	declare nam2 mediumtext CHARACTER SET utf8mb4;
	declare age int(11);
	declare has_data tinyint default 1;
    declare has_data2 tinyint default 1;
 
	declare user_result cursor for select * from user; ##定义游标变量 user_result; 必须申请在处理程序之前
    declare test_result cursor for select * from test05;  
	### 捕获系统抛出的 not found 错误，如果捕获到，将 has_data 设置为 0  相当于try异常
	## 这里需要注意continue handler和exit handler 的区别，开始我使用的的exit handler直接监控到游标没有了 程序执行停止运行了，查了半天不知道什么问题
	declare continue handler for not found set has_data = 0, has_data2 = 0;
 
	##打开游标
	OPEN user_result;
 
	##循环游标
	while  has_data = 1 DO
			fetch user_result into id, name, age;  ##循环获取每行数据，注意这里的id,name,age的变量类型必须是跟表定义的类型一致
			select CONCAT('id=', id, 'name=', name, 'age=', age);
	END while;
 
	##关闭游标
	CLOSE user_result;
 
 
	set has_data2 = 1;  ##上面的循环把has_data设置=0，所以这里需要重新设置=1
	##打开游标
	OPEN test_result;
	
	##循环游标
	WHILE has_data2 = 1 DO
			fetch test_result into id, nam2;  ##循环获取每行数据，注意这里的id,name,age的变量类型必须是跟表定义的类型一致
			select CONCAT('id=', id, 'name=', nam2);
	END WHILE;
 
	##关闭游标
	CLOSE test_result;
	
END$
 
delimiter ;

注意细节：

定义游标没有后参数改变的变量，不能使用 declare exit handler,要使用continue handler。

declare continue handler for not found set has_data = 0, has_data2 = 0;

五、函数

定义一个函数，传userId，返回userId的行数；

跟存储过程不同的时，可以使用return变量 

delimiter $
 
create function fun1(userId int)  returns int  ##函数定义 返回int，记得这里是returns
BEGIN
	DECLARE cnum int;
	select COUNT(*) INTO cnum from test04 where user_id = userId;
	return cnum; ##返回变量
end$
delimiter ;

调用使用select fun1(1); 

SELECT fun1(1);

六、一些存储过程的案例

①  批量导入100万的随机测试数据 

delimiter $
 
CREATE procedure test_pro(in n int(11))
BEGIN
		declare temp_char char(1);
		###select repeat(temp_char, floor(1 + rand() * (100 - 1)));
		WHILE n > 0 DO
			##随机取出一个字符
			SET temp_char = char(floor(33 + rand() * (93 - 1)));
			
			insert into user value(null, repeat(temp_char, floor(1 + rand() * (100 - 1))), 11);
			SET n = n - 1;
	
		END WHILE;
		
end$
 
delimiter;

 char ASCII的数字转换成字符串

 repeat 是将字符串重复多少个

七、存储过程和函数的区别

1、 函数可以使用return返回值的形式，而存储过程只能只用 out 的参数形式，

2、函数调用直接用select, 存储过程需要使用 call命令

3、其他的好像没有什么区别，中间的代码都是可以共用的