I would like to clarify that the answer to the question of whether to use a stored procedure or function depends entirely on your business requirement and design workflow, provided that you clearly understand the purpose of your program. If you are not clear about your purpose, simply, as your question, no coding procedures and functions will be useful.
You must keep in mind that stored procedures and functions serve different purposes in PL / SQL programming. These are the following:
Stored Procedures:
a. Stored procedures are named blocks (as opposed to anonymous blocks) that are able to accept parameters and work with them.
b. Stored procedures define an independent procedural workflow where you can perform a series of DML and / or other operations.
from. Stored procedures must not return a value. Therefore, they cannot be called from an SQL statement. Stored procedures must be executed from a PL / SQL block name or anonymous.
D. Advantages:
e. Demerits:
- Cannot be called from SQL query - DML or
SELECT
. - Cannot be used in indexes.
Functions:
a. Functions are named blocks that can take parameters and return a value.
b. Functions also define a procedural workflow, but when used in SQL statements, you cannot execute DML or DDL.
from. The function must be called from an SQL or PL / SQL statement, where the value returned by the function is used, that is, it is assigned to a variable, passed as a parameter, etc.
D. Advantages:
- Can be used in an SQL query - DML or
SELECT
. - It can be used in functional indexes if the function is deterministic (this means that for a certain set of inputs the function returns the same output every time it is called).
e. Demerits:
- If the function called from the SQL query contains any DML, the query fails.
- A required function returns a value. Therefore, a function call cannot be an independent statement, similar to a procedure call.
For more information, visit Oracle Docs .
source share