CURSOR SQL

EX: 5 

Create cursor for Employee table & extract the values from the table. Declare the variables ,Open the cursor & extrct the values from the cursor. Close the cursor. Employee(E_id, E_name, Age, Salary)

1. Start the procedure

• Give the procedure a name.

• Begin the procedure block.

---

2. Declare variables

• Create variables to store column values from the table.

• All variables must be declared at the beginning.

---

3. Declare the cursor

• Define a cursor that selects rows from the table.

• This cursor will be used to read records one by one.

---

4. Declare a handler

• Create a handler to detect the end of the cursor.

• This handler will stop the loop when there are no more rows.

---

5. Open the cursor

• Open the cursor to start fetching rows.

---

6. Loop and fetch data

• Start a loop.

• Fetch one row from the cursor into variables.

• Check if the cursor reached the end.

• If not end, process the row (e.g., display or store values).

---

7. Exit the loop

• When no more rows exist, exit the loop.

---

8. Close the cursor

• Close the cursor to release resources.

---

9. End the procedure

• End the procedure block.

• Procedure is ready to be called.

CODE:

Step: Create DB, Use DB, Create Table.

CREATE TABLE Employee (

    E_id INT PRIMARY KEY,

    E_name VARCHAR(50),

    Age INT,

    Salary DECIMAL(10,2)

);

 Step D1: Insert rows:

 Create Stored Procedure with Cursor

Why stored procedure?

Because cursor logic requires a procedure.

Step E1: Change Delimiter

DELIMITER $$
Step E2: Create Procedure
CREATE PROCEDURE extract_employee()
BEGIN
Step E3: Declare Variables (All Declarations First)
DECLARE v_id INT;
DECLARE v_name VARCHAR(50);
DECLARE v_age INT;
DECLARE v_salary DECIMAL(10,2);
DECLARE done INT DEFAULT 0;
Step E4: Declare Cursor
DECLARE emp_cursor CURSOR FOR
    SELECT E_id, E_name, Age, Salary FROM Employee;
 Step E5: Declare Handler
DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = 1;

 Step E6: Create Temporary Table
CREATE TEMPORARY TABLE IF NOT EXISTS temp_employee (
    E_id INT,
    E_name VARCHAR(50),
    Age INT,
    Salary DECIMAL(10,2)
);
Step E7: Open Cursor
OPEN emp_cursor;

Step E8: Fetch Loop
read_loop: LOOP
    FETCH emp_cursor INTO v_id, v_name, v_age, v_salary;

    IF done = 1 THEN
        LEAVE read_loop;
    END IF;

    INSERT INTO temp_employee VALUES (v_id, v_name, v_age, v_salary);
END LOOP;

Step E9: Close Cursor
CLOSE emp_cursor;

Step E10: Display Data
SELECT * FROM temp_employee;

Step E11: End Procedure
END$$
DELIMITER ;

Run the Procedure
CALL extract_employee();