Oracle创建一些常用的函数
1.这个函数是求两个时间的差值 得出来的结果以*天*时*分*秒的格式呈现
CREATE OR REPLACE FUNCTION F_GET_DIFF_TIME(START_TIME IN DATE, END_TIME IN DATE) RETURN VARCHAR2 IS DIFF_TIME VARCHAR2(50); BEGIN SELECT TDAY || '天' || THOUR || '时' || TMINUTE || '分' || ROUND((TT - TMINUTE) * 60) || '秒' INTO DIFF_TIME FROM (SELECT TDAY, THOUR, TRUNC((TT - THOUR) * 60) TMINUTE, (TT - THOUR) * 60 TT FROM (SELECT TDAY, TRUNC((TT - TDAY) * 24) THOUR, (TT - TDAY) * 24 TT FROM (SELECT TO_NUMBER(END_TIME - START_TIME) AS TT, TRUNC(TO_NUMBER(END_TIME - START_TIME)) AS TDAY FROM (SELECT START_TIME, END_TIME FROM dual)))); RETURN DIFF_TIME; END;
这个函数在ADS当中创建需要将选项中的红框内的两个勾掉 可以直接在命令窗口创建，反而在函数编辑窗口总是报错。。。
创建好以后 测试
select F_GET_DIFF_TIME( to_date('2019-07-16 18:10:10','yyyy-MM-dd hh24:mi:ss'),to_date('2019-07-18 12:40:50','yyyy-MM-dd hh24:mi:ss')) as 时间差 from dual
测试无误 至此已经简单的制作，使用了一个函数了。
2.两个时间点中间有效的工作时长的函数
创建这个函数之前 需要先建立一张非工作日的数据库表
CREATE TABLE IRP_DM_XXRLB (
XXRQ DATE NULL
)
例如：
INSERT INTO IRP_DM_XXRLB(XXRQ)
VALUES(TO_DATE('2017-05-06 00:00:00','YYYY-MM-DD HH24:MI:SS'))
GO
INSERT INTO IRP_DM_XXRLB(XXRQ)
VALUES(TO_DATE('2017-05-07 00:00:00','YYYY-MM-DD HH24:MI:SS'))
GO
这里面的时间全部都是非工作日（周六周日、放假日）
CREATE OR REPLACE FUNCTION WK_HOURS_BETWEEN (i_startTime in varchar2,i_endTime in varchar2)
return number
is
v_real_startTime date;--开始时间 变量
v_real_endTime date;--结束时间 变量
v_hours number(18,0);--计算结果
v_number number(18,4);
begin
--格式转换
v_real_startTime := to_date(i_startTime,'YYYY-MM-DD HH24:MI:SS');
v_real_endTime := to_date(i_endTime,'YYYY-MM-DD HH24:MI:SS');
--开始时间及结束时间转换
if v_real_startTime > v_real_endTime -- 如果起始时间大于结束时间，将起始时间与结束时间替换
then
select v_real_startTime, v_real_endTime into v_real_endTime, v_real_startTime from dual;
end if;
if v_real_startTime<trunc(v_real_startTime,'dd')+9/24 -- 如果小于当天9点，将其置为当天9点(因为你是9点上班)
then
v_real_startTime:=trunc(v_real_startTime,'dd')+9/24;
/*-- 如果大于当天12点，且小于当天14点，将其置为当天14点(因为你下午是14点上班)
elsif v_real_startTime>trunc(v_real_startTime,'dd')+12/24 and v_real_startTime<trunc(v_real_startTime,'dd')+14/24
then
v_real_startTime:=trunc(v_real_startTime,'dd')+14/24;*/
-- 如果大于当天18点，将其置为第二天9点
elsif v_real_startTime>trunc(v_real_startTime,'dd')+18/24
then
v_real_startTime:=trunc(v_real_startTime+1,'dd')+9/24;
end if;
-- 如果小于当天9点，将其置为昨天18点
if v_real_endTime<trunc(v_real_endTime,'dd')+9/24
then
v_real_endTime:=trunc(v_real_endTime-1,'dd')+18/24;
/* -- 如果大于当天12点，且小于当天14点，将其置为当天12点(因为你上午是12点下班)
elsif v_real_endTime>trunc(v_real_endTime,'dd')+12/24 and v_real_endTime<trunc(v_real_endTime,'dd')+14/24
then
v_real_endTime:=trunc(v_real_endTime,'dd')+12/24;*/
elsif v_real_endTime>trunc(v_real_endTime,'dd')+18/24 -- 如果大于当天18点，将其置为当天18天(因为你是18点下班)
then
v_real_endTime:=trunc(v_real_endTime,'dd')+18/24;
end if;
--with tem as 语句 将后面语句查询结果赋值于tem 使用于多次查询的语句，可简化
with a as( select v_real_startTime+(level-1)/24 as cdate,
trunc(v_real_startTime+(level-1)/24,'hh') as tr_cdate
from dual
connect by level <= (v_real_endTime-v_real_startTime)*24+2 ),--connect by level 递归查询 按小时对时间进行拆分
--对a中内容进行筛选，排除非工作日的 to_char 'D' 得到所在所在周内的第几天
b as( select cdate, tr_cdate cdate2 from a where trunc(a.cdate) not in (select XXRQ from IRP_DM_XXRLB) and to_char(a.cdate,'D') not in ('1','7') ), -- 排除周六、日 和 节假日
--对b中时间点进行转换 主要以工作时间09——18来进行
c as( select (case when to_char(t1.cdate,'hh24') in ('18') then trunc(t1.cdate,'hh') else t1.cdate end) as cdate1, --超过18点的按18点
(case when to_char(t1.cdate2,'hh24') in ('09') then trunc(t2.cdate+1/24,'hh24') else t2.cdate end) as cdate2 --超过9点的按9点
from b t1
left join b t2 on trunc(t1.cdate,'hh24')=trunc(t2.cdate+1/24,'hh24')
order by (case when to_char(t1.cdate,'hh') in ('09','18') then trunc(t1.cdate,'hh') else t1.cdate end) ),
--上一步将超过9点和18点的转换成了整点，此步将整点的字段值信息进行调整
--对c表中信息进行筛选，选取在工作时间点上的来计算
d as ( select (case when c.cdate1>v_real_endTime then v_real_endTime
else c.cdate1 end) as cdate1,
c.cdate2
from c
where to_char(c.cdate1,'hh24') in ('10','11','12','13','14','15','16','17','18') )
--*1440为时间进度计算到分钟
select nvl(sum(d.cdate1-nvl(d.cdate2,d.cdate1))*24,0) into v_number from d;
return v_number;
end;
案例：
select WK_HOURS_BETWEEN('20170505 15:00:00','20170508 10:00:00') as 时间差 from dual
20170505 周五 20170508周一 工作日期按照从上午9：00到下午16：00 最后算出来相差四个小时工作时长
结果：
3.对于一个表中某个字段的拆分函数
--建表类型 这个首先数据库直接运行一下
create or replace type str_split as table of VARCHAR2(32676);
-----拆分字段函数 这个函数需要在编辑器中创建函数窗口里面建
CREATE FUNCTION SPLITSTR(p_string IN VARCHAR2, p_delimiter IN VARCHAR2)
RETURN str_split
PIPELINED
AS
v_length NUMBER := LENGTH(p_string);
v_start NUMBER := 1;
v_index NUMBER;
BEGIN
WHILE(v_start <= v_length)
LOOP
v_index := INSTR(p_string, p_delimiter, v_start);
IF v_index = 0
THEN
PIPE ROW(SUBSTR(p_string, v_start));
v_start := v_length + 1;
ELSE
PIPE ROW(SUBSTR(p_string, v_start, v_index - v_start));
v_start := v_index + 1;
END IF;
END LOOP;
RETURN;
END splitstr;
GO
测试案例：
这样就根据逗‘，’来切割n份，一行转成了n行了
浙公网安备 33010602011771号