CODEWITHSUNDEEP
mysqlsql
Surodip
Surodip

Reputation: 487

Looping on a Select query result set element and get the elements in MySQL

I am using MySQL 5.2.42 CE.

I have a table which has a column named "organization_id". It contains some repeated values for different rows, means more than one row can have "org1" value in that field. Now I have to loop through the distinct values of that column, means different organizations.

But I don't know if there is any For-Each loop in mysql.

I have done like this:

Select Distinct organization_id From tbl_organization

this is returning me all the different organizations. Now how can I loop through the list and access each organization_id? I am writing this in a trigger in DB. Please help.

Thanks & Regards, Surodip Patra

Upvotes: 1

Views: 21935

Answers (3)

Surodip
Surodip

Reputation: 487

I have accomplished my task with the help of the answers given by all. My SQL is given below:

CREATE DEFINER=`root`@`%` PROCEDURE `test_report`()
BEGIN

DECLARE _id varchar(20);
DECLARE done int;
    DECLARE cur_report cursor for
                    Select distinct id From my_report_table;
       DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = 1;
       SET @start_date = DATE_FORMAT(CURDATE(), '%Y-%m-01'); # first date of month
       SET @end_date = LAST_DAY(CURDATE()); #last date of month
        open cur_report;
        reading_reports: LOOP
            Fetch cur_report INTO _id; 
                IF done = 1 THEN
                    LEAVE reading_orgs;
                END IF;
                Insert into my_table(id,creation_date,start_date,end_date) Values(_id,CURDATE(),@start_date,@end_date);
        END LOOP;
end

Upvotes: 0

Ravinder Reddy
Ravinder Reddy

Reputation: 24002

You have to use CURSORS. Using a cursor, read the fetched ids into local variable and perform other conditional operations based on that organisation id.

...
DECLARE cur_orgs CURSOR FOR
           SELECT DISTINCT organization_id FROM tbl_organization;  
...

OPEN cur_orgs;
...

Reading_Orgs: LOOP
    FETCH cur_orgs INTO _org_id;
    ... 
    IF done THEN
      LEAVE Reading_Orgs;
    END IF;

    IF ... THEN
      ...
    ELSE
      ...
    END IF;
END LOOP;

CLOSE cur_orgs;

...
...

Upvotes: 3

developerCK
developerCK

Reputation: 4506

If I am understanding right then

you can use CURSOR in your trigger.

ursor is used to iterate through a set of rows, which returned by a query, and process individual row. Currently with all versions greater 5.x, MySQL cursor has following properties:

  • Read only: it means you cannot update the cursor.
  • Non-scrollable: it only can traverse in one direction and cannot skip, move back or forth in result set.
  • Asensitive: you should avoid update table while open a cursor on that table otherwise you may get unexpected results.

MySQL supports following statements for working with cursor.

First you have to declare a cursor using DECLARE statement:

DECLARE cursor_name CURSOR FOR SELECT_statement;

Second you have to open the cursor using OPEN statement. You must open cursor before fetching rows from it.

 OPEN cursor_name;

Next you can retrieve next row from cursor and move the cursor to the following row in a result set by using FETCH statement.

FETCH cursor_name INTO variable list;

And finally, you must close the cursor to deactivate it and release the memory associated with that cursor. To close the cursor you use CLOSE statement:

CLOSE cursor_name;

and get the value of a column in a mysql variable using loop.

You can check more detail and a good tutorial , on how to use cursor. http://www.mysqltutorial.org/mysql-cursor/

Upvotes: 1

Related Questions