Re: PL/SQL function [8.0.5]

Here's a quick test:

create or replace function total_compensation ( empno_in in emp.empno%type ) return number is

   retval number;
begin

   variables.pass := variables.pass + 1;    dbms_output.put_line('('||to_char(variables.pass) || ') empno='||empno_in);

--
   select sal + nvl(comm,0)
     into retval
     from emp
    where empno=empno_in;
   return (Retval);
end;
/


create or replace package variables as
    pass number := 0;
end;
/

rem comp_query2.sql
select empno, ename, job, total_Compensation(empno), deptno
from emp
where total_compensation(empno)>2500
/

SQL> select * from emp where sal+nvl(comm,0) > 2500;

    EMPNO ENAME      JOB             MGR HIREDATE         SAL      COMM
DEPTNO
--------- ---------- --------- --------- ---------- --------- --------- ----
-----
     7566 JONES      MANAGER        7839 04/02/1981      2975
20
     7654 MARTIN     SALESMAN       7698 09/28/1981      1250      1400
30
     7698 BLAKE      MANAGER        7839 05/01/1981      2850
30
     7788 SCOTT      ANALYST        7566 12/09/1982      3000
20
     7839 KING       PRESIDENT           11/17/1981      5000
10
     7902 FORD       ANALYST        7566 12/03/1981      3000
20

6 rows selected.

sql> exec variables.pass := 0;

PL/SQL procedure successfully completed.

SQL> _at_comp_query2

    EMPNO ENAME      JOB       TOTAL_COMPENSATION(EMPNO)    DEPTNO
--------- ---------- --------- ------------------------- ---------
     7566 JONES      MANAGER                        2975        20
     7654 MARTIN     SALESMAN                       2650        30
     7698 BLAKE      MANAGER                        2850        30
     7788 SCOTT      ANALYST                        3000        20
     7839 KING       PRESIDENT                      5000        10
     7902 FORD       ANALYST                        3000        20

6 rows selected.

SQL> exec dbms_output.put_line('flush buffer');


(1) empno=7369

(2) empno=7499

(3) empno=7521

(4) empno=7566

(5) empno=7566

(6) empno=7654

(7) empno=7654

(8) empno=7698

(9) empno=7698

(10) empno=7782

(11) empno=7788

(12) empno=7788

(13) empno=7839

(14) empno=7839

(15) empno=7844

(16) empno=7876

(17) empno=7900

(18) empno=7902

(19) empno=7902


(20) empno=7934


flush buffer

PL/SQL procedure successfully completed.

SQL> select count(*) from emp;

 COUNT(*)
---------
       14

iks <oopsik_at_spoczta.onet.pl> wrote in message
news:8uuilr$oj3$1_at_orfika.office.polbox.pl...
> Hi,
>
> I have pl/sql function, so I can use it in sql statements like built-in
> funtion.
> SQL statement is like following:
>
> select key, function(key) fun_value from table
>  where function(key) > 10
>
> My question is: how many times oracle calls this function
> for each record: once or twice ?
> I cannot use:
> select key, function(key) fun_value from table
>  where fun_value > 10
>
> I have oracle 8.0.5.
>
> Tia,
>
> x
>
>