所以我试图在mySQL中学习更多,我得到了此练习以从事程序&交易。它的读数如下:
"创建一个名为Schedulematch的MySQL过程,该过程在比赛中进行了匹配。
该过程应使用以下参数:
- 比赛名称
- 主队的名字
- 外客团队的名字
- 比赛日期
该过程通过首先调度(创建)在家庭与客队之间的给定日期进行匹配。为了做到这一点,该过程必须确保两个团队在给定日期免费。"
然后我们要使用此语法来调用它。
CALL scheduleMatch(‘Tournament1’, ‘TeamA’, ’TeamB’, ‘2015-11-08’);
delimiter $$
create procedure schedulematch(IN tournamentName_param varchar(40), IN HomeTeamId_param varchar(40),
IN AwayTeamId_param varchar(40),IN matchDate_param date)
BEGIN
DECLARE success_entry BOOL DEFAULT TRUE;
SET success_entry = TRUE;
start Transaction;
IF NOT EXISTS (select * from match where MatchDate = MatchDate_Param AND HomeTeamId = HomeTeamId_Param AND AwayTeamId = AwayTeamId_Param) THEN
insert into match values
(NULL, matchDate_Param, fetch_teamID(HomeTeamId_param),fetch_teamID(AwayTeamId_param), 0, 0, 0 , 0);
SELECT 'match Added ' AS MESSAGE;
commit;
else
rollback;
end if;
end$$
delimiter ;
CALL scheduleMatch(‘Tournament1’, ‘TeamA’, ’TeamB’, ‘2015-11-08’);
我以为我上面有什么能有效的,但是它仍然运行插入语句。任何帮助,将不胜感激!
我正在寻找一些教育,而不仅仅是答案!
您可以使用INSERT INTO .. SELECT FROM
构造和使用WHERE NOT EXISTS
更改IF NOT EXISTS
零件,例如
insert into `match`
SELECT NULL, matchDate_Param, fetch_teamID(HomeTeamId_param),
fetch_teamID(AwayTeamId_param), 0, 0, 0 , 0,
'match Added ' AS MESSAGE
FROM DUAL
WHERE NOT EXISTS (select 1 from `match`
where MatchDate = MatchDate_Param
AND HomeTeamId = HomeTeamId_Param
AND AwayTeamId = AwayTeamId_Param);