This content has been marked as final. Show 15 replies
Functions are generally restricted to returning a single value, while procedures can have multiple OUT parameters. There are situations where functions are more useful in queries (i.e. you want to create your own function that can be used in a query like the standard functions UPPER, etc.). In some client-side languages, it's slightly easier to call stored procedures rather than stored functions.
99% of the time, I've seen people opt for stored procedures. I suspect this is more because of convention than becauseone is technically superior to the other.
Only functions return a value. Procedures can only have OUT parameters. Therefore, we can use functions in a SELECT statement but we cannot use procedures similarly.
There is a convention that procedures should be used whenever DML is required and functions should be used for "read-only" calls. Sometimes it seems as though this is more honoured in the breach than the observance.
Oracle doesn't really do "inheritance", at least not in the full-blooded way that (say) Java does.
SQL> create or replace
2 function delete_emps(dept in number) return number as
5 delete emp
6 where emp.deptno = dept;
8 return sql%rowcount;
SQL> var rowsdel number
SQL> exec :rowsdel := delete_emps(30);
PL/SQL procedure successfully completed.
SQL> print rowsdel
Er, up to a point Lord Copper.
You can't do DML in a function that is called from within a select statement.
SQL> select empno, ename from empOf course, using the AUTONOMOUS_TRANSACTION pragma in such a fashion is extremely dangerous. In fact, this is the sort of thing which makes Tom Kyte say, "I really wish I hadn't told you how to do that, because it's such a bad idea".
2 where deptno = 10
SQL> create or replace function delete_emps
2 (dept in number) return number as
3 pragma autonomous_transaction;
4 return_value number;
6 delete from emp
7 where emp.deptno = dept;
8 return_value := sql%rowcount;
10 return return_value;
SQL> select delete_emps(10) from dual
SQL> select empno, ename from emp
2 where deptno = 10
no rows selected