php+MySQL(存储过程) +yii2完整的增删改查
1在MySQL中创建存储过程
a 我将添加和修改 作为 一起
CREATE DEFINER=`root`@`%` PROCEDURE `p_boss_role_save`(IN `_id` CHAR(36), IN `in_league_id` char(36), IN `in_name` varchar(255), IN `in_description` varchar(255)) BEGIN IF EXISTS (SELECT 1 FROM t_boss_role WHERE id = _id) THEN UPDATE t_boss_role SET league_id=in_league_id,name=in_name,description=in_description WHERE id = _id; ELSE SET _id=UUID(); INSERT INTO t_boss_role(id, league_id, name, description,is_enabled,is_deleted) VALUES(_id, in_league_id, in_name, in_description,1,0); END if; SELECT _id; -- 返回最后查询的id END
2 php添加和修改的调用
$cmd = \Yii::$app->db->createCommand('CALL p_boss_role_save(:id,:leagueId,:name,:description)'); $cmd->bindValue(':id',$id); $cmd->bindValue(':leagueId',$leagueId); $cmd->bindValue(':name',$name); $cmd->bindValue(':description',$description); return $cmd->queryScalar();
3.1 创建 删除 的存储过程 逻辑删除
CREATE DEFINER=`root`@`%` PROCEDURE `p_boss_role_delete`(IN `_id` CHAR(36)) BEGIN UPDATE t_boss_role SET is_deleted = 1 WHERE id = _id; END
4.1 php调用
$cmd = \Yii::$app->db->createCommand('CALL p_boss_role_save(:id)'); $cmd->excute();
男儿立志出乡关 学不成名誓不还