MySql 内部游标的循环嵌套使用

2013-07-05 04:45:59 779 阅读 AI 写同款 · GEO 优化AI 选题数据监测一键分发 ›

mysql 存储过程中游标cur和while循环的嵌套使用本来对存储过程不是很精，我们现在做的项目，基本上好多都是使用存储过程来完成一些业务逻辑的处理等。这里举例使用存储过程来实现支付完成后商品库存减少的功能：这里使用到了while循环和cur游标的循环嵌套。 main: BEGIN #处理付款成功减库存功能 #传递参数是订单id（oid） DECLARE tpid varchar(100); #DECLARE rt int def... 阅读详情

MySql 内部游标的循环嵌套使用

比如 DECLARE c_1 CURSOR FOR Select name from tabla a ;
DECLARE c_2 CURSOR FOR Select num from tabla b WHERE NAME=V_NAME;

open c_1 ;
FETCH c_1 Into V_NAME;
此时我想将V_NAME作为c_2 游标中select 语句后面的SQL变量条件，该如何处理！

Mysql菜鸟，望高手赐教!感激

...全文

关于mysql双游标嵌套循环方式以上为个人经验，希望能给大家一个参考！微点阅读。阅读详情

kcajyc1314

MySQL 57,062 成员 56,759 内容

+ 关注 + 加入社区

切换为时间正序

kcajyc1314 2013-07-06

打赏
举报

请问为什么我在113行打开 c_ck游标的时候V_DP在传入 DECLARE c_CK CURSOR FOR SELECT A.CK01, B.Dskh01 From JLTB_DPBMRY A, JLTB_DPXX B WHERE B.DPNICK=V_DP AND A.DPSID = B.DPSID; 一直都是空值啊？

kcajyc1314 2013-07-06

打赏
举报


CREATE DEFINER=`jusr6dvq8jji`@`%` PROCEDURE `JLTB_EXEC_XSDDSP`()
BEGIN
	#Routine body goes here...
  DECLARE v_GSID Varchar(4);
  DECLARE v_SPSL DECIMAL(16, 4); #金力商品数量
  DECLARE v_SPXX02  Varchar(30);
  DECLARE v_SPXX04  Varchar(100);
  DECLARE V_CK01    Varchar(16);
  DECLARE v_IFLAG   INT(8);
  DECLARE v_JQM     INT(8);
  DECLARE V_WSDJ   DECIMAL(16, 4);
  DECLARE V_TZJE   DECIMAL(16, 4);
  DECLARE V_YHJE   DECIMAL(16, 4);
  DECLARE V_XSZJ   DECIMAL(16, 4);
  DECLARE v_YYSPSL DECIMAL(16, 4);
  DECLARE v_FFTT   VARCHAR(1000); 
  DECLARE V_FWSPXX01 DECIMAL(16);
  DECLARE V_WLBBJ  INT(8);
  DECLARE V_SSKH   DECIMAL(16);
  DECLARE v_FLAG int;
  DECLARE v_SPZL int;

  DECLARE isRecordNotFound bool default 1;

  /*声明c_XSDDITEM游标内变量*/
  DECLARE V_XSDD03 VARCHAR(100);
  DECLARE V_XSDD02 VARCHAR(100);
  DECLARE V_XSDD01 VARCHAR(50);
  DECLARE V_XSDDI01 VARCHAR(50);
  DECLARE V_SL      DECIMAL(16,4);
  DECLARE V_XSDD34  VARCHAR(50);
  DECLARE V_TBSPID  DECIMAL(16);
  DECLARE V_XSDDI10    DECIMAL(16,4);
  DECLARE V_XSDD18  VARCHAR(100);
  DECLARE V_C_SPXX02 VARCHAR(100);
  DECLARE V_XSDDI03  int(8);
  DECLARE V_SGTZJE   DECIMAL(16,4);
  DECLARE V_C_YHJE     DECIMAL(16,4);
  DECLARE V_XSDDI05  DECIMAL(16,4);
  DECLARE V_SKUID    VARCHAR(50);
  DECLARE V_SSTATUS  VARCHAR(100);
  DECLARE V_FPTT     VARCHAR(100); 
  DECLARE V_DP       VARCHAR(100);


  /*声明c_JLTBSPXX游标变量*/
  DECLARE V_SPXX01   DECIMAL(16);
  DECLARE v_SPGGITEM01 DECIMAL(16,4);
  DECLARE V_ZSPBJ    int(8);
  DECLARE V_SPGGITEM03 int(8);
  DECLARE V_JG DECIMAL(16,4);

  /*声明c_CK游标变量*/
  DECLARE c_CK01 VARCHAR(16);
  DECLARE c_DSKH01 int(8);

  /*声明游标 */
  DECLARE c_XSDDITEM CURSOR FOR Select A.XSDD03, A.XSDD02, B.XSDD01, B.XSDDI01, B.XSDDI05 SL, A.XSDD34, 
             B.XSDDI06 TBSPID, A.XSDD18 XSDD18, B.XSDDI20 as SPXX02,
             B.XSDDI10, B.XSDDI03, B.XSDDI02 as SGTZJE, B.XSDDI04 as YHJE,
             B.XSDDI05, B.XSDDI23 SKUID, A.XSDD36 as SSTATUS,
             C.XSDDZ47 as FPTT
        From JLTB_DOWN_XSDD A, JLTB_DOWN_XSDDITEM B, JLTB_DOWN_XSDDZT C
       Where A.XSDD01 = B.XSDD01
         And A.XSDD01 = C.XSDD01
         AND A.LYLX = B.LYLX
         AND A.LYLX = C.LYLX
         AND (A.XSDD02 <> 'WAIT_BUYER_PAY')
         AND (A.XSDD02 <> 'TRADE_CLOSED_BY_TAOBAO')
         And C.XSDDZ37 = 0
       Order By B.XSDD01, B.XSDDI01;

  DECLARE c_JLTBSPXX CURSOR FOR SELECT SPXX01, SPGGITEM01 SL, SPGGITEM03 ZSPBJ, SPGGITEM02 JG,SPGGITEM03
                                From JLTB_SPGGITEM Where SPGG01 = V_SKUID Order By SPGGITEM03 Desc; #主商品排第一 

  DECLARE c_CK CURSOR FOR SELECT A.CK01, B.Dskh01 From JLTB_DPBMRY A, JLTB_DPXX B
                                 WHERE B.DPNICK=V_DP AND  A.DPSID = B.DPSID;

  DECLARE CONTINUE HANDLER FOR NOT FOUND set isRecordNotFound=1;
  #必须这样写，不然进入不了游标#
  SET isRecordNotFound=0;
  OPEN c_XSDDITEM;
   Loop1: Loop
   FETCH c_XSDDITEM Into V_XSDD03,V_XSDD02,V_XSDD01,V_XSDDI01,V_SL,V_XSDD34,
                         V_TBSPID,V_XSDD18,V_C_SPXX02,V_XSDDI10,V_XSDDI03,
                         V_SGTZJE,V_C_YHJE,V_XSDDI05,V_SKUID,V_SSTATUS,V_FPTT;
   IF isRecordNotFound <> 1 THEN
		 Set V_WLBBJ = 0;
		 SET v_FLAG = 1;
		 Block1:BEGIN
			 declare continue handler for not found set v_FLAG = 0;
				 Select JL
					 Into v_FLAG
						From V_XSDD_ZT
					 WHERE V_XSDD_ZT.XSDD01 = V_XSDD01; 
		 END Block1;
		 SET v_GSID='';
     If (v_FLAG = 0) AND (V_XSDDI03 = 0) Then
			 open c_JLTBSPXX;
       Loop2:Loop
			 FETCH c_JLTBSPXX Into V_SPXX01,v_SPGGITEM01,V_ZSPBJ,V_JG,V_SPGGITEM03;
					SET v_SPZL = 1;
      	  Block2:BEGIN
						 declare continue handler for not found set v_SPZL = 0;
						 Select SPXX02, SPXX04, SPXX28
							Into v_SPXX02, v_SPXX04, v_JQM
							 From SPXX
						Where SPXX01 = V_SPXX01; #取商品重量
					END Block2;

					SET V_SPSL = V_SL * v_SPGGITEM01;
          SET V_DP = V_XSDD18;
					open c_CK;
          Loop3 :Loop
					FETCH  c_CK Into c_CK01,c_DSKH01;
             IF isRecordNotFound = 1 THEN 
               LEAVE Loop3;
             END IF;
						 SET V_CK01 = c_CK01;
						 SET V_GSID = GET_GSID(V_CK01);
						 SET V_SSKH = c_DSKH01;
					 END Loop Loop3;
					CLOSE c_CK;
					/*防止c_CK游标无值导致isRecordNotFound=1*/
					SET isRecordNotFound=0; 
					
					IF V_SPGGITEM03 = 1 THEN  
						SET V_TZJE = V_SGTZJE;
						SET V_YHJE = V_C_YHJE;
						SET V_WSDJ = V_XSDDI10;
						SET V_XSZJ = V_XSDDI10 * V_XSDDI05;
					ELSE 
						SET V_TZJE = 0;
						SET V_YHJE = 0;
						SET V_WSDJ = 0;
						SET V_XSZJ = 0;
					END IF;

					Insert Into JLTB_DOWN_XSDDSP
							(XSDD01, XSDDI01, SPXX01, XSDDS01, XSDDS02, XSDDS03, XSDDS04,
							 XSDDS08, CK01, NEWCK01, KHSCK01, XSDDS09, XSDDS12,
							 XSDDS13, XSDDS14, XSDDS15, YSPXX01, XSDDS17,DSKH01)
					Values
							(V_XSDD01, V_XSDD01,V_SPXX01,
							 v_SPGGITEM01,v_SPSL, V_XSZJ - V_YHJE + V_TZJE, v_SPZL,
							 V_TBSPID, V_CK01, V_CK01, NULL, v_JQM, V_TZJE, V_YHJE,
							 V_WSDJ, 0, V_SPXX01, V_WLBBJ,V_SSKH);
       END Loop Loop2;
			 CLOSE c_JLTBSPXX;
				
			 IF V_FPTT IS NOT NULL THEN
				 SET v_FFTT = '发票抬头:' || V_FPTT;
			 ELSE 
				 SET v_FFTT = NULL;
			 END IF; 

			 UPDATE JLTB_DOWN_XSDD
						SET GSXX01 = v_GSID, XSDD40 = v_FFTT
				 WHERE XSDD01 = V_XSDD01;
		 END IF;
   END IF;
   END Loop Loop1;
   CLOSE c_XSDDITEM;
END

ACMAIN_CHM 2013-07-05

打赏
举报

DECLARE c_1 CURSOR FOR Select name from tabla a ; DECLARE c_2 CURSOR FOR Select num from tabla b WHERE NAME=V_NAME; open c_1 ; FETCH c_1 Into V_NAME; open c_2 直接在变量赋值后再OPEN C2就可以了。

WWWWA 2013-07-05

打赏
举报

DECLARE c_2 CURSOR FOR Select num from tt FETCH c_1 Into V_NAME; drop table IF EXISTS tt; set @asql=concat('create table tt as select num from tabla where NAME="',V_NAME,'"'); prepare stml from @asql; execute stml;

MySql 内部游标的循环嵌套使用

相关推荐