MySQL存储过程了解一下

MySQL存储过程了解一下简介 存储过程(Stored Procedure)是在大型数据库系统中,一组为了完成特定功能的SQL 语句集,它存储在数据库中,一次编译后永久有效,用户通过指定存储过程的名字并给出参数(如果该存储过程

MySQL存储过程了解一下

简介

存储过程(Stored Procedure)是在大型数据库系统中,一组为了完成特定功能的SQL 语句集,它存储在数据库中,一次编译后永久有效,用户通过指定存储过程的名字并给出参数(如果该存储过程带有参数)来执行它。存储过程是数据库中的一个重要对象。

存储过程的使用

创建存储过程

语法:

CREATE PROCEDURE 存储过程名( IN|OUT|INOUT 参数名 数据类型 , ...)
BEGIN
	...
END;

MySQL存储过程的参数类型:

  1. IN,表示存储过程的输入参数,该参数的值将会传递给存储过程,在存储过程中可以对该参数进行修改,但是在存储过程返回时,该参数值不会被返回,相当于在存储过程中对该参数的修改对调用者来说是不可见的。

  2. OUT,表示存储过程的输入参数,该参数的值会在存储过程中初始化为NULL,当存储过程返回时,该值也会被返回,调用者可以看到被修改后的值。

  3. INOUT,表示存储过程的输入输出参数,该参数由调用者初始化,在存储过程中的做的任何更改都会被返回,调用者可以看到修改后的值。

存储过程创建示例:

创建存储过程student_procedure,student_procedure有一个输入参数age和一个输出参数num,查询tb_student表学生年龄大于等于输入参数age的人数,并将人数设置到num。

CREATE PROCEDURE student_procedure(IN age TINYINT, OUT num INT)
BEGIN
	SELECT COUNT(*) INTO num FROM tb_student t WHERE t.age>=age;
END;

MySQL命令行创建存储过程:

如果是在MySQL命令行创建存储过程,则需要临时的修改语句分隔符,因为MySQL默认语句分隔符是;,会使存储过程中的语句被直接解析而导致语法错误。

-- 设置//为语句分隔符
mysql> DELIMITER //

mysql> CREATE PROCEDURE student_procedure(IN age TINYINT, OUT num INT)
    -> BEGIN
    -> SELECT COUNT(*) INTO num FROM tb_student t WHERE t.age>=age;
    -> END;
    -> //
Query OK, 0 rows affected

--恢复为原来的分隔符
mysql> DELIMITER ;
调用存储过程

tb_student表数据:

+----+------+-----+-------------+-----------+----------+
| id | name | age | phone       | address   | class_id |
+----+------+-----+-------------+-----------+----------+
|  1 | 小明 |  18 | 188xxxx1234 | xxxxxxxxx |        1 |
|  2 | 小米 |  28 | 188xxxx1234 | xxxxxxxxx |        2 |
|  3 | 小看 |  28 | 188xxxx1234 | xxxxxxxxx |        3 |
|  4 | 小阿 |  38 | 188xxxx1234 | xxxxxxxxx |        3 |
|  5 | 小鬼 |  48 | 188xxxx1234 | xxxxxxxxx |        3 |
+----+------+-----+-------------+-----------+----------+

调用存储过程,查询年龄大于38的学生人数:

-- 调用存储过程
mysql> CALL student_procedure(38, @num);
Query OK, 1 row affected

-- 查看返回结果
mysql> select @num;
+------+
| @num |
+------+
|    2 |
+------+
查看存储过程的定义

语法:

SHOW CREATE PROCEDURE proc_name;

如查看student_procedure的定义:

mysql> SHOW CREATE PROCEDURE student_procedure;
+-------------------+----------------------------------------------------------------+------------------------------------------------------------------------------------------------------------------------------------------------------------------+----------------------+----------------------+--------------------+
| Procedure         | sql_mode                                                       | Create Procedure                                                                                                                                                 | character_set_client | collation_connection | Database Collation |
+-------------------+----------------------------------------------------------------+------------------------------------------------------------------------------------------------------------------------------------------------------------------+----------------------+----------------------+--------------------+
| student_procedure | STRICT_TRANS_TABLES,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION | CREATE DEFINER=`root`@`localhost` PROCEDURE `student_procedure`(IN age TINYINT, OUT num INT) BEGIN SELECT COUNT(*) INTO num FROM tb_student t WHERE t.age>=age;END | utf8                 | utf8_general_ci      | utf8_general_ci    |
+-------------------+----------------------------------------------------------------+------------------------------------------------------------------------------------------------------------------------------------------------------------------+----------------------+----------------------+--------------------+
修改存储过程

语法:

ALTER PROCEDURE proc_name [characteristic ...]

characteristic: {
    COMMENT "string"
  | LANGUAGE SQL
  | { CONTAINS SQL | NO SQL | READS SQL DATA | MODIFIES SQL DATA }
  | SQL SECURITY { DEFINER | INVOKER }
}

ALTER PROCEDURE 语句用于修改存储过程的某些特征。如果要修改存储过程的内容,可以先删除原存储过程,再以相同的命名创建新的存储过程。

删除存储过程

语法:

DROP PROCEDURE [ IF EXISTS ] proc_name

如删除student_procedure:

mysql> DROP PROCEDURE student_procedure;
Query OK, 0 rows affected

mysql> CALL student_procedure(38, @num);
1305 - PROCEDURE student_procedure does not exist

流程控制语句

IF语句

语法:

IF 判断条件 THEN 处理语句
    [ELSEIF 判断条件 THEN 处理语句]...
    [ELSE 处理语句]
END IF

示例:

CREATE PROCEDURE test1(IN sex TINYINT)
BEGIN
	IF sex=1 THEN SET @sex="男";
	ELSEIF sex=0 THEN SET @sex="女";
	ELSE SET @sex="未知";
	END IF;
END;
CASE语句

CASE语句有两种写法:

语法1:

CASE 值
    WHEN 值1 THEN 处理语句
    [WHEN 值2 THEN 处理语句]...
    [ELSE 处理语句]
END CASE

语法2:

CASE
    WHEN 条件判断 THEN 处理语句
    [WHEN 条件判断 THEN 处理语句] ...
    [ELSE 处理语句]
END CASE

示例:

-- 写法1
CREATE PROCEDURE test2(IN sex TINYINT)
BEGIN
	CASE sex
	WHEN 1 THEN SET @sex="男";
	WHEN 0 THEN SET @sex="女";
	ELSE SET @sex="未知";
	END CASE;
END;

-- 写法2
CREATE PROCEDURE test3(IN sex TINYINT)
BEGIN
	CASE 
	WHEN sex=1 THEN SET @sex="男";
	WHEN sex=0 THEN SET @sex="女";
	ELSE SET @sex="未知";
	END CASE;
END;
LOOP语句

LOOP循环是一个死循环,一般情况需要配合LEAVE语句和ITERATE语句使用,LEAVE语句表示跳出该循环(类似Java中的break),ITERATE语句表示跳出本次循环(类似Java中的continue)。

语法:

[别名:]LOOP
    处理逻辑
END LOOP [别名]

示例:

CREATE PROCEDURE test4()
BEGIN
	SET @num=0;
	add_num:LOOP
		SET @num=@num+1;
		IF @num=10 THEN LEAVE add_num;
		END IF;
	END LOOP add_num;
END;
REPEAT语句

REPEAT语句是自带条件判断的循环语句,每次语句执行完毕后,会对条件进行判断,如果为true则退出循环,否则继续循环。(类似Java中的do while循环)

语法:

[别名:] REPEAT
    处理语句
    UNTIL 条件判断
END REPEAT [别名]

示例:

CREATE PROCEDURE test5()
BEGIN
	SET @num=0;
	add_num:REPEAT
		SET @num=@num+1;
		UNTIL @num=10 END REPEAT add_num;
END;
WHILE语句

WHILE语句也是自带条件判断的循环,和REPEAT语句的区别在于WHILE语句会先进行条件判断,当条件判断为true时才继续执行循环中的语句,为false则直接退出循环。(类似于Java中的while循环)

语法:

[别名:] WHILE 条件判断 DO
    处理逻辑
END WHILE [别名]

示例:

CREATE PROCEDURE test6()
BEGIN
	SET @num=0;
	add_num:WHILE @num<10 DO
		SET @num=@num+1;
		END WHILE add_num;
END;

游标

游标是用来逐行处理某个查询的结果集。

游标的声明必须出现在HANDLER声明之前声明,变量和条件声明之后声明

创建游标:
DECLARE 游标名称 CURSOR FOR sql查询;
打开游标:
OPEN 游标名称;
使用游标:
FETCH 游标名称 INTO 变量1 [,变量2]...

将结果集中的数据保存到对应的变量当中去,游标第一次使用时默认读取结果集中的第一行,一般配合循环语句逐行处理整个结果集。

关闭游标:
CLOSE 游标名称;

CLOSE释放游标使用的所有内部内存和资源,因此每个游标不再需要时都应该关闭。游标关闭后不能使用,如果需要使用则需要重新打开游标。

示例

查询tb_student表,将所有学生名称连接成一个字符串设置到变量@name_Str中。

CREATE PROCEDURE test7()
BEGIN
	-- 声明局部变量student_name,用于接收数据集中的数据
	DECLARE student_name VARCHAR(10);
	-- 声明局部变量done,用于判断是否退出循环,默认值为FALSE
	DECLARE done INT DEFAULT FALSE;
	-- 声明游标my_cursor
	DECLARE my_cursor CURSOR FOR SELECT `name` FROM tb_student;
	-- 声明continue handler句柄,当出现SQLSTATE "02000"时将done设置为TRUE
	DECLARE CONTINUE HANDLER FOR SQLSTATE "02000" SET done = TRUE;
	
	-- 设置用户变量@name_Str为空字符串
	SET @name_Str="";
	-- 打开游标
	OPEN my_cursor;
		-- 开始LOOP循环
		concat_name:LOOP
			-- 将数据集中的一行数据存放到指定的变量中
			FETCH my_cursor INTO student_name;
			-- 判断是否退出循环
			IF done THEN LEAVE concat_name;
			END IF;
			-- 连接学生名称字符串
			SET @name_Str = CONCAT(@name_Str,student_name);	
		END LOOP concat_name;
	-- 关闭游标	
	CLOSE my_cursor;
END;

结果:

mysql> call test7();
Query OK, 0 rows affected

mysql> select @name_Str;
+----------------------+
| @name_Str            |
+----------------------+
| 小明小米小看小阿小鬼 |
+----------------------+
关于SQLSTATE “02000”

在使用游标时,可以通过FETCH将数据集中的数据保存到变量中进行处理,但是当整个数据集已经FETCH结束的时候,再去FETCH就会抛异常:

1329 - No data - zero rows fetched, selected, or processed

该异常对应的SQLSTATE为02000,所以需要指定句柄捕获这种异常情况来给标志赋值,后续就可以通过这个标志来判断数据集循环读取结束。

异常信息详见:Error Reference

版权声明:本文内容由互联网用户自发贡献,该文观点仅代表作者本人。本站仅提供信息存储空间服务,不拥有所有权,不承担相关法律责任。如发现本站有涉嫌侵权/违法违规的内容, 请发送邮件至 举报,一经查实,本站将立刻删除。
转载请注明出处: https://daima100.com/6424.html

(0)
上一篇 2023-04-10 11:30
下一篇 2023-04-10

相关推荐

  • mysql数据超出范围的处理方法_表格数据超出范围怎么设置

    mysql数据超出范围的处理方法_表格数据超出范围怎么设置MySQL默认情况下,当一个数据超出定义列的数据类型的范围时,数据会以数据类型所充许的最大值存储。 例: 1. 创建表 create table t1 ( `num` int not null); …

    2022-12-23
    144
  • Python文件句柄的关闭方法

    Python文件句柄的关闭方法Python中使用with语句可以更加简洁地处理文件句柄的关闭,即使在发生错误的情况下也会自动关闭句柄。with语句可以将文件句柄的创建、使用和关闭全部包含在内,代码如下:

    2024-04-03
    78
  • 巨杉数据库公司_巨杉数据库待遇

    巨杉数据库公司_巨杉数据库待遇近期,巨杉数据库与浪潮商用完成技术兼容性测试,正式发布了相互认证证书。 双方产品在兼容性、稳定性、安全性上表现良好,运行流畅。此次兼容性测试和认证工作,帮助双方在技术生态拓展上迈出了坚实一步,能够共…

    2023-03-13
    146
  • 用Python获取列表元素下标的技巧

    用Python获取列表元素下标的技巧在Python中,列表是最常用的数据结构之一。而在处理列表的过程中,经常需要获取某一个元素的下标位置。本文将阐述用Python获取列表元素下标的技巧,帮助读者更高效地处理列表操作。

    2024-07-10
    44
  • vlan划分和access端口,trunk端口,hybrid端口的配置[亲测有效]

    vlan划分和access端口,trunk端口,hybrid端口的配置[亲测有效]#一.华为交换机上的端口类型access接口,用于连接终端设备,access接口只允许一个vlan的流量通过。trunk接口,用于连接不通交换设备,trunk接口可以允许多个vlan的流量通过。hy…

    2023-03-30
    143
  • 安装anaconda3教程

    安装anaconda3教程Anaconda是一个全平台的Python发行版,包括conda,python等工具,是python语言的集成环境。Anaconda3是包含了Python3.x版本和各种常用库的一个安装包,下面是Anaconda3的安装步骤。

    2024-07-11
    43
  • 故障分析 | binlog flush 失败导致的 Crash

    故障分析 | binlog flush 失败导致的 Crash作者:xuty 开个坑,记录自己平时由于解决问题需要或是兴趣研究进行的 MySQL 源码跟踪学习过程。 一、问题现象 某项目上出现 MySQL Crash,相关 errorlog 日志如下,从日志可…

    2023-01-30
    162
  • MySQL日志管理详解_MySQL binlog

    MySQL日志管理详解_MySQL binlog概述 日志文件记录 MySQL 数据库运行期间发生的变化,当数据库遭到意外的损害时,可以通过日志文件查询出错原因,并进件数据恢复 MySQL 日志文件可以分成以下几类: 二进制日志:记录所有更改数据的

    2023-05-14
    144

发表回复

您的电子邮箱地址不会被公开。 必填项已用*标注