数据库之存储过程详解

一、存储过程的概念

  存储过程和函数是事先经过编译并存储在数据库中的一段SQL语句的集合，存储和和函数的区别在于函数必须有返回值，而存储过程没有，存储过程的参数可以使用IN、OUT、INOUT类型，而函数的参数只能是IN类型。

  存储过程再简单点来说，就是为以后的使用而保存的一条或多条MySQL语句的集合。可将其视为批件，虽然它们的作用不仅限于批处理。在我看来， 存储过程就是有业务逻辑和流程的集合， 可以在存储过程中创建表，更新数据， 删除等等。

二、存储过程的SQL相关操作

/**** 1、创建和使用存储过程 ****/

// 创建存储过程（IN和OUT分别为输入和输出参数）
DELIMITER ||
CREATE PROCEDURE pro_book(IN BT INT,OUT sum INT,OUT avg FLOAT)
BEGIN
SELECT SUM(price) INTO sum FROM t_book WHERE bookTypeId=1;
SELECT AVG(price) INTO avg FROM t_book WHERE bookTypeId=1;
END
||
DELIMITER ;

// 调用和显示结果
CALL pro_book(1, @total, @avg);
SELECT @total as total;

/**** 2、使用游标 ****/

// 创建游标，打开游标，关闭游标
DELIMITER ||
CREATE PROCEDURE pro_sor()
BEGIN
DECLARE bname CURSOR FOR SELECT bookName FROM t_book;
OPEN bname;
CLOSE bname;
END
||
DELIMITER ;

/**** 3、流程控制 ****/

// case语句（代替多个if else）
DELIMITER ||
CREATE PROCEDURE pro_book2(IN bookId INT)
BEGIN
SELECT COUNT(*) INTO @num FROM t_book WHERE id=bookId;
--CASE语句开始处
CASE @num
WHEN 1 THEN UPDATE t_book SET bookName="历史" WHERE id=bookId;
WHEN 0 THEN INSERT INTO t_book VALUES ("NULL", "中国历史", "MK", "45", "4");
ELSE INSERT INTO t_book VALUES ("NULL", "中国历史", "MK", "45", "4");
--CASE语句结束处
END CASE;
END
||
DELIMITER ;

// loop-leave语句

DELIMITER ||
CREATE PROCEDURE pro_book3(IN Num INT)
BEGIN
--LOOP循环开始处
aaa:LOOP
SET Num=Num-1;
--LEAVE表示结束循环
IF Num=0 THEN LEAVE aaa;
--ITERATE表示跳出循环继续执行
ELSEIF Num=2 THEN ITERATE aaa;
END IF;
INSERT INTO t_log VALUES(NULL,NOW(),"在book表中删除了一条数据");
--LOOP循环结束处
END LOOP aaa;
END
||
DELIMITER ;

// repeat-until语句
DELIMITER ||
CREATE PROCEDURE pro_book4(IN Num INT)
BEGIN
REPEAT
SET Num=Num-1;
INSERT INTO t_log VALUES(NULL,NOW(),"在book表中删除了一条数据");
UNTIL Num=1 END REPEAT;
END
||
DELIMITER ;

// while语句
DELIMITER ||
CREATE PROCEDURE pro_book5(IN Num INT)
BEGIN
WHILE Num>0 DO
INSERT INTO t_log VALUES(NULL,NOW(),"在book表中删除了一条数据");
SET Num=Num-1;
END WHILE;
END
||
DELIMITER ;

/**** 4、查看和删除存储过程 ****/

SHOW PROCEDURE STATUS LIKE 'pro_book';

SHOW CREATE PROCEDURE pro_book;

DROP PROCEDURE pro_book;

二、存储过程的作用和优点

1、增加性能

  创建时已经检查了语法，第一次执行的时候执行计划被创建，被编译，再次执行时不需要重检查语法、不需要重编译，根据已经缓存的计划来决定是否需要重创建执行计划。

2、可保证数据的安全性和完整性

  通过存储过程可以使没有权限的用户在控制之下间接地存取数据库，从而保证数据的安全。通过存储过程可以使相关的动作在一起发生，不要求反复建立一系列处理步骤，从而可以维护数据库的完整性。

3、降低网络的通信量

参考：https://www.cnblogs.com/ToNi/p/4234370.html
https://www.cnblogs.com/phpper/p/7587556.html