PROGRAM - 6
Write a PL/SQL block of code using parameterized Cursor, that will
merge the data available in the newly created table N_RollCall with
the data available in the table O_RollCall. If the data in the first table
already exist in the second table then that data should be skipped.
1. Create the Tables
mysql> CREATE DATABASE ROLLCALL;
Query OK, 1 row affected (0.01 sec)
mysql> USE ROLLCALL;
Database changed
mysql> CREATE TABLE N_RollCall (
student_id INT PRIMARY KEY,
student_name VARCHAR(255),
birth_date DATE
);
Query OK, 0 rows affected (0.05 sec)
mysql> CREATE TABLE O_RollCall (
student_id INT PRIMARY KEY,
student_name VARCHAR(255),
birth_date DATE
);
Query OK, 0 rows affected (0.04 sec)
2. Add Sample Records to both tables
mysql> INSERT INTO O_RollCall (student_id, student_name, birth_date)
VALUES
(1, 'Shivanna', '1995-08-15'),
(3, 'Cheluva', '1990-12-10');
Query OK, 2 rows affected (0.01 sec)
Records: 2 Duplicates: 0 Warnings: 0
mysql> INSERT INTO N_RollCall (student_id, student_name, birth_date)
VALUES
(1, 'Shivanna', '1995-08-15'),
(2, 'Bhadramma', '1998-03-22'),
(3, 'Cheluva', '1990-12-10'),
(4, 'Devendra', '2000-05-18'),
(5, 'Eshwar', '1997-09-03');
Query OK, 5 rows affected (0.02 sec)
Records: 5 Duplicates: 0 Warnings: 0
3. Define the Stored Procedure
mysql> DELIMITER //
mysql> CREATE PROCEDURE merge_rollcall_data()
BEGIN
DECLARE done INT DEFAULT FALSE;
DECLARE n_id INT;
DECLARE n_name VARCHAR(255);
DECLARE n_birth_date DATE;
DECLARE n_cursor CURSOR FOR
SELECT student_id, student_name, birth_date
FROM N_RollCall;
DECLARE CONTINUE HANDLER FOR NOT FOUND
SET done = TRUE;
OPEN n_cursor;
cursor_loop: LOOP
FETCH n_cursor INTO n_id, n_name, n_birth_date;
IF done THEN
LEAVE cursor_loop;
END IF;
IF NOT EXISTS (
SELECT 1
FROM O_RollCall
WHERE student_id = n_id
) THEN
INSERT INTO O_RollCall (student_id, student_name, birth_date)
VALUES (n_id, n_name, n_birth_date);
END IF;
END LOOP;
CLOSE n_cursor;
END//
Query OK, 0 rows affected (0.02 sec)
mysql> DELIMITER ;
4. Execute the Stored Procedure
mysql> CALL merge_rollcall_data();
Query OK, 0 rows affected (0.02 sec)
5. Verify Records in O_RollCall
mysql> SELECT * FROM O_RollCall;
+------------+--------------+------------+
| student_id | student_name | birth_date |
+------------+--------------+------------+
| 1 | Shivanna | 1995-08-15 |
| 2 | Bhadramma | 1998-03-22 |
| 3 | Cheluva | 1990-12-10 |
| 4 | Devendra | 2000-05-18 |
| 5 | Eshwar | 1997-09-03 |
+------------+--------------+------------+
5 rows in set (0.00 sec)