MySQL常用操作之创建存储过程语法详解

article/2025/9/16 23:51:42

MySQL常用操作之创建存储过程语法详解

  • 前言
  • 简介
  • 语法
    • 创建结构
    • 变量结构
    • 入参变量和出参变量
    • 流程控制
      • 判断(IF 语句)
      • 判断(CASE 语句)
      • 循环(LOOP 语句)
      • 循环(WHILE 语句)
      • 循环(REPEAT 语句)
      • 再次循环(ITERATE 语句)
  • 总结
  • 参考链接

前言

  • 场景介绍

    作为一名Java搬运工，实际开发中经常使用定时任务来进行业务批处理，实际上通过数据库存过定时执行也能达到同样的效果。且存在两大好处：

    1. 通过修改存储过程的方式修改业务逻辑，不需要重启服务器。

    2. 存储过程将每一条SQL语句都进行编译，并保存在数据库当中，那么我们通过Java程序调用SQL语句时可以不用一条条的进行编译，而是直接调用存储过程，效率更高。

简介

  • 概念

    存储过程（Stored Procedure）是一种在数据库中存储复杂程序，以便外部程序调用的一种数据库对象，它可以封装成SQL语句集，以便完成一些较为复杂的业务逻辑，并且可以想 Java 等高级编程语言一样输入参数。

    存储过程是为了完成特定功能的SQL语句集，创建时会预先编译，并保存在数据库中，用户后续的调用都不需要再次编译了，用户可通过指定存储过程的名字并给定参数(需要时)来调用执行。

    存储过程思想上很简单，就是数据库 SQL 语言层面的代码封装与重用。

语法

创建结构

  • 创建存过模板

    CREATE[DEFINER = { user | CURRENT_USER }]PROCEDURE sp_name ([proc_parameter[,...]])[characteristic ...] routine_body

    参数介绍

    1. proc_parameter参数部分(先写输入/输出参数名，再写参数类型)
    [ IN | OUT | INOUT ] param_name type

    1. type 是 MySQL 支持的所有类型，如int、varchar等

    2. routine_body(程序体)部分 ，可以书写合法的SQL语句

    BEGIN ...... END$$

  • 声明语句结束符

    -- $$ 可以自定义为其他作为结束符DELIMITER $$

  • 声明存储过程

    CREATE PROCEDURE nbsp_procedure(IN 参数名 参数类型)

  • 存储过程开始和结束符

    BEGIN .... END

  • 创建示例

    -- 声明语句结束符，可以自定义:delimiter $$-- 创建存储过程，名为  nbsp_procedure 参数为空， 执行输出 'hello world'create procedure nbsp_procedure () -- 默认当前用户下创建 create definer = `nbsp`@`%` procedure nbsp_procedure ()beginselect 'hello world';end $$-- 声明语句结束符delimiter ;-- 调用存储过程call nbsp_procedure();-- 删除存储过程drop procedure nbsp_procedure;

  • 注意事项

    1. 这里需要注意的是 DELIMITER$$ 和 DELIMITER; 两句，DELIMITER是声明分割符的意思，因为MySQL默认以 “;” 为分隔符，如果我们没有声明分割符，那么编译器会把存储过程当成SQL语句进行处理，则存储过程的编译过程会报错，所以要事先用DELIMITER关键字申明当前段分隔符，这样MySQL才会将“;”当做存储过程中的代码，不会执行这些代码，用完了之后要把分隔符还原。

    2. 存储过程根据需要可能会有输入、输出、输入输出参数，如果有多个参数用“,”分割开。

    3. 过程体的开始与结束使用BEGIN与END进行标识

变量结构

  • 变量分类

    MySQL变量一共分为两大类：用户自定义变量和系统变量。

    1. 用户自定义变量(重点)：局部变量、会话变量

    2. 系统变量(理解)：会话变量、全局变量

  • 局部变量

    用户自定义在begin…end代码块中的，也在代码块中有效。

    -- 语法declare  变量名  变量类型  [default 默认值]-- 举例declare name varchar(32) '张三'-- set 赋值delimiter $$drop procedure if exists var01$$create procedure var01()begin-- 声明变量declare username varchar(32) default '张三';-- 为变量赋值set username = '李四';select username;end $$delimiter ;call var01();

  • 会话变量

    会话变量即为服务器为每个客户端连接维护的变量。在客户端连接时，使用相应全局变量的当前值对客户端的回话变量进行初始化。

    设置会话变量不需要特殊权限，但客户端只能更改自己的会话变量。其作用域与生命周期均限于当前客户端连接。

    @username 会话变量可以在 begin … end 代码块中定义，也可以在外部定义，定义后同一变量名在内存中只有一份，且当前会话有效。

    delimiter $$drop procedure if exists var02$$-- 创建存储过程create procedure var02()beginset @username='张三';end $$delimiter ;-- 调用存储过程call var02();-- 查询会话变量的值select @username;

入参变量和出参变量

  • IN 输入变量

    表示该参数的值必须在调用存储过程时指定，在存储过程中修改该参数的值不能被返回，为默认值

    仅需要将数据传入存储过程，并不需要返回计算后的该值。

    只能当做传入参数。

  • OUT 输出参数

    该值可在存储过程内部被改变，并可返回

    不接受外部传入的数据，仅返回计算之后的值。

    只能当做转出参数。

  • INOUT输入输出参数

    调用时指定，并且可被改变和返回

    需要数据传入存储过程经过调用计算后，再传出返回值

    可当做传入转出参数。

  • 演示示例

    1. 创建一个会话变量，用于测试out对会话变量的影响
    set @username='wangwu';
    1. 创建一个var03存过，返回用户名
    delimiter $$drop procedure if exists var03$$-- 传入两个变量，第一个为输入in变量，第二个为是输出out变量。当输入用户id 时，返回用户名。create procedure var03(in id int,out username varchar(32))beginselect id,username;-- select 'procedure into username' into username;-- set username='procedure into username'end $$delimiter ;
    1. 查询会话变量用户名
    -- 在还未调用存储过程时，我们发现此时的会话变量@username的值依然为 wangwuselect @username;
    1. 调用存过，查看会话变量值是否正确
    call var03(35,@username);

会话变量返回值

原因分析：这里就是 in 和 out 参数的区别， 也有区别与Java等高级语言中的参数。

如果是以Java的思想理解调用存储过程的话，我们可能会认为说 @username变量的值为wangwu 传入到 存储过程函数中，所以我们也理所应当的认为 username的值应该为wangwu。

out函数它只负责变量的接收，不负责传入值。 也就是说 调用call var03(35,@username); 的时候，@username只是将它的内存地址传进去，告诉存储过程说，等等你把out变量username的值给我（我是@username）。然后在存储过程中一直都没有给username进行赋值的操作，所以此时的username的值为null，最终就将null赋值给了 @username。

  1. 查询会话变量用户名
-- 此时会话变量为NULLselect @username;

流程控制

  • 内容介绍

    在存储过程和自定义函数中可以使用流程控制语句来控制程序的流程。

    MySQL 中流程控制语句有：IF 语句、CASE 语句、LOOP 语句、LEAVE 语句、ITERATE 语句、REPEAT 语句和 WHILE 语句等。

判断(IF 语句)

  • 定义

    IF 语句用来进行条件判断，根据是否满足条件（可包含多个条件），来执行不同的语句，是流程控制中最常用的判断语句。

  • 语法

    IF search_condition THEN statement_list[ELSEIF search_condition THEN statement_list]...[ELSE statement_list]END IF------------------------------------------------------------------------------------------IF  判断条件  THEN 执行语句[ELSELF  判断条件  THEN  执行语句][ELSE  执行语句]END IF

  • 参数介绍

    search_condition 参数表示条件判断语句，如果返回值为 TRUE ，相应的 SQL 语句列表（statement_list）被执行；如果返回值为 FALSE，则 ELSE 子句的语句列表被执行。statement_list 可以包括一个或多个语句。

  • 示例

    set @username = '张三风';-- 存过创建失败，不清楚原因delimiter $$drop procedure if exists if01$$create procedure if01()beginIF @username = '张三' THENselect concat('我是',@username);ELSEIF  @username = '张三风' THENselect '张三是我徒弟';ELSEselect '我是栗四,不认识张三';END IF;end $$delimiter ;call if01;

    创建失败原因：最后一个end 后面要跟 “;” 号，可以创建成功，但是执行完依旧报错1064 - ‘$$’ at line 10；但是创建其他存过一样的写法创建正常。

  • 注意事项

    MySQL 中的 IF( ) 函数不同于这里的 IF 语句。

判断(CASE 语句)

  • 定义

    CASE 语句也是用来进行条件判断的，它提供了多个条件进行选择，可以实现比 IF 语句更复杂的条件判断。

  • 语法

    语法一：expr_condition为条件表达式,计算结果为true,则执行THEN后面的语句。多个WHEN…THEN依次执行，ELSE为可选条件。

    CASEWHEN expr_condition THEN ...do something...WHEN expr_condition THEN ...do something.......多个 WHEN THEN 语句.....[ELSE ...do something...]END CASE;

    语法二：其中，case_value表示条件判断的表达式,WHEN后的表达式结果如果和case_value匹配,则执行相应的THEN后面的语句。没有则执行ELSE，ELSE为可选。(建议使用第一种,逻辑清晰一点)。

    CASE case_valueWHEN when_value THEN statement_list[WHEN when_value THEN statement_list]...[ELSE statement_list]END CASE------------------------------------------------------------------CASE  参数变量WHEN  参数变量1  THEN  执行语句][WHEN  参数变量2  THEN  执行语句][ELSE  执行语句]END  CASE

  • 参数介绍

    1. case_value 参数表示条件判断的变量，决定了哪一个 WHEN 子句会被执行；

    2. when_value 参数表示变量的取值，如果某个 when_value 表达式与 case_value 变量的值相同，则执行对应的 THEN 关键字后的 statement_list 中的语句；

    3. statement_list 参数表示 when_value 值没有与 case_value 相同值时的执行语句。

    4. CASE 语句都要使用 END CASE 结束。

  • 示例

    DELIMITER $$  CREATE PROCEDURE proc1(IN parameter int)  begin declare var int;  set var=parameter+1;  case var  when 0 then   insert into t values(17);  when 1 then   insert into t values(18);  else   insert into t values(19);  end case;  end;  $$  DELIMITER ;

  • 注意事项

    方式二类似与 Java 中的 switch…case 语句的使用。 与之区别就是执行时，只要进入其中的一个判断语句之后，就不会在执行其他的，也不需要使用break语句进行跳出。

循环(LOOP 语句)

  • 定义

    反复执行循环体中的语句，直到循环结束。如果没有leave等关键字，此时的loop为死循环。

  • 语法

    [begin_label:] LOOP statement_list ;END LOOP [begin_label];------------------------------------------------------------------循环名: loop-- 循环体执行语句;end loop 循环名;

  • 示例

    -- 需求： 打印1-10，输出结果为 1，2，3，4......9,10-- 如果存在loop_test，则先删除drop procedure if exists loop_test;delimiter $$-- 创建存储过程loop_testcreate procedure loop_test()begindeclare c_index int default 1;declare result_str varchar(256) default '1';cnt: loop-- 循环10次，则跳出循环if c_index >= 10 thenleave cnt;end if;-- 自增set c_index = c_index + 1;-- 拼接select concat(result_str, ',', c_index) into result_str;end loop cnt;-- 查询结果select result_str;end $$delimiter ;call loop_test();

  • 注意事项

    loop循环不需要初始条件，这点和while 循环相似，同时和repeat循环一样不需要结束条件。

    LEAVE关键字作用

    leave 就相当于Java中的break语句，用于跳出当前循环

    Java类比代码

    public void loop_test() {String result_str = "1";int c_index = 1;while (true) {if (c_index >= 10) {break;}c_index = c_index + 1;result_str = result_str + "," + c_index;}System.out.println(result_str);}

循环(WHILE 语句)

  • 定义

  • 语法

    [WHILE标注] : WHILE 条件表达式 DO执行语句END LOOP [WHILE标注];

  • 参数介绍

    “WHILE标注” 为可选，表示WHILE循环语句的标签。

  • 示例

    drop procedure if exists sp_flow_while;delimiter $$-- 创建存储过程sp_flow_whilecreate procedure sp_flow_while()begindeclare c_index int default 1;-- 收集结果集字符串declare result_str varchar(256) default '1';while c_index < 10 doset c_index = c_index + 1;set result_str = concat(result_str, ',', c_index);end while;-- 查询结果select result_str;end $$delimiter ;call sp_flow_while();

  • 注意事项

    WHILE的执行过程是：先判断条件表达式 expr_condition，如果为true执行循环，否则结束循环(与REPEAT的区别是：WHILE先判断条件，REPEAT是后判断条件)。

循环(REPEAT 语句)

  • 定义

    repeat在执行操作后检查结果，而while则是执行前进行检查。

  • 语法

    [REPEAT标注]:REPEAT执行语句UNTIL 条件表达式END REPEAT [REPEAT标注]

  • 参数介绍

    1. “REPEAT标注” 为可选，表示REPEAT循环语句的标签；

    2. UNTIL指定循环条件。

  • 示例

    drop procedure if exists test;   -- 如果存在test存储过程则删除delimiter $$                            -- 定义标识符为双斜杠create procedure test()                 -- 创建无参存储过程,名称为testbegindeclare i int default 6;            -- 申明变量repeatinsert into test values (i);    -- 往test表添加数据set i = i + 1;                  -- 循环一次,i加一until i > 10 end repeat;            -- 结束循环的条件: 当i大于10时跳出repeat循环select * from test;                 -- 查看test表数据end;$$										-- 结束定义语句delimiter ;

  • 注意事项

    REPEAT执行过程是：每次循环体执行完毕需要去判断一下条件表达式expr_condition，如果为true继续执行，否则结束循环。

再次循环(ITERATE 语句)

  • 定义

    再次循环，将执行顺序转到语句开头处，只能用于LOOP，REPEAT和WHILE语句内。

  • 语法

    ITERATE [LOOP/REPEAT/WHILE标注]

  • 参数介绍

    类似于Java代码语言的continue

  • 示例

    drop procedure if exists test;          -- 如果存在test存储过程则删除delimiter $$                            -- 定义标识符为双斜杠create procedure test()                 -- 创建无参存储过程,名称为testbegindeclare i int(11) default 0;        -- 申明变量myloop:loopset i = i + 1;if i < 5 then iterate myloop; -- 跳过本次循环elseif i > 8 then leave myloop;end if;insert into test values (i); end loop myloop;select * from test;  end;$$delimiter  ;                           -- 结束定义语句

总结

  • 优点

    1. 存储过程可封装，并隐藏复杂的商业逻辑。

    2. 存储过程可以回传值，并可以接受参数。

    3. 存储过程无法使用 SELECT 指令来运行，因为它是子程序，与查看表，数据表或用户定义函数不同。

    4. 存储过程可以用在数据检验，强制实行商业逻辑等。

  • 缺点

    1. 存储过程，往往定制化于特定的数据库上，因为支持的编程语言不同。当切换到其他厂商的数据库系统时，需要重写原有的存储过程。

    2. 存储过程的性能调校与撰写，受限于各种数据库系统。

参考链接

  • MySQL（七）存储过程

    https://blog.csdn.net/Dim_Jerry/article/details/110203466

  • mysql存储过程、存储函数（二）：流程控制语句

    https://blog.csdn.net/weixin_40482816/article/details/112258414

  • mysql创建定时执行存储过程任务

    https://www.cnblogs.com/master-zxc/p/6429266.html

  • mysql定时任务(event事件)

    https://www.cnblogs.com/angryjj/p/11324590.html


http://chatgpt.dhexx.cn/article/OkcVFyT6.shtml

相关文章

Oracle 存储过程语法

Oracle 存储过程语法

Oracle 存储过程语法 1. 创建表&#xff08;测试数据准备&#xff09; -- 创建用户表 create table TT_USER (USERID NUMBER(10),USERNAME VARCHAR2(255),PASSWORD VARCHAR2(255),SEX VARCHAR2(1) );INSERT INTO TT_USER VALUES (101, zhang, 111, 1); INSERT INTO TT…
阅读更多...
mysql存储过程基本语法

mysql存储过程基本语法

本文来说下mysql存储过程基本语法 文章目录 基本语法使用实例变量的使用变量定义declare语句变量赋值用户变量 存储过程的参数in 输入参数out 输出参数inout输入输出参数 本文小结 基本语法 存储过程就是具有名字的一段代码&#xff0c;用来完成一个特定的功能。创建的存储过程…
阅读更多...
Oracle存储过程基本语法

Oracle存储过程基本语法

后来者居上&#xff0c;不是easy的事情 尤其技术类work&#xff0c;更加如此。都是have one 投入的过程的。尤其开发&#xff0c;还是属于技术壁垒挺高的行业。 创建基本的存储过程 1 CREATE OR REPLACE PROCEDURE MyProName IS 2 BEGIN 3 NULL; 4 END; 行1:CREATE OR REPL…
阅读更多...
存储过程常见语法

存储过程常见语法

存储过程常见语法 一、存储过程的概念: 1、存储过程Procedure是一组为了完成特定功能的SQL语句集合&#xff0c;经编译后存储在数据库中&#xff0c;用户通过指定存储过程的名称并给出参数来执行 2、存储过程中可以包含逻辑控制语句和数据操纵语句&#xff0c;它可以接受参数…
阅读更多...
存储过程的语法讲解

存储过程的语法讲解

在上一篇文章&#xff1a;别再说不知道什么是存储过程和存储函数了 中简单的介绍了存储过程和存储函数以及其使用。其实存储过程是可以进行编程的&#xff0c;所以可以和其他的编程语言一样使用变量、表达式以及控制结构进行编程&#xff0c;从而实现一些复杂和有用的功能。这篇…
阅读更多...
子网掩码的作用

子网掩码的作用

IP地址由网络和主机两部分标识组成 IP地址由“网络标识&#xff08;网络地址&#xff09;”和“主机标识&#xff08;主机地址&#xff09;”两部分组成。在局域网内相互间通信的网络必须具有相同网络地址&#xff0c;也叫相同的网段&#xff0c;在同一个网段内每个设备的主机…
阅读更多...
子网掩码使用详解

子网掩码使用详解

一、子网掩码 IP地址是以网络号和主机号来标示网络上的主机的&#xff0c;我们把网络号相同的主机称之为本地网络&#xff0c;网络号不相同的主机称之为远程网络主机&#xff0c;本地网络中的主机可以直接相互通信&#xff1b;远程网络中的主机要相互通信必须通过本地网关&…
阅读更多...
什么是子网掩码 子网掩码的作用是什么？

什么是子网掩码 子网掩码的作用是什么？

什么是子网掩码 子网掩码的作用是什么&#xff1f; 网络工作人员经常需要与ip和子网掩码等打交道&#xff0c;相信绝大数的朋友都知道IP的意思&#xff0c;但是还不理解子网掩码的意思&#xff0c;下面装机之家小编来为大家介绍下关于子网掩码的相关知识&#xff0c;希望能够对…
阅读更多...
ip、子网掩码、网关、默认网关

ip、子网掩码、网关、默认网关

这里写目录标题 ip网络地址主机地址 子网子网掩码子网掩码的表示方法为什么要使用子网掩码&#xff1f;子网掩码的分类 网关默认网关 ip ip地址 网络地址 主机地址&#xff08;又称&#xff1a;网络号和主机号&#xff09;&#xff0c;我们把网络号相同的主机称之为本地网络…
阅读更多...
子网划分和子网掩码

子网划分和子网掩码

目录 前言 1、IP地址 1.1 IP地址的内容 1.2 IP地址的分类 2、子网掩码的作用 2.1 主机间的通信 2.2 子网掩码 3.子网划分 3.1 子网划分的原因 3.2 子网划分的原理 3.3 IP地址汇总 总结 前言 知道IP地址的分类和基本使用&#xff0c;如果公司拥有300台计算机&#xf…
阅读更多...
子网掩码详解

子网掩码详解

IP地址 IP地址被用来给Internet上的电脑一个编号。大家日常见到的情况是每台联网的PC上都需要有IP地址&#xff0c;才能正常通信。我们可以把“个人电脑”比作“一台电话”&#xff0c;那么“IP地址”就相当于“电话号码”&#xff0c;而Internet中的路由器&#xff0c;就相当于…
阅读更多...
子网掩码的两种计算方式

子网掩码的两种计算方式

&#xff08;尊重劳动成果&#xff0c;转载请注明出处&#xff1a;http://blog.csdn.net/qq_25827845/article/details/70946041冷血之心的博客&#xff09; 关注微信公众号&#xff08;文强的技术小屋&#xff09;&#xff0c;学习更多技术知识&#xff0c;一起遨游知识海洋~ …
阅读更多...
子网掩码的划分和计算详解

子网掩码的划分和计算详解

一、子网掩码的计算 TCP/IP网间网技术产生于大型主流机环境中&#xff0c;它能发展到今天的规模是当初的设计者们始料未及的。网间网规模的迅速扩展对IP地址模式的威胁并不是它不能保证主机地址的唯一性&#xff0c;而是会带来两方面的负担&#xff1a;第一&#xff0c;巨大的…
阅读更多...
一文带你了解什么是子网掩码

一文带你了解什么是子网掩码

什么是子网掩码 子网掩码代表了“网络号子网号”与主机号之间的分割方案。 很晦涩&#xff1f; 说子网掩码&#xff0c;我们还得先说说IP地址。 什么是IP地址 因特网上的每台主机或路由器端口都必须有一个唯一的IP地址。因为IP地址&#xff0c;在网络上我们才能互相识别&a…
阅读更多...
制作QQ登录界面（UI版）

制作QQ登录界面（UI版）

工具&#xff1a;Android Studio activity_main.xml <?xml version"1.0" encoding"utf-8"?> <androidx.constraintlayout.widget.ConstraintLayout xmlns:android"http://schemas.android.com/apk/res/android"xmlns:app"http:…
阅读更多...
html登录界面

html登录界面

今天是2019年11月14日我第一次注册博客&#xff0c;我把我做的一个登陆界面分享给大家看希望各位大佬指教。 首先是html文件。 1.登录界面html 登录界面 用户名: 密 码: 注册 2.注册界面html。 注册界面 用户名: 输入密码: 确认密码: 立刻注册返回登录 **3.效果图**! 欢迎…
阅读更多...
Android仿QQ微信开场导航以及登陆界面

Android仿QQ微信开场导航以及登陆界面

相信大家对于微信等社交应用的UI界面已经都很熟悉了&#xff0c;该UI最值得借鉴的莫过于第一次使用的时候一些列产品介绍的图片&#xff0c;可以左右滑动浏览&#xff0c;最后进入应用&#xff0c;这一效果适用于多种项目中&#xff0c;相信今后开发应用一定会用得到。网路上也…
阅读更多...
[练习]QQ登陆界面-测试用例的编写

[练习]QQ登陆界面-测试用例的编写

&#xff08;Test Case&#xff09;是为了实施测试而向被测试系统提供的一组集合&#xff0c; 包括&#xff1a;测试环境、操作步骤、测试数据、预期结果等要素。 一条测试用例最终只有一个结果。 一个功能点至少有一个测试用例。 测试用例数/功能点数 测试的覆盖率&#xf…
阅读更多...
web之qq邮箱登录界面

web之qq邮箱登录界面

我们可以用css来做这个登录的表格 具体的完整代码实现如下&#xff1a; <!DOCTYPE html> <html> <meta charset"utf-8"> <title>登录qq邮箱</title> <style type"text/css">* {padding: 0;margin: 0;}.content {wid…
阅读更多...
转载：QQ登录界面

转载：QQ登录界面

//:登陆界面代码&#xff1a; package Myjava_QQ; import java.awt.*; import javax.swing.*; import Myjava_QQ.truess; import java.awt.event.*; import java.applet.*; import java.io.BufferedReader; import java.io.FileNotFoundException; import java.io.FileReader; …
阅读更多...
推荐文章

Copyright @ 2022~2023