首页
学习
活动
专区
圈层
工具
发布
社区首页 >问答首页 >MySQL -存储过程中的游标不工作

问MySQL -存储过程中的游标不工作
EN

Stack Overflow用户
提问于 2012-12-06 21:03:20
回答 2查看 1.3K关注 0票数 1

我看到以前有人问过这个问题,但没有提供答案。

我使用游标来解析SELECT语句返回的数据。但是,与在命令行中运行SELECT语句相比,它不会返回我期望的数据。

代码语言:javascript
复制
Broker                  Year    Month   Total
Houlder Insurance           2011    10  1
Houlder Insurance           2011    11  1
Houlder Insurance           2011    12  1
Incepta                 2012    6   4
Incepta                 2012    7   6
Incepta                 2012    8   4
Kay International Plc   2011    10  4
Kay International Plc   2011    11  5
Kay International Plc   2011    12  2
Kay International Plc   2012    1   1
Kay International Plc   2012    2   1
Kay International Plc   2012    4   4
Kay International Plc   2012    5   6
Kay International Plc   2012    6   2
Kay International Plc   2012    7   1
Kay International Plc   2012    8   1
Miles Smith                 2011    12  3
Miles Smith                 2012    2   1
Miles Smith                 2012    4   1
Miles Smith                 2012    5   6
Miles Smith                 2012    6   1
Miles Smith                 2012    7   2
Miles Smith                 2012    8   2

当我运行以下存储过程代码时,它似乎只返回与代理"Kay International“和"Incepta”相关的记录!有没有解决这个问题的办法?

代码语言:javascript
复制
    CREATE DEFINER = 'root'@'localhost'
PROCEDURE src_survey.PopulateReportMonthSurveySubmitted()
BEGIN DECLARE broker    TEXT;
  DECLARE yy        INT;
  DECLARE jan       INT;
  DECLARE feb       INT;
  DECLARE mar       INT;
  DECLARE apr       INT;
  DECLARE may       INT;
  DECLARE jun       INT;
  DECLARE jul       INT;
  DECLARE aug       INT;
  DECLARE sep       INT;
  DECLARE toct      INT;
  DECLARE nov       INT;
  DECLARE tdec      INT;

  DECLARE db_broker TEXT;
  DECLARE db_year   INT;
  DECLARE db_month  INT;
  DECLARE db_total  INT;

  DECLARE eof       INT;
  DECLARE cur1 CURSOR FOR SELECT b.company_name AS 'Broker'
                               , year(submit_date_time) AS 'Year'
                               , month(submit_date_time) AS 'Month'
                               , count(*) AS 'Total'
                          FROM
                            survey_request a
                          JOIN survey d
                          ON d.src_reference = a.src_reference
                          JOIN contacts_companies b
                          ON b.reference = a.broker_company_reference
                          JOIN src_status c
                          ON c.reference = a.src_status_reference
                          WHERE
                            underwriter_company_reference = 73
                          GROUP BY
                            broker
                          , year
                          , month
                          ORDER BY
                            broker
                          , year
                          , month;
  DECLARE CONTINUE HANDLER FOR NOT FOUND SET eof = 1;

  SET broker = '';
  SET yy = 0;
  SET jan = 0;
  SET feb = 0;
  SET mar = 0;
  SET apr = 0;
  SET may = 0;
  SET jun = 0;
  SET jul = 0;
  SET aug = 0;
  SET sep = 0;
  SET toct = 0;
  SET nov = 0;
  SET tdec = 0;

  TRUNCATE TABLE reportmonthsurveysubmitted;
  OPEN cur1;
  SET eof = 0;

  WHILE eof = 0
  DO
    FETCH cur1 INTO db_broker, db_year, db_month, db_total;
    IF broker <> db_broker THEN
      SET broker = db_broker;
      SET yy = 0;
    END IF;
    IF yy <> db_year THEN
      IF yy <> 0 THEN
        INSERT INTO reportmonthsurveysubmitted VALUES (0, broker, yy, jan, feb, mar, apr, may, jun, jul, aug, sep, toct, nov, tdec);
      END IF;
      SET yy = db_year;
      SET jan = 0;
      SET feb = 0;
      SET mar = 0;
      SET apr = 0;
      SET may = 0;
      SET jun = 0;
      SET jul = 0;
      SET aug = 0;
      SET sep = 0;
      SET toct = 0;
      SET nov = 0;
      SET tdec = 0;
    END IF;
    CASE db_month
    WHEN 1 THEN
      SET jan = db_total;
    WHEN 2 THEN
      SET feb = db_total;
    WHEN 3 THEN
      SET mar = db_total;
    WHEN 4 THEN
      SET apr = db_total;
    WHEN 5 THEN
      SET may = db_total;
    WHEN 6 THEN
      SET jun = db_total;
    WHEN 7 THEN
      SET jul = db_total;
    WHEN 8 THEN
      SET aug = db_total;
    WHEN 9 THEN
      SET sep = db_total;
    WHEN 10 THEN
      SET toct = db_total;
    WHEN 11 THEN
      SET nov = db_total;
    WHEN 12 THEN
      SET tdec = db_total;
    END CASE;

  END WHILE;
  CLOSE cur1;

  INSERT INTO reportmonthsurveysubmitted VALUES (0, broker, yy, jan, feb, mar, apr, may, jun, jul, aug, sep, toct, nov, tdec);

END
EN

回答 2

Stack Overflow用户

发布于 2012-12-06 21:27:13

你的while循环有问题。如果您将跟踪,那么您将了解到,由于while循环中的"Houlder保险“,插入查询不会被触发。

第一次yy = 0,在设置broker = db_broker和yy = 0之后。然后,IF yy <> db_year THEN求值为TRUE,因此它将检查yy <> 0,因此将求值设置为FALSE。然后设置yy = db_year,因此yy变成2011。第二次,broker <> db_broker是假的,所以它不会进行进一步的检查,然后第三次记录也会进行检查。现在,当"Incepta“的第四条记录出现时,broker <> db_broker的计算结果为TRUE,因为broker具有"Houlder保险”,而db_broker具有"Incepta",因此broker持有“Incepta”且yy = 0。接下来,yy <> db_year计算为TRUE为yy =0,db_year为2012。下一个条件yy <> 0为FALSE,所以这次也不执行insert,所以它作为一个整体完全跳过了“霍尔德保险”记录。

以下是更正后的过程

代码语言:javascript
复制
 CREATE DEFINER = 'root'@'localhost'
PROCEDURE src_survey.PopulateReportMonthSurveySubmitted()
BEGIN DECLARE broker    TEXT;
  DECLARE yy        INT;
  DECLARE jan       INT;
  DECLARE feb       INT;
  DECLARE mar       INT;
  DECLARE apr       INT;
  DECLARE may       INT;
  DECLARE jun       INT;
  DECLARE jul       INT;
  DECLARE aug       INT;
  DECLARE sep       INT;
  DECLARE toct      INT;
  DECLARE nov       INT;
  DECLARE tdec      INT;

  DECLARE db_broker TEXT;
  DECLARE db_year   INT;
  DECLARE db_month  INT;
  DECLARE db_total  INT;

  DECLARE eof       INT;
  DECLARE cur1 CURSOR FOR SELECT b.company_name AS 'Broker'
                               , YEAR(submit_date_time) AS 'Year'
                               , MONTH(submit_date_time) AS 'Month'
                               , COUNT(*) AS 'Total'
                          FROM
                            survey_request a
                          JOIN survey d
                          ON d.src_reference = a.src_reference
                          JOIN contacts_companies b
                          ON b.reference = a.broker_company_reference
                          JOIN src_status c
                          ON c.reference = a.src_status_reference
                          WHERE
                            underwriter_company_reference = 73
                          GROUP BY
                            broker
                          , YEAR
                          , MONTH
                          ORDER BY
                            broker
                          , YEAR
                          , MONTH;
  DECLARE CONTINUE HANDLER FOR NOT FOUND SET eof = 1;

  SET broker = '';
  SET yy = 0;
  SET jan = 0;
  SET feb = 0;
  SET mar = 0;
  SET apr = 0;
  SET may = 0;
  SET jun = 0;
  SET jul = 0;
  SET aug = 0;
  SET sep = 0;
  SET toct = 0;
  SET nov = 0;
  SET tdec = 0;

  TRUNCATE TABLE reportmonthsurveysubmitted;
  OPEN cur1;
  SET eof = 0;

  WHILE eof = 0
  DO
    FETCH cur1 INTO db_broker, db_year, db_month, db_total;
    IF broker <> db_broker THEN
    IF yy <> 0 THEN
        INSERT INTO reportmonthsurveysubmitted VALUES (0, broker, yy, jan, feb, mar, apr, may, jun, jul, aug, sep, toct, nov, tdec);
    END IF;
    SET broker = db_broker;
    SET yy = 0;
    END IF;
    IF yy <> db_year THEN
    IF yy <> 0 THEN
        INSERT INTO reportmonthsurveysubmitted VALUES (0, broker, yy, jan, feb, mar, apr, may, jun, jul, aug, sep, toct, nov, tdec);
    END IF;

      SET yy = db_year;
      SET jan = 0;
      SET feb = 0;
      SET mar = 0;
      SET apr = 0;
      SET may = 0;
      SET jun = 0;
      SET jul = 0;
      SET aug = 0;
      SET sep = 0;
      SET toct = 0;
      SET nov = 0;
      SET tdec = 0;
    END IF;

    CASE db_month
    WHEN 1 THEN
      SET jan = db_total;
    WHEN 2 THEN
      SET feb = db_total;
    WHEN 3 THEN
      SET mar = db_total;
    WHEN 4 THEN
      SET apr = db_total;
    WHEN 5 THEN
      SET may = db_total;
    WHEN 6 THEN
      SET jun = db_total;
    WHEN 7 THEN
      SET jul = db_total;
    WHEN 8 THEN
      SET aug = db_total;
    WHEN 9 THEN
      SET sep = db_total;
    WHEN 10 THEN
      SET toct = db_total;
    WHEN 11 THEN
      SET nov = db_total;
    WHEN 12 THEN
      SET tdec = db_total;
    END CASE;

  END WHILE;
  CLOSE cur1;

  INSERT INTO reportmonthsurveysubmitted VALUES (0, broker, yy, jan, feb, mar, apr, may, jun, jul, aug, sep, toct, nov, tdec);

END 

希望它能帮到你。

票数 1
EN

Stack Overflow用户

发布于 2012-12-06 21:21:11

试试这个:

代码语言:javascript
复制
CREATE DEFINER = 'root'@'localhost'
PROCEDURE src_survey.PopulateReportMonthSurveySubmitted()
BEGIN DECLARE broker    TEXT;
  DECLARE yy        INT;
  DECLARE jan       INT;
  DECLARE feb       INT;
  DECLARE mar       INT;
  DECLARE apr       INT;
  DECLARE may       INT;
  DECLARE jun       INT;
  DECLARE jul       INT;
  DECLARE aug       INT;
  DECLARE sep       INT;
  DECLARE toct      INT;
  DECLARE nov       INT;
  DECLARE tdec      INT;

  DECLARE db_broker TEXT;
  DECLARE db_year   INT;
  DECLARE db_month  INT;
  DECLARE db_total  INT;

  DECLARE eof       INT DEFAULT 0;
  DECLARE cur1 CURSOR FOR SELECT b.company_name AS 'Broker'
                               , YEAR(submit_date_time) AS 'Year'
                               , MONTH(submit_date_time) AS 'Month'
                               , COUNT(*) AS 'Total'
                          FROM
                            survey_request a
                          JOIN survey d
                          ON d.src_reference = a.src_reference
                          JOIN contacts_companies b
                          ON b.reference = a.broker_company_reference
                          JOIN src_status c
                          ON c.reference = a.src_status_reference
                          WHERE
                            underwriter_company_reference = 73
                          GROUP BY
                            broker
                          , YEAR
                          , MONTH
                          ORDER BY
                            broker
                          , YEAR
                          , MONTH;
  DECLARE CONTINUE HANDLER FOR NOT FOUND SET eof = 1;

  SET broker = '';
  SET yy = 0;
  SET jan = 0;
  SET feb = 0;
  SET mar = 0;
  SET apr = 0;
  SET may = 0;
  SET jun = 0;
  SET jul = 0;
  SET aug = 0;
  SET sep = 0;
  SET toct = 0;
  SET nov = 0;
  SET tdec = 0;

  TRUNCATE TABLE reportmonthsurveysubmitted;
  OPEN cur1;
  curLoop: 
    LOOP 
         FETCH cur1 INTO db_broker, db_year, db_month, db_total;
        IF eof = 1 THEN 
            CLOSE cur1;
            LEAVE curLoop;
        END IF;

    IF broker <> db_broker THEN
      SET broker = db_broker;
      SET yy = 0;
    END IF;
    IF yy <> db_year THEN
      IF yy <> 0 THEN
        INSERT INTO reportmonthsurveysubmitted VALUES (0, broker, yy, jan, feb, mar, apr, may, jun, jul, aug, sep, toct, nov, tdec);
      END IF;
      SET yy = db_year;
      SET jan = 0;
      SET feb = 0;
      SET mar = 0;
      SET apr = 0;
      SET may = 0;
      SET jun = 0;
      SET jul = 0;
      SET aug = 0;
      SET sep = 0;
      SET toct = 0;
      SET nov = 0;
      SET tdec = 0;
    END IF;
    CASE db_month
    WHEN 1 THEN
      SET jan = db_total;
    WHEN 2 THEN
      SET feb = db_total;
    WHEN 3 THEN
      SET mar = db_total;
    WHEN 4 THEN
      SET apr = db_total;
    WHEN 5 THEN
      SET may = db_total;
    WHEN 6 THEN
      SET jun = db_total;
    WHEN 7 THEN
      SET jul = db_total;
    WHEN 8 THEN
      SET aug = db_total;
    WHEN 9 THEN
      SET sep = db_total;
    WHEN 10 THEN
      SET toct = db_total;
    WHEN 11 THEN
      SET nov = db_total;
    WHEN 12 THEN
      SET tdec = db_total;
    END CASE;

SET eof = 0;        
    END LOOP;

  INSERT INTO reportmonthsurveysubmitted VALUES (0, broker, yy, jan, feb, mar, apr, may, jun, jul, aug, sep, toct, nov, tdec);

END
票数 0
EN
页面原文内容由Stack Overflow提供。腾讯云小微IT领域专用引擎提供翻译支持
原文链接:

https://stackoverflow.com/questions/13744192

复制
相关文章

相似问题

领券
问题归档专栏文章快讯文章归档关键词归档开发者手册归档开发者手册 Section 归档