Loading

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  听雨危楼  阅读(152)  评论(0编辑  收藏  举报