Scalar functions can make tough queries easier
By: John Adolph Palinski
A scalar function is a Select statement that is embedded in a section of another Select statement. Like functions, it returns a value to the calling object. It differs from a function in that the scalar function is simply another Select statement and doesn't contain a PL/SQL code block. As Listing 1 shows, scalar functions can be used in Oracle to return values such as Where clause condition arguments (subquery) or as a data source in the From clause (in-line view). Listing 1 contains a Select statement that displays a salesman's total commission and draws against commission for salesmen in Molly Pitcher's sales area. Two scalar functions were used to compute the total commission and the total draws. A third scalar function is used to derive a Where clause argument. The scalar function determines Molly Pitcher's sales area. This value is then used as an argument to limit the result set to salesmen in that same sales area.
Listing 1 - Select statement containing a Where clause subquery and in-line view
SQL> select last_name, total_commissions, total_draws
2 from salesmen s1,
3 (select salesman_number, -- in-line view 1
4 sum(commission) total_commissions
5 from salesman_commissions
6 group by salesman_number) s2,
7 (select salesman_number, -- in-line view 2
8 sum(draw_amount) total_draws
9 from salesman_draws
10 group by salesman_number) s3
11 where s1.salesman_number = s2.salesman_number(+)
12 and s1.salesman_number = s3.salesman_number(+)
13 and sales_area = (select sales_area -- sub-query
14 from salesmen
15 where last_name = 'PITCHER');
LAST_NAME TOTAL_COMMISSIONS TOTAL_DRAWS
-------------------- ----------------- -----------
WASHINGTON 4450
PITCHER 750
VAN BUREN 4250 140
WASHINGTON 4300
PIERCE 2550
BUCHANAN 4200 390
6 rows selected.
SQL>
The previous example showed common ways of using scalar functions. They can also be used in other places. These are:
- Case expressions
- Function parameters
- Select clause expressions
- Insert statement Values clause expressions
- Order By clause expressions
Scalar functions are useful whenever a specific value is needed. Listing 2 below illustrates a Select statement that displays a salesman's last name and the number of salesmen hired before and after the salesman. Using scalar functions in the Select clause make this statement exceptionally easy to write. The scalar functions were used to count the number of hire_date values previous to and later than the current salesman's hire_date value. The scalar function used the current row's hiredate value (bolded) as a filtering argument. Notice that the master Select statement's data source, Salesman, has an alias of S1. The S1 data source is referenced in each of the scalar functions.
Listing 2 - Using scalar functions to determine the number of salesmen hired before and after
SQL> select last_name, hire_date,
2 (select count(*)
3 from salesmen s2
4 where s2.hire_date > s1.hire_date) "Hired Later",
5 (select count(*)
6 from salesmen s3
7 where s3.hire_date < s1.hire_date) "Hired Earlier"
8 from salesmen s1 ;
LAST_NAME HIRE_DATE Hired Later Hired Earlier
-------------------- --------- ----------- -------------
WASHINGTON 08-AUG-86 19 0
ADAMS 09-SEP-87 18 1
ROSS 14-JUN-89 17 2
JEFFERSON 30-OCT-90 15 3
MADISON 30-OCT-90 15 3
MONROE 04-JAN-91 14 5
PITCHER 23-AUG-93 12 7
ADAMS 15-MAR-93 13 6
JACKSON 23-SEP-93 11 8
VAN BUREN 03-JUL-95 10 9
HARRISON 19-OCT-96 9 10
TYLER 24-NOV-98 8 11
POLK 05-FEB-99 7 12
TAYLOR 02-MAR-00 6 13
WASHINGTON 30-JUN-00 5 14
FILLMORE 17-APR-01 4 15
PIERCE 28-JAN-02 3 16
BUCHANAN 08-APR-02 2 17
LINCOLN 09-DEC-02 1 18
JOHNSON 18-JUN-03 0 19
20 rows selected.
SQL>
I originally intended to write this article simply about scalar functions. It took about two minutes to write and test the Listing 2 Select statement. While writing the article I thought it would be beneficial to see an SQL statement that produced the same results as Listing 2 but did not use scalar functions . After about 30 minutes I really couldn't write the statement and my head was starting to hurt. The result set could be produced using PL/SQL, but I couldn't seem to get the result set using a Select statement without scalar functions (if you know a solution, e-mail it to realistic@radiks.net). I then realized that using scalar functions in the Select clause could really simplify my Select statements and possibly allow me to use SQL in cases that I would use PL/SQL. I hope that you also see these benefits after studying the statement.
There are some rules to using scalar functions. Scalar functions cannot:
- Return default values in a table column
- Be used in Returning clauses
- Be used in Check constraints
- Be used in trigger When conditions
- Be used in Group By clauses
- Be used in Having clauses
- Be used in Start With and Connect By clauses
I hope that you can see the benefits of scalar functions and begin to use them in your work.
John Adolph Palinski is a former adjunct faculty member at the University of Nebraska at Omaha and Iowa Western Community College. He is the author of the "Oracle Database Construction Kit", Que, 1997, the "Oracle SQL and PL/SQL Handbook: A Guide for Data Administrators, Developers, and Business Analysts", Addison Wesley Longman, 2002, and "Oracle 9i Developer: Developing Web Applications With Forms Builder", Course Technology, 2003. He has also written numerous articles for periodicals such as Oracle Internals and Oracle Professional. He provides Oracle training and consulting services. One of his passions is to help customers set up logical business models to improve information efficiency and effectiveness. He may be contacted at www.oracle-trainer.com.
