数据库学习笔记(十一)-----存储过程

最新推荐文章于 2024-06-17 15:00:11 发布

天真小孩，！

最新推荐文章于 2024-06-17 15:00:11 发布

阅读量486

点赞数

分类专栏：数据库-----笔记文章标签：数据库学习 mysql

本文链接：https://blog.csdn.net/qq_55078048/article/details/128473582

版权

数据库-----笔记专栏收录该内容

14 篇文章 6 订阅

订阅专栏

前言

MySQL存储过程学习笔记

一、存储过程

存储过程和函数是在数据库中定义的一些SQL语句的集合，然后直接调用这些存储过程和函数来执行已经定义好的SQL语句。存储过程和函数可以避免开发人员重复编写相同的SQL语句。而且，存储过程和函数是在MySQL服务器中存储和执行的，可以减少客户器端和服务端的数据传输。

1.创建存储过程

创建存储过程和函数是指将经常使用的一组SQL语句组合在一起，并将这些SQL语句当作一个整体存储在MySQL服务器中。存储程序可以分为存储过程和函数。在MySQL中创建存储过程使用的语句CREATE PROCEDURE。其语法形式如下：

CREATE PROCEDURE procedure_name([proc_param[,…]]) routine_body

在上述语句中，参数procedure_name表示所要创建的存储过程名字，参数proc_param表示存储过程的参数，参数routine_body表示存储过程的SQL语句代码，可以用BEGIN…END来标志SQL语句的开始和结束。
提示: 在具体创建存储过程时，存储过程名不能与已经存在的存储过程名重名，实战中推荐存储过程名命名为procedure_xxx或者proc_xxx。
proc_param中每个参数的语法形式如下：

[IN|OUT|INOUT] param_name type

在上述语句中，每个参数由三部分组成，分别为输入/输出类型、参数名和参数类型。其中，输入/输出类型有三种类型，分别为IN（表示输入类型）、OUT（表示输出类型）、INOUT（表示输入/输出类型）。param_name表示参数名；type表示参数类型，可以是MySQL软件所支持的任意一个数据类型。
举例：

#创建存储过程
create PROCEDURE  proc_delete_student (IN sid int )                                   
	BEGIN 
   	declare cid  int ;   #定义变量cid                            
   	Select class_id into cid from student where id = sid;    #通过查询语句设置变量                                                                       
   	delete from grade where id = sid;  #删除成绩表中的记录  
   	delete from student where id = sid;   #删除学生表中的记录                                                    
   	update class set count=count-1 where id = cid; #更新班级表中的记录   
  	END;                                                                        
   	$$                                                                           
   	DELIMITER ;
   	
call proc_delete_student(2);    #调用存储过程

2.在存储过程中使用变量

在存储过程和函数中，可以定义和使用变量。用户可以使用关键字DECLARE来定义变量，然后为变量赋值。这些变量的作用范围是在BEGIN…END程序段中。

1.定义变量

在MySQL中，可以使用DECLARE关键字来定义变量。定义变量的基本语法如下：

DECLARE var_name[,…] type [DEFAULT value]

其中，关键字DECLARE是用来声明变量的；参数var_name是变量的名称，可以同时定义多个变量；参数type用来指定变量的类型；DEFAULT value子句将变量默认值设置为value，没有使用DEFAULT子句时，默认值为NULL。
定义变量cid，数据类型为INT型，默认值为10，代码如下：

DECLARE cid INT DEFAULT 10;

2.为变量赋值

在MySQL中可以使用关键字SET来为变量赋值，SET语句的基本语法如下：

SET var_name=expr[,var_name=expr]…

其中，关键字SET用来为变量赋值；参数var_name是变量的名称；参数expr是赋值表达式。一个SET语句可以同时为多个变量赋值，各个变量的赋值语句之间用逗号隔开。
例如，将变量tmp_id赋值为88，代码如下：

SET tmp_id = 88;

在MySQL中，还可以使用SELECT…INTO语句为变量赋值。其基本语法如下：

SELECT col_name[,…] INTO var_name[,…] FROM table_name WHERE condition

其中，参数col_name表示查询的字段名称；参数var_name是变量的名称；参数table_name指表的名称；参数condition指查询条件。
举例：

use school; 
drop  PROCEDURE if exists query_student_class_info;	#如果存在存储过程query_student_class_info，则删除
DELIMITER $$ 

create procedure  query_student_class_info (IN sid int, OUT cname varchar(128), OUT ccount  int)                                   
	BEGIN   
  	declare tmp_name varchar(128);
	declare tmp_count int;
  	declare tmp_cid  int;
 	select class_id into tmp_cid from student where id = sid;	#通过传入的sid查询学生的class_id，并将class_id保存到tmp_cid         
  	select name, count into tmp_name, tmp_count from class where id = tmp_cid;	
  	set cname = tmp_name, ccount = tmp_count;	#给cname与ccount赋值（cname与ccount是传出的数据）
   	END;    
   	$$                                                                           
   	DELIMITER ;

call query_student_class_info(4, @name, @count);	#调用存储过程	name与count是用户变量，需要用‘@’
select @name, @count;

在这里插入图片描述

3.光标的使用

查询语句可能查询出多条记录，在存储过程和函数中使用光标来逐条读取查询结果集中的记录。有些书上将光标称为游标。光标的使用包括声明光标、打开光标、使用光标和关闭光标。光标必须声明在处理程序之前，并且声明在变量和条件之后。

1.声明光标

在MySQL中，可以使用DECLARE关键字来声明光标，其基本语法如下：

DECLARE cursor_name CURSOR FOR select_statement;

其中，参数cursor_name表示光标的名称；参数select_statement表示SELECT语句的内容。

2.打开光标

在MySQL中，使用关键字OPEN来打开光标，其基本语法如下：

OPEN cursor_name;

其中，参数cursor_name表示光标的名称。
下面代码打开一个名为cur_student的光标，代码如下：

OPEN cur_student;

3.使用光标

在MySQL中，使用关键字FETCH来使用光标，其基本语法如下：

FETCH cursor_name INTO var_name[,var_name…];

其中，参数cursor_name表示光标的名称；参数var_name表示将光标中的SELECT语句查询出来的信息存入该参数中。var_name必须在声明光标之前就定义好。
举例：

delimiter $$
create procedure query_student (IN sid int, OUT cname varchar(128), OUT cid int)
	BEGIN                                                                             
  	declare tmp_name varchar(128);    #必须定义在声明光标之前                                                                                             
   	declare tmp_cid  int;                                                           
   	declare  done int default 0;                                                                                                                                                
 	declare cur_student CURSOR FOR SELECT name, class_id FROM  student where id = sid;                                                                                      
   	declare continue handler for not found set done = 1; #将结束标志绑定到游标上                                                                             
   	open  cur_student;                                                             
   	select done;                                                                    
  	fetch cur_student into tmp_name, tmp_cid;                                        
  	select done;                                                                                      
  	select tmp_name, tmp_cid;         #打印从光标中获取到的值                                                
   	close cur_student;                                                              
   	set cname = tmp_name, cid = tmp_cid;                                                                                                                      
   	END;
	$$
	delimiter ;

call query_student(3,@a,@b);

在这里插入图片描述

4.关闭光标

在MySQL中，使用关键字CLOSE来关闭光标，其基本语法如下：

CLOSE cursor_name;

其中，参数cursor_name表示光标的名称。
例如: 关闭一个名为cur_student的光标，代码如下：

CLOSE cur_student;

在上面的示例中，关闭了这个名称为cur_student的光标。关闭了之后就不能使用FETCH来使用光标了。
提示:如果存储过程或函数中执行了SELECT语句，并且SELECT语句会查询出多条记录，这种情况最好使用光标来逐条读取记录，光标必须在处理程序之前且在变量和条件之后声明，而且光标使用完毕后一定要关闭。

4.流程控制的使用

在存储过程和函数中，可以使用流程控制来控制语句的执行。在MySQL中，可以使用IF语句、CASE语句、LOOP语句、LEAVE语句、ITERATE语句、REPEAT语句和WHILE语句来进行流程控制。

1. IF语句

IF语句用来进行条件判断。根据条件执行不同的语句。其语法的基本形式如下：

IF search_condition THEN statement_list       
[ELSEIF search_condition THEN statement_list] ...
[ELSE statement_list]                           
END  IF

参数search_condition表示条件判断语句；参数statement_list表示不同条件的执行语句。
举例:

IF age>20 THEN SET @count1=@count1+1;  
ELSEIF age=20 THEN @count2=@count2+1;
ELSE @count3=@count3+1;                
END IF;

该示例根据age与20的大小关系来执行不同的SET语句。如果age值大于20，将count1的值加1；如果age值等于20，就将count2的值加1；其他情况将count3的值加1。IF语句都需要使用END IF来结束。

2.CASE语句

CASE语句可实现比IF语句更复杂的条件判断，其语法的基本形式如下：

CASE case_value                                  
WHEN when_value THEN statement_list            
[ WHEN when_value THEN statement_list ]          
[ELSE statement_list]                               
END CASE

其中，参数case_value表示条件判断的变量；参数when_value表示变量的取值；参数statement_list表示不同when_value值的执行语句。
举例：

CASE level                                    
      WHEN 20 THEN SET attack = attack + 5;  
      WHEN 30 THEN SET attack = attack + 10; 
      WHEN 40 THEN SET attack = attack + 15; 
      ELSE SET attack = attack + 1; 
END CASE

当级别level值为20时，attack值加5；当级别level值为30时，attack值加10；当级别level值为40时，attack值加15；否则，attack + 1。CASE语句使用END CASE结束。

3.LOOP语句

LOOP语句可以使某些特定的语句重复执行，实现一个简单的循环。LOOP语句本身没有停止循环，只有遇到LEAVE语句等才能停止循环。LOOP语句的语句形式如下：

   [begin_label:] LOOP             
   statement_list                  
   END LOOP [end_label]

其中，参数begin_label和参数end_label分别表示循环开始和结束的标志，这两个标志必须相同，而且都可以省略；参数statement_list表示需要循坏执行的语句。
举例：

add_num:LOOP                 
     SET @count = @count + 1; 
END LOOP add_num;

该示例循环执行count加1的操作。因为没有跳出循环的语句，这个循环成了一个死循环。LOOP循环都以END LOOP结束。

4.LEAVE语句

LEAVE语句主要用于跳出循环控制，其语法形式如下：

LEAVE label

其中，参数label表示循环的标志。
举例：

add_num: LOOP             
SET @count=@count + 1;
Select @count;
IF @count = 100 THEN 
    LEAVE add_num;     
END IF;
END LOOP add_num;

该示例循环执行count值加1的操作。当count的值等于100时，LEAVE语句跳出循环。

5.ITERATE语句

ITERATE语句也是用来跳出循环的语句，但是ITERATE语句是跳出本次循环，然后直接进入下一次循环，ITERATE语句的语法形式如下：

ITERATE label

其中，参数label表示循环的标志。
举例：

add_num1:LOOP              
    Set @count = @count +1 
    IF @count=100 THEN     
        LEAVE add_num1       
    ELSE IF MOD(@count, 3) = 0 then
        ITERATE add_num1;   
     Select * from student;    
END　LOOP add_num1;

该示例循环执行count加1的操作，count的值为100时结束循环。如果count的值能够整除3，就跳出本次循环，不再执行下面的SELECT语句。

注意： LEAVE语句和ITERATE语句都用来跳出循环语句，但是两者的功能是不一样的。LEAVE语句是跳出整个循环，然后执行循环后面的程序，和C++ break 相似。ITERATE语句是跳出本次循环，然后进入下一次循环,和C++ continue 相似。使用这两个语句时一定要区分清楚。

6.REPEAT语句

REPEAT语句是有条件控制的循环语句。当满足特定条件时，就会跳出循环语句。REPEAT语句的基本语法形式如下：

[begin_label:] REPEAT        
        statement_list;       
     UNTIL search_condition 
END REPEAT [end_label]

其中，参数statement_list表示循环的执行语句；参数search_condition表示结束循环的条件，满足该条件时循环结束。
举例：

REPEAT                        
     SET @count=@count+1;   
     UNTIL @count=100        
END REPEAT;

该示例循环执行count加1的操作，count值为100时结束循环。REPEAT循环都用END REPEAT结束。

7.WHILE语句

WHILE语句也是有条件控制的循环语句，但WHILE语句和REPEAT语句是不一样的。WHILE语句是当满足条件时执行循环内的语句。WHILE语句的基本语法形式如下：

[begin_label:] WHILE search_condition DO   
        Statement_list                      
END WHILE [end_label]

其中，参数statement_condition表示循环执行的条件，满足该条件时循环执行；参数statement_list表示循环的执行语句。
举例：

WHILE @count<100 DO       
    SET @count = @count + 1;
END WHILE;

5.流程控制综合运用

循环访问光标操作，访问光标中的所有记录,代码如下：

delimiter $$
create procedure query_all_students (IN sid int, OUT cname varchar(128), OUT cid int)
	BEGIN                                                                             
   	declare tmp_name varchar(128);    #必须定义在声明光标之前                                                                                             
	declare tmp_cid  int;                                                           
   	declare  done int default 0;                                                                                                                                                
  	declare cur_student CURSOR FOR SELECT name, class_id FROM  student ;                                                                                      
   	declare continue handler for not found set done = 1; #将结束标志绑定到游标上                                                                             
  	open  cur_student;                                                             
  	read_loop:LOOP      #循环读取                                                                   
   	fetch cur_student into tmp_name, tmp_cid;                                                                                                                              
   	IF done=1 then                                                              
   	Leave read_loop;                                                                  
  	END IF;                                                                     
  	select tmp_name, tmp_cid;         #打印从光标中获取到的值                                                                                       
 	END LOOP read_loop;                                                                                
 	close cur_student;                                                              
  	set cname = tmp_name, cid = tmp_cid;                                                                                                                      
	END;
	$$
	delimiter ;

call query_all_students(1,@name,@id);

在这里插入图片描述
在学生表中插入一条记录，并返回记录的自增长id

delimiter $$
create procedure fetch_insert_student_id (IN p_name varchar(128), in p_class_id int, IN p_sex char(1), OUT rid int)
	BEGIN                                                                             
  	Insert into student (name, class_id, sex) values(p_name, p_class_id, p_sex);                                                                                
  	select last_insert_id() as rid;                                                                                                                     
   	END;
   	$$
delimiter ;

6.查看存储过程

存储过程创建以后，用户可以通过SHOW STATUS语句来查看存储过程的状态，也可以通过SHOW CREATE语句来查看存储过程的定义。用户也可以通过查询information_schema数据库下的Routines表来查看存储过程的信息。本节将详细讲解查看存储过程的状态与定义的方法。

1.SHOW STATUS语句查看存储过程

在MySQL中，可以通过SHOW STATUS语句。其基本语法形式如下：

 SHOW PROCEDURE STATUS  [ like ‘pattern’ ] ;

其中，参数PROCEDURE表示查询存储过程；参数LIKE 'pattern’用来匹配存储过程的名称。
举例：

show procedure status like '%student%';

在这里插入图片描述
执行结果显示了存储过程的创建时间、修改时间和字符集等信息。

2.使用SHOW CREATE语句查看存储过程的定义

在MySQL中，可以通过SHOW CREATE语句查看存储过程的状态，语法形式如下：

SHOW CREATE PROCEDURE proc_name

其中，参数PROCEDURE表示查询存储过程；参数proc_name表示存储过程的名称。
举例：
查询名为proc_delete_student的存储过程的状态，代码如下：

 show create procedure proc_delete_student \G

在这里插入图片描述

3.从information_schema.Routine表中查看存储过程的信息

存储过程和函数的信息存储在information_schema数据库下的Routines表中。可以通过查询该表的记录来查询存储过程和函数的信息。其基本语法形式如下：

SELECT * FROM information_schema.Routines Where ROUTINE_NAME = ‘proc_name’;

其中，字段ROUTINE_NAME是Routines 存储存储过程和函数的列名称；参数proc_name表示存储过程或函数的名称。
举例：
下面从Routines表中查询名为proc_delete_student的存储过程信息，具体SQL代码如下：

select routine_definition from information_schema.Routines where routine_name='proc_delete_student';

在这里插入图片描述

4.存储过程的删除

在MySQL中删除存储过程通过SQL语句DROP完成：

DROP PROCEDURE proc_name;

在上述语句中，关键字DROP PROCEDURE用来表示实现删除存储过程，参数proc_name表示所要删除的存储过程名称。
举例：

drop procedure proc_delete_student;

总结

以上便是个人对MySQL存储过程的学习理解，此博客为学习笔记，仅供参考。如有误处，还请大佬指正！

天真小孩，！

关注

0
点赞
踩
5

收藏

觉得还不错? 一键收藏
0
评论
复制链接

分享到 QQ

分享到新浪微博

扫一扫

专栏目录

数据库学习笔记(十一)-----存储过程

文章目录

前言

一、存储过程

1.创建存储过程

2.在存储过程中使用变量

1.定义变量

2.为变量赋值

3.光标的使用

1.声明光标

2.打开光标

3.使用光标

4.关闭光标

4.流程控制的使用

1. IF语句

2.CASE语句

3.LOOP语句

4.LEAVE语句

5.ITERATE语句

6.REPEAT语句

7.WHILE语句

5.流程控制综合运用

6.查看存储过程

1.SHOW STATUS语句查看存储过程

2.使用SHOW CREATE语句查看存储过程的定义

3.从information_schema.Routine表中查看存储过程的信息

4.存储过程的删除

总结