MySQL存储过程

最新推荐文章于 2024-07-27 21:50:15 发布
最新推荐文章于 2024-07-27 21:50:15 发布
阅读量179 收藏 1
点赞数 1
分类专栏： MySQL 文章标签： mysql 数据库 sql
版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
本文链接：https://blog.csdn.net/m0_54797663/article/details/120479633
版权

MySQL存储过程

存储过程是数据库的一个重要对象，可以封装SQL，完成一些复杂的业务逻辑。使用存储过程，最主要的特点是执行速度快，其经过编译后会比一条一条单独执行快，也能避免频繁的连接而消耗资源。

语法结构

创建的语法结构

create 
    [definer = user]                                   -- 用来指定用户的执行权限，默认所有用户可用；
    procedure  procedure_name([proc_parameter[,...]])  --  procedure_name存储过程的名字；
                         -- proc_parameter参数，类型有in（入参）、out（出参）、inout（入参和出参）；
   body                  -- body中写程序体，只要是合法的sql即可。以begin开始，以end结束，必须成对出现，通常一对即可。

call 调用存储过程

call  procedure_name   -- 使用call关键字调用存储过程

实例：创建存储过程并调用

-- 创建存储过程
create procedure proc_test()
begin
    select now() from dual;  -- 查询当前时间
end;
-- 调用存储过程
call proc_test();  -- 执行结果是当前时间

删除的语法结构

drop procedure procedure_name  -- 删除时指定存储过程名称即可，不能加括号。

实例：删除存储过程

drop procedure proc_test;

变量及赋值

局部变量

用户自定义的变量，尽在begin/end块中有效。

-- 使用declare关键字声明变量名和类型，可指定默认值。 [] 表示可选参数
declare var_name type [default var_value];
使用set赋值

给变量赋值，可以使用set和into关键字，对所有变量都适用。

create procedure proc_var01()
begin
    declare user_name varchar(50);#直接声明，无默认值
    declare age int(11) default 20;#声明的同时指定默认值
    set user_name = '张三';#赋值
    select user_name,age; #查询
end;
-- 调用存储过程 
call proc_var01

若有多个set对其赋值，则最终值是最后一个set的结果。

使用into赋值
create procedure proc_var02()
begin
    declare create_time datetime;
    select now() into create_time;
    select create_time;
end;
-- 调用存储过程 
call proc_var02

若在select中使用into给多个变量赋值，则使用逗号分隔即可，但前面的值和后面的变量必须一一对应。

用户变量

用户自定义的变量，在当前会话有效。

@var_name  #无需提前声明，使用即声明
赋值
create procedure proc_var03()
begin
    set @create_time = now(); # 并未声明就直接赋值，但变量名前面必须带@符号，表示它是用户变量。
    select @create_time;
end;
-- 调用存储过程 
call proc_var03

会话变量与全局变量

会话变量和全局变量都是系统提供的变量。

会话变量：当前会话有效。由于用的不多，在此略。

全局变量：整个MySQL服务有效。由于用的不多，在此略。

入参和出参

in | out | inout param_name type  # in（入参）、out（出参）、inout（入参和出参） typ参数的类型

入参

create procedure proc_var04(in name varchar(50))
begin
    select name;
end;
-- 调用存储过程 传入名字进行查询：
call proc_var04('root');

出参

create procedure proc_var05(in phone varchar(11),out phone_suffix varchar(4))
begin
    set phone_suffix = right(phone,4);  -- 截取后4位：
end;
-- 调用存储过程 传入手机号，
call proc_var05('15623524651',@suffix);
-- 执行结果是4651。需要注意的是，在接收输出参数时，必须使用用户变量，否则接收不到，调用时还会出错。
select @suffix;

入参和出参

create procedure proc_var06(inout name varchar(100))
begin
    set name = concat('hello,',name); #将hello拼接用户名
end;
-- 设置传入的参数
set @username='张三';
-- 调用存储过程 传入参数用户名
call proc_var06(@username);
-- 查询用户名
select @username;

判断（流程控制）

if

if判断有if、if-else、if-elseif-else等。其条件在判断等于时用一个等号，条件后使用then，且以 if 开头 end if 成对结尾。

仅有if

create procedure proc_var07(in age int)
begin
    declare msg varchar(20) default '';
    if age = 20 then                            -- 满足条件进入then
        set msg = '年龄为20';
    end if;                                     -- 表示if 结束了
    select msg;
end;
-- 调用存储过程 传入参数用户名
call proc_var07(20);

if-else

create procedure proc_var08(in age int)
begin
    declare msg varchar(20) default '';
    if age = 20 then                            -- 满足条件进入then
        set msg = '年龄为20';
    else                                        -- 不满足条件进入else
        set msg = '年龄不为20';
    end if;                                     -- 表示if 结束了
    select msg;
end;
-- 调用存储过程 传入参数用户名
call proc_var07(21);

if-elseif-else

create procedure proc_var09(in age int)
begin
    declare msg varchar(20) default '';
    if age > 20 then                           -- 满足条件进入then
        set msg = '年龄大于20';
    elseif age < 20 then                       -- 满足条件进入then
        set msg = '年龄小于20';
    else                                       -- 不满足条件进入else
        set msg = '年龄等于20';
    end if;                                    -- 表示if 结束了
    select msg;
end;
-- 调用存储过程 传入参数用户名
call proc_var09(18);

case分支

case的语法有两种

第一种：值匹配

create procedure proc_var10(in season int)
begin
    declare msg varchar(20) default '';
    case season                             -- 值匹配 season为3 就进入3这个分支
        when 1 then
            set msg = 'spring';
        when 2 then
            set msg = 'summer';
        when 3 then
            set msg = 'autumn';
        else
            set msg = 'winter';
    end case;
    select msg;
end;
-- 调用存储过程 传入3 执行的结果是autumn
call  proc_var10(3);

第二种：表达式和if一样，哪个满足条件进入哪个

create procedure proc_var10(in age int)
begin
    declare msg varchar(20) default '';
    case
        when age > 20 then
            set msg = '年龄大于20';
        when age < 20 then
            set msg = '年龄小于20';
        else
            set msg = '年龄等于20';
    end case;
    select msg;
end;
-- 调用存储过程 传入18 执行的结果是年龄小于20
call  proc_var10(18);

循环（流程控制）

**leave：**用于结束循环。

iterate：用于结束本次循环，进入下一次循环。

loop循环

[begin_label:] loop
    statement_list
end loop [begin_label]

begin_label是给该循环起个别名，当多个loop嵌套时结束循环需根据别名。另外loop是死循环，必须使用leave结束循环.

实例： 打印1到10：

create procedure proc_var11(in count int )
begin
    declare msg varchar(200) default '1';
    declare curr int default 1;
    cnt:loop                        -- cnt是loop循环的别名
      if curr >= count then
        leave cnt;                   -- 当curr>10时，结束cnt循环       
      end if;
      set curr = curr + 1;
      set msg = concat(msg,',',curr);  
    end loop cnt;
    select msg;
end;
-- 调用存储过程 传入10 执行的结果是打印1到10
call  proc_var11(10);

repeat循环

[begin_label:] repeat
    statement_list
until condition
end repeat [begin_label]

当until后面的条件符合时结束循环，否则会一直循环。

实例： 打印1到10：

create procedure proc_var12(in count int)
begin
    declare msg varchar(200) default '1';
    declare curr int default 1;     
    cnt:repeat
      set curr = curr + 1;
      set msg = concat(msg,',',curr);            
    until curr >= count #符合条件结束循环 ，注意until后不能加分号。 
    end repeat cnt;
    select msg;
end;
-- 调用存储过程 传入10 执行的结果是打印1到10
call  proc_var12(10);

注意：until后不能加分号。

while循环

[begin_label:] while condition do    
    statement_list
end while [begin_label]

实例： 打印1到10：

create procedure proc_var13(in count int)
begin
    declare msg varchar(100) default '';
    declare curr int default 1;
    cnt:while curr<=count do               -- 符合条件进入循环体，不符合条件退出循环
        set msg = concat(msg,',',curr);
        set curr = curr + 1;
    end while;
    select msg;
end;
-- 调用存储过程 传入10 执行的结果是打印1到10
call  proc_var13(10);

游标

数据准备

# 创建员工表emp
CREATE TABLE `emp` (
  `id` int(11) NOT NULL,
  `name` varchar(200) DEFAULT NULL COMMENT '姓名',
  `dept_no` varchar(32) DEFAULT NULL COMMENT '部门编号',
  `sal` decimal(10,2) DEFAULT NULL COMMENT '工资',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

# 创建部门表dept
CREATE TABLE `dept` (
  `dept_no` varchar(32) NOT NULL COMMENT '部门编号',
  `dept_name` varchar(200) DEFAULT NULL COMMENT '部门名称',
  PRIMARY KEY (`dept_no`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

#添加数据
INSERT INTO `dept`(`dept_no`, `dept_name`) VALUES ('A001', '策划部');
INSERT INTO `dept`(`dept_no`, `dept_name`) VALUES ('A002', '开发部');
INSERT INTO `dept`(`dept_no`, `dept_name`) VALUES ('A003', '人事部');
INSERT INTO `emp`(`id`, `name`, `dept_no`, `sal`) VALUES (1, '张飒', 'A002', 5000.00);
INSERT INTO `emp`(`id`, `name`, `dept_no`, `sal`) VALUES (2, '李敏', 'A003', 4500.00);
INSERT INTO `emp`(`id`, `name`, `dept_no`, `sal`) VALUES (3, '赵虹', 'A001', 4350.00);
INSERT INTO `emp`(`id`, `name`, `dept_no`, `sal`) VALUES (4, '赵敏敏', 'A002', 6530.00);
INSERT INTO `emp`(`id`, `name`, `dept_no`, `sal`) VALUES (5, '孙慧', 'A002', 7150.00);

游标语法

游标的作用主要是遍历查询的结果集。故在声明游标时for后面就是select的查询语句。

#声明游标
declare cursor_name cursor for select_statement
#声明句柄
declare continue handler for 1329 set finished = 1;
#打开游标
open cursor_name 
#遍历取值 使用fetch遍历，当结果集遍历到最后一条时，它会报错，错误码是1329，错误状态是02000，需要用Handler处理异常
fetch cursor_name into var_name ...
#使用Handler（句柄）来判断异常进行处理。
if finished = 1 then  
  具体处理
#关闭游标，使用完后需关闭
close cursor_name

实例： 给开发部的员工涨工资的存储过程：

create procedure proc_var14(in name varchar(200),in add_sal decimal)
begin
    declare user_id int;
    declare finished int default 0;
    #声明游标
    declare cur_emp cursor for 
        select id from emp where dept_no =(select dept_no from dept where dept_name =name) ; 
    #声明句柄
    declare continue handler for 1329 set finished = 1;
    #打开游标
    open cur_emp;
    emp_loop:loop
        #遍历取值
        fetch cur_emp into user_id;
        #使用Handler（句柄）来判断异常进行处理。
        if finished = 1 then 
            leave emp_loop;  #退出循环
        else
            update  emp e set sal = e.sal + add_sal where e.id = user_id;  #开发部的员工涨工资
        end if; 
    end loop emp_loop;
    #关闭游标，使用完后需关闭
    close cur_emp;
end;
-- 调用存储过程 给开发部的员工1000涨工资
call  proc_var14('开发部',1000);

声明句柄的注意事项：

除了使用错误码外，还可以使用错误状态，声明如下，二选一即可：

declare continue handler for sqlstate '02000' set finished = 1;
菜鸟小杰
关注
专栏目录
mysql存储过程
LvTvGuXiang的博客
12-02 1469
MySQL储存过程
MySQL存储过程的异常处理方法
12-15
本实例展示了如何在MySQL存储过程中实现异常处理，以捕获并处理可能出现的错误。 首先，我们注意到在创建存储过程`myProc`时，使用了`delimiter $$`来改变MySQL客户端的语句分隔符，这是为了在存储过程中使用多个...
【MySQL】存储过程 —— if判断
最新发布
qq_39921135的博客
07-27 601
在if条件判断的结构中，ELSE IF结构可以有多个，也可以没有。ELSE结构可以有，也可以没有。
MySQL存储过程详解(实用)
Java方向程序员_张帅鹏
11-18 666
1、存储过程简介 一个存储过程是一个可编程的函数，它在数据库中创建并保存。 (1)存储过程增强了SQL语言的功能和灵活性 (2)存储过程允许标准组件是编程。 (3)存储过程能实现较快的执行速度   预编译。 比批处理快 (4)存储过程能够减少网络流量 (5)存储过程可被作为 2、创建存储过程 (1) delimiter //   从新设置分隔符  表示;不代表语句结束。 (2)c
Mybatis调用MySQL存储过程的简单实现
08-30
本文将详细介绍如何使用MyBatis调用MySQL存储过程，以及存储过程的相关概念、优缺点和基本语法。 首先，存储过程是数据库中预编译的一组SQL语句，它可以包含控制流语句，以实现更复杂的业务逻辑。存储过程的创建和...
mysql存储过程原理与使用方法详解
12-15
本文实例讲述了mysql存储过程原理与使用方法。分享给大家供大家参考，具体如下： 存储过程包含了一系列可执行的sql语句，存储过程存放于MySQL中，通过调用它的名字可以执行其内部的一堆sql 存储过程的优点 #1. 用于...
Python简单调用MySQL存储过程并获得返回值的方法
12-23
在Python编程中，调用MySQL数据库的存储过程并获取返回值是常见的操作，尤其是在执行复杂...如果你对MySQL存储过程的创建、参数传递、错误处理等方面有更深入的需求，可以参考相关教程或文档，进一步学习这些高级特性。
mysql存储过程之if语句用法实例详解
09-08
主要介绍了mysql存储过程之if语句用法,结合实例形式详细分析了mysql存储过程中if语句相关原理、使用技巧与操作注意事项,需要的朋友可以参考下
MYSQL存储过程
热门推荐
aiqi6666的博客
06-23 1万+
本文实例来自于易百教程 一.存储过程的定义： 存储过程是存储在数据库目录中的一段声明性SQL语句，优点有： 通常存储过程有助于提高应用程序的性能 存储过程有助于减少应用程序和数据库服务器之间的流量 存储的程序对任何应用程序都是可重用的和透明的 存储的程序是安全的 二.存储过程入门： 第一个存储过程 说明：DELIMITER 定好结束符为”//”, 然后最后又定义为”;”，//...
MySQL存储过程深度解析
"MySQL存储过程详解" MySQL存储过程是一种强大的数据库编程工具，它允许数据库管理员和开发者将一组SQL语句组合在一起，形成一个可重用的逻辑单元，以完成特定的任务。存储过程在数据库中预先编译，提高了执行效率...
评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

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

抵扣说明：

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

余额充值