码上言知识库

# 1、创建存储过程

存储过程就是一条或者多条SQL语句 (opens new window)的集合，可以视为批文件。它可以定义批量插入的语句，也可以定义一个接收不同条件的SQL。

创建存储过程的语句为 CREATE PROCEDURE。

创建存储函数的语句为CREATE FUNCTION。

调用存储过程的语句为CALL。

调用存储函数的形式就像调用MySQL内部函数一样。

DROP TABLE IF EXISTS t_student;
 
CREATE TABLE t_student
(
  id INT(11) PRIMARY KEY AUTO_INCREMENT,
  name VARCHAR(255) NOT NULL,
  age  INT(11) NOT NULL
);
 
INSERT INTO t_student VALUES(NULL,'大宇',22),(NULL,'小宇',20);
1
2
3
4
5
6
7
8
9
10

如上述，t_student表中的数据有两条。如果我们要分别查询出来这两条数据，显然就是根据ID来查询。查询出来了第一条数据以后，我们可能会去做其它的操作。等过两天，我们要查询另外一条记录的时候，可能又要再写一次这样的查询语句。

如果能像Java那样，提供一个ID，就能查询到指定ID的记录，这样就可以复用之前写的SQL语句。对于查询SQL语句，我们能不能像Java那样，封装这个查询学生的SQL呢？存储过程与存储函数应运而生。

定义一个根据ID查询学生记录的存储过程。

DROP PROCEDURE IF EXISTS getStuById;
 
DELIMITER //  -- 定义存储过程结束符号为//
CREATE PROCEDURE getStuById(IN stuId INT(11),OUT stuName VARCHAR(255),OUT stuAge INT(11)) -- 定义输入与输出参数
COMMENT 'query students by their id'  -- 提示信息
SQL SECURITY DEFINER  -- DEFINER指明只有定义此SQL的人才能执行，MySQL默认也是这个
BEGIN
   SELECT name ,age INTO stuName , stuAge FROM t_student WHERE id = stuId; -- 分号要加
END // -- 结束符要加

DELIMITER ;  -- 重新定义存储过程结束符为分号
1
2
3
4
5
6
7
8
9
10
11

语法： CREATE PROCEDURE sp_name(定义输入输出参数) [ 存储特性 ] BEGIN SQL语句； END

IN 表示输入参数，OUT表示输出参数，INOUT表示既可以输入也可以输出的参数。sp_name为存储过程的名字。

如果此存储过程没有任何输入输出，其实就没什么意义了，但是sp_name()的括号不能省略。

​ 查看刚才创建的存储过程。

SHOW PROCEDURE STATUS LIKE 'g%'
1

​ 下面是调用存储过程。对于存储过程提供的临时变量而言，MySQL规定要加上@开头。

#study 是当前数据库名称
 
CALL study.getStuById(1,@name,@age);
 
SELECT @name AS stuName,@age AS stuAge;

-- =================================
CALL getStuById(2,@name,@age);
 
SELECT @name AS stuName,@age AS stuAge;
1
2
3
4
5
6
7
8
9
10

这样做的好处是，如果一段较为复杂的SQL语句，我们可能过了几天再去写它，又费事又费力。存储过程可以封装我们写过的SQL，在下次需要调用它的时候，直接提供参数并指明查询结果输出到哪些变量中即可。

提示：如果存储过程一次查询出两个记录，将会提示出错。[Err] 1172 - Result consisted of more than one row

所以需要在存储过程的SQL后面加上LIMIT 1。从位偏移量为0的，即从查询结果的第一条数据开始，查询一条记录。

# 2、创建存储函数

存储函数与存储过程本质上是一样的，都是封装一系列SQL语句，简化调用。

我们自己编写的存储函数可以像MySQL函数那样自由的被调用。

DROP FUNCTION IF EXISTS getStuNameById;
 
DELIMITER //
CREATE FUNCTION getStuNameById(stuId INT)  -- 默认是IN，但是不能写上去。stuId视为输入的临时变量
RETURNS VARCHAR(255)   -- 指明返回值类型
RETURN  (SELECT name FROM t_student WHERE id = stuId); //  -- 指明SQL语句,并使用结束标记。注意分号位置
DELIMITER ;
1
2
3
4
5
6
7

这里遇到一个问题：

在执行上述sql语句的时候，会报错信息为：mysql创建function 报错误1418 - This function has none of DETERMINISTIC, NO SQL, or READS SQL DATA in

解决方法：

执行这条sql就可以了：

set global log_bin_trust_function_creators=1;
1

使用存储函数。

SELECT getStuNameById(1);
1

提示：在RETURN 语句后面，有趣的是，分号在SQL语句的外面。如果不加分号，查询结果居然查询出两条记录，很奇怪。

从上述存储函数的写法上来看，存储函数有一定的缺点。首先与存储过程一样，只能返回一条结果记录。另外就是存储函数只能指明一列数据作为结果，而存储过程能够指明多列数据作为结果。

# 3、定义变量

如果希望MySQL执行批量插入的操作，那么至少要有一个计数器来计算当前插入的是第几次。

这里的变量是用在存储过程中的SQL语句中的，变量的作用范围在BEGIN .... END 中。

没有DEFAULT子句，初始值为NULL。

定义变量的操作

DECLARE name,address VARCHAR;  -- 发现了吗，SQL中一般都喜欢先定义变量再定义类型，与Java是相反的。
DECLARE age INT DEFAULT 20; -- 指定默认值。若没有DEFAULT子句，初始值为NULL。
1
2

​ 为变量赋值

SET name = 'jay';  -- 为name变量设置值
1
DECLARE var1,var2,var3 INT;
SET var1 = 10,var2 = 20;  -- 其实为了简化记忆其语法，可以分开来写
-- SET var1 = 10;
-- SET var2 = 20;
SET var3 = var1 + var2;
1
2
3
4
5

​ 使用变量实例。如下表，在做了去除主键约束后，我又添加了一条id=1的数据。现在希望查询出id为1的记录的数量。

DROP PROCEDURE IF EXISTS contStById;
 
DELIMITER //  -- 定义存储过程结束符号为//
CREATE PROCEDURE contStById(IN sid INT(11),OUT result INT(11)) -- 定义输入变量
BEGIN
    DECLARE sCount INT;
    SELECT COUNT(*) INTO sCount FROM t_student WHERE id = sid;
    SET result = sCount; -- 用变量为输出结果设值
END // -- 结束符要加
DELIMITER ;  -- 重新定义存储过程结束符为分号
 
CALL contStById(1,@result);
SELECT @result;
1
2
3
4
5
6
7
8
9
10
11
12
13
14

显然，在存储过程中的变量，可以直接与输出变量进行相应的计算。本例直接把sCount这个变量的值赋值到输出中。

# 4、定义条件与定义处理程序

定义条件CONDITION定义的是：在执行存储过程中的SQL语句的时候，可能出现的问题。

定义处理程序HANDLER：定义遇到了指定问题应该如何处理，避免存储过程执行异常而停止。

定义条件与定义处理语句程序的位置应该在BEGIN ... END 之间。

定义条件的语法：DECLARE condtion_name CONDTION FOR 错误码||错误值

错误码可以视为一个错误的引用，比如404，它代表的就是找不到页面的错误，它的错误值可以视为NullPointerException。

DECLARE command_not_allowed CONDITION FOR SQLSTATE '42000'; -- 错误值
DECLARE command_not_allowed CONDITION FOR 1148;  -- 错误码
1
2

定义处理程序语法：DECLARE HANDLER_TYPE HANDLER FOR condtion_name sp_statement；

MySQL定义了三种HANDLER_TYPE:

  • CONTIUE：是指遇到错误忽略，继续执行下面的SQL。（小写：contiue）

  • EXIT：表示遇到错误退出，默认的策略就是EXIT。

  • undo：遇到错误之后撤回之前的操作，mysql目前不支持。

condtion_name可以是我们自己的定义的条件，也可以是MySQL内置的条件，比如SQLWARNING，匹配01开头的错误代码。sp_statement指遇到错误的时候，需要执行的存储过程或存储函数。

DECLARE CONTINUE HANDLER FOR SQLSATTE '42S02' SET @info = 'NO_SUCH_TABLE'; -- 忽略错误值为42S02的SQL异常
 
DECLARE EXIT HANDLER FOR SQLEXCEPTION SET @info = 'ERROR_OCCUR'; -- 捕获SQL执行异常并输出信息
 
DECLARE no_such_table CONDITION FOR 1146; -- 为错误码为1146的错误定义条件
DECLARE CONTINUE HANDLER FOR no_such_table SET @info = 'no_such_table'; -- 为指定的条件设置处理程序
1
2
3
4
5
6
DROP TABLE IF EXISTS t_student;
 
CREATE TABLE t_student
(
  id INT(11) PRIMARY KEY AUTO_INCREMENT,
  name VARCHAR(255) NOT NULL,
  age  INT(11) NOT NULL
);
1
2
3
4
5
6
7
8

现通过存储过程，为这张表插入数据。因为id属性有主键约束，所以不能插入相同的id。

DROP PROCEDURE IF EXISTS insertStu;
 
DELIMITER //  -- 定义存储过程结束符号为//
CREATE PROCEDURE insertStu(OUT result INT) -- 指定输出结果
BEGIN
    DECLARE flag INT(11) DEFAULT 0; -- 指定变量为0
    DECLARE primary_key_limit CONDITION FOR SQLSTATE '23000';  -- 主键约束的错误值
    DECLARE CONTINUE HANDLER FOR primary_key_limit SET @info = -1; -- 设计如果出现错误，@info将会被设置为 -1 
    INSERT INTO t_student(id,name,age) VALUES(1,'dayu',22); -- 插入值，设置主键为1
    SET flag = 1; -- 普通变量设值为1
    SET result = flag;  -- 如果下面的SQL执行出现异常，那么就退出，只有上面的SQL生效。将普通变量的值给输出
    INSERT INTO t_student(id,name,age) VALUES(1,'dayu',22); -- 插入值，设置主键为1
    SET flag = 2; -- 如果处理程序是EXIT，那么就不会执行到这一步了
    SET result = flag; -- 将普通变量的值给输出
END // -- 结束符要加
DELIMITER ;  -- 重新定义存储过程结束符为分号
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16

CONTIUE是指遇到错误忽略，继续执行下面的SQL。因为是CONTINUE来处理程序，所以遇到错误将会继续执行。

另外，第二次插入记录，因为违反了主键约束，所以插入失败，但是存储过程仍然继续执行完毕。

CALL insertStu(@result);
SELECT @result,@info; -- @info没有申明就能调用到，可能是是全局变量吧
1
2

再次查看t_student表，只插入了一条记录，但是所有的存储过程都执行完毕了。

​ 现在，重新执行下面的SQL。先重新建表，再将处理程序的处理策略换为EXIT：在执行存储过程中遇到了错误，那么就立即退出。

DROP TABLE IF EXISTS t_student;
 
CREATE TABLE t_student
(
    id INT(11) PRIMARY KEY AUTO_INCREMENT,
  name VARCHAR(255) NOT NULL,
  age  INT(11) NOT NULL
);
 
DROP PROCEDURE IF EXISTS insertStu;
 
DELIMITER //  -- 定义存储过程结束符号为//
CREATE PROCEDURE insertStu(OUT result INT) -- 指定输出结果
BEGIN
    DECLARE flag INT(11) DEFAULT 0; -- 指定变量为0
    DECLARE primary_key_limit CONDITION FOR SQLSTATE '23000';  -- 主键约束的错误值
    DECLARE EXIT HANDLER FOR primary_key_limit SET @info = -1; -- 设计如果出现错误，@info将会被设置为 -1 
    INSERT INTO t_student(id,name,age) VALUES(NULL,'dayu',22); -- 
    SET flag = 1; -- 普通变量设值为1
    SET result = flag;  -- 如果下面的SQL执行出现异常，那么就退出，只有上面的SQL生效。将普通变量的值给输出
    INSERT INTO t_student(id,name,age) VALUES(NULL,'dayu',22); -- 
    SET flag = 2; -- 如果处理程序是EXIT，那么就不会执行到这一步了
    SET result = flag; -- 将普通变量的值给输出
END // -- 结束符要加
DELIMITER ;  -- 重新定义存储过程结束符为分号
 
CALL insertStu(@result);
SELECT @result,@info; -- @info没有申明就能调用到，可能是是全局变量吧
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28

在声明条件后并遇到相关的错误条件后，那就看应该怎么处理。如果是EXIT，那么存储过程只生效到错误处的上一条SQL。如果是CONTINUE，那么将会忽略掉执行错误的SQL，继续执行下面的其它存储过程。

# 5、流程控制

# 5.1、If语句的使用

DROP PROCEDURE IF EXISTS testIf;
DELIMITER //
CREATE PROCEDURE testIf(OUT result VARCHAR(255))
BEGIN
     DECLARE val VARCHAR(255);
     SET val = 'a';
     IF val IS NULL
        THEN SET result = 'IS NULL';
        ELSE SET result = 'IS NOT NULL';
     END IF;
END //
DELIMITER ;
 
CALL testIf(@result);
SELECT @result;
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15

# 5.2、CASE语句

DROP PROCEDURE IF EXISTS testCase;
DELIMITER //
CREATE PROCEDURE testCase(OUT result VARCHAR(255))
BEGIN
     DECLARE val VARCHAR(255);
     SET val = 'a';
     CASE val IS NULL
         WHEN 1 THEN SET result = 'val is true';
         WHEN 0 THEN SET result = 'val is false';
         ELSE SELECT 'else';
     END CASE;
END //
DELIMITER ;
 
CALL testCase(@result);
SELECT @result;
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16

# 5.3、LOOP

LOOP用于重复执行SQL。LEAVE 用于退出循环。

DROP PROCEDURE IF EXISTS testLoop;
DELIMITER //
CREATE PROCEDURE testLoop(OUT result VARCHAR(255))
BEGIN
     DECLARE id INT DEFAULT 0;
     add_loop:LOOP
	   SET id = id + 1;
	   IF id>10 THEN LEAVE add_loop; -- 可在此处修改成批量插入
	   END IF;
     SET result = id;
     END LOOP add_loop;
END //
DELIMITER ;
 
CALL testLoop(@result);
SELECT @result;
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16

批量插入得例子：

DROP TABLE IF EXISTS t_student;
 
CREATE TABLE t_student
(
  id INT(11) PRIMARY KEY AUTO_INCREMENT,
  name VARCHAR(255) NOT NULL,
  age  INT(11) NOT NULL
);
 
DROP PROCEDURE IF EXISTS testLoop;
DELIMITER //
CREATE PROCEDURE testLoop(IN columnCount INT(11))
BEGIN
     DECLARE id INT DEFAULT 0;
     add_loop:LOOP
	SET id = id + 1;
	IF id>columnCount THEN LEAVE add_loop;
	END IF;
       INSERT INTO t_student(id,name,age) VALUES(id,' mysql',22);
     END LOOP add_loop;
END //
DELIMITER ;
 
CALL testLoop(15);
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24

# 5.4、WHILE

DROP PROCEDURE IF EXISTS testWhile;
DELIMITER //
 
CREATE PROCEDURE testWhile(IN myCount INT(11),OUT result INT(11))
BEGIN
   DECLARE i INT DEFAULT 0 ; -- 定义变量
   WHILE i < myCount DO  -- 符合条件就循环
       -- 核心循环SQL;  
       SET i = i + 1 ; -- 计数器+1
   END WHILE;       -- 当不满足条件，结束循环  --分号一定要加！
   SET result = i;  -- 将变量赋值到输出
END //
1
2
3
4
5
6
7
8
9
10
11
12
CALL testWhile(10,@result);
SELECT @result AS 循环次数;
1
2

# 6、调用存储过程

存储过程必须使用CALL语句来调用。如果要调用其它数据库的存储过程，需要指定数据库名称。例如 CALL dbname.spname

DROP TABLE IF EXISTS t_student;
CREATE TABLE t_student
(
  id INT(11) PRIMARY KEY AUTO_INCREMENT,
  name VARCHAR(255) NOT NULL,
  age  INT(11),
  address VARCHAR(255),
  sex  CHAR(2)
);
 
INSERT INTO t_student VALUES(NULL,'小明',21,'北京','男');
INSERT INTO t_student VALUES(NULL,'小花',22,'上海','女');
INSERT INTO t_student VALUES(NULL,'小兰',22,'南京','女');
1
2
3
4
5
6
7
8
9
10
11
12
13
DELIMITER //  --存储过程分隔符设定为//
CREATE PROCEDURE CountStu(IN stu_sex CHAR,OUT num INT)  --stu_sex表示输入，num表示输出
BEGIN 
   SELECT COUNT(*) INTO num FROM t_student WHERE sex = stu_sex; --结果存入num，条件用上变量
END //        --以指定分隔符结束存储过程
DELIMITER ;   --存储过程分隔符设定为;
1
2
3
4
5
6
CALL CountStu('男',@num);
SELECT @num;
 
-- 本质为执行下面的SQL：
 
SELECT COUNT(*) AS @num 
FROM t_student WHERE sex = '男';

CALL CountStu('女',@num);
SELECT @num;

-- 尝试传入的其它的数据
CALL CountStu('3',@num);
SELECT @num;
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15

原来，存储过程并不是什么神秘的东西。它只是将SQL语句抽取出来，定义成模版，并定义接收条件参数。在下次要使用这些SQL的时候，传入我们需要的参数。其实就像Java一样，定义一个方法。

再回头来看最初的存储过程的定义：存储过程就是一条或者多条SQL语句的集合。

现在可以理解为什么要用存储过程。如果我们要做测试数据，需要往表里面批量插入1000条数据。不可能手动输入1000次INSERT语句，这个时候，就能用的上存储过程。另外，当我们需要根据不同的条件去查询指定表里面的数据的时候，也可以预定义一些存储过程。这样，根据不同的条件，我们就能快速的获取到查询结果了。

# 6.1、调用存储函数

​ 存储函数不需要使用CALL关键字。另外，存储函数的参数类型默认为IN输入。

DELIMITER //
CREATE FUNCTION countStu2(stu_sex CHAR) -- 默认是IN，OUT、INOUT不支持。效果是IN,但是不能加上IN
RETURNS INT
RETURN  (SELECT COUNT(*) FROM t_student WHERE sex = stu_sex); // -- 不加结束标记，运行失败
DELIMITER ;
1
2
3
4
5

如果遇到一下错误：

执行创建函数的sql语句 (opens new window)时，提示:This function has none of DETERMINISTIC, NO SQL, or READS SQL DATA in its declaration and binary logging is enabled

解决方案：

在mysql数据库中执行以下语句 （临时生效，重启后失效） set global log_bin_trust_function_creators=TRUE;

SELECT countStu2('男');
1

虽然存储函数与存储过程的定义稍有不同，但是可以实现相同的功能，应该灵活选择。

通过定义存储函数，就能像使用MySQL提供的函数那样。换言之，存储函数是自己定义的，而内部函数是MySQL开发者定义的。

​ 如果SQL语句较为复杂，下面加上BEGIN与END的写法更佳。

DELIMITER //
CREATE FUNCTION countStu2(stu_sex CHAR) -- 默认是IN，OUT、INOUT不支持。效果是IN,但是不能加上IN
RETURNS INT  -- 定义返回值类型
BEGIN
   RETURN  (复杂的SQL);
END // -- 结束标记代表的是存储过程定义的结束。
DELIMITER ;
1
2
3
4
5
6
7

# 6.2、 使用SHOW STATUS 查看存储过程或函数的状态

SHOW PROCEDURE STATUS LIKE 'C%';
1
SHOW FUNCTION STATUS LIKE 'C%';
1

​ 知道了存储过程，如果希望查看具体的存储过程或者存储函数的定义。

SHOW CREATE PROCEDURE school.CountStu;
 
-- Create Procedure 列为核心语句
CREATE DEFINER=`root`@`localhost` PROCEDURE `CountStu`(IN stu_sex CHAR,OUT num INT)
BEGIN 
   SELECT COUNT(*) INTO num FROM t_student WHERE sex = stu_sex;
END
1
2
3
4
5
6
7

​ 查看存储函数有哪些。

SHOW FUNCTION STATUS LIKE 'C%'
1

​ 查看具体的存储函数创建语句。

SHOW CREATE FUNCTION school.countStu2
 
-- Create Function 列的语句
CREATE DEFINER=`root`@`localhost` FUNCTION `countStu2`(stu_sex CHAR) 
RETURNS int(11)
RETURN  (SELECT COUNT(*) FROM t_student WHERE sex = stu_sex)
1
2
3
4
5
6

# 6.3、从information_schema.Routines 表中查询存储过程与函数（了解）

原来，MySQL中的存储过程与存储函数都存放在information_schema数据库下的Routines表中。

SELECT * FROM information_schema.ROUTINES WHERE ROUTINE_NAME LIKE 'C%'
1

如果什么时候忘记了存储函数或者存储过程的名字，可以查询这张表的数据。然后确定了是某个存储过程或者是存储函数，就可以使用SHOW CREATE PROCEDURE / FUNCTION 数据库.sp_name查看指定的创建语句了。

# 6.4、 修改存储过程

​ 语法：ALTER PROCEDURE | FUNCTION sp_name [ 存储特性 ]

修改存储过程，将读写权限改为 MODIFIES SQL DATE 并指明调用者
ALTER PROCEDURE countStu2
MODIFIES SQL DATE   -- 表示子程序中包含写数据的语句
SQL SECURITY INVOKER  -- 表示调用者才能执行
1
2
3
4

# 6.5、 删除存储过程

删除存储过程语法：DROP PROCEDURE [ IF EXISTS ] sp_name；

删除存储函数语法：DROP FUNCTION [ IF EXISTS ] function_name；

SHOW PROCEDURE STATUS LIKE 'C%'
1
DROP PROCEDURE IF EXISTS study.CountTec;  -- 删除study库下的名为CountTec存储过程
 
SHOW PROCEDURE STATUS LIKE 'c%' --再次查看有哪些存储过程
1
2
3

# 7、存储过程与存储函数的补充

# 7.1、MySQL的存储过程与存储函数有什么区别？

​ 存储函数只能通过return语句返回单个值或者表对象。

​ 存储过程不能用return，但是可以使用多个out参数返回多个值。

# 7.2、存储过程如何修改代码？

虽然提供了ALTER PROCEDURE sp_name [存储特性]，但是只能修改存储过程的存储特性，不能修改SQL。需要删除并重新创建。

# 7.3、存储过程中能调用其它存储过程吗？

​ 可以在存储过程中的SQL中通过CALL调用其它存储过程，但是不能用DROP删除其它存储过程。

# 7.4、IN、OUT等参数与字段名相同了怎么办？

​ 需要将它们区别开来，否则将会出现错误。

# 7.5、存储过程的IN参数可能是中文怎么办？

​ 在定义存储过程的时候，加上character set gbk。

例如：

DELIMITER //
CREATE PROCEDURE getAddressByName(IN u_name VARCHAR(50) character set gbk , OUT address VARCHAR(50))
BEGIN
     SQL;
END//
DELIMITER ;
1
2
3
4
5
6

← 视图 →

多关联表和函数
Copyright © - 码上言   |