Page 126 - Computer Software Application TP - Volume 1

COMPUTER SOFTWARE APPLICATION - CITS

5 Insert sample data –
Insert some sample data into the table:

INSERT INTO employee (emp name, emp salary) VALUES
(‘John Doe’, 50000.00),
(‘Jane Smith’, 60000.00),
(‘Bob Johnson’, 75000.00);

6 Create a stored procedure with a cursor
Now, create a stored procedure that uses a cursor to iterate through the rows and display the employee names:
DELIMITER //
CREATE PROCEDURE DisplayEmployeeNames()
BEGIN
DECLARE done BOOLEAN DEFAULT FALSE;
DECLARE emp name value VARCHAR(255);
-- Declare a cursor for the table
DECLARE cursor employee CURSOR FOR
SELECT emp name FROM employee;
-- Declare an exit handler to close the cursor when no more rows are found
DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE;
-- Open the cursor
OPEN cursor employee;
-- Start looping through the rows
cursor loop: LOOP
-- Fetch the next row into emp name value
FETCH cursor employee INTO emp name value;

-- Check if we have reached the end of the cursor
IF done THEN
LEAVE cursor loop;
END IF;

111
CITS : IT & ITES - Computer Software Application - Exercise 34