MySQL给了我"正确的语法,可以在''附近使用作为响应我的SP的错误。
附加的SQL抛出了上述错误,不知道为什么,SP需要放入左窗格中,但没有它就无法保存。
http://www.sqlfiddle.com/#!2/075db/3
知道为什么要这样做吗?
试试这个:
CREATE PROCEDURE Upsert_Page(
pageID smallint,
pageTitle varchar(50),
pageName varchar(50),
pageViewable tinyint,
pageDeleted tinyint,
pageMetaData varchar(5000),
pageParent smallint,
pageRequired tinyint,
pageAdmin tinyint
)
BEGIN
IF (SELECT 1 = 1 FROM Page WHERE Page.ID = pageID) THEN
UPDATE `Page`
SET `Page`.`Title` = pageTitle,
`Page`.`Name` = pageName,
`Page`.`IsViewable` = pageViewable,
`Page`.`IsDeleted` = pageDeleted,
`Page`.`MetaData` = pageMetaData,
`Page`.`ParentID` = pageParent,
`Page`.`IsRequired` = pageRequired,
`Page`.`IsAdmin` = pageAdmin
WHERE `Page`.`ID` = pageID;
ELSE
begin
DECLARE last_id int;
INSERT INTO `Page`(`Title`, `Name`, `IsViewable`, `IsDeleted`,
`MetaData`, `ParentID`, `IsRequired`, `IsAdmin`)
VALUES (pageTitle, pageName, pageViewable, pageDeleted,
pageMetaData, pageParent, pageRequired, pageAdmin);
SET last_id = LAST_INSERT_ID();
INSERT INTO `PageWidget`(`ID`,`WidgetID`,`PageID`)
SELECT
null, `Widget`.`ID`, last_id
FROM `Widget`
JOIN `WidgetType` ON `Widget`.`WidgetTypeID` = `WidgetType`.`ID`
WHERE `WidgetType`.`IsUnique` = 1;
end;
END IF;
END;
/
现在它编译得很好,看这个演示: http://www.sqlfiddle.com/#!2/a3d58
(您需要滚动到左侧面板的底部)
有一些小错误 - 缺少分号,缺少反引号字符等。
一句话:IF ... THEN ... END
不需要BEGIN ... END
块,只需:
IF condition THEN
statements
.....
.....
ELSE
statements
....
....
END IF;