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

首先要声明的是

1，游标的方法会很慢在mysql中，在oracle数据库中还可以，除非没有别的方法，一般不建议在mysql中使用游标，

2，不建议在mysql中拼接sql，会使存储过程显得很臃肿，可以使用or来动态判别传入的参数是否为空

?

1 2	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，优化后的存储过程：http://www.tuohang.net/article/254035.html

测试示例 

?

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 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59

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

运行结果：

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

?

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 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139 140 141 142 143 144 145 146 147 148 149 150 151 152 153 154 155 156 157 158 159 160 161 162 163 164 165 166 167 168 169 170 171 172 173 174 175 176 177 178 179

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;                          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

运行结果

以上为个人经验，希望能给大家一个参考，也希望大家多多支持服务器之家。

原文链接：https://blog.csdn.net/zhaofuqiangmycomm/article/details/88651446