我看到以前有人问过这个问题,但没有提供答案。
我使用游标来解析SELECT语句返回的数据。但是,与在命令行中运行SELECT语句相比,它不会返回我期望的数据。
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”相关的记录!有没有解决这个问题的办法?
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发布于 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,所以它作为一个整体完全跳过了“霍尔德保险”记录。
以下是更正后的过程
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 希望它能帮到你。
发布于 2012-12-06 21:21:11
试试这个:
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);
ENDhttps://stackoverflow.com/questions/13744192
复制相似问题