-- 一个奇怪的My SQL游标循环问题 --

luoyoumou 2011-12-05 03:07:26 
-- 我的一个函数

-- 其作用是生成一个可执行的SQL语句，

-- 目的是生成“查询一张表的int类型字段的所有max、min、avg值”的SQL语句的函数：



DELIMITER $$



CREATE FUNCTION get_tb_numberInfo(v_tb_name varchar(50))

  RETURNS VARCHAR(2000) CHARSET utf8

BEGIN

  DECLARE v_sql varchar(20000);



  DECLARE v_column_name varchar(200);

  DECLARE v_column_name_min varchar(200);

  DECLARE v_column_name_max varchar(200);

  DECLARE v_column_name_avg varchar(200);

  DECLARE no_more_departments boolean;



  DECLARE get_tbInfo CURSOR FOR

    SELECT column_name FROM information_schema.columns 

     WHERE UPPER(TABLE_NAME)=upper(v_tb_name)

       AND TABLE_SCHEMA=(select database())

       AND DATA_TYPE IN('bigint','int','decimal');



  DECLARE CONTINUE HANDLER FOR NOT FOUND SET no_more_departments=1;

  SET no_more_departments=0; 

  SET v_sql := 'SELECT ';

  OPEN get_tbInfo;

  REPEAT

  -- Get get_tbInfo 

  FETCH get_tbInfo INTO v_column_name;

    SET v_column_name_min = concat('min(',v_column_name,') as ',v_column_name,'_min,');

    SET v_column_name_max = concat('max(',v_column_name,') as ',v_column_name,'_max,');

    SET v_column_name_avg = concat('avg(',v_column_name,') as ',v_column_name,'_avg,');

    SET v_sql = concat(v_sql,v_column_name_min,v_column_name_max,v_column_name_avg,'\n');

  UNTIL no_more_departments

  END REPEAT;

  CLOSE get_tbInfo;



  SET v_sql = substring(v_sql,1,length(v_sql)-1);

  SET v_sql = concat(v_sql,' from ',v_tb_name,';');



  RETURN v_sql;

END;

$$



delimiter ;



-- 但是：我每次执行后，最后一个字段生成了两次：min、max、avg字段，例如：

mysql> select get_tb_numberInfo('f_union_refer_date_index');



 SELECT

min(date_id) as date_id_min,max(date_id) as date_id_max,avg(date_id) as date_id_avg,

min(refer_id) as refer_id_min,max(refer_id) as refer_id_max,avg(refer_id) as refer_id_avg,

min(ip_num) as ip_num_min,max(ip_num) as ip_num_max,avg(ip_num) as ip_num_avg,

min(direct_pv_num) as direct_pv_num_min,max(direct_pv_num) as direct_pv_num_max,avg(direct_pv_num) as direct_pv_num_avg,

min(indirect_pv_num) as indirect_pv_num_min,max(indirect_pv_num) as indirect_pv_num_max,avg(indirect_pv_num) as indirect_pv_num_avg,

min(direct_cv_num) as direct_cv_num_min,max(direct_cv_num) as direct_cv_num_max,avg(direct_cv_num) as direct_cv_num_avg,

min(indirect_cv_num) as indirect_cv_num_min,max(indirect_cv_num) as indirect_cv_num_max,avg(indirect_cv_num) as indirect_cv_num_avg,

min(direct_uv_num) as direct_uv_num_min,max(direct_uv_num) as direct_uv_num_max,avg(direct_uv_num) as direct_uv_num_avg,

min(new_uv_num) as new_uv_num_min,max(new_uv_num) as new_uv_num_max,avg(new_uv_num) as new_uv_num_avg,

min(new_uv_num) as new_uv_num_min,max(new_uv_num) as new_uv_num_max,avg(new_uv_num) as new_uv_num_avg from f_union_refer_date_index;



-- 请问是怎么回事儿呢？有什么办法解决不？
...全文
181 12 打赏 收藏 转发到动态 举报
写回复
用AI写文章
12 条回复
切换为时间正序
请发表友善的回复…
发表回复
Diana_24 2012-02-28
  • 打赏
  • 举报
 回复
同意楼主的做法，我也觉得可以这么解决，但是究竟有没有单纯用循环语句而不用判断语句就执行正确的方法呢？求赐教！！！
ACMAIN_CHM 2011-12-05
  • 打赏
  • 举报
 回复
最后的重复的原因是你的FETCH get_tbInfo INTO v_column_name;的时候，当第一次到达最后一条记录时，取出记录，把值给变量，这时一切正常，没有错误。然后UNTIL no_more_departments循环到REPEAT重新开始，再FETCH一条时，已经没有记录，no_more_departments会被 SET no_more_departments=1;，但此刻你没有进行任何判断，程序会继续执行SET v_column_name_min 。。。语句，而由于FETCH没有取到记录，则没有对变量进行赋值，所以变量仍是原值。当到UNTIL no_more_departments时程序退出REPEAT，从而你最后的记录会被重复。


wwwwb 2011-12-05
  • 打赏
  • 举报
 回复
[Quote=引用 6 楼 luoyoumou 的回复:]
引用 2 楼 wwwwb 的回复:

DELIMITER $$
DROP FUNCTION IF EXISTS get_tb_numberInfo$$
CREATE FUNCTION get_tb_numberInfo(v_tb_name VARCHAR(50))
RETURNS VARCHAR(2000) CHARSET utf8
BEGIN
DECLARE v_sql VARCH……
[/Quote]
不会吧我测试过，是可以的，建议不用REPEAT，用WHILE DO
luoyoumou 2011-12-05
  • 打赏
  • 举报
 回复 
-- 嗯，呵呵，我解决啦：



DELIMITER $$



DROP FUNCTION IF EXISTS get_tb_numberInfo$$



CREATE FUNCTION get_tb_numberInfo(v_tb_name varchar(50))

  RETURNS VARCHAR(2000) CHARSET utf8

BEGIN

  DECLARE v_sql varchar(20000);



  DECLARE v_column_name varchar(200);

  DECLARE v_column_name_min varchar(200);

  DECLARE v_column_name_max varchar(200);

  DECLARE v_column_name_avg varchar(200);

  DECLARE no_more_departments INT;



  DECLARE get_tbInfo CURSOR FOR

    SELECT column_name FROM information_schema.columns 

     WHERE UPPER(TABLE_NAME)=upper(v_tb_name)

       AND TABLE_SCHEMA=(select database())

       AND DATA_TYPE IN('bigint','int','decimal');



  DECLARE CONTINUE HANDLER FOR NOT FOUND

    SET no_more_departments=1;

  SET no_more_departments=0;

  SET v_sql := 'SELECT ';

  OPEN get_tbInfo;

  REPEAT

  -- Get get_tbInfo 

  FETCH get_tbInfo INTO v_column_name;

  IF no_more_departments = 0 THEN

    SET v_column_name_min = concat('min(',v_column_name,') as ',v_column_name,'_min,');

    SET v_column_name_max = concat('max(',v_column_name,') as ',v_column_name,'_max,');

    SET v_column_name_avg = concat('avg(',v_column_name,') as ',v_column_name,'_avg,');

    SET v_sql = concat(v_sql,'\n',v_column_name_min,v_column_name_max,v_column_name_avg);

  END IF;

  UNTIL no_more_departments = 1

  END REPEAT;

  CLOSE get_tbInfo;



  SET v_sql = substring(v_sql,1,length(v_sql)-1);

  SET v_sql = concat(v_sql,' from ',v_tb_name,';');



  RETURN v_sql;

END;

$$





DELIMITER ;
聪明的一休 2011-12-05
  • 打赏
  • 举报
 回复
FETCH 后要立刻判断状态标志，做出对应的处理。
luoyoumou 2011-12-05
  • 打赏
  • 举报
 回复 
-- 我这个表有10个数值类型的字段，

mysql> desc f_union_date_uid_index;

+-----------------+-------------+------+-----+---------+-------+

| Field           | Type        | Null | Key | Default | Extra |

+-----------------+-------------+------+-----+---------+-------+

| date_id         | int(8)      | NO   | PRI | NULL    |       |

| union_id        | varchar(10) | NO   | PRI | NULL    |       |

| out_ip_num      | int(10)     | YES  |     | NULL    |       |

| out_cv_num      | int(8)      | YES  |     | NULL    |       |

| ip_num          | int(10)     | YES  |     | NULL    |       |

| direct_pv_num   | int(10)     | YES  |     | NULL    |       |

| indirect_pv_num | int(10)     | YES  |     | NULL    |       |

| direct_cv_num   | int(10)     | YES  |     | NULL    |       |

| indirect_cv_num | int(10)     | YES  |     | NULL    |       |

| direct_uv_num   | int(10)     | YES  |     | NULL    |       |

| new_uv_num      | int(10)     | YES  |     | NULL    |       |

+-----------------+-------------+------+-----+---------+-------+

11 rows in set (0.00 sec)



-- 但是执行2楼你的存储过程，只得到了6个字段生成的SQL语句：



  SELECT min(date_id) as date_id_min,max(date_id) as date_id_max,avg(date_id) as date_id_avg,

min(out_cv_num) as out_cv_num_min,max(out_cv_num) as out_cv_num_max,avg(out_cv_num) as out_cv_num_avg,

min(direct_pv_num) as direct_pv_num_min,max(direct_pv_num) as direct_pv_num_max,avg(direct_pv_num) as direct_pv_num_avg,

min(direct_cv_num) as direct_cv_num_min,max(direct_cv_num) as direct_cv_num_max,avg(direct_cv_num) as direct_cv_num_avg,

min(direct_uv_num) as direct_uv_num_min,max(direct_uv_num) as direct_uv_num_max,avg(direct_uv_num) as direct_uv_num_avg,

min(new_uv_num) as new_uv_num_min,max(new_uv_num) as new_uv_num_max,avg(new_uv_num) as new_uv_num_avg, from f_union_date_uid_index;
luoyoumou 2011-12-05
  • 打赏
  • 举报
 回复
[Quote=引用 2 楼 wwwwb 的回复:]

DELIMITER $$
DROP FUNCTION IF EXISTS get_tb_numberInfo$$
CREATE FUNCTION get_tb_numberInfo(v_tb_name VARCHAR(50))
RETURNS VARCHAR(2000) CHARSET utf8
BEGIN
DECLARE v_sql VARCHAR(20000);

……
[/Quote]

-- 这样是没有重复生成SQL语句的字段啦，但是：好像又少了些字段没有生成SQL语句！
nicenight 2011-12-05
  • 打赏
  • 举报
 回复
导致的原因是在循环体中获取记录的时候，虽然最后一条没有获取到，但你是执行了四句 set 以后才“END REPEAT”的，因为没有获取到记录，变量中的内容就是最后一次的信息，所以就把前一次的结果再加了一次。
nicenight 2011-12-05
  • 打赏
  • 举报
 回复
循环体这样改改看：
  FETCH get_tbInfo INTO v_column_name;

  REPEAT

  -- Get get_tbInfo 

    SET v_column_name_min = concat('min(',v_column_name,') as ',v_column_name,'_min,');

    SET v_column_name_max = concat('max(',v_column_name,') as ',v_column_name,'_max,');

    SET v_column_name_avg = concat('avg(',v_column_name,') as ',v_column_name,'_avg,');

    SET v_sql = concat(v_sql,v_column_name_min,v_column_name_max,v_column_name_avg,'\n');

  FETCH get_tbInfo INTO v_column_name;

  UNTIL no_more_departments

  END REPEAT;
jfhyn 2011-12-05
  • 打赏
  • 举报
 回复
没用过,同群,顶一下再说,
wwwwb 2011-12-05
  • 打赏
  • 举报
 回复
DELIMITER $$
DROP FUNCTION IF EXISTS get_tb_numberInfo$$
CREATE FUNCTION get_tb_numberInfo(v_tb_name VARCHAR(50))
RETURNS VARCHAR(2000) CHARSET utf8
BEGIN
DECLARE v_sql VARCHAR(20000);

DECLARE v_column_name VARCHAR(200);
DECLARE v_column_name_min VARCHAR(200);
DECLARE v_column_name_max VARCHAR(200);
DECLARE v_column_name_avg VARCHAR(200);
DECLARE no_more_departments BOOLEAN;

DECLARE get_tbInfo CURSOR FOR
SELECT column_name FROM information_schema.columns
WHERE UPPER(TABLE_NAME)=UPPER(v_tb_name)
AND TABLE_SCHEMA=(SELECT DATABASE())
AND DATA_TYPE IN('bigint','int','decimal');

DECLARE CONTINUE HANDLER FOR NOT FOUND SET no_more_departments=1;
SET no_more_departments=0;
SET v_sql := 'SELECT ';
OPEN get_tbInfo;
WHILE no_more_departments=0 DO
-- Get get_tbInfo
FETCH get_tbInfo INTO v_column_name;
SET v_column_name_min = CONCAT('min(',v_column_name,') as ',v_column_name,'_min,');
SET v_column_name_max = CONCAT('max(',v_column_name,') as ',v_column_name,'_max,');
SET v_column_name_avg = CONCAT('avg(',v_column_name,') as ',v_column_name,'_avg,');
SET v_sql = CONCAT(v_sql,v_column_name_min,v_column_name_max,v_column_name_avg,'\n');
-- select v_sql;
FETCH get_tbInfo INTO v_column_name;
END WHILE ;
CLOSE get_tbInfo;

SET @v_sql = SUBSTRING(v_sql,1,LENGTH(v_sql)-1);
-- SELECT @v_sql;
SET v_sql = CONCAT(@v_sql,' from ',v_tb_name,';');

RETURN v_sql;
END;
$$

DELIMITER ;

SELECT get_tb_numberInfo('attachment');

MySQL存储过程中游标循环的跳出和继续操作示例
最近遇到这样的问题,在MySQL的存储过程中,游标操作时,需要执行一个conitnue的操作.众所周知,MySQL中的游标循环操作常用的有三种,LOOP,REPEAT,WHILE.三种循环,方式大同小异.以前从没用过,所以记下来,方便以后查阅. 1.REPEAT 代码如下: REPEAT     Statements;   UNTIL expression END REPEAT demo DECLARE num INT; DECLARE my_string  VARCHAR(255); REPEAT SET  my_string =CONCAT(my_string,num,’,’); SET
SQL工作笔记-达梦7存储过程中游标的使用(for循环 IF等)
表如下： 如下存储过程使用游标遍历所有数据： CREATE OR REPLACE PROCEDURE "CFFTEST"."SELECT_STUDENT"("id" IN INT) AS myId int; myName varchar(50); cursor myCursor is select id, name from cfftest.student; BEGIN open myCursor; loop fetch myCursor into myId, myName; exit when myCursor%notfound; print 'id: ' ||
SQL Server 2014 索引和游标
主讲内容： 第一讲 课程简介 第二讲 索引简介 第三讲 创建索引 第四讲 查看索引 第五讲 索引管理 第六讲 全文索引 等课程
SQL中的循环、for循环、游标
--SQL中的循环、for循环、游标 --1. 利用游标循环更新、删除MemberAccount表中的数据 DECLARE My_Cursor CURSOR --定义游标 FOR (SELECT * FROM dbo.MemberAccount) --查出需要的集合放到游标中 OPEN My_Cursor; --打开游标 FETCH NEXT FROM My_Cursor ; --读取第一...
SQL游标(cursor)详细说明及内部循环使用示例
SQL游标(cursor)详细说明及内部循环使用示例 ​ 游标 　　游标（cursor）是系统为用户开设的一个数据缓冲区，存放SQL语句的执行结果。每个游标区都有一个名字,用户可以用SQL语句逐一从游标中获取记录，并赋给主变量，交由主语言进一步处理。 　　游标是处理结果集的一种机制吧，它可以定位到结果集中的某一行，多数据进行读写，也可以移动游标定位到你所需要的行中进行操作数据。一般复杂的存储过程，都会有游标的出现，他的用处主要有： 定位到结果集中的某一行。 对当前位置的数据进行读写。
MySQL

56,687

社区成员

56,710

社区内容

发帖
与我相关
我的任务
社区描述
MySQL相关内容讨论专区
社区管理员
  • MySQL
加入社区
  • 近7日
  • 近30日
  • 至今

加载中

查看更多榜单
社区公告
暂无公告

试试用AI创作助手写篇文章吧

+ 用AI写文章