存储过程。

于 2022-12-03 14:21:44 发布
阅读量436 收藏
点赞数
分类专栏： mysql 文章标签： 数据库
版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
本文链接：https://blog.csdn.net/m0_50546929/article/details/128083617
版权

        存储过程是事先经过编译并存储在数据库中的一段 SQL 语句的集合，调用存储过程可以简化应用开发人员的很多工作，减少数据在数据库和应用服务器之间的传输，对于提高数据处理的效率是有好处的。存储过程思想上很简单，就是数据库 SQL 语言层面的代码封装与重用。

特点

封装，复用 -----------------------> 可以把某一业务SQL封装在存储过程中，需要用到

的时候直接调用即可。

可以接收参数，也可以返回数据 --------> 再存储过程中，可以传递参数，也可以接收返回值。

减少网络交互，效率提升 -------------> 如果涉及到多条SQL，每执行一次都是一次网络传输。 而如果封装在存储过程中，我们只需要网络交互一次可能就可以了。

存储过程基本语法

--创建
create procedure p1()
begin
    select count(*) from student;
end;

--调用
call p1();

--查看
-- 查询指定数据库的存储过程及状态信息
SELECT * FROM INFORMATION_SCHEMA.ROUTINES WHERE ROUTINE_SCHEMA = 'xxx'; 
-- 查询某个存储过程的定义
SHOW CREATE procedure 存储过程名称 ; 

--删除
DROP procedure [ IF EXISTS ] 存储过程名称 ；

变量

        系统变量： 系统变量 是MySQL服务器提供，不是用户定义的，属于服务器层面。分为全局变量（GLOBAL）、会话 变量（SESSION）。

如果没有指定 SESSION/GLOBAL ，默认是 SESSION ，会话变量。
A. 全局变量 (GLOBAL): 全局变量针对于所有的会话。
B. 会话变量 (SESSION): 会话变量针对于单个会话，在另外一个会话窗口就不生效了。 
--查看系统变量
-- 查看所有系统变量
SHOW [ SESSION | GLOBAL ] VARIABLES ; 
-- 可以通过LIKE模糊匹配方式查找变量
SHOW [ SESSION | GLOBAL ] VARIABLES LIKE '......'; 
-- 查看指定变量的值
SELECT @@[SESSION | GLOBAL] 系统变量名; 

--设置系统变量
--0关闭，1开启
SET [ SESSION | GLOBAL ] 系统变量名 = 值 ;
SET @@[SESSION | GLOBAL] 系统变量名 = 值 ;

        用户定义变量： 是用户根据需要自己定义的变量，用户变量不用提前声明，在用的时候直接用 "@变量名" 使用就可以。其作用域为当前连接。用户定义的变量无需对其进行声明或初始化，只不过获取到的值为NULL。 

--赋值
--方式一
SET @var_name = expr [, @var_name = expr] ... ;
SET @var_name := expr [, @var_name := expr] ... ;
--方式二
SELECT @var_name := expr [, @var_name := expr] ... ;
SELECT 字段名 INTO @var_name FROM 表名;


--使用
SELECT @var_name ;

        局部变量：是根据需要定义的在局部生效的变量，访问之前，需要DECLARE声明。可用作存储过程内的局部变量和输入参数，局部变量的范围是在其内声明的BEGIN ... END块。 

--声明
DECLARE 变量名 变量类型 [DEFAULT ... ] ;

--赋值
SET 变量名 = 值 ;
SET 变量名 := 值 ;
SELECT 字段名 INTO 变量名 FROM 表名 ... ;

        用在存储过程中的if判断

create procedure p3()

begin
declare score int default 58;
declare result varchar(10);
if score >= 85 then
set result := '优秀';
elseif score >= 60 then
set result := '及格';
else
set result := '不及格';
end if;
select result;
end;

call p3();

        存储过程中的参数的类型，主要分为以下三种：IN、OUT、INOUT。 具体的含义如下：

         语法

CREATE PROCEDURE 存储过程名称 ([ IN/OUT/INOUT 参数名 参数类型 ])
BEGIN
-- SQL语句
END ;

        例1

create procedure p4(in score int, out result varchar(10))

begin
if score >= 85 then
set result := '优秀';
elseif score >= 60 then
set result := '及格';
else
set result := '不及格';
end if;
end;
-- 定义用户变量 @result来接收返回的数据, 用户变量可以不用声明

call p4(18, @result);

select @result;

        例2

create procedure p5(inout score double)
begin
set score := score * 0.5;
end;

set @score = 198;

call p5(@score);

select @score;

        存储过程中的case

-- 含义： 当case_value的值为 when_value1时，
--执行statement_list1，当值为 when_value2时，
--执行statement_list2， 否则就执行 statement_list

CASE case_value
WHEN when_value1 THEN statement_list1
[ WHEN when_value2 THEN statement_list2] ...
[ ELSE statement_list ]
END CASE;


-- 含义： 当条件search_condition1成立时，执行statement_list1，
--当条件search_condition2成立时，
--执行statement_list2， 否则就执行 statement_list
CASE
WHEN search_condition1 THEN statement_list1
[WHEN search_condition2 THEN statement_list2] ...
[ELSE statement_list]
END CASE;

        例1

create procedure p6(in month int)

begin
declare result varchar(10);

case
when month >= 1 and month <= 3 then
set result := '第一季度';
when month >= 4 and month <= 6 then
set result := '第二季度';
when month >= 7 and month <= 9 then
set result := '第三季度';
when month >= 10 and month <= 12 then
set result := '第四季度';
else
set result := '非法参数';
end case ;

select concat('您输入的月份为: ',month, ', 所属的季度为: ',result);
end;

call p6(16);

        存储过程中的while

while 循环是有条件的循环控制语句。满足条件后，再执行循环体中的 SQL 语句。 
-- 先判定条件，如果条件为true，则执行逻辑，否则，不执行逻辑
WHILE 条件 DO
SQL逻辑...
END WHILE;

        例1

-- A. 定义局部变量, 记录累加之后的值;
-- B. 每循环一次, 就会对n进行减1 , 如果n减到0, 则退出循环
create procedure p7(in n int)

begin
declare total int default 0;
while n>0 do
set total := total + n;
set n := n - 1;
end while;
select total;
end;

call p7(100);

        存储过程中的repert

repeat 是有条件的循环控制语句 , 当满足 until 声明的条件的时候，则退出循环 
-- 先执行一次逻辑，然后判定UNTIL条件是否满足，如果满足，则退出。如果不满足，则继续下一次循环

REPEAT
SQL逻辑...
UNTIL 条件
END REPEAT;

        例1

-- A. 定义局部变量, 记录累加之后的值;
-- B. 每循环一次, 就会对n进行-1 , 如果n减到0, 则退出循环
create procedure p8(in n int)

begin
declare total int default 0;
repeat
set total := total + n;
set n := n - 1;
until n <= 0
end repeat;
select total;
end;

call p8(10);
call p8(100);

        存储过程中的loop

LOOP 实现简单的循环，如果不在 SQL 逻辑中增加退出循环的条件，可以用其来实现简单的死循环。
LOOP 可以配合一下两个语句使用：
        LEAVE ：配合循环使用，退出循环。
        ITERATE：必须用在循环中，作用是跳过当前循环剩下的语句，直接进入下一次循环。 
[begin_label:] LOOP
--SQL逻辑...
END LOOP [end_label];

LEAVE label; -- 退出指定标记的循环体
ITERATE label; -- 直接进入下一次循环

        例1

-- A. 定义局部变量, 记录累加之后的值;
-- B. 每循环一次, 就会对n进行-1 , 如果n减到0, 则退出循环 ----> leave xx
create procedure p9(in n int)
begin

declare total int default 0;
sum:loop
if n<=0 then
leave sum;
end if;
set total := total + n;
set n := n - 1;
end loop sum;
select total;

end;
call p9(100);

        例2

-- A. 定义局部变量, 记录累加之后的值;
-- B. 每循环一次, 就会对n进行-1 , 如果n减到0, 则退出循环 ----> leave xx
-- C. 如果当次累加的数据是奇数, 则直接进入下一次循环. --------> iterate xx
create procedure p10(in n int)
begin

declare total int default 0;

sum:loop

if n<=0 then
leave sum;
end if;

if n%2 = 1 then
set n := n - 1;
iterate sum;
end if;

set total := total + n;
set n := n - 1;
end loop sum;

select total;

end;
call p10(100);

游标

        游标（CURSOR）是用来存储查询结果集的数据类型 , 在存储过程和函数中可以使用游标对结果集进行循环的处理。游标的使用包括游标的声明、OPEN、FETCH 和 CLOSE。

        在存储过程中用一般的变量无法接收特定的查询结果集，使用游标解决这一问题。

语法

--声明游标
DECLARE 游标名称 CURSOR FOR 查询语句 ;

--打开游标
OPEN 游标名称 ;

--获取游标记录
FETCH 游标名称 INTO 变量 [, 变量 ] ;

--关闭记录
CLOSE 游标名称 ;

                例1

-- 逻辑:
-- A. 声明游标, 存储查询结果集
-- B. 准备: 创建表结构
-- C. 开启游标
-- D. 获取游标中的记录
-- E. 插入数据到新表中
-- F. 关闭游标

create procedure p11(in uage int)
begin

declare uname varchar(100);
declare upro varchar(100);
declare u_cursor cursor for 
select name,profession from tb_user where age <=uage;

drop table if exists tb_user_pro;
create table if not exists tb_user_pro(
id int primary key auto_increment,
name varchar(100),
profession varchar(100)
);

open u_cursor;

while true do
fetch u_cursor into uname,upro; --将游标中的记录赋值给自定义的变量
insert into tb_user_pro values (null, uname, upro);
end while;

close u_cursor;

end;
call p11(30);
上述的存储过程，最终我们在调用的过程中，会报错，之所以报错是因为上面的 while循环中，并没有退出条件。当游标的数据集获取完毕之后，再次获取数据，就会报错，从而终止了程序的执行。
但是此时，tb_user_pro 表结构及其数据都已经插入成功了，我们可以直接刷新表结构，检查表结构 中的数据。
上述的功能，虽然我们实现了，但是逻辑并不完善，而且程序执行完毕，获取不到数据，数据库还报错。要想解决这个问题，就需要通过MySQL 中提供的 条件处理程序 Han dler 。 

 

条件处理程序

条件处理程序（Handler）可以用来定义在流程控制结构执行过程中遇到问题时相应的处理步骤。

语法

DECLARE handler_action HANDLER FOR condition_value [, condition_value]
... statement ;

--handler_action 的取值：CONTINUE EXIT
    --CONTINUE: 继续执行当前程序
    --EXIT: 终止执行当前程序
--condition_value 的取值：
    --SQLSTATE sqlstate_value: 状态码，如 02000
    --SQLWARNING: 所有以01开头的SQLSTATE代码的简写
    --NOT FOUND: 所有以02开头的SQLSTATE代码的简写
    --SQLEXCEPTION: 所有没有被SQLWARNING 或 NOT FOUND捕获的SQLSTATE代码的简写

        例1 

-- 逻辑:
-- A. 声明游标, 存储查询结果集
-- B. 准备: 创建表结构
-- C. 开启游标
-- D. 获取游标中的记录
-- E. 插入数据到新表中
-- F. 关闭游标
create procedure p11(in uage int)
begin
declare uname varchar(100);
declare upro varchar(100);
declare u_cursor cursor for select name,profession from tb_user where age <=
uage;
-- 声明条件处理程序 ： 当SQL语句执行抛出的状态码为02000时，将关闭游标u_cursor，并退出
declare exit handler for SQLSTATE '02000' close u_cursor;
drop table if exists tb_user_pro;
create table if not exists tb_user_pro(
id int primary key auto_increment,
name varchar(100),
profession varchar(100)
);
open u_cursor;
while true do
fetch u_cursor into uname,upro;
insert into tb_user_pro values (null, uname, upro);
end while;
close u_cursor;
end;
call p11(30);

        例2

create procedure p12(in uage int)
begin
declare uname varchar(100);
declare upro varchar(100);
declare u_cursor cursor for select name,profession from tb_user where age <=
uage;
-- 声明条件处理程序 ： 当SQL语句执行抛出的状态码为02开头时，将关闭游标u_cursor，并退出
declare exit handler for not found close u_cursor;
drop table if exists tb_user_pro;
create table if not exists tb_user_pro(
id int primary key auto_increment,
name varchar(100),
profession varchar(100)
);
open u_cursor;
while true do
fetch u_cursor into uname,upro;
insert into tb_user_pro values (null, uname, upro);
end while;
close u_cursor;
end;
call p12(30);

存储函数

存储函数是有返回值的存储过程，存储函数的参数只能是IN类型的，默认为in。

语法

CREATE FUNCTION 存储函数名称 ([ 参数列表 ])
RETURNS type [characteristic ...]
BEGIN
-- SQL语句
RETURN ...;
END ;
characteristic 说明：
DETERMINISTIC ：相同的输入参数总是产生相同的结果
NO SQL ：不包含 SQL 语句。
READS SQL DATA ：包含读取数据的语句，但不包含写入数据的语句。

        例1

create function fun1(n int)
returns int deterministic
begin
declare total int default 0;
while n>0 do
set total := total + n;
set n := n - 1;
end while;
return total;
end;

select fun1(50);

 

midrodong
关注
  • 0
    点赞
  • 踩
  • 0
    收藏
    觉得还不错? 一键收藏
  • 0
    评论
专栏目录
mysql exit handler_declare handler 声明异常处理的语法
weixin_33054119的博客
01-27 1611
声明异常处理的语法DECLARE{EXIT | CONTINUE}HANDLER FOR{error-number | SQLSTATE error-string | condition}SQL statement上述定义包括：Handler Type (CONTINUE,EXIT)//处理类型 继续或退出Handler condition (SQLSTATE,MYSQL ERROR,CONDIT...
MYSQL数据库的基本操作七（存储过程）
zhangguangyi520的博客
12-10 1094
一，存储过程的创建语法 1,存储过程创建 delimiter // create procedure pr_name([proc_parameter]) [characteristic ...] #存储过程特性，创建时一般省略，默认系统设置 begin ...
[MySQL] 存储过程错误异常处理例子
09-16
-- DECLARE EXIT HANDLER FOR SQLEXCEPTION
mysql continue exit_MySQL数据库存储过程中事务和DECLARE EXIT/CONTINUE HANDLER的使用
weixin_32204175的博客
01-27 1082
1、DECLARE EXIT HANDLER FOR SQLEXCEPTION 语句后面可以跟一个 begin end的复合语句块，也可以直接跟一个简单语句例如 ：DECLARE EXIT HANDLER FOR SQLEXCEPTION v_succ=0;2、EXIT会在执行异常后执行执行 FOR SQLEXCEPTION 后的语句或块而整个停止下来；CONTINUE选项会在异常后继续执行，从而...
[MySQL] 存储过程错误异常处理例子 -- DECLARE EXIT HANDLER FOR SQLEXCEPTION
热门推荐
csdn's blog
06-19 1万+
刚才一个朋友问到： mysql有类似mssql退出执行的方法不？ 比如我执行到某个条件，下面就终止执行了。 想起以前写的存储过程，找了好久才找到，就发给他，希望对他有所帮助，贴在这里，留作纪念，也方便自己以后查找。 -- -----------------------------------------------------------------...
存储过程
12-14
1、什么是存储过程? 把一堆SQL语句放在一起,还可以根据条件执行不通SQL语句。 2、为什么要使用存储过程? 比单纯SQL语句执行要快。 3、存储过程优点： 1.允许模块化的设计，就是说只需创建一次，以后在该程序中就可以...
关于存储过程的编写的一些体会
12-15
由于周牛的JS知识很多，下面我来分享一下SQL下编写储存过程的初级阶段 在数据库界，在SQL，DB，甲骨文三大数据库中，通过编写储存过程增强或改变业务的功能，是一个手段，对数据库的灵活性的增强不言而喻。...
高斯数据库存储过程模板
最新发布
07-10
包括存过日志加载，分区创建，已经存过的使用
C#中调用MySQL存储过程的方法
09-03
主要介绍了C#中调用MySQL存储过程的方法,涉及C#数据库操作的相关技巧,具有一定参考借鉴价值,需要的朋友可以参考下
Mysql存储过程
01-31
我们常用的操作数据库语言SQL语句在执行的时候需要要先编译，然后执行，而存储过程（Stored Procedure）是一组为了完成特定功能的SQL语句集，经编译后存储在数据库中，用户通过指定存储过程的名字并给定参数（如果该...
MySql错误处理-错误处理的例子
Jerry的一亩三分地
08-16 5604
有几种错误处理的声明形式：  § 如果任何错误（不是 NOT FOUND ） , 设置 l_error 为 1 后继续执行：  DECLARE CONTINUE HANDLER FOR SQLEXCEPTION  SET l_error=1;  § 如果发生任何错误（不是 NOT FOUND）， 执行 ROLLBACK和产生一条错误消息后退出当前块或存储过程。  DECLARE EXIT
存储过程错误异常处理例子 --＞ DECLARE EXIT HANDLER FOR SQLEXCEPTION (转)
weixin_46020146的博客
10-03 1023
存储过程错误异常处理例子 --> DECLARE EXIT HANDLER FOR SQLEXCEPTION (转)
【官方原版翻译】DECLARE ... HANDLER语法
良心有约
01-21 1536
本文仅适用于MySQL 5.7 DECLARE handler_action HANDLER FOR condition_value [, condition_value ] ... statement handler_action : CONTINUE | EXIT | UNDO condition_value : mysql_...
declare handler 声明异常处理的语法
weixin_30702413的博客
06-20 875
声明异常处理的语法 DECLARE {EXIT | CONTINUE} HANDLER FOR {error-number | SQLSTATE error-string | condition} SQL statement 上述定义包括： Handler Type （CONTINUE,EXIT）//处理类型 继续或退出 Handler condition （SQLSTAT...
mysql 异常处理
dianrenjiang7330的博客
03-13 305
--该文章内容通过网络搜索组合， mysql 异常，可以自定义异常，再应用。也可使用系统默认的异常，捕获应用。 一、异常定义： DECLARE condition_name CONDITION FOR [condition_type]; condition_name参数表示异常的名称； condition_type参数异常类型 condition_type由SQLSTATE ...
mysql的procedure错误回滚问题
q195136130的专栏
09-12 1668
先科普一点知识：mysql在一个事物中发生错误时，是不会回滚整个事物的。 我们先证明这点 mysql> desc wzy; +-------+---------+------+-----+---------+-------+ | Field | Type    | Null | Key | Default | Extra | +-------+---------+------+-
MYSQL存储过程抓捕异常
heima201907的博客
11-13 1399
Mysql存储过程异常抓捕和异常信息捕获 直接上代码 create procedure testErrorMessage( INOUT code char， //输出的错误编号 INOUT message text //输出错误文本信息 ) begin DECLARE EXIT HANDLER FOR SQLEXCEPTION //① BEGIN ...
mysql continue exit_MYSQL存储过程中事务和DECLARE EXIT/CONTINUE HANDLER的使用
weixin_35259353的博客
01-19 288
-- 1、DECLARE EXIT HANDLER FOR SQLEXCEPTION 语句后面可以跟一个 begin end的复合语句块，也可以直接跟一个简单语句例如 ：DECLARE EXIT HANDLER FOR SQLEXCEPTIONv_succ=0;-- 2、EXIT会在执行异常后执行执行FOR SQLEXCEPTION 后的语句或块而整个停止下来；CONTINUE选项会在异常后继...

“相关推荐”对你有帮助么？

  • 非常没帮助
  • 没帮助
  • 一般
  • 有帮助
  • 非常有帮助
提交
评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

1.余额是钱包充值的虚拟货币，按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载，可以购买VIP、付费专栏及课程。

余额充值