数据库存储过程（全网最全）

81 阅读 0 评论 54 点赞

我是靠谱客的博主甜蜜跳跳糖，最近开发中收集的这篇文章主要介绍数据库存储过程（全网最全），觉得挺不错的，现在分享给大家，希望可以做个参考。

概述

一、存储过程的概念

存储过程是定义在服务器上的一段子程序代码，存储过程时数据库对象之一。

存储过程在服务器端运行，需要时调用，执行速度快，方便使用
确保数据库的安全，存储过程可以完成所有的数据库操作
降低网络负载，客户端不必提交sql语句
可以接受用户参数，也可以返回参数

二、存储过程类型

系统存储过程【名字以sp_为前缀，存储在master库中】
本地存储过程【存储在用户定义的数据库中】
扩展存储过程【名字都已xp_为前缀，储存在master库中】
临时存储过程【名字以#开头的】

三、创建并调用存储过程

创建一个存储过程的语句：

delimiter $$ //这是定义一个结束符$$
create procedure [存储过程名称]([参数])
begin
......
end$$
delimiter ; //重新定义结束符为 ;

创建一个统计课程数量的存储过程：

create procedure count_course()
begin
select count(*) from course;
end$$

delimiter 重新定义结束符号

调用count_course存储过程

call count_course$$

在这里插入图片描述

四、存储过程的变量

1、变量定义

使用declare声明变量

declare number1 int default 20;

一句declare只声明一个变量
作用域在begin…end范围中
变量具有与sql语句相同的数据类型和长度，还可以指定默认值与字符集和排序规则
变量使用set赋值，也可以使用select into赋值

创建test（）存储过程函数显示男和女的人数：

create procedure test()
begin
declare boys int(10);
declare girls int(10);
select count(*) into boys from student where Ssex='男';
select count(*) into girls from student where Ssex='女';
select boys,girls;
end$$

在这里插入图片描述

在无参数的情况下返回变量值，可以用select语句。

2、变量的作用域

一个函数可以有多个begin…end块，一个块里还可以嵌套多个begin…end块
在函数父作用块中定义的变量对所有子块可用
在单个begin…end块中，变量是局部变量，不能跨兄弟块使用
函数传入参数属于全局变量，可以在所有块中使用

创建一个显示成年人和未成年人数量的表以及最大年龄和最小年龄的存储过程函数：

delimiter $$
create procedure age_count()
begin
	begin
		declare adult int;
		declare minor int;
		select count(*) into adult from student where Sage>=18;
		select count(*) into minor from student where Sage<18;
		select adult,minor;
	end;
	begin
		declare age_max int;
		declare age_min int;
		select max(Sage) into age_max from student;
		select min(Sage) into age_min from student;
		select age_max,age_min;
	end;
end$$
delimiter ;

在每个嵌套块的结尾end要加上 ; sql结尾符

在这里插入图片描述
把变量提到父begin块后，变量可以在两个块之间交换使用

drop procedure age_count;
delimiter $$
create procedure age_count()
begin
	declare adult int;
	declare minor int;
	declare age_max int;
	declare age_min int;
	begin
		select count(*) into adult from student where Sage>=18;
		select count(*) into minor from student where Sage<18;
		select adult,age_min;
	end;
	begin
		select max(Sage) into age_max from student;
		select min(Sage) into age_min from student;
		select age_max,minor;
	end;
end$$
delimiter ;

在这里插入图片描述

五、存储过程参数

前面提到的函数都是没带参数的，只使用select返回结果集
函数可以带的参数分为：传入参数in 传出参数out 传入传出参数inout；

函数不指定参数类型的情况下，传进来的参数默认是in类型

drop procedure findname;
delimiter $$
create procedure findname(sno int)
begin
	declare name varchar(10);
	select Sname into name from student where Sno=sno limit 1;
	select name;
end$$
delimiter ;

在这里插入图片描述

使用 select into 语句赋值的时候要确保该语句只返回一条结果，或者加上 limit 1 来限制返回结果的行数
SQL变量名不能和列名一样

使用 out 类型的参数输出，结果应该与上题一致

drop procedure findname;
delimiter $$
create procedure findname(in sno int,out sname varchar(10))
begin
	select Sname into sname from student where Sno=sno limit 1;
end$$
delimiter ;

set @name='';
call findname(2,@name);
select @name as studentname;

在这里插入图片描述

这里出现了点问题没能出来暂时不深究，先留着以后再填坑

六、定义条件和定义处理的程序

定义条件：
事先定义好程序执行过程中可能遇到的问题
处理程序：
对已经定义好的问题作出相应处理，并保证存储函数在遇到警告或错误时能继续执行，避免程序异常停止工作

定义条件

declare [condition_name] condition for [错误码/错误值];
declare command_not_allowed condition for sqlstate '42000';//错误值
declare command_not_allowed condition for 42000;//错误码

处理程序

declare [handler_type] handler for [condition_name]
......

handler_type 错误处理方式
mysql提供了三个值

continue //不处理错误，存储函数继续往下执行
exit //遇到错误立即退出
undo //遇到错误撤销之前操作

condition_name 错误类型
condition_name 可以自定义错误类型，mysql也有自带的错误类型：

sqlstate_value：包含5个字符的字符串错误值；
condition_name：表示declare condition定义的错误条件名称；
SQLWARNING：匹配所有以01开头的sqlstate错误代码；
NOT FOUND：匹配所有以02开头的sqlstate错误代码；
SQLEXCEPTION：匹配所有未被SQLWARNING或NOT FOUND捕获的sqlstate错误代码；

七、流程控制

运算符

在这里插入图片描述
优先顺序

条件语句

（1）if语句

基本结构：

单条件语句

begin
if(...)
then
	......
else
	......
	end if;
end$$

多条件语句

begin
if(...)
then
	......
elseif(...)
then
	......
else 
	......
end if;

end$$

if 语句需要有 end if 来结束 if 语句

判断Cno是否有空值

delimiter $$
CREATE PROCEDURE ifnull()
begin
	declare flag int;
	select count(*) into flag from student where Cno is null;
	if flag is null
		then select '没有空值' as '是否有空值';
		else select '仍有空值' as '是否有空值';
	end if;
end
delimiter ;

在这里插入图片描述

（2）case语句

case语句可以计算多个条件式，并将其中一个符合条件的结果报答是返回

case [测试表达式]
when [测试值1] then [结果表达式1]
when [测试值2] then [结果表达式2]
when [测试值3] then [结果表达式3]
......
else [结果表达式0]
end


DROP PROCEDURE IF EXISTS testCase;
DELIMITER //
CREATE PROCEDURE testCase(OUT result VARCHAR(255))
BEGIN
     DECLARE val VARCHAR(255);
     SET val = 'a';
     CASE val IS NULL
         WHEN 1 THEN SET result = 'val is true';
         WHEN 0 THEN SET result = 'val is false';
         ELSE SELECT 'else';
     END CASE;
END //
DELIMITER ;
set @result='';
CALL testCase(@result);

本例子原文链接：https://blog.csdn.net/yanluandai1985/article/details/83689524

根据输入的课程名，添加一行课程类别

create procedure course_cate(cid varchar(10))
begin
update course set cate=
case(select cname from course where Cid=cid)
when '语文' then '文科'
when '数学' then '理科'
else 'x'
end case
end

循环语句

（3）while语句

基本结构：

begin
	while([执行条件]) do
	......
	end while;
end;

新建一个Sscore列：

alter table student add Sscore int;

随机从1~100分插入成绩，输入参数i 作为需要修改成绩的人数，使用while循环一行行修改成绩

drop procedure add_math_score();
delimiter $$
create procedure add_math_score(i int)
begin
	declare n int default 0;
	declare score int default 0;
	while(n<i) do
	begin
		set n=n+1;
		set score=floor(100*rand());
		select score ;
		update student set Sscore=score where Sno=n;
	end;
	end while;
end$$
delimiter ;

结果：
在这里插入图片描述

修改前12人的成绩为随机数

（4）repeat语句

基本结构：

begin
	repeat
	......
	until [跳出条件]
	end repeat;
end;

参照while的例子，结果相同

drop procedure add_math_score();
delimiter $$
create procedure add_math_score(i int)
begin
	declare n int default 0;
	declare score int default 0;
	repeat
	begin
		set n=n+1;
		set score=floor(100*rand());
		select score ;
		update student set Sscore=score where Sno=n;
	end;
	until n>=15
	end repeat;
end$$
delimiter ;

repeat 和 while 的区别在于两点，一是条件写的位置，while是在循环块的开头写循环条件，repeat是在结尾处写。二是条件语句，while的条件语句是为真则执行，repeat是条件语句为真时跳出。
repeat 的 until 哪一行不加分号 ;

（5）loop语句

基本结构：

begin
	[循环名称]:loop
		......
		if [跳出条件] then leave [循环条件];
		end if;
	end loop [循环条件];
end

批量添加student表数据：

drop procedure add_data();
delimiter $$
create procedure add_data(i int)
begin
declare flag int default 0;
add_loop:loop
set flag=flag+1;
if flag>i then leave add_loop;
end if;
insert into student(Sname,Sno,Cno,Ssex,Sage,Sscore) values('批量人',flag+15,1002,'男',22,100);
end loop add_loop;
end
delimiter ;

结果
在这里插入图片描述

八、游标 Cursor

游标实际上是一种能从包括多条数据记录的结果集中每次提取一条记录的机制。游标充当指针的作用。尽管游标能遍历结果中的所有行，但他一次只指向一行。游标的作用就是用于对查询数据库所返回的记录进行遍历，以便进行相应的操作。

游标时保存查询结果的临时区域。
游标是对行集进行逐行遍历操作，循环则是重复某一组操作
使用游标时需要有的操作

定义

declare stopflag int default 0;
declare [游标名] cursor for [sql查询语句];
declare continue handler for not found set stopflag=1;//使用越界标志控制循环

打开

open [游标名];

取值

fetch [游标名] into [变量名];

关闭

close [游标名]

把student表中Sno为偶数的学生改名

create procedure change_cursor()
begin
declare student_name varchar(10);
declare stopflag int default 0;
declare name_cur cursor for select Sname from student where Sno%2=0;
declare continue handler for not found set stopflag=1;
open name_cur;
fetch name_cur into student_name;
while(stopflag=0) do
begin
update student set Sname=concat(student_name,'雨课堂') where Sname=student_name;
fetch name_cur into student_name;
end;
end while;
close name_cur;
end;

游标select的字段数需要与fetch into的变量数一致

结果：
在这里插入图片描述
关于游标的用法日后会单独开一篇详解，这里不做描述
关于mysql存储过程更多的补充可以在下面链接里找到：
https://blog.csdn.net/yanluandai1985/article/details/83715441
下面是本次实验用到的数据库：
](https://img-blog.csdnimg.cn/20200216224755872.png?x-oss-process=image/watermark,type_ZmFuZ3poZW5naGVpdGk,shadow_10,text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl8zOTU4OTAzMw==,size_16,color_FFFFFF,t_70)