Tags: creating, database, deffered, following, function, link, mysql, oracle, procedure, procedures, resoultion, sql, states, stored, supports
Difference between Procedure and Function in Oracle
Does Oracle supports deffered name resoultion while creating Procedures.
In the following link, someone states that Stored Procedure supports deffered name resoultion while function not.
Leave a comment...
- 11 Comments
- I think I found the post in the thread that you are referring to and it is absolute ****.
For all practical purposes the only difference between a function and a procedure is how the two objects are called. Both are stored in a compiled format and if you try to create either when the source references objects that do not exist you will get compile time errors.
HTH -- Mark D Powell --#1; Fri, 22 Feb 2008 14:22:00 GMT
- I suppose **** does not mean 4-stars-service...
both could be defined with AUTHID DEFINERif you need to resolve names at execution time (it will be slower). So that is not the difference
A function can eventually be used in SQL#2; Fri, 22 Feb 2008 14:23:00 GMT
- Dear Laurent,
I have tried to create procedure/function with AUTHID DEFINER clause. I have refer a table in the procedure code which doesn't exist. Still error has been encountered.
I think AUTHID DEFINER clause is used to run the procedure with the rights of the procedure's owner/executing schema.
Neeraj.#3; Fri, 22 Feb 2008 14:24:00 GMT
- yes, a mistake of mines, DEFINER is the default, I meant CURRENT_USER !#4; Fri, 22 Feb 2008 14:25:00 GMT
- function - it perform some calculation and one value must be return by function.
after compiling the named function, one object is created in your data base and can be used in DML statement [ Ex : min(), max() ]
it performs fome action. it may or may not return values.
you cann't use this function in any DML statement.
but it is possible to call this named procedure from any oracle developer tools like forms, reprots,
Functions are used for computations where as procedures can be used for performing business logic
Functions MUST return a value, procedures need not be.
Function returns 1 value only.
Procedure can return multiple values(max. 1024)#5; Fri, 22 Feb 2008 14:26:00 GMT
- this is not a correct definition
a procedure return nothing, a function return a value
a function performing DML statement must be defined as AUTONOMOUS TRANSACTION
a function with OUT parameters cannot be used in sql#6; Fri, 22 Feb 2008 14:27:00 GMT
- I will go back to my statement that for all practical purposes there is little difference between a stored procedure or function since this is Oracle's official view:
From the 9.2 Concepts manual under the topic P/LSQL Program Units in ch 14, SQL, PL/SQL, and Java >> Procedures and functions are identical except that functions always return a single value to the caller, while procedures do not <<
There is no need to make the answer more complex than this.
The OP should see the referenced chapter for more details along with the PL/SQL manual.
IMHO -- Mark D Powell --#7; Fri, 22 Feb 2008 14:28:00 GMT
- To clear up any confusion on the subject of deferred name resolution mentioned in the original post (or possibly add to it), name resolution works exactly the same way for all stored code, whether procedure, function, package or type.
Referenced objects must always exist at compilation time, regardless of whether the stored code is declared AUTHID DEFINER or AUTHID CURRENT_USER. However, with AUTHID CURRENT_USER, references are resolved again for the current user at runtime and might therefore run against a different object (which can lead to some interesting bugs if a table has a different definition to the one that the code was compiled against).
I must have missed that thread on OraFAQ because it was posted on the Unix forum, otherwise I'm sure I or one of the other regulars would have posted something at the time. The OraFAQ forums are usually pretty good, but there are some very weird suggestions in that one, my favourite being Sasikumar.R's advice "Function do not return the images,text whereas sp returns all."#9; Fri, 22 Feb 2008 14:30:00 GMT
- Will, excellent followup note. -- Mark --#10; Fri, 22 Feb 2008 14:31:00 GMT
- Has any one tried returning a Images from functions/Procedures?#11; Fri, 22 Feb 2008 14:32:00 GMT