mysql 光标使用

DROP PROCEDURE
IF EXISTS `InitFactorSignal`;
DELIMITER ;;

CREATE PROCEDURE `InitFactorSignal`()
BEGIN
    DECLARE t_error INTEGER DEFAULT 0;   
   
        DECLARE v_id VARCHAR(255); 
        DECLARE d_factor DECIMAL(5,2); 
        DECLARE d_startdate datetime; 
        DECLARE d_enddate datetime; 
        DECLARE cur_factor CURSOR FOR SELECT cid, cstarttime,cendtime,cfactornum from tb_plo_artificialfactor; 
      DECLARE EXIT HANDLER FOR NOT FOUND CLOSE cur_factor;
        DECLARE CONTINUE HANDLER FOR SQLEXCEPTION SET t_error=1;
        
    START TRANSACTION;
        
        open cur_factor;
        
        REPEAT  FETCH cur_factor into v_id, d_startdate,d_enddate,d_factor;
        
        INSERT INTO `efquantitativetrading`.`tb_plo_factorsignal`(`cid`, `cuorgcode`, `ccreatorid`, `ccreatetime`, `clastupdateuserid`, `clastupdatetime`, `cartificialfactorid`, `csignaldatetime`, `cfactornum`)
    VALUES (replace(uuid(), -, ‘‘), NULL, NULL, NULL, NULL, NULL, v_id, d_startdate, d_factor);

        INSERT INTO `efquantitativetrading`.`tb_plo_factorsignal`(`cid`, `cuorgcode`, `ccreatorid`, `ccreatetime`, `clastupdateuserid`, `clastupdatetime`, `cartificialfactorid`, `csignaldatetime`, `cfactornum`)
    VALUES (replace(uuid(), -, ‘‘), NULL, NULL, NULL, NULL, NULL, v_id, date_add(d_enddate, interval 1 second) , 0);
    
                        
                    
   UNTIL 0 END REPEAT;
     
        
         
        close  cur_factor;
     
  IF t_error = 1 THEN    
            ROLLBACK;    
        ELSE    
            COMMIT;    
        END IF;    
   select t_error;   

END
;;
DELIMITER ;

 

mysql 光标使用

上一篇:批量修改文件名(Python)


下一篇:MYSQL出现:Out of sort memory, consider increasing server sort buffer size; nested exception is java.sql.SQLException: Out of sort memory, consider increasing server sort buffer size