参见MySQL数据库-存储过程详解
案例:
DROP PROCEDURE IF EXISTS `addDep`;
DELIMITER ;;
CREATE DEFINER=`root`@`localhost` PROCEDURE `addDep`(in depName varchar(32),in parentId int,in enabled boolean,out result int,out result2 int)
begin
declare did int;
declare pDepPath varchar(64);
insert into department set name=depName,parentId=parentId,enabled=enabled;
# 受影响的行数
select row_count() into result;
# 刚插入数据的id
select last_insert_id() into did;
set result2=did;
select depPath into pDepPath from department where id=parentId;
update department set depPath=concat(pDepPath,'.',did) where id=did;
update department set isParent=true where id=parentId;
end
;;
DELIMITER ;
关于 DEFINER=root@localhost
参见 MySQL 授权用户 ; 存储过程的DEFINER; 命令分隔符DELIMITER
在写存储过程中,可能会涉及到外键约束问题,可以开始关闭外键约束,结束再开启外键约束,案例:
#禁用外键约束
SET FOREIGN_KEY_CHECKS=0;
。。。。。。。
#启动外键约束
SET FOREIGN_KEY_CHECKS=1;