Various stored routines from chapter 7
CREATE TRIGGER fake_statement_trigger | |
BEFORE INSERT ON sometable | |
FOR EACH ROW | |
BEGIN | |
DECLARE v_row_count INT DEFAULT ROW_COUNT(); | |
IF v_row_count <> 1 THEN | |
-- Your code here | |
END IF; | |
END; | |
CREATE EVENT optimize_somedb ON SCHEDULE EVERY 1 WEEK | |
DO | |
CALL optimize_tables('somedb'); | |
CREATE EVENT optimize_somedb ON SCHEDULE EVERY 1 WEEK | |
DO | |
CALL optimize_tables('somedb'); | |
CREATE TRIGGER fake_statement_trigger | |
BEFORE INSERT ON sometable | |
FOR EACH ROW | |
BEGIN | |
DECLARE v_row_count INT DEFAULT ROW_COUNT(); | |
/*!99999 ROW_COUNT() is 1 except for the first row, so this executes | |
only once per statement. */ | |
IF v_row_count <> 1 THEN | |
-- Your code here | |
END IF; | |
END; | |
CREATE PROCEDURE bad_cursor() | |
BEGIN | |
DECLARE film_id INT; | |
DECLARE f CURSOR FOR SELECT film_id FROM sakila.film; | |
OPEN f; | |
FETCH f INTO film_id; | |
CLOSE f; | |
END | |
DROP PROCEDURE IF EXISTS optimize_tables; | |
DELIMITER // | |
CREATE PROCEDURE optimize_tables(db_name VARCHAR(64)) | |
BEGIN | |
DECLARE t VARCHAR(64); | |
DECLARE done INT DEFAULT 0; | |
DECLARE c CURSOR FOR | |
SELECT table_name FROM INFORMATION_SCHEMA.TABLES | |
WHERE TABLE_SCHEMA = db_name AND TABLE_TYPE = 'BASE TABLE'; | |
DECLARE CONTINUE HANDLER FOR SQLSTATE '02000' SET done = 1; | |
OPEN c; | |
tables_loop: LOOP | |
FETCH c INTO t; | |
IF done THEN | |
LEAVE tables_loop; | |
END IF; | |
SET @stmt_text := CONCAT("OPTIMIZE TABLE ", db_name, ".", t); | |
PREPARE stmt FROM @stmt_text; | |
EXECUTE stmt; | |
DEALLOCATE PREPARE stmt; | |
END LOOP; | |
CLOSE c; | |
END// | |
DELIMITER ; | |
REPEAT | |
FETCH c INTO t; | |
IF NOT done THEN | |
SET @stmt_text := CONCAT("OPTIMIZE TABLE ", db_name, ".", t); | |
PREPARE stmt FROM @stmt_text; | |
EXECUTE stmt; | |
DEALLOCATE PREPARE stmt; | |
END IF; | |
UNTIL done END REPEAT; |
Sign up for free
to join this conversation on GitHub.
Already have an account?
Sign in to comment