oracle通过存储过程实现动态行转列

一、 事件经过

最近在做业务的时候，有个需求，是需要将表中的数据按照日期为列，统计当月每天任务完成数量，不能通过代码完成，只能写视图。

数据库表中的数据如下：

姓名	日期
小明	2019-10-02
小王	2019-10-21
小李	2019-10-30
小明	2019-10-22

要求的数据结构如下：

姓名	10月1	10月2	10月3	......	10月31
小明	2	3	6	......	0
小王	2	4	7	......	8
......

二、难点

要实现这个业务，有三点是比较困难的：

1.要查询出部门下所有员工的任务完成情况，没有任务的统计为0， 表中的数据只有完成任务的人员记录，所以通过group by 是不能得到所有人员的记录的

2.需要获得当月全部日期的数据，当天没有数据的也是记为0，但是跟上边的情况类似，某一天如果没有数据，需要自己构造出数据。

3.以日期作为列，每个月的天数是不定的，所以列数也是不确定的。

三、解决思路

1. 解决人员问题

这个没啥说的，既然是要所有人员的记录，那么就以人员表为主表去进行关联业务数据表

2. 解决行转列

通过人员表与业务表去关联，能获取到有记录的数据，但是没有记录的那些日期怎么办？如何动态的加载列？这个问题考虑了很久 。后来想把问题首先进行分解，如果只是统计一天的数据该怎么统计。 我看网上的做法是通过decode 函数外加聚合函数去进行行转列。 伪sql 如下

select name,count(decode(date,'2019-10-30'),1,null) as '2019-10-30' from ***  group by ...

解释一下，date 是表中的日期字段，如果日期字段等于'2019-10-30' 那么就是1，否则是null,再通过count聚合函数进行统计。也就统计出了'2019-10-30' 这一天的数据。

3. 解决全部日期的问题

解决了一天的数据，那么就是要构造出这个月所有日期的列，显然首先要获取到当前月的所有日期，通过查找函数，找到了oracle自带的函数，获取到了。完整的sql如下：

    SELECT 
        TO_CHAR(TRUNC(SYSDATE,'MM')+ROWNUM-1,'yyyy-MM-DD') as local_date 
    FROM dual 
        connect by ROWNUM<=to_char(last_day(SYSDATE),'dd')

得到了结果：

......

4.解决全部日期的行转列

获取到了当月所有日期，那么怎么使用。通过上面的sql一列一列构造每天的日期列显然是不现实的，既然是拼接，那么就可以通过函数或存储过程的循环，动态的从上面的日期数据中获取日期，然后构造counth函数进行拼接啊，想到这里，感觉有点眉目了。

4.游标

思路是有了，但是上面查询出来的日期数据该怎么用，怎样放进loop循环中。我查找oracle 中有没有存储查询结果的东西，最后发现了游标这个东西。 官方解释如下：

游标的使用可以让用户想操作数组一样操作查询出来的数据集，
实际上，它提供了一种从集合性质的结果中提取单挑记录的手段

游标分为好多种，这里就不详细说明了，用法就是定义一个游标，类似定义存储过程中的变量，然后定义一个变量，去接收游标中的数据，游标中存储的是已经查询好的结果集，然后在loop 中去取，每次循环会依次取游标中的数据，相当于迭代器中的next()方法。

--定义游标
CURSOR  cursor_date IS  
        SELECT TO_CHAR(TRUNC(SYSDATE,'MM')+ROWNUM-1,'yyyy-MM-DD') as local_date 
        FROM dual connect by ROWNUM<=to_char(last_day(SYSDATE),'dd') ;
--使用游标  v_data 接收游标中的遍历的元素
......
 for v_data in cursor_date
     LOOP
     ......

离成功只差一步了，就剩拼接sql 了。 定义了存储过程、定义变量、游标、然后就开始赋值。比较坑的是，拼接sql的时候，sql本身是用单引号引起来的，但是有的查询条件也是单引号的 例如 sql:= 'select ...... from student where name = '小明' ' 这时候要使用 || 进行字符串连接，而且 值得注意的是，在拼接sql时，当遇到sql中经常使用的单引号时，解释器会认为出了问题； 需要使用2个单引号''来代替单引号' 也就是说需要写成如下结果： sql:= 'select ...... from student where name = '||'''小明'''||'

调试了几下，大功告成： 完整的sql 如下,主要展示存储过程、游标等的写法和用法

--定义存储过程
CREATE OR REPLACE
PROCEDURE date_event_select
AS
--定义变量，拼接sql
v_sql VARCHAR2(4000);
--定义游标
CURSOR  cursor_date IS  
        SELECT TO_CHAR(TRUNC(SYSDATE,'MM')+ROWNUM-1,'yyyy-MM-DD') as local_date 
        FROM dual connect by ROWNUM<=to_char(last_day(SYSDATE),'dd') ;
-- 存储过程方法体
BEGIN
     v_sql := 'select b.depid, b.deptname,b.areaid as areaid, b.subname';
     --循环体
     for v_data in cursor_date
     --拼接列
     LOOP
       v_sql := v_sql || ',COUNT(DECODE(b.DAY,'''||v_data.local_date||''',1,NULL)) as '||'"' || v_data.local_date || '"';
   END LOOP;
v_sql := v_sql || 'from (
SELECT
        dept.id AS depid,
        dept.DEPARTMENTNAME AS deptname,
        a.local_date AS day,
        dept.SUBCOMPANYID1 as areaid,
        sub.subcompanyname as subname
FROM
        HRMDEPARTMENT dept
    LEFT JOIN (
        SELECT
            evdept.DEPARTMENT_ID AS depid,
            ev.create_date AS local_date 
        FROM
            UF_EVENT_DEPARTMENT evdept
        LEFT JOIN UF_EVENT_REQUEST ev ON ev.requestid = evdept.requestid 
        where ev.CURRENT_NODE_TYPE_ID = 3
    ) a ON a.depid = dept.ID
    LEFT JOIN HRMDEPARTMENTDEFINED deptdef ON deptdef.DEPTID = dept.id 
    LEFT JOIN hrmsubcompany sub on sub.id = dept.SUBCOMPANYID1
WHERE
    deptdef.GRID = '||'''bm'''||'
)    b
GROUP BY  deptname,depid,areaid,subname' ;
 DBMS_OUTPUT.PUT_LINE(v_sql);
--创建视图
 v_sql := 'CREATE OR REPLACE VIEW date_event_view  AS '||  v_sql;
 --立即执行
 EXECUTE IMMEDIATE v_sql;
END;

5. 创建定时任务

上面的存储过程创建了一个视图,存储过程是当时创建的时候执行了一次，也就是时候视图中的数据只是当月的数据，那么下到下个月的时候还得执行以下存储过程，所以就必须通过创建定时任务去定时的执行存储过程。

--创建一个定时任务，每天执行一次
Declare 
  i Integer;
--下面的表达式中 i 是任务编号  第二个参数是存储过程名称，第三个参数是执行时间，第四个参数是间隔时间，间隔时间有的表达式可以自己根据需求构造  
Begin
   dbms_job.submit(i,'DATE_EVENT_SELECT;',Sysdate,'TRUNC(SYSDATE + 1)');
end;
-- 执行
Declare 
  job_num Integer;
Begin
 -- 查找计划号
 Select t.JOB Into job_num From User_Jobs t ;
 -- 运行制定的执行计划
 dbms_job.run(job_num);
end;

6.定时任务常用的定时任务命令

--查询生成的job id
select job,broken,what,interval,t.* from user_jobs t;
​
--删除任务
begin
   dbms_job.remove(任务编号);
end;

三、总结与思考

其实看下来，主要问题是就是获取当月日期的问题以sql 循环拼接的问题。这方面主要还是对于oracle 的一下常用函数不熟悉所致。还有就是遇到问题还是需要冷静分析，当开始面对这个问题的时候，觉得太难实现了，心理有抵触情绪，其实仔细的分析问题，将问题进行分解，一块一块解决，就会柳暗花明。 特此记录一下问题的解决过程。