|
PL/SQL functions returns a scalar value and PL/SQL procedures return nothing. Both can take zero or more number of parameters as input or output. The special feature about PL/SQL is that a procedure/function argument can be of input (indicating the argument is read-only), output (indicating the argument is write-only) or both (both readable and writable).
A D V E R T I S E M E N T
Functions in PL/SQL are a collection of SQL and PL/SQL statements that perform a task and should return a value to the calling environment.
CREATE OR REPLACE FUNCTION <<function_name>> [(input/output variable declarations)] RETURN return_type
<<IS|AS>>
[declaration block]
BEGIN
<<PL/SQL block WITH RETURN statement>>
[EXCEPTION
EXCEPTION block]
END;
Procedures are the same as Functions, in that they are also used to perform some task with the difference being that procedures cannot be used in a SQL statement and although they can have multiple out parameters they do not return a value.
Procedures are traditionally the workhorse of the coding world and functions are traditionally the smaller, more specific pieces of code. In general, if you need to update the chart of accounts, you would write a procedure. If you need to retrieve the organization code for a particular GL account, you would write a function.
Here are a few more differences between a procedure and a function:
- A function MUST return a value
- A procedure cannot return a value
- Procedures and functions can both return data in OUT and IN OUT parameters
- The return statement in a function returns control to the calling program and returns the results of the function
- The return statement of a procedure returns control to the calling program and cannot return a value
- Functions can be called from SQL, procedure cannot
- Functions are considered expressions, procedure are not
|
