计算数据库中数据总和的存储过程

来源：互联网发布：淘宝线下超市编辑：程序博客网时间：2024/06/14 19:22

需改进的地方: 年, 月, 需要改进

DROP PROCEDURE IF EXISTS `pSum_TABLE_ROWS`;
CREATE DEFINER=`root`@`%` PROCEDURE `pSum_TABLE_ROWS`(
    IN inDBName varchar(40),
    IN inTABLEPrefix varchar(40),
    IN inYear INT,
    IN inMonth INT,
    IN inDayStart INT,
    IN inHourStart INT,
    IN inDayEnd INT,
    IN inHourEnd INT
)
BEGIN
    DECLARE v_tableName varchar(40);
    DECLARE vFLAG INT DEFAULT 0;
    DECLARE vSameDayFLAG INT DEFAULT 0;

    DECLARE vDay_start INT DEFAULT 0;
    DECLARE vDay_end INT DEFAULT 0;
    DECLARE vHour_start INT DEFAULT 0;
    DECLARE vHour_end INT DEFAULT 0;

    DECLARE szMonth char(10);
    DECLARE szDay char(10);
    DECLARE szHour char(10);

DECLARE sum_table_rows INT DEFAULT 0;

SET sum_table_rows = 0;

    IF ( inDayStart > inDayEnd) THEN
       select 'Day errror';
       SET vFLAG = -1;
    ELSE
        IF ( inDayStart = inDayEnd AND inHourStart >= inHourEnd) THEN
            select 'hour errror';
            SET vFLAG = -1;
        END IF;
    END IF;

    IF (vFLAG = -1) THEN
       SET sum_table_rows = -1;
    ELSE
       SET vDay_start = inDayStart;
       SET vDay_end = inDayEnd;
       SET vHour_start = inHourStart;
       SET vHour_end = inHourEnd;

       WHILE (vDay_start <= vDay_end) DO    # the same month
          #MONTH
          IF (inMonth < 10) THEN
             SET szMonth = CONCAT("0",inMonth);
          ELSE
             SET szMonth = CONCAT(inMonth);
          END IF;
          #DAY
          IF (vDay_start < 10) THEN
             SET szDay = CONCAT("0",vDay_start);
          ELSE
             SET szDay = CONCAT(vDay_start);
          END IF;
          # HOUR
          IF (vHour_start < 10) THEN
             SET szHour = CONCAT("0",vHour_start);
          ELSE
             SET szHour = CONCAT(vHour_start);
          END IF;

SET v_tableName = CONCAT(inTABLEPrefix, inYear,szMonth, szDay, szHour, "0000");

          SET @sql = CONCAT("SELECT SUM( TABLE_ROWS ) INTO @rowcount
               FROM information_schema.`tables` WHERE TABLE_SCHEMA='", inDBName,
               "' AND TABLE_NAME = '", v_tableName, "'");

          PREPARE s0 FROM @sql;
           EXECUTE s0;
           SET sum_table_rows = sum_table_rows + @rowcount;
           DEALLOCATE PREPARE s0;

          ###
          IF (vDay_start < vDay_end) THEN

             IF ( vHour_start < 23) THEN
                SET vHour_start = vHour_start + 1;
             ELSE
                SET vHour_start = 0;
                SET vDay_start = vDay_start + 1; # Day add 1
             END IF;

ELSE

             IF ( vHour_start < vHour_end) THEN
                SET vHour_start = vHour_start + 1;
             ELSE
                SET vDay_start = vDay_start + 1;
             END IF;

          END IF;

       END WHILE;

    END IF;

select sum_table_rows;
END;