SELECT statement from table inside cursor loop force cursor to EXIT

Question

New to MySQL Stored Procedures. If I uncomment any of the 4 SELECT lines (Below) then the routine EXITS out of the FETCH loop. Do not understand why

-- --------------------------------------------------------------------------------
-- Routine DDL
-- Note: comments before and after the routine body will not be stored by the server
-- --------------------------------------------------------------------------------
DELIMITER $$

CREATE DEFINER=`root`@`localhost` PROCEDURE `UpdateStatusAudit`()
BEGIN
   -- Create loop for all $Service records
  DECLARE svc_id INT;
  DECLARE test INT;
  DECLARE svc_name VARCHAR(100);
  DECLARE no_more_rows BOOLEAN;
  DECLARE up_duration DECIMAL(11,2);
  DECLARE down_duration DECIMAL(11,2);
  DECLARE maint_duration DECIMAL(11,2);
  DECLARE degr_duration DECIMAL(11,2);
  DECLARE services_cur CURSOR FOR SELECT service_id,service_name FROM services ORDER BY service_id;

  -- Declare 'handlers' for exceptions
  DECLARE CONTINUE HANDLER FOR NOT FOUND
  SET no_more_rows = TRUE;

  OPEN services_cur;
  the_loop: LOOP
    FETCH services_cur INTO svc_id,svc_name;
    IF no_more_rows THEN
        CLOSE services_cur;
        LEAVE the_loop;
    END IF;
    SET up_duration = 0;
    SET down_duration = 0;
    SET maint_duration = 0;
    SET degr_duration = 0;
    SELECT svc_id;
    BEGIN
      -- SELECT IFNULL(sum(duration),0) INTO up_duration FROM daily_audit_summary where service_id = svc_id AND status = 'UP' AND Date = current_date - 1 group by date,service_id,status;
      -- SELECT IFNULL(sum(duration),0) INTO down_duration FROM daily_audit_summary where service_id = svc_id AND status = 'DOWN' AND Date = current_date - 1 group by date,service_id,status;
      -- SELECT IFNULL(sum(duration),0) INTO maint_duration FROM daily_audit_summary where service_id = svc_id AND status = 'MAINT' AND Date = current_date - 1 group by date,service_id,status;
      -- SELECT IFNULL(sum(duration),0) INTO degr_duration FROM daily_audit_summary where service_id = svc_id AND status = 'DEGR' AND Date = current_date - 1 group by date,service_id,status;
    END;   
    -- insert into daily_status 
    INSERT INTO daily_status (date,service_id,time_up,time_down,time_maint,time_degraded) values (current_date-1,svc_id,up_duration,down_duration,maint_duration,degr_duration);

  END LOOP the_loop;

END

You've declared the proc delimiter (DELIMITER $$) but there's not one at the end of the definition. I hope you've only missed it when pasting the code here and it is present in your actual code. — Andriy M
– Andriy M, Commented Feb 27, 2012 at 8:17

Andriy M · Accepted Answer · 2012-02-27 08:15:43Z

Did you try assigning the variables like this:

SELECT
  up_duration := IFNULL(SUM(duration), 0)
FROM daily_audit_summary
WHERE service_id = svc_id
  AND status = 'UP'
  AND Date = current_date - 1
GROUP BY
  date,
  service_id,
  status;

?

You could also combine all the assignments into a single SELECT:

SELECT
  up_duration    := SUM(CASE status WHEN 'UP'    THEN duration ELSE 0 END)
  down_duration  := SUM(CASE status WHEN 'DOWN'  THEN duration ELSE 0 END)
  maint_duration := SUM(CASE status WHEN 'MAINT' THEN duration ELSE 0 END)
  degr_duration  := SUM(CASE status WHEN 'DEGR'  THEN duration ELSE 0 END)
FROM daily_audit_summary
WHERE service_id = svc_id
  AND status = 'UP'
  AND Date = current_date - 1
GROUP BY
  date,
  service_id,
  status;

But maybe you could avoid the cursor (and thus the loop) by using a single statement to do all the job:

INSERT INTO daily_status (
  date,
  service_id,
  time_up,
  time_down,
  time_maint,
  time_degraded
)
SELECT
  d.Date,
  s.service_id,
  SUM(CASE das.status WHEN 'UP'    THEN das.duration ELSE 0 END),
  SUM(CASE das.status WHEN 'DOWN'  THEN das.duration ELSE 0 END),
  SUM(CASE das.status WHEN 'MAINT' THEN das.duration ELSE 0 END),
  SUM(CASE das.status WHEN 'DEGR'  THEN das.duration ELSE 0 END)
FROM services s
  CROSS JOIN (SELECT CURRENT_DATE - 1 AS Date) AS d
  LEFT JOIN daily_audit_summary AS das
    ON s.service_id = das.service_id
   AND das.Date = d.Date;

Brookfield · Accepted Answer · 2012-02-27 17:56:43Z

I guess that I needed to give a better explanation... The Code is a Work In Progress and due to requirements, I cannot get rid of the "Services_cur" Cursor.

What I am finding is that when the query for the "Services_Cur" returns say 10 records and within the "the Loop" If I use a SELECT INTO statement from a TABLE such as "SELECT F1 INTO MyVar from Atable where Afld = somevalue" the LOOP exits as if the "Services Cur" cursor was out of data??? If I issue a "SELECT 1234 INTO MyVar" the Loop works and I get 10 results (As Expected).

I am new to Stored Procedures for MySql and could not find an example of someone doing a series of "SELECT value for table" while within a Loop of FETCHES.

I hope this helps explain the issue better

Thanks for any help.

Collectives™ on Stack Overflow

SELECT statement from table inside cursor loop force cursor to EXIT

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related