Difference Between Stored Procedure and Function

Stored Procedure vs Function
 

Stored procedures and functions are two types of programming blocks. Both of them must have calling names. Those calling names are used to call them inside another programming block like procedures functions and packages or SQL queries. Both of these object types accept parameters and perform the task behind those objects. This is the syntax (in ORACLE) to create a stored procedure,

 create or replace procedure procedurename (parameters)

 as

 begin

     statements;

 exception

     exception_handling

 end;

And here is the syntax to create a function (in ORACLE),

create or replace function function_name (parameters)

return return_datatype

as

begin

statements;

return return_value/variable;

exception;

exception_handling;

end;

Stored Procedures

As mentioned above stored procedures are named programming blocks. They accept parameters as user input and process according to the logic behind the procedure and give the result (or perform a specific action). These parameters can be IN, OUT and INOUT types. Variable declarations, variable assignments, control statements, loops, SQL queries and other functions/procedure/package calls can be inside the body of procedures.

Functions

Functions also are named programming blocks, which must return a value using RETURN statement, and before it returns a value, its body performs some actions too (according to the given logic). Functions also accept parameters to run. Functions can be called inside the queries. When a function is called inside a SELECT query, it applies to each row of the result set of the SELECT query. There are several categories of ORACLE functions. They are,

• Single row functions (returns a single result for each and every row of the query)

There are sub categories of a single row functions.

• Numeric function (Ex : ABS, SIN, COS)
• Character function (Ex: CONCAT, INITCAP)
• Date time function (Ex: LAST_DAY, NEXT_DAY)
• Conversion functions (Ex: TO_CHAR, TO_DATE)
• Collection function (Ex: CARDINALITY, SET)
• Aggregate functions (Returns a single row, based on a group of rows. Ex: AVG, SUM, MAX)
• Analytic  functions
• Object reference functions
• Model functions
• User defined functions

What is the difference between function and Stored Procedure? • All functions must return a value using RETURN statement. Stored procedures do not return values using RETURN statement. RETURN statement inside a procedure will return its control to the calling programme. OUT parameters can be used to return values from stored procedures. • Functions can be called inside the queries, but stored procedures cannot be used inside the queries. • RETURN data type must be included to create a function, but in stored procedure DDL, it is not.