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. |