mysql存储过程如何利用临时表返回结果集

作者:你好龙卷风!!!

这篇文章主要介绍了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,优化后的存储过程: https://www.jb51.net/article/263729.htm

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;  
    #语句执行结束  

注意类型 为存储过程 procedure 不是存储函数function

运行结果:

真实需求,查找出所有用建单情况

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) ,#姓名
						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;
							 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;
    #语句执行结束  

以上为个人经验,希望能给大家一个参考,也希望大家多多支持脚本之家。

您可能感兴趣的文章:
  • MySQL新手入门进阶语句汇总
    MySQL新手入门进阶语句汇总
    2022-09-09
  • Mysql中DATEDIFF函数的基础语法及练习案例
    Mysql中DATEDIFF函数的基础语法及练习案例
    2022-09-09
  • MySQL下载安装配置详细教程 附下载资源
    MySQL下载安装配置详细教程 附下载资源
    2022-09-09
  • MYSQL 数据库时间字段 INT,TIMESTAMP,DATETIME 性能效率的比较介绍
    MYSQL 数据库时间字段 INT,TIMESTAMP,DATETIME 性能效率的比较介绍
    2022-09-09
  • 如何优化sql中的orderBy语句
    如何优化sql中的orderBy语句
    2022-09-09
  • mysql存储过程如何利用临时表返回结果集
    mysql存储过程如何利用临时表返回结果集
    2022-09-09
  • MySQL中的存储过程异常处理
    MySQL中的存储过程异常处理
    2022-09-09
  • MySQL 中这么多索引该怎么选择
    MySQL 中这么多索引该怎么选择
    2022-09-09
  • 美国设下计谋,用娘炮文化重塑日本,已影响至中国
    美国设下计谋,用娘炮文化重塑日本,已影响至中国
    2021-11-19
  • 时空伴随者是什么意思?时空伴随者介绍
    时空伴随者是什么意思?时空伴随者介绍
    2021-11-09
  • 工信部称网盘企业免费用户最低速率应满足基本下载需求,天翼云盘回应:坚决支持,始终
    工信部称网盘企业免费用户最低速率应满足基本下载需求,天翼云盘回应:坚决支持,始终
    2021-11-05
  • 2022年放假安排出炉:五一连休5天 2022年所有节日一览表
    2022年放假安排出炉:五一连休5天 2022年所有节日一览表
    2021-10-26
  • 电脑版 - 返回首页

    2006-2023 脚本之家 JB51.Net , All Rights Reserved.
    苏ICP备14036222号