•Functions MUST return a value but procedures doesn't need to.
•You can have DML (insert,update, delete) statements inside a function. But, you cannot call such a function in a SQL query. For example, if you have a function that is updating a table, you cannot call that function from a SQL query.
select FunctionName() from tableName; will throw error.
•Function returns 1 value only. Procedure can return multiple values (maximum 1024).
•Stored procedure supports deferred name resolution. Example while writing a stored procedure that uses table named tabl1 and tabl2 etc. but actually not exists in database is allowed only in during creation but run time throws error Function wont support deferred name resolution. Stored procedure returns always integer value by default zero where as function return type could be scalar or table or table values(SQL Server). Stored procedure is precompiled execution plan where as functions are not.
•A procedure may modify an object where a function can only return a value.
Point of Information: In earlier releases of Oracle it was better to put as much code as possible in procedures rather than triggers. At that stage procedures executed faster than triggers as triggers had to be re-compiled every time before executed (unless cached). In more recent releases both triggers and procedures are compiled when created (stored p-code) and one can add as much code as one likes in either procedures or triggers.
Related Documents
Informative. I was looking for it.
ReplyDeleteAnswer is not up the mark............
ReplyDeleteonly one difference between Function and Procedure is
we cannot call a procedure in select statement but we can call function in select statement...
http://www.oracle-expert.co.cc