I don't know if it is the same for you but I tend to forget certain details, that's why often I need to write down some examples in this place to save time for the future.
For instance one thing that I tend to forget is the order in which you can specify functions and subqueries inside the same WITH.
Functions must come first, then subqueries, if any.
Here is a working example, two functions, two subqueries and the final SELECT.
with -- first function function msg ( msgnum in integer) return varchar2 as s varchar2(200); i pls_integer; begin i := utl_lms.get_message(msgnum, 'rdbms', 'ora', 'italian', s); return 'ORA-'||to_char(msgnum,'TM9')||': '||s; end; -- second function function msg2 ( msgnum in integer) return varchar2 asAccording to a performance comparison made by Tim Hall, inline functions seems to be faster than equivalent ordinary functions unless PRAGMA UDF has been specified in the latter, in which case ordinary functions seem to outperform the inline ones.
s varchar2(200); i pls_integer; begin i := utl_lms.get_message(msgnum, 'rdbms', 'ora', 'italian', s); return 'ORA-'||to_char(msgnum,'TM9')||': '||s; end; -- some subqueries x as (select 13367 as a from dual ), y as (select 13368 as a from dual) -- final query select msg(a) from x union all select msg2(a) from y;
Adding PRAGMA UDF to inline functions doesn't seem to make any difference.
No comments:
Post a Comment