Functions vs procedures in Oracle

by

The difference is- A function must return a value (of any type) by default definition of it, whereas in case of a procedure you need to use parameters like OUT or IN OUT parameters to get the results. You can use a function in a normal SQL where as you cannot use a procedure in SQL statements.

Some Differences between Functions and Procedures

  1. A function always returns a value using the return statement while a procedure may return one or more values through parameters or may not return at all.Although, OUT parameters can still be used in functions, they are not advisable neither are there cases where one might find a need to do so. Using OUT parameter restricts a function from being used in a SQL Statement.

  2. Functions can be used in typical SQL statements like SELECT, INSERT, UPDATE, DELETE, MERGE, while procedures can’t.

  3. Functions are normally used for computations where as procedures are normally used for executing business logic.

  4. Oracle provides the provision of creating “Function Based Indexes” to improve the performance of the subsequent SQL statement. This applies when performing the function on an indexed column in where clause of a query.

More Information on Functions Vs. Procedures here and here.

More Related Contents:

  1. Place to store PL/SQL block [closed]
  2. Passing an array of data as an input parameter to an Oracle procedure
  3. Oracle stored procedure with parameters for IN clause
  4. PL/SQL ORA-01422: exact fetch returns more than requested number of rows
  5. Get resultset from oracle stored procedure
  6. Create Pivot view in SQL from a SQL table
  7. passing in table name as plsql parameter
  8. How to use parameters in a ‘where value in…’ clause?
  9. Why cannot I create triggers on objects owned by SYS?
  10. No more data to read from socket
  11. Maximum Number of times we can query an oracle database [closed]
  12. How to uninstall / completely remove Oracle 11g (client)?
  13. Get counts of all tables in a schema
  14. Oracle trigger after insert or delete
  15. PL/SQL – Use “List” Variable in Where In Clause
  16. What is the effect of placing the commit after DML in procedure?
  17. PLS-00428: an INTO clause is expected in this SELECT statement
  18. creating parameterized views in oracle11g
  19. PL/SQL print out ref cursor returned by a stored procedure
  20. How to send email using Oracle 10 g Forms
  21. How to create a user in Oracle 11g and grant permissions
  22. Array in IN() clause oracle PLSQL
  23. Trigger to enforce M-M relationship
  24. Oracle: how to INSERT if a row doesn’t exist
  25. How to see refcursor result/output in Oracle SQL Developer? [duplicate]
  26. HikariCP: What database level timeouts should be considered to set maxLifetime for Oracle 11g
  27. How to use Alias in Where clause? [duplicate]
  28. How to call Oracle MD5 hash function?
  29. Oracle SQL Developer: Show REFCURSOR Results in Grid?
  30. How to test an Oracle Stored Procedure with RefCursor return type?

Leave a Comment