Cursor Inside Cursor

Cursor inside cursor

I don't fully understand what was the problem with the "update current of cursor" but it is solved by using the fetch statement twice for the inner cursor:

FETCH NEXT FROM INNER_CURSOR

WHILE (@@FETCH_STATUS <> -1)
BEGIN

UPDATE CONTACTS
SET INDEX_NO = @COUNTER
WHERE CURRENT OF INNER_CURSOR

SET @COUNTER = @COUNTER + 1

FETCH NEXT FROM INNER_CURSOR
FETCH NEXT FROM INNER_CURSOR
END

How to declare a cursor inside another cursor

Cursors can take parameters hence

cursor c_employees    is
            Select employees_id from employees;
cursor c_leaves(e_id int)
  is Select hours from my_table where employess_id = e_id;
            
begin
    for e in c_employees loop
        begin
           for j in c_leaves(e.employees_id) loop
                begin
                   Insert into table2(......
                end;
           end loop;

But generally, when you start seeing cursors with cursors etc etc...its also time to look at whether the queries can be replaced with a JOIN. For example, it might be the case that the code above could just be

insert into table2
select ..
from employees e, my_table m
where e.employess_id = m.employess_id;

Nested Cursors in MYSQL, cursor not working as expected

Try the below procedure, Since the issue might be incorrect closing of cursor.

CREATE PROCEDURE `Analysis`()
BEGIN
declare v_date datetime;
declare v_sensor varchar(50);
DECLARE datecursHandler,sensorCursHandler BOOLEAN DEFAULT FALSE;

Block1: BEGIN 
declare datecursor CURSOR for 
select  distinct date from dateTable;
DECLARE CONTINUE HANDLER FOR NOT FOUND SET datecursHandler = TRUE;

select  distinct date from dateTable; #what is the result set you are getting?

Open datecursor;
datecurs: loop

FETCH datecursor into v_date;
IF datecursHandler THEN
        LEAVE datecurs;
END IF;

        Block2: BEGIN 

        declare sensorCursor CURSOR for
        select distinct sensor from sensorTable ;
        DECLARE CONTINUE HANDLER FOR NOT FOUND SET sensorCursHandler = TRUE;

        Open sensorCursor;
        senscurs: loop
        FETCH  sensorCursor into v_sensor;

          IF sensorCursHandler THEN
    SET sensorCursHandler  = false;
       LEAVE senscurs;
          END IF;

        Insert into temptable(
        sensorValue,
        DateID,
        TimeID, 
        TotalCount,
        TotalDistinctCount
        )
        SELECT 
            sensor AS sensorValue,
            DATE_FORMAT(firstdate, '%Y%m%d') AS DateID,
            HOUR(firstdate) + 1 AS TimeID,
            COUNT(*) AS totalcount,
            COUNT(DISTINCT sensor) AS sensordistinctcount
        FROM
            (SELECT 
                    sensor AS sensor,
                    first_seen AS DeviceFirstSeen,
                    last_seen AS DeviceLastSeen,
                    DATE_FORMAT(FROM_UNIXTIME(first_seen), '%Y/%m/%d %k:%i:%s.%f') AS firstdate,
                    DATE_FORMAT(FROM_UNIXTIME(last_seen), '%Y/%m/%d %k:%i:%s.%f') AS lastdate,          
            FROM
                sensorTable
            INNER JOIN sensorTable2 ON sensorTable.ID = sensorTable2.ID
            WHERE sensorTable.DeviceFirstSeen BETWEEN date_format(date_sub(date,interval 1 day),'%Y-%m-%d 15:00:00') AND date_format(date,'%Y-%m-%d 14:59:59')) a
        GROUP BY DATE_FORMAT(firstdate, '%Y%m%d') , HOUR(firstdate) + 1;

        end loop senscurs;
        close sensorCursor;
        END Block2;

END loop datecurs;
close datecursor;
END Block1;
END

Are nested cursors in SQL Server possible?

You have an infinite loop because you dont FETCH NEXT from either cursor inside of the 2nd loop, so you are always operating on the first value loaded into the cursor. So FETCH STATUS is always = to 0. FETCH STATUS only returns 1 when you try to fetch a record from the cursor and theres no more data to fetch.

You can nest cursors no problem as long as you eventually break out of your loop.

Cursor for each row

Your issue is that at first you fetch into these two variables:

FETCH NEXT FROM cc into @Broj_ugovora, @Naziv_Zup 

But then within your loop you fetch into different variables:

FETCH NEXT FROM cc into @Ugovor_ID, @Zupanija_ID 

That means that @Broj_ugovora and @Naziv_Zup never change value. Since they never change, the result of this assignment also never changes:

SET @Ugovor_ID   = (SELECT Ugovor_ID FROM Ugovor WHERE "Broj_ugovora" = @Broj_ugovora)
SET @Zupanija_ID = (SELECT Zupanija_ID FROM Zupanija_Sif WHERE Zupanija_naziv = @Naziv_Zup)

Also never changes, so you insert the same values over and over again.

The quick fix is to just change the fetch inside the loop to match the initial fetch.

The proper fix is to rewrite as a set based query. I think the following is the equivalent of what you are doing:

INSERT INTO Ugovor_Zupanija (Ugovor_ID, Zupanija_ID)
SELECT  u.Ugovor_ID,
        sif.Zupanija_ID
FROM    Ug_zup AS zup
        LEFT JOIN Zupanija_Sif AS sif
            ON sif.Zupanija_naziv = zup.Zupanija
        LEFT JOIN Ugovor AS u
            ON u.Broj_ugovora = zup.[Broj ugovora];

MySql. How to combine several columns in cursor?

I resolved my problem. For this decision I create a new table and I insert new row in this table if the student is not yet now in this table, and update this table (set new student and comma) if the student is present in table. And I did this with via cursors.
This is the final right decision. Now everything works.

---

Related Topics