oracle 存储过程 示例
CreationTime--2018年9月4日09点49分
Author:Marydon
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;