Page 123 - Computer Software Application TP - Volume 1

COMPUTER SOFTWARE APPLICATION - CITS

4 INSERT SAMPLE DATA –
Insert some sample data into the table:
INSERT INTO example table (data) VALUES (‘Data 1’), (‘Data 2’), (‘Data 3’);

5 Create A Stored Procedure With A Cursor –
Now, create a stored procedure that uses a cursor to iterate through the rows and display the data:
DELIMITER //
CREATE PROCEDURE IterateExampleTable()
BEGIN

DECLARE done BOOLEAN DEFAULT FALSE;
DECLARE data value VARCHAR(255);
-- Declare a cursor for the table
DECLARE cursor example CURSOR FOR

SELECT data FROM example table;
-- 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 example;
-- Start looping through the rows

cursor loop: LOOP
-- Fetch the next row into data value
FETCH cursor example INTO data value;
-- Check if we have reached the end of the cursor

IF done THEN
LEAVE cursor loop;
END IF;
-- Output the current row data
SELECT data value AS OutputData;

END LOOP;
-- Close the cursor
CLOSE cursor example;
END //

DELIMITER ;

108
CITS : IT & ITES - Computer Software Application - Exercise 33