在存储过程中使用游标实现根据A表的项目依次查询B表的功能,最后把数据都放到临时表(temporary )中,返回一个结果集。如不放到临时表中返回结果集,每次循环都会返回一个结果集。具体存储过程的代码如下:
# Host: 192.168.*.* (Version: 5.5.20)
# Generator: MySQL-Front 5.3 (Build 4.4)
CREATE PROCEDURE `GetWorkloadByWard`(IN cWard varchar(20),IN dStart datetime,IN dEnd datetime)
BEGIN
-- 不存在则创建临时表
create temporary table if not exists tmpTable
(
Name varchar(50),
Count varchar(10)
);
-- 使用前先清空临时表。
truncate TABLE tmpTable;
BEGIN
-- 声明一个标志done,判断游标是否遍历完成
DECLARE done INT DEFAULT 0;
-- 声明变量存放从游标中提取的数据,名字不能与游标中使用的列名相同
DECLARE iname varchar(50);
DECLARE itype varchar(20);
DECLARE src varchar(20);
DECLARE cate varchar(20);
DECLARE keyvalue varchar(50);
-- 为了处理方便,关键字最多有三个
DECLARE key1 varchar(20) DEFAULT '';
DECLARE key2 varchar(20) DEFAULT '';
DECLARE key3 varchar(20) DEFAULT '';
-- 记录关键字分隔符的位置
DECLARE location INT DEFAULT 0;
-- 声明游标对应的 SQL 语句
DECLARE cw CURSOR FOR
select FS01, FE10, FE11, FS10, FS20 from w432 order by FI01;
-- 游标循环到最后时会将 done 设置为 1,此时说明循环结束
DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = 1;
-- 打开游标,开始循环
open cw;
-- 遍历每一行
REPEAT
-- 把每一行的数据放到对应的变量中
FETCH cw INTO iname, itype, src, cate, keyvalue;
if(not done) then
-- 下面可以使用参数对应的数据做需要的处理
-- 先给三个关键字初始值,给变量设置值的时候要使用 set
set key1 = '';
set key2 = '';
set key3 = '';
-- 只处理任务的相关数据
if(itype = 'task') then
-- 处理关键字,判断条件的写法和sql的where条件的写法类似
if(keyvalue is not null and keyvalue != '') then
-- keyvalue的值不为空时keyvalue是关键字,为空时iname的值是关键字
-- 第一个关键字 key1
-- 使用LOCATE()函数获取指定字符的位置
set location = LOCATE(',',keyvalue);
-- 大于0说明存在指定字符
if(location > 0) then
-- 截取指定字符左边的内容给关键字key1,然后截取指定字符左边的内容给自身,用于后续处理
set key1 = SUBSTRING(keyvalue,1,location-1);
set keyvalue = SUBSTRING(keyvalue,location+1);
-- 第二个关键字 key2 ,处理类似 key1
set location = LOCATE(',',keyvalue);
if(location > 0) then
set key2 = SUBSTRING(keyvalue,1,location-1);
set keyvalue = SUBSTRING(keyvalue,location+1);
-- 第三个关键字 key3 ,处理类似 key1
set location = LOCATE(',',keyvalue);
if(location > 0) then
set key3 = SUBSTRING(keyvalue,1,location-1);
#set keyvalue = SUBSTRING(keyvalue,location+1);
else
if(keyvalue is not null and keyvalue != '') then
set key3 = keyvalue;
end if;
end if;
else
-- 如果不包含指定字符,则 keyvalue 就是关键字
if(keyvalue is not null and keyvalue != '') then
set key2 = keyvalue;
end if;
end if;
else
set key1 = keyvalue;
end if;
end if;
-- 处理三个键值,方便后面查询语句的使用
if(key1 is not null and key1 != '') then
if(key2 is null or key2 = '') then
set key2 = key1;
end if;
if(key3 is null or key3 = '') then
set key3 = key1;
end if;
else
-- 如果三个键的值都是空,说明关键字是 iname
set key1 = iname;
set key2 = iname;
set key3 = iname;
end if;
-- 处理类别,如果类别存在,增加按类别查找的条件
if(cate is not null and cate != '') then
-- "INSERT INTO Table(ID,NAME) SELECT ID,NAME FROM OtherTable;"
-- 把从OtherTable中查出的数据插入Table表中
INSERT INTO tmpTable(Name,Count)
SELECT iname,COUNT(*) FROM wg_tasks t LEFT JOIN wg_users u ON t.uid=u.id
WHERE u.region=cWard AND t.status='T' AND t.exectime BETWEEN dStart AND dEnd AND t.cateName=cate
AND (t.advText LIKE CONCAT('%',key1,'%') OR t.advText LIKE CONCAT('%',key2,'%') OR t.advText LIKE CONCAT('%',key3,'%'));
else
INSERT INTO tmpTable(Name,Count)
SELECT iname,COUNT(*) FROM wg_tasks t LEFT JOIN wg_users u ON t.uid=u.id
WHERE u.region=cWard AND t.status='T' AND t.exectime BETWEEN dStart AND dEnd
AND (t.advText LIKE CONCAT('%',key1,'%') OR t.advText LIKE CONCAT('%',key2,'%') OR t.advText LIKE CONCAT('%',key3,'%'));
end if;
else
-- 非任务的项目暂不处理,但是为了显示,依然取出
INSERT INTO tmpTable(Name,Count) SELECT iname,'';
end if;
end if;
UNTIL done END REPEAT;
CLOSE cw;
-- 查找临时表,把数据返回
SELECT * FROM tmpTable;
END;
END;
关于上面SQL代码用到的一些知识点:
1、临时表:
创建临时表后要跟 BEGIN…END,在此BEGIN…END中做存储过程的具体的处理,否则创建临时表时容易引起错误。
注意:临时表的空间占用受 tem_table_size 限制,到达上限后MySQL自动把它转化为基于磁盘的MyISAM表保存到磁盘,可以包含TEXT, BLOB等字段。与之相似的有一个内存表,内存表的空间占用受 max_heap_table_size 限制,这个变量支持动态改变,即set @max_heap_table_size=1024M ,但对于已经存在的内存表没有什么作用,除非表被重新创建(create table)或者修改(alter table)或者truncate table,到达上限后会提示数据满错误,不能包含TEXT,BLOB等字段。
2、用到的两个字符串处理函数:
LOCATE(substr, str); LOCATE(substr, str, pos);
返回子串 substr 在字符串 str 中第一次出现的位置,如果使用 pos 参数则从 pos 位置后开始查找,如果在 str 中未找到子串 substr 返回 0;如代码 LOCATE(‘a’, ‘abcde’) 返回 1,可见在某些情况下 mysql 的下标是从 1 开始的。速度上比 like 稍快,但是不能使用索引。
SUBSTRING(str, pos); SUBSTRING(str, pos, length);
返回从 pos 位置开始截取 str 得到的字符串,如果使用 length 参数则截取 <=length 长度的字符串(剩余字符不够length长度时截取到最后);如果 pos 参数的值是负数(如 -5)则从 str 最后一位向前数5位开始截取字符串。
3、表复制:
格式1:INSERT INTO Table2(field1, field2…) SELECT va,vb… FROM Table1
注意点:表 Table2 必须存在,相应的各 field 字段也必须存在,不仅可以插入 Table1 的字段还可以插入常量。
格式2:SELECT v1,v2… INTO Table2 FROM Table1
注意点: 要求表 Table2 不存在,在插入时会自动创建表 Table2,并将Table1中的指定字段的数据复制到Table2中。
结束语:
人生就是要和对的人,以大家都舒服的方式,一起度过。