ORACLE SQL, PL/SQL: ORACLE Stored Procedure Vs. Function

What is the Difference between Functions and Procedures? How ORACLE makes the difference while referencing?

1. A function always returns a value using the return statement (and can return multiple values with OUT parameter as well) while a procedure may return one or more values through parameters (Max 1024) or may not return at all.
Although, OUT parameters can still be used in functions, they are not advisable neither are there cases where one might find a need to do so. Using OUT parameter restricts a function from being used in a SQL Statement.

2. Functions can be used in typical SQL statements like SELECT, INSERT, UPDATE, DELETE, MERGE, while procedures can't. So, Function can be called from SQL statement whereas procedure can't be called from the SQL statement.

3. Functions are normally used for computations whereas procedures are normally used for executing business logic.

4. Procedure can perform one or more tasks whereas function performs a specific task.

5. We can use DDL in Procedure using Execute Immediate statement while that is not possible in functions(When a function is called from within a query or DML statement)

6. Oracle provides the provision of creating "Function Based Indexes" to improve the performance of the subsequent SQL statement. This applies when performing the function on an indexed column in where clause of a query.

7. Stored procedure returns always integer value by default zero. whereas function returns type could be scalar or table or table value

8. Stored procedure uses a precompiled execution plan whereas function compiles every time we call it.

9. Exception can be handled by try-catch block in a Procedure whereas try-catch block cannot be used in a Function.

10. We can go for Transaction Management in Procedure whereas we can't go in functions(When a function is called from within a query or DML statement)

11. 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 runtime throws error Function won’t support deferred name resolution.

Get involved and leave your Comments in the Box Below. The more people get involved, the more we all benefit. So, leave your thoughts before you leave the page.

5 comments:

UnknownAugust 2, 2018 at 2:10 AM
Hi Ravi,

M going though article ,nice post but I've objection with point 5 that inside function we cant create DDL ,wrong we can

simple sample m writing
create or replace function FUNC_DDL_dync_chk RETURN VARCHAR2 IS
lv_module_name VARCHAR2(10) := 'Module 2';
BEGIN
execute immediate 'Create table ABC_dual as select 1 as c from dual';
RETURN 1;
END;
/

calling
declare
v1 varchar2(500);
begin
v1:=FUNC_DDL_dync_chk;
end;

I knew its not a right practice but yes we cab do
UnknownAugust 2, 2018 at 2:14 AM
even point number 10 is also confusing ,did you checked practically..we can do transaction management through functions also which is nt a good practice but yes we can do
preetyFebruary 8, 2022 at 2:52 AM
Very glad to see this post.thank alot nice information.
Here you can get more info about oracle fusion courses.

Oracle Fusion SCM Online Training

Oracle Fusion HCM Online Training

Oracle Fusion Financials Online Training

Oracle Fusion Technical Training
AbhishekJune 15, 2022 at 3:46 PM
Hello Sir,
Thank you for the informative blog.
I have objection on point 11, deferred name resolution is not possible in case of procedures as well, Oracle checks the objects at compile time itself.

Translate

ORACLE Stored Procedure Vs. Function

5 comments: