Problem
You want to invoke a function from an SQL query in PL/SQL. For example, you want to take the quarter-hour rounding function from this blog and invoke it on hourly values in a Oracle 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 called, one of which is via a query. A function can be executed inline via a SELECT statement, as was the case with the solution to this recipe. A function can also be executed by assigning it to a variable within an anonymous block or another function/procedure. Since all functions return a value, this works quite well. For instance, the following QTR_HOUR variable can be assigned the value that is returned from the function:
DECLARE
qtr_hour NUMBER;
BEGIN
qtr_hour := calc_quarter_hour(1.3);
DBMS_OUTPUT.PUT_LINE(qtr_hour);
END; You can also execute a function as part of an expression. In the following statement, you can see that TOTAL_HOURS is calculated by adding the bill total to the value returned from the function:
DECLARE
total_hours NUMBER;
hours NUMBER := 8;
BEGIN
total_hours := hours + calc_quarter_hour(3.2);
DBMS_OUTPUT.PUT_LINE(total_hours);
END;The way in which your PL/SQL program calls a function depends on its needs. If you need to simply return some results from the database and apply a function to each of the results, then use a query. You may have an application that needs to pass a value to a function and use the result at some later point, in which case assigning the function to a variable would be a good choice for this case. Whatever the case may be, functions provide convenient calling mechanisms to cover most use cases.
