Advantage of PL/SQL procedure and function - when to use ?

What is the difference between PL/SQL procedure and function ?

When is better to use procedure and function ?
0
give a positive ratinggive a negative rating
19 May 2022 at 03:57 PM
Hi,

Both procedures and functions can be described as a group of SQL statements, that perform a specific task. They are usually used to perform a more complex operations. Procedures and functions might look very similar in some cases, but there are several key differences between them:

User Defined Function (UDF)
  • Is used to get a result using given inputs. It cannot be used to modify the database (statements INSERT, UPDATE or DELETE are not allowed).
  • Can have only Input parameter.
  • Should return a single value.
  • Procedure cannot be called from a function.
  • Transactions are not possible.
  • Can be used in the SQL statements in the SELECT, WHERE or HAVING section.


Stored Procedure (SP)
  • Can be used to modify the database (statements INSERT, UPDATE or DELETE can be used).
  • Can have Input and Output parameters.
  • Can return zero, single or multiple values.
  • Function can be called from a procedure.
  • Transactions are possible.
  • Cannot be used in the SQL statements in the SELECT, WHERE, or HAVING section.
0
give a positive ratinggive a negative rating
05 Jul 2022 at 11:16 AM
Tim
Share on FacebookShare on TwitterShare on LinkedInSend email
x
x
Follow us on Facebook
2022 AnswerTabsTermsContact us