tmrgo/name.sql

CREATE DEFINER=`root`@`%` PROCEDURE `updatedownloadprojname`(
  pastureidIN BIGINT (20),
  dateIN VARCHAR(50)
)
label:BEGIN
  DECLARE positionIdS
  INT (20);
 DECLARE
 positionNameS VARCHAR (100);
  DECLARE i,amount,sorts,amountsl,sortssl,havebuttons,havebuttond1 INT(11) DEFAULT 0;
  DECLARE msg1 TEXT;
   DECLARE code1 CHAR(5) DEFAULT '00000';
   DECLARE ids,projnames,temstr,tem,pids,idssl varCHAR(30);
   DECLARE t_error INTEGER DEFAULT 0;
  DECLARE CONTINUE HANDLER FOR SQLEXCEPTION
  BEGIN
      -- 获取异常code,异常信息
      GET DIAGNOSTICS CONDITION 1
      code1 = RETURNED_SQLSTATE, msg1 = MESSAGE_TEXT;
   END;

  #开启事务
   START TRANSACTION;
	 
	 
	 select 1 into tem;
	SELECT '' INTO temstr;
	SELECT 0 INTO sorts;

	 SELECT count(*) into amount  FROM downloadedplan
	WHERE mydate = dateIN AND pastureid = pastureidIN   ;
	
	 WHILE amount > 0 DO
	 
	  SELECT amount-1 INTO amount;
		
	         SELECT id,projname,pid,havebuttons INTO ids,projnames,pids,havebuttons FROM downloadedplan
	 WHERE mydate = dateIN AND pastureid = pastureidIN 
	 ORDER BY projname,id ASC LIMIT sorts,1;
	 
	 
	 
	 IF(length(projnames) = 6) and havebuttons = 0
	 THEN
	 SELECT '1' INTO tem;
	 SELECT LEFT(projnames,2) INTO temstr;
	 
	 	 WHILE (SELECT COUNT(*) FROM downloadedplan  WHERE pastureid=pastureidIN and mydate = dateIN AND projname = CONCAT(LEFT(temstr,2),LPAD(tem,3,'0')))>0 do
	  SELECT tem+1 INTO tem;
	 end WHILE;
	 
	 	 UPDATE downloadedplan
	 SET projname = CONCAT(LEFT(projname,2),LPAD(tem,3,'0'))
	 WHERE pastureid=pastureidIN AND id = ids;
	  END IF;
		
		
		select count(1) into amountsl FROM  downloadedplan  WHERE pastureid=pastureidIN and mydate = dateIN AND length(projname) > 6  and pid = pids;
-- -- 		
			SELECT 0 INTO sortssl;
			 select projname into projnames from downloadedplan where id = ids;
			
			 
			if  length(projnames) < 6 THEN
-- 			 select projnames;
		 WHILE amountsl > 0 DO
		  SELECT 0 INTO havebuttond1;
-- 		 
			select id,havebutton into idssl,havebuttond1 FROM downloadedplan WHERE pastureid=pastureidIN and mydate = dateIN AND length(projname) > 6  and pid = pids  ORDER BY projname,id ASC LIMIT sortssl,1;
-- 		 
		 SELECT sortssl + 1 INTO sortssl;
		 if havebuttond1 = 0 THEN
						UPDATE downloadedplan
						SET projname = CONCAT(projnames,'.', sortssl)
						WHERE pastureid=pastureidIN AND id = idssl;
		  END IF; 		 
		 SELECT amountsl-1 INTO amountsl;
		END WHILE;
		END IF; 		 
		SELECT sorts + 1 INTO sorts;

	  END WHILE;	
	 
	 
	 
	 
	 
	 
	 
	 
	 SELECT 0 INTO sorts;
	 SELECT 0 INTO amount;
	 
  	SELECT count(*) into amount  FROM downloadedplan
	WHERE mydate = dateIN AND pastureid = pastureidIN  ;
	
	WHILE amount > 0 DO
	        SELECT id INTO ids FROM downloadedplan
	 WHERE mydate = dateIN AND pastureid = pastureidIN  
	 ORDER BY projname asc limit sorts,1;
	
	 SELECT sorts+1 INTO sorts;
	 
		 UPDATE downloadedplan
	 SET sort = sorts
	 WHERE pastureid=pastureidIN AND id = ids;
    
     SELECT amount-1 INTO amount;
	 END WHILE;	





-- 	SELECT count(*) into amount  FROM downloadedplan
-- 	WHERE mydate = dateIN AND pastureid = pastureidIN and length(projname)>5
-- 	 ORDER BY projname,id asc;
-- 	
-- 

-- 	update downloadedplan  
-- 	set sort = 200 + sort
-- 	WHERE pastureid=pastureidIN AND mydate = dateIN and LENGTH(projname)>5;
-- 	
--   WHILE amount > 0 DO
--      SELECT amount-1 INTO amount;
--     
--         SELECT id,projname INTO ids,projnames FROM downloadedplan
-- 	 WHERE mydate = dateIN AND pastureid = pastureidIN AND LENGTH(projname)>5
-- 	 ORDER BY projname,id ASC LIMIT 1;
-- 	 
-- 	 
-- 	 
-- 	 IF(temstr<>LEFT(projnames,2))
-- 	 THEN
-- 		SELECT '1' INTO tem;
-- 		SELECT LEFT(projnames,2) INTO temstr;
-- 	 END IF;
-- 	 
-- 	 WHILE (SELECT COUNT(*) FROM downloadedplan  WHERE pastureid=pastureidIN and mydate = dateIN AND projname = CONCAT(LEFT(temstr,2),LPAD(tem,3,'0')))>0 do
-- 	  SELECT tem+1 INTO tem;
-- 	 end WHILE;
-- 
-- 	 WHILE (SELECT COUNT(*) FROM downloadedplan  WHERE pastureid=pastureidIN AND mydate = dateIN AND sort = sorts)>0 DO
-- 	  SELECT sorts+1 INTO sorts;
-- 	 END WHILE;
-- 	 
-- 	 UPDATE downloadedplan
-- 	 SET projname = CONCAT(LEFT(projname,2),LPAD(tem,3,'0')),
-- 	 sort = sorts
-- 	 WHERE pastureid=pastureidIN AND id = ids;
-- 
-- 	 SELECT tem+1 INTO tem;
-- 	 SELECT sorts+1 INTO sorts;
-- 	   
--   END WHILE;	
	
       
 
  IF code1 = '00000' THEN
    COMMIT;
   SELECT
    'success' msg;
  ELSE
     ROLLBACK;
   SELECT
    msg1 msg;
  END IF;  


END