记录oracle业务用法（函数）

黄贞辉

已于 2024-01-04 21:46:57 修改

阅读量1k

点赞数

文章标签： oracle

于 2022-04-12 12:13:45 首次发布

本文链接：https://blog.csdn.net/weixin_45429720/article/details/124120887

版权

1、case

oracle 当状态为0时显示状态为执行完成，当状态为1时显示状态为执行异常

SELECT citi.TASK_NAME, citi.TASK_CODE, citi.FILE_PATH, citl.START_TIME, citl.END_TIME, 
(case 
when citl.RESULT= '0' then '执行完成' 
when citl.RESULT= '1' then '执行异常' 
else '' 
end ) RESULT, 
citl.RESULT_INFO 
FROM CF_INF_TASK_INFO citi, CF_INF_TASK_LOG citl 
WHERE citi.STATE=0
AND citi.TASK_CODE=citl.TASK_CODE
ORDER BY citl.START_TIME DESC

2、分组统计数量排序，再取前10个

注意：在使用group by进行分组的时候，select 后面要查询出来的表字段只用有两种情况：
1、在group by 子句中出现
2、或者写在聚合函数里面，像count（id）等等。
若是没有，select出来的表字段就会报错，不符合group by分组

第一步：分组统计数量排序

SELECT PARENT_AREA_NAME,count(WORKSHEET_NBM) as count
FROM SH_BACKLOG_WORKSHEET_DETAIL 
group by PARENT_AREA_NAME 
order by count desc

在这里插入图片描述
第二步，在外面加一层select 获取前10个数据

select * from (
SELECT PARENT_AREA_NAME,count(WORKSHEET_NBM) as count
FROM SH_BACKLOG_WORKSHEET_DETAIL 
group by PARENT_AREA_NAME 
order by count desc ) v where ROWNUM<= 10

在这里插入图片描述

3、oracle 根据查询的数据进行插入操作

    insert into SH_BACKLOG_TOP_TEN_BAR(PARENT_AREA_ID , NAME, PARENT_ID, COUNT, TYPE) 
         SELECT PARENT_AREA_ID, PARENT_AREA_NAME as NAME , 200 as PARENT_ID, 
         count(WORKSHEET_NBM) as COUNT , 1 as TYPE   
         FROM SH_BACKLOG_ORDER_DETAIL  
         group by PARENT_AREA_ID,PARENT_AREA_NAME

4、新建函数 f_time_format 根据秒的数字转化成时分秒

create or replace function f_time_format(f_time in varchar) --函数参数p
return varchar2 --函数返回类型
is v_out varchar2(50) ;--返回的变量
--函数结构体
begin
 if f_time =0 then
    v_out := '';
    return(v_out);
  end if;  

   SELECT 
        TO_CHAR(TRUNC(f_time/3600),'FM9900') || '时' ||
        TO_CHAR(TRUNC(MOD(f_time,3600)/60),'FM00') || '分' ||
        TO_CHAR(MOD(f_time,60),'FM00')|| '秒'  
        into v_out
    FROM dual ;
     return v_out;
     exception
      when no_data_found then
        v_out := '';
        return(v_out);  
end;

select f_time_format(62) from dual

在这里插入图片描述

5、round() 四舍五入，floor()向下取整，ceil()向上取整

	select round(75.5666) from dual; 返回76
	select round(75.5666,2) from dual; 返回75.57
	select floor(75.5666) from dual;返回75
	select ceil(75.4666) from dual;返回76

6、instr(字符串,关键字符,字符串第几位)，substr(字符串，数字)，都是从1开始，不是从0开始

例子：
在字符串中 ‘许滨,许滨,许滨,许滨,许滨,许滨,许滨,许滨,许滨1’，想取许滨1，即最后一个逗号之后的字符串，以下sql含义，‘-1’是倒着寻找， '，'逗号是我的关键字符，倒着找第一次出现的逗号的位置是24，

这里得到逗号的位置24
select instr('许滨,许滨,许滨,许滨,许滨,许滨,许滨,许滨,许滨1', ',', -1) from dual

从24+1开始截取剩下的字符串 ’许滨1‘
  select substr('许滨,许滨,许滨,许滨,许滨,许滨,许滨,许滨,许滨1',
                  instr('许滨,许滨,许滨,许滨,许滨,许滨,许滨,许滨,许滨1', ',', -1) + 1) from dual

7、listagg(列名，‘拼接符’)within group(order by 排序的列)，一个环节多条记录，拼接该环节的某字段

1个oper_track_id有多条记录，现把同多条记录按照oper_track_id排序后，把每个turn_party_name拼接起来，用逗号隔开

select order_id,
                   listagg(turn_party_name, ',') within group(order by oper_track_id) as turn_party_name
              from pr_order_oper_track poot
             group by order_id) temp
     where temp.order_id = in_order_id;



也可以结合case使用
select  listagg((case

                         when poot.oper_type_id = '200' then

                         poot.turn_party_name

                         else

                          ''

                       end),

                       ',') within group(order by oper_track_id) as zhuanpPartyName
											 from pr_order_oper_track poot where poot.order_id=741257;

8、创建函数functtion

若group by id 后该id有3行数据，wm_concat可以合并3行数据的某一列，默认用,分隔，
但是可能不按顺序，所以需要over (partition by …）分区再排序，最后用max()取最长的一个。

--3、新增获取备注函数
create or replace function f_work_result(f_order_id in varchar) --函数参数p
return varchar2 --函数返回类型
is v_out varchar2(2048) ;--返回的变量
--函数结构体
begin
 if f_order_id is null then
    v_out := '';
    return(v_out);
  end if;  

   select max(a.work_result)  into v_out  from (
select order_id,wm_concat(case
                            when oper_type_id = '130' then
                           work_result
                           else
                            null
                         end) over (partition by order_id order by OPER_TRACK_ID asc)  as  work_result from pr_order_oper_track
where order_id = f_order_id 
)a   group by a.order_id ;
     return v_out;
     exception
      when no_data_found then
        v_out := '';
        return(v_out);  
end;

一个order_id定单有多条工单oper_track_id信息，每个工单也有多条环节记录，现在要把该订单最后转派人turn_party_name筛选出来, listagg()将排序后的结果拼接起来，然后再截取最后一个逗号之后的字符串。

CREATE OR REPLACE function   f_get_last_turn_party_name(in_order_id in number)
  return varchar is
  v_out varchar2(1000);
begin
  if in_order_id is null then
    v_out := '';
    return(v_out);
  end if;
  begin
    select substr(temp.turn_party_name,
                  instr(temp.turn_party_name, ',', -1) + 1)
      into v_out
      from (select order_id,
                   listagg(turn_party_name, ',') within group(order by oper_track_id) as turn_party_name
              from pr_order_oper_track poot
             group by order_id) temp
     where temp.order_id = in_order_id;
  exception
    when others then
      v_out := '';
      return(v_out);
  end;
  return v_out;
end f_get_last_turn_party_name;

9、同义词

之前是在SY_DESI用户下，只能
select * from SY_DESI.sic_deq
执行了这个同义词语句之后
CREATE SYNONYM ltcs.sic_deq FOR SY_DESI.sic_deq;
这个语句也可以执行了
select * from ltcs.sic_deq

10、计算总计

COALESCE(temp.city, ‘总计’) as city
搭配 group by ROLLUP (DIVISION_NAME)
或搭配 group by GROUPING SETS ((DIVISION_NAME, sd.DEPTNAME), DIVISION_NAME)
能帮忙算出总计，而且当字段是 ROUND() 时，还能直接算出总计的平均数，而不是把百分比相加

<select id="selectKpiObstacle" resultType="com.iwhalecloud.ids.busi.watch.superviseinfo.dto.SuperviseCountDto">

        SELECT COALESCE(temp.city, '总计') as city,
        <if test="userLevel == 2">
            COALESCE(temp.county, '总计') as county,
        </if>
        
        temp.countOvertimeNotArchived,
        ROUND((
            CASE WHEN temp.orderTotal = 0 THEN 1
            ELSE (1-(temp.outTimeOrderNum / temp.orderTotal))  END
                     +
            CASE WHEN temp.cityOrderTotal = 0 THEN 1
            ELSE (1-(temp.outTimeCityOrderNum / temp.cityOrderTotal)) END
            ) * 50, 2) AS processingTimelinessRatio
        FROM
             (
        SELECT
        DIVISION_NAME as city,
        <if test="userLevel == 2 ">
            sd.DEPTNAME as county,
        </if>
            SUM(CASE WHEN EXTERNAL_NUM > 0 THEN 1 ELSE 0 END) AS countExternalReminders,
            COUNT(*) AS orderTotal,
            SUM(CASE
                      WHEN       ((TO_NUMBER(REGEXP_SUBSTR(FORM_WEIGHT, '[0-9]+')) >= 500 OR MEDALS = '1') AND ZYFGCZW = '城市' AND (SYSDATE - TO_DATE(ACCEPT_TIME, 'YYYY-MM-DD HH24:MI:SS')) * 24 > 8)
                              OR ((TO_NUMBER(REGEXP_SUBSTR(FORM_WEIGHT, '[0-9]+')) >= 500 OR MEDALS = '1') AND ZYFGCZW != '城市' AND (SYSDATE - TO_DATE(ACCEPT_TIME, 'YYYY-MM-DD HH24:MI:SS')) * 24 > 12)
                              OR ((TO_NUMBER(REGEXP_SUBSTR(FORM_WEIGHT, '[0-9]+')) <![CDATA[ < ]]> 500 AND MEDALS = '0') AND (SYSDATE - TO_DATE(ACCEPT_TIME, 'YYYY-MM-DD HH24:MI:SS')) * 24 > 24)
                          THEN 1
                      ELSE 0
                END) AS outTimeOrderNum,

            SUM(CASE
                    WHEN ZYFGCZW = '城市'
                    THEN 1
                    ELSE 0
            END) AS cityOrderTotal,
            SUM(CASE
                    WHEN ZYFGCZW = '城市'
                    AND  (TO_DATE(ACCEPT_TIME, 'YYYY-MM-DD HH24:MI:SS') <![CDATA[ < ]]> TRUNC(TO_DATE(ACCEPT_TIME, 'YYYY-MM-DD HH24:MI:SS')) + (20 / 24) AND OVER_TIME > TRUNC(TO_DATE(ACCEPT_TIME, 'YYYY-MM-DD HH24:MI:SS')) + 1 )
                    OR   (TO_DATE(ACCEPT_TIME, 'YYYY-MM-DD HH24:MI:SS') >= TRUNC(TO_DATE(ACCEPT_TIME, 'YYYY-MM-DD HH24:MI:SS')) + (20 / 24) AND OVER_TIME > TRUNC(TO_DATE(ACCEPT_TIME, 'YYYY-MM-DD HH24:MI:SS')) + 2 )
                    THEN 1
                    ELSE 0
            END) AS outTimeCityOrderNum,
            SUM(CASE
                      WHEN BACK_DATE IS NULL
                          AND ((TO_NUMBER(REGEXP_SUBSTR(FORM_WEIGHT, '[0-9]+')) >= 500 OR MEDALS = '1') AND ZYFGCZW = '城市' AND (SYSDATE - TO_DATE(ACCEPT_TIME, 'YYYY-MM-DD HH24:MI:SS')) * 24 > 8)
                          OR   ((TO_NUMBER(REGEXP_SUBSTR(FORM_WEIGHT, '[0-9]+')) >= 500 OR MEDALS = '1') AND ZYFGCZW != '城市' AND (SYSDATE - TO_DATE(ACCEPT_TIME, 'YYYY-MM-DD HH24:MI:SS')) * 24 > 12 )
                          OR   ((TO_NUMBER(REGEXP_SUBSTR(FORM_WEIGHT, '[0-9]+')) <![CDATA[ < ]]> 500 AND MEDALS = '0') AND (SYSDATE - TO_DATE(ACCEPT_TIME, 'YYYY-MM-DD HH24:MI:SS')) * 24 > 24)
                          THEN 1
                      ELSE 0
                END) AS countOvertimeNotArchived
        FROM TABLE1 svob
        JOIN TABLE2 sd ON  sd.TERR_CODE = SUBSTR(svob.TERR_CODE, 1, 8)
        <if test="userLevel == 2 and areaName != null and areaName != '' ">
            and DIVISION_NAME = #{areaName}
        </if>
        <if test="userLevel == 3 ">
            group by  ROLLUP (DIVISION_NAME)
        </if>
        <if test="userLevel == 2 ">
            group by  GROUPING SETS ((DIVISION_NAME, sd.DEPTNAME), DIVISION_NAME)
        </if>
        )temp

    </select>

当userLevel = 3时是统计全省：
在这里插入图片描述

当userLevel =2 时是统计某地市：

在这里插入图片描述

11、获取多个值中最小，最大值

SELECT LEAST(10, 20) FROM dual;
SELECT GREATEST(45, 8, 67) FROM dual;

12、截取时间的各部分

SELECT SYSDATE FROM dual;
SELECT TRUNC(SYSDATE)  FROM dual;
SELECT TO_CHAR(SYSDATE, 'YYYY') FROM dual;
SELECT TO_CHAR(SYSDATE, 'MM') FROM dual;
SELECT TO_CHAR(SYSDATE, 'DD') FROM dual;
SELECT TO_CHAR(SYSDATE, 'HH24') FROM dual;
SELECT TO_CHAR(SYSDATE, 'MI') FROM dual;
SELECT TO_CHAR(SYSDATE, 'SS') FROM dual;

13、查看某表某字段是否建了索引

SELECT INDEX_NAME
FROM ALL_IND_COLUMNS
WHERE TABLE_NAME = 'INSTALL_FORM_TABLE'
  AND COLUMN_NAME = 'CREATE_DATE';

持续更新中…

黄贞辉

关注

0
点赞
踩
0

收藏

觉得还不错? 一键收藏
0
评论
记录oracle业务用法（函数）

1、caseoracle 当状态为0时显示状态为执行完成，当状态为1时显示状态为执行异常SELECT citi.TASK_NAME, citi.TASK_CODE, citi.FILE_PATH, citl.START_TIME, citl.END_TIME, (case when citl.RESULT= '0' then '执行完成' when citl.RESULT= '1' then '执行异常' else '' end ) RESULT, citl.RESULT_INFO FRO
复制链接

扫一扫