mysql存储过程示例

DROP PROCEDURE if exists p_report_xxsjtjb$$
CREATE PROCEDURE p_report_xxsjtjb(IN  pi_branchid INT(11), -- 分店id
                                  IN  pi_sb       SMALLINT, -- 市别，0:午市；1:晚市；-1:全天
                                  IN  pi_ksrq     DATETIME, -- 开始日期，
                                  IN  pi_jsrq     DATETIME, -- 结束日期
                                  IN  pi_cxlx     SMALLINT, -- 查询类型 1:应收金额 2:实收金额 3:结算人数 4:开台数
                                  IN  pi_qy       VARCHAR(50), -- 餐台所属区域 -1:全部 非-1:区域id
                                  OUT po_errmsg   VARCHAR(100)
                                  -- 错误信息
)
SQL SECURITY INVOKER
COMMENT '详细数据统计表'
label_main:
BEGIN
  -- 返回字段说明如下(2个固定字段+不固定字段)：
  -- 区域 桌号 时间列1 时间列2 时间列3.....
  --
  -- 返回数据举例（返回值包含多条数据）：
  -- 大堂 1 22 33 44


  DECLARE v_date_start    DATETIME;
  DECLARE v_date_end      DATETIME;
  DECLARE v_date_interval DATETIME; #时间间隔  
  DECLARE v_loop_num      INT DEFAULT 0; #根据开始结束时间和显示类型，来设置循环次数
  DECLARE v_table_index   INT;
  DECLARE v_table_id      VARCHAR(50);
  DECLARE v_sql           VARCHAR(5000) DEFAULT '';

  -- 异常处理模块，出现异常返回null
  DECLARE EXIT HANDLER FOR SQLEXCEPTION
  BEGIN
    SELECT NULL;
    GET DIAGNOSTICS CONDITION 1 po_errmsg = MESSAGE_TEXT;
  END;

  IF pi_branchid IS NULL THEN
    SELECT NULL;
    SET po_errmsg = '分店ID输入不能为空';
    LEAVE label_main;
  END IF;


  SET @@max_heap_table_size = 1024 * 1024 * 300;
  SET @@tmp_table_size = 1024 * 1024 * 300;

  #设置循环次数,处理开始结算时间
  SET v_date_start = str_to_date(concat(date_format(pi_ksrq, '%Y-%m-%d'), '00:00:00'), '%Y-%m-%d %H:%i:%s');
  SET v_date_end = str_to_date(concat(date_format(pi_jsrq, '%Y-%m-%d'), '23:59:59'), '%Y-%m-%d %H:%i:%s');
  SET v_date_interval = date_sub(date_add(v_date_start, INTERVAL 1 DAY), INTERVAL 1 SECOND);
  SET v_loop_num = timestampdiff(DAY, v_date_start, v_date_end) + 1;

  #创建订单临时内存表
  DROP TEMPORARY TABLE IF EXISTS t_temp_order;
  CREATE TEMPORARY TABLE t_temp_order
  (
    orderid VARCHAR(50),
    begintime DATETIME,
    tableid VARCHAR(50),
    womanNum INT,
    childNum INT,
    mannum INT
  ) ENGINE = MEMORY DEFAULT CHARSET = utf8;

  IF pi_sb > -1 THEN
    INSERT INTO t_temp_order
    SELECT orderid
         , begintime
         , currenttableid
         , womanNum
         , childNum
         , mannum
    FROM
      t_order USE INDEX (IX_t_order_begintime)
    WHERE
      branchid = pi_branchid
      AND begintime BETWEEN v_date_start AND v_date_end -- 需要创建索引IX_t_order_begintime  
      AND shiftid = pi_sb
      AND orderstatus = 3;
  ELSE
    INSERT INTO t_temp_order
    SELECT orderid
         , begintime
         , currenttableid
         , womanNum
         , childNum
         , mannum
    FROM
      t_order USE INDEX (IX_t_order_begintime)
    WHERE
      branchid = pi_branchid
      AND begintime BETWEEN v_date_start AND v_date_end -- 需要创建索引IX_t_order_begintime  
      AND orderstatus = 3;
  END IF;

  #为订单内存表创建索引
  CREATE UNIQUE INDEX ix_t_temp_order_orderid ON t_temp_order (orderid);


  IF pi_cxlx = 1 THEN
    DROP TEMPORARY TABLE IF EXISTS t_temp_order_detail;
    CREATE TEMPORARY TABLE t_temp_order_detail
    (
      orderid VARCHAR(50),
      dishnum DOUBLE(13, 2),
      orignalprice DOUBLE(13, 2),
      tableid VARCHAR(50),
      begintime DATETIME,
      childdishtype TINYINT,
      primarykey VARCHAR(50),
      superkey VARCHAR(50)
    ) ENGINE = MEMORY DEFAULT CHARSET = utf8 MAX_ROWS = 1000000;

    # 向临时订单详情内存表中放值
    INSERT INTO t_temp_order_detail
    SELECT b.orderid
         , b.dishnum
         , b.orignalprice
         , a.tableid
         , a.begintime
         , b.childdishtype
         , b.primarykey
         , b.superkey
    FROM
      t_temp_order a, t_order_detail b
    WHERE
      a.orderid = b.orderid
      AND b.orignalprice > 0;

    # 删除套餐明细
    DROP TEMPORARY TABLE IF EXISTS t_temp_keys;
    CREATE TEMPORARY TABLE t_temp_keys
    (
      primarykey VARCHAR(50)
    ) ENGINE = MEMORY DEFAULT CHARSET = utf8 MAX_ROWS = 1000000;
    INSERT INTO t_temp_keys
    SELECT primarykey
    FROM
      t_temp_order_detail
    WHERE
      childdishtype = 2;

    DELETE
    FROM
      t_temp_order_detail
    USING
      t_temp_order_detail, t_temp_keys
    WHERE
      t_temp_order_detail.superkey = t_temp_keys.primarykey
      AND t_temp_order_detail.primarykey != t_temp_keys.primarykey;

    CREATE INDEX ix_t_temp_order_detail_begintime ON t_temp_order_detail (begintime);

  #创建结算明细内存表
  ELSEIF pi_cxlx = 2 THEN
    DROP TEMPORARY TABLE IF EXISTS t_temp_settlement_detail;
    CREATE TEMPORARY TABLE t_temp_settlement_detail
    (
      orderid VARCHAR(50),
      payway INT,
      payamount DOUBLE(13, 2),
      begintime DATETIME,
      tableid VARCHAR(50)
    ) ENGINE = MEMORY DEFAULT CHARSET = utf8 MAX_ROWS = 1000000;

    #生产临时结算明细表数据
    INSERT INTO t_temp_settlement_detail
    SELECT b.orderid
         , b.payway
         , b.payamount
         , a.begintime
         , a.tableid
    FROM
      t_temp_order a, t_settlement_detail b
    WHERE
      a.orderid = b.orderid
      AND b.payway IN (0, 1, 5, 8, 13, 17, 18)   -- shangwenchao 2015/12/21 22:46:49 增加结算方式13, 17 ,18
      AND b.payamount > 0;

    CREATE INDEX ix_t_temp_settlement_detail_begintime ON t_temp_settlement_detail (begintime);

    #创建会员消费内存表
    DROP TEMPORARY TABLE IF EXISTS t_temp_order_member;
    CREATE TEMPORARY TABLE t_temp_order_member
    (
      orderid VARCHAR(50),
      Inflated DOUBLE(13, 2),
      begintime DATETIME,
      tableid VARCHAR(50)
    ) ENGINE = MEMORY DEFAULT CHARSET = utf8 MAX_ROWS = 1000000;

    #生成临时会员结算数据  
    INSERT INTO t_temp_order_member
    SELECT b.orderid
         , b.Inflated
         , a.begintime
         , a.tableid
    FROM
      t_temp_order a, t_order_member b
    WHERE
      a.orderid = b.orderid;
    CREATE INDEX ix_t_temp_order_member_begintime ON t_temp_order_member (begintime);
  END IF;


  #筛选桌子
  DROP TEMPORARY TABLE IF EXISTS t_temp_table;
  CREATE TEMPORARY TABLE t_temp_table
  (
    areaname VARCHAR(10),
    tableid VARCHAR(50),
    tableNo VARCHAR(50)
  ) ENGINE = MEMORY DEFAULT CHARSET = utf8;

  IF pi_qy = '-1' THEN
    INSERT INTO t_temp_table (areaname, tableid, tableNo)
    SELECT b.areaname
         , a.tableid
         , a.tableNo
    FROM
      t_table a, t_tablearea b
    WHERE
      a.areaid = b.areaid
      AND b.branchid = pi_branchid;
  ELSE
    INSERT INTO t_temp_table (areaname, tableid, tableNo)
    SELECT b.areaname
         , a.tableid
         , a.tableNo
    FROM
      t_table a, t_tablearea b
    WHERE
      a.areaid = b.areaid
      AND b.areaid = pi_qy
      AND b.branchid = pi_branchid;
  END IF;


  #创建结果集
  DROP TEMPORARY TABLE IF EXISTS t_temp_res;
  CREATE TEMPORARY TABLE t_temp_res
  (
    areaname VARCHAR(10),
    tableid VARCHAR(50),
    stime VARCHAR(10),
    svalue DOUBLE(13, 2)
  ) ENGINE = MEMORY DEFAULT CHARSET = utf8;


  #创建虚增结果集
  DROP TEMPORARY TABLE IF EXISTS t_temp_inflated;
  CREATE TEMPORARY TABLE t_temp_inflated
  (
    tableid VARCHAR(50),
    stime VARCHAR(10),
    svalue DOUBLE(13, 2)
  ) ENGINE = MEMORY DEFAULT CHARSET = utf8;

  #####################################遍历桌子及统计天数，生成结果数据##########################################
  WHILE v_loop_num > 0
  DO

    #统计应收
    IF pi_cxlx = 1 THEN
      INSERT INTO t_temp_res (tableid, stime, svalue)
      SELECT tableid
           , date_format(v_date_start, '%Y/%m/%d')
           , sum(dishnum * orignalprice)
      FROM
        t_temp_order_detail
      WHERE
        begintime BETWEEN v_date_start AND v_date_interval
      GROUP BY
        tableid;

    #统计实收
    ELSEIF pi_cxlx = 2 THEN
      INSERT INTO t_temp_res (tableid, stime, svalue)
      SELECT tableid
           , date_format(v_date_start, '%Y/%m/%d')
           , ifnull(sum(payamount), 0)
      FROM
        t_temp_settlement_detail
      WHERE
        begintime BETWEEN v_date_start AND v_date_interval
      GROUP BY
        tableid;

      INSERT INTO t_temp_inflated
      SELECT tableid
           , date_format(v_date_start, '%Y/%m/%d')
           , ifnull(sum(Inflated), 0)
      FROM
        t_temp_order_member
      WHERE
        begintime BETWEEN v_date_start AND v_date_interval
      GROUP BY
        tableid;

    #INSERT INTO t_temp_res (tableid, stime, svalue) VALUES (v_table_id, date_format(v_date_start, '%Y/%m/%d'), @shishou - @xuzeng);

    #统计结账人数
    ELSEIF pi_cxlx = 3 THEN
      INSERT INTO t_temp_res (tableid, stime, svalue)
      SELECT tableid
           , date_format(v_date_start, '%Y/%m/%d')
           , ifnull(sum(mannum + womanNum + childNum), 0)
      FROM
        t_temp_order
      WHERE
        begintime BETWEEN v_date_start AND v_date_interval
      GROUP BY
        tableid;

    #统计开台数据
    ELSEIF pi_cxlx = 4 THEN
      INSERT INTO t_temp_res (tableid, stime, svalue)
      SELECT tableid
           , date_format(v_date_start, '%Y/%m/%d')
           , count(1)
      FROM
        t_temp_order
      WHERE
        begintime BETWEEN v_date_start AND v_date_interval
      GROUP BY
        tableid;
    #退出程序
    ELSE
      SELECT NULL;
      SET po_errmsg = '显示类型输入有误 1:应收金额 2:实收金额 3:结算人数 4:开台数';
      LEAVE label_main;
    END IF;

    #动态拼装sql，为后面的返回数据做准备
    SET @date_start = date_format(v_date_start, '%Y/%m/%d');
    SET v_sql = concat(v_sql, ',max(case stime when \'', @date_start, '\' then svalue else 0 end) \'', @date_start, '\'');

    SET v_date_start = date_add(v_date_start, INTERVAL 1 DAY);
    SET v_date_interval = date_add(v_date_interval, INTERVAL 1 DAY);
    SET v_loop_num = v_loop_num - 1;
  END WHILE;


  #删除不需要统计的桌号
  DELETE t_temp_res
  FROM
    t_temp_res
  LEFT JOIN t_temp_table
  ON t_temp_res.tableid = t_temp_table.tableid
  WHERE
    t_temp_table.tableid IS NULL;

  #更新实收数据
  UPDATE t_temp_res a, t_temp_inflated b
  SET
    a.svalue = a.svalue - b.svalue
  WHERE
    a.tableid = b.tableid
    AND a.stime = b.stime;

  #更新桌号及区域名称
  UPDATE t_temp_res a, t_temp_table b
  SET
    a.areaname = b.areaname, a.tableid = b.tableNo
  WHERE
    a.tableid = b.tableid;

  #返回数据
  SET v_sql = concat('SELECT areaname, tableid', v_sql);
  SET v_sql = concat(v_sql, ' FROM t_temp_res GROUP BY areaname, tableid order by tableid-0,areaname');
  SET @sql_xxsj = v_sql;
  PREPARE s1 FROM @sql_xxsj;
  EXECUTE s1;
  DEALLOCATE PREPARE s1;

#清空内存表
--   DROP TEMPORARY TABLE IF EXISTS t_temp_order;
--   DROP TEMPORARY TABLE IF EXISTS t_temp_order_detail;
--   DROP TEMPORARY TABLE IF EXISTS t_temp_settlement_detail;
--   DROP TEMPORARY TABLE IF EXISTS t_temp_order_member;
--   DROP TEMPORARY TABLE IF EXISTS t_temp_table;
--   DROP TEMPORARY TABLE IF EXISTS t_temp_res;
--   DROP TEMPORARY TABLE IF EXISTS t_temp_inflated;
--   DROP TEMPORARY TABLE IF EXISTS t_temp_keys;
END
$$