Free Trial

Safari Books Online is a digital library providing on-demand subscription access to thousands of learning resources.

  • Create BookmarkCreate Bookmark
  • Create Note or TagCreate Note or Tag
  • PrintPrint
Share this Page URL

Chapter 7. Application Management in PL/SQL > Procedures Versus Functions

Procedures Versus Functions

Given the fact that what can be achieved by functions can also be achieved by using procedures with OUT parameters, the reasons for using functions and, for that matter, the reasons for having two separate CREATE PROCEDURE and CREATE FUNCTION “subprogram entities” boil down to the following:

  • A coding standards violation that prevents the use of a procedure for returning only one value. A second implication of this is to use procedures only for returning multiple values using OUT parameters, because using OUT parameters with functions again violates such standards. However, this is more concerned with code ethics than code technicality.

  • The most invaluable use of a function comes in calling it from SQL. This is where procedures cannot be used and the complexity of logic involved makes it otherwise impossible or impractical to conglomerate such logic into a single query. This mandates the encapsulation of such logic into a function callable from SQL.

  • A second use of functions instead of procedures is when using function-based indexes. Using function-based indexes on complex expressions on the left side of WHERE conditions in queries enables the query to use the so-created index, resulting in optimal execution. This is required in data warehousing and business intelligence environments. Another use of function-based indexes relates to the previous point mentioned here.

  • A third use of functions over procedures is for simulating datasets using pipelined table functions. Procedures cannot be used for this purpose. A primary use of such datasets is, again, while performing ETL operations in data warehousing and business intelligence applications. This use is discussed in detail in Chapter 11.


You are currently reading a PREVIEW of this book.


Get instant access to over $1 million worth of books and videos.


Start a Free 10-Day Trial

  • Safari Books Online
  • Create BookmarkCreate Bookmark
  • Create Note or TagCreate Note or Tag
  • PrintPrint