mysql存储过程如何利用临时表返回结果集
1,游标的方法会很慢在mysql中,在oracle数据库中还可以,除非没有别的方法,一般不建议在mysql中使用游标,
2,不建议在mysql中拼接sql,会使存储过程显得很臃肿,可以使用or来动态判别传入的参数是否为空
and ( TTB.office_id=输入参数 or 输入参数 is null or 输入参数 = )and ( TTB.office_id=IN_Office_id or IN_Office_id is null or IN_Office_id = )
3,不建议使用临时表来存储多用户下经常查询的内容,比如报表
4,返回结果集更好的方法是直接链接多个表返回结果集即可,下面的示例虽然给以得到正确结果,
但代码臃肿,速度异常的慢,可以当个反面教材
5,优化后的存储过程:#
测试示例 BEGIN#创建一个临时表
DROP TABLE if exists user_temporary;
create temporary table if not exists user_temporary
(
id VARCHAR(64) primary key,#id
user_name VARCHAR(20) #姓名
) ;
begin
#定义 变量 接收id和姓名
declare a VARCHAR(64);
declare b VARCHAR(20);
#这个用于处理游标到达最后一行的情况
DECLARE s int default 0;
#声明游标cursor_name(cursor_name是个多行结果集)
DECLARE cursor_name CURSOR FOR select id ,name from user ;
#设置一个终止标记
DECLARE CONTINUE HANDLER FOR SQLSTATE 02000 SET s=1;
#打开游标
OPEN cursor_name;
#获取游标当前指针的记录,读取一行数据并传给变量a,b
fetch cursor_name into a,b;
#开始循环,判断是否游标已经到达了最后作为循环条件
while s 1 do
insert into user_temporary(id,user_name) values(a,b);
#读取下一行的数据
fetch cursor_name into a,b;
end while;
#关闭游标
CLOSE cursor_name ;
#从临时表中拿到结果集
SELECT * from user_temporary;
#语句执行结束
end;
END
注意类型 为存储过程 procedure 不是存储函数function
运行结果:
DROP TABLE if exists user_temporary;
create temporary table if not exists user_temporary
(
id VARCHAR(64) primary key,id
user_name VARCHAR(20) ,#姓名
company_name VARCHAR(20) ,#所属公司
worksheet_num INTEGER, #机会点总数
sign_worksheet_num INTEGER ,#签单数量
exchange_num INTEGER ,#填写交流记录次数
sales_volume double(20,2) #销售额
) ;
begin
#定义 变量
declare a_id VARCHAR(64);
declare b_user_name VARCHAR(20);
declare c_company_name VARCHAR(20);
declare d_worksheet_num int ;
declare e_sign_worksheet_num int ;
declare f_exchange_num int ;
declare g_sales_volume double(20,2) ;
#这个用于处理游标到达最后一行的情况
DECLARE s int default 0;
#声明游标cursor_name(cursor_name是个多行结果集)
DECLARE cursor_name CURSOR FOR
select a.id ,a.name ,o.name AS company_name from sys_user a LEFT JOIN sys_office o on a.company_id =o.id;
#设置一个终止标记
DECLARE CONTINUE HANDLER FOR SQLSTATE 02000 SET s=1;
#打开游标
OPEN cursor_name;
#获取游标当前指针的记录,读取一行数据并传给变量a,b
fetch cursor_name into a_id,b_user_name ,c_company_name;
#开始循环,判断是否游标已经到达了最后作为循环条件
while s 1 do
#读取下一行的数据
#声明输入变量,以便在sql串中拼接
set @userId=a_id;
set @beginDate=begin_date;
set @endDate=end_date;
#收集总机会点 有if判断用拼接sql,然后再解析执行sql,
set @exesqlAll =CONCAT( SELECT count(1) into @handle_num FROM crm_worksheet
where create_by=@userId and del_flag=0 );
IF begin_date is not null and begin_date != THEN
set @exesqlAll =CONCAT(@exesqlAll, AND TO_DAYS(create_date) =TO_DAYS(@beginDate) );
END IF;
IF end_date is not null and end_date != THEN
set @exesqlAll =CONCAT(@exesqlAll, AND TO_DAYS(create_date) =TO_DAYS(@endDate) );
END IF;
prepare allWorksheet from @exesqlAll; #解析sql
execute allWorksheet; #执行sql
DEALLOCATE prepare allWorksheet; #释放sql
SET d_worksheet_num = @handle_num; #变量赋值
IF d_worksheet_num 0 THEN
set @exesqlSign =CONCAT( SELECT count(1) into @sign_num FROM crm_worksheet
where create_by=@userId and del_flag=0 and important_degree= sys_basic_qian_shu_he_tong );
IF begin_date is not null and begin_date != THEN
set @exesqlSign =CONCAT(@exesqlSign, AND TO_DAYS(create_date) =TO_DAYS(@beginDate) );
END IF;
IF end_date is not null and end_date != THEN
set @exesqlSign =CONCAT(@exesqlSign, AND TO_DAYS(create_date) =TO_DAYS(@endDate) );
END IF;
prepare signWorksheet from @exesqlSign;
execute signWorksheet;
DEALLOCATE prepare signWorksheet;
SET e_sign_worksheet_num = @sign_num;
#收集交流次数
set @exesqlExchange =CONCAT( SELECT COUNT(1) into @exchange_num from crm_wkst_exchange_record e LEFT JOIN
crm_worksheet w on e.worksheet_no= w.worksheet_no where w.create_by=@userId and w.del_flag=0 );
IF begin_date is not null and begin_date != THEN
set @exesqlExchange =CONCAT(@exesqlExchange, AND TO_DAYS(w.create_date) =TO_DAYS(@beginDate) );
END IF;
IF end_date is not null and end_date != THEN
set @exesqlExchange =CONCAT(@exesqlExchange, AND TO_DAYS(w.create_date) =TO_DAYS(@endDate) );
END IF;
prepare exchangeWorksheet from @exesqlExchange;
execute exchangeWorksheet;
DEALLOCATE prepare exchangeWorksheet;
SET f_exchange_num = @exchange_num;
#收集销售额
set @exesqlSales =CONCAT( SELECT (@sumSalary := @sumSalary + solution) AS count into @sales_num FROM crm_worksheet cw , (SELECT @sumSalary := 0) b
WHERE cw.create_by=@userId and important_degree= sys_basic_qian_shu_he_tong and cw.del_flag=0 ORDER BY count desc limit 1 );
IF begin_date is not null and begin_date != THEN
set @exesqlExchange =CONCAT(@exesqlExchange, AND TO_DAYS(cw.create_date) =TO_DAYS(@beginDate) );
END IF;
IF end_date is not null and end_date != THEN
set @exesqlExchange =CONCAT(@exesqlExchange, AND TO_DAYS(cw.create_date) =TO_DAYS(@endDate) );
END IF;
prepare salesWorksheet from @exesqlSales;
execute salesWorksheet;
DEALLOCATE prepare salesWorksheet;
SET g_sales_volume = @sales_num;
ELSE
SET e_sign_worksheet_num=0; SET f_exchange_num=0; SET g_sales_volume=0;
END IF;
insert into user_temporary(id,user_name,company_name,sign_worksheet_num,exchange_num,worksheet_num,sales_volume)
values(a_id,b_user_name,c_company_name,e_sign_worksheet_num,f_exchange_num,d_worksheet_num,g_sales_volume); #插入临时表
fetch cursor_name into a_id,b_user_name ,c_company_name;
end while;
#关闭游标
CLOSE cursor_name ;
#从临时表中查出结果集
set @userIdInput=user_id;
set @exesqlResult =CONCAT( SELECT user_name,company_name,sign_worksheet_num,exchange_num,worksheet_num,sales_volume from user_temporary );
IF user_id is not null and user_id != THEN
set @exesqlResult =CONCAT(@exesqlResult, where id = @userIdInput );
END IF;
prepare resultUser from @exesqlResult;
execute resultUser;
DEALLOCATE prepare resultUser;
#语句执行结束
end;
END
运行结果
以上为个人经验,希望能给大家一个参考,也希望大家多多支持。
我想要获取技术服务或软件
服务范围:MySQL、ORACLE、SQLSERVER、MongoDB、PostgreSQL 、程序问题
服务方式:远程服务、电话支持、现场服务,沟通指定方式服务
技术标签:数据恢复、安装配置、数据迁移、集群容灾、异常处理、其它问题
本站部分文章参考或来源于网络,如有侵权请联系站长。
数据库远程运维 mysql存储过程如何利用临时表返回结果集
相关文章
- mysql存储过程实例_sql存储过程创建实例详解
- MySQL解锁:免密码登录的简单操作(mysql跳过密码登陆)
- 复制MySQL触发器:实现外部复制(mysql触发器外部)
- MySQL 数据库优化实战: 理论与实践一起学(mysql数据库优化书籍)
- 数据库架设手机MySQL数据库:解决方案(手机mysql)
- 解决MySQL连接失效烦恼(mysql连接失效)
- 备份MySQL 数据文件备份:保护重要信息不丢失(mysql的数据文件)
- 挑战MySQL二级考试,调整你的知识!(mysql二级考试)
- 注册MySQL数据库:简易指南(如何注册mysql)
- MySQL中如何删除用户账号(mysql如何删除用户)
- MySQL处理二进制数据的技巧(mysql二进制数据)
- MySQL管理二进制数据的指南(mysql二进制数据)
- MySQL如何重新索引以提高性能(mysql重新索引)
- MySQL如何找回密码(mysql找回密码)
- MySQL 数据库如何进行四舍五入操作?(mysql四舍五入)
- MySQL如何监控和解决锁表问题?(mysql监控锁表)
- 如何禁用MySQL触发器?(mysql禁止触发器)
- MySQL 如何使用两列和聚合函数进行数据分析?(mysql两列和)
- MySQL 数据库备份文件类型:LDF 详解(ldf文件mysql)
- 使用MySQL存储精确时间:掌握存时分秒的方法(mysql存时分秒)
- MySQL如何创建数据库?(mysql 如何创建数据库)
- 如何找回误删的 MySQL 数据库?(mysql数据库找回)
- 如何在MySQL中修改用户权限(mysql修改权限)
- MySQL查询过滤:优化数据检索的技巧(mysql查询过滤)
- MySQL登录快捷方式:从指定数据库中快速连接(mysql登录指定数据库)
- ASP程序如何利用MySQL更好地实现服务(asp需要用到mysql)
- 如何在MySQL中正确插入变量(mysql不能插入$变量)
- MySQL如何利用不等式索引提高查询效率(mysql 不等式 索引)
- 如何解决MySQL无法打开端口的问题(mysql不打开端口使用)