PL/SQL “Do’s” 3 & 4
uUse the NOCOPY command
ØWhen passing an IN/OUT parameter to avoid passing by value.
ØNOCOPY is a hint, not a directive so Oracle may ignore it.
ØIf passing a variable by value to a subroutine and that routine fails, the original value of the variable does not change.
uBe
careful of function calls that impact
performance
ØIn
loops
§Perform the calculations before the loop
§Cache the calculated data in the local variables
ØA function in an SQL statement will execute at least once for every row returned.
§Look
closely at the execution plan
