尝试使用Mysql服务器创建存储过程时遇到问题 .
这是我的mysql查询
DELIMITER |
CREATE PROCEDURE `EditProduk`(IN `XML` LONGTEXT) NOT DETERMINISTIC NO SQL SQL SECURITY DEFINER BEGIN
BEGIN
declare v_row_index int unsigned default 0;
declare v_row_count int unsigned;
declare v_xpath_row varchar(255);
declare RESULT int unsigned default 0;
declare USER int unsigned default 0;
declare PRDKID int unsigned default 0;
IF(XML <> '' AND XML <> '0' AND XML IS NOT NULL)
THEN
-- calculate the number of row elements.
set v_row_count := extractValue(XML,'count(/xml/ProdukData)');
-- loop through all the row elements
while v_row_index < v_row_count do
set v_row_index := v_row_index + 1;
set v_xpath_row := concat(
'/xml/ProdukData['
, v_row_index
, ']'
);
SET USER = extractValue(XML,concat(v_xpath_row,'/USER/text()'));
SET PRDKID = extractValue(XML,concat(v_xpath_row,'/id/text()'));
UPDATE Produk SET
ProdukName = extractValue(XML,concat(v_xpath_row,'/name/text()')),
ProdukCode = extractValue(XML,concat(v_xpath_row,'/code/text()')),
ProdukMerek = (SELECT IF(extractValue(XML,concat(v_xpath_row,'/merek/text()')) = "",0, ProdukMerekId) FROM ProdukMerek WHERE ProdukMerekName = extractValue(XML,concat(v_xpath_row,'/merek/text()'))),
ProdukCategory = (SELECT IF(extractValue(XML,concat(v_xpath_row,'/category/text()')) = "",0, ProdukCategoryId) FROM ProdukCategory WHERE ProdukCategoryName = extractValue(XML,concat(v_xpath_row,'/category/text()'))),
ProdukQuality = (SELECT IF(extractValue(XML,concat(v_xpath_row,'/quality/text()')) = "",0, ProdukQualityId) FROM ProdukQuality WHERE ProdukQualityName = extractValue(XML,concat(v_xpath_row,'/quality/text()'))),
ProdukGroup = (SELECT IF(extractValue(XML,concat(v_xpath_row,'/group/text()')) = "",0, ProdukGroupId) FROM ProdukGroup WHERE ProdukGroupName = extractValue(XML,concat(v_xpath_row,'/group/text()'))),
ProdukBuyPrice = extractValue(XML,concat(v_xpath_row,'/buyprice/text()')),
ProdukSellPrice = extractValue(XML,concat(v_xpath_row,'/sellprice/text()')),
ProdukDiscount = extractValue(XML,concat(v_xpath_row,'/discount/text()')),
ProdukStatus = extractValue(XML,concat(v_xpath_row,'/status/text()')),
ProdukImagePath = extractValue(XML,concat(v_xpath_row,'/defaultimage/text()')),
ModifiedDate = NOW(),
ModifiedBy = USER
WHERE ProdukId = PRDKID;
end while;
-- calculate the number of row elements.
set v_row_count := extractValue(XML,'count(/xml/ProdukDetail)');
SET v_row_index = 0;
SET PRDKID = LAST_INSERT_ID();
-- loop through all the row elements
while v_row_index < v_row_count do
set v_row_index := v_row_index + 1;
set v_xpath_row := concat(
'/xml/ProdukDetail['
, v_row_index
, ']'
);
if extractValue(XML,concat(v_xpath_row,'/id/text()')) = '0'
then
insert into ProdukDetail (ProdukId, ProdukDetailWarna,
ProdukDetailUkuran, ProdukDetailImagePage,
ModifiedDate, ModifiedBy,
CreatedDate, CreatedBy) values (
PRDKID
, extractValue(XML,concat(v_xpath_row,'/color/text()'))
, extractValue(XML,concat(v_xpath_row,'/size/text()'))
, extractValue(XML,concat(v_xpath_row,'/photo/text()'))
, NOW(), USER, NOW(), USER
);
else
UPDATE ProdukDetail SET
ProdukDetailWarna =extractValue(XML,concat(v_xpath_row,'/color/text()')),
ProdukDetailUkuran =extractValue(XML,concat(v_xpath_row,'/size/text()')),
ProdukDetailImagePage=extractValue(XML,concat(v_xpath_row,'/photo/text()')),
ModifiedDate=NOW(),
ModifiedBy =USER
WHERE ProdukDetailId = extractValue(XML,concat(v_xpath_row,'/id/text()'));
end if;
end while;
SET RESULT = 1;
else
SET RESULT = 0;
END IF;
SELECT RESULT;
END|
DELIMITER ;
这是我得到的错误:
Galat Pencarian SQL:DELIMITER | CREATE PROCEDURE EditProduk(IN XML LONGTEXT)NOT DETERMINISTIC NO SQL SQL SECURITY DEFINER BEGIN BEGIN声明v_row_index int unsigned default 0;声明v_row_count int unsigned;声明v_xpath_row varchar(255); declare RESULT int unsigned default 0;声明USER int unsigned default 0;声明PRDKID int无符号默认值0; IF(XML <>''AND XML <>'0'和XML不是NULL)那么 - 计算行元素的数量 . set v_row_count:= extractValue(XML,'count(/ xml / ProdukData)'); - 遍历所有行元素,而v_row_index <v_row_count do set v_row_index:= v_row_index 1; set v_xpath_row:= concat('/ xml / ProdukData [',v_row_index,']'); SET USER = extractValue(XML,concat(v_xpath_row,'/ USER / text()')); SET PRDKID = extractValue(XML,concat(v_xpath_row,'/ id / text()')); UPDATE产品SET ProdukName = extractValue(XML [...] MySQL menyatakan:Dokumentasi 1064-您的SQL语法有错误;请查看与您的MySQL服务器版本对应的手册,以便在第87行附近使用正确的语法
有人能帮我吗?
因为我试图探索找到解决方案,但没找到..
谢谢
1 回答
你有2个
BEGIN
在DEFINER之后删除BEGIN,你应该好好去 .