Since Oracle 12, it's possible to include a function declaration right in your query. You define it much like a normal function, but it is included right after the "WITH" clause; and it is only available in the attached query that follows.
In the following examples, Note these details: 1- The function needs to immediately follow the "WITH" keyword. 2- The PL/SQL code that defines the function ends with a semicolon. 3- Other "WITH" clause subqueries can follow the function declaration. 4- The query can be terminated with a semicolon; but ... 5- To run it in a SQL-Developer like tool, you may need a terminating slash "/".
The following is an example of a function that uses the 'calculator' like functionality of the "SELECT" to evaluate an expression. If the expression or 'numeric value' can be represented as a number, then that value is returned. If not, then the 'exception' handler in the function returns a "NULL". The 'table' being queried is built into the "WITH" clause in the example below.
WITH
FUNCTION to_nmbr(f_exp in varchar2)
return number
is
v_num number ;
BEGIN
execute immediate 'select '||f_exp||' from dual'
into v_num ;
return v_num ;
exception
when others then return NULL ;
END ;
-----
w_data as
( select null as id, null as val from dual where 1=2
----------------------------------------------------
union all select 1, ' 218.95 ' from dual
union all select 2, ' +56 ' from dual
union all select 3, ' -84 ' from dual
union all select 4, ' 4.549e6 ' from dual
union all select 5, ' +(((4.8))) ' from dual
union all select 6, ' 13+7+9-15 ' from dual
union all select 7, ' 40-2*(11-2) ' from dual
union all select 8, ' -(123) ' from dual
union all select 9, ' -(5) - -(7) ' from dual
union all select 10, NULL from dual
union all select 11, ' words ' from dual
union all select 12, ' a 721 ' from dual
union all select 13, ' 2 3 ' from dual
union all select 14, ' 3 - -2 ' from dual
union all select 15, ' -321.7e-5 ' from dual
----------------------------------------------------
)
select id, val, to_nmbr(val) as rslt
from w_data
order by 1 ;
/
The following function tests a date value stored in a text string against a given format string. If the date evaluates against the given format, then the DATE value is returned; if not, then it returns "NULL". In the SQL query, the 'coalesce()' tests several format strings for each date; the first successfull one is returned as the 'best-guess' DATE value.
WITH
FUNCTION try_dt
( f_dt in varchar2
, f_fmt in varchar2
) return date is
v_dt date ;
begin
v_dt := to_date( f_dt, f_fmt ) ;
return v_dt ;
exception when others then return NULL ;
end ;
select id
, dt
, coalesce
( try_dt(dt, 'YYYY-MM-DD')
, try_dt(dt, 'YYYY-Mon-DD')
, try_dt(dt, 'MM-DD-YYYY')
, try_dt(dt, 'DD-MM-YYYY') ) as best_dt_guess
from ( select null as id, null as dt from dual where 1=2
-------------------------------------------------
union all select 1, '2018-Oct-17' from dual
union all select 2, '1949-01-05' from dual
union all select 3, '4/25-2004' from dual
union all select 4, '25/4/2018' from dual
union all select 5, '3-7-1984' from dual
union all select 6, '11.5.1978' from dual
union all select 7, '2:22:2021' from dual
union all select 8, '2020-2-30' from dual
)
order by 1
/
One last example. This query has a built in 'Multi-Replace' function, allowing a Replace() like functionality but with lists of search/replace values.
WITH
---------- /* Function Definition */
function replace_mltpl
( f_txt varchar2
, f_srch varchar2
, f_rplc varchar2
, f_dlim char default ','
)
RETURN VARCHAR2
AS
v_str varchar2(4000) ;
v_cnt integer ;
v_exp varchar2(20) ;
v_sep char := f_dlim ;
BEGIN
v_str := f_txt ;
v_cnt := regexp_count(f_srch, '\'||v_sep)+1 ;
v_exp := '\'||v_sep||'?([^\'||v_sep||']*)' ;
for n in 1..v_cnt loop
v_str := regexp_replace
( v_str
, regexp_substr(f_srch, v_exp, 1, n, 'i', 1)
, '®¤'||n||'¤®'
) ;
end loop ;
for n in 1..v_cnt loop
v_str := regexp_replace
( v_str
, '®¤'||n||'¤®'
, regexp_substr(f_rplc, v_exp, 1, n, 'i', 1)
) ;
end loop ;
RETURN v_str ;
END ;
---------- /* Sample Data */
w_data as
( select NULL as txt
, NULL as srch
, NULL as rplc
, NULL as dlm
from dual where 1=2
union all select 'A quick brown fox jumps over the lazy dog.'
, 'fox~dog~lazy~quick' , 'dog~fox~quick~lazy'
, '~'
from dual
union all select 'A quick brown fox jumps over the lazy dog.'
, 'fox~dog~lazy~quick' , 'walaby~dingo~mangy~agile'
, '~'
from dual
union all select 'a~b=c; x-y~z; 2=3~4; e=f-g'
, '~|=|-|;| ' , '=|-|~| |/'
, '|'
from dual
union all select 'one two three; four five six; seven 8 9'
, '; , ,e' , ': ,-,E'
, ','
from dual
)
---------- /* Query */
select txt, srch, rplc, dlm
, replace_mltpl( txt, srch, rplc, dlm ) as rslt
from w_data
/
No comments:
Post a Comment