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

存储过程新手指南

存储过程(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 …格式

最后编辑于 ：
©著作权归作者所有,转载或内容合作请联系作者
  • 人面猴
    序言：七十年代末，一起剥皮案震惊了整个滨河市，随后出现的几起案子，更是在滨河造成了极大的恐慌，老刑警刘岩，带你破解...
    沈念sama阅读 161,601评论 4赞 369
  • 死咒
    序言：滨河连续发生了三起死亡事件，死亡现场离奇诡异，居然都是意外死亡，警方通过查阅死者的电脑和手机，发现死者居然都...
    沈念sama阅读 68,367评论 1赞 305
  • 救了他两次的神仙让他今天三更去死
    文/潘晓璐 我一进店门，熙熙楼的掌柜王于贵愁眉苦脸地迎上来，“玉大人，你说我怎么就摊上这事。” “怎么了？”我有些...
    开封第一讲书人阅读 111,249评论 0赞 254
  • 道士缉凶录：失踪的卖姜人
    文/不坏的土叔 我叫张陵，是天一观的道长。 经常有香客问我，道长，这世上最难降的妖魔是什么？ 我笑而不...
    开封第一讲书人阅读 44,539评论 0赞 217
  • 港岛之恋（遗憾婚礼）
    正文 为了忘掉前任，我火速办了婚礼，结果婚礼上，老公的妹妹穿的比我还像新娘。我一直安慰自己，他们只是感情好，可当我...
    茶点故事阅读 52,967评论 3赞 295
  • 恶毒庶女顶嫁案：这布局不是一般人想出来的
    文/花漫 我一把揭开白布。 她就那样静静地躺着，像睡着了一般。 火红的嫁衣衬着肌肤如雪。 梳的纹丝不乱的头发上，一...
    开封第一讲书人阅读 40,929评论 1赞 224
  • 城市分裂传说
    那天，我揣着相机与录音，去河边找鬼。 笑死，一个胖子当着我的面吹牛，可吹牛的内容都是我干的。 我是一名探鬼主播，决...
    沈念sama阅读 32,098评论 2赞 317
  • 双鸳鸯连环套：你想象不到人心有多黑
    文/苍兰香墨 我猛地睁开眼，长吁一口气：“原来是场噩梦啊……” “哼！你这毒妇竟也来了？” 一声冷哼从身侧响起，我...
    开封第一讲书人阅读 30,825评论 0赞 207
  • 万荣杀人案实录
    序言：老挝万荣一对情侣失踪，失踪者是张志新（化名）和其女友刘颖，没想到半个月后，有当地人在树林里发现了一具尸体，经...
    沈念sama阅读 34,609评论 1赞 249
  • 护林员之死
    正文 独居荒郊野岭守林人离奇死亡，尸身上长有42处带血的脓包…… 初始之章·张勋 以下内容为张勋视角 年9月15日...
    茶点故事阅读 30,796评论 2赞 253
  • 白月光启示录
    正文 我和宋清朗相恋三年，在试婚纱的时候发现自己被绿了。 大学时的朋友给我发了我未婚夫和他白月光在一起吃饭的照片。...
    茶点故事阅读 32,282评论 1赞 265
  • 活死人
    序言：一个原本活蹦乱跳的男人离奇死亡，死状恐怖，灵堂内的尸体忽然破棺而出，到底是诈尸还是另有隐情，我是刑警宁泽，带...
    沈念sama阅读 28,603评论 3赞 261
  • 日本核电站爆炸内幕
    正文 年R本政府宣布，位于F岛的核电站，受9级特大地震影响，放射性物质发生泄漏。R本人自食恶果不足惜，却给世界环境...
    茶点故事阅读 33,277评论 3赞 242
  • 男人毒药：我在死后第九天来索命
    文/蒙蒙 一、第九天 我趴在偏房一处隐蔽的房顶上张望。 院中可真热闹，春花似锦、人声如沸。这庄子的主人今日做“春日...
    开封第一讲书人阅读 26,159评论 0赞 8
  • 一桩弑父案，背后竟有这般阴谋
    文/苍兰香墨 我抬头看了看天上的太阳。三九已至，却和暖如春，着一层夹袄步出监牢的瞬间，已是汗流浃背。 一阵脚步声响...
    开封第一讲书人阅读 26,959评论 0赞 201
  • 情欲美人皮
    我被黑心中介骗来泰国打工， 没想到刚下飞机就差点儿被人妖公主榨干…… 1. 我叫王不留，地道东北人。 一个月前我还...
    沈念sama阅读 36,079评论 2赞 285
  • 代替公主和亲
    正文 我出身青楼，却偏偏与公主长得像，于是被迫代替她去往敌国和亲。 传闻我的和亲对象是个残疾皇子，可洞房花烛夜当晚...
    茶点故事阅读 35,874评论 2赞 277

推荐阅读更多精彩内容