when should I go for procedure or function in PL/SQL?

Question

I am new in PL/SQL trying some practice examples.
I have few questions regarding PL/SQL PROCEDURE & FUNCTION:

When should I go for the PROCEDURE or FUNCTION?

Means, whatever task I am doing using FUNCTION the same task will do by using PROCEDURE. then why should I go for the function? is their any advantage of FUNCTION over the PROCEDURE in PL/SQL?

FUNCTION must return value. Is this the only advantage for using a function or are there any other advantages of functions?

Answer 1

I would like to clarify that the answer for whether you should use a stored procedure or a function is completely dependent upon your business requirement and design workflow, provided you are clear about your program objective. If you are unclear about your objective, just the way your question is, no amount of coding procedures and functions would be useful.

You must note that stored procedures and functions serve different purposes in PL/SQL programming. These are as follows:

1. Stored procedures:

   a. Stored procedures represent named blocks (as opposed to anonymous blocks) that are capable of accepting parameters and work on them.

   b. Stored procedures define an independent procedural workflow where you can perform a series of DML and/or other operations.

   c. Stored procedures do not have to return a value. Hence, they cannot be called from inside an SQL statement. Stored procedures must be executed from a PL/SQL block- named or anonymous.

   d. Merits:

   • A procedure does not have to return a value (This can be a demerit too).
   • Can be used to perform a series of DML or DDL (yes, this is possible through dynamic SQL with a few restrictions) operations.

   • Can be simply called as an independent statement from a PL/SQL block. e.g.,

     myProcedure (x, y);
     

   e. Demerits:

   • Cannot be called from an SQL query - DML or a SELECT statement.
   • Cannot be used in indexes.

2. Functions:

   a. Functions are named blocks that are capable of accepting parameters and return a value.

   b. Functions also define a procedural workflow but when used in SQL statements, you cannot perform any DML or DDL.

   c. A function must be called from a SQL or PL/SQL statement where the value returned by the function is utilized- i.e., assigned to a variable, passed as a parameter, etc.

   d. Merits:

   • Can be used in an SQL query - DML or a SELECT statement.
   • Can be used in function-based indexes if the function is deterministic (meaning for a definite set of inputs the function returns the same output every time it is called).

   e. Demerits:

   • If the function being called from an SQL query contains any DML, the query fails.
   • It is obligatory for a function to return a value. Hence a function call cannot be an independent statement like a procedure call.

For further reference, visit Oracle Docs.

Answer 2

A user defined function, with certain limitations, can be used in SELECT statements and PL/SQL IF statements whereas a PROCEDURE cannot.

You can SELECT from a FUNCTION that is CAST as a table using pipeline and PIPE ROW statements, but that is an advanced PL/SQL feature you can use much later.

Consult the Oracle Developer documentation online as it is free and very good: Developing and Using Stored Procedures

Answer 3

1. A function will return a value, A "value" have be one of many things including PL/SQL tables, ref cursors etc. Adding to that, it is possible to use a function in SQL statements, whereas procedures cannot be used.
2. Procedures are used to execute business logic, where we can return multiple values from the procedure using OUT or IN OUT parameters.
3. Personally I use function for computations - For example: check for a specific condition like retrieving a value based on the condition, checking the condition for true or false.

4. You can have DML(insert, update, delete) statements in a function. But, you cannot call such a function in a SQL query. *Eg: If you have a function that is updating a table, you can't call that function in any SQL query.

   select myFunction(field) from sometable; --will throw error.
   

It is on your choice whether to use procedure or function depends on you requirement and your comfortability.

Answer 4

Main advantages:

1. Function it must return a values where as procedure may or may not return a values.
2. Function mainly used for to computes a values. Where as procedure mainly used for executive business logic .
3. Function to retrive a value where as procedure to manupliate a values.
4. Function it must return only one values but it accept many return types.