Nested cursors : Declare « Cursor « MySQL Tutorial

Home
MySQL Tutorial
1.Introduction
2.Select Query
3.Database
4.Table
5.Table Join
6.Subquery
7.Insert Update Delete
8.Logic Operator
9.View
10.Data Types
11.Procedure Function
12.Cursor
13.Trigger
14.Date Time Functions
15.Comparison Functions Operators
16.Aggregate Functions
17.Cast Functions Operators
18.Control Flow Functions
19.Encryption Compression Functions
20.Information Functions
21.Math Numeric Functions
22.Miscellaneous Functions
23.String Functions
24.Regular Expressions
25.Data Dictionary
26.MySQL Utilities
27.Privilege
MySQL Tutorial » Cursor » Declare 
12.1.3.Nested cursors
mysql>
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, rows affected (0.03 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, row affected (0.00 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, 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, 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, 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, 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, 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, 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, row affected (0.00 sec)

mysql>
mysql> select from Employee;
+------+------------+-----------+------------+------------+---------+-----------+-------------+
| id   | first_name | last_name | start_date | end_date   | salary  | city      | description |
+------+------------+-----------+------------+------------+---------+-----------+-------------+
|    | Jason      | Martin    | 1996-07-25 2006-07-25 1234.56 | Toronto   | Programmer  |
|    | Alison     | Mathews   | 1976-03-21 1986-02-21 6661.78 | Vancouver | Tester      |
|    | James      | Smith     | 1978-12-12 1990-03-15 6544.78 | Vancouver | Tester      |
|    | Celia      | Rice      | 1982-10-24 1999-04-21 2344.78 | Vancouver | Manager     |
|    | Robert     | Black     | 1984-01-15 1998-08-08 2334.78 | Vancouver | Tester      |
|    | Linda      | Green     | 1987-07-30 1996-01-04 4322.78 | New York  | Tester      |
|    | David      | Larry     | 1990-12-31 1998-02-12 7897.78 | New York  | Manager     |
|    | James      | Cat       | 1996-09-17 2002-04-15 1232.78 | Vancouver | Tester      |
+------+------------+-----------+------------+------------+---------+-----------+-------------+
rows in set (0.00 sec)

mysql>
mysql>
mysql>
mysql> delimiter $$
mysql> CREATE PROCEDURE myProc()
    ->    READS SQL DATA
    -> BEGIN
    ->
    ->   DECLARE l_department_id INT;
    ->   DECLARE l_employee_id   INT;
    ->   DECLARE l_emp_count     INT DEFAULT ;
    ->   DECLARE l_done          INT DEFAULT  0;
    ->
    ->   DECLARE dept_csr cursor  FOR
    ->     SELECT id FROM employee;
    ->
    ->   DECLARE emp_csr cursor  FOR
    ->     SELECT id+FROM employee;
    ->
    ->   DECLARE CONTINUE HANDLER FOR NOT FOUND SET l_done=1;
    ->
    ->   OPEN dept_csr;
    ->   dept_loop: LOOP   -- Loop through departments
    ->     FETCH dept_csr into l_department_id;
    ->
    ->     IF l_done=THEN
    ->        LEAVE dept_loop;
    ->     END IF;
    ->
    ->     OPEN emp_csr;
    ->     SET l_emp_count=0;
    ->     emp_loop: LOOP      -- Loop through employee in dept.
    ->       FETCH emp_csr INTO l_employee_id;
    ->
    ->       IF l_done=THEN
    ->          LEAVE emp_loop;
    ->       END IF;
    ->       SET l_emp_count=l_emp_count+1;
    ->     END LOOP;
    ->     CLOSE emp_csr;
    ->     SET l_done=0;
    ->
    ->     SELECT CONCAT('Department ',l_department_id,' has ',
    ->            l_emp_count,' employees');
    ->
    ->   END LOOP dept_loop;
    ->   CLOSE dept_csr;
    ->
    -> END$$
Query OK, rows affected (0.02 sec)

mysql>
mysql> delimiter ;
mysql>
mysql> call myProc();
+------------------------------------------------------------------------------------+
| CONCAT('Department ',l_department_id,' has ',
           l_emp_count,' employees') |
+------------------------------------------------------------------------------------+
| Department has employees                                                       |
+------------------------------------------------------------------------------------+
row in set (0.01 sec)

+------------------------------------------------------------------------------------+
| CONCAT('Department ',l_department_id,' has ',
           l_emp_count,' employees') |
+------------------------------------------------------------------------------------+
| Department has employees                                                       |
+------------------------------------------------------------------------------------+
row in set (0.01 sec)

+------------------------------------------------------------------------------------+
| CONCAT('Department ',l_department_id,' has ',
           l_emp_count,' employees') |
+------------------------------------------------------------------------------------+
| Department has employees                                                       |
+------------------------------------------------------------------------------------+
row in set (0.01 sec)

+------------------------------------------------------------------------------------+
| CONCAT('Department ',l_department_id,' has ',
           l_emp_count,' employees') |
+------------------------------------------------------------------------------------+
| Department has employees                                                       |
+------------------------------------------------------------------------------------+
row in set (0.03 sec)

+------------------------------------------------------------------------------------+
| CONCAT('Department ',l_department_id,' has ',
           l_emp_count,' employees') |
+------------------------------------------------------------------------------------+
| Department has employees                                                       |
+------------------------------------------------------------------------------------+
row in set (0.03 sec)

+------------------------------------------------------------------------------------+
| CONCAT('Department ',l_department_id,' has ',
           l_emp_count,' employees') |
+------------------------------------------------------------------------------------+
| Department has employees                                                       |
+------------------------------------------------------------------------------------+
row in set (0.03 sec)

+------------------------------------------------------------------------------------+
| CONCAT('Department ',l_department_id,' has ',
           l_emp_count,' employees') |
+------------------------------------------------------------------------------------+
| Department has employees                                                       |
+------------------------------------------------------------------------------------+
row in set (0.03 sec)

+------------------------------------------------------------------------------------+
| CONCAT('Department ',l_department_id,' has ',
           l_emp_count,' employees') |
+------------------------------------------------------------------------------------+
| Department has employees                                                       |
+------------------------------------------------------------------------------------+
row in set (0.03 sec)

Query OK, rows affected (0.27 sec)

mysql> drop procedure myProc;
Query OK, rows affected (0.00 sec)

mysql>
mysql>
mysql>
mysql>
mysql> drop table Employee;
Query OK, rows affected (0.00 sec)

mysql>
mysql>
12.1.Declare
12.1.1.Creating Cursors
12.1.2.Declare a cursor
12.1.3.Nested cursors
java2s.com  | Contact Us | Privacy Policy
Copyright 2009 - 12 Demo Source and Support. All rights reserved.
All other trademarks are property of their respective owners.