1. Toy模板网首页
  2. > Toy博客

mysql 存储过程

10月前 作者：悟世君子 分类：Toy博客 阅读(22) 违法举报

这篇具有很好参考价值的文章主要介绍了mysql 存储过程。希望对大家有所帮助。如果存在错误或未考虑完全的地方，请大家不吝赐教，您也可以点击"举报违法"按钮提交疑问。

mysql存储过程：事先经过编译并且存储在数据库中的一段SQL语句集合。调用存储过程可以减少数据库和应用程序间传输的网络性能消耗

目录

1、创建存储过程和调用

1.1、无参存储过程

1.2、有参存储过程

1.3、mysql变量

1.3.1、系统变量

1.3.2、用户自定义变量

1.3.3、局部变量

2、存储过程中条件判断 if 的使用

3、存储过程中case的使用

4、存储过程中循环的使用

4.1、while 循环

4.2、repeat 循环

4.3、loop 循环

5、存储过程-游标 CURSOR

1、创建存储过程和调用

创建存储过程语句

CREATE PROCEDURE 存储过程名称([参数])
BEGIN
内容
END;

调用存储过程使用CALL

调用存储过程语句

CALL 存储过程名称([参数])

1.1、无参存储过程

示例

创建无参存储过程，查看数据库版本

CREATE PROCEDURE p_version()
BEGIN
SELECT VERSION();
END;

注意：笔者这里使用的是 navicat 和 mysql5.7.25 版本，如果读者使用的是 mysql 命令行客户端，需要使用 DELIMITER $$  定义结束符，因为上边语句中的 "；" 会被mysql 命令行客户端识别为结束符，因此在有"；"的函数体中，需要读者额外定义结束符，如下面代码

DELIMITER $$
CREATE PROCEDURE p_version()
BEGIN
SELECT VERSION();
END $$

后面的代码笔者都是直接在navicat上执行，因此不再重复说明  DELIMITER $$  定义结束符的事

调用

CALL p_version();

mysql存储过程实例,mysql,mysql,数据库

1.2、有参存储过程

声明存储过程参数需要3个关键字

IN

OUT

INOUT

IN：表示输入的参数

OUT：表示输出的参数

INOUT：即可以表示输入的参数，也可以表示输出的参数

示例sql

CREATE PROCEDURE p_name(IN userid int, OUT username varchar(20))
BEGIN
SELECT name INTO username from user where id = userid;
END;

数据库中有张user表，有1条数据，上面存储过程传入id，返回user的name字段

上面sql中的 name INTO username，是将user表的name赋值给存储过程的 username，这个INTO后面会详细说明，这里先看运行效果

mysql存储过程实例,mysql,mysql,数据库

调用存储过程

CALL p_name(1, @username);
SELECT @username;

 @username是一个变量，用来接收存储过程输出的值，再通过SELECT @username;显示出来

运行效果

mysql存储过程实例,mysql,mysql,数据库

1.3、mysql变量

mysql变量有系统变量、用户自定义变量、局部变量

1.3.1、系统变量

系统变量：是mysql提供的，用户不能定义，分全局变量（GLOBAL）、会话变量（SESSION）

系统变量是2个@表示，即@@

查看所有系统变量（GLOBAL）

SHOW GLOBAL VARIABLES;

查看所有会话变量（SESSION）

SHOW SESSION VARIABLES;

查看某一系统变量（GLOBAL）

SELECT @@GLOBAL.变量名

查看某一会话变量（SESSION）

SELECT @@SESSION.变量名

设置系统变量（GLOBAL）

SET GLOBAL 变量名 = 值

设置会话变量（SESSION）

SET SESSION 变量名 = 值

注意：这里设置的系统变量（GLOBAL）或会话变量（SESSION），当mysql服务器重启后都会失效；如果想长期有效需要在mysql配置文件中设置

示例

SHOW GLOBAL VARIABLES;

SHOW SESSION VARIABLES;

SELECT @@GLOBAL.autocommit;

SELECT @@SESSION.autocommit;

SET SESSION autocommit = 0;

运行效果

mysql存储过程实例,mysql,mysql,数据库

1.3.2、用户自定义变量

用户自定义变量：是用户自己定义的变量，不用提前声明，在使用的时候直接 @变量名 即可，作用域是当前连接，用1个@表示

赋值，有4种方式

SET @变量名 = 值
SET @变量名 := 值
SELECT @变量名 := 值
SELECT 字段名 INTO @变量名 FROM 表名

使用用户自定义变量

SELECT @变量名

示例

SET @demo_name = '雾失楼台';
SET @demo_age := 18;
SELECT @demo_gender := '女';
SELECT name INTO @demo_username FROM user;

SELECT @demo_name;
SELECT @demo_age,@demo_gender,@demo_username;

运行效果

mysql存储过程实例,mysql,mysql,数据库

1.3.3、局部变量

局部变量：在局部生效的变量，使用之前需要用 DECLARE 关键字先声明，可作为存储过程内的局部变量和输入参数，作用范围是局部变量声明的存储过程的BEGIN END间

声明

DECLARE 变量名 变量类型

变量类型就是数据库字段的类型，int、varchar、date等

赋值，有3种方式

SET 变量名 = 值
SET 变量名 := 值

SELECT 字段名 INTO 变量名 FROM 表名

使用局部变量

SELECT 变量名

示例

CREATE PROCEDURE p_localvar()
BEGIN
DECLARE demo_name VARCHAR(20);
SELECT name into demo_name from user;
SELECT demo_name;
END;

CALL p_localvar();

CREATE PROCEDURE p_localvar2()
BEGIN
DECLARE demo_name VARCHAR(20);
SET demo_name = '尊前谈笑人依旧';
SELECT demo_name;
END;

CALL p_localvar2();

CREATE PROCEDURE p_localvar3()
BEGIN
DECLARE demo_name VARCHAR(20);
SET demo_name := '雨打梨花深闭门，忘了青春，误了青春';
SELECT demo_name;
END;

CALL p_localvar3();

运行效果

mysql存储过程实例,mysql,mysql,数据库

2、存储过程中条件判断 if 的使用

存储过程中可以写 if 条件判断

语法格式

IF 条件1 THEN
内容
ELSEIF 条件2 THEN
内容
ELSE 
内容
END IF;

示例

CREATE PROCEDURE p_judging_age(IN age int)
BEGIN
    DECLARE content VARCHAR(20);
    IF age < 18 THEN
        SET content := '未成年人';
    ELSEIF 18<= age AND age <=65 THEN
        SET content := '青年人';
    ELSEIF 66<= age && age <=79 THEN
        SET content := '中年人';
    ELSE 
        SET content := '老年人';
    END IF;
    SELECT content;
END;

if 中多条件可以使用 AND 或 OR

CALL p_judging_age(17);
CALL p_judging_age(18);
CALL p_judging_age(19);
CALL p_judging_age(65);
CALL p_judging_age(66);
CALL p_judging_age(98);

运行效果

mysql存储过程实例,mysql,mysql,数据库

3、存储过程中case的使用

case when的作用和 if 判断类似 

语法格式1

CASE case_value
    WHEN when_value1 THEN
        statement_list1
    WHEN when_value2 THEN
        statement_list2
    ELSE
        statement_list3
END CASE;

当case_value的值符合when_case1时，执行statement_list1

当case_value的值符合when_case2时，执行statement_list2

其他情况执行 ELSE 中的statement_list3

语法格式2

CASE 
  WHEN search_condition1 THEN
      statement_list1
    WHEN search_condition2 THEN
      statement_list2
  ELSE
      statement_list3
END CASE;

当search_condition1条件成立时，执行statement_list1

当search_condition2条件成立时，执行statement_list2

其他情况执行statement_list3

示例语法格式1

先创建一张userinfo表，用于测试

DROP TABLE IF EXISTS `userinfo`;
CREATE TABLE `userinfo`  (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL,
  `age` int(11) NULL DEFAULT NULL,
  `gender` char(1) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL,
  PRIMARY KEY (`id`) USING BTREE
) ENGINE = InnoDB AUTO_INCREMENT = 4 CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Dynamic;

-- ----------------------------
-- Records of userinfo
-- ----------------------------
INSERT INTO `userinfo` VALUES (1, '李白', 27, 'm');
INSERT INTO `userinfo` VALUES (2, '朱淑真', 18, 'f');
INSERT INTO `userinfo` VALUES (3, '张先', 53, NULL);

userinfo表中有3条数据

mysql存储过程实例,mysql,mysql,数据库

 下面存储过程传入 userinfo 表 id，显示对应 id 用户的性别信息

CREATE PROCEDURE p_judging_gender(IN userid int)
BEGIN
    DECLARE user_gender CHAR(1);
    DECLARE gender_string VARCHAR(10);
    SELECT gender INTO user_gender from userinfo where id = userid;
    CASE user_gender
        WHEN 'm' THEN
            SET gender_string := '男';
        WHEN 'f' THEN
            SET gender_string := '女';
        ELSE
            SET gender_string := '未知';
    END CASE;
    SELECT gender_string;
END;

调用

CALL p_judging_gender(1);
CALL p_judging_gender(2);
CALL p_judging_gender(3);

运行效果

mysql存储过程实例,mysql,mysql,数据库

示例语法格式2

还是上面的例子，传入 userinfo 表 id，显示对应 id 用户的性别信息，换一种写法

CREATE PROCEDURE p_judging_gender2(IN userid int)
BEGIN
    DECLARE user_gender CHAR(1);
    DECLARE gender_string VARCHAR(10);
    SELECT gender INTO user_gender from userinfo where id = userid;
    CASE 
        WHEN user_gender = 'm' THEN
            SET gender_string := '男';
        WHEN user_gender = 'f' THEN
            SET gender_string := '女';
        ELSE
            SET gender_string := '未知';
    END CASE;
    SELECT gender_string;
END;

调用

CALL p_judging_gender2(1);
CALL p_judging_gender2(2);
CALL p_judging_gender2(3);

运行效果

mysql存储过程实例,mysql,mysql,数据库

4、存储过程中循环的使用

在存储过程中可以写循环

4.1、while 循环

while循环语法格式

WHILE 条件 DO
    内容
END WHILE;

条件为true时，执行内容

示例

CREATE PROCEDURE p_circulate()
BEGIN
    DECLARE num int;
	SET num := 10;
	WHILE num > 0 DO
	    SELECT num;
        set num := num - 1;
    END WHILE;
END;

调用

CALL p_circulate();

运行效果

mysql存储过程实例,mysql,mysql,数据库

4.2、repeat 循环

repeat循环语法格式

REPEAT
    内容
UNTIL 条件 
END REPEAT;

repeat 循环先执行内容，然后判断条件，如果条件为 true 则退出循环，否则继续循环

示例

CREATE PROCEDURE p_circulate_repeat()
BEGIN
    DECLARE num int;
    SET num := 10;
    REPEAT
        SELECT num;
        set num := num - 1;
    UNTIL num <= 0
    END REPEAT;
END;

调用

CALL p_circulate_repeat();

运行效果

mysql存储过程实例,mysql,mysql,数据库

4.3、loop 循环

 语法格式

label: LOOP
    statement_list

    IF exit_condition THEN
        LEAVE label; 
    END IF; 
END LOOP label;

label是一个标记，通过它控制循环的结束

loop 循环中使用 LEAVE label; 退出循环，如果loop中没有 LEAVE label;则是死循环

除了LEAVE外循环中还可以有 ITERATE，ITERATE的作用是跳过当前循环剩下的内容，直接进入下一次循环

示例1

CREATE PROCEDURE p_circulate_loop()
BEGIN
    DECLARE num int;
    SET num := 10;
    looplabel: LOOP
        IF num <= 0 THEN
            LEAVE looplabel; 
        END IF; 
        SELECT num;
        SET num := num - 1;
    END LOOP looplabel;
END;

调用

CALL p_circulate_loop();

运行效果

mysql存储过程实例,mysql,mysql,数据库

示例2

加入 ITERATE，当 num = 5 时跳过

CREATE PROCEDURE p_circulate_loop2()
BEGIN
    DECLARE num int;
    SET num := 10;
    looplabel: LOOP
        IF num <= 0 THEN
            LEAVE looplabel; 
        END IF; 
				
        IF num = 5 THEN
            SET num := num - 1;
            ITERATE looplabel;
        END IF; 		
				
        SELECT num;
        SET num := num - 1;
    END LOOP looplabel;
END;

调用

CALL p_circulate_loop2();

运行效果

mysql存储过程实例,mysql,mysql,数据库

 没有输出5

5、存储过程-游标 CURSOR

游标：是保存查询结果集的类型，在存储过程和自定义函数中可以使用游标对结果集进行处理。游标的使用包括声明、打开（OPEN）、获取（FETCH）、关闭（CLOSE）

声明

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

打开

OPEN 游标名称;

获取

FETCH 游标名称 INTO 变量;

关闭

CLOSE 游标名称;

示例

CREATE PROCEDURE p_cursor()
BEGIN
    DECLARE username VARCHAR(10);
    DECLARE userage int;
    DECLARE count int;
    DECLARE userinfo_cursor CURSOR FOR SELECT name, age FROM userinfo;
    SELECT count(*) INTO count FROM userinfo;

    OPEN userinfo_cursor;
    WHILE count > 0 DO
        FETCH userinfo_cursor INTO username, userage;
        SET count := count - 1;
        SELECT username, userage;
    END WHILE;
    CLOSE userinfo_cursor;

END;

查询 userinfo 表 name age 字段

查询 userinfo 表总数据条数用来计算循环次数，当然这里结束循环也可以使用 mysql自带的条件处理程序，这里不做过多介绍

调用

CALL p_cursor();

运行效果

mysql存储过程实例,mysql,mysql,数据库

至此完文章来源地址https://www.toymoban.com/news/detail-770264.html

到了这里，关于mysql 存储过程的文章就介绍完了。如果您还想了解更多内容，请在右上角搜索TOY模板网以前的文章或继续浏览下面的相关文章，希望大家以后多多支持TOY模板网！

本文来自互联网用户投稿，该文观点仅代表作者本人，不代表本站立场。本站仅提供信息存储空间服务，不拥有所有权，不承担相关法律责任。如若转载，请注明出处： 如若内容造成侵权/违法违规/事实不符，请点击违法举报进行投诉反馈，一经查实，立即删除！

分享到：
领支付宝红包 赞助服务器费用

相关文章

  • Mysql数据库实验报告--实验五 存储过程和触发器

    Mysql数据库实验报告--实验五 存储过程和触发器

    在这个系列会更新一些最近老师要求写的实验报告~ 大家尽量对着我的文章做一个参考，不要盲目的cv，毕竟这个对于我们以后的工作学习还是十分重要的。 从这个实验开始，就不在cmd命令行里面进行mysql数据库的操作，因为代码长度和代码的复杂性，需使用 MySQL Workbench： 双

    2024年02月09日
    浏览(68)

  • MySQL数据库——存储过程-变量（系统变量、用户定义变量、局部变量）

    目录 系统变量  1.查看系统变量  2.设置系统变量 演示示例 用户定义变量 1.赋值  2.使用 演示示例 局部变量 声明  赋值 演示示例 变量 在MySQL中变量分为三种类型: 系统变量、用户定义变量、局部变量。 系统变量是MySQL服务器提供，不是用户定义的，属于服务器层面。分为全

    2024年02月05日
    浏览(53)
  • MySQL数据库——存储过程-介绍以及基本语法（特点、创建、调用、查看、删除、示例）

    MySQL数据库——存储过程-介绍以及基本语法（特点、创建、调用、查看、删除、示例）

    目录 介绍 特点 基本语法 创建 调用 查看 删除  示例  存储过程是 事先经过编译 并 存储在数据库 中的一段 SQL 语句的 集合 ，调用存储过程可以简化应用开发人员的很多工作，减少数据在数据库和应用服务器之间的传输，对于提高数据处理的效率是有好处的。 存储过程思想

    2024年02月06日
    浏览(72)
  • 数据库系统原理及MySQL应用教程实验七存储过程与函数的创建管理

    数据库系统原理及MySQL应用教程实验七存储过程与函数的创建管理

    1. 理解存储过程和函数的概念。 2. 掌握创建存储过程和函数的方法。 3. 掌握执行存储过程和函数的方法。 4. 掌握游标的定义、使用方法。 1.验证性实验：某超市的食品管理的数据库的Food表对其操作。 2.设计性试验：学校教师管理数据库中的teacherInfo表对其操作。 三、实验步

    2024年02月03日
    浏览(55)
  • 【Mysql】X-DOC：Mysql数据库大量数据查询加速（定时JOB和存储过程应用案例）

    【Mysql】X-DOC：Mysql数据库大量数据查询加速（定时JOB和存储过程应用案例）

    在某中台系统中，设计了大量的基础数据（维度数据、维度映射关系等）来支撑业务功能，业务表中存在大量的维度外键关联字段，其优点是可以实现前端的选择录入，数据校验，确保录入数据的准确性；缺点是在做业务报表时，需要做大量的维度关联（join）操作。 受限于

    2024年02月12日
    浏览(44)
  • 【MySQL】探索MySQL存储过程的魔力，初学者的数据库编程秘笈（内含实战SQL脚本）

    【MySQL】探索MySQL存储过程的魔力，初学者的数据库编程秘笈（内含实战SQL脚本）

    🧑‍💻作者名称：DaenCode 🎤作者简介：啥技术都喜欢捣鼓捣鼓，喜欢分享技术、经验、生活。 😎人生感悟：尝尽人生百味，方知世间冷暖。 📖所属专栏：重温MySQL MySQL存储过程作为一种服务器端的 数据库编程方式 ，提供了高效、可重用的方法来执行相对复杂的数据库操

    2024年02月15日
    浏览(72)

  • 数据库: 存储过程

    sql server begin end用法: SQL Server中的BEGIN END用法是用于定义一个代码块，这个代码块可以包含多个SQL语句，BEGIN END通常用于控制流程语句，例如IF语句、WHILE语句、TRY CATCH语句等。在BEGIN END代码块中，可以使用变量、函数、存储过程等SQL Server的元素。BEGINEND的语法如下: BEGIN SQL语

    2024年02月09日
    浏览(43)

  • Oracle数据库创建存储过程

    下面是一个迁移数据库数据的存储过程： 存储过程中用到的 while 循环逻辑：  

    2024年02月11日
    浏览(43)
  • 数据库实验6 存储过程实验

    数据库实验6 存储过程实验

    前言：游标的mysql代码不懂写，所以没有运行结果 实验6.1 存储过程实验 1.实验目的 掌握数据库 PL/SQL 编程语言，以及数据库存储过程的设计和使用方法。 2.实验内容和要求 存储过程定义,存储过程运行,存储过程更名,存储过程删除,存储过程的参数传递。掌握PL/SQL编程语言和编

    2024年02月04日
    浏览(55)
  • 进阶数据库系列（十一）：PostgreSQL 存储过程

    进阶数据库系列（十一）：PostgreSQL 存储过程

    前面介绍了 PostgreSQL 数据类型和运算符、常用函数、锁操作、执行计划、视图与触发器相关的知识点，今天我将详细的为大家介绍 PostgreSQL 存储过程相关知识，希望大家能够从中收获多多！如有帮助，请点在看、转发支持一波！！！ 工作中可能会存在业务比较复杂，重复性

    2024年02月03日
    浏览(45)

觉得文章有用就打赏一下文章作者

支付宝扫一扫打赏

博客赞助

微信扫一扫打赏

请作者喝杯咖啡吧~博客赞助

支付宝扫一扫领取红包，优惠每天领

二维码1

领取红包

二维码2

领红包