PROGRAM - 5
Create cursor for Employee table & extract the values from the table. Declare
the variables, Open the cursor & extract the values from the cursor. Close the
cursor.
CUSTOMERS(ID,NAME,AGE,ADDRESS,SALARY)
1. Creating the Employee Table and insert few records
mysql> CREATE TABLE Employee
(
E_id INT,
E_name VARCHAR(255),
Age INT,
Salary DECIMAL(10, 2)
);
Query OK, 0 rows affected (0.04 sec)
mysql> INSERT INTO Employee (E_id, E_name, Age, Salary)
VALUES (1, 'Samarth', 30, 50000.00),
(2, 'Ramesh Kumar', 25, 45000.00),
(3, 'Seema Banu', 35, 62000.00),
(4, 'Dennis Anil', 28, 52000.00),
(5, 'Rehman Khan', 32, 58000.00);
Query OK, 5 rows affected (0.01 sec)
Records: 5 Duplicates: 0 Warnings: 0
mysql> DELIMITER //
mysql> CREATE PROCEDURE fetch_employee_data()
BEGIN
DECLARE emp_id INT;
DECLARE emp_name VARCHAR(255);
DECLARE emp_age INT;
DECLARE emp_salary DECIMAL(10,2);
DECLARE emp_cursor CURSOR FOR
SELECT E_id, E_name, Age, Salary
FROM Employee;
DECLARE CONTINUE HANDLER FOR NOT FOUND
SET @finished = 1;
OPEN emp_cursor;
SET @finished = 0;
cursor_loop: LOOP
FETCH emp_cursor INTO emp_id, emp_name, emp_age, emp_salary;
IF @finished = 1 THEN
LEAVE cursor_loop;
END IF;
SELECT CONCAT('Employee ID: ', emp_id, ', Name: ', emp_name, ', Age: ', emp_age, ', Salary: ', emp_salary) AS Employee_Info;
END LOOP;
CLOSE emp_cursor;
END//
Query OK, 0 rows affected (0.01 sec)
mysql> DELIMITER ;
mysql> CALL fetch_employee_data();
+----------------------------------------------------------+
| Employee_Info |
+----------------------------------------------------------+
| Employee ID: 1, Name: Samarth, Age: 30, Salary: 50000.00 |
+----------------------------------------------------------+
1 row in set (0.00 sec)
+---------------------------------------------------------------+
| Employee_Info |
+---------------------------------------------------------------+
| Employee ID: 2, Name: Ramesh Kumar, Age: 25, Salary: 45000.00 |
+---------------------------------------------------------------+
1 row in set (0.01 sec)
+-------------------------------------------------------------+
| Employee_Info |
+-------------------------------------------------------------+
| Employee ID: 3, Name: Seema Banu, Age: 35, Salary: 62000.00 |
+-------------------------------------------------------------+
1 row in set (0.01 sec)
+--------------------------------------------------------------+
| Employee_Info |
+--------------------------------------------------------------+
| Employee ID: 4, Name: Dennis Anil, Age: 28, Salary: 52000.00 |
+--------------------------------------------------------------+
1 row in set (0.02 sec)
+--------------------------------------------------------------+
| Employee_Info |
+--------------------------------------------------------------+
| Employee ID: 5, Name: Rehman Khan, Age: 32, Salary: 58000.00 |
+--------------------------------------------------------------+
1 row in set (0.02 sec)
Query OK, 0 rows affected (0.03 sec)