在存储过程中建立一个光标进行循环查询
DECLARE cur1 cursor for select FMethodID,FID FROM WincoBosCombinedPmc;
OPEN cur1;
REPEAT
FETCH cur1 INTO aa, bb;
IF NOT done THEN
SET $T_FBang='';
END IF;
UNTIL done END REPEAT;
CLOSE cur1;
执行时报错:
错误码: 1064
You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'DECLARE cur1 cursor for select FMethodID,FID FROM WincoBosCombinedPmc;
OPEN cur1' at line 40
还想问一下,mysql中的光标是否和ms sql server中的游标作用类似。现在是想把ms sql server已写好的存储过程移植到mysql平台上边。
DECLARE cur1 cursor for select FMethodID,FID FROM WincoBosCombinedPmc;
OPEN cur1;
REPEAT
FETCH cur1 INTO aa, bb;
IF NOT done THEN
SET $T_FBang='';
END IF;
UNTIL done END REPEAT;
CLOSE cur1;
执行时报错:
错误码: 1064
You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'DECLARE cur1 cursor for select FMethodID,FID FROM WincoBosCombinedPmc;
OPEN cur1' at line 40
还想问一下,mysql中的光标是否和ms sql server中的游标作用类似。现在是想把ms sql server已写好的存储过程移植到mysql平台上边。
第20章:存储程序和函数mysql> CREATE PROCEDURE tp ()
-> BEGIN
-> DECLARE done INT DEFAULT 0;
-> DECLARE $T_FBang varchar(30);
->
-> DECLARE cur1 CURSOR FOR select FMethodID,FID FROM WincoBosCombinedPmc;
-> DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = 1;
->
-> OPEN cur1;
-> REPEAT
-> SET $T_FBang='';
-> UNTIL done END REPEAT;
->
-> CLOSE cur1;
->
-> END;
-> //
Query OK, 0 rows affected (0.00 sec)
DELIMITER $$DROP PROCEDURE IF EXISTS `wincodb`.`P_GetBosCombinedQZ`$$CREATE DEFINER=`root`@`%` PROCEDURE `P_GetBosCombinedQZ`($M_FOrder INT,$M_FDate DATE,$M_FClassID INT)
BEGIN
#DECLARE $T_FLevalID INT,$T_FSizeID INT,$T_FWorkNo VARCHAR(45);
DECLARE $T_FLevel,$T_FBang,$T_FSize,$T_FWorkNo VARCHAR(45);
DECLARE $T_FName,$T_FColor VARCHAR(128);
DECLARE $T_FInterID,$maxCount,$j,$T_FMethodID,$T_FID INT;
DECLARE $T_FNote VARCHAR(256);
DECLARE $FchkMYS1,$FchkMYS2,$FchkMYS3,$FchkMYS4,$FchkMYS5 TINYINT;
DECLARE $FchkDYS1,$FchkDYS2,$FchkDYS3,$FchkDYS4,$FchkDYS5 TINYINT;
DECLARE $FtxtMYS,$FtxtDYS VARCHAR(45);
DECLARE $Color1,$Color2 INT;
DROP TABLE IF EXISTS PMC_WorkNoList;
CREATE TABLE PMC_WorkNoList(
FInterID INT DEFAULT 1, #内码,排序用的
FID INT NULL,
FEntryID INT NULL, #序列号
FMethodID INT NULL, #方法标号
FWorkNo VARCHAR(45) NULL, #工程单号
FCustomerName VARCHAR(45) NULL,#客户
FName VARCHAR(128) NULL, #物料名称
FYSSL DECIMAL(28,10) NULL, #订单实数
FZDSS DECIMAL(28,10) NULL, #最低实数
FBPSL DECIMAL(28,10) NULL, #本批数量
FJGSL DECIMAL(28,10) NULL, #加工数量
FDeliveryDate VARCHAR(128) NULL, #交期
FMadeWidth DECIMAL(28,10) NULL, #开纸长
FMadeLong DECIMAL(28,10) NULL, #开纸宽
FLevel VARCHAR(128) NULL, #纸材
FBang INT, #磅数
FSize VARCHAR(50) NULL, #纸度
FNote VARCHAR(256) NULL,
FColor NVARCHAR(128) NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
IF $M_FDate=''THEN
INSERT INTO PMC_WorkNoList(FWorkNo,FCustomerName,FName,FYSSL,FZDSS,FBPSL,FJGSL,FDeliveryDate,FSize,FMadeWidth,
FMadeLong,FID,FEntryID,FMethodID)
SELECT A.FBillNo,A.FCustomerName,A.FName,A.FYSSL,A.FZDSS,B.FBPSL,B.FJGSL,B.FDeliveryDate,A.FSize0,A.FMadeWidth0
,A.FMadeLong0,B.FID,B.FEntryID,B.FMethodID
FROM WincoBosCombinedPmc A,WincoBosCombinedPmcEntry B
WHERE(A.FBillNo=B.FWorkNo)AND(D.FOrder=$M_FOrder)
AND(B.FMethodID!=2) ORDER BY B.FMethodID,B.FEntryID;
ELSE
INSERT INTO PMC_WorkNoList(FWorkNo,FCustomerName,FName,FYSSL,FZDSS,FBPSL,FJGSL,FDeliveryDate,FSize,FMadeWidth,
FMadeLong,FID,FEntryID,FMethodID)
SELECT A.FBillNO,A.FCustomerName,A.FName,A.FYSSL,A.FZDSS,B.FBPSL,B.FJGSL,A.FDeliveryDate,A.FSize0,A.FMadeWidth0
,A.FMadeLong0,B.FID,B.FEntryID,B.FMethodID
FROM WincoBosCombinedPmc A,WincoBosCombinedPmcEntry B
WHERE(A.FBillNo=B.FWorkNo)AND(A.FOrder=@M_FOrder);
END IF;
SET $maxCount=0;
SET $j=0;
SELECT SUM(FInterID) INTO $maxCount FROM PMC_WorkNoList
WHERE FMethodID!=2 GROUP BY FInterID; DECLARE done INT DEFAULT 0;
DECLARE PMC_CH CURSOR FOR
SELECT A.FLevel0,B.FWorkNo,B.FMethodID,B.FID
FROM WincoBosCombinedPmc A,PMC_WorkNoList B
WHERE A.FBillNO=B.FWorkNo ORDER BY B.FEntryID;
OPEN PMC_CH;
REPEAT
FETCH PMC_CH INTO $T_FLeval,$T_FWorkNo,$T_FMethodID,$T_FID;
IF NOT done THEN
DECLARE $str VARCHAR(100);
DECLARE $i INT;
SET $str=CHAR_LENGTH($T_FLeval);
SET $i=1;
SET $T_FBang='';
END IF;
UNTIL done END REPEAT;
CLOSE PMC_CH; DROP TABLE PMC_WorkNoList;
END$$DELIMITER ;
FETCH PMC_CH INTO $T_FLeval,$T_FWorkNo,$T_FMethodID,$T_FID;
没有这个光标怎么移动呢?
DECLARE $done INT DEFAULT 0;
DECLARE PMC_CH CURSOR FOR
SELECT A.FLevel0,B.FWorkNo,B.FMethodID,B.FID
FROM WincoBosCombinedPmc A,PMC_WorkNoList B
WHERE A.FBillNO=B.FWorkNo ORDER BY B.FEntryID;
DECLARE CONTINUE HANDLER FOR NOT FOUND SET $done = 1;OPEN PMC_CH;
REPEAT
FETCH PMC_CH INTO $T_FLevel,$T_FWorkNo,$T_FMethodID,$T_FID;
BEGIN
DECLARE $strLength,$i INT;
IF NOT $done THEN
SET $strLength=LENGTH($T_FLeval);
SET $i=1;
SET $T_FBang='';
WHILE $i<$str D0
IF LOCATE('%[0-9]%',SUBSTRING($T_FLeval,$i,1))>0 THEN
SET $T_FLevel=LEFT($T_FLeval,LOCATE('%[0-9]%',$T_FLeval)-1);
SET $T_FBang=$T_FBang+SUBSTRING($T_FLeval,$i,1);
END IF;
SET $i=$i+1;
END WHILE;
SET $T_FLevel=RTRIM(REPLACE($T_FLevel,'-',''));
SET $T_FBang=CAST(TRIM(REPLACE($T_FBang,'-',''))AS INT);
END IF;
END;
UNTIL $done END REPEAT;
CLOSE PMC_CH;
提示出错:
错误码: 1064
You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'WHILE $i<$str D0
IF LOCATE('%[0-9]%',SUBSTRING($T_FLeval,$i,1))>0 THEN
' at line 35
请大家给我找找原因呀?
SET $T_FBang=CAST(TRIM(REPLACE($T_FBang,'-','')) AS DECIMAL);