存储过程程式如下:
#创建光标
DECLARE $done INT DEFAULT 0;
DECLARE PMC_CH CURSOR FOR
SELECT FWorkNo,FMethodID,FID,FYSSL
FROM PMC_WorkNoList
ORDER BY FEntryID;
DECLARE CONTINUE HANDLER FOR NOT FOUND SET $done=1;OPEN PMC_CH;
REPEAT
FETCH PMC_CH INTO $T_FWorkNo,$T_FMethodID,$T_FID,$T_FYSSL;
BEGIN
IF NOT $done THEN
对表PMC_WorkNoList进行更新操作;
END IF;
END;
UNTIL $done END REPEAT;
CLOSE PMC_CH;
检测发现对表PMC_WorkNoList只更新了第一行的数据,其它的没有更新,不知道为什么?
#创建光标
DECLARE $done INT DEFAULT 0;
DECLARE PMC_CH CURSOR FOR
SELECT FWorkNo,FMethodID,FID,FYSSL
FROM PMC_WorkNoList
ORDER BY FEntryID;
DECLARE CONTINUE HANDLER FOR NOT FOUND SET $done=1;OPEN PMC_CH;
REPEAT
FETCH PMC_CH INTO $T_FWorkNo,$T_FMethodID,$T_FID,$T_FYSSL;
BEGIN
IF NOT $done THEN
对表PMC_WorkNoList进行更新操作;
END IF;
END;
UNTIL $done END REPEAT;
CLOSE PMC_CH;
检测发现对表PMC_WorkNoList只更新了第一行的数据,其它的没有更新,不知道为什么?
解决方案 »
- 为什么mysql存储过程使用PHP无法执行,而命令行方式执行是好的,怎么解决?
- 一道较难面试题(mysql)
- mysql 中文乱码 如何恢复
- 有用C语言开发mysql数据库的同事吗?问个结果集获取的问题。
- MSSql 导出数据到MySql,从gb2312转成utf-8编码问题
- 用powerdesign产生的sql出错!
- MS SQL的语句在MySQL里如何表示?
- 使用grant命令创建了一个mysql 用户,如何让其生效?
- 哪里有mysql的客户端工具,可以登陆或者管理的!
- 在win下编的vc程序,怎样使它更新web服务器上的mysql数据库,(odbc,mysql api 皆可),万分感谢!
- mysql多表查询排序
- mysql 联查相减 老鸟帮忙看看 要怎么才能得出结果
DECLARE $done INT DEFAULT 0;DECLARE PMC_CH CURSOR FOR
SELECT FWorkNo,FMethodID,FID,FYSSL
FROM PMC_WorkNoList
ORDER BY FEntryID;
DECLARE CONTINUE HANDLER FOR NOT FOUND SET $done=1;OPEN PMC_CH;REPEAT
FETCH PMC_CH INTO $T_FWorkNo,$T_FMethodID,$T_FID,$T_FYSSL;
IF NOT $done THEN
对表PMC_WorkNoList进行更新操作;
END IF;
UNTIL $done=1 END REPEAT;
CLOSE PMC_CH;
-> (
-> FWorkNo int,
-> FMethodID int,
-> FID int,
-> FYSSL int,
-> FEntryID int
-> );
Query OK, 0 rows affected (0.05 sec)mysql> insert into PMC_WorkNoList values (1,1,1,1,1);
Query OK, 1 row affected (0.06 sec)mysql> insert into PMC_WorkNoList values (2,2,2,2,2);
Query OK, 1 row affected (0.02 sec)mysql> insert into PMC_WorkNoList values (3,3,3,3,3);
Query OK, 1 row affected (0.08 sec)mysql>
mysql> delimiter //
mysql>
mysql> CREATE PROCEDURE simpleproc ()
-> BEGIN
-> DECLARE $T_FWorkNo INT;
-> DECLARE $T_FMethodID INT;
-> DECLARE $T_FID INT;
-> DECLARE $T_FYSSL INT;
->
-> DECLARE $done INT DEFAULT 0;
->
->
-> DECLARE PMC_CH CURSOR FOR
-> SELECT FWorkNo,FMethodID,FID,FYSSL
-> FROM PMC_WorkNoList
-> ORDER BY FEntryID;
-> DECLARE CONTINUE HANDLER FOR NOT FOUND SET $done=1;
->
-> OPEN PMC_CH;
->
-> REPEAT
-> FETCH PMC_CH INTO $T_FWorkNo,$T_FMethodID,$T_FID,$T_FYSSL;
-> IF NOT $done THEN
-> select $T_FWorkNo,$T_FMethodID,$T_FID,$T_FYSSL;
-> END IF;
-> UNTIL $done=1 END REPEAT;
-> CLOSE PMC_CH;
-> END;
-> //
Query OK, 0 rows affected (0.02 sec)mysql>
mysql> delimiter ;
mysql> CALL simpleproc();
+------------+--------------+--------+----------+
| $T_FWorkNo | $T_FMethodID | $T_FID | $T_FYSSL |
+------------+--------------+--------+----------+
| 1 | 1 | 1 | 1 |
+------------+--------------+--------+----------+
1 row in set (0.00 sec)+------------+--------------+--------+----------+
| $T_FWorkNo | $T_FMethodID | $T_FID | $T_FYSSL |
+------------+--------------+--------+----------+
| 2 | 2 | 2 | 2 |
+------------+--------------+--------+----------+
1 row in set (0.00 sec)+------------+--------------+--------+----------+
| $T_FWorkNo | $T_FMethodID | $T_FID | $T_FYSSL |
+------------+--------------+--------+----------+
| 3 | 3 | 3 | 3 |
+------------+--------------+--------+----------+
1 row in set (0.00 sec)Query OK, 0 rows affected (0.00 sec)mysql>
DELIMITER $$DROP PROCEDURE IF EXISTS `wincodb`.`P_GetBosCombinedBH`$$CREATE DEFINER=`root`@`%` PROCEDURE `P_GetBosCombinedBH`($M_FOrder INT,$M_FDate VARCHAR(45),$M_FClassID INT)
BEGIN
DECLARE $T_FWorkNo VARCHAR(45) CHARACTER SET utf8;
DECLARE $T_FJGSL,$T_FYSSL DECIMAL(28,10);
DECLARE $T_FInterID,$maxCount,$j,$T_FMethodID,$T_FID INT;
DECLARE $T_FNote VARCHAR(256) CHARACTER SET utf8;
DECLARE $FQZ,$FYS,$FGY,$FBKZ,$FBH,$FCK,$FMK TINYINT;
DECLARE $FClassName,$T_FClassName VARCHAR(128) CHARACTER SET utf8;
DECLARE $FchkGY1,$FchkGY2,$FchkGY3,$FchkXS1,$FchkXS2,$FchkMG,$FchkUV1,$FchkUV3 TINYINT;
#创建光标
DECLARE $done INT DEFAULT 0;
DECLARE PMC_CH CURSOR FOR
SELECT FWorkNo,FMethodID,FID,FYSSL
FROM PMC_WorkNoList
ORDER BY FEntryID;
DECLARE CONTINUE HANDLER FOR NOT FOUND SET $done=1;
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, #订单实数
FWJGSL 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, #开纸宽
FClassName VARCHAR(128) NULL, #表明处理类别
FNote VARCHAR(256) NULL,
FGX VARCHAR(128) NULL
) ENGINE=InnoDB CHARACTER SET utf8 COLLATE utf8_general_ci;
IF($M_FDate='')THEN
INSERT INTO PMC_WorkNoList(FWorkNo,FCustomerName,FName,FYSSL,FBPSL,FJGSL,FDeliveryDate,FMadeWidth,
FMadeLong,FID,FEntryID,FMethodID,FNote,FGX)
SELECT A.FBillNO,A.FCustomerName,A.FName,A.FYSSL,B.FBPSL,B.FJGSL,B.FDeliveryDate,A.FMadeWidth0,A.FMadeLong0,B.FID,B.FEntryID,B.FMethodID,B.FNote,B.FGX
FROM WincoBosCombinedPmc A,WincoBosCombinedPmcEntry B
WHERE(A.FBillNo=B.FWorkNo)AND(B.FOrder=$M_FOrder)
AND(B.FMethodID!=2) ORDER BY B.FMethodID,B.FEntryID;
ELSE
INSERT INTO PMC_WorkNoList(FWorkNo,FCustomerName,FName,FYSSL,FBPSL,FJGSL,FDeliveryDate,FMadeWidth,
FMadeLong,FID,FEntryID,FMethodID,FNote,FGX)
SELECT A.FBillNO,A.FCustomerName,A.FName,A.FYSSL,B.FBPSL,B.FJGSL,B.FDeliveryDate,A.FMadeWidth0,A.FMadeLong0,B.FID,B.FEntryID,B.FMethodID,B.FNote,B.FGX
FROM WincoBosCombinedPmc A,WincoBosCombinedPmcEntry B
WHERE(A.FBillNo=B.FWorkNo)AND(B.FOrder=$M_FOrder)AND(B.FDate=$M_FDate)AND(B.FMethodID!=2)
ORDER BY B.FMethodID,B.FEntryID;
END IF;
SET $maxCount=0;
SET $j=0;
SELECT SUM(FInterID) INTO $maxCount FROM PMC_WorkNoList
WHERE FMethodID!=2 GROUP BY FInterID;
OPEN PMC_CH;
REPEAT
FETCH PMC_CH INTO $T_FWorkNo,$T_FMethodID,$T_FID,$T_FYSSL;
IF NOT $done THEN
SET $T_FJGSL=0;
SET $T_FYSSL=0;
SELECT FQZ,FYS,FGY,FBKZ,FBH,FCK,FMK
INTO $FQZ,$FYS,$FGY,$FBKZ,$FBH,$FCK,$FMK
FROM WincoBosCombinedPmc WHERE FBillNO=$T_FWorkNo;
SET $FClassName='';
SET $T_FClassName='';
IF $FQZ=1 THEN
SET $FCLassName='切.';
END IF;
IF $FYS=1 THEN
SET $FCLassName='印.';
END IF;
IF $FGY=1 THEN
SELECT FchkGY1,FchkGY2,FchkGY3,FchkXS1,FchkXS2,FchkMG,FchkUV1,FchkUV3
INTO $FchkGY1,$FchkGY2,$FchkGY3,$FchkXS1,$FchkXS2,$FchkMG,$FchkUV1,$FchkUV3
FROM WincoBosCombinedPmc
WHERE FBillNo=$T_FWorkNo;
IF($FchkGY1=1)THEN
SET $T_FClassName='光油';
END IF;
IF($FchkGY2=1)THEN
SET $T_FClassName=CONCAT($T_FClassName,'水油');
END IF;
IF($FchkGY3=1)THEN
SET $T_FClassName=CONCAT($T_FClassName,'啞油');
END IF;
IF($FchkXS1=1)THEN
SET $T_FClassName=CONCAT($T_FClassName,'環保吸塑');
END IF;
IF($FchkXS2=1)THEN
SET $T_FClassName=CONCAT($T_FClassName,'普通吸塑');
END IF;
IF($FchkMG=1)THEN
SET $T_FClassName=CONCAT($T_FClassName,'磨光');
END IF;
IF $FchkUV1=1 THEN
SET $T_FClassName='U.V';
END IF;
IF $FchkUV3=1 THEN
SET $T_FClassName='不脫U.V';
END IF;
SET $FCLassName=CONCAT($FCLassName,$T_FClassName,'.');
END IF;
IF $FBKZ=1 THEN
SET $FCLassName=CONCAT($FCLassName,'裱坑.');
END IF;
IF $FBH=1 THEN
SET $FCLassName=CONCAT($FCLassName,'啤.');
END IF;
IF $FCK=1 THEN
SET $FCLassName=CONCAT($FCLassName,'膠片.');
END IF;
IF $FMK=1 THEN
SET $FCLassName=CONCAT($FCLassName,'包裝');
END IF;
SELECT SUM(FJGSL) INTO $T_FJGSL FROM WincoBosCombinedPmcEntry
WHERE(FWorkNo=$T_FWorkNo)AND(FOrder=$M_FOrder)AND(FMethodID=2)
GROUP BY FWorkNo,FOrder,FMethodID;
IF($T_FMethodID=3)OR($T_FMethodID=4)THEN
UPDATE PMC_WorkNoList
SET FEntryID=$maxCount+1,FNote=$T_FNote,FClassName=$FClassName,FWJGSL=$T_FYSSL-$T_FJGSL
WHERE(FWorkNo=$T_FWorkNo)AND(FID=$T_FID);
UPDATE WincoBosCombinedPmcEntry
SET FEntryID=$maxCount+1
WHERE(FWorkNo=$T_FWorkNo)AND(FID=$T_FID);
SET $maxCount=$maxCount+1;
ELSE
UPDATE PMC_WorkNoList
SET FEntryID=$j+1,FNote=$T_FNote,FClassName=$FClassName,FWJGSL=$T_FYSSL-$T_FJGSL
WHERE(FWorkNo=$T_FWorkNo)AND(FID=$T_FID);
UPDATE WincoBosCombinedPmcEntry
SET FEntryID=$j+1
WHERE(FWorkNo=$T_FWorkNo)AND(FID=$T_FID);
SET $j=$j+1;
END IF;
SELECT * FROM PMC_WorkNoList WHERE FID=$T_FID ORDER BY FEntryID;
END IF;
UNTIL $done END REPEAT;
CLOSE PMC_CH;
IF( $M_FDate='')THEN
SELECT A.FID,A.FEntryID,A.FMethodID,A.FWorkNo,A.FCustomerName,A.FName,A.FYSSL,A.FWJGSL,A.FDeliveryDate
,A.FMadeWidth,A.FMadeLong,A.FBPSL,A.FJGSL,A.FNote,A.FClassName,A.FGX
FROM PMC_WorkNoList A,WincoBosCombinedPmcEntry B
WHERE(A.FID=B.FID)AND(B.FClassID=$M_FClassID)
ORDER BY A.FEntryID;
ELSE
SELECT * FROM PMC_WorkNoList ORDER BY FEntryID;
END IF;
DROP TABLE PMC_WorkNoList;
END$$DELIMITER ;
SELECT SUM(FJGSL) INTO $T_FJGSL FROM WincoBosCombinedPmcEntry
WHERE(FWorkNo=$T_FWorkNo)AND(FOrder=$M_FOrder)AND(FMethodID=2)
GROUP BY FWorkNo,FOrder,FMethodID;
为什么出现求和的时候光标就不能移动呢?
SET $T_FJGSL=0;
SET $T_FYSSL=0;
对其初时化后查询得出的结果为NULL.
由于你已经 DECLARE CONTINUE HANDLER FOR NOT FOUND SET $done=1; 所以此时 MySQL会进行 SET $done=1;等到执行 UNTIL $done END REPEAT; 的时候自然就结束了。
BEGIN
DECLARE $T_FWorkNo INT;
DECLARE $T_FMethodID INT;
DECLARE $T_FID INT;
DECLARE $T_FYSSL INT; DECLARE $done INT DEFAULT 0;
DECLARE PMC_CH CURSOR FOR
SELECT FWorkNo,FMethodID,FID,FYSSL
FROM PMC_WorkNoList
ORDER BY FEntryID;
DECLARE CONTINUE HANDLER FOR NOT FOUND SET $done=1; OPEN PMC_CH; FETCH PMC_CH INTO $T_FWorkNo,$T_FMethodID,$T_FID,$T_FYSSL;
WHILE $done = 0 DO
select $T_FWorkNo,$T_FMethodID,$T_FID,$T_FYSSL; select * from PMC_WorkNoList where FWorkNo=100; SET $done=0;
FETCH PMC_CH INTO $T_FWorkNo,$T_FMethodID,$T_FID,$T_FYSSL;
END WHILE;
CLOSE PMC_CH;
END;