浅谈mysql数据库技术,轻松玩转存储过程

本文涉及的产品
云数据库 RDS SQL Server,独享型 2核4GB
云数据库 RDS MySQL Serverless,0.5-2RCU 50GB
简介: 存储过程新手指南存储过程(Stored Procedure)一组可编程的函数,是为了完成特定功能的SQL语句集,经编译创建并保存在数据库中,用户可通过指定存储过程的名字并给定参数(需要时)来调用执行。

存储过程新手指南

存储过程(Stored Procedure)

一组可编程的函数,是为了完成特定功能的SQL语句集,经编译创建并保存在数据库中,用户可通过指定存储过程的名字并给定参数(需要时)来调用执行。

为什么要用存储过程(优点)?

  • 对于一些复用性高或者业务复杂的一些操作,封装到一个存储过程中,避免了重复编写SQL造成漏写或错写操作,简化了SQL的调用

  • 批量处理:SQL + 循环,减少流量

  • 数据迁移,数据备份

  • 统一接口,保证数据安全(重点,尤其是银行系统)

相对于Oracle数据库来说,Mysql的存储过程功能相对较弱,运用的较少,在之前的文章中也有提到,Mysql是把存储过程划分为函数体,没有Oracle那样细分。

存储过程的创建与调用

  • 存储过程就是有名字的一段代码,用来完成特定功能的操作

  • 创建的存储过程保存在数据库的数据字典中

创建存储过程


CREATE

[DEFINER = { user | CURRENT_USER }]

PROCEDURE sp_name ([proc_parameter[,...]])

[characteristic ...] routine_body

proc_parameter:

[ IN | OUT | INOUT ] param_name type

characteristic:

COMMENT 'string'

| LANGUAGE SQL

| [NOT] DETERMINISTIC

| { CONTAINS SQL | NO SQL | READS SQL DATA | MODIFIES SQL DATA }

| SQL SECURITY { DEFINER | INVOKER }

routine_body:

Valid SQL routine statement

[begin_label:] BEGIN

[statement_list]

……

END [end_label]

  • 创建数据库,用于示例操作

CREATE DATABASE huafeng_db;

use huafeng_db;

DROP TABLE IF EXISTS `huafeng_db`.`t_scores`;

DROP TABLE IF EXISTS `huafeng_db`.`t_students`;

DROP TABLE IF EXISTS `huafeng_db`.`t_class`;

CREATE TABLE `huafeng_db`.`t_class` (

  `class_id` int(11) NOT NULL,

  `class_name` varchar(32) CHARACTER SET utf8 DEFAULT NULL,

  PRIMARY KEY (`class_id`)

) ENGINE=InnoDB DEFAULT CHARSET=utf8;

INSERT INTO `huafeng_db`.`t_class` (`class_id`, `class_name`) VALUES ('1', '一年级');

INSERT INTO `huafeng_db`.`t_class` (`class_id`, `class_name`) VALUES ('2', '二年级');

INSERT INTO `huafeng_db`.`t_class` (`class_id`, `class_name`) VALUES ('3', '三年级');

INSERT INTO `huafeng_db`.`t_class` (`class_id`, `class_name`) VALUES ('4', '四年级');

INSERT INTO `huafeng_db`.`t_class` (`class_id`, `class_name`) VALUES ('5', '五年级');

INSERT INTO `huafeng_db`.`t_class` (`class_id`, `class_name`) VALUES ('6', '六年级');

CREATE TABLE `t_students` (

  `student_id` int(11) NOT NULL AUTO_INCREMENT,

  `student_name` varchar(32) NOT NULL,

  `sex` int(1) DEFAULT NULL,

  `seq_no` int(11) DEFAULT NULL,

  `class_id` int(11) NOT NULL,

  PRIMARY KEY (`student_id`),

  KEY `class_id` (`class_id`),

  CONSTRAINT `t_students_ibfk_1` FOREIGN KEY (`class_id`) REFERENCES `t_class` (`class_id`)

) ENGINE=InnoDB DEFAULT CHARSET=utf8;

INSERT INTO `huafeng_db`.`t_students`(`student_name`,`sex`,`seq_no`,`class_id`) VALUES('小红',0,1,'1');

INSERT INTO `huafeng_db`.`t_students`(`student_name`,`sex`,`seq_no`,`class_id`) VALUES('小青',0,2,'2');

INSERT INTO `huafeng_db`.`t_students`(`student_name`,`sex`,`seq_no`,`class_id`) VALUES('小明',1,3,'3');

INSERT INTO `huafeng_db`.`t_students`(`student_name`,`sex`,`seq_no`,`class_id`) VALUES('小兰',0,4,'4');

INSERT INTO `huafeng_db`.`t_students`(`student_name`,`sex`,`seq_no`,`class_id`) VALUES('小米',1,5,'5');

INSERT INTO `huafeng_db`.`t_students`(`student_name`,`sex`,`seq_no`,`class_id`) VALUES('小白',1,6,'6');

CREATE TABLE `huafeng_db`.`t_scores` (

  `score_id` int(11) NOT NULL AUTO_INCREMENT,

  `course_name` varchar(64) DEFAULT NULL,

  `score` double(3,2) DEFAULT NULL,

  `student_id` int(11) DEFAULT NULL,

  PRIMARY KEY (`score_id`),

  KEY `student_id` (`student_id`),

  CONSTRAINT `t_scores_ibfk_1` FOREIGN KEY (`student_id`) REFERENCES `t_students` (`student_id`)

) ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=utf8;

INSERT INTO `t_scores` (`score_id`, `course_name`, `score`, `student_id`) VALUES ('1', '语文', '90', '1');

INSERT INTO `t_scores` (`score_id`, `course_name`, `score`, `student_id`) VALUES ('2', '数学', '97', '1');

INSERT INTO `t_scores` (`score_id`, `course_name`, `score`, `student_id`) VALUES ('3', '英语', '95', '1');

INSERT INTO `t_scores` (`score_id`, `course_name`, `score`, `student_id`) VALUES ('4', '语文', '92', '2');

INSERT INTO `t_scores` (`score_id`, `course_name`, `score`, `student_id`) VALUES ('5', '数学', '100', '2');

INSERT INTO `t_scores` (`score_id`, `course_name`, `score`, `student_id`) VALUES ('6', '英语', '98', '2');

示例:创建一个存储过程,根据学生编号删除学生信息


DELIMITER $$ #将语句的结束符号从分号;临时改为两个$$(可以是自定义)

CREATE PROCEDURE p_delstudent_by_no(IN seqNo INT)

BEGIN

DELETE FROM t_students WHERE seq_no = seqNo;

END $$

DELIMITER ; #将语句的结束符号恢复为分号

注: 在定义过程中,使用DELIMITER $$ 命令将语句的结束符号从分号 ; 临时改为两个$$,使得过程体中使用的分号被直接传递到服务器,而不会被客户端(如mysql)解释。

调用存储过程:call sp_name[(传参)]


mysql> select *from t_students;

+------------+--------------+------+--------+----------+

| student_id | student_name | sex  | seq_no | class_id |

+------------+--------------+------+--------+----------+

|          1 | 小红        |    0 |      1 |        1 |

|          2 | 小青        |    0 |      2 |        2 |

|          3 | 小明        |    1 |      3 |        3 |

|          4 | 小兰        |    0 |      4 |        4 |

|          5 | 小米        |    1 |      5 |        5 |

|          6 | 小白        |    1 |      6 |        6 |

+------------+--------------+------+--------+----------+

6 rows in set (0.00 sec)

mysql> call p_delstudent_by_no(2);

Query OK, 1 row affected (0.01 sec)

mysql> select *from t_students;

+------------+--------------+------+--------+----------+

| student_id | student_name | sex  | seq_no | class_id |

+------------+--------------+------+--------+----------+

|          1 | 小红        |    0 |      1 |        1 |

|          3 | 小明        |    1 |      3 |        3 |

|          4 | 小兰        |    0 |      4 |        4 |

|          5 | 小米        |    1 |      5 |        5 |

|          6 | 小白        |    1 |      6 |        6 |

+------------+--------------+------+--------+----------+

5 rows in set (0.00 sec)

注: 在存储过程中设置了需要传入的参数seqNo,在调用存储过程时,把2传参到seqNo,然后执行存储过程的SQL操作。

存储过程体

  • 存储过程体包含了在过程调用时必须执行的语句,例如:DML、DDL语句,IF-THEN-ELSE和WHILE-DO语句、声明变量的DECLARE语句等

  • 过程体格式:以BEGIN开始,以END结束(可嵌套)


BEGIN

BEGIN

BEGIN

#业务操作

END

END

END

注: 每个嵌套块及其中的每条语句,必须以分号结束,表示过程体结束的BEGIN-END块(又叫做复合语句compound statement),则不需要分号。

标签化


[begin_label:] BEGIN

[statement_list]

END [end_label]

  • 给每个过程体命名,以增强代码的可读性,示例:

label1:BEGIN

label2:BEGIN

label3:BEGIN

#业务操作

END label3;

END label2;

END label1

存储过程参数 (proc_parameter)

在存储过程创建的时候,参数可有0到多个,参数的属性可分为以下3种:

  • IN 输入参数:表示调用者向存储过程传入值(传入值可以是字面量或变量)

  • OUT 输出参数:表示过程向调用者传出值(可以返回多个值)(传出值只能是变量)

  • INOUT输入输出参数:既表示调用者向过程传入值,又表示过程向调用者传出值(值只能是变量)

IN 输入参数


mysql> DELIMITER $$

mysql> CREATE PROCEDURE p_prinlt(IN num INT)

->  BEGIN

->    SELECT num;

->  END $$

Query OK, 0 rows affected (0.01 sec)

mysql>  DELIMITER ;

mysql> call p_prinlt(2);

+------+

| num  |

+------+

|    2 |

+------+

OUT 输出参数


mysql> delimiter $$

mysql> create procedure out_param(out result int)

-> begin

-> set result = 2;

-> select result;

-> end $$

Query OK, 0 rows affected (0.00 sec)

mysql> delimiter ;

mysql> set @result = 1;

Query OK, 0 rows affected (0.00 sec)

mysql> call out_param(@result);

+--------+

| result |

+--------+

|      2 |

+--------+

INOUT 输入输出参数


mysql> delimiter $$

mysql> create procedure inout_param(inout p_inout int)

->  begin

->    select p_inout;

->    set p_inout=2;

->    select p_inout;

->  end

-> $$

mysql> delimiter ;

mysql> set @p_inout=1;

mysql> call inout_param(@p_inout);

+---------+

| p_inout |

+---------+

|      1 |

+---------+

+---------+

| p_inout |

+---------+

|      2 |

+---------+

mysql> select @p_inout;

+----------+

| @p_inout |

+----------+

|        2 |

+----------+

注: 在设计传参时应注意确保参数名字不能与数据库表列名相同,否则在过程体中,参数名被当做列名来处理

  • 作为一个追求完美的人,我们都应该做到专词专用,传入参数使用 IN ,传出参数使用 OUT ,尽量不用INOUT

高手进阶

前面我们已经初步了解了存储过程的创建与调用,但是我们编写存储过程的目的是为了解决一些复用性高或者业务复杂的一些操作;

统一接口,保证数据安全等。所以需要使用事务管理 DML、DDL语句,IF-THEN-ELSE和WHILE-DO语句、声明变量的DECLARE语句等

全局变量

全局变量可用于初始化一些数据,作为公共资源,使用方法如下:


mysql> set @num = 2;

mysql> DELIMITER $$

mysql> CREATE PROCEDURE p_prinlt(IN num INT)

->  BEGIN

->    SELECT num;

->  END $$

Query OK, 0 rows affected (0.01 sec)

mysql>  DELIMITER ;

mysql> call p_prinlt(@num);

+------+

| num  |

+------+

|    2 |

+------+

注: 设置全局参数名字一定要带 @ 前缀

声明语句(DECLARE)

对于一些复杂的业务操作,过程体需要经常用到相同的变量时,则需要声明一个或多个局部变量以满足业务需求,使用方法如下:


DELIMITER $$

CREATE PROCEDURE p_get_classId(IN seqNo int)

BEGIN

    DECLARE classId INT;

    DECLARE result VARCHAR(256) CHARACTER set utf8;#解决中文乱码问题

set classId = (SELECT class_id FROM t_students WHERE seq_no = seqNo);

SELECT classId;

END $$

事务机制(TRANSACTION)

为了保证数据的安全,以及接口的统一,防止脏数据产生,这样的存储过程都应该使用事务。

银行转账就是其中的经典例子,需要双方都成功才会提交事务,否则回滚(ROLLBACK)

下面我们用之前创建好的数据库作为例子,编写一个根据学生编号删除学生的学习成绩,并把对应的学生的信息删除,这样的一个存储过程。方法如下:


DELIMITER $$

CREATE PROCEDURE p_del_studentInfo_bySeqNo(IN seqNo INT)

BEGIN

    DECLARE studentId INT;

START TRANSACTION;#启动事务

set studentId = (SELECT student_id FROM t_students WHERE seq_no = seqNo);

DELETE FROM t_scores WHERE student_id = studentId;

DELETE FROM t_students WHERE student_id = studentId;

COMMIT ;#提交事务

END $$

DELIMITER ;

条件判断语句

在存储过程中,条件判断语句是必不可少的,经常使用的有IF-THEN-ELSE和WHILE-DO语句,条件语句可以减少很多数据库不必要的内存开销,

现在我们来优化一下上面写的删除学生信息的存储过程,代码如下:


DROP PROCEDURE IF EXISTS `p_del_studentInfo_bySeqNo`;

DELIMITER $$

CREATE PROCEDURE p_del_studentInfo_bySeqNo(IN seqNo INT)

BEGIN

    DECLARE studentId INT;

START TRANSACTION;#启动事务

set studentId = (SELECT student_id FROM t_students WHERE seq_no = seqNo);

IF studentId >0 THEN

DELETE FROM t_scores WHERE student_id = studentId;

DELETE FROM t_students WHERE student_id = studentId;

COMMIT ;#提交事务

ELSE

ROLLBACK;#回滚

END IF;

END $$

DELIMITER ;

注: IF条件判断语句一定是以 “END IF; ”结束的,中间可以有多层条件判断。格式:IF 条件体 THEN 执行业务结果 ELSE 执行业务结果 END IF

异常处理(SQLEXCEPTION)

每个存储过程都应该有报错处理的能力,我们还是使用上面的存储过程进行优化,使用方法如下:


DROP PROCEDURE IF EXISTS `p_del_studentInfo_bySeqNo`;

DELIMITER $$

CREATE PROCEDURE p_del_studentInfo_bySeqNo(IN seqNo INT)

BEGIN

    DECLARE studentId INT;

    DECLARE e_code INTDEFAULT 0;#初始化报错码为0

    DECLARE result VARCHAR(256) CHARACTER set utf8;#初始化返回结果,解决中文乱码问题

DECLARE CONTINUE HANDLER FOR SQLEXCEPTION SET e_code=1;#遇到错误后继续执行;(需要返回执行结果时用这个)

START TRANSACTION;#启动事务

set studentId = (SELECT student_id FROM t_students WHERE seq_no = seqNo);

IF studentId >0 THEN

DELETE FROM t_scores WHERE student_id = studentId;

DELETE FROM t_students WHERE student_id = studentId;

ELSE

SET e_code = 1;

SET result = '该学生不存在!';

END IF;

IF e_code=1 THEN

ROLLBACK;  #回滚

ELSE

COMMIT;

SET result = '该学生已被删除成功';

END IF;

SELECT result;

END $$

DELIMITER ;

  • 定义报错代码变量名并设置初始化值为0 DECLARE e_code INT DEFAULT 0;

  • 定义化返回结果变量名并设置编码为utf-8 DECLARE result VARCHAR(256) CHARACTER set utf8;

  • DECLARE CONTINUE HANDLER FOR SQLEXCEPTION SET e_code=1; 遇到错误后继续执行;(需要返回执行结果时用这个)

  • DECLARE EXIT HANDLER FOR SQLEXCEPTION SET e_code=1;遇到错误后退出当前块;

声明该存储过程的使用对象(SQL SECURITY INVOKER)

在创建存储过程的时候,默认是当前用户可使用所有权限,其他用户不能使用或者不能执行存储过程中的DML语句,为了使其他用户也能使用该存储过程

则需要在创建的时候声明一下,使用方法如下:


DROP PROCEDURE IF EXISTS `p_del_studentInfo_bySeqNo`;

DELIMITER $$

CREATE PROCEDURE p_del_studentInfo_bySeqNo(IN seqNo INT)

SQL SECURITY INVOKER  #允许其他用户运行

BEGIN

    DECLARE studentId INT;

    DECLARE e_code INTDEFAULT 0;#初始化报错码为0

    DECLARE result VARCHAR(256) CHARACTER set utf8;#初始化返回结果,解决中文乱码问题

DECLARE CONTINUE HANDLER FOR SQLEXCEPTION SET e_code=1;#遇到错误后继续执行;(需要返回执行结果时用这个)

START TRANSACTION;#启动事务

set studentId = (SELECT student_id FROM t_students WHERE seq_no = seqNo);

IF studentId >0 THEN

DELETE FROM t_scores WHERE student_id = studentId;

DELETE FROM t_students WHERE student_id = studentId;

ELSE

SET e_code = 1;

SET result = '该学生不存在!';

END IF;

IF e_code=1 THEN

ROLLBACK;  #回滚

ELSE

COMMIT;

SET result = '该学生已被删除成功';

END IF;

SELECT result;

END $$

DELIMITER ;

拓展

习惯性建议

  • 默认情况下,存储过程和默认数据库相关联,如果想指定存储过程创建在某个特定的数据库下,那么在过程名前面加数据库名做前缀;

  • 代码尽量简洁,有序,该缩进的地方使用缩进

  • 过程体有必要的时候尽量采用标签

  • 存储过程传参数时尽量使用对应的名词,尽量不用INOUT

  • 每次创建表或者函数和存储过程的时候尽量先判断是否存在再删除以免造成报错,采用 DROP … IF EXISTS …格式

相关实践学习
基于CentOS快速搭建LAMP环境
本教程介绍如何搭建LAMP环境,其中LAMP分别代表Linux、Apache、MySQL和PHP。
全面了解阿里云能为你做什么
阿里云在全球各地部署高效节能的绿色数据中心,利用清洁计算为万物互联的新世界提供源源不断的能源动力,目前开服的区域包括中国(华北、华东、华南、香港)、新加坡、美国(美东、美西)、欧洲、中东、澳大利亚、日本。目前阿里云的产品涵盖弹性计算、数据库、存储与CDN、分析与搜索、云通信、网络、管理与监控、应用服务、互联网中间件、移动服务、视频服务等。通过本课程,来了解阿里云能够为你的业务带来哪些帮助     相关的阿里云产品:云服务器ECS 云服务器 ECS(Elastic Compute Service)是一种弹性可伸缩的计算服务,助您降低 IT 成本,提升运维效率,使您更专注于核心业务创新。产品详情: https://www.aliyun.com/product/ecs
目录
相关文章
|
16小时前
|
存储 人工智能 关系型数据库
新一代数据库技术:面向未来的数据存储解决方案
【2月更文挑战第2天】 随着信息时代的快速发展,数据量呈指数级增长,传统关系型数据库已经难以满足大规模数据处理的需求。本文将介绍新一代数据库技术的特点和优势,探讨其在面向未来数据存储解决方案中的应用前景。
|
1天前
|
存储 机器学习/深度学习 NoSQL
现代化数据库技术应用与发展
在当今信息爆炸的时代,数据库技术作为信息存储和管理的核心工具,扮演着越来越重要的角色。本文将探讨现代化数据库技术的应用与发展,介绍了一些最新的数据库技术趋势和应用案例,深入剖析了数据库技术在大数据、云计算以及人工智能等领域的发展前景。
|
2天前
|
存储 NoSQL 关系型数据库
现代数据库技术的发展与应用
本文将探讨现代数据库技术的发展趋势和广泛应用领域。我们将从传统关系型数据库开始,介绍NoSQL数据库、分布式数据库以及最新的图数据库等技术,探讨它们的特点和优势。此外,我们还将讨论数据库在大数据、云计算和物联网等领域中的应用案例,并展望未来数据库技术的发展方向。
|
2天前
|
监控 Oracle 关系型数据库
Flink CDC(Change Data Capture)是一种用于捕获数据库变更的技术
Flink CDC(Change Data Capture)是一种用于捕获数据库变更的技术
19 8
|
3天前
|
存储 SQL 关系型数据库
[MySQL]存储过程
[MySQL]存储过程
36 0
[MySQL]存储过程
|
3天前
|
人工智能 大数据 数据处理
未来数据库技术发展趋势及挑战
随着信息时代的快速发展,数据库技术也在不断创新和进步。本文将探讨未来数据库技术可能面临的挑战,并提出应对之策,以期帮助读者更好地了解数据库技术的发展方向。
|
14天前
|
存储 关系型数据库 MySQL
MySQL 中的存储过程-3
MySQL 中的存储过程
15 0
|
14天前
|
存储 SQL 关系型数据库
MySQL 中的存储过程-2
MySQL 中的存储过程
8 0
|
14天前
|
存储 关系型数据库 MySQL
MySQL 中的存储过程-1
MySQL 中的存储过程
32 2
|
15天前
|
存储 SQL 关系型数据库
MySQL技能完整学习列表7、存储过程和函数——1、存储过程(Stored Procedures)的创建和执行——2、函数(Functions)的创建和使用
MySQL技能完整学习列表7、存储过程和函数——1、存储过程(Stored Procedures)的创建和执行——2、函数(Functions)的创建和使用
26 0

热门文章

最新文章