Oracle and PL/SQL Recipes: A Problem-Solution Approach

4-2. Executing a Stored Function from a Query

Problem

You want to invoke a function from an SQL query. For example, you want to take the quarter-hour rounding function from Recipe 4-1 and invoke it on hourly values in a database table.

Solution

Write a query and invoke the function on values returned by the SELECT statement. In the following lines, the function that was written in the previous recipe will be called. The results of calling the function from within a query are as follows:

SQL> select calc_quarter_hour(.17) from dual; CALC_QUARTER_HOUR(.17) ----------------------   .25 SQL> select calc_quarter_hour(1.3) from dual; CALC_QUARTER_HOUR(1.3) ----------------------  1.25

How It Works

There are a few ways in which a function can be ...