MySQL存储过程

杨青葱

于 2021-11-07 19:22:31 发布

阅读量580

点赞数 1

分类专栏：存储过程游标文章标签： mysql 数据库 sql

本文链接：https://blog.youkuaiyun.com/LUSH_BOY/article/details/121195583

版权

存储过程同时被 2 个专栏收录

1 篇文章

订阅专栏

游标

1 篇文章

订阅专栏

一、存储过程介绍

1.指令执⾏过程

将能够完成特定功能的SQL指令进⾏封装（SQL指令集），编译之后存储在数据库服务器上，并且为之取⼀个名字，客户端可以通过名字直接调⽤这个SQL指令集，获取执⾏结果。
在这里插入图片描述

2.存储过程优点：

SQL指令⽆需客户端编写，通过⽹络传送，可以节省⽹络开销，同时避免SQL指令在⽹络传输过程中被恶意篡改保证安全性；
存储过程经过编译创建并保存在数据库中的，执⾏过程⽆需重复的进⾏编译操作，对SQL指令的执⾏过程进⾏了性能提升；
存储过程中多个SQL指令之间存在逻辑关系，⽀持流程控制语句（分⽀、循环），可以实现更为复杂的业务;

3.存储过程的缺点：

存储过程是根据不同的数据库进⾏编译、创建并存储在数据库中；当我们需要切换到其他的数据库产品时，需要重写编写针对于新数据库的存储过程；
存储过程受限于数据库产品，如果需要⾼性能的优化会成为⼀个问题；
在互联⽹项⽬中，如果需要数据库的⾼（连接）并发访问，使⽤存储过程会增加数据库的连接执⾏时间（因为我们将复杂的业务交给了数据库进⾏处理）

二、存储过程创建语法

create procedure <proc_name>([IN/OUT args])
begin
	-- SQL
end;

#参数列表包含三部分
参数模式  参数名   参数类型
如：
IN	stuname		varchar(20);

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


#示例1
create procedure proc_test1(IN a int,IN b int,OUT c int)
begin
	SET c = a+b;
end; 


#示例2
create procedure proc_test6(INOUT str varchar(20))
begin
		#将学号为str的学生的姓名赋给str;
		select stu_name INTO str from students where stu_num=str;
end;

set @name='20210108';
call proc_test6(@name);
select @name from dual;


#使用注意事项
A:如果存储过程体仅仅只有一句话，begin end可以省略。

B:存储过程体中的每条sql语句的结尾要求必须加分号。
 
C 存储过程的结尾可以使用 delimiter 重新设置
  语法：
  delimiter 结束标记
  如：
  delimiter $

三、调⽤存储过程

-- 调⽤存储过程
CALL 存储过程名(实参列表);

#例如
-- 定义变量@m
set @m = 0;
-- 调⽤存储过程，将3传递给a，将2传递给b，将@m传递给c
call proc_test1(3,2,@m);
-- 显示变量值
select @m from dual;

四、存储过程中变量的使⽤

1.局部变量：定义在存储过程中的变量，只能在存储过程内部使⽤

-- 局部变量要定义在存储过程中，⽽且必须定义在存储过程开始
declare <attr_name> <type> [default value];	#default:在定义时设置默认值；可选；
#或
declare <attr_name> <type>;
set <attr_name> = <常数值/变量>;


#实例
create procedure proc_test2(IN a int,OUT r int)
begin
	declare x int default 0; -- 定义x int类型，默认值为0
	declare y int default 1; -- 定义y
	set x = a*a;
	set y = a/2;
	set r = x+y;
end;

2.定义⽤户变量：相当于全局变量，但在存储体内部无法访问用户变量；

-- ⽤户变量会存储在mysql数据库的数据字典中（dual）
-- ⽤户变量定义使⽤set关键字直接定义，变量名要以@开头
set @n=1;	#只能在定义用户变量的时候赋初值；

#查询用户变量
select @attrName from dual;
#如
select @n from dual;

⽤户变量使⽤注意事项

因为⽤户变量相当于全局变量，可以在SQL指令以及多个存储过程中共享，在开发中建议尽量少使⽤⽤户变量，⽤户变量过多会导致程序不易理解、难以维护。

五、存储过程管理

1.查询存储过程

-- 查询所有的存储过程
show procedure status;

-- 根据数据库名，查询当前数据库中的存储过程
show procedure status where db='数据库名';

-- 查询存储过程的创建细节
show create procedure 数据库名.存储过程名;

#查看当前数据库中存储过程的相关信息
SHOW CREATE PROCEDURE  存储过程名;

2.修改存储过程

修改存储过程指的是修改存储过程的特征/特性

alter procedure <proc_name> 特征1 [特征2 特征3 ....];

存储过程的特征参数：

CONTAINS SQL 表示⼦程序包含 SQL 语句，但不包含读或写数据的语句
NO SQL 表示⼦程序中不包含 SQL 语句
READS SQL DATA 表示⼦程序中包含读数据的语句
MODIFIES SQL DATA 表示⼦程序中包含写数据的语句
SQL SECURITY { DEFINER |INVOKER } 指明谁有权限来执⾏
- DEFINER 表示只有定义者⾃⼰才能够执⾏
- INVOKER 表示调⽤者可以执⾏
COMMENT ‘string’ 表示注释信息

#示例
alter procedure proc_test1 READS SQL DATA;

3.删除存储过程

-- drop 删除数据库中的对象 数据库、数据表、列、存储过程、视图、触发器、索引....
-- delete 删除数据表中的数据

#删除存储过程语法
drop procedure 存储过程名;

六、存储过程中流程控制

1. 分⽀语句

-- 单分⽀：如果条件成⽴，则执⾏SQL
if conditions then
	-- SQL
end if;

#示例
create procedure proc_test7(IN a int)
begin
		-- 如果参数a的值为1，则添加⼀条班级信息
		if a=1 then
					insert into classes(class_name,remark) values('Java2109','test');
		end if;
end; 



-- 双分⽀：如果条件成⽴则执⾏SQL1，否则执⾏SQL2
if conditions then
		-- SQL1
else
		-- SQL2
end if;

#示例
create procedure proc_test7(IN a int)
begin
	-- 如果参数a的值为1，则添加⼀条班级信息；
	if a=1 then
		insert into classes(class_name,remark) values('Java2109','test');
	-- 否则添加⼀条学⽣信息
	else
		insert into students(stu_num,stu_name,stu_gender,stu_age,cid,remark) 	values('20210110','⼩花','⼥',19,1,'...');
	end if;
end;


-- 多分支
-- case
create procedure proc_test8(IN a int)
begin
	case a
		when 1 then
			-- SQL1 如果a的值为1 则执⾏SQL1
			insert into classes(class_name,remark) values('Java2110','wahaha');
		when 2 then
			-- SQL2 如果a的值为2 则执⾏SQL2
			insert into students(stu_num,stu_name,stu_gender,stu_age,cid,remark) values('20210111','⼩刚','男',21,2,'...');
		else
			-- SQL (如果变量的值和所有when的值都不匹配，则执⾏else中的这个SQL)
			update students set stu_age=18 where stu_num='20210110';
	end case;
end;

2.循环语句

-- while
create procedure proc_test9(IN num int)
begin
	declare i int;
	set i = 0;
	while i<num do
		-- SQL
		insert into classes(class_name,remark) values( CONCAT('Java',i) ,'....');
		set i = i+1;
	end while;
end;


-- repeat
create procedure proc_test10(IN num int)
begin
	declare i int;
	set i = 1;
	repeat
		-- SQL
		insert into classes(class_name,remark) values( CONCAT('Python',i) ,'....');
		set i = i+1;
	until i > num end repeat;
end;


-- loop
create procedure proc_test11(IN num int)
begin
	declare i int ;
	set i =0;
	myloop:loop    #myloop为循环体别名
		-- SQL
		insert into classes(class_name,remark) values( CONCAT('HTML',i) ,'....');
		set i = i+1;
		
		if i=num then   #判断结束条件
			leave myloop; #离开循环体
		end if;
		
	end loop;
end;

七、游标

1 游标的概念

游标可以⽤来依次取出查询结果集中的每⼀条数据——逐条读取查询结果集中的记录

2.游标的使用

#声明游标
DECLARE cursor_nanme CURSOR FOR select_statement;  
#cursor_nanme：游标别名；	select_statement：查询语句

#打开游标
open cursor_nanme;   #cursor_nanme：游标别名

#使⽤游标：提取游标当前指向的记录（提取之后，游标⾃动下移）;一般在循环体内遍历
FETCH cursor_nanme INTO 属性名1,属性名2,属性名3,...;

#关闭游标
CLOSE cursor_nanme;

3.案例

-- 游标使⽤案例
create procedure proc_test2(OUT result varchar(200))
begin
		declare bname varchar(20);
		declare bauthor varchar(20);
		declare bprice decimal(10,2);
		declare num int;
		declare i int;
		declare str varchar(50);
		-- 此查询语句执⾏之后返回的是⼀个结果集（多条记录），使⽤游标可以来遍历查询结果集
		declare mycursor cursor for select book_name,book_author,book_price from books;
		select count(1) INTO num from books;#查询共有多少条记录
		-- 打开游标
		open mycursor;
		-- 使⽤游标要结合循环语句
		set i=0;
		while i<num do
			-- 使⽤游标：提取游标当前指向的记录（提取之后，游标⾃动下移）
			FETCH mycursor INTO bname,bauthor,bprice;
			set i=i+1;
			-- set str=concat_ws('~',bname,bauthor,bprice); 或
			select concat_ws('~',bname,bauthor,bprice) INTO str;
			set result = concat_ws(',',result,str);
		end while;
		-- 关闭游标
		close mycursor;
end;

-- 案例测试
set @r = '';
call proc_test2(@r);
select @r from dual;

4.游标的遍历

#当fetch游标到了数据库表格最后一行的时候，设置done=1;
declare continue handler for sqlstate '02000' set done=1；

'02000'	主要代表的意思可以理解为:
发生下述异常之一时:
SELECT INTO语句或INSERT语句的子查询的结果为空表。
在搜索的UPDATE或DELETE语句内标识的行数为零。
在FETCH语句中引用的游标位置处于结果表最后一行之后。
就是说你定义了，当fetch游标到 了数据库表格最后一行的时候，设置done=1;


#游标遍历示例
#当fetch游标到了数据库表格最后一行的时候，设置done=1;
declare continue handler for sqlstate '02000' set done=1；
-- 打开游标
open mycursor;
-- 使⽤游标要结合循环语句
while (done!=1) do
		-- 使⽤游标：提取游标当前指向的记录（提取之后，游标⾃动下移）
		FETCH mycursor INTO bname,bauthor,bprice;
		-- set str=concat_ws('~',bname,bauthor,bprice); 或
		select concat_ws('~',bname,bauthor,bprice) INTO str;
		set result = concat_ws(',',result,str);
end while;
-- 关闭游标
close mycursor;