330-334

Table of Contents

Appendix A
Using Built-In SQL And PL/SQL Functions

One of the nice things about SQL and PL/SQL is the number of built-in functions that Oracle provides. This appendix provides explanations and examples for the most commonly used built-in functions. (You ll also find some other useful functions that Oracle didn t take the time to include.)

Single-Row SQL Functions

This is the largest and most varied group of SQL functions. Functions within this group can operate only on the values contained within a single-row. Single-row functions can be sorted by functionality and the datatype of their parameters. In most aspects, single-row functions closely resemble stored functions. For purposes of this appendix, I ve divided the most commonly used single-row SQL functions into five groups: conversion, alphabetic, date, numeric, and miscellaneous.

Examples of each function are included. In some instances, the functions are illustrated like this:

 chr (97) 

This is a call to the function that can appear in one of two places:

   Inside a DML statement:

 SELECT chr (97) FROM   <table>; 

In this instance, <table> can be the name of any table to which you have access. You can also use DUAL here. DUAL is a dummy table recognized by the system for the express purpose of retrieving a value.

   On the right side of an expression in a PL/SQL block:

 vDelimiter := chr (25); 

In this instance, the output of the function will be assigned to the variable vDelimiter .

In other instances, the examples utilize a DML statement or excerpt from a PL/SQL block. This indicates that the function calls must be used in the context shown in the example.

Conversion Functions

Oracle provides conversion functions so values of one datatype can be easily converted to another datatype. While Oracle will perform implicit conversions of datatypes for you, this could lead to performance problems in your applications and to compatibility problems in future versions of Oracle. I strongly recommend that you use these conversion functions instead of relying on Oracle s implicit conversions.

to_char()

The to_char() function is probably the most commonly used conversion function. The function converts both numerical data and date data to datatype varchar2 .

The simplest way of using to_char() to convert date information is as follows :

 to_char ('02/14/97') 

This converts the date into a character string in the default date format (numerical month, numerical day, and a two-digit year, separated by slashes ). However, the most common use of to_char() is to convert dates to type varchar2 in a specific format, as shown in this example,

 to_char ('14-FEB-97', 'DD-MON-YYYY') 

which returns the following string:

 14-FEB-1997 

Using to_char() with numerical data is very similar to using the function with dates. One common use is to simply convert data from a numerical datatype to type varchar2 . For example,

 to_char (25000) 

returns

 25000 

When converting numerical data, to_char() can also take a second argument, a format model for the output of the function. For example,

 to_char (25000, ',999.99') 

returns

 ,000.00 

Chapter 3 lists the elements that can be used in format models for numerical and date data.

to_date()

The to_date() function is used to convert character data to the date datatype. Like to_char() , this function can be called with a single parameter, much like

 to_date ('02-MAY-97') 

which returns a value of type date . to_date() may also be called with a second parameter, which instructs the function to convert the specified string from the specified format into a standard date. For example,

 to_date ('02 May 1997', 'DD MONTH YYYY') 

returns

 02-MAY-97 

Valid elements of the format model are discussed in Chapter 3. The examples provided here have all used the default Oracle date format.

to_number()

The to_number() function converts data of type char or varchar2 to type number . The function can accept a single parameter. For example,

 to_number ('25000') 

returns

 25000 

The function can also accept a format model as the second parameter, like the to_char() and to_date() functions. For example,

 to_number ('25000', ',999.99') 

returns

 ,000.00 

Valid elements of the format model are discussed in Chapter 3.

Alphabetic Functions

The alphabetic group of functions deals primary with operations on strings but also provides some other interesting functions, such as functions that deal with converting characters to and from their ASCII equivalents.

ascii()

The ascii() function returns the ASCII value of a specified character. For example,

 ascii ('a') 

returns

 97 

If you call ascii() using a string longer than a single character, only the ASCII value of the first character will be returned. For example,

 ascii ('abcdefg') 

returns

 97 

chr()

The chr() function is the counterpart of the ascii() function. chr() returns the character associated with a specific ASCII value. For example,

 chr (97) 

returns

 a 

initcap()

The initcap() function accepts a string as a parameter and returns the string with the first letter of each word in the string capitalized. For example,

 initcap ('the quick brown fox...') 

returns

 The Quick Brown Fox... 

instr()

The instr() function is one of the most useful character functions. In its simplest incarnation, the function accepts two parameters: a string to be searched (let s call this string1 ) and a string to be searched for (let s call this string2 ) within string1. The syntax for instr() is as follows:

 instr ('JELLY BEANS AND APPLE TREES', 'E') 

The instr() function returns an integer value. In our example, the result of the function call is

 2 

indicating that the first occurrence of string2 in string1 occurs at the second character.

The function can accept a third parameter (let s call this parameter x ), which is used to indicate the starting position for the search within string1. For example,

 instr ('JELLY BEANS AND APPLE TREES', 'E', 3) 

returns

 8 

X can be negative, which instructs Oracle to count backwards from the end of string1 by x characters before starting the search. For example,

 instr ('JELLY BEANS AND APPLE TREES', 'E', -3) 

returns

 25 

As you can see, the value returned by this call to instr() is the position of string2 based on the total length of string1 and not on the starting position for the search.

But wait, there s more! instr() can also accept a fourth parameter (let s call this parameter y ), which tells Oracle which occurrence of the string2 should be returned. For example,

 instr ('JELLY BEANS AND APPLE TREES', 'E', 3, 2) 

returns

 21 

The values of the x and y parameters default to 1 (which means the values start with the first character in string1 and find the first occurrence of string2).

length()

The length() function accepts a string as a parameter and returns an integer value. For example,

 length ('HORSES') 

returns

 6 

lower()

The lower() function accepts a string and converts each character in the string to its lowercase equivalent. For example,

 lower ('APPLE') 

returns

 apple 

lpad()

The lpad() function accepts two parameters: a string (let s call this string1 ) and an integer value that represents the desired width of the string. Using this information, the function returns the value of string1 padded on the left side until string1 has the desired length. For example,

 lpad ('apple', 10) 

returns

 apple 

lpad() can also accept a third parameter, string2, which should be used to pad string1 to the desired width. For example,

 lpad ('apple', 10, '*') 

returns

 *****apple 

The third parameter of lpad() defaults to a single space ( ˜ ).

ltrim ()

In its simplest incarnation, the ltrim() function removes all spaces from the left side of a specified string. For example,

 ltrim ('     apple') 

returns

 apple 

The ltrim() function can also accept a second parameter, a set of characters that should be trimmed from the left side of the string. The string is trimmed until the function reaches the first character not appearing in the specified set. For example,

 ltrim ('apple', 'ap') 

returns

 le 

The second parameter of ltrim() defaults to a single space ( ˜ ).

replace()

In its simplest form, replace() accepts two parameters: a string to be searched (let s call it string1 ) and a string that should be removed from string1 (let s call it string2 ). For example,

 replace ('applesauce and marinara sauce', 'sauce') 

returns

 apple  and marinara 

replace() can also accept a third parameter (let s call it string3 ). When string3 is provided, all occurrences of string2 are replaced with string3. For example,

 replace ('applesauce and marinara sauce', 'sauce', ' tree') 

returns

 apple tree and marinara  tree 

The third parameter of replace() defaults to NULL .

rpad()

The rpad() function is identical to the lpad() function, with the exception that string1 is padded on the right side instead of the left.

rtrim ()

The rtrim() function is identical to the ltrim() function, with the exception that the string is trimmed on the right side instead of the left.

substr()

The substr() function is another extremely useful function for parsing strings. Most calls to substr() use three parameters. For example,

 substr ('apples and oranges', 1, 6) 

returns

 apples 

The call to substr() in this example instructs the function to start with the first character of the specified string and return the first six characters of the string. The third parameter can be left out, in which case, the function starts reading at the position indicated by the second parameter and reads until the end of the string is reached.

The value of the second parameter can be negative, which instructs the function to read backwards from the end of the string to find the starting position.

translate()

The translate() function accepts three parameters. For example,

 translate ('xyz', 'x', 'w') 

returns

 wyz 

Every occurrence of the second parameter within the string is replaced with the third parameter. A common use of this is function is to remove nonprinting or special characters from a string. For example,

 replace (<string variable>, chr (9), NULL) 

removes all the tabs from a string of text.

upper()

The upper() function is identical to the lower() function, with the exception that every character in the string is converted to its uppercase equivalent.

Date Functions

There aren t very many commonly used date functions. However, the one function that is commonly used ” SYSDATE() ”is, perhaps, the most commonly used function provided by SQL*Plus.

SYSDATE()

The SYSDATE() function returns the current date and time in the default Oracle date format. The default format for the date returned is

 MM-DD-YY 

It s very common to use SYSDATE() in conjunction with to_char() . For example,

 to_char (SYSDATE, 'MM-DD-YYYY HH:MI:SS'); 

returns a string containing not only the current date, but also the current time down to the second. The most common uses of SYSDATE() don t use a date format model:

 dStartDate := SYSDATE; 

Numeric Functions

The following functions all accept numeric values as parameters and return numeric values. Each of the functions listed in this section is accurate to 38 significant digits.

abs()

The abs() function accepts a single number as a parameter and returns the absolute value of that number. For example,

 abs (-9.37) 

returns

 9.37 

mod()

The mod() function accepts two numbers as parameters, x and y, and returns the remainder of x divided by y. For instance, calling the function to get the remainder of 5 divided by 2,

 mod (5, 2) 

returns

 1 

If y is zero, the function returns x. For instance,

 mod (5, 0) 

returns

 5 

power()

The power() function accepts two numbers as parameters, x and y, and returns the value of x raised to the power of y. For instance, calling the function to get the value of 10 raised to the 6th power,

 power (10, 6) 

returns

 1000000 

If the value of x is negative, the value of y must be an integer. For instance,

 power (-3.54, 2) 

returns

 12.5316 

round()

The round() function accepts a single number, x, as a parameter and rounds that number to the nearest integer value. For instance, calling the function with a value of 15.37,

 round (15.37) 

returns

 15 

The function can also take an integer parameter, y, following the number parameter. This parameter tells the function how many significant digits should be left in place. Using the same value as the previous example while passing 1 for the y parameter,

 round (15.37, 1) 

returns

 15.4 

If y is a negative value, the function rounds backwards from the decimal. For example,

 round (153.17, -1) 

returns

 150 

which has been rounded at the ones position in the final value.

The function can be called with only one parameter. In this case, y defaults to 0, and the function returns an integer value.

sqrt()

The sqrt() function accepts a single number as a parameter and returns the square root of that number. For instance, passing 4 to the function,

 sqrt (4) 

returns

 2 

This function can t accept a negative value as a parameter, but the function can return a decimal value. For example,

 sqrt (5.25) 

returns

 2.29128785 

trunc()

The trunc() function accepts a single number, x, as a parameter and returns that number truncated to 0 decimal places. For instance,

 trunc (15.37) 

returns

 15 

The function s second parameter, y, tells the function how many digits of the number should be left intact. This second parameter defaults to 0 and must be an integer value. Using the same value as the previous example, but passing 1 for y,

 trunc (15.37, 1) 

returns

 15.3 

Miscellaneous Functions

There are some commonly used functions that don t fall neatly into any of the other categories ”namely, decode() and nvl() .

decode()

Of all the functions provided by SQL*Plus, decode() is perhaps the most useful. A call to decode() can accept up to 255 parameters. Calls to decode() must follow this basic syntax:

 decode (expression, value, new value, default value); 

The decode() call can only have one expression and one default value. Value and new value must be a pair of parameters. Consider the following call to decode() :

 SELECT decode (course_grade, 'A', 4,                              'B', 3,                              'C', 2,                              'D', 1, 0) FROM   SCHEDULED_CLASSES WHERE  ssn           = '999999999' AND    course_number = 2103; 

This function is the closest equivalent to a case statement that can be found in SQL. The value and new value parameters don t have to be literal values; however, they can be expressions such as mathematical operations and function calls.

In the event that you try to port SQL statements between Oracle and another relational database, you should be aware that decode() is not an ANSI standard SQL function.

nvl()

The nvl() function is used to substitute a NULL value with another value. The most common example of a call to nvl() looks like this:

 SELECT nvl (base_salary, 20000) FROM   EMPLOYEES; 

This would replace any NULL value retrieved by the query with the number 20000.

Multi-Row SQL Functions

Multi-row SQL functions (also called group or aggregate functions) work with groups of rows. These functions ignore NULL values, except where noted in this section. The most commonly used multi-row SQL functions fall into the numeric group. All functions listed in this section are numeric functions.

avg()

The avg() function returns the average value of a numeric field from a group of rows. For example,

 SELECT avg (base_salary) FROM   EMPLOYEES; 

returns the average salary of all employees.

count()

The count() function counts the number of rows in a group of rows. This function counts all rows in the group, including those for which a NULL value is present. There are two ways of calling count() , as follows:

 SELECT count (*) FROM   EMPLOYEE_HISTORY WHERE  employee_number = 90213 AND    warning         = 'Y'; SELECT count (married) FROM   EMPLOYEE_HISTORY WHERE  employee_number = 90213 AND    warning         = 'Y'; 

The first example returns the total number of rows that match the query s WHERE clause. The second example returns the total number of rows that have a non- NULL value in the specified column.

max()

The max() function returns the highest value of a specified column from a group of rows. For example,

 SELECT max (base_salary) FROM   EMPLOYEES; 

returns the salary of the highest paid employee.

min()

The min() function returns the lowest value of a specified column from a group of rows. For example,

 SELECT min (base_salary) FROM   EMPLOYEES; 

returns the salary of the lowest paid employee.

sum()

The sum() function returns the total of all values for a specified column in a group of rows. For example,

 SELECT sum (vacation_days_used) FROM   EMPLOYEES; 

returns the total number of vacation days taken by employees this year.

PL/SQL Functions

PL/SQL provides two important error reporting functions that are not provided by SQL: SQLCODE() and SQLERRM() .

SQLCODE()

The SQLCODE() function provides the number of the latest Oracle error that has occurred. Following is an example of calling the SQLCODE() function:

 vErrorCode := SQLCODE; 

SQLERRM()

The SQLERRM() function provides the complete text of the most recent Oracle error that has occurred, including the error number provide by the SQLCODE() function. Following is an example of calling the SQLERRM() function:

 vErrorText := SQLERRM; 

Special Functions Used To Convert Numbers

The following functions aren t provided by Oracle, but I ve chosen to include them because they are useful in certain situations or are cleverly written examples of the types of work that a function can do.

Converting Numbers Between Bases

Projects that involve the conversion of data from legacy systems may have to deal with numerical data in a nondecimal base. The stored functions described in this section were designed to handle these conversions.

---

Author s Note:     The following functions were contributed by Shawn Ramsey of AT&T Wireless Services in Seattle, Washington. Many thanks to Shawn for allowing me to include these functions here.

---

Converting Base10 Numbers To Another Base

The Decimal_2_Any_Base() function converts numbers from base10 to any base between base2 and base36 (inclusive). Listing A.1 shows the complete source code for the function.

Listing A.1 The Decimal_2_Any_Base() function.

 FUNCTION Decimal_2_Any_Base    (p_DecNo IN    integer,     p_Base  IN    integer) RETURN varchar2 IS -- ***************************************************************** -- DESCRIPTION: Takes a decimal number and converts the base to --              any base from 2 to 36 -- -- Parameters:  p_DecNo = Decimal Number to convert. --              p_Base  = Base to convert the number to. -- -- Returns:     Character string of the number converted to the --              desired base. NULL if invalid conversion or --              a conversion error occurs. -- -- AUTHOR:      Shawn M. Ramsey -- -- REVISION HISTORY -- Date         Reviser     Change -- ---------------------------------------------------------------- -- 28 JAN 1997  S. Ramsey   Function creation. -- -- ***************************************************************** ---- Characters for conversion v_AnyNo varchar2(36) := '0123456789ABCDEFGHIJKLMNOPQRSTUVWXYZ'; ---- Variables for conversion n_DecNo  integer        := p_DecNo; n_Base   integer        := p_Base; v_Return varchar2 (100) := NULL; n_Cnt    integer        := 0; n_Val    integer        := 0; BEGIN    ---- Check Base Number and Number to convert    IF (NOT n_Base BETWEEN 2 AND 36) OR (n_DecNo < 0) THEN       RETURN NULL;    ELSIF (n_DecNo = 0) THEN       RETURN '0';    END IF;    ---- Initialize Variables    v_Return := '';    n_Cnt    := 0 ;    ---- Loop and convert    WHILE (n_DecNo <> 0) LOOP       n_Val    := mod (n_DecNo,                       (n_Base**(n_Cnt+1)))/(n_Base**n_cnt);       n_DecNo  := n_DecNo - (n_Val * (n_Base**n_Cnt));       v_Return := substr (v_AnyNo, n_Val+1, 1)  v_Return;       n_Cnt    := n_Cnt + 1;    END LOOP;    RETURN v_Return; EXCEPTION    WHEN OTHERS THEN         RETURN NULL; END; 

Converting Numbers Of Odd Bases To Base10

The Any_2_Base10() function can be used to convert a number in any base between base2 and base36 to its base10 equivalent. Listing A.2 shows the complete source code for the function.

Listing A.2 The Any_2_Base10() function.

 FUNCTION ANY_2_Base10()    (p_Conv  IN varchar2,     p_Base  IN integer) RETURN varchar2 IS -- ***************************************************************** -- DESCRIPTION: Takes a number in any base from 2 to 36 and --              converts it to an integer number. -- -- Parameters:  p_Conv  = Number to convert. --              p_Base  = Base to convert the number from. -- -- Returns:     Number converted to the decimal. NULL if invalid --              conversion or a conversion error occurs. -- -- AUTHOR:      Shawn M. Ramsey -- -- REVISION HISTORY -- Date         Reviser     Change -- ---------------------------------------------------------------- -- 28 JAN 1997  S. Ramsey   Function creation. -- -- ***************************************************************** ---- Characters for conversion v_AnyNo   varchar2 (36) := '0123456789ABCDEFGHIJKLMNOPQRSTUVWXYZ'; ---- Exception e_BadData exception; ---- Variables for conversion v_Conv    varchar2 (200) := upper (p_Conv); v_ValChar varchar2 (200) := NULL; n_Base    integer        := p_Base; n_Return  integer        := NULL; n_Cnt     integer        := 0; n_Val     integer        := 0; BEGIN    ---- Check Base Number and Number to convert    IF (NOT n_Base BETWEEN 2 AND 36) OR (v_Conv IS NULL) THEN       RETURN NULL;    END IF;    ---- Initialize Variables    n_Return  := 0;    n_Cnt     := 0;    v_ValChar := substr (v_AnyNo, 1, n_Base);    ---- Validate String    FOR n_Cnt IN 1..length (v_Conv) LOOP        IF (v_ValChar NOT LIKE '%'            substr(v_Conv, n_Cnt, 1)  '%') THEN              RAISE e_BadData;        END IF;    END LOOP;    ---- Loop and convert    FOR n_Cnt IN 1..length (v_Conv) LOOP        IF (v_ValChar NOT LIKE '%'            substr (v_Conv, n_Cnt, 1)  '%') THEN              RAISE e_BadData;        END IF;    END LOOP;    ---- Convert Number    FOR n_Cnt IN REVERSE 1..length (v_Conv) LOOP        n_Val    := instr (v_ValChar, substr (v_Conv, n_Cnt, 1)) - 1;        n_Return :=  n_Return + (n_Val *                    (n_Base**(length (v_Conv) - n_Cnt)));    END LOOP;    RETURN n_Return; EXCEPTION    WHEN OTHERS THEN         RETURN NULL; END; 

Is_Number()

The Is_Number() function is a relatively simple function that depends on an exception being raised to work properly. A function like this can be useful when data occasionally contains characters mixed with numerals. Listing A.3 gives a complete listing for the function.

Listing A.3 The Is_Number() function.

 FUNCTION Is_Number (vValue IN     number) RETURN boolean IS    nValue   number; BEGIN    nValue := to_number (vValue);    RETURN TRUE; EXCEPTION    WHEN INVALID_NUMBER THEN         RETURN FALSE; END Is_Number; 

Summary

This has been a discussion of the most commonly used SQL and PL/SQL functions. There are several more functions that haven t been discussed, but these functions aren t commonly encountered . If you encounter a call to a function that you don t recognize and you can t locate the source code, it might be a function that wasn t discussed here. Your best bet is to check for the function in the PL/SQL User s Guide And Reference provided by Oracle.

Table of Contents