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

本文涉及的产品
云数据库 RDS MySQL,集群系列 2核4GB
推荐场景:
搭建个人博客
RDS MySQL Serverless 基础系列,0.5-2RCU 50GB
云数据库 RDS PostgreSQL,集群系列 2核4GB
简介: 存储过程新手指南存储过程(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 …格式

相关实践学习
如何在云端创建MySQL数据库
开始实验后,系统会自动创建一台自建MySQL的 源数据库 ECS 实例和一台 目标数据库 RDS。
全面了解阿里云能为你做什么
阿里云在全球各地部署高效节能的绿色数据中心,利用清洁计算为万物互联的新世界提供源源不断的能源动力,目前开服的区域包括中国(华北、华东、华南、香港)、新加坡、美国(美东、美西)、欧洲、中东、澳大利亚、日本。目前阿里云的产品涵盖弹性计算、数据库、存储与CDN、分析与搜索、云通信、网络、管理与监控、应用服务、互联网中间件、移动服务、视频服务等。通过本课程,来了解阿里云能够为你的业务带来哪些帮助     相关的阿里云产品:云服务器ECS 云服务器 ECS(Elastic Compute Service)是一种弹性可伸缩的计算服务,助您降低 IT 成本,提升运维效率,使您更专注于核心业务创新。产品详情: https://www.aliyun.com/product/ecs
目录
相关文章
|
1月前
|
数据库 索引
深入探索数据库索引技术:回表与索引下推解析
【10月更文挑战第15天】在数据库查询优化的领域中,回表和索引下推是两个核心概念,它们对于提高查询性能至关重要。本文将详细解释这两个术语,并探讨它们在数据库操作中的作用和影响。
50 3
|
1月前
|
负载均衡 网络协议 数据库
选择适合自己的数据库多实例负载均衡技术
【10月更文挑战第23天】选择适合自己的数据库多实例负载均衡技术需要全面考虑多种因素。通过深入的分析和评估,结合自身的实际情况,能够做出明智的决策,为数据库系统的高效运行提供有力保障。
111 61
|
6天前
|
JavaScript 安全 Java
java版药品不良反应智能监测系统源码,采用SpringBoot、Vue、MySQL技术开发
基于B/S架构,采用Java、SpringBoot、Vue、MySQL等技术自主研发的ADR智能监测系统,适用于三甲医院,支持二次开发。该系统能自动监测全院患者药物不良反应,通过移动端和PC端实时反馈,提升用药安全。系统涵盖规则管理、监测报告、系统管理三大模块,确保精准、高效地处理ADR事件。
|
23天前
|
监控 前端开发 Java
【技术开发】接口管理平台要用什么技术栈?推荐:Java+Vue3+Docker+MySQL
该文档介绍了基于Java后端和Vue3前端构建的管理系统的技术栈及功能模块,涵盖管理后台的访问、登录、首页概览、API接口管理、接口权限设置、接口监控、计费管理、账号管理、应用管理、数据库配置、站点配置及管理员个人设置等内容,并提供了访问地址及操作指南。
|
27天前
|
监控 关系型数据库 MySQL
MySQL自增ID耗尽应对策略:技术解决方案全解析
在数据库管理中,MySQL的自增ID(AUTO_INCREMENT)属性为表中的每一行提供了一个唯一的标识符。然而,当自增ID达到其最大值时,如何处理这一情况成为了数据库管理员和开发者必须面对的问题。本文将探讨MySQL自增ID耗尽的原因、影响以及有效的应对策略。
90 3
|
1月前
|
SQL Java 数据库连接
在Java应用中,数据库访问常成为性能瓶颈。连接池技术通过预建立并复用数据库连接,有效减少连接开销,提升访问效率
在Java应用中,数据库访问常成为性能瓶颈。连接池技术通过预建立并复用数据库连接,有效减少连接开销,提升访问效率。本文介绍了连接池的工作原理、优势及实现方法,并提供了HikariCP的示例代码。
48 3
|
1月前
|
缓存 负载均衡 监控
数据库多实例的负载均衡技术深入
【10月更文挑战第23天】数据库多实例负载均衡技术是确保数据库系统高效运行的重要手段。通过合理选择负载均衡策略、实时监控实例状态、不断优化调整,能够实现资源的最优分配和系统性能的提升。在实际应用中,需要根据具体情况灵活运用各种负载均衡技术,并结合其他相关技术,以满足不断变化的业务需求。
|
1月前
|
Java 数据库连接 数据库
优化之路:Java连接池技术助力数据库性能飞跃
在Java应用开发中,数据库操作常成为性能瓶颈。频繁的数据库连接建立和断开增加了系统开销,导致性能下降。本文通过问题解答形式,深入探讨Java连接池技术如何通过复用数据库连接,显著减少连接开销,提升系统性能。文章详细介绍了连接池的优势、选择标准、使用方法及优化策略,帮助开发者实现数据库性能的飞跃。
30 4
|
1月前
|
Java 数据库连接 数据库
深入探讨Java连接池技术如何通过复用数据库连接、减少连接建立和断开的开销,从而显著提升系统性能
在Java应用开发中,数据库操作常成为性能瓶颈。本文通过问题解答形式,深入探讨Java连接池技术如何通过复用数据库连接、减少连接建立和断开的开销,从而显著提升系统性能。文章介绍了连接池的优势、选择和使用方法,以及优化配置的技巧。
31 1
|
1月前
|
存储 SQL NoSQL