mysql 记录一次实际业务中使用到存储过程、游标

最新推荐文章于 2024-04-16 15:55:03 发布

百世经纶_一页书_

最新推荐文章于 2024-04-16 15:55:03 发布

阅读量557

点赞数

CC 4.0 BY-SA版权

文章标签： mysql 存储过程游标

本文链接：https://blog.youkuaiyun.com/BaiSJL/article/details/70243989

本文介绍了在实际业务中如何利用MySQL的存储过程和游标，针对一张包含Time、ExchangeID、InstrumentID等字段的表，当PreClosePrice为空时，查询前一天的ClosePrice并用于更新当天的PreClosePrice，从而满足业务需求。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

需求

有一张表，字段有
Time,ExchangeID,InstrumentID,ClosePrice,PreClosePrice……
其中Time,ExchangeID,InstrumentID作为复合主键；
需要根据前一天的ClosePrice更新当天PreClosePrice；
这里有三个类似PreClosePrice的字段需要更新；

思路

使用存储过程完成，选出PreClosePrice字段为空的记录，使用游标遍历每一条记录，查询该记录对应的前一天记录中的ClosePrice的值x，再更新当前记录的PreClosePrice值为x;

实现


DROP PROCEDURE IF EXISTS update_KlineDayTab; 
CREATE PROCEDURE update_KlineDayTab()
    BEGIN
        DECLARE n INT DEFAULT 0;
        DECLARE total INT;
        DECLARE done INT DEFAULT FALSE;
        DECLARE vTime TIMESTAMP;
        DECLARE vExchangeID VARCHAR(8);
        DECLARE vInstrumentID VARCHAR(10);
        DECLARE vPreClosePrice DECIMAL;
        DECLARE vPreSettlementPrice DECIMAL;
        DECLARE vPreOpenInterest INT;
        DECLARE tPreClosePrice DECIMAL;
        DECLARE tPreSettlementPrice DECIMAL;
        DECLARE tPreOpenInterest INT;
        DECLARE cur_ CURSOR FOR
            select 
            Time,ExchangeID,InstrumentID,PreClosePrice,PreSettlementPrice,PreOpenInterest 
            from kline_day
            where PreClosePrice IS NULL 
            OR PreSettlementPrice IS NULL 
            OR PreOpenInterest IS NULL;
        DECLARE CONTINUE HANDLER FOR NOT found SET done = TRUE;
        SET vPreSettlementPrice = NULL;
        set vPreOpenInterest = NULL;
        set vPreClosePrice = NULL;
        sel
        OPEN cur_;
            WHILE n < total DO
                FETCH next FROM cur_ INTO vTime,vExchangeID,vInstrumentID,vPreClosePrice,vPreSettlementPrice,vPreOpenInterest;
                IF (vPreClosePrice IS NULL)
                    THEN 
                        SELECT ClosePrice from kline_day 
                        where Time < vTime and ExchangeID = vExchangeID and InstrumentID = vInstrumentID
                        ORDER BY Time DESC LIMIT 0,1
                        INTO tPreClosePrice;
                        IF(tPreClosePrice IS NOT NULL) THEN
                            UPDATE kline_day SET PreClosePrice = tPreClosePrice 
                            where Time = vTime and ExchangeID = vExchangeID and InstrumentID = vInstrumentID;
                        END IF;
                END IF;
                IF (vPreSettlementPrice IS NULL)
                    THEN 
                        SELECT SettlementPrice from kline_day 
                        where Time < vTime and ExchangeID = vExchangeID and InstrumentID = vInstrumentID
                        ORDER BY Time DESC LIMIT 0,1
                        INTO tPreSettlementPrice;
                        IF(tPreSettlementPrice IS NOT NULL) THEN
                            UPDATE kline_day SET PreSettlementPrice = tPreSettlementPrice 
                            where Time = vTime and ExchangeID = vExchangeID and InstrumentID = vInstrumentID;
                        END IF;
                END IF;
                IF (vPreOpenInterest IS NULL)
                 THEN 
                        # select vTime,vExchangeID,vInstrumentID,'vPreOpenInterest is null';
                        SELECT OpenInterest from kline_day 
                        where Time < vTime and ExchangeID = vExchangeID and InstrumentID = vInstrumentID
                        ORDER BY Time DESC LIMIT 0,1
                        INTO tPreOpenInterest;
                        IF(tPreOpenInterest IS NOT NULL) THEN
                            UPDATE kline_day SET PreOpenInterest = tPreOpenInterest 
                            where Time = vTime and ExchangeID = vExchangeID and InstrumentID = vInstrumentID;
                        END IF;
                END IF;
                SET num = num + 1;
            END WHILE;
        CLOSE cur_;
    END

# 调用存储过程
CALL update_KlineDayTab();