SP and Functions

[DKL01]

Hi,

I'm new to Oracle and functions in Oracle. Is it possible what I'm trying to do with function ?

Please ignore the syntax.

Thanks


Create or Replace Procedure Proc1

select EmpID, EmpName, BasicSalary, Allowance

from Employee

where GetPercent (BasicSalary, Allowance) > 10000

End Proc1

Create or Replace Function GetPercent
Return BasicSalary + Allowance;
End GetPercent

 

[Guest_imported]

Assuming that you are sending 2 parameters to the GetPercent function, doing something in the function and returning a single number value back, and using the same function in the where clause of select, your peice of code should work.

 

[Guest_imported]

Just as a warning, while the suggestion should work
because it assures the proper purity- not modifying
package states, etc, watch out for a performance
hit, at least in terms of CPU consumption if you
use functions in queries.
(especially where clauses, since you hit every row
and can't take advantage of indices.
)

 

[ija]

When you declare the function, add this piece of code:

[tt]PRAGMA RESTRICT_REFERENCES (GetPercent, WNDS, WNPS, RNDS, RNPS);[/tt]

It assures, that the conditions named by johnfuller360 are fullfilled. Compiler requires the pragma.

 

[sem]

To call it from SQL you need RNPS, WNPS only for remote (called via dblink) functions. Your function MUST have WNDS purity level. In the case of using it in WHERE clause it also must have WNPS.
In fact you do need to declare purity level only if it's packaged. Oracle is robust enough to check it for standalone function itself (AFAIK 8i may also check it during runtime, but performance may degrade).