DBMSL 7
CursorAkash
mysql
3 years ago
2.3 kB
7
Indexable
CREATE TABLE O_RollCall (RollNo INT, Name varchar(30), Address varchar(50)); INSERT INTO O_RollCall VALUES (1, 'Amit', 'Pune'); INSERT INTO O_RollCall VALUES (2, 'Sahil', 'Nanded'); INSERT INTO O_RollCall VALUES (3, 'Tilak', 'Mumbai'); SELECT * FROM O_RollCall; -- +--------+-------+---------+ -- | RollNo | Name | Address | -- +--------+-------+---------+ -- | 1 | Amit | Pune | -- | 2 | Sahil | Nanded | -- | 3 | Tilak | Mumbai | -- +--------+-------+---------+ CREATE TABLE N_RollCall (RollNo INT, Name varchar(30), Address varchar(50)); DELIMITER $ CREATE PROCEDURE InsertIntoNew() BEGIN DECLARE RNo INT; DECLARE ExitLoop BOOLEAN; DECLARE C1 CURSOR FOR SELECT RollNo FROM O_RollCall; DECLARE CONTINUE HANDLER FOR NOT FOUND SET ExitLooP= TRUE; OPEN C1; C1_Loop: LOOP FETCH C1 INTO RNo; IF NOT EXISTS(SELECT * FROM N_RollCall WHERE RollNo=RNo) THEN INSERT INTO N_RollCall SELECT * FROM O_RollCall WHERE RollNo=RNo; END IF; IF ExitLoop THEN CLOSE C1; LEAVE C1_Loop; END IF; END LOOP C1_Loop; END $ DELIMITER ; CALL InsertIntoNew(); SELECT * FROM N_RollCall; -- +--------+-------+---------+ -- | RollNo | Name | Address | -- +--------+-------+---------+ -- | 1 | Amit | Pune | -- | 2 | Sahil | Nanded | -- | 3 | Tilak | Mumbai | -- +--------+-------+---------+ INSERT INTO O_RollCall VALUES (4, 'Ram', 'Latur'); SELECT * FROM O_RollCall; -- +--------+-------+---------+ -- | RollNo | Name | Address | -- +--------+-------+---------+ -- | 1 | Amit | Pune | -- | 2 | Sahil | Nanded | -- | 3 | Tilak | Mumbai | -- | 4 | Ram | Latur | -- +--------+-------+---------+ SELECT * FROM N_RollCall; -- +--------+-------+---------+ -- | RollNo | Name | Address | -- +--------+-------+---------+ -- | 1 | Amit | Pune | -- | 2 | Sahil | Nanded | -- | 3 | Tilak | Mumbai | -- +--------+-------+---------+ CALL InsertIntoNew(); SELECT * FROM N_RollCall; -- +--------+-------+---------+ -- | RollNo | Name | Address | -- +--------+-------+---------+ -- | 1 | Amit | Pune | -- | 2 | Sahil | Nanded | -- | 3 | Tilak | Mumbai | -- | 4 | Ram | Latur | -- +--------+-------+---------+
Editor is loading...