Output data in a cursor

http://www.java2s.com/Code/SQL/Cursor/Outputdatainacursor.htm

 mysql>mysql>mysql> CREATE TABLE Employee(  ->   id      int,  ->   first_name  VARCHAR(15),  ->   last_name   VARCHAR(15),  ->   start_date  DATE,  ->   end_date   DATE,  ->   salary    FLOAT(8,2),  ->   city     VARCHAR(10),  ->   description  VARCHAR(15)  -> );Query OK, 0 rows affected (0.05 sec)mysql>mysql>mysql> insert into Employee(id,first_name, last_name, start_date, end_Date,  salary, City,    Description)  ->       values (1,'Jason',  'Martin', '19960725', '20060725', 1234.56, 'Toronto', 'Programmer');Query OK, 1 row affected (0.02 sec)mysql>mysql> insert into Employee(id,first_name, last_name, start_date, end_Date,  salary, City,    Description)  ->        values(2,'Alison',  'Mathews', '19760321', '19860221', 6661.78, 'Vancouver','Tester');Query OK, 1 row affected (0.00 sec)mysql>mysql> insert into Employee(id,first_name, last_name, start_date, end_Date,  salary, City,    Description)  ->        values(3,'James',  'Smith',  '19781212', '19900315', 6544.78, 'Vancouver','Tester');Query OK, 1 row affected (0.00 sec)mysql>mysql> insert into Employee(id,first_name, last_name, start_date, end_Date,  salary, City,    Description)  ->        values(4,'Celia',  'Rice',   '19821024', '19990421', 2344.78, 'Vancouver','Manager');Query OK, 1 row affected (0.00 sec)mysql>mysql> insert into Employee(id,first_name, last_name, start_date, end_Date,  salary, City,    Description)  ->        values(5,'Robert',  'Black',  '19840115', '19980808', 2334.78, 'Vancouver','Tester');Query OK, 1 row affected (0.00 sec)mysql>mysql> insert into Employee(id,first_name, last_name, start_date, end_Date,  salary, City,    Description)  ->        values(6,'Linda',  'Green',  '19870730', '19960104', 4322.78,'New York', 'Tester');Query OK, 1 row affected (0.00 sec)mysql>mysql> insert into Employee(id,first_name, last_name, start_date, end_Date,  salary, City,    Description)  ->        values(7,'David',  'Larry',  '19901231', '19980212', 7897.78,'New York', 'Manager');Query OK, 1 row affected (0.00 sec)mysql>mysql> insert into Employee(id,first_name, last_name, start_date, end_Date,  salary, City,    Description)  ->        values(8,'James',  'Cat',   '19960917', '20020415', 1232.78,'Vancouver', 'Tester');Query OK, 1 row affected (0.00 sec)mysql>mysql> select * from Employee;+------+------------+-----------+------------+------------+---------+-----------+-------------+| id  | first_name | last_name | start_date | end_date  | salary | city   | description |+------+------------+-----------+------------+------------+---------+-----------+-------------+|  1 | Jason   | Martin  | 1996-07-25 | 2006-07-25 | 1234.56 | Toronto  | Programmer ||  2 | Alison   | Mathews  | 1976-03-21 | 1986-02-21 | 6661.78 | Vancouver | Tester   ||  3 | James   | Smith   | 1978-12-12 | 1990-03-15 | 6544.78 | Vancouver | Tester   ||  4 | Celia   | Rice   | 1982-10-24 | 1999-04-21 | 2344.78 | Vancouver | Manager   ||  5 | Robert   | Black   | 1984-01-15 | 1998-08-08 | 2334.78 | Vancouver | Tester   ||  6 | Linda   | Green   | 1987-07-30 | 1996-01-04 | 4322.78 | New York | Tester   ||  7 | David   | Larry   | 1990-12-31 | 1998-02-12 | 7897.78 | New York | Manager   ||  8 | James   | Cat    | 1996-09-17 | 2002-04-15 | 1232.78 | Vancouver | Tester   |+------+------------+-----------+------------+------------+---------+-----------+-------------+8 rows in set (0.00 sec)mysql>mysql>mysql>mysql> delimiter $$mysql> CREATE PROCEDURE myProc()  ->   READS SQL DATA  -> BEGIN  ->   DECLARE l_last_row INT DEFAULT 0;  ->   DECLARE l_dept_id INT;  ->   DECLARE c_dept CURSOR FOR  ->      SELECT id  ->       FROM employee;  ->   /* handler to set l_last_row=1 if a cursor returns no more rows */  ->   DECLARE continue handler for NOT FOUND SET l_last_row=1;  ->  ->   OPEN c_dept;  ->   dept_cursor: LOOP  ->      FETCH c_dept INTO l_dept_id;  ->      IF (l_last_row=1) THEN  ->        LEAVE dept_cursor;  ->      END IF;  ->      select l_dept_id;  ->  ->   END LOOP dept_cursor;  ->   CLOSE c_dept;  ->  -> END$$Query OK, 0 rows affected (0.01 sec)mysql>mysql> delimiter ;mysql> call myProc();+-----------+| l_dept_id |+-----------+|     1 |+-----------+1 row in set (0.00 sec)+-----------+| l_dept_id |+-----------+|     2 |+-----------+1 row in set (0.16 sec)+-----------+| l_dept_id |+-----------+|     3 |+-----------+1 row in set (0.16 sec)+-----------+| l_dept_id |+-----------+|     4 |+-----------+1 row in set (0.16 sec)+-----------+| l_dept_id |+-----------+|     5 |+-----------+1 row in set (0.17 sec)+-----------+| l_dept_id |+-----------+|     6 |+-----------+1 row in set (0.17 sec)+-----------+| l_dept_id |+-----------+|     7 |+-----------+1 row in set (0.17 sec)+-----------+| l_dept_id |+-----------+|     8 |+-----------+1 row in set (0.17 sec)Query OK, 0 rows affected (0.17 sec)mysql> drop procedure myProc;Query OK, 0 rows affected (0.00 sec)mysql>mysql>mysql>mysql>mysql>mysql> drop table Employee;Query OK, 0 rows affected (0.00 sec)mysql>mysql>     

原标题：Output data in a cursor

关键词：