mysq 存储过程 插入测试数据

CREATE DEFINER = 'root'@'%' PROCEDURE `proc_InsertTestData`(
        IN iPatientIdBegin INTEGER(11),
        IN iPersonsCount INTEGER(11),
        IN dStartDate DATE,
        IN dEndDate DATE,
        IN iRecordCountPerDay INTEGER(11)
    )
    NOT DETERMINISTIC
    CONTAINS SQL
    SQL SECURITY DEFINER
    COMMENT ''
BEGIN
declare tempPatientId int;
declare iPatientIndex int;
declare tempDateTimeDesc varchar(100);

declare tempDate date;
declare tempIndex int;

declare tempFiledATime DateTime;
declare tempSymptoms varchar(200);
declare tempIncentives varchar(200);
declare tempDrugs varchar(200);

declare tempGatherTime DateTime;
declare tempFiledB Int;
declare tempFiledC float(9,2);
declare tempFTimeFlag varchar(5);
declare tempFAVailFlag tinyint;

declare tempTime1 varchar(6);
declare tempTime2 varchar(200);

set tempDateTimeDesc = '03:00|07:00|13:00|17:00|19:00';
set tempTime2 = concat(tempDateTimeDesc , '|');
set tempSymptoms = '1|2';
set tempIncentives = '1|2';
set tempDrugs = '4|3|Ƭ;5|5|ml';

/* 0 from first to last patientId */
set iPatientIndex = 1;
while iPatientIndex <= iPersonsCount do
    set tempPatientId = iPatientIdBegin + iPatientIndex;
    set iPatientIndex = iPatientIndex + 1;
    insert into gathertimesFiledB (PatientID,DateTimeDesc , RecordCreateTime)
    values(tempPatientId , tempDateTimeDesc ,dStartDate);
end while; /* 0 from first to last patientId */

/* 1 from first to last date */
set tempDate = dStartDate;
while tempDate <= dEndDate do
    
    /* 1.1 from first to last patientId */
    set iPatientIndex = 1;
    while iPatientIndex <= iPersonsCount do
        set tempPatientId = iPatientIdBegin + iPatientIndex;
        set iPatientIndex = iPatientIndex + 1;
        
        /* 1.2 record a day */
        set tempIndex = 1;
        while tempIndex <= iRecordCountPerDay do
            
            /* Filed */
            set tempFiledATime = cast(concat(tempDate,' ',tempIndex,':05:00') as datetime);
            insert into tableA (PatientID , FiledATime ,Symptoms
                   ,SymptomsOther , Incentives , IncentivesOther , Drugs)
             values(tempPatientId ,tempFiledATime ,tempSymptoms
                  ,'',tempIncentives, '' ,tempDrugs);
                
            /* FiledB unvalid */
            set tempFTimeFlag = concat(lpad(tempIndex,2,'0'),':00');
            
            if FIND_IN_SET(tempFTimeFlag,tempDateTimeDesc) <= 0 then             
                set tempGatherTime = cast(concat(tempDate,' ',lpad(tempIndex,2,'0'),':10:00') as datetime);
                set tempFiledB = FLOOR(250 + ( RAND() * 51 ) );
                   set tempFiledC = Round(1 + (RAND() * 2) ,1);            
                set tempFAVailFlag = "0";
                insert into tableB(PatientID , GatherTime , FiledB , FiledC , FTimeFlag , FAVailFlag)
                VALUES(tempPatientId, tempGatherTime , tempFiledB , tempFiledC , tempFTimeFlag , tempFAVailFlag);
            end if;
            
            set tempIndex = tempIndex + 1;           
            
        end while; /* 1.2 record a day */
        
        /* 1.3 FiledB valid */
        while LENGTH(tempTime2) > 0 do            
            set tempTime1 = substring(tempTime2,1,5);
            set tempTime2 = substring(tempTime2,7);  
            set tempFiledB = FLOOR(250 + ( RAND() * 51 ) );
            set tempFiledC = Round(1 + (RAND() * 2) ,1);  
            set tempGatherTime = cast(concat(tempDate,' ',tempTime1,':00') as datetime);
            set tempFTimeFlag = tempTime1;
            set tempFAVailFlag = "1";
            insert into tableB(PatientID , GatherTime , FiledB , FiledC , FTimeFlag , FAVailFlag)
            VALUES(tempPatientId, tempGatherTime , tempFiledB , tempFiledC , tempFTimeFlag , tempFAVailFlag);
        end while; /* 1.3 FiledB valid */
        
    end while; /* 1.1 from first to last patientId */

    set tempDate = date_add(tempDate, interval 1 day);
end while; /* 1 from first to last date */

END;