Oracle Deterministic Keywords

See deterministic many times, have been very puzzled, today did an experiment. We cheat Oracle to say it is a deterministic function, which is called only once in SQL. If you do not use deterministic, you can see that the values are different. After using deterministic, different sessions come out with the same value.

sql> Create or Replace function f_t (i_p int) return number deterministic is
I_RTN number;
Begin
I_RTN: = i_p * Dbms_random.value (1,10);
return I_RTN;
End
/
The function has been created.

Sql> Select level,f_t (1) from DUAL CONNECT by level<=10;
Level f_t (1)
---------- ----------
1 2.55732959
2 2.55732959
3 2.55732959
4 2.55732959
5 2.55732959
6 2.55732959
7 2.55732959
8 2.55732959
9 2.55732959
10 2.55732959
10 rows have been selected.

sql> Create or Replace function f_t (i_p int) return number is
I_RTN number;
Begin
I_RTN: = i_p * Dbms_random.value (1,10);
return I_RTN;
End
/
The function has been created.

Sql> Select level,f_t (1) from DUAL CONNECT by level<=10;
Level f_t (1)
---------- ----------
1 8.48649118
2 8.9396978
3 2.2786135
4 5.29205905
5 5.32847713
6 8.70095819
7 6.20471031
8 2.00101537
9 3.53814265
10 3.64991086
10 rows have been selected.

Oracle Deterministic Keywords