APEX开发过程中需要注意的小细节5.5

最新推荐文章于 2024-10-11 17:04:33 发布

王小小鸭

最新推荐文章于 2024-10-11 17:04:33 发布

阅读量830

点赞数 6

文章标签：数据库 oracle Oracle apex sql

本文链接：https://blog.csdn.net/clover_oreo/article/details/137557682

版权

oracle保留小数点后两位的函数

在日常开发中经常用到百分比做数据对比，但是有可能得到的数据是一个多位小数，结果如下所示：

如果想截取部分小数如保留小数点后两位可以怎么做呢？

在Oracle中，可以使用ROUND函数来四舍五入保留小数点后两位，或者使用TRUNC函数来截断小数点后多余的位数。

例子：

使用ROUND函数：

SELECT ROUND(123.4567, 2) FROM DUAL;

-- 结果: 123.46

使用TRUNC函数：

SELECT TRUNC(123.4567, 2) FROM DUAL;

-- 结果: 123.45

在这里，DUAL是Oracle提供的一个虚拟表，可以用来进行这种单行选择操作。ROUND函数进行四舍五入，而TRUNC函数则直接截断多余的小数位数。两者的区别在于如何处理.5的情况，ROUND会四舍五入到最接近的偶数，而TRUNC则简单地截断。

【注意】截取的先后顺序也有讲究，如果是百分比需要100，建议先100再截取，否则结果精确度会受影响

(select distinct ROUND((select sum(CONSUMED)
                               from JA_PROJECT_MANAGER_OF_GROUP_TASK_V
                               where PRODUCTID = C.PRODUCTID
                                 and TO_CHAR(WORK_DATE, 'YYYYIW') = TO_CHAR(TRUNC(SYSDATE), 'YYYYIW'))
                                  / (select sum(CONSUMED)
                                     from JA_PROJECT_MANAGER_OF_GROUP_TASK_V
                                     where TO_CHAR(WORK_DATE, 'YYYYIW') = TO_CHAR(TRUNC(SYSDATE), 'YYYYIW'))* 100,2)
              from JA_PROJECT_MANAGER_OF_GROUP_TASK_V)AS ACTUAL_RATE_DEVELOPMENT
      --“开发投入实际占比”：当前统计周期内当前专项上投入的开发资源工时/当前统计周期内所有开发资源工时 * 100%
      from JAS_PROJECT_MASTER M
               left join JA_PROJECt_MAPPING B on B.PROJECT_ID = M.PROJECT_ID
               left join JA_PROJECT_MANAGER_OF_GROUP_TASK_V C on C.PRODUCTID = B.PRODUCT_ID

实际取值:8.26%

错误示范：

取值：8%

【不想拼接为空的数】

现状：当查询到的内容为空依然会拼接%，但是很突兀

原代码：直接拼接，并未判断，为空也展示%

改进：判断是否为空，若为空则赋值0

改进后效果：

【本周计划完成度与状态关联，当本周计划完成度小于100状态显示为异常（1），反之则正常（0）】

之前的做法是在两个项（本周计划完成度，状态）都设置动态操作，在里面设置对应的动态操作，设置值-JavaScript表达式-

if (utils.checkNull($v('P203_WEEK_PERCENTAGE')) ) {
return 0;
} else{
if (($v('P203_WEEK_PERCENTAGE')) >= 100) {
return 0;
}else{
return 1;
}
}

最后设置-刷新

受影响的元素-项-状态

但是这样太麻烦不说，js返回的0和1在项-本周计划完成度中没有意义

于是有了

方案二

直接对本周计划完成度做判断然后给状态赋值

JavaScript表达式-

if (($v('P203_WEEK_PERCENTAGE')) >= 100) {
$s('P203_STATUS',0);
}else{
$s('P203_STATUS',1);
}

效果：

完成度100为正常

完成度小于100为异常

【在APEX页面实现鼠标指到悬浮展示数据】

APEX也可以实现

属性-高级-初始化JavaS函数-

function(config) {
    config.defaultGridViewOptions = {
        tooltip: {
            // when the tooltip is integrated with the grid view the content callback
            // gets some extra helpful parameters
            content: function(callback, model, recordMeta, colMeta, columnDef ) {
                var text = null;

                // if in/over the row header display a tooltip based on 
                // the record edit state metadata
                if (recordMeta && $(this).hasClass( "a-GV-rowHeader" ) ) {
                    if ( recordMeta.deleted ) {
                        text = "This record has been deleted";
                    } else if ( recordMeta.inserted ) {
                        text = "This record has been added";
                    } else if ( recordMeta.updated ) {
                        text = "This record has been changed";
                    }
                } else {
                    if ( columnDef && recordMeta) {
                        // if in/over the DEFAULT_VALUE column put the DEFAULT_VALUE in a tooltip so more of the DEFAULT_VALUE can be seen
                        // if in/over the name column show the hire date
                        if ( columnDef.property === "ACTUAL_RATE_DEVELOPMENT" ) {
                            text = model.getValue( recordMeta.record, "DETAILS" );
                        }
                    }
                    // if in/over any other column display a tooltip based on 
                    // the changed state metadata
                    if ( colMeta && colMeta.changed ) {
                        if ( text !== null) {
                            text += "<br>";
                        } else {
                            text = "";
                        }
                        text += "This cell has been changed";
                    }
                }
                return text;
            }
        }
    };
    return config;
}

【汉字拼接多个查询值输出为一个值】

1.0版本

SELECT LISTAGG(label, ',') WITHIN GROUP (ORDER BY label) AS labels
FROM (SELECT '该项目本周总开发工时：' || sum(CONSUMED) AS label
      FROM JAS_PROJECT_MASTER M
               left join JA_PROJECt_MAPPING B on B.PROJECT_ID = M.PROJECT_ID
               left join JA_PROJECT_MANAGER_OF_GROUP_TASK_V C on C.PRODUCTID = B.PRODUCT_ID
      where TO_CHAR(WORK_DATE, 'YYYYIW') = TO_CHAR(TRUNC(SYSDATE), 'YYYYIW')
        and M.PROJECT_ID = :P205_PROJECT_ID
      UNION ALL
      SELECT '晶品研发组本周开发工时(小时)：' || nvl((select sum(CONSUMED)
                                                     from JA_PROJECT_MANAGER_OF_GROUP_TASK_V
                                                     where PRODUCTID = C.PRODUCTID
                                                       and code = 'kaifa-2024'
                                                       and TO_CHAR(WORK_DATE, 'YYYYIW') = TO_CHAR(TRUNC(SYSDATE), 'YYYYIW')),
                                                    0)
      FROM JAS_PROJECT_MASTER M
               left join JA_PROJECt_MAPPING B on B.PROJECT_ID = M.PROJECT_ID
               left join JA_PROJECT_MANAGER_OF_GROUP_TASK_V C on C.PRODUCTID = B.PRODUCT_ID
      where TO_CHAR(WORK_DATE, 'YYYYIW') = TO_CHAR(TRUNC(SYSDATE), 'YYYYIW')
        and M.PROJECT_ID = :P205_PROJECT_ID
      UNION ALL
      SELECT '晶品支撑组本周开发工时(小时)：' || nvl((select sum(CONSUMED)
                                                     from JA_PROJECT_MANAGER_OF_GROUP_TASK_V
                                                     where PRODUCTID = C.PRODUCTID
                                                       and code = 'LCODEDEV-2024'
                                                       and TO_CHAR(WORK_DATE, 'YYYYIW') = TO_CHAR(TRUNC(SYSDATE), 'YYYYIW')),
                                                    0)
      FROM JAS_PROJECT_MASTER M
               left join JA_PROJECt_MAPPING B on B.PROJECT_ID = M.PROJECT_ID
               left join JA_PROJECT_MANAGER_OF_GROUP_TASK_V C on C.PRODUCTID = B.PRODUCT_ID
      where TO_CHAR(WORK_DATE, 'YYYYIW') = TO_CHAR(TRUNC(SYSDATE), 'YYYYIW')
        and M.PROJECT_ID = :P205_PROJECT_ID)
;

输出

都在同一行，页面不美观

改进2.0版本：在分结果之后面拼接|| CHR(10)

SELECT LISTAGG(label, ',') WITHIN GROUP (ORDER BY label) AS labels
FROM (SELECT '该项目本周总开发工时：' || sum(CONSUMED) AS label
      FROM JAS_PROJECT_MASTER M
               left join JA_PROJECt_MAPPING B on B.PROJECT_ID = M.PROJECT_ID
               left join JA_PROJECT_MANAGER_OF_GROUP_TASK_V C on C.PRODUCTID = B.PRODUCT_ID
      where TO_CHAR(WORK_DATE, 'YYYYIW') = TO_CHAR(TRUNC(SYSDATE), 'YYYYIW')
        and M.PROJECT_ID = :P205_PROJECT_ID
      UNION ALL
      SELECT '晶品研发组本周开发工时(小时)：' || nvl((select sum(CONSUMED)
                                                     from JA_PROJECT_MANAGER_OF_GROUP_TASK_V
                                                     where PRODUCTID = C.PRODUCTID
                                                       and code = 'kaifa-2024'
                                                       and TO_CHAR(WORK_DATE, 'YYYYIW') = TO_CHAR(TRUNC(SYSDATE), 'YYYYIW')),
                                                    0) || CHR(10)
      FROM JAS_PROJECT_MASTER M
               left join JA_PROJECt_MAPPING B on B.PROJECT_ID = M.PROJECT_ID
               left join JA_PROJECT_MANAGER_OF_GROUP_TASK_V C on C.PRODUCTID = B.PRODUCT_ID
      where TO_CHAR(WORK_DATE, 'YYYYIW') = TO_CHAR(TRUNC(SYSDATE), 'YYYYIW')
        and M.PROJECT_ID = :P205_PROJECT_ID
      UNION ALL
      SELECT '晶品支撑组本周开发工时(小时)：' || nvl((select sum(CONSUMED)
                                                     from JA_PROJECT_MANAGER_OF_GROUP_TASK_V
                                                     where PRODUCTID = C.PRODUCTID
                                                       and code = 'LCODEDEV-2024'
                                                       and TO_CHAR(WORK_DATE, 'YYYYIW') = TO_CHAR(TRUNC(SYSDATE), 'YYYYIW')),
                                                    0) || CHR(10)
      FROM JAS_PROJECT_MASTER M
               left join JA_PROJECt_MAPPING B on B.PROJECT_ID = M.PROJECT_ID
               left join JA_PROJECT_MANAGER_OF_GROUP_TASK_V C on C.PRODUCTID = B.PRODUCT_ID
      where TO_CHAR(WORK_DATE, 'YYYYIW') = TO_CHAR(TRUNC(SYSDATE), 'YYYYIW')
        and M.PROJECT_ID = :P205_PROJECT_ID)
;

在数据库层面实现了换行，但是在Oracle APEX页面还是紧密排序的，看来无法使用。

改进3.0版本：拼接|| '<br/>'，在APEX页面层面实现拼接换行

SELECT LISTAGG(label, ',') WITHIN GROUP (ORDER BY label) AS labels
FROM (SELECT '该项目本周总开发工时：' || sum(CONSUMED) AS label
      FROM JAS_PROJECT_MASTER M
               left join JA_PROJECt_MAPPING B on B.PROJECT_ID = M.PROJECT_ID
               left join JA_PROJECT_MANAGER_OF_GROUP_TASK_V C on C.PRODUCTID = B.PRODUCT_ID
      where TO_CHAR(WORK_DATE, 'YYYYIW') = TO_CHAR(TRUNC(SYSDATE), 'YYYYIW')
        and M.PROJECT_ID = :P205_PROJECT_ID
      UNION ALL
      SELECT '晶品研发组本周开发工时(小时)：' || nvl((select sum(CONSUMED)
                                                     from JA_PROJECT_MANAGER_OF_GROUP_TASK_V
                                                     where PRODUCTID = C.PRODUCTID
                                                       and code = 'kaifa-2024'
                                                       and TO_CHAR(WORK_DATE, 'YYYYIW') = TO_CHAR(TRUNC(SYSDATE), 'YYYYIW')),
                                                    0)|| '<br/>'
      FROM JAS_PROJECT_MASTER M
               left join JA_PROJECt_MAPPING B on B.PROJECT_ID = M.PROJECT_ID
               left join JA_PROJECT_MANAGER_OF_GROUP_TASK_V C on C.PRODUCTID = B.PRODUCT_ID
      where TO_CHAR(WORK_DATE, 'YYYYIW') = TO_CHAR(TRUNC(SYSDATE), 'YYYYIW')
        and M.PROJECT_ID = :P205_PROJECT_ID
      UNION ALL
      SELECT '晶品支撑组本周开发工时(小时)：' || nvl((select sum(CONSUMED)
                                                     from JA_PROJECT_MANAGER_OF_GROUP_TASK_V
                                                     where PRODUCTID = C.PRODUCTID
                                                       and code = 'LCODEDEV-2024'
                                                       and TO_CHAR(WORK_DATE, 'YYYYIW') = TO_CHAR(TRUNC(SYSDATE), 'YYYYIW')),
                                                    0)|| '<br/>'
      FROM JAS_PROJECT_MASTER M
               left join JA_PROJECt_MAPPING B on B.PROJECT_ID = M.PROJECT_ID
               left join JA_PROJECT_MANAGER_OF_GROUP_TASK_V C on C.PRODUCTID = B.PRODUCT_ID
      where TO_CHAR(WORK_DATE, 'YYYYIW') = TO_CHAR(TRUNC(SYSDATE), 'YYYYIW')
        and M.PROJECT_ID = :P205_PROJECT_ID)

改进4.0版本：想优先展示总开发工时，再展示各组

去掉分组和排序条件，按照想要的顺序查询数据并拼接即可

select *
from (select d.project_detail_id                                               project_detail_id,
             m.project_id                                                      project_id,
             m.project_name                                                    project_name,
             m.project_class                                                   project_class,--工作类型 专项，事务，推广/内部专项、外部专项
             m.important_grade                                                 important_grade, --重要等级（仅内部人员可见）
             m.project_status                                                  project_status,--当前阶段
             m.project_scope                                                   project_scope,--项目范围
             m.leading_unit                                                    leading_unit,--需求单位
             (CASE
                  WHEN plan_to_end_date IS NULL THEN NULL
                  when SYSDATE > plan_to_end_date then '100%'
                  ELSE
                      FLOOR((SYSDATE - start_date) / (plan_to_end_date - start_date) * 100) || '%'
                 END)                                                       AS progress, --项目进度（%）
             (SELECT next_plan
              FROM (SELECT f.project_id                                                                            project_id,
                           f.next_plan                                                                             next_plan,
                           ROW_NUMBER() OVER (PARTITION BY t.project_id ORDER BY f.CREATE_DATE DESC NULLS LAST) AS rn
                    FROM JAS_PROJECT_MASTER t
                             JOIN
                        JAS_PROJECT_DETAIL f ON t.project_id = f.project_id and to_char(t.start_date, 'yyyy') = '2024')
              WHERE rn = 2
                and project_id = m.project_id)                                 plan,--上周的“下周计划”即——本周计划
             d.current_progress                                             as current_progress_week, --本周工作总结
             (case
                  when d.WEEK_PERCENTAGE is null then ''
                  else d.WEEK_PERCENTAGE || '%' end)                        as WEEK_PERCENTAGE, --本周计划内工作完成度（%）
             (case
                  when d.ACTUAL_RATE_WEEK is null then ''
                  else d.ACTUAL_RATE_WEEK || '%' end)                          ACTUAL_RATE_WEEK, --本周实际工作完成度（%）
             d.next_plan                                                       next_plan, --下周工作计划
             m.project_owner                                                   project_owner, --项目负责人
             m.project_target                                                  project_target,--项目目标
             m.progress_memo                                                   progress_memo,--进度说明
             m.start_date                                                      start_date,--开始时间
             m.plan_to_end_date                                                plan_to_end_date,--计划完成时间
             m.actual_end_date                                                 actual_end_date,--实际完成时间
             m.REQUEST_CONTACT_POINT, --需求方对接人（必填）
             m.HQIT_TEAM,--总部IT对接组
             m.HQIT_TEAM_CP,--总部IT对接人
             m.VENDOR,--厂商
             m.VENDOR_CP,--厂商对接人
            m.WEIGHT_COEFFICIENT                                              WEIGHT_COEFFICIENT, --权重系数
            d.NEXT_PLAN_ORG,--下周工作责任方/对接方
             d.NEXT_PLAN_OWNER,--下周工作责任人/对接人
             (case when d.STATUS = 0 or d.STATUS is null then 0 else 1 end) as STATUS,
             d.project_comment                                                 project_comment,--异常说明
             d.update_date                                                     update_date,
             (case
                  when m.WEIGHT_COEFFICIENT = 0 then ''
                  else NVL(ROUND((select m.WEIGHT_COEFFICIENT /
                                        sum(WEIGHT_COEFFICIENT) * 100
                                  from JAS_PROJECT_MASTER D
                                  where PROJECT_CLASS = '内部专项'
                                  group by PROJECT_CLASS),
                                2), 0) || '%' end)
                                                                           AS REASONABLE_RATE_DEVELOPMENT,
             --“开发投入合理占比”：当前内部专项权重系数 / 所有内部专项的权重之和 * 100%
             (select distinct NVL(ROUND((select sum(CONSUMED)
                                         from JA_PROJECT_MANAGER_OF_GROUP_TASK_V2
                                         where PRODUCTID = B.PRODUCT_ID
                                           and TO_CHAR(WORK_DATE, 'YYYYIW') = TO_CHAR(TRUNC(SYSDATE), 'YYYYIW'))
                                           / (select sum(CONSUMED)
                                               from JA_PROJECT_MANAGER_OF_GROUP_TASK_V2
                                               where TO_CHAR(WORK_DATE, 'YYYYIW') = TO_CHAR(TRUNC(SYSDATE), 'YYYYIW')) *
                                        100, 2), 0)
             from JA_PROJECT_MANAGER_OF_GROUP_TASK_V2) || '%'
                                                                           AS ACTUAL_RATE_DEVELOPMENT,
             --“开发投入实际占比”：当前统计周期内当前专项上投入的开发资源工时/当前统计周期内所有开发资源工时 * 100%
(SELECT LISTAGG(label, '') AS labels
FROM (SELECT distinct '该项目本周总开发工时：' || sum(CONSUMED)  || '<br/>' AS label
      FROM JAS_PROJECT_MASTER M2
               left join JA_PROJECt_MAPPING B on B.PROJECT_ID = M2.PROJECT_ID
               left join JA_PROJECT_MANAGER_OF_GROUP_TASK_V2 C on C.PRODUCTID = B.PRODUCT_ID
      where TO_CHAR(WORK_DATE, 'YYYYIW') = TO_CHAR(TRUNC(SYSDATE), 'YYYYIW')
        and M.PROJECT_ID = M2.PROJECT_ID
      UNION ALL
      SELECT distinct  '晶品研发组本周开发工时(小时)：' || nvl((select sum(CONSUMED)
                                                     from JA_PROJECT_MANAGER_OF_GROUP_TASK_V2
                                                     where PRODUCTID = C.PRODUCTID
                                                       and code = 'kaifa-2024'
                                                       and TO_CHAR(WORK_DATE, 'YYYYIW') = TO_CHAR(TRUNC(SYSDATE), 'YYYYIW')),
                                                    0) || '<br/>'
      FROM JAS_PROJECT_MASTER M0
               left join JA_PROJECt_MAPPING B on B.PROJECT_ID = M0.PROJECT_ID
               left join JA_PROJECT_MANAGER_OF_GROUP_TASK_V2 C on C.PRODUCTID = B.PRODUCT_ID
      where TO_CHAR(WORK_DATE, 'YYYYIW') = TO_CHAR(TRUNC(SYSDATE), 'YYYYIW')
        and M.PROJECT_ID = M0.PROJECT_ID
      UNION ALL
      SELECT distinct  '晶品支撑组本周开发工时(小时)：' || nvl((select sum(CONSUMED)
                                                     from JA_PROJECT_MANAGER_OF_GROUP_TASK_V2
                                                     where PRODUCTID = C.PRODUCTID
                                                       and code = 'LCODEDEV-2024'
                                                       and TO_CHAR(WORK_DATE, 'YYYYIW') = TO_CHAR(TRUNC(SYSDATE), 'YYYYIW')),
                                                    0) || '<br/>'
      FROM JAS_PROJECT_MASTER M1
               left join JA_PROJECt_MAPPING B on B.PROJECT_ID = M1.PROJECT_ID
               left join JA_PROJECT_MANAGER_OF_GROUP_TASK_V2 C on C.PRODUCTID = B.PRODUCT_ID
      where TO_CHAR(WORK_DATE, 'YYYYIW') = TO_CHAR(TRUNC(SYSDATE), 'YYYYIW')
        and M.PROJECT_ID = M1.PROJECT_ID) ) DETAILS
      from JAS_PROJECT_MASTER M
               left join JA_PROJECt_MAPPING B on B.PROJECT_ID = M.PROJECT_ID
--                left join JA_PROJECT_MANAGER_OF_GROUP_TASK_V2 C on C.PRODUCTID = B.PRODUCT_ID
               left join (select *
                          from (select rank() over (partition by e.project_id order by e.CREATE_DATE desc NULLS LAST ) rn,
                                       e.project_detail_id,
                                       e.project_id,
                                       e.update_date,
                                       e.updator,
                                       e.project_comment,
                                       e.STATUS,
                                       e.NEXT_PLAN_ORG,
                                       e.NEXT_PLAN_OWNER,
                                       e.WEEK_PERCENTAGE,
                                       e.next_plan,
                                       e.current_progress,
                                       e.CREATE_BY,
                                       e.CREATE_DATE,
                                       e.ACTUAL_RATE_WEEK
                                from JAS_PROJECT_DETAIL e)
                          where rn = 1) d
                        on m.project_id = d.project_id
               left join JA_PROJECt_MAPPING P on P.PROJECT_ID = M.PROJECT_ID
      where start_date >= to_date('2024-01-01', 'yyyy-mm-dd')
        and start_date <= to_date('2024-12-31', 'yyyy-mm-dd')
        and m.PROJECT_OWNER is not null
        and m.DEL_FLAG is null
        and (m.important_grade = :P205_IMPORTANT_GRADE or :P205_IMPORTANT_GRADE is null)
        and (m.project_class = :P205_CLASSIFY or :P205_CLASSIFY is null)
        and (m.project_status = :P205_PROJECT_STATUS or :P205_PROJECT_STATUS is null)
      order by M.IMPORTANT_GRADE);