mysql的存储过程

最新推荐文章于 2025-08-08 14:10:14 发布

原创最新推荐文章于 2025-08-08 14:10:14 发布 · 599 阅读

0 ·

CC 4.0 BY-SA版权

文章标签：

#存储 #mysql #integer #date #timezone #insert

数据库同时被 2 个专栏收录

2 篇文章

订阅专栏

mysql

1 篇文章

订阅专栏

CREATE DEFINER = 'root'@'localhost' PROCEDURE `usp_CreateBioFeedBackDataTest`(
        IN PatientIDMin INTEGER(11),
        IN PatientIDMax INTEGER(11),
        IN TestDateMin DATETIME,
        IN TestDateMax DATETIME,
        IN SDIDFirst INTEGER(11)
    )
    NOT DETERMINISTIC
    CONTAINS SQL
    SQL SECURITY DEFINER
    COMMENT ''
BEGIN
    declare pID int;
	declare testDate DateTime;
    declare preDate DateTime;
    declare i int;
    declare k int;
    declare strDate varchar(30);
    declare strPID varchar(10);
    set testDate=TestDateMin;
    set pID=PatientIDMin;
    set i=SDIDFirst;
    set k=0;
    set preDate=TestDateMin;
    
    while testDate<=TestDateMax do
    	set pID=PatientIDMin;
    	while pID<=PatientIDMax do
        	set strDate=DATE_FORMAT( testDate,   '%Y%m%d%H%i%s');
            set strPID=cast(pID as char(10));
        	INSERT INTO `rmsms_signaldata` (SDID,`PatientID`, `TestDate`, `SignalType`, `FilePath`, `IsAnalysed`, `PPGFilePath`, `HR`, `FreqFilePath`, `MeanHR`, `MaxHR`, `MinHR`, `BioTrainTime`, `BreathIn`, `Halt`, `BreathOut`, `Hold`, `IsValid`, `IsDownload`, `TimeZone`) VALUES 
  				(i, pID, testDate, 1, CONCAT('\\rmsms\\StressDir\\',strPID,'\\',strDate,'.dat'), 1, 
          		  CONCAT('\\rmsms\\StressDir\\',strPID,'\\',strDate,'PPG.txt'), '69,80,73,75,80,84,84,88,90,95,90,86,90,86,86,78,82,78,84,84,80,82,78,84,84,80,80,78,82,78,82,78,82,80,86,84,88,92,88,90,92,88,88,86,92,88,86,77,82,80,80,78,78,84,86,86,82,84,82,82,86,82,78,78,86,84,80,86,92,95,88,86,86,90,86,82,77,75,80,40,84,90,90,86,86,80,78,77,75,71,71,73,77,78,78,84,78,82,80,78,88,84,92,95,90,88,92,90,95,88,95,100,95,97,88,82,77,77,78,77,78,82,86,88,86,82,78,77,82,78,75,72,71,78,78,73,75,75,77,78,82,82,72,78,78,82,80,82,84,80,92,88,86,78,77,78,78,82,88,86,88,90,75,72,73,69,71,68,75,75,80,84,80,75,77,80,75,75,82,77,77,80,78,82,88,88,86,82,80,84,82,80,82,86,82,78,78,86,82,77,75,75,75,75,75,77,82,82,78,78,84,84,82,78,82,82,82,82,80,86,86,82,77,75,73,75,78,75,75,77,82,77,75,73,75,82,78,78,78,78,84,78,77,73,77,82,78,75,75,78,82,80,73,71,75,75,73,75,84,86,80,82,78,84,86,82,84,86,80,82,86,84,78,88,86,86,84,88,97,90,86,86,90,90,95,92,86,86,86,88,84,84,82,86,86,82,80,75,80,90,92,86,67,73,77,86,86,95,88,78,80,80,84,84,84,77,77,82,80,80,84,82,84,72,78,82,80,84,82,78,77,78,90,69,75,82,82,80,82,71,73,80,78,84,78,82,78,82,78,82,77,82,77,80,86,82,82,77,77,84,80,82,86,82,88,80,75,86,86,73,77,86,86,84,84,84,77,78,80,73,72,75,84,86,86,86,82,75,75,78,73,73,77,82,78,82,82,86,84,82,84,86,82,80,77,78,82,82,77,84', 
                  CONCAT('\\rmsms\\StressDir\\',strPID,'\\',strDate,'Freq.txt'),
           		  81, 100, 40, 5, 4, 1, 5, 1, 0, 0, '+0800');
        	INSERT INTO `rmsms_stressresult` (`SDID`,  `StressVal`,  `RenitentAbility`,  `ANSBalance`,  `ANSActivity`,  `SDNN`,  `RMSSD`,  `LFNorm`,  `HFNorm`,  `Ratio`,  `NNAVG`) 
				VALUE (i,80,80,90,70,80,85,52,48,1.23,85);
                
        	set i=i+1;
            set pID=pID+1;
        end while;
        set k=k+1;
        if(k<3) THEN
        	set testDate=DATE_ADD(testDate,INTERVAL 2 HOUR); 
        ELSE
        	set preDate=DATE_ADD(preDate,INTERVAL 1 DAY); 
            set testDate=preDate;
        END IF;
     end while;
END;

mysql的存储过程，其中的几个函数的应用：

set strDate=DATE_FORMAT( testDate,   '%Y%m%d%H%i%s'); //格式化日期，将日期转化成字符串
DATE_ADD(testDate,INTERVAL 2 HOUR);   //日期加某一段时间

set strPID=cast(pID as char(10)); //将整型转化成字符串

CONCAT('\\rmsms\\StressDir\\',strPID,'\\',strDate,'.dat')  //字符串拼接