They all execute at same speed, but it will be faster if you get rid of dead code and (if possible) implement the logic in SQL itself.
AFAIK, there are NO speed breaks designed for pl/sql code, only YOU (means the developer) creates them by writing inefficient code. Oracle (when executing) doesn't know if you have written good or bad code, it is merely an code executioner not a code tuner, to do the later part you need good understanding and experience.
1. do you want to execute a pl/sql block as a statement?
2. Do you want to return more than once value from the pl/sql block?
3. If you are returning only one value from the block, is the data type of the return value a SQL type or a PL/SQL type?
4. You do not wish to use this pl/sql block in SQL
Go for a procedure
1. if you wish to use your pl/sql block in SQL statements
2. You return only one value which is a SQL type
3. your pl/sql block confirms to purity requirements
Go for a functions
There is NO other difference. Procedure and function are same except that function must return a value which is assigned to a variable, and is always on the LHS of the statement or the test case. Whereas a procedure may or may not return zero or more parameters (remember not return values) and is executed as a statement, i.e. a procedure can never be used in an assignment statement.
Raj
--------------------------------------------------------------------------------
Rajendra dot Jamadagni at nospamespn dot com
All Views expressed in this email are strictly personal.
QOTD: Any clod can have facts, having an opinion is an art !
*********************************************************************This e-mail message is confidential, intended only for the named recipient(s) above and may contain information that is privileged, attorney work product or exempt from disclosure under applicable law. If you have received this message in error, or are not the named recipient(s), please immediately notify corporate MIS at (860) 766-2000 and delete this e-mail message from your computer, Thank you.*********************************************************************1
