March 28, 2006
MySQL 5.0 Stored Procedures
MySQL 5.0 comes with some really cool additions like stored procedures, functions and views.
I was experimenting with writing a procedure at work and, this is what i came up with.
DELIMITER $$;
DROP PROCEDURE IF EXISTS `vms_database`.`FixSafety`$$
CREATE PROCEDURE `FixSafety`()
BEGIN
DECLARE id, b int ;
DECLARE serial_no1 varchar(255) ;
DECLARE track_id varchar(255) ;
DECLARE cur_1 CURSOR FOR SELECT MAX(SAFETY_VALVE_REPAIR_ID) AS id , SERIAL_NO , SAFETY_VALVE_EQUIPMENT_TRACK_ID AS track_id FROM safety_valve_repair WHERE SERIAL_NO IS NOT NULL AND trim(SERIAL_NO)!=” GROUP BY SERIAL_NO;
DECLARE CONTINUE HANDLER FOR NOT FOUND
SET b = 1 ;
OPEN cur_1 ;
REPEAT
FETCH cur_1 INTO id, serial_no1, track_id ;
UPDATE safety_valve_repair SET SAFETY_VALVE_EQUIPMENT_TRACK_ID=id,MOST_RECENT_FLAG=‘N’ WHERE SERIAL_NO = serial_no1 AND SAFETY_VALVE_REPAIR_ID!=id ;
UPDATE safety_valve_repair SET MOST_RECENT_FLAG=‘Y’ WHERE SAFETY_VALVE_REPAIR_ID = id ;
UNTIL b = 1
END REPEAT ;
CLOSE cur_1 ;
END$$
DELIMITER ;$$
DROP PROCEDURE IF EXISTS `vms_database`.`FixSafety`$$
CREATE PROCEDURE `FixSafety`()
BEGIN
DECLARE id, b int ;
DECLARE serial_no1 varchar(255) ;
DECLARE track_id varchar(255) ;
DECLARE cur_1 CURSOR FOR SELECT MAX(SAFETY_VALVE_REPAIR_ID) AS id , SERIAL_NO , SAFETY_VALVE_EQUIPMENT_TRACK_ID AS track_id FROM safety_valve_repair WHERE SERIAL_NO IS NOT NULL AND trim(SERIAL_NO)!=” GROUP BY SERIAL_NO;
DECLARE CONTINUE HANDLER FOR NOT FOUND
SET b = 1 ;
OPEN cur_1 ;
REPEAT
FETCH cur_1 INTO id, serial_no1, track_id ;
UPDATE safety_valve_repair SET SAFETY_VALVE_EQUIPMENT_TRACK_ID=id,MOST_RECENT_FLAG=‘N’ WHERE SERIAL_NO = serial_no1 AND SAFETY_VALVE_REPAIR_ID!=id ;
UPDATE safety_valve_repair SET MOST_RECENT_FLAG=‘Y’ WHERE SAFETY_VALVE_REPAIR_ID = id ;
UNTIL b = 1
END REPEAT ;
CLOSE cur_1 ;
END$$
DELIMITER ;$$
The procedure that i wrote was 2-3 times faster, than a piece of java code that, i wrote to perform the same operation.
Eventhough i still don’t like much of how procedures are written in MySQL 5.0, its still a good start.
