Oracle PL/SQL: Diff. b/w. Function & Procedure.

Difference between function and stored procedure in Oracle with examples

Functions and stored procedures are set of SQL statements that can be called by name. They have many similarities like they take arguments, used to perform required tasks and have same programming style and structure. But we are more interested in differences to grab the knowledge about power they held against each other. So here are some difference between function and stored procedure in Oracle point of view with examples.

Function

Purpose of function

Function is written for a specific task or computations

Return a value

Function return at least one value of any data type. More values can be returned using OUT parameters

CREATE OR REPLACE FUNCTION function_name RETURN datatype…

Call from SQL statements (Insert, Update, Delete…)

Functions can be called from SQL statements

SELECT function_name() FROM table_name;

Click here to learn history of SQL

DML Statements

DML Statements (Insert, Update, Delete…) can be called inside functions but that function cannot be used in SQL statements

Execution of Functions

Functions are executed in following ways:

• Inside an SQL query (E.g. Select function_name(parameters) from dual)
• Using assignment variables (E.g. var := function_name(parameters); )

Stored Procedure

Purpose of procedure

Procedure is written to execute a business logic

Return a value

Procedure may or may not return a value. More values can be returned using OUT parameters (Maximum: 1024)

CREATE OR REPLACE PROCEDURE procedure_name …

Call from SQL statements (Insert, Update, Delete…)

Procedures cannot be called from SQL statements

DML Statements

DML Statements can be called inside procedures. You can call that procedure inside other procedures

Execution of Procedures

Procedures are executed in following ways:

• In Begin…End blocks

Begin
 Procedure_name(parameters);
End;

• Using Execute command ( E.g. Execute procedure_name(parameters); )
• Calling procedures (E.g. procedure_name(parameters);)

If you liked the above post put your comments below. Your suggestions and feedback are valuable