SQL SERVICE的存储过程与ORACLE的包

字数 14阅读 301

一、SQL SERVICE的存储过程

ALTER PROCEDURE Gene_Task_SearchData
AS
BEGIN
    TRUNCATE TABLE Gene_Task_Search_Result_Tmp;

    INSERT INTO Gene_Task_Search_Result_Tmp
    SELECT l.id "流程实例编号",
           l.LearingTilteC "案件名称",
           hr.NAME "申请人",
           r.distribution_time "申请时间",
           l.id "流程名称",
           hr.CORPNAME "公司名称",
           '' "status",
           '' "formid",
           '' "viewurl"
      FROM [dbo].[LearingResult] r
           inner join [dbo].[E-learing] l on r.LearingId = l.ID
           inner join ckhr.dbo.IF_V_SP_CONTACTS hr on hr.CODE = r.Uid
           inner join dbo.AD_User_Map um on um.userCode = hr.CODE
     WHERE r.isdelete <> 1
       AND (l.ID like (select '%'+processNumber+'%' from dbo.Gene_Task_Search_Para_Tmp p where p.processNumber is not null)
           OR (select COUNT(*) from dbo.Gene_Task_Search_Para_Tmp p where p.processNumber is not null) = 0)
       AND (l.LearingTilteC like (select '%'+casename+'%' from dbo.Gene_Task_Search_Para_Tmp p where p.casename is not null)
           OR (select COUNT(*) from dbo.Gene_Task_Search_Para_Tmp p where p.casename is not null) = 0)
       AND (CONVERT(varchar(100), r.distribution_time, 23) = (select applicationTime from dbo.Gene_Task_Search_Para_Tmp p where p.applicationTime is not null)
           OR (select COUNT(*) from dbo.Gene_Task_Search_Para_Tmp p where p.applicationTime is not null) = 0)
       AND (hr.CODE in (SELECT userCode FROM Gene_Task_Search_Para_Tmp WHERE userCode is not null))
     ;
SELECT * FROM Gene_Task_Search_Result_Tmp;
    SELECT COUNT(*) CT FROM Gene_Task_Search_Result_Tmp;
END;

二、ORACLE的包

CREATE OR REPLACE PACKAGE oa_pkg AS

    /********************************************* 
        
        This package for Ftit OA system
        
        Author: xianyu.ying
        Date: 2017-06-26
    *********************************************/

    /********************************************* 
      每天计算SVN状态数据,并填充到结果表 
      Author: xianyu.ying
      Date: 2017-06-26
    *********************************************/
    PROCEDURE calc_svn_report_by_day;
END oa_pkg;
/
CREATE OR REPLACE PACKAGE BODY oa_pkg AS

    /********************************************* 
      每天计算SVN状态数据,并填充到结果表 
      Author: xianyu.ying
      Date: 2017-06-26
    *********************************************/
    PROCEDURE calc_svn_report_by_day IS
    
        CURSOR rec_data IS
            SELECT *
                  FROM (SELECT oa_pro.i_project_code 项目编号,
                               oa_pro.i_project_name 项目名称,
                               ceil(SYSDATE - to_date(oa_pro.i_start_date, 'yyyymmdd')) 开发时长,
                               oa_pro.user_sale 销售负责人,
                               oa_pro.user_sale_code 销售编号,
                               user_pro 项目负责人,
                               oa_pro.user_charg_code 负责人编号,
                               SUM(svn_rep.filenum) 总文件数量,
                               SUM(svn_rep.codeline) 总代码数量,
                               COUNT(1) 总提交次数,
                               decode((SELECT '是'
                                        FROM t_project_developer
                                       WHERE projectid = oa_pro.i_project_code
                                         AND userid = oa_dev.i_user_code),
                                      NULL,
                                      '否',
                                      '是') 项目预定成员,
                               decode(oa_dev.i_user_name,
                                      NULL,
                                      svn_rep.developer,
                                      oa_dev.i_user_name) 开发成员名称,
                               decode(oa_dev.i_user_code,
                                      NULL,
                                      svn_rep.developer,
                                      oa_dev.i_user_code) 员工编号,
                               decode(svn_day.filenum, NULL, 0, svn_day.filenum) 本日文件数量,
                               decode(svn_week.filenum, NULL, 0, svn_week.filenum) 本周文件数量,
                               decode(svn_month.filenum, NULL, 0, svn_month.filenum) 本月文件数量,
                               decode(svn_year.filenum, NULL, 0, svn_year.filenum) 本年文件数量,
                               decode(svn_day.codeline, NULL, 0, svn_day.codeline) 本日代码行数,
                               decode(svn_week.codeline, NULL, 0, svn_week.codeline) 本周代码行数,
                               decode(svn_month.codeline, NULL, 0, svn_month.codeline) 本月代码行数,
                               decode(svn_year.codeline, NULL, 0, svn_year.codeline) 本年代码行数,
                               decode(svn_day.commitcount, NULL, 0, svn_day.commitcount) 本日提交次数,
                               decode(svn_week.commitcount, NULL, 0, svn_week.commitcount) 本周提交次数,
                               decode(svn_month.commitcount, NULL, 0, svn_month.commitcount) 本月提交次数,
                               decode(svn_year.commitcount, NULL, 0, svn_year.commitcount) 本年提交次数,
                               'http://showsan.com:8081/' || to_char(SYSDATE, 'yyyymmdd') || '/' ||
                               svn_rep.projectname || '/user_' || svn_rep.developer ||
                               '.html' 明细链接
                          FROM t_log_report svn_rep,
                               (SELECT u_d.i_user_code,
                                       u_d.i_user_name,
                                       m2.svn_developer svn_user_code
                                  FROM t_svn_oa_developer_mapping m2, t_users_mst u_d
                                 WHERE m2.oa_developer = u_d.i_user_code) oa_dev,
                               (SELECT t.*,
                                       m.*,
                                       t.i_user_sale      user_sale_code,
                                       t.i_user_charg     user_charg_code,
                                       u_sale.i_user_name user_sale,
                                       u_pro.i_user_name  user_pro
                                  FROM t_project_mst            t,
                                       t_svn_oa_project_mapping m,
                                       t_users_mst              u_sale,
                                       t_users_mst              u_pro
                                 WHERE t.i_project_code = m.oa_project
                                   AND t.i_user_sale = u_sale.i_user_code(+)
                                   AND t.i_user_charg = u_pro.i_user_code) oa_pro,
                               (SELECT projectname,
                                       developer,
                                       SUM(codeline) codeline,
                                       SUM(filenum) filenum,
                                       COUNT(1) commitcount
                                  FROM t_log_report
                                 WHERE to_char(logtime, 'yyyymmdd') =
                                       to_char(SYSDATE - 1, 'yyyymmdd')
                                 GROUP BY projectname, developer) svn_day,
                               (SELECT projectname,
                                       developer,
                                       SUM(codeline) codeline,
                                       SUM(filenum) filenum,
                                       COUNT(1) commitcount
                                  FROM t_log_report
                                 WHERE to_date(to_char(logtime, 'yyyymmdd'), 'yyyymmdd') BETWEEN
                                       trunc(SYSDATE - 1, 'd') + 1 AND
                                       trunc(SYSDATE - 1, 'd') + 1 + 6
                                 GROUP BY projectname, developer) svn_week,
                               (SELECT projectname,
                                       developer,
                                       SUM(codeline) codeline,
                                       SUM(filenum) filenum,
                                       COUNT(1) commitcount
                                  FROM t_log_report
                                 WHERE to_date(to_char(logtime, 'yyyymmdd'), 'yyyymmdd') BETWEEN
                                       trunc(SYSDATE, 'mm') AND last_day(SYSDATE)
                                 GROUP BY projectname, developer) svn_month,
                               (SELECT projectname,
                                       developer,
                                       SUM(codeline) codeline,
                                       SUM(filenum) filenum,
                                       COUNT(1) commitcount
                                  FROM t_log_report
                                 WHERE to_date(to_char(logtime, 'yyyymmdd'), 'yyyymmdd') BETWEEN
                                       trunc(SYSDATE, 'yy') AND
                                       last_day(add_months(trunc(SYSDATE, 'y'), 11))
                                 GROUP BY projectname, developer) svn_year
                         WHERE svn_rep.projectname = oa_pro.svn_project
                           AND svn_rep.developer = oa_dev.svn_user_code(+)
                           AND svn_rep.projectname = svn_day.projectname(+)
                           AND svn_rep.developer = svn_day.developer(+)
                           AND svn_rep.projectname = svn_week.projectname(+)
                           AND svn_rep.developer = svn_week.developer(+)
                           AND svn_rep.projectname = svn_month.projectname(+)
                           AND svn_rep.developer = svn_month.developer(+)
                           AND svn_rep.projectname = svn_year.projectname(+)
                           AND svn_rep.developer = svn_year.developer(+)
                         GROUP BY oa_pro.i_project_code,
                                  oa_pro.i_project_name,
                                  'http://showsan.com:8081/' || to_char(SYSDATE, 'yyyymmdd') || '/' ||
                                  svn_rep.projectname || '/user_' || svn_rep.developer ||
                                  '.html',
                                  ceil(SYSDATE - to_date(oa_pro.i_start_date, 'yyyymmdd')),
                                  oa_pro.user_sale,
                                  user_pro,
                                  oa_pro.user_sale_code,
                                  oa_pro.user_charg_code,
                                  svn_rep.developer,
                                  oa_dev.i_user_name,
                                  oa_dev.i_user_code,
                                  svn_day.filenum,
                                  svn_week.filenum,
                                  svn_month.filenum,
                                  svn_year.filenum,
                                  svn_day.codeline,
                                  svn_week.codeline,
                                  svn_month.codeline,
                                  svn_year.codeline,
                                  svn_day.commitcount,
                                  svn_week.commitcount,
                                  svn_month.commitcount,
                                  svn_year.commitcount
                        
                        UNION ALL
                        
                        SELECT pd.projectid 项目编号,
                               tpm.i_project_name 项目名称,
                               ceil(SYSDATE - to_date(tpm.i_start_date, 'yyyymmdd')) 开发时长,
                               tumsale.i_user_name 销售负责人,
                               tpm.i_user_sale 销售编号,
                               tumcharg.i_user_name 项目负责人,
                               tpm.i_user_charg 负责人编号,
                               0 总文件数量,
                               0 总代码数量,
                               0 总提交次数,
                               '是' 项目预定成员,
                               tum.i_user_name 开发成员名称,
                               pd.userid 员工编号,
                               0 本日文件数量,
                               0 本周文件数量,
                               0 本月文件数量,
                               0 本年文件数量,
                               0 本日代码行数,
                               0 本周代码行数,
                               0 本月代码行数,
                               0 本年代码行数,
                               0 本日提交次数,
                               0 本周提交次数,
                               0 本月提交次数,
                               0 本年提交次数,
                               '' 明细链接
                          FROM t_project_developer        pd,
                               t_svn_oa_developer_mapping dm,
                               t_users_mst                tum,
                               t_svn_oa_project_mapping   pm,
                               t_project_mst              tpm,
                               t_users_mst                tumsale,
                               t_users_mst                tumcharg
                         WHERE dm.oa_developer = pd.userid
                           AND tum.i_user_code = pd.userid
                           AND pm.oa_project = pd.projectid
                           AND NOT EXISTS
                         (SELECT 1
                                  FROM t_log_report r
                                 WHERE r.developer = dm.svn_developer)
                           AND tpm.i_project_code = pd.projectid
                           AND tumsale.i_user_code(+) = tpm.i_user_sale
                           AND tumcharg.i_user_code = tpm.i_user_charg)
                 ORDER BY 2;

    
    
        CURSOR rec_data2 IS
            SELECT decode(oa_dev.i_user_code,
                                    NULL,
                                    svn_rep.developer,
                                    oa_dev.i_user_code) 开发者编号,
                             decode(oa_dev.i_user_name,
                                    NULL,
                                    svn_rep.developer,
                                    oa_dev.i_user_name) 开发者姓名,
                             pnumber.a 参与项目数,
                             oa_pro.i_project_code 项目编号,
                             oa_pro.i_project_name 项目名称,
                             decode((SELECT '是'
                                      FROM t_project_developer
                                     WHERE projectid = oa_pro.i_project_code
                                       AND userid = oa_dev.i_user_code),
                                    NULL,
                                    '否',
                                    '是') 项目预定成员,
                             oa_pro.user_sale 销售人员,
                             oa_pro.user_sale_code 销售编号,
                             oa_pro.user_charg 项目负责人,
                             oa_pro.user_charg_code 负责人编号,
                             ceil(SYSDATE - to_date(oa_pro.i_start_date, 'yyyymmdd')) 开发时长,
                             decode(svn_day.filenum, NULL, 0, svn_day.filenum) 本日文件数量,
                             decode(svn_week.filenum, NULL, 0, svn_week.filenum) 本周文件数量,
                             decode(svn_month.filenum, NULL, 0, svn_month.filenum) 本月文件数量,
                             decode(svn_year.filenum, NULL, 0, svn_year.filenum) 本年文件数量,
                             decode(svn_day.codeline, NULL, 0, svn_day.codeline) 本日代码行数,
                             decode(svn_week.codeline, NULL, 0, svn_week.codeline) 本周代码行数,
                             decode(svn_month.codeline, NULL, 0, svn_month.codeline) 本月代码行数,
                             decode(svn_year.codeline, NULL, 0, svn_year.codeline) 本年代码行数,
                             decode(svn_day.commitcount, NULL, 0, svn_day.commitcount) 本日提交次数,
                             decode(svn_week.commitcount, NULL, 0, svn_week.commitcount) 本周提交次数,
                             decode(svn_month.commitcount, NULL, 0, svn_month.commitcount) 本月提交次数,
                             decode(svn_year.commitcount, NULL, 0, svn_year.commitcount) 本年提交次数
                        FROM t_log_report svn_rep,
                             t_project_developer pd,
                             (SELECT u_d.i_user_code,
                                     u_d.i_user_name,
                                     m2.svn_developer svn_user_code
                                FROM t_svn_oa_developer_mapping m2, t_users_mst u_d
                               WHERE m2.oa_developer = u_d.i_user_code) oa_dev,
                             (SELECT t.*,
                                     m.*,
                                     t.i_user_sale       user_sale_code,
                                     t.i_user_charg      user_charg_code,
                                     u_sale.i_user_name  user_sale,
                                     u_charg.i_user_name user_charg
                                FROM t_project_mst            t,
                                     t_svn_oa_project_mapping m,
                                     t_users_mst              u_sale,
                                     t_users_mst              u_charg
                               WHERE t.i_project_code = m.oa_project
                                 AND t.i_user_sale = u_sale.i_user_code(+)
                                 AND t.i_user_charg = u_charg.i_user_code) oa_pro,
                             (SELECT projectname,
                                     developer,
                                     SUM(codeline) codeline,
                                     SUM(filenum) filenum,
                                     COUNT(1) commitcount
                                FROM t_log_report
                               WHERE to_char(logtime, 'yyyymmdd') =
                                     to_char(SYSDATE - 1, 'yyyymmdd')
                               GROUP BY projectname, developer) svn_day,
                             (SELECT projectname,
                                     developer,
                                     SUM(codeline) codeline,
                                     SUM(filenum) filenum,
                                     COUNT(1) commitcount
                                FROM t_log_report
                               WHERE to_date(to_char(logtime, 'yyyymmdd'), 'yyyymmdd') BETWEEN
                                     trunc(SYSDATE - 1, 'd') + 1 AND
                                     trunc(SYSDATE - 1, 'd') + 1 + 6
                               GROUP BY projectname, developer) svn_week,
                             (SELECT projectname,
                                     developer,
                                     SUM(codeline) codeline,
                                     SUM(filenum) filenum,
                                     COUNT(1) commitcount
                                FROM t_log_report
                               WHERE to_date(to_char(logtime, 'yyyymmdd'), 'yyyymmdd') BETWEEN
                                     trunc(SYSDATE, 'mm') AND last_day(SYSDATE)
                               GROUP BY projectname, developer) svn_month,
                             (SELECT projectname,
                                     developer,
                                     SUM(codeline) codeline,
                                     SUM(filenum) filenum,
                                     COUNT(1) commitcount
                                FROM t_log_report
                               WHERE to_date(to_char(logtime, 'yyyymmdd'), 'yyyymmdd') BETWEEN
                                     trunc(SYSDATE, 'yy') AND
                                     last_day(add_months(trunc(SYSDATE, 'y'), 11))
                               GROUP BY projectname, developer) svn_year,
                             (SELECT COUNT(*) a, developer b
                                FROM (SELECT projectname, COUNT(projectname), developer
                                        FROM t_log_report
                                       GROUP BY projectname, developer)
                               GROUP BY developer) pnumber
                       WHERE svn_rep.projectname = oa_pro.svn_project
                         AND svn_rep.developer = oa_dev.svn_user_code(+)
                         AND svn_rep.projectname = svn_day.projectname(+)
                         AND svn_rep.developer = svn_day.developer(+)
                         AND svn_rep.projectname = svn_week.projectname(+)
                         AND svn_rep.developer = svn_week.developer(+)
                         AND svn_rep.projectname = svn_month.projectname(+)
                         AND svn_rep.developer = svn_month.developer(+)
                         AND svn_rep.projectname = svn_year.projectname(+)
                         AND svn_rep.developer = svn_year.developer(+)
                         AND svn_rep.developer = pnumber.b
                       GROUP BY oa_pro.i_project_code,
                                oa_pro.i_project_name,
                                pnumber.a,
                                ceil(SYSDATE - to_date(oa_pro.i_start_date, 'yyyymmdd')),
                                oa_pro.user_sale,
                                oa_pro.user_charg,
                                oa_pro.user_sale_code,
                                oa_pro.user_charg_code,
                                svn_rep.developer,
                                oa_dev.i_user_name,
                                oa_dev.i_user_code,
                                svn_day.filenum,
                                svn_week.filenum,
                                svn_month.filenum,
                                svn_year.filenum,
                                svn_day.codeline,
                                svn_week.codeline,
                                svn_month.codeline,
                                svn_year.codeline,
                                svn_day.commitcount,
                                svn_week.commitcount,
                                svn_month.commitcount,
                                svn_year.commitcount;

    
    
        CURSOR rec_data3 IS
            SELECT to_char(svn_rep.logtime, 'yyyyMMdd') 日期,
                   to_char(svn_rep.logtime, 'hh:MM') 时间,
                   svn_rep.versionnum 上传编号,
                   oa_pro.i_project_code 项目编号,
                   oa_pro.i_project_name 项目名称,
                   oa_pro.i_start_date 开工日期,
                   ceil(SYSDATE - to_date(oa_pro.i_start_date, 'yyyymmdd')) 开发时长,
                   oa_pro.user_sale 销售负责人,
                   oa_pro.user_sale_code 销售编号,
                   oa_pro.user_pro 项目负责人,
                   oa_pro.user_charg_code 负责人编号,
                   decode((SELECT '是'
                            FROM t_project_developer
                           WHERE projectid = oa_pro.i_project_code
                             AND userid = oa_dev.i_user_code),
                          NULL,
                          '否',
                          '是') 项目预定成员,
                   decode(oa_dev.i_user_name,
                          NULL,
                          svn_rep.developer,
                          oa_dev.i_user_name) 开发成员名称,
                   decode(oa_dev.i_user_code,
                          NULL,
                          svn_rep.developer,
                          oa_dev.i_user_code) 员工编号,
                   svn_rep.filenum 上传文件数量,
                   svn_rep.codeline 上传代码行数,
                   'http://showsan.com:8081/' || to_char(SYSDATE, 'yyyymmdd') || '/' ||
                   svn_rep.projectname || '/user_' || svn_rep.developer || '.html' 明细链接
              FROM t_log_report svn_rep,
                   t_project_mst pro_mst,
                   (SELECT u_d.i_user_code,
                           u_d.i_user_name,
                           m2.svn_developer svn_user_code
                      FROM t_svn_oa_developer_mapping m2, t_users_mst u_d
                     WHERE m2.oa_developer = u_d.i_user_code) oa_dev,
                   (SELECT t.*,
                           m.*,
                           t.i_user_sale      user_sale_code,
                           t.i_user_charg     user_charg_code,
                           u_sale.i_user_name user_sale,
                           u_pro.i_user_name  user_pro
                      FROM t_project_mst            t,
                           t_svn_oa_project_mapping m,
                           t_users_mst              u_sale,
                           t_users_mst              u_pro
                     WHERE t.i_project_code = m.oa_project
                       AND t.i_user_sale = u_sale.i_user_code(+)
                       AND t.i_user_charg = u_pro.i_user_code) oa_pro,
                   (SELECT projectname,
                           developer,
                           SUM(codeline) codeline,
                           SUM(filenum) filenum,
                           COUNT(1) commitcount
                      FROM t_log_report
                     WHERE to_char(logtime, 'yyyymmdd') =
                           to_char(SYSDATE - 1, 'yyyymmdd')
                     GROUP BY projectname, developer) svn_day,
                   (SELECT projectname,
                           developer,
                           SUM(codeline) codeline,
                           SUM(filenum) filenum,
                           COUNT(1) commitcount
                      FROM t_log_report
                     WHERE to_date(to_char(logtime, 'yyyymmdd'), 'yyyymmdd') BETWEEN
                           trunc(SYSDATE - 1, 'd') + 1 AND
                           trunc(SYSDATE - 1, 'd') + 1 + 6
                     GROUP BY projectname, developer) svn_week,
                   (SELECT projectname,
                           developer,
                           SUM(codeline) codeline,
                           SUM(filenum) filenum,
                           COUNT(1) commitcount
                      FROM t_log_report
                     WHERE to_date(to_char(logtime, 'yyyymmdd'), 'yyyymmdd') BETWEEN
                           trunc(SYSDATE, 'mm') AND last_day(SYSDATE)
                     GROUP BY projectname, developer) svn_month,
                   (SELECT projectname,
                           developer,
                           SUM(codeline) codeline,
                           SUM(filenum) filenum,
                           COUNT(1) commitcount
                      FROM t_log_report
                     WHERE to_date(to_char(logtime, 'yyyymmdd'), 'yyyymmdd') BETWEEN
                           trunc(SYSDATE, 'yy') AND
                           last_day(add_months(trunc(SYSDATE, 'y'), 11))
                     GROUP BY projectname, developer) svn_year
             WHERE svn_rep.projectname = oa_pro.svn_project
               AND svn_rep.developer = oa_dev.svn_user_code(+)
               AND svn_rep.projectname = svn_day.projectname(+)
               AND svn_rep.developer = svn_day.developer(+)
               AND svn_rep.projectname = svn_week.projectname(+)
               AND svn_rep.developer = svn_week.developer(+)
               AND svn_rep.projectname = svn_month.projectname(+)
               AND svn_rep.developer = svn_month.developer(+)
               AND svn_rep.projectname = svn_year.projectname(+)
               AND svn_rep.developer = svn_year.developer(+)
             GROUP BY svn_rep.logtime,
                      svn_rep.versionnum,
                      oa_pro.i_project_code,
                      oa_pro.i_project_name,
                      oa_pro.i_start_date,
                      svn_rep.filenum,
                      svn_rep.codeline,
                      'http://showsan.com:8081/' || to_char(SYSDATE, 'yyyymmdd') || '/' ||
                      svn_rep.projectname || '/user_' || svn_rep.developer || '.html',
                      ceil(SYSDATE - to_date(oa_pro.i_start_date, 'yyyymmdd')),
                      oa_pro.user_sale,
                      oa_pro.user_pro,
                      oa_pro.user_sale_code,
                      oa_pro.user_charg_code,
                      svn_rep.developer,
                      oa_dev.i_user_name,
                      oa_dev.i_user_code;

    
    BEGIN
        /* 清空计算当天数据 */
        DELETE t_svn_report_by_project
        WHERE  to_char(report_time, 'yyyymmdd') = to_char(SYSDATE, 'yyyymmdd');
    
        DELETE t_svn_report_by_users
        WHERE  to_char(report_time, 'yyyymmdd') = to_char(SYSDATE, 'yyyymmdd');
    
        DELETE t_svn_report_by_detail
        WHERE  to_char(report_time, 'yyyymmdd') = to_char(SYSDATE, 'yyyymmdd');
    
        /* 循环写入Project数据 */
        FOR rec IN rec_data LOOP
            INSERT INTO t_svn_report_by_project
                (project_code
                ,project_name
                ,develop_days
                ,user_sales
                ,user_project
                ,file_count
                ,code_count
                ,commit_count
                ,schedule_developer
                ,developer
                ,user_code
                ,file_count_day
                ,file_count_week
                ,file_count_month
                ,file_count_year
                ,code_count_day
                ,code_count_week
                ,code_count_month
                ,code_count_year
                ,commit_count_day
                ,commit_count_week
                ,commit_count_month
                ,commit_count_year
                ,detai_url
                ,report_time
                ,user_sale_code
                ,user_charg_code)
            VALUES
                (rec.项目编号
                ,rec.项目名称
                ,rec.开发时长
                ,rec.销售负责人
                ,rec.项目负责人
                ,rec.总文件数量
                ,rec.总代码数量
                ,rec.总提交次数
                ,rec.项目预定成员
                ,rec.开发成员名称
                ,rec.员工编号
                ,rec.本日文件数量
                ,rec.本周文件数量
                ,rec.本月文件数量
                ,rec.本年文件数量
                ,rec.本日代码行数
                ,rec.本周代码行数
                ,rec.本月代码行数
                ,rec.本年代码行数
                ,rec.本日提交次数
                ,rec.本周提交次数
                ,rec.本月提交次数
                ,rec.本年提交次数
                ,rec.明细链接
                ,SYSDATE
                ,rec.销售编号
                ,rec.负责人编号);
        END LOOP;
    
        /* 循环写入users数据 */
        FOR rec IN rec_data2 LOOP
            INSERT INTO t_svn_report_by_users
                (developer_number
                ,developer_name
                ,join_project_number
                ,project_number
                ,project_name
                ,schedule_developer
                ,user_sale
                ,user_project
                ,project_days
                ,file_count_day
                ,file_count_week
                ,file_count_month
                ,file_count_year
                ,code_count_day
                ,code_count_week
                ,code_count_month
                ,code_count_year
                ,commit_count_day
                ,commit_count_week
                ,commit_count_month
                ,commit_count_year
                ,report_time
                ,user_sale_code
                ,user_charg_code)
            VALUES
                (rec.开发者编号
                ,rec.开发者姓名
                ,rec.参与项目数
                ,rec.项目编号
                ,rec.项目名称
                ,rec.项目预定成员
                ,rec.销售人员
                ,rec.项目负责人
                ,rec.开发时长
                ,rec.本日文件数量
                ,rec.本周文件数量
                ,rec.本月文件数量
                ,rec.本年文件数量
                ,rec.本日代码行数
                ,rec.本周代码行数
                ,rec.本月代码行数
                ,rec.本年代码行数
                ,rec.本日提交次数
                ,rec.本周提交次数
                ,rec.本月提交次数
                ,rec.本年提交次数
                ,SYSDATE
                ,rec.销售编号
                ,rec.负责人编号);
        END LOOP;
        /* 循环写入detail数据 */
        FOR rec IN rec_data3 LOOP
            INSERT INTO t_svn_report_by_detail
                (submit_date
                ,submit_time
                ,upload_number
                ,project_number
                ,project_name
                ,start_date
                ,user_sale
                ,user_project
                ,schedule_developer
                ,developer
                ,user_code
                ,file_number
                ,code_number
                ,detai_url
                ,report_time
                ,user_sale_code
                ,user_charg_code
                )
            VALUES
                (rec.日期
                ,rec.时间
                ,rec.上传编号
                ,rec.项目编号
                ,rec.项目名称
                ,rec.开工日期
                ,rec.销售负责人
                ,rec.项目负责人
                ,rec.项目预定成员
                ,rec.开发成员名称
                ,rec.员工编号
                ,rec.上传文件数量
                ,rec.上传代码行数
                ,rec.明细链接
                ,SYSDATE
                ,rec.销售编号
                ,rec.负责人编号);
        END LOOP;
    
        /* 提交事务 */
        COMMIT;
    
        /* 返回状态 */
        RETURN;
    EXCEPTION
        WHEN OTHERS THEN
            RETURN;
    END calc_svn_report_by_day;

END oa_pkg;
/

推荐阅读更多精彩内容