请问mysql的自定义函数怎么建啊。可不可以不要返回值,可不可以向存储过程那样就执行一段语句,不要返回值。下面这段存储过程怎么样转成自定义函数。
create procedure tran()
BEGIN
DECLARE ID VARCHAR(40);
DECLARE ID1 VARCHAR(40);
DECLARE DT VARCHAR(40);
DECLARE done INT;
DECLARE Y VARCHAR(40);
DECLARE M VARCHAR(40);
DECLARE R VARCHAR(40);
DECLARE TEMP VARCHAR(40);
DECLARE DT1 VARCHAR(40);
DECLARE CUR_TRAN CURSOR FOR SELECT MEMBERID,PRODUCTID,date from reviewsnew;
DECLARE CONTINUE HANDLER FOR NOT FOUND SET done=1;
OPEN CUR_TRAN;
REPEAT FETCH CUR_TRAN into ID,ID1, DT;
SET DT1=DT;
SET Y=SUBSTRING_INDEX(DT,',',-1);
SET M=SUBSTRING_INDEX(DT,' ',1);
SET TEMP=SUBSTRING_INDEX(DT,',',1);
SET R=SUBSTRING_INDEX(TEMP,' ',-1);
IF M like 'January' THEN
SET TEMP=concat(Y,'-','01-',R);
END IF;
IF M like 'February' THEN
SET TEMP=concat(Y,'-','02-',R);
END IF;
IF M like 'March' THEN
SET TEMP=concat(Y,'-','03-',R);
END IF;
IF M like 'April' THEN
SET TEMP=concat(Y,'-','04-',R);
END IF;
IF M like 'May' THEN
SET TEMP=concat(Y,'-','05-',R);
END IF;
IF M like 'June' THEN
SET TEMP=concat(Y,'-','06-',R);
END IF;
IF M like 'July' THEN
SET TEMP=concat(Y,'-','07-',R);
END IF;
IF M like 'August' THEN
SET TEMP=concat(Y,'-','08-',R);
END IF;
IF M like 'Septemper' THEN
SET TEMP=concat(Y,'-','09-',R);
END IF;
IF M like 'October' THEN
SET TEMP=concat(Y,'-','10-',R);
END IF;
IF M like 'November' THEN
SET TEMP=concat(Y,'-','11-',R);
END IF;
IF M like 'December' THEN
SET TEMP=concat(Y,'-','12-',R);
END IF;update reviewsnew set date=TEMP WHERE MEMBERID=ID AND PRODUCTID=ID1 AND date=DT1;
until done=1 end repeat;
CLOSE CUR_TRAN;
END
create procedure tran()
BEGIN
DECLARE ID VARCHAR(40);
DECLARE ID1 VARCHAR(40);
DECLARE DT VARCHAR(40);
DECLARE done INT;
DECLARE Y VARCHAR(40);
DECLARE M VARCHAR(40);
DECLARE R VARCHAR(40);
DECLARE TEMP VARCHAR(40);
DECLARE DT1 VARCHAR(40);
DECLARE CUR_TRAN CURSOR FOR SELECT MEMBERID,PRODUCTID,date from reviewsnew;
DECLARE CONTINUE HANDLER FOR NOT FOUND SET done=1;
OPEN CUR_TRAN;
REPEAT FETCH CUR_TRAN into ID,ID1, DT;
SET DT1=DT;
SET Y=SUBSTRING_INDEX(DT,',',-1);
SET M=SUBSTRING_INDEX(DT,' ',1);
SET TEMP=SUBSTRING_INDEX(DT,',',1);
SET R=SUBSTRING_INDEX(TEMP,' ',-1);
IF M like 'January' THEN
SET TEMP=concat(Y,'-','01-',R);
END IF;
IF M like 'February' THEN
SET TEMP=concat(Y,'-','02-',R);
END IF;
IF M like 'March' THEN
SET TEMP=concat(Y,'-','03-',R);
END IF;
IF M like 'April' THEN
SET TEMP=concat(Y,'-','04-',R);
END IF;
IF M like 'May' THEN
SET TEMP=concat(Y,'-','05-',R);
END IF;
IF M like 'June' THEN
SET TEMP=concat(Y,'-','06-',R);
END IF;
IF M like 'July' THEN
SET TEMP=concat(Y,'-','07-',R);
END IF;
IF M like 'August' THEN
SET TEMP=concat(Y,'-','08-',R);
END IF;
IF M like 'Septemper' THEN
SET TEMP=concat(Y,'-','09-',R);
END IF;
IF M like 'October' THEN
SET TEMP=concat(Y,'-','10-',R);
END IF;
IF M like 'November' THEN
SET TEMP=concat(Y,'-','11-',R);
END IF;
IF M like 'December' THEN
SET TEMP=concat(Y,'-','12-',R);
END IF;update reviewsnew set date=TEMP WHERE MEMBERID=ID AND PRODUCTID=ID1 AND date=DT1;
until done=1 end repeat;
CLOSE CUR_TRAN;
END
解决方案 »
免费领取超大流量手机卡,每月29元包185G流量+100分钟通话, 中国电信官方发货