stephen O'D a écrit :
> One way, is to turn your function into a package like this:-
>
> create or replace package countit
>
> is
>
> function my_func (i_p1 in varchar2)
> return integer;
>
> procedure display_it;
>
> procedure reset_it;
>
> end countit;
> /
>
> create or replace package body countit
>
> is
>
> v_call_count integer default 0;
>
> function my_func (i_p1 in varchar2)
> return integer
> is
> begin
> v_call_count := v_call_count + 1;
>
> -- do whatever it is you want todo
>
> return 1;
> end my_func;
>
> procedure display_it
> is
> begin
> dbms_output.put_line ('Has been called '||to_char(v_call_count));
> end display_it;
>
> procedure reset_it
> is
> begin
> v_call_count := 0;
> end reset_it;
>
> end countit;
> /
>
> 1 select * from
> 2 (
> 3 select 1 a
> 4 from dual
> 5 connect by level <= 5
> 6 )
> 7* where a = countit.my_func (1)
> SQL> /
>
> A
> ----------
> 1
> 1
> 1
> 1
> 1
>
> SQL> set serveroutput on
> SQL> exec countit.display_it;
> Has been called 5
>
> PL/SQL procedure successfully completed.
>
> SQL> exec countit.reset_it;
>
> PL/SQL procedure successfully completed.
>
> SQL> exec countit.display_it;
> Has been called 0
>
> PL/SQL procedure successfully completed.
>
> SQL>
>
> I am going to be bold and guess you are wanting to count the times the
> function has been called as you are trying to reduce the number of
> function calls to make things faster etc. If I change my query about
> slightly (to turn the subquery into a scalar sub query) then Oracle
> caches the function call results, which could be a good win for you:-
>
>
> 1 select * from
> 2 (
> 3 select 1 a
> 4 from dual
> 5 connect by level <= 5
> 6 )
> 7* where a = (select countit.my_func (1) from dual)
> SQL> /
>
> A
> ----------
> 1
> 1
> 1
> 1
> 1
>
> SQL> exec countit.display_it
> Has been called 1
>
> PL/SQL procedure successfully completed.
>
Thanks. It works fine.
I didn't know globals variables for packages.
Received on Wed Aug 10 2005 - 08:07:35 CDT
