oracle 存储过程 示例

1.情景展示

　　对VIRTUAL_QRCODELOG表的静态二维码，动态二维码，二维码总量按时间进行分组统计

　　表结构展示

 

　　存储过程入参展示

2.sql分析

 要想完成这个sql需要将行转成列

　　第一步：

　　第二步：借助UNION ALL

--按日期统计动态二维码
SELECT TO_CHAR(QR_CREATETIME, 'yyyy-MM-dd') TIME,
       0 STATIC_CODE,
       COUNT(1) DYNAMIC_CODE
  FROM VIRTUAL_QRCODELOG
 WHERE QR_CODE_TYPE = 0
 GROUP BY TO_CHAR(QR_CREATETIME, 'yyyy-MM-dd')
UNION ALL
--按日期统计静态二维码
SELECT TO_CHAR(QR_CREATETIME, 'yyyy-MM-dd') TIME,
       COUNT(1) STATIC_CODE,
       0 DYNAMIC_CODE
  FROM VIRTUAL_QRCODELOG
 WHERE QR_CODE_TYPE = 1
 GROUP BY TO_CHAR(QR_CREATETIME, 'yyyy-MM-dd')
 ORDER BY TIME

　　

　　第三步：按日期进行分组求和

SELECT TIME, --时间
       SUM(STATIC_CODE) STATIC_CODE, --静态二维码
       SUM(DYNAMIC_CODE) DYNAMIC_CODE, --动态二维码
       SUM(STATIC_CODE) + SUM(DYNAMIC_CODE) SCAN_CODE --扫码
  FROM (
        --按日期统计动态二维码
        SELECT TO_CHAR(QR_CREATETIME, 'yyyy-MM-dd') TIME,
                0 STATIC_CODE,
                COUNT(1) DYNAMIC_CODE
          FROM VIRTUAL_QRCODELOG
         WHERE QR_CODE_TYPE = 0
         GROUP BY TO_CHAR(QR_CREATETIME, 'yyyy-MM-dd')
        UNION ALL
        --按日期统计静态二维码
        SELECT TO_CHAR(QR_CREATETIME, 'yyyy-MM-dd') TIME,
                COUNT(1) STATIC_CODE,
                0 DYNAMIC_CODE
          FROM VIRTUAL_QRCODELOG
         WHERE QR_CODE_TYPE = 1
         GROUP BY TO_CHAR(QR_CREATETIME, 'yyyy-MM-dd'))
 GROUP BY TIME
 ORDER BY TIME;

3.存储过程展示

CREATE OR REPLACE PROCEDURE CARD_COUNT_QRCODE(V_START    IN VARCHAR2, --开始时间
                                              V_END      IN VARCHAR2, --结束时间
                                              OUT_CURSOR OUT SYS_REFCURSOR) IS --系统游标
  /**
  * 内容：电子卡二维码应用情况统计
  * 日期：2018/08/20
  * 作者：MARYDON
  * 版本：1.0
  */
  V_START_DATE VARCHAR2(19);
  V_END_DATE   VARCHAR2(19);
  V_DATEFORMAT VARCHAR2(10);--按日期进行分组格式
  --开始时间、结束时间拼接
  BEGIN
  IF (LENGTH(V_START) = 4) THEN
    --yyyy
    V_START_DATE := V_START || '-01-01 00:00:00';
    V_END_DATE   := V_END || '-12-31 00:00:00';
    V_DATEFORMAT := 'YYYY';
  ELSIF (LENGTH(V_START) = 7) THEN
    --yyyy-mm
    V_START_DATE := V_START || '-01 00:00:00';
    SELECT TO_CHAR(LAST_DAY(TO_DATE(V_END, 'YYYY-MM')), 'YYYY-MM-DD') ||
           ' 00:00:00'
      INTO V_END_DATE
      FROM DUAL;
    V_DATEFORMAT := 'YYYY-MM';  
  ELSE
    --yyyy-mm-dd
    V_START_DATE := V_START || ' 00:00:00';
    V_END_DATE   := V_END || ' 00:00:00';
    V_DATEFORMAT := 'YYYY-MM-DD';
  END IF;

  --业务执行
  OPEN OUT_CURSOR FOR
    SELECT TIME,--时间
           SUM(STATIC_CODE) STATIC_CODE, --静态二维码
           SUM(DYNAMIC_CODE) DYNAMIC_CODE, --动态二维码
           SUM(STATIC_CODE) + SUM(DYNAMIC_CODE) SCAN_CODE --扫码
      FROM (
            --按日期统计动态二维码
            SELECT TO_CHAR(QR_CREATETIME, V_DATEFORMAT) TIME, 0 STATIC_CODE, COUNT(1) DYNAMIC_CODE
              FROM VIRTUAL_QRCODELOG
             WHERE QR_CODE_TYPE = 0
               AND QR_CREATETIME >=
                   TO_DATE(V_START_DATE, 'YYYY-MM-DD HH24:MI:SS')
               AND QR_CREATETIME <
                   (TO_DATE(V_END_DATE, 'YYYY-MM-DD HH24:MI:SS') + 1)
             GROUP BY TO_CHAR(QR_CREATETIME, V_DATEFORMAT)
            UNION ALL
            --按日期统计静态二维码
            SELECT TO_CHAR(QR_CREATETIME, V_DATEFORMAT) TIME, COUNT(1) STATIC_CODE, 0 DYNAMIC_CODE
              FROM VIRTUAL_QRCODELOG
             WHERE QR_CODE_TYPE = 1
               AND QR_CREATETIME >=
                   TO_DATE(V_START_DATE, 'YYYY-MM-DD HH24:MI:SS')
               AND QR_CREATETIME <
                   (TO_DATE(V_END_DATE, 'YYYY-MM-DD HH24:MI:SS') + 1)
             GROUP BY TO_CHAR(QR_CREATETIME, V_DATEFORMAT)
             )
        GROUP BY TIME
        ORDER BY TIME;
END CARD_COUNT_QRCODE;　

2023年3月10日10:05:33

4.扩展

  非空判断并赋值

  方式一：

--V_DATE赋值
  IF IN_DATE IS NULL THEN
    SELECT TO_CHAR(SYSDATE, 'YYYY-MM-DD') INTO V_DATE FROM DUAL;
  ELSE
    V_DATE := IN_DATE;
  END IF;

说明：

IN_DATE IS NULL表示的是：IN_DATE = NULL || IN_DATE = ''

  方式二：

--V_DATE赋值
  SELECT NVL(IN_DATE, TO_CHAR(SYSDATE, 'YYYY-MM-DD'))
    INTO V_DATE
    FROM DUAL;

 

posted @ 2018-09-04 12:56  Marydon  阅读(1727)  评论(0)  编辑  收藏  举报