Procedures and Functions

A procedure in a PL/SQL structures is a sub-program that consists of a group of compiled PL/SQL statements that can be referenced through a name. And it is stored as a database object. Whereas function is a standalone PL/SQL sub-program that is referenced through its name. The main differences between procedures and functions include:

• While functions have return type and value , stored procedures do not return values and it rather returns the values via OUT parameters.
• Functions do not allow to use of data manipulation (DML) queries, procedures do allow to use DML queries such as INSERT, UPDATE, SELECT, CREATE, DELETE and so on. Functions allow only SELECT queries.
• Functions do not allow output parameters, whereas procedures allow both input and output parameters.
• You can call a function from a stored procedure, whereas you cannot call stored procedures from a function.
• You can call a function using a SELECT statement, whereas you cannot call a procedure using a SELECT statements
• More