Mysql的存储过程中使用游标

A Simple Example of a MySQL Stored Procedure that uses a cursor

 

http://www.kbedell.com/2009/03/02/a-simple-example-of-a-mysql-stored-procedure-that-uses-a-cursor/

 

----

drop table if exists friend_status; 

 

CREATE TABLE IF NOT EXISTS `friend_status` (

    `id`            INTEGER(10) unsigned NOT NULL auto_increment,

    `name`          VARCHAR(255) NOT NULL,

    `status_update` VARCHAR(255) NOT NULL,

    PRIMARY KEY (`id`)

);

 

insert into friend_status

    (name, status_update)

  values

      ('John',  'Woke up. Guiness for Brkfst.')

    , ('Fred',  'is thinking about joining the circus')

    , ('Erin',  "Getting ready for a job interview")

    , ('Amy',   'at work and dreaming of kittens')

    , ('John',  'Watching Scooby Doo reruns. Guiness for Lunch.')

    , ('Amy',   'dreaming of fuzzy slippers and wedding dresses')

    , ('Julie', 'is hating working two jobs')

    , ('John',  'Out of the shower finally. Guiness for Dinner.')

    , ('Erin',  "if I don't get this job, I'll be asking 'Paper or Plastic?'")

    , ('Amy',   'dreaming of Meeting Mr. Right!')

    , ('Erin',  'Nailed the job interview -- calling John to celebrate!')

    , ('Amy',   'John called -- meeting him at the pub!')

    , ('John',  'Heading out to meet friends for some Guiness!')

;

----

DROP PROCEDURE IF EXISTS `usp_cursor_example`;

DELIMITER $$

CREATE DEFINER=`root`@`localhost` PROCEDURE `usp_cursor_example`(

  IN name_in VARCHAR(255)

)

READS SQL DATA

BEGIN

 

  /*

    All 'DECLARE' statements must come first

  */

 

  -- Declare '_val' variables to read in each record from the cursor

  DECLARE name_val VARCHAR(255);

  DECLARE status_update_val VARCHAR(255);

 

  -- Declare variables used just for cursor and loop control

  DECLARE no_more_rows BOOLEAN;

  DECLARE loop_cntr INT DEFAULT 0;

  DECLARE num_rows INT DEFAULT 0;

 

  -- Declare the cursor

  DECLARE friends_cur CURSOR FOR

    SELECT name, status_update

    FROM friend_status

    WHERE name = name_in;

 

  -- Declare 'handlers' for exceptions

  DECLARE CONTINUE HANDLER FOR NOT FOUND

    SET no_more_rows = TRUE;

 

  /*

    Now the programming logic

  */

 

  -- 'open' the cursor and capture the number of rows returned

  -- (the 'select' gets invoked when the cursor is 'opened')

  OPEN friends_cur;

  select FOUND_ROWS() into num_rows;

 

  the_loop: LOOP

 

    FETCH  friends_cur INTO name_val, status_update_val;

 

    -- break out of the loop if

      -- 1) there were no records, or

      -- 2) we've processed them all

    IF no_more_rows THEN

        CLOSE friends_cur;

        LEAVE the_loop;

    END IF;

 

    -- the equivalent of a 'print statement' in a stored procedure

    -- it simply displays output for each loop

    select name_val, status_update_val;

 

    -- count the number of times looped

    SET loop_cntr = loop_cntr + 1;

 

  END LOOP the_loop;

 

  -- 'print' the output so we can see they are the same

  select num_rows, loop_cntr;

 

END

$$

DELIMITER ;