Procedures vs. Functions

Difference between procedures and functions in programming

If you have dealt with programming in computers then you must have come across the words Procedures and Functions. Procedures and Functions are grouped set of instructions in a program. These small groups of instructions are easy to understand especially if the program is too big. Procedures and Functions divide the long linear codes into small independent sections which make coding flexible.

Functions accept arguments and they carry out the task according to these arguments and return values. For example a function accepts a string and returns a record from a database. It considers the content of the field.

The syntax of function is as follows:

CREATE OR REPLACE FUNCTION my_func

(p_name IN VARCHAR2 := ‘Jack’) return varchar2 as begin … end

Procedures can also accept arguments and carry out the tasks in accordance with these arguments or parameters. If you want a procedure to accept a string as an argument then it will give out the list with all the records from the database by considering the content in each field.

The syntax of procedures is as follows:

CREATE OR REPLACE PROCEDURE my_proc

(p_name IN VARCHAR2 := ‘Jack’) as begin … end

The parameter passes a function or a procedure by value or by reference. If it is passed by the value then the value remains unaffected while if it is passed by reference then the actual value of the argument will be affected everywhere it will be called in the code.

Differences between procedures and functions are given here for better understanding:

  • When a parameter or argument is passed through a function, it returns a value but not in case of procedure.
  • Functions can return vales from databases while procedures are not used in databases.
  • Functions can return limited values while procedure can return multiple values.
  • DML operations cannot be used for storing functions while they can be for the procedures.
  • Functions return only one value mandatorily while it is not so in case of procedures. They can return zero values as well.
  • You can perform error handling in stored procedures while you cannot do this in case of functions.
  • In case of functions you can pass only the input parameters or arguments while you can pass input and output arguments in case of procedures.
  • You can call functions from procedures but it is not possible vice versa.
  • Transaction management is possible only in procedures.