1-MySQL - 存储过程

目录

about

centos7.9 + mysql5.7.20

MySQL从5.0版本开始支持存储过程。

存储过程（Stored Procedure）是一种在数据库中存储的复杂程序，以便外部程序调用的一种数据库对象。

存储过程是为了完成特定功能的SQL语句集，经编译创建并保存在数据库中，用户可通过指定存储过程的名字并给定参数(需要时)来调用执行。

说白了，存储过程就是保存在MySQL上的一个"别名"——封装SQL语句集。通过整个别名来调用封装好的语句集，相当方便。

存储过程思想上很简单，就是数据库 SQL 语言层面的代码封装与重用。

优点

  • 存储过程可封装，并隐藏复杂的逻辑。
  • 存储过程可以回传值，并可以接受参数。
  • 存储过程无法使用 SELECT 指令来运行，因为它是子程序，与查看表，数据表或用户定义函数不同。
  • 存储过程可以用在数据检验，强制某些逻辑等。

缺点

  • 存储过程，往往定制化于特定的数据库上，因为支持的编程语言不同。当切换到其他厂商的数据库系统时，需要重写原有的存储过程。
  • 存储过程的性能调校与撰写，受限于各种数据库系统。

一般的，存储过程分为两类，一类是无参的存储过程，另一类是有参数的存储过程，我们分别来学习。

注意，创建的存储过程会绑定在当前所在的数据库，接下来的操作，都是在school数据库下进行的操作。

准备数据：https://www.cnblogs.com/Neeo/articles/13565900.html

创建无参存储过程

一个简单的无参存储过程：

-- 创建无参存储过程
DELIMITER //
CREATE PROCEDURE p1()    	-- 通过CREATE PROCEDURE声明存储过程，存储过程名称是 p1，无参
BEGIN
  -- 这里开始写SQL，当调用存储过程的时候，这里的所有SQL都会执行
  SELECT * FROM student WHERE sid > 10;
END	//
DELIMITER ;

-- 在终端中使用 call 调用存储过程
CALL p1();

上例，存储过程p1用来查询student表中sid大于10的记录。

注意，存储过程不能重复创建。

使用pymysql中这么调用：

import pymysql

conn = pymysql.Connect(host='10.0.0.200', user='root', password='123', database='school', charset='utf8')
cursor = conn.cursor()

# 调用存储过程
cursor.callproc('p1')
conn.commit()

result = cursor.fetchall()
print(result)
"""
(
    (11, '男', 2, '李四'), (12, '女', 3, '如花'), (13, '男', 3, '刘三'), 
    (14, '男', 3, '刘一'), (15, '女', 3, '刘二'), (16, '男', 3, '刘四')
)
"""
cursor.close()
conn.close()

创建有参存储过程

存储过程允许传参，通常有三种传参方式：

  • in
  • out
  • inout

一起来看看这三个参数怎么用。

in

in参数：

  • 在调用存储过程时，必须指定。
  • 在存储过程执行中，该参数不能被修改。
  • 一般只需传参无需返回时使用in。

创建有参存储过程：

-- 接受 n1 和 n2 两个外部传来的int数据
DELIMITER //
CREATE PROCEDURE p2(
  IN n1 INT,
  IN n2 INT
)
BEGIN
  SELECT * FROM student WHERE sid BETWEEN n1 AND n2;
END	//
DELIMITER ;

-- 使用 call 调用
CALL p2(10, 20)

使用pymysql中这么调用：

import pymysql

conn = pymysql.Connect(host='10.0.0.200', user='root', password='123', database='school', charset='utf8')
cursor = conn.cursor()

# 调用存储过程，以元组的形式传参
cursor.callproc('p2', (10, 20))
conn.commit()

result = cursor.fetchall()
print(result)
"""
(
    (10, '女', 2, '李二'), (11, '男', 2, '李四'), (12, '女', 3, '如花'), 
    (13, '男', 3, '刘三'), (14, '男', 3, '刘一'), (15, '女', 3, '刘二'), (16, '男', 3, '刘四')
)
"""
cursor.close()
conn.close()

out

out参数：

  • 存储过程执行时，可以被修改。
  • 可以被返回。

创建有参存储过程：

DELIMITER //
CREATE PROCEDURE p3(
  IN n1 INT,
  OUT n2 INT
)
BEGIN
  -- 存储过程执行时，首先修改 n2 的值，然后被select语句使用
  SET n2 = 20;
  SELECT * FROM student WHERE sid BETWEEN n1 AND n2;
END	//
DELIMITER ;

-- set定义一个局部的变量 n2,值是10，然后整个变量被当作存储过程的out参数传入存储过程中
SET @n2 = 10;
CALL p3(10, @n2);

-- 终端中使用select查看变量 n2
SELECT @n2;

对于out参数，pymysql操作起来，就有点麻烦了：

import pymysql

conn = pymysql.Connect(host='10.0.0.200', user='root', password='123', database='school', charset='utf8')
cursor = conn.cursor()
# 调用存储过程，获取存储过程的执行结果
cursor.callproc('p3', (10, 10))
conn.commit()

result1 = cursor.fetchall()
print(result1)
"""
(
    (10, '女', 2, '李二'), (11, '男', 2, '李四'), (12, '女', 3, '如花'), 
    (13, '男', 3, '刘三'), (14, '男', 3, '刘一'), (15, '女', 3, '刘二'), (16, '男', 3, '刘四')
)
"""

# 获取out的返回值
cursor.execute("select @_p3_0,@_p3_1")  # 固定写法
result2 = cursor.fetchall()
print(result2)
"""
cursor.execute("select @_p3_0,@_p3_1")  
    - select开头 
    - 空格紧随其后
    - @_存储过程名称_参数序号，逗号分隔多个参数
    - 参数序号从0开始，从左往右依此类推
    
返回值：
((10, 20),)  第一个值10，是存储过程n1的值，第二值20，是存储过程执行时修改后的out参数n2的返回值
"""

cursor.close()
conn.close()

inout

顾名思义，inout参数：

  • 能被修改。
  • 能被返回。

创建有参存储过程：

DELIMITER //
CREATE PROCEDURE p4(
  INOUT n1 INT
)
BEGIN
  -- 存储过程执行时，首先修改 n1 的值，然后被select语句使用
  SET n1 = 20;
  SELECT * FROM student WHERE sid < n1;
END	//
DELIMITER ;

-- set定义一个局部的变量 n1,值是10，然后整个变量被当作存储过程的inout参数传入存储过程中
SET @n1 = 10;
CALL p4(@n1);

-- 终端中使用select查看变量 n1
SELECT @n1;

pymysql中的用法跟out类似：

import pymysql

conn = pymysql.Connect(host='10.0.0.200', user='root', password='123', database='school', charset='utf8')
cursor = conn.cursor()
# 调用存储过程，获取存储过程的执行结果
cursor.callproc('p4', (10,))   # 注意，元组中只有一个元素时，要带逗号
conn.commit()

result1 = cursor.fetchall()
print(result1)
"""
(
    (1, '男', 1, '理解'), (2, '女', 1, '钢蛋'), (3, '男', 1, '张三'), (4, '男', 1, '张一'), 
    (5, '女', 1, '张二'), (6, '男', 1, '张四'), (7, '女', 2, '铁锤'), (8, '男', 2, '李三'), 
    (9, '男', 2, '李一'), (10, '女', 2, '李二'), (11, '男', 2, '李四'), (12, '女', 3, '如花'), 
    (13, '男', 3, '刘三'), (14, '男', 3, '刘一'), (15, '女', 3, '刘二'), (16, '男', 3, '刘四')
)
"""

# 获取inout的返回值
cursor.execute("select @_p4_0")  # 固定写法
result2 = cursor.fetchall()
print(result2)
"""
((20,),)  修改后的n1值是20
"""

cursor.close()
conn.close()

存储过程中的事务

通过out参数的不同值表示存储过程中的事务执行结果：

DELIMITER //
CREATE PROCEDURE p5(
	OUT n1 TINYINT
)
BEGIN
	-- 如果事务内部的代码执行结果是error就执行这部分代码
	DECLARE EXIT HANDLER FOR SQLEXCEPTION
	BEGIN
		-- error
		SET n1 = 1;
		ROLLBACK;
	END;
	
	-- 如果事务内部的代码执行结果是warning就执行这部分代码
	DECLARE EXIT HANDLER FOR SQLWARNING
	BEGIN
		-- warning
		SET n1 = 2;
		ROLLBACK;
	END;
	
	-- 事务部分代码
	START TRANSACTION;
	  -- 删表成功意味着查询失败
		DROP TABLE student;
		SELECT * FROM student;
	COMMIT;

	-- success，执行到这里表示存储过程整体执行成功
	SET n1 = 0;
END	//
DELIMITER ;


-- 通过 call 调用
SET @n1 = 0;
CALL p5(@n1);   
SELECT @n1;   -- 结果返回 1，表示事务执行失败

n1的值：

  • 1表示事务执行失败。
  • 2表示事务执行有warning。
  • 0表示执行成功。

来看pymysql中怎么用：

import pymysql

conn = pymysql.Connect(host='10.0.0.200', user='root', password='123', database='school', charset='utf8')
cursor = conn.cursor()
# 调用存储过程，获取存储过程的执行结果
cursor.callproc('p5', (1,))
conn.commit()

result1 = cursor.fetchall()
print(result1)
"""
()   事务执行失败，查询结果为空就很对
"""

# 获取out的返回值
cursor.execute("select @_p5_0")  # 固定写法
result2 = cursor.fetchall()
print(result2)
"""
((1,),)   1表示执行失败了
"""

cursor.close()
conn.close()

存储过程中的游标应用

游标(Cursor)是一个存储在MySQL服务器上的数据库查询，但它不是一条select语句，而是被该语句所检索出来的结果集。游标的作用就是对结果集进行迭代(也可以立即为遍历)，游标每次只迭代一条记录，方向不可逆也不可以跳过记录。

游标的缺点是性能不高，而且使用游标时会产生死锁，造成内存开销过过大。

游标通常应用于存储过程、函数、触发器、事件中。

这里通过完成一个需求来学习游标在存储过程中的应用。

需求：在存储过程中，读取一个表中的指定字段，复制到另一个表中。

首先，我们先把表和数据准备好：

DROP TABLE IF EXISTS cursor_a;
CREATE TABLE cursor_a(
	id INT NOT NULL PRIMARY KEY AUTO_INCREMENT,
	num INT
) ENGINE=INNODB DEFAULT CHARSET=utf8;
INSERT INTO cursor_a(num) VALUES(123),(234),(345),(456),(789);

存储过程如下：

DELIMITER //
CREATE PROCEDURE p6()
BEGIN
	-- 声明变量temp_num用来接受循环读取游标中的值
	DECLARE temp_num INT;
	-- 声明变量done，默认为false
	DECLARE done INT DEFAULT FALSE;
	-- 声明游标my_cursor，并且读取自cursor_a表中的记录存储到游标中
	DECLARE my_cursor CURSOR FOR SELECT num FROM cursor_a;
	-- 当游标中的数据被循环读取完毕，就将done设置为true，用于退出循环
	DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE;  -- 这一过程MySQL内部帮我们处理
	
	-- 创建一张跟cursor_a一摸一样的表cursor_b
	DROP TABLE IF EXISTS cursor_b;
	CREATE TABLE cursor_b LIKE cursor_a;	
	
	-- 开工，首先打开游标
	OPEN my_cursor;
		-- 使用loop
		start_loop:LOOP   -- start_loop这个变量可以自定义
			-- 通过fetch循环获取游标中的值并赋值给之前定义的变量temp_num
			FETCH my_cursor INTO temp_num;
			-- 当游标被取值完毕后，就离开循环
			IF done THEN
				LEAVE start_loop;
			END IF;
			-- 将循环中的值插入到cursor_b表中
			INSERT INTO cursor_b(num) VALUE(temp_num);
		END LOOP start_loop;
	-- 循环结束后，关闭游标
	CLOSE my_cursor;
END	//
DELIMITER ;

-- 通过 call 调用
CALL p6();

注意，如果存储过程中存在声明语句，比如声明变量和游标，那么这些声明语句，必须在增删改查语句的上面，否则存储过程无法执行。

动态执行SQL

在存储过程中执行动态SQL可以用来防止SQL注入。

来看例子：

-- 方式1
DELIMITER //
CREATE PROCEDURE p7(
	IN s_arg INT
)
BEGIN
	-- 校验说SQL语句的合法性
	PREPARE prod FROM "select * from student where sid > ?";  -- prod代指要校验的SQL，可自定义
	-- 格式化SQL后执行，这里需要一个session级别的变量，所以先设置
	SET @s_arg = s_arg;
	EXECUTE prod USING @s_arg;
	DEALLOCATE PREPARE prod;
END	//
DELIMITER ;

CALL p7(10);

-- 方式2
DELIMITER //
CREATE PROCEDURE p8(
	IN s_arg INT
)
BEGIN
	SET @s_sql = CONCAT("select * from student where sid > ", s_arg, ";");
	PREPARE prod FROM @s_sql;
	EXECUTE prod;
	DEALLOCATE PREPARE prod;
END	//
DELIMITER ;

CALL p8(10);

上面两种方式，更推荐使用第二种，使用concat函数拼接SQL相对简单。

存储过程的应用和创建，基本讲解完毕。接下来，我们来看看存储过程的管理。

存储过程管理

查看存储过程

-- 查询MySQL中所有的存储过程
SELECT db,NAME FROM mysql.proc;

--  查询指定数据库下的所有存储过程
SELECT * FROM mysql.proc WHERE db="school";
SHOW PROCEDURE STATUS WHERE db="school";

-- 查询指定存储过程的创建信息
SHOW CREATE PROCEDURE school.p1;

删除存储过程

-- 删除当前数据库下的存储过程
DROP PROCEDURE p1;

-- 删除指定数据下的指定存储过程
DROP PROCEDURE 数据库名.存储过程名;

更新存储过程

删了重建方便，这里不在多表。

导入导出存储过程

这里再来介绍下，如何将存储过程导出和导入，毕竟在外部文件中，更容易管理存储过程：

-- 导出school数据库下的存储过程
mysqldump -h 10.0.0.200 -P 3306 -uroot -p -ntd -R school > /tmp/my_proc.sql

-- -n: no-create-db
-- -d: no-data
-- -t: no-create-info
-- -R:表示导出函数和存储过程

-- 将存储过程导入到school数据库中
mysql -h 10.0.0.200 -P 3306 -uroot -p school < /tmp/my_proc.sql

最后，我们可以使用存储过程来搞一些事情，如防止SQL注入，但存储过程性能不佳也是不争的事实，大家在使用时要有所取舍。

that's all,see also:

MySQL 存储过程 | mysql存储过程使用DECLARE定义游标遇到的问题 | MySQL游标(cursor) 定义及使用 | MySQL 存储过程参数IN OUT INOUT区别 | [MySQL 游标的使用](https://www.cnblogs.com/oukele/p/10684639.html) | MySQL游标概念与用法详解 | MySQL CURSOR游标 | MySQL 存储过程 | mysql游标的用法及作用 | MySQL存储过程中实现执行动态SQL语句 | MySql带参数的存储过程编写（动态执行SQL语句） | Python开发【第十八篇】：MySQL（二） | [MySQL存储过程]

posted @ 2020-09-24 23:18  听雨危楼  阅读(308)  评论(0)  编辑  收藏  举报