Executing a PL/SQL Stored Function from a Query

PL/SQL Stored Function ExecutingProblem

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.

 

Вас заинтересует / Intresting for you:

Describe the Basic PL/SQL Prog...
Describe the Basic PL/SQL Prog... 375 views Андрей Волков Wed, 12 Sep 2018, 15:23:39
PL/SQL package: Collecting Rel...
PL/SQL package: Collecting Rel... 145 views sepia Sat, 01 Dec 2018, 10:54:57
A Brief History of SQL
A Brief History of SQL 271 views Александров Попков Wed, 17 Oct 2018, 15:04:29
Why Oracle DBAs learn PL/SQL a...
Why Oracle DBAs learn PL/SQL a... 372 views Андрей Волков Wed, 12 Sep 2018, 14:43:12

Comments on Executing a PL/SQL Stored Function from a Query

Doc
Doc Yes, this is a working solution! 1 year ago
loader
loader
Attachment
Please login to comment