mysql之存储过程

一时失言乱红尘 2021-09-28 02:46 301阅读 0赞

分享一篇mysql存储过程实例

BEGIN
     	 DECLARE Done INT DEFAULT 0;   
        DECLARE NSRSBH VARCHAR(30); 
        DECLARE NSRYF VARCHAR(30);
        DECLARE NN INT DEFAULT 0;
        DECLARE CC INT DEFAULT 0;
        DECLARE rs CURSOR FOR select gf_nsrsbh nn, left(kprq,7) ss from invoice 
    	 	where kprq > left(date_sub(now(), interval 12 month), 10) group by gf_nsrsbh ,left(kprq, 7) ORDER BY GF_NSRSBH;
        DECLARE CONTINUE HANDLER FOR SQLSTATE '02000' SET Done = 1; 
        OPEN rs;   
        FETCH NEXT FROM rs INTO NSRSBH, NSRYF;      
        REPEAT 
        	  SELECT NSRYF;
            IF NOT Done THEN 
            		/*	REPEAT 
            		  	  SET CC = CC + 1;*/
    	        		  SELECT count(*) N into NN FROM TEST WHERE ID = NSRSBH AND YF = NSRYF;
    	        		  IF NN>0 THEN 
    	        		      SELECT CC;
    	        		  		SET NN = 0;
    					  		UPDATE TEST SET SE = (SELECT SUM(SE) SE FROM INVOICE 
    							WHERE GF_NSRSBH = NSRSBH AND LEFT(KPRQ,7) = NSRYF), UPDATE_DATE = NOW();
    	        		  ELSE 
    	        		      SELECT CC;
    	        		  		INSERT INTO TEST(ID,YF,SE) SELECT GF_NSRSBH,NSRYF YF, SUM(SE) SE FROM INVOICE 
    					 		WHERE GF_NSRSBH = NSRSBH AND LEFT(KPRQ,7) = NSRYF GROUP BY GF_NSRSBH, LEFT(KPRQ,7); 
    	        		  END IF;
    				  /*UNTIL CC = 12 
            		  END REPEAT;*
    				  /*REPEAT 
            		  	  SET CC = CC + 1;
            		  	  
    	        		  delete FROM test where ID = NSRSBH AND YF = left(MON,7);
    	        		  SELECT CC;
    	        		  INSERT INTO TEST(ID,YF,SE) SELECT GF_NSRSBH,left(MON,7) YF, SUM(SE) SE FROM INVOICE 
    					  WHERE GF_NSRSBH = NSRSBH AND LEFT(KPRQ,7) = left(MON,7) GROUP BY GF_NSRSBH, LEFT(KPRQ,7); 
            		     SET MON = DATE_SUB(MON, INTERVAL 1 MONTH);
            		     SELECT MON;
    				  UNTIL CC = 12 
            		  END REPEAT;
            		  SET MON = NOW();*/
             END IF;           
        FETCH NEXT FROM rs INTO NSRSBH, NSRYF;  
        UNTIL Done END REPEAT; 
        CLOSE rs; 
    END