创建自定义函数

格式化字符串时间，如将20201102080808，格式化为2020-11-02 08:08:08

--创建函数，格式化字符串时间
create or replace function sydba.hsdt2dt(para1 in varchar2)
return varchar2 is
begin
	IF para1 is null THEN
	    return '1';
	elsif para1='' then
	    return '2';
	else
	    return to_date(para1,'yyyymmddhh24miss')
	end if;
end;

以上代码参数为空时不起作用！

询问AI回复如下：

这个函数的逻辑可能有些问题，我来帮你修改一下：
 CREATE OR REPLACE FUNCTION sydba.hsdt2dt(para1 IN VARCHAR2)
 RETURN VARCHAR2 AS
BEGIN
    RETURN (CASE 
              WHEN para1 IS NULL THEN '1'
              WHEN para1 ='' THEN '2'
              ELSE to_date(para1,'yyyymmddhh24miss')
         END);
END;
这个函数首先判断para1是否为空或为NULL，如果是，则返回字符串1。然后判断para1是否为空字符串，如果是，则返回字符串2。如果以上两种情况都不满足，则将字符串para1转换为日期类型，并返回该日期字符串。

为什么不用if语句实现

在 SQL 中不建议使用IF-THEN-ELSE语句，因为它们不是 SQL 的核心语言。
SQL 主要用于数据查询和操作，不支持编程语言中的条件语句。因此，在 SQL 中最好使用CASE-WHEN-ELSE结构来实现条件判断。

优化为以下代码：

create or replace function sydba.hsdt2dt(v_str varchar2) 
 return varchar2 as 
begin 
  return(case when v_str is null or v_str = '' then '' 
            else to_char(to_date(v_str,'yyyymmddhh24miss'),'yyyy-mm-dd hh24:mi:ss') 
        end); 
end;

使用自定义函数

自定义函数创建同义名和赋权限

--创建同义名
CREATE OR REPLACE PUBLIC SYNONYM hsdt2dt FOR sydba.hsdt2dt ;

--直接将函数赋权限
--grant execute on sydba.hsdt2dt to ROLE_APP1;
--Revoke execute on sydba.hsdt2dt from ROLE_APP1;

--直接对同义名赋权，试验可用没问题，这样赋权比较方便
grant execute on hsdt2dt to ROLE_APP1;
Revoke execute on hsdt2dt from ROLE_APP1;

这样就可以直接使用了

select tk.task_id,
sydba.hsdt2dt(tk.entry_when) as 呼叫时间,
sydba.hsdt2dt(tk.update_when) as 派单时间,
hsdt2dt(tk.start_time) as 接单时间, --不带所属也可以
hsdt2dt(tk.stop_time) as 完成时间, --不带所属也可以
tk.update_when-tk.entry_when,
tk.start_time-tk.update_when,
tk.stop_time-tk.start_time
from vedba.vetask tk
--where task_id=84
order by task_id desc;

但是值为空时，怎么排除，还没找到方法！

上面的函数，判断空值无效。。。。

已经解决，见上面第二个代码！

结果：
SQL> select hsdt2dt('20210812160808') from dual;
HSDT2DT('20210812160808')
--------------------------------------------------------------------------------
2021-08-12 16:08:08

SQL> select hsdt2dt('') from dual;
HSDT2DT('')
--------------------------------------------------------------------------------

参考：
SQL> select to_char(sysdate,'yyyy-mm-dd hh24:mi:ss') from dual;
TO_CHAR(SYSDATE,'YYYY-MM-DDHH2
------------------------------
2024-01-26 11:33:22

SQL> select to_date('20210812160808','yyyymmddhh24miss') from dual;
TO_DATE('20210812160808','YYYY
------------------------------
2021/8/12 16:08:08

扩展：创建三个函数

--hsdt2dt: 20231025110655-->2023-10-25 11:06:52
create or replace function sydba.hsdt2dt(v_str varchar2) 
 return varchar2 as 
begin 
    return(case when v_str is null or v_str = '' then '' 
        else to_char(to_date(v_str,'yyyymmddhh24miss'),'yyyy-mm-dd hh24:mi:ss') 
    end); 
end;
/
CREATE OR REPLACE PUBLIC SYNONYM hsdt2dt FOR sydba.hsdt2dt ;
grant execute on hsdt2dt to ROLE_APP1;


--hsdt2dd: 20231025110655-->2023-10-25
create or replace function sydba.hsdt2dd(v_str varchar2) 
 return varchar2 as 
begin 
    return(case when v_str is null or v_str = '' then '' 
        else to_char(to_date(v_str,'yyyymmddhh24miss'),'yyyy-mm-dd') 
    end); 
end;
/
CREATE OR REPLACE PUBLIC SYNONYM hsdt2dd FOR sydba.hsdt2dd ;
grant execute on hsdt2dd to ROLE_APP1;


--hsdt2tt: 20231025110655-->11:06:52
create or replace function sydba.hsdt2tt(v_str varchar2) 
 return varchar2 as 
begin 
    return(case when v_str is null or v_str = '' then '' 
        else to_char(to_date(v_str,'yyyymmddhh24miss'),'hh24:mi:ss') 
    end); 
end;
/
CREATE OR REPLACE PUBLIC SYNONYM hsdt2tt FOR sydba.hsdt2tt ;
grant execute on hsdt2tt to ROLE_APP1;