mysql存储过程使用DECLARE定义游标遇到的问题

左手的ㄟ右手 2022-12-27 11:20 224阅读 0赞

## mysql存储过程使用DECLARE定义游标遇到的问题 ##

话不多说，直接上SQL代码

DECLARE updateId CURSOR FOR (select custId from tempforreceipt where ISNULL(deliveryID)=1 or LENGTH(trim(deliveryID))=0);
    	OPEN updateId; 	
      updateList: LOOP
    	 FETCH updateId INTO custId2; 
    	IF done = 1 THEN
    		LEAVE updateList; 
    	END IF; 
    	  update tempforreceipt set `pid` = 0 where custId = custId2 and (ISNULL(deliveryID)=1 or LENGTH(trim(deliveryID))=0);
    	  update tempforreceipt set `deliveryID` = i where custId = custId2 and (ISNULL(deliveryID)=1 or LENGTH(trim(deliveryID))=0);		
    	  set i = i+1;
      END LOOP updateList;			
    	CLOSE updateId;

这个是我写的游标代码，没有任何问题。  
但是报错存储过程的时候报错，如下：  
![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2NhaXh1YW5qaQ_size_16_color_FFFFFF_t_70]  
报错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 updateId CURSOR FOR (select custId from tempforreceipt where ISNULL(deli’ at line 43。

这样的错误也是无从下手，找了很多文章资料，原来在MySQL中，存储过程的所有关于数据库增删改查的操作必须在所有 declarre声明完所有变量 ,包括游标等 之后再执行,否则该存储过程将无法运行。

必须写在语句块的最前面，即出现在其他增删改查业务语句之前！！！！！

所以上面的代码只要把定义游标的语句提到整个语句块的最前面，或者说业务查询语句之前就正确了！

OPEN updateId; 	
      updateList: LOOP
    	 FETCH updateId INTO custId2; 
    	IF done = 1 THEN
    		LEAVE updateList; 
    	END IF; 
    	  update tempforreceipt set `pid` = 0 where custId = custId2 and (ISNULL(deliveryID)=1 or LENGTH(trim(deliveryID))=0);
    	  update tempforreceipt set `deliveryID` = i where custId = custId2 and (ISNULL(deliveryID)=1 or LENGTH(trim(deliveryID))=0);		
    	  set i = i+1;
      END LOOP updateList;			
    	CLOSE updateId;

[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2NhaXh1YW5qaQ_size_16_color_FFFFFF_t_70]: /images/20221120/db0653146cbe466ba7871d23610b0a4d.png