Page 131 - Computer Software Application TP - Volume 1

COMPUTER SOFTWARE APPLICATION - CITS

4 STORED PROCEDURE –
DELIMITER //
CREATE PROCEDURE GetBooksByAuthor(IN authorName VARCHAR(255))
BEGIN
SELECT * FROM books WHERE author = authorName;
END //
DELIMITER ;

TRIGGER –
DELIMITER //
CREATE TRIGGER AfterBookBorrowed
AFTER INSERT ON borrowed books
FOR EACH ROW
BEGIN
UPDATE books
SET quantity = quantity - 1

WHERE book id = NEW.book id;
END //
DELIMITER ;
5 CURSOR –
DELIMITER //
CREATE PROCEDURE DisplayBooksAndQuantities()
BEGIN
DECLARE done INT DEFAULT FALSE;
DECLARE book title VARCHAR(255);

DECLARE book quantity INT;
DECLARE books cursor CURSOR FOR
SELECT title, quantity FROM books;
DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE;
OPEN books cursor;
read loop: LOOP
FETCH books cursor INTO book title, book quantity;

IF done THEN
LEAVE read loop;
END IF;
SELECT CONCAT(book title, ‘: ‘, book quantity) AS BookInfo;

END LOOP;
CLOSE books cursor;
END //
DELIMITER ;

116
CITS : IT & ITES - Computer Software Application - Exercise 36