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

作者: 画风故事汇 | 来源:发表于2018-04-02 18:20 被阅读12次

存储过程新手指南

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