Oracle
Allen 2024-01-25 0 1844 0 0 0 0
Oracle函数,Oracle基础,Oracle创建自定义函数,自定义函数赋权限,自定义函数创建同义词,格式化字符串时间

创建自定义函数

格式化字符串时间,如将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;

Tag: Oracle函数 Oracle基础
我也要发一个   ·   返回首页   ·   返回[Oracle]   ·   前一个   ·   下一个
欢迎评论
未登录,
请先 [ 注册 ] or [ 登录 ]
(一分钟即可完成注册!)
返回首页     ·   返回[Oracle]   ·   返回顶部