MySQL Tutorial/Cursor/Declare — различия между версиями
Admin (обсуждение | вклад) м (1 версия) |
|
(нет различий)
| |
Текущая версия на 09: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:
DECLARE <cursor name> CURSOR FOR <SELECT statement>;
Declare a cursor
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>
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, 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>