Difference between Procedure and Function ?

Procedure

  • A procedure is a named PL/SQL block which performs one or more specific task.This is similar to a procedure in other programming languages.
  • A procedure has a header and a body.
  • The header consists of the name of the procedure and the parameters or variables passed to the procedure.
  • The body consists declaration section, execution section and exception section similar to a general PL/SQL Block. A procedure is similar to an anonymous PL/SQL Block but it is named for repeated usage.

We can pass parameters to procedures in three ways

Parameters Description
IN type These types of parameters are used to send values to stored procedures
OUT type These types of parameters are used to get values from stored procedures. This is similar to a return type in functions.
IN OUT type These types of parameters are used to send values and get values from stored procedures.

Syntax

CREATE [OR REPLACE] PROCEDURE procedure_name (<Argument> {IN, OUT, IN OUT}    <Datatype>,…)  
IS 
  Declaration section<variable, constant> ; 
BEGIN 
  Execution section 
EXCEPTION 
  Exception section  
END

Functions

  • A function is a named PL/SQL Block which is similar to a procedure.
  • The major difference between a procedure and a function is, a function must always return a value, but a procedure may or may not return a value.

Syntax

CREATE [OR REPLACE] FUNCTION function_name [parameters]  
 RETURN return_datatype;  {IS, AS} 
 Declaration_section <variable,constant> ;
 BEGIN   
    Execution_section 
    Return return_variable;   
 EXCEPTION 
   exception section   
    Return return_variable;  
 END;

Procedure vs function

Procedure Function
Used mainly to a execute certain process. Used mainly to perform some calculation.
Cannot call in SELECT statement. A Function that contains no DML statements can be called in SELECT statement.
Use OUT parameter to return the value. Use RETURN to return the value.
It is not mandatory to return the value. It is mandatory to return the value.
RETURN will simply exit the control from subprogram. RETURN will exit the control from subprogram and also returns the value.
Return datatype will not be specified at the time of creation. Return datatype is mandatory at the time of creation.

Categorized in:

Oracle

Tagged in:

, , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , ,