探讨MySQL存储过程返回记录集

探讨MySQL存储过程返回记录集

1.问题描述

通过存储过程计算统计后，返回程序一个记录集。如果使用表，把记录集保存在过渡表中，在多用户访问的时候，表的数据可能不可控。
使用MySQL 的临时内存表，可以实现在独立会话之间互不可见，而且会话结束后，自动删除。

2.测试程序

（1）存储过程

存储过程用途，入口参数，学生分数的区间范围值，返回值是分数区间的人数。
同时将相应的学生信息保存在临时表中，用于查询。

create temporary table tmp_student (name varchar(50),grade int ,birthday date) engine=memory ;

存储过程如下：

CREATE DEFINER=`root`@`%` PROCEDURE `pro_test_temptb_cursor`(in start_grade int, in end_grade int,out cur_count int)
begin
		declare cur_done int default 1;
		declare var_name varchar(50);
		declare var_grade int;
		declare var_birthday date;
		
		declare cur_student cursor for select t.name,t.grade,t.birthday from tb_student t 
			where t.grade>=start_grade and t.grade<=end_grade order by t.grade desc;
		declare continue handler for not found set cur_done = 0;

		open cur_student;

		drop table if exists tmp_student;	
		create temporary table tmp_student (name varchar(50),grade int ,birthday date) engine=memory ;

		while cur_done <> 0  do
				fetch next from cur_student into var_name,var_grade, var_birthday ;
				
				if cur_done = 1 then
					insert into tmp_student values (var_name,var_grade, var_birthday);
				end if;
		end while;
		select count(*) into cur_count from tmp_student;

		close cur_student;
		
end
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

存储过程执行测试效果：
测试95-100的学生数量，学生信息保存在临时表；

mysql> call pro_test_temptb_cursor(95,100,@curnum) ;
Query OK, 1 row affected (0.01 sec)

mysql> select @curnum;
+---------+
| @curnum |
+---------+
|      12 |
+---------+
1 row in set (0.00 sec)

1
2
3
4
5
6
7
8
9
10
11

查询临时表：

mysql> select * from tmp_student t;
+------------+-------+------------+
| name       | grade | birthday   |
+------------+-------+------------+
| UFCKQYIPFD |   100 | 2023-09-23 |
| PYRESDIMBG |    99 | 2023-10-22 |
| WKJVXXFKGY |    99 | 2023-10-25 |
| GTMWQCYWDP |    98 | 2023-11-02 |
| PDSMXIGYNW |    98 | 2023-08-31 |
| OQECTGEEGA |    98 | 2023-07-20 |
| PEKZMUMXIG |    98 | 2023-09-04 |
| IVHWJQYABC |    97 | 2023-10-27 |
| LJMANWXXOQ |    97 | 2023-10-27 |
| ABLSJRRXHE |    96 | 2023-07-15 |
| VBEYEQRYIX |    96 | 2023-07-26 |
| KPFMTKCJGQ |    95 | 2023-10-13 |
+------------+-------+------------+
12 rows in set (0.00 sec)

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19

临时表的特点：

• 不保存在数据库结构中。
• 当前会话可查询，如果新建立一个会话，也无法查询tmp_student 表。
• 假设创建一个和已有的普通表名字相同的临时表，该会话只能看到临时表而看不见同名的普通表。当临时表被删除后，才可以看到普通表。就是说临时表的优先级高于普通表。

mysql> show tables like 'tmp_student';
Empty set (0.00 sec)

1
2
3

（2）通过Python调用存储过程的记录集

from mysql.connector import MySQLConnection, Error
import pymysql

conn = MySQLConnection(**global_config)
cursor = conn.cursor()

para =[95,100,0]
result = cursor.callproc('pro_test_temptb_cursor',para)

print('result[0]:' ,result[0])
print('result:',result)
print('para:',para)

# 测试临时表
sql_string = 'select * from tmp_student t;'
record = cursor.execute(sql_string)

print('record:',record)

res = cursor.fetchall()
cursor.close()
conn.close()

for r in res :
    print(r)
    #print(r[0],r[1],r[2])    
    
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

执行结果：

result[0]: 95
result: (95, 100, 12)
para: [95, 100, 0]
record: None
('UFCKQYIPFD', 100, datetime.date(2023, 9, 23))
('PYRESDIMBG', 99, datetime.date(2023, 10, 22))
('WKJVXXFKGY', 99, datetime.date(2023, 10, 25))
('GTMWQCYWDP', 98, datetime.date(2023, 11, 2))
('PDSMXIGYNW', 98, datetime.date(2023, 8, 31))
('OQECTGEEGA', 98, datetime.date(2023, 7, 20))
('PEKZMUMXIG', 98, datetime.date(2023, 9, 4))
('IVHWJQYABC', 97, datetime.date(2023, 10, 27))
('LJMANWXXOQ', 97, datetime.date(2023, 10, 27))
('ABLSJRRXHE', 96, datetime.date(2023, 7, 15))
('VBEYEQRYIX', 96, datetime.date(2023, 7, 26))
('KPFMTKCJGQ', 95, datetime.date(2023, 10, 13))
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16

运行结果说明：
1、入口参数的para ，执行完存储过程，没有返回结果，para: [95, 100, 0] 是list，para[2]是0；
2、返回参数result ，执行完存储过程，result: (95, 100, 12) 是元组，result[2]是12 ；
3、record: None ，Python调用存储过程的返回值 ，居然是None ，不是记录数。