数据库存储过程
1.无参数的存储过程
USE `markmanger`$$ DROP PROCEDURE IF EXISTS `test_sp2`$$ CREATE DEFINER=`hao`@`%` PROCEDURE `test_sp2`() BEGIN UPDATE ContactBook SET CONTACTNAME='www111' WHERE CONTACTID=5; END$$
2.有参数的(无事务)
2.1 输入参数的存储过程
DELIMITER $$ USE `markmanger`$$ DROP PROCEDURE IF EXISTS `test_sp2`$$ CREATE DEFINER=`hao`@`%` PROCEDURE `test_sp2`(uname VARCHAR(10),phoneum VARCHAR(10)) BEGIN UPDATE ContactBook SET CONTACTNAME=uname WHERE CONTACTID=5; UPDATE ContactBook SET CONTACTPHONE=phoneum WHERE CONTACTID=5; END$$ DELIMITER ;
调用
CALL test_sp2('aa','bb');
1.2输出参数的存储过程
DELIMITER $$ USE `markmanger`$$ DROP PROCEDURE IF EXISTS `test_sp3`$$ CREATE DEFINER=`hao`@`%` PROCEDURE `test_sp3`(OUT s VARCHAR(10)) BEGIN SELECT COUNT(*) FROM ContactBook INTO s; SELECT s; END$$ DELIMITER ;
调用
CALL test_sp3(@S);
3.带事务的存储过程
DELIMITER $$ USE `markmanger`$$ DROP PROCEDURE IF EXISTS `test_sp4`$$ CREATE DEFINER=`hao`@`%` PROCEDURE `test_sp4`() BEGIN DECLARE t_error INTEGER DEFAULT 0; DECLARE CONTINUE HANDLER FOR SQLEXCEPTION SET t_error=1; START TRANSACTION; SELECT COUNT(*) FROM ContactBook; IF t_error = 1 THEN ROLLBACK; ELSE COMMIT; END IF; END$$ DELIMITER ;
调用
CALL test_sp4();