Beginning Oracle SQL: For Oracle Database 12c, Third Edition (2014)

Chapter 5. Retrieval: Functions

This chapter is a logical continuation of the previous chapter. The main topic is still retrieval. It introduces functions and regular expressions, which enable you to formulate more powerful and complicated queries in an easy way.

Oracle supports an abundance of functions. Apart from the various ANSI/ISO SQL standard functions, many Oracle-specific functions have been added to Oracle’s SQL implementation over the years.

The chapter begins with an overview of the seven categories of functions: arithmetic, text, regular expression, date, general, conversion, and group. The remaining sections discuss each type, with the exception of group functions, which are introduced in Chapter 8. You will also learn about regular expressions, which are used with some text functions to search for certain patterns in text. The last section of this chapter briefly explains how you can define your own SQL functions in Oracle using the PL/SQL programming language.

5.1 Overview of Functions

In Chapter 2, you saw that SQL supports the following standard SQL operators:

· Arithmetic operators: +, -, *, and /

· Alphanumeric operator: || (concatenation)

Besides using these operators, you can also perform many operations on your data using functions. You can use functions virtually anywhere within queries: in the SELECT, WHERE, HAVING, and ORDER BY clauses.

You can recognize functions as follows: they each have a name, followed by one or more arguments (between parentheses). In general, function arguments can be constants, variables, or expressions, and sometimes function arguments contain functions themselves. Functions inside function arguments are referred to as nested functions. In some cases, function arguments are optional. This means that you can omit the optional argument and allow Oracle to use a standard (or default) value.

Functions are usually listed in two groups: single-row functions, which have one row as input, and multiple-row functions, which have many rows as their input. Multiple-row functions are also called group functions. When they are used as aggregate functions, they return one value for each group they look at—examples are SUM, AVG, COUNT, and so on. They may also be used as analytic functions, in which case they return one value for each row that they evaluate. All the other functions are single-row functions, taking one row value as input and returning one value. Examples are UPPER, LOWER, TRUNC, and TO_CHAR.

Note Online documentation Oracle SQL Reference uses different terms for two similar concepts: functions without arguments and pseudo columns. For example, SYSDATE and USER are listed as functions, and ROWNUM, LEVEL, and NEXTVAL are listed as pseudo columns. If you check older versions of the documentation, you will see that Oracle has changed terminology over the years. In version 5.1, both SYSDATE and USER were pseudo columns; in version 6.0, SYSDATE was promoted to a function, but USER was still a pseudo column; and in version 7.3, bothSYSDATE and USER were documented as functions. You could argue that SYSDATE and USER return the same value for every row, while ROWNUM, LEVEL, and NEXTVAL normally return different values. According to the current Oracle SQL Reference, functions take zero or more arguments. This book sometimes refers to items as pseudo columns where Oracle SQL Reference refers to them as functions.

Obviously, the function arguments come with some constraints. For example, the datatype of the function arguments must make some logical sense. The Oracle DBMS always tries to perform implicit datatype conversion, and it will generate an error message only if such an attempt fails. In other words, if you specify a number as an argument for a function that expects a string instead, the number will be interpreted alphanumerically. However, if you ask for the square root of an employee name, you will get the error message ORA-01722: invalid number.”

1. Here are some examples of calls to functions to illustrate the above. A number given to a string function (incorrect usage)—number interpreted as string:

SQL> select upper(123) from dual;
UPP
---
123

2. A string given to a string function (correct usage):

SQL> select upper('de haan') from dual;
UPPER('
-------
DE HAAN

3. A string given to a number function (incorrect apostrophes). Implicit datatype conversion is done.

SQL> select trunc('123.4555', 2) from dual;
TRUNC('123.4555',2)
-------------------
123.45

4. A number given to a number function (correct usage):

SQL> select trunc(123.4555 , 2) from dual;
TRUNC(123.4555,2)
-----------------
123.45

5. Pseudo columns do not have arguments:

SQL> select sysdate, user from dual;
SYSDATE USER
----------------- ----------
13 February 2014 BOOK

Caution It is not a good idea to rely on implicit datatype conversion in your SQL statements. You should always use explicit conversion functions instead. This improves SQL readability, robustness, and possibly performance.

Testing against the public DUAL table, introduced in Chapter 4, is practical, since you get only one result because there is only one row in the table. It is present in all Oracle databases, is owned by the most privileged user SYS, and has one column and one row, containing an X.

SQL> select * from dual;
D
-
X

DUAL seems to indicate something to do with “two,” and the original table did indeed have two rows. Now it has only one row, and that is the important thing about it, but the name remained unchanged.

As stated previously, Oracle supports many functions. You can categorize them based on the datatype they expect in their arguments, as shown in Table 5-1.

Table 5-1. Function Types

The last category in Table 5-1, group functions, is covered in Chapter 8, where we discuss the GROUP BY and HAVING clauses of the SELECT command, since that chapter is a more natural place to introduce them. The other function types are discussed in the following sections.

5.2 Arithmetic Functions

The most popular arithmetic functions of Oracle are listed in Table 5-2.

Table 5-2. Common Oracle Arithmetic Functions

As Table 5-2 shows, the ROUND and TRUNC functions have an optional argument m; the default value for m is zero. Note that you can also use negative values for m, as you can see from the second example in Listing 5-1.

Listings 5-1 through 5-4 show some self-explanatory examples of using the following arithmetic functions: ROUND, CEIL, FLOOR, ABS, SIGN, POWER, and MOD.

Listing 5-1. Using the ROUND, CEIL, and FLOOR Functions

select round(345.678, 0), ceil(345.678), floor(345.678)
from dual;

ROUND(345.678) CEIL(345.678) FLOOR(345.678)
-------------- ------------- --------------
346 346 345

select round(345.678, 2)
, round(345.678,-1)
, round(345.678,-2)
from dual;

ROUND(345.678,2) ROUND(345.678,-1) ROUND(345.678,-2)
---------------- ----------------- -----------------
345.68 350 300

Listing 5-2. Using the ABS and SIGN Functions

select abs(-123), abs(0), abs(456)
, sign(-123), sign(0), sign(456)
from dual;

ABS(-123) ABS(0) ABS(456) SIGN(-123) SIGN(0) SIGN(456)
--------- -------- -------- ---------- -------- ---------
123 0 456 -1 0 1

Listing 5-3. Using the POWER and MOD Functions

select power(2,3), power(-2,3)
, mod(8,3), mod(13,0)
from dual;

POWER(2,3) POWER(-2,3) MOD(8,3) MOD(13,0)
---------- ----------- -------- ---------
8 -8 2 13

Listing 5-4. Using MOD in the WHERE Clause

select empno as odd_empno
, ename
from employees
where mod(empno,2) = 1;
ODD_EMPNO ENAME
--------- --------
7369 SMITH
7499 ALLEN
7521 WARD
7839 KING

The example in Listing 5-5 calculates the age (expressed in weeks and additional days) of all employees working for department 10. In this example, we use the difference between the BDATE column and the pseudo column SYSDATE. Of course, your results will be different from the results in Listing 5-5, because they depend on the point in time that you execute the query.

Listing 5-5. Using the FLOOR and MOD Functions

select ename
, floor((sysdate-bdate)/7) as weeks
, floor(mod(sysdate-bdate,7)) as days
from employees
where deptno = 10;

ENAME WEEKS DAYS
-------- -------- --------
CLARK 2032 5
KING 2688 0
MILLER 2208 6

Listing 5-6 shows an example using the arithmetic functions SIN, TANH, EXP, LOG, and LN. You probably recognize the number 3.14159265 as an approximation of π (pi), which is used in the SIN function example to convert degrees into radians.

Listing 5-6. Trigonometric, Exponential, and Logarithmic Functions

select sin(30*3.14159265/180), tanh(0.5)
, exp(4), log(2,32), ln(32)
from dual;

SIN(30*3.14159265/180) TANH(0.5) EXP(4) LOG(2,32) LN(32)
---------------------- --------- -------- --------- --------
.5 .4621172 54.59815 5 3.465736

5.3 Text Functions

The most important Oracle text functions are listed in Table 5-3.

Table 5-3. Common Oracle Text Functions

Note When counting positions in strings, always start with one, not with zero.

Several text functions have a corresponding function with a B suffix, such as SUBSTRB, INSTRB, and LENGTHB. These special functions express their results in bytes instead of characters. This distinction is relevant only if you are using multibyte character sets. See Oracle SQL Reference for more details.

Listing 5-7 shows some examples of the LOWER, UPPER, INITCAP, and LENGTH text functions; the results are self-explanatory.

Listing 5-7. Using the LOWER, UPPER, INITCAP, and LENGTH Functions

select lower(job), initcap(ename)
from employees
where upper(job) = 'SALESREP'
order by length(ename);

LOWER(JOB) INITCAP(ENAME)
---------- --------------
salesrep Ward
salesrep Allen
salesrep Martin
salesrep Turner

Listing 5-8 illustrates the text functions ASCII and CHR. If you compare the third and the fifth columns of the result, you can see that the ASCII function considers only the first character of its argument, regardless of the length of the input text (see Table 5-3 for the description of theASCII text function).

Listing 5-8. Using the ASCII and CHR Functions

select ascii('a'), ascii('z')
, ascii('A'), ascii('Z')
, ascii('ABC'), chr(77)
from dual;

ASCII('A') ASCII('Z') ASCII('A') ASCII('Z') ASCII('ABC') CHR(77)
---------- ---------- ---------- ---------- ------------ -------
97 122 65 90 65 M

The first two column headings in Listing 5-8 are very confusing, because SQL*Plus converts all SELECT clause expressions to uppercase, including your function arguments. If you want lowercase characters in your column headings, you must add column aliases and specify them between double quotes. For example, the first line of Listing 5-8 would look like this:

select ascii('a') as "ASCII('a')", ascii('z') as "ASCII('z')"

Listings 5-9 and 5-10 show some examples of using the INSTR, SUBSTR, LTRIM, and RTRIM text functions. (The layout in Listing 5-9 is formatted to increase readability.)

Listing 5-9. Using the INSTR and SUBSTR Functions

select dname
, substr(dname,4) as substr1
, substr(dname,4,3) as substr2
, instr(dname,'I') as instr1
, instr(dname,'I',5) as instr2
, instr(dname,'I',3,2) as instr3
from departments;

DNAME SUBSTR1 SUBSTR2 INSTR1 INSTR2 INSTR3
---------- ------- ------- -------- -------- --------
ACCOUNTING OUNTING OUN 8 8 0
HR 0 0 0
SALES ES ES 0 0 0
TRAINING INING INI 4 6 6

Listing 5-10. Using the LTRIM and RTRIM Functions

select ename
, ltrim(ename,'S') as ltrim_s
, rtrim(ename,'S') as rtrim_s
from employees
where deptno = 20;

ENAME LTRIM_S RTRIM_S
-------- -------- --------
ADAMS ADAMS ADAM
FORD FORD FORD
JONES JONES JONE
SCOTT COTT SCOTT
SMITH MITH SMITH

As we can see, the SUBSTR function returns a fragment of the string cut from position n (remember, we start counting from 1). The length of this fragment is defined by parameter m. If m is unspecified, all characters will be returned from position n to the end of string. The INSTRfunction returns the starting position of the expression in the string. In a case where n parameter is given, we will start checking only from nth position, but we will still count from the beginning of the string. If the m parameter is not given, the first iteration of expression occurrence in the string will be returned, and the mth occurrence is returned if m is specified.

LTRIM and RTRIM functions in Listing 5-10 remove specified characters but only if they appear at the left or right end of the string. This is a very useful function for removing trailing blanks in a string.

Listing 5-11 demonstrates using the LPAD and RPAD functions. Note that they not only lengthen strings, as their names suggest, but sometimes they also shorten strings; for example, see what happens with ACCOUNTING and TRAINING in Listing 5-11.

Listing 5-11. Using the LPAD and RPAD Functions

select dname
, lpad(dname,9,'>')
, rpad(dname,6,'<')
from departments;

DNAME LPAD(DNAM RPAD(D
---------- --------- ------
ACCOUNTING ACCOUNTIN ACCOUN
HR >>>>>>>HR HR<<<<
SALES >>>>SALES SALES<
TRAINING >TRAINING TRAINI

You can use the LPAD and RPAD functions to produce column-value histograms by providing variable expressions, instead of constant values, as their second argument. For an example, see Listing 5-12, which shows how to create a salary histogram with a granularity of 100.

Listing 5-12. Producing Histograms with the LPAD and RPAD Functions

select lpad(msal,4)||' '||
rpad('o',msal/100,'o') as histogram
from employees
where deptno = 30;

HISTOGRAM
-----------------------------------------------
1600 oooooooooooooooo
1250 oooooooooooo
1250 oooooooooooo
2850 oooooooooooooooooooooooooooo
1500 ooooooooooooooo
800 oooooooo

Listing 5-13 shows the difference between the functions REPLACE and TRANSLATE. TRANSLATE replaces individual characters. REPLACE offers the option to replace words with other words. Note also what happens if you use the REPLACE function with only two arguments, instead of three: the function removes words instead of replacing them.

Listing 5-13. Using the TRANSLATE and REPLACE Functions

select translate('beer bucket','beer','milk') as translate
, replace ('beer bucket','beer','milk') as replace_1
, replace ('beer bucket','beer') as replace_2
from dual;

TRANSLATE REPLACE_1 REPLACE_2
----------- ----------- ---------
miik muckit milk bucket bucket

5.4 Regular Expressions

The previous chapter introduced the LIKE operator, and the previous section of this chapter introduced the INSTR, SUBSTR, and REPLACE functions. All of these SQL functions search for text. The LIKE operator offers the two wildcard characters % and _, which allow you to perform more advanced searches. The other three functions accept plain text searches only. This functionality is sometimes insufficient for complicated search operations. Therefore, Oracle SQL also supports four functions: REGEXP_LIKE, REGEXP_INSTR, REGEXP_SUBSTR, andREGEXP_REPLACE. These SQL functions support, as their names suggest, so-called regular expressions. Apart from that, they serve the same purpose as their non-REGEXP counterparts.

Regular expressions are well known in all UNIX operating system variants (such as Linux, Solaris, and HP/UX) and are part of the international POSIX standard. They are documented in great detail in Oracle SQL Reference, Appendix C. This section provides an introduction to regular expressions, focusing on their use with the Oracle SQL regular expression functions.

Regular Expression Operators and Metasymbols

Table 5-4shows the most important regular expression metasymbols and their meanings. The Type column in Table 5-4 may contain the following:

· Postfix, which means that the operator follows its operand

· Prefix, which means that the operator precedes its operand

· Infix, which means that the operator separates its operands

· Nothing (empty), which means that the operator has no operands

Table 5-4. Common Regular Expression Operators and Metasymbols

If the square brackets notation does not give you enough precision or flexibility, you can use multicharacter collation elements, character classes, and equivalence classes, as follows:

· Multicharacter collation elementsare relevant for certain languages. Valid values are predefined and depend on the NLS_SORT setting. Use [. and .] to enclose collation elements.

· Character classesgive you more flexibility than the dash symbol between square brackets; for example, you can refer to alphabetic characters, numeric digits, alphanumeric characters, blank spaces, punctuation, and so on. Use [: and :] to enclose character classes.

· Equivalence classesallow you to match all accented and unaccented versions of a letter. Use [= and =] to enclose equivalence classes.

Before we look at some examples of how these regular expression operators work with the regular expression functions (in Listings 5-14 through 5-16), we need to discuss the syntax of the functions.

Regular Expression Function Syntax

The four regular expression functions have the following syntax. You can specify regular expressions in their pattern argument.

· REGEXP_LIKE(text, pattern[, options])

· REGEXP_INSTR(text, pattern[, pos[, occurrence[, return[, options]]]])

· REGEXP_SUBSTR(text, pattern[, pos[, occurrence[, options]]])

· REGEXP_REPLACE(text, pattern[, replace [, pos[, occurrence[, options]]]])

For all four functions, the first two arguments (text and pattern) are mandatory. These arguments provide the source text and the regular expression to search for, respectively. All of the remaining arguments are optional. However, function arguments can only be omitted from the right to the left. For example, if you want to specify a value for the options argument of the REGEXP_INSTR function, all six arguments are mandatory and must be specified.

In REGEXP_INSTR, REGEXP_SUBSTR, and REGEXP_REPLACE, you can use the pos argument to specify from which position in text you want the search to start (the default value is 1), and with occurrence, you can specify how often you want to find the search pattern (the default value is 1). The options argument of all four of the functions and the return argument of the REGEXP_INSTR function require a bit more explanation.

Influencing Matching Behavior

You can influence the matching behavior of the regular expression functions with their options argument. Table 5-5 shows the values you can specify in the options function argument.

Table 5-5. Regular Expression Option Values

You can specify one or more of these values. If you specify conflicting combinations, such as ic, the Oracle DBMS uses the last value (c) and ignores the first one.

Note The default behavior for case-sensitivity depends on the NLS_SORT parameter value.

REGEXP_INSTR Return Value

The return option of the REGEXP_INSTR function allows you to influence the return value. By default, the position where the pattern was found is returned, but sometimes you want to know the position immediately after the found pattern. Of course, you can add the length of the pattern to the result of the function; however, using the return option is easier in that case. Table 5-6 shows the values you can specify in the return function argument.

Table 5-6. Regular Expression Return Values

REGEXP_LIKE

Let’s look at an example of the REGEXP_LIKE function, using a SQL*Plus trick that will be explained in a later chapter. The ampersand character (&) in the WHERE clause of the query in Listing 5-14 makes SQL*Plus prompt for a value for text; therefore, you can repeat this query in the SQL buffer with the / command as often as you like, specifying different source text values to explore the effect of the search pattern.

Listing 5-14. Using the REGEXP_LIKE Function

SQL> select 'found!' as result from dual
2 where regexp_like('&text', '^.a{1,2}.+$', 'i');

Enter value for text:bar

RESULT
------
found!

SQL>/
Enter value for text:BAARF

RESULT
------
found!

SQL>/
Enter value for text:ba

no rows selected

SQL>

The results of Listing 5-14 show that the pattern means the following: the first character is arbitrary, followed by at least one and at most two a characters, followed by one or more arbitrary characters, while ignoring the differences between uppercase and lowercase. By the way, Listing 5-14 shows that REGEXP_LIKE is a Boolean function; its result is TRUE or FALSE.

REGEXP_INSTR

Listing 5-15 uses the REGEXP_INSTR function to search for history comments with nine or more words. It looks for at least nine nonempty (+) substrings that do not contain spaces ([^ ]).

Listing 5-15. Using the REGEXP_INSTR Function

select comments
from history
where regexp_instr(comments, '[^ ]+', 1, 9) > 0;

COMMENTS
------------------------------------------------------------
Not a great trainer; let's try the sales department!
Sales also turns out to be not a success...
Hired as the new manager for the accounting department
Junior sales rep -- has lots to learn... :-)

Notice that the last row of the result contains only seven actual words. It is found because the text strings -- and :-) are counted as “words.”

REGEXP_SUBSTR

Listing 5-16 demonstrates searching for comments between parentheses, using the REGEXP_SUBSTR function. The search pattern looks for a left parenthesis, followed by at least one character not equal to a right parenthesis, followed by a right parenthesis. Note that you need the backslash character (\) to suppress the special meaning of the parentheses.

Listing 5-16. Using the REGEXP_SUBSTR Function

select comments
, regexp_substr(comments, '\([^\)]+\)') as substring
from history
where comments like '%(%';

COMMENTS
------------------------------------------------------------
SUBSTRING
------------------------------------------------------------
Project (half a month) for the ACCOUNTING department
(half a month)

REGEXP_REPLACE

Listing 5-17 shows how you can use the REGEXP_REPLACE function to replace all words starting with an f with a question mark.

Listing 5-17. Using the REGEXP_REPLACE Function

select regexp_replace(comments, ' f[a-z]* ',' ? ',1,1,'i')
from history
where regexp_like(comments, ' f[a-z]* ','i');

REGEXP_REPLACE(COMMENTS,'F[A-Z]*','?',1,1,'I')
-------------------------------------------------------------------
Hired as the new manager ? the accounting department
Founder and ? employee of the company
Project (half a month) ? the ACCOUNTING department

Notice that you must specify values for all function arguments if you want to make the replacement case-insensitive, including default values for pos and occurrence. The WHERE clause ensures that the query returns only the matching rows. Also notice that the word ‘Founder’ is not substituted, because there is no blank in front of it. See the blank at the beginning of the pattern.

5.5 Date Functions

Before discussing the various Oracle date functions, let’s first review the syntax to specify date/time-related constants (or literals), using predefined ANSI/ISO SQL standard formats.

Table 5-7 shows the syntax for the literals and examples.

Table 5-7. Syntax for Date/Time-Related Constants

You can experiment with this syntax by entering the following query, using the SQL*Plus ampersand (&) substitution method (as in Listing 5-14):

select &input_date from dual;

If you simply enter an alphanumeric string, such as '21-JUN-04', you must rely on an implicit conversion by Oracle. This implicit conversion succeeds or fails depending on the NLS_DATE_FORMAT and NLS_TIMESTAMP_FORMAT parameter settings for your session. If you want to see an overview of all current NLS parameter settings for your session, you can use the following query:

select * fromnls_session_parameters;

If you execute this query, you will see the current values for NLS_DATE_FORMAT and NLS_TIMESTAMP_FORMAT.

In SQL Developer you may see and change this information at session level using Tools/Preferences/Database/NLS.

In SQL*Plus (and of course also SQL Developer) you may set your preferred date format using for instance:

SQL> alter session set NLS_DATE_FORMAT = 'dd Month yyyy hh24:mi:ss';
SYSDATE
--------------------------
13 February 2014 10:33:42

Table 5-8 shows the most commonly used Oracle date functions.

Table 5-8. Common Oracle Date Functions

We’ll start with the last function listed in Table 5-8.

EXTRACT

You can extract various components of a date or timestamp expression with the ANSI/ISO standard EXTRACT function. Depending on the datatype of the argument d(DATE, TIMESTAMP, or INTERVAL) the following values for c are supported: YEAR, MONTH, DAY, HOUR, MINUTE,SECOND, TIMEZONE_ABBR, and so on. Listing 5-18 shows an example.

Listing 5-18. Using the EXTRACT Function

select bdate
, extract(year from bdate) as year_of_birth
, extract(month from bdate) as month_of_birth
, extract(day from bdate) as day_of_birth
from employees
where ename = 'KING';

BDATE YEAR_OF_BIRTH MONTH_OF_BIRTH DAY_OF_BIRTH
----------- ------------- -------------- ------------
17-NOV-1952 1952 11 17

ROUND and TRUNC

Table 5-9 lists the date formats (fmt) supported by the date functions ROUND and TRUNC. The default format is 'DD', resulting in rounding or truncating to midnight. For example, TRUNC(SYSDATE,'DD') truncates the current system date and time to midnight.

Table 5-9. ROUND and TRUNC Date Formats

MONTHS_BETWEEN and ADD_MONTHS

Listings 5-19 and 5-20 show examples of using the date functions MONTHS_BETWEEN and ADD_MONTHS.

Listing 5-19. Using the MONTHS_BETWEEN Function

select ename, months_between(sysdate,bdate)
from employees
where deptno = 10;

ENAME MONTHS_BETWEEN(SYSDATE,BDATE)
-------- -----------------------------
CLARK 467.5042
KING 618.2461
MILLER 508.0525

Listing 5-20. Using the ADD_MONTHS Function

select add_months('29-JAN-1996', 1) add_months_1
, add_months('29-JAN-1997', 1) add_months_2
, add_months('11-AUG-1997',-3) add_months_3
from dual;

ADD_MONTHS_1 ADD_MONTHS_2 ADD_MONTHS_3
------------ ------------ ------------
29-FEB-1996 28-FEB-1997 11-MAY-1997

Notice what happens in Listing 5-20 with a non-leap year. There is something else worth noting about the query in Listing 5-20. As explained earlier, you could get back an error message because you rely on implicit interpretation and conversion of the three strings by Oracle. It would have been preferable to specify the three date literals in Listing 5-20 using the keyword DATE (see the beginning of this section) or using the TO_DATE conversion function. (See Section 5.7 later in this chapter for details about conversion functions.)

NEXT_DAY and LAST_DAY

Listing 5-21 shows examples of using the date functions NEXT_DAY, LAST_DAY, ROUND, and TRUNC. Compare the various function results with the first column, showing the current SYSDATE value.

Listing 5-21. Using the NEXT_DAY, LAST_DAY, ROUND, and TRUNC Functions

select sysdate
, next_day(sysdate,'SAT') as next_sat
, last_day(sysdate) as last_day
, round(sysdate,'YY') as round_yy
, trunc(sysdate,'CC') as trunc_cc
from dual;


SYSDATE NEXT_SAT LAST_DAY ROUND_YY TRUNC_CC
----------- ----------- ----------- ----------- -----------
13-feb-2014 15-feb-2014 28-feb-2014 01-jan-2014 01-jan-2001

5.6 General Functions

The most important general (datatype-independent) functions are shown in Table 5-10.

Table 5-10. Common General Oracle Functions

You can express all of the other functions as CASE expressions, too, because they all share a procedural nature. In other words, you don’t really need them. Nevertheless, these functions can be useful in your SQL code because, for example, they make your code more compact. Note also that only the CASE, NULLIF, and COALESCE functions are part of the ANSI/ISO standard. The remaining five functions (GREATEST, LEAST, NVL, NVL2, and DECODE) are Oracle-specific SQL extensions. In other words, if your goal is to write portable SQL code, you should use onlyCASE, NULLIF, and COALESCE.

GREATEST and LEAST

The GREATEST and LEAST functions can be useful in certain situations. Don’t confuse them with the MAX and MIN group functions (which are covered in detail in Chapter 8). For now, remember the following differences:

· GREATEST and LEAST allow you to make horizontal comparisons; they operate at the row level, therefore listed as single-row functions.

· MAX and MIN allow you to make vertical comparisons; they operate at the column level - these are multi-row functions or group functions.

Listing 5-22 shows an example of the GREATEST and LEAST functions, selecting three constant expressions against the DUAL table.

Listing 5-22. Using the GREATEST and LEAST Functions

select greatest(12*6,148/2,73)
, least (12*6,148/2,73)
from dual;

GREATEST(12*6,148/2,73) LEAST(12*6,148/2,73)
----------------------- --------------------
74 72

NVL

The NVL function is useful if you want to prevent certain expressions, or expression components, from evaluating to a null value, as you can see in Listing 5-23.

Listing 5-23. Using the NVL Function

select ename, msal, comm
, 12*msal+nvl(comm,0) as yearsal
from employees
where ename like '%T%';

ENAME MSAL COMM YEARSAL
-------- -------- -------- --------
SMITH 800 9600
MARTIN 1250 1400 16400
SCOTT 3000 36000
TURNER 1500 0 18000

DECODE

The DECODE function is a typical remnant from the days that Oracle SQL did not yet support CASE expressions. There are three good reasons not to use DECODE anymore:

· DECODE function expressions are quite difficult to read.

· DECODE is not part of the ANSI/ISO SQL standard.

· CASE expressions are much more powerful.

For completeness, and because you may encounter the DECODE function in legacy Oracle SQL programs, Listing 5-24 shows a query where the DECODE function is used in the SELECT clause (to get a certain output) and in the ORDER BY clause (to do a customized sorting of the records).

Listing 5-24. Using the DECODE Function

select job, ename
, decode(greatest(msal,2500)
,2500,'cheap','expensive') as class
from employees
where bdate < date '1964-01-01'
order by decode(job,'DIRECTOR',1,'MANAGER',2,3);

JOB ENAME CLASS
-------- -------- ---------
DIRECTOR KING expensive
MANAGER BLAKE expensive
SALESREP ALLEN cheap
SALESREP WARD cheap
ADMIN MILLER cheap
TRAINER FORD expensive
TRAINER SCOTT expensive
SALESREP MARTIN cheap

And then using CASE for the exact same result:

select job, ename
, case greatest(msal,2500)
when 2500 then 'cheap'
else 'expensive'
end as class
from employees
where bdate < date '1964-01-01'
order by decode(job,'DIRECTOR',1,'MANAGER',2,3);

SAMPLE Function

You might have millions of records and want to do statistics on only 1% of randomly selected rows, so you want to sample, and you want it to be fast. Well, there is a function SAMPLE:

SQL> select * from orders sample (1);

When you are testing you may want the same sample each time you test. For that you need to add SEED, like this:

SQL> select * from orders sample (1) seed (1234);

5.7 Conversion Functions

Conversion functions allow you to convert expressions explicitly from one datatype into another datatype. Table 5-11 lists the most common conversion functions in Oracle SQL. See Oracle SQL Reference for more conversion functions.

Table 5-11. Common Oracle Conversion Functions

Note The syntax in Table 5-11 is not complete. Most conversion functions allow you to specify additional NLS parameters after the format (fmt) argument. For example, you can influence the currency symbol, the numeric characters (period and comma), and the date language. SeeOracle SQL Reference and Globalization Support Guide for more details.

TO_NUMBER and TO_CHAR

Listing 5-25 shows how you can use the TO_NUMBER and TO_CHAR functions (with or without a format argument) to convert strings to numbers and vice versa.

Listing 5-25. Using the TO_CHAR and TO_NUMBER Functions

select 123
, to_char(123)
, to_char(123,'$09999.99')
, to_number('123')
from dual;

123 TO_ TO_CHAR(12 TO_NUMBER('123')
-------- --- ---------- ----------------
123 123 $00123.00 123

Listing 5-26 shows how you can nest conversion functions. On the third line, you use the TO_DATE function to interpret the string '01/01/2006' as a date value; then, you use the TO_CHAR function to extract the day from the date value, as you can see in the third column of the query result.

Listing 5-26. Nesting the TO_CHAR and TO_DATE Functions

select sysdate as today
, to_char(sysdate,'hh24:mi:ss') as time
, to_char(to_date('01/01/2014','dd/mm/yyyy')
,'"is on "Day') as new_year_2014
from dual;

TODAY TIME NEW_YEAR_2014
----------- -------- ---------------
13-feb-2014 11:39:09 is on Wednesday

In this example, the format Day results in Wednesday because the default language is English. You can set the NLS_LANGUAGE parameter to another language to influence this behavior. For example, if you set this session (or system) parameter to Dutch, the result becomesWoensdag. You could also override this default at the statement level, by setting the NLS_DATE_LANGUAGE parameter, as shown in Listing 5-27.

Listing 5-27. Influencing the Date Language at the Statement Level

select to_char(sysdate, 'Day')
, to_char(sysdate, 'Day', 'nls_date_language=Dutch')
from dual;

TO_CHAR(S TO_CHAR(S
--------- ---------
Tuesday Dinsdag

Conversion Function Formats

Table 5-11 showed that several Oracle conversion functions support an optional format (fmt) argument. These format arguments allow you to deviate from the default conversion. Table 5-12 shows most of the possibilities.

Table 5-12. Conversion Functions: Optional Format Components

Note You can influence several date characteristics, such as the first day of the week, with the NLS_TERRITORY parameter.

Oracle supports some additions that you can use in conversion function format strings to further refine the results of those functions. Table 5-13 shows these additions.

Table 5-13. Conversion Functions: Format Component Additions

In fill mode, Oracle does not perform paddingwith spaces, and numbers are not prefixed with leading zeros. You can enable and disable this fill mode mechanism within the same format string as many times as you like, by repeating FM (it is a toggle). Ordinal numbers indicate a relative position in a sequence.

The conversion function formats are case-sensitive, as demonstrated in Listing 5-28.

Listing 5-28. TO_CHAR Formats and Case-Sensitivity

select to_char(sysdate,'DAY dy Dy') as day
, to_char(sysdate,'MONTH mon') as month
from dual;

DAY MONTH
----------------- -------------
MONDAY mon Mon MAY may

Datatype Conversion

In the area of datatype conversion, you can leave many issues up to the Oracle DBMS. However, for reasons of syntax clarity, it is better to express the datatype conversions explicitly with the appropriate conversion functions. See the query in Listing 5-29 for an example.

Listing 5-29. Relying on Implicit Datatype Conversion

select ename, substr(bdate,8)+16
from employees
where deptno = 10;

ENAME SUBSTR(BDATE,8)+16
-------- ------------------
CLARK 81
KING 68
MILLER 78

This query is internally interpreted and executed by the Oracle DBMS as the following:

select ename, TO_NUMBER(substr(to_char(bdate,'...'),8))+16
from employees
where deptno = 10

You should have formulated the query that way in the first place.

CAST

The last function to discuss in this section about conversion functions is CAST. This function is part of the ANSI/ISO SQL standard, as opposed to all other conversion functions discussed so far in this section. The CAST function is a generic conversion function. It allows you to convert anyexpression to any specific datatype, including the option to specify a datatype precision. See Listing 5-30 for some examples.

Listing 5-30. CAST Function Examples

select cast(12.98 as number(2)) example1
, cast('oak' as char(10) ) example2
, cast(null as date ) example3
from dual;

EXAMPLE1 EXAMPLE2 EXAMPLE3
-------- ---------- ---------
13 oak

5.8 Stored Functions

Although you might argue that Oracle already offers more than enough functions, you may find that you need a specific capability that isn’t already provided. In that case, you can develop your own functions (using PL/SQL) and add them to the SQL language.

PL/SQL is the standard procedural programming language for Oracle databases. PL/SQL is a superset of SQL, adding several procedural capabilities to the nonprocedural SQL language. Here, we will investigate one simple example of PL/SQL language usage in relation to custom functions. For more information about PL/SQL, refer to Oracle PL/SQL User’s Guide and Reference.

Listing 5-31 shows how to define a function to determine the number of employees for a given department.

Listing 5-31. Creating a Stored Function Using PL/SQL

create or replace function emp_count(p_deptno in number)
return number
is
cnt number(2) := 0;
begin
select count(*)
into cnt
from employees e
where e.deptno = p_deptno;
return (cnt);
end;
/

Function created.

Now it becomes relatively easy to produce an overview of all departments, with their (correct) number of employees, as you can see in Listing 5-32. This query would be more complicated without this function. In particular, department 40 (the well-known department without employees) would not show up in your query results without some extra work. Without the stored function, you would need a so-called OUTER JOIN (see Chapter 8) or you would need a subquery in the SELECT clause (see Chapter 9).

Listing 5-32. Using the Stored Function

select deptno, dname, location
, emp_count(deptno)
from departments;

DEPTNO DNAME LOCATION EMP_COUNT(DEPTNO)
-------- ---------- -------- -----------------
10 ACCOUNTING NEW YORK 3
20 TRAINING DALLAS 5
30 SALES CHICAGO 6
40 HR BOSTON 0

Listing 5-33 shows how the SQL*Plus DESCRIBE command treats these stored functions.

Listing 5-33. Describing a Stored Function

SQL> describe emp_count

FUNCTION emp_count RETURNS NUMBER

Argument Name Type In/Out Default?
------------------------- --------------- ------ --------
P_DEPTNO NUMBER IN

SQL>

When the purpose of the function is to call it within a SQL statement, it should not have side effects, so you are not allowed to be doing things like commit/rollback, inserts, deletes or changing package variables.

You may read more about these restrictions here: http://docs.oracle.com/cd/E16655_01/appdev.121/e17620/adfns_packages.htm#ADFNS00908

Function in WITH Clause of Query

It is now—in 12c onward—possible to define your own function as a local object directly within the WITH clause of a SELECT. Then you avoid storing all kinds of one-time-only functions to the Data Dictionary. It looks like this if you want to concatenate an e-mail based on the employee’s first and last names:

with
function email
(p_first_name in varchar2, p_last_name in varchar2)
return varchar2
is
begin
return substr(p_first_name,1,1)||substr(p_last_name,1,7);
end;
select first_name, last_name, email(first_name,last_name)
from employees
order by first_name;

The result looks like Figure 5-1:

Figure 5-1. Function in WITH

In 12c you may improve the performance of an inline PL/SQL function by including the following compiler instruction in the DECLARE section of the function: PRAGMA UDF. UDF is the abbreviation for User Defined Function.

with
function email
(p_first_name in varchar2, p_last_name in varchar2)
return varchar2
is
pragma udf;
begin ....................

5.9 Exercises

Use a database schema with the seven case tables (see Appendix A of this book) to perform the following exercises. The answers are presented in Appendix B

1. For all employees, provide their last name, a comma, followed by their initials.

2. For all employees, list their last name and date of birth in a format such as April 2nd, 1967.

3. On which day are (or were) you exactly 10,000 days old? On which day of the week is (was) this?

4. Rewrite the example in Listing 5-23 using the NVL2 function.

5. Rewrite the example in Listing 5-24 to remove the DECODE functions using CASE expressions, both in the SELECT clause and in the ORDER BY clause.

6. Rewrite the example in Listing 5-20 using DATE and INTERVAL constants, in such a way that they become independent of the NLS_DATE_FORMAT setting.

7. Investigate the difference between the date formats WW and IW (week number and ISO week number) using an arbitrary date and explain your findings.

8. Look at Listing 5-15, where we use the REGEXP_INSTR function to search for words. Rewrite this query using REGEXP_LIKE. Hint: You can use {n,} to express “at least n times.”

9. If you have a 12c database, try out the local PL/SQL function in the WITH of a query. Use the code for Figure 5-1 as a template.