MySQL Tutorial/Cursor/Declare — различия между версиями

Материал из SQL эксперт
Перейти к: навигация, поиск
м (1 версия)
 
м (1 версия)
 
(нет различий)

Текущая версия на 12:49, 26 мая 2010

Creating Cursors

DECLARE...CURSOR, OPEN, FETCH, and CLOSE statements, as well as the HANDLER declaration are the statements for using cursors.

The statement to create the cursor is shown here:



   <source lang="sql">

DECLARE <cursor name> CURSOR FOR <SELECT statement>;</source>


Declare a cursor

   <source lang="sql">

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.02 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.01 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> mysql> CREATE PROCEDURE myProc (in_customer_id INT) 

   -> BEGIN
   ->
   ->   DECLARE v_id   INT;
   ->   DECLARE v_name VARCHAR(30);
   ->
   ->   DECLARE c1 CURSOR FOR
   ->       SELECT id,first_name
   ->         FROM employee
   ->        WHERE id=in_customer_id;
   ->
   ->
   -> END$$

Query OK, 0 rows affected (0.00 sec) mysql> delimiter ; mysql> mysql> call myProc(1); Query OK, 0 rows affected (0.00 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></source>


Nested cursors

   <source lang="sql">

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, 0 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, 1 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, 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_department_id INT;
   ->   DECLARE l_employee_id   INT;
   ->   DECLARE l_emp_count     INT DEFAULT 0 ;
   ->   DECLARE l_done          INT DEFAULT  0;
   ->
   ->   DECLARE dept_csr cursor  FOR
   ->     SELECT id FROM employee;
   ->
   ->   DECLARE emp_csr cursor  FOR
   ->     SELECT id+1 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=1 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=1 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, 0 rows affected (0.02 sec) mysql> mysql> delimiter ; mysql> mysql> call myProc(); +------------------------------------------------------------------------------------+ | CONCAT("Department ",l_department_id," has ", 

          l_emp_count," employees") |

+------------------------------------------------------------------------------------+ | Department 1 has 8 employees | +------------------------------------------------------------------------------------+ 1 row in set (0.01 sec) +------------------------------------------------------------------------------------+ | CONCAT("Department ",l_department_id," has ", 

          l_emp_count," employees") |

+------------------------------------------------------------------------------------+ | Department 2 has 8 employees | +------------------------------------------------------------------------------------+ 1 row in set (0.01 sec) +------------------------------------------------------------------------------------+ | CONCAT("Department ",l_department_id," has ", 

          l_emp_count," employees") |

+------------------------------------------------------------------------------------+ | Department 3 has 8 employees | +------------------------------------------------------------------------------------+ 1 row in set (0.01 sec) +------------------------------------------------------------------------------------+ | CONCAT("Department ",l_department_id," has ", 

          l_emp_count," employees") |

+------------------------------------------------------------------------------------+ | Department 4 has 8 employees | +------------------------------------------------------------------------------------+ 1 row in set (0.03 sec) +------------------------------------------------------------------------------------+ | CONCAT("Department ",l_department_id," has ", 

          l_emp_count," employees") |

+------------------------------------------------------------------------------------+ | Department 5 has 8 employees | +------------------------------------------------------------------------------------+ 1 row in set (0.03 sec) +------------------------------------------------------------------------------------+ | CONCAT("Department ",l_department_id," has ", 

          l_emp_count," employees") |

+------------------------------------------------------------------------------------+ | Department 6 has 8 employees | +------------------------------------------------------------------------------------+ 1 row in set (0.03 sec) +------------------------------------------------------------------------------------+ | CONCAT("Department ",l_department_id," has ", 

          l_emp_count," employees") |

+------------------------------------------------------------------------------------+ | Department 7 has 8 employees | +------------------------------------------------------------------------------------+ 1 row in set (0.03 sec) +------------------------------------------------------------------------------------+ | CONCAT("Department ",l_department_id," has ", 

          l_emp_count," employees") |

+------------------------------------------------------------------------------------+ | Department 8 has 8 employees | +------------------------------------------------------------------------------------+ 1 row in set (0.03 sec) Query OK, 0 rows affected (0.27 sec) mysql> drop procedure myProc; Query OK, 0 rows affected (0.00 sec) mysql> mysql> mysql> mysql> mysql> drop table Employee; Query OK, 0 rows affected (0.00 sec) mysql> mysql></source>

Источник — «http://sqle.ru/index.php?title=MySQL_Tutorial/Cursor/Declare&oldid=28»