Procedures vs Functions in Programming

Procedures and Functions in
programming, allow programmers to group instructions together in a
single block and it can be called from various places within the
program. The code becomes easier to understand and more compact. By
performing the modifications in a single place, the whole code will get
affected. With the help of functions and procedures; a linear and long
code can be divided into independent sections. They provide more
flexibility to the coding of various programming languages and
databases.

What are functions?

Functions are capable of accepting
parameters which are also known as arguments. They carry out the tasks
according to these arguments or parameters and return values of given
types. We can explain it better with the help of an example: A function
accepts a string as a parameter and returns the first entry or record
from a database. It takes into account the content for a specific field
that begins with such characters.

The syntax of function is as follows:

CREATE OR REPLACE FUNCTION my_func

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

What are procedures?

Procedures can accept the parameters or
arguments and they carry out tasks as per these parameters. If a
procedure accepts a string as a parameter and it gives out a list with
records in database for which content of a specific field begin with
such characters.

The syntax of procedures is as follows:

CREATE OR REPLACE PROCEDURE my_proc

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

Mainly, there are two ways by which a
parameter is passed in functions and procedures; by value or by
reference. If parameter is passed by a value; the modification is
affected within the function or procedure without affecting the actual
value of it.

On the other hand, if the parameters are
passed by references; the actual value of this parameter will be
changed wherever it is called within the code as per the instructions.

Difference between procedures and functions • When the parameter is passed into the procedure; it does not return any value whereas a function always returns a value. • One of the major differences in both of them is that procedures are not used in databases whereas functions play an important role in returning values from a database. • Procedures are capable of returning multiple values and the functions are able to return limited values. • DML operations can be used in stored procedures; however, they are not possible in functions. • Functions can return only one value and it is mandatory whereas procedures can return n or zero values. • In functions, error handling cannot be done whereas it can be performed in stored procedures. • Input and output parameters can be passed in procedures whereas in case of functions; only input parameters can be passed. • Functions can be called from procedures whereas it is not possible to call a procedure from a function. • Transaction management can be considered in procedures and it cannot be considered in case of functions.