- Data Definition Language
- Data Manipulation Language
- SQL Functions
- Transactions
This chapter is from the book
This chapter is from the book
This chapter is from the book
SQL Functions
Oracle SQL has three main types of functions: single-row functions, user-defined functions, and aggregate functions. Single-row functions are usually referred to simply as functions and are the type we will be describing here. User-defined functions are functions that we write ourselves, using either Java or PL/SQL, and can be used like regular SQL functions; we will examine those in Chapter 5, "PL/SQL." Aggregate functions are a special kind of function that combines multiple values returned by a query that returns multiple rows of data—to calculate a total or an average, for example. Aggregate functions will be examined in the next chapter, "More SQL: Queries, Subqueries, and Views."
Single-row functions are similar to Java methods: They take zero or more parameters and return a single value. (Unlike Java, however, they must return a value; there is no equivalent to the void return type.) SQL functions can be used anywhere that a literal constant or a select list item can be used, and its parameters can be either constants or select list items.
We can use functions in a SELECT statement to modify a select list item. The following example, using the UPPER() function, will convert the names of fruits to uppercase:
SQL> SELECT UPPER(FRUIT) FROM FRUITS; UPPER(FRUI ---------- APPLE APPLE MANGO MANGOSTEEN DURIAN ORANGE
We can also use functions in a SELECT statement's WHERE clause. The following example selects fruits that have names that are six letters long:
SQL> SELECT FRUIT FROM FRUITS 2 WHERE LENGTH(FRUIT)=6; FRUIT ---------- Durian Orange
Note that when we call the function for a value in the select list, the function is called once per each returned row. (If the table is large, we normally restrict the size of the result set to a manageable size by using a WHERE clause.) But if we use a function in the WHERE clause, it will be called for every row in the table; unless we have created a function-based index, this can cause performance problems for large tables.
There are four main categories of functions:
- Numeric
- Character
- Date
- Miscellaneous
Functions can be categorized either on the basis of the first and principal parameter or on the basis of the return value. The function LENGTH(), for example, would be a character function based on the parameter it takes, but a numeric function based on its return value. Here, they are categorized according to their first parameter. A few functions do not take any parameters and are categorized as miscellaneous.
Because Oracle has a very large number of functions, only a few common ones will be briefly covered. Refer to the Oracle SQL Reference for a complete list.
Numeric Functions
All numeric functions take one or more numeric values and return a numeric value. These typically perform mathematical operations, such as rounding, calculating the square root, raising to a power, etc. Here are a few commonly used functions:
ABS(n)—Returns the absolute value of n.
Example: ABS(-123)
Returns: 123
MOD(m, n)—Returns m modulo n, that is, the remainder that results from dividing m integrally by n.
Example: MOD(15, 4)
Returns: 3
POWER(m, n)—Returns m raised to the power n. Both m and n can be floating point numbers, unless m is negative, in which case, n must be an integer.
Example: POWER(2, 8)
Returns: 256
ROUND(m [, n])—Rounds m to the nearest integer, unless n is specified, in which case, m is rounded to the number of decimal places corresponding to n. If n is positive, it refers to places to the right of the decimal point; if n is negative it refers to places to the left of the decimal point.
Example: ROUND(2.718281828459)
Returns: 3
Example: ROUND(2.718281828459, 5)
Returns: 2.71828
Example: ROUND(186282.397, -3)
Returns: 186000
SQRT(n)—Returns the square root of n.
Example: SQRT(256)
Returns: 16
Character Functions
Character functions take a string as the first parameter and sometimes additional character or numeric parameters. Most return a string, but a few, such as LENGTH(), return a numeric value. Here are some commonly used character functions:
ASCII(c)—Returns decimal value of the character c in the database character set. Note that this function is misnamed, because the database character set is unlikely to actually be ASCII.
Example: ASCII('?')
Returns: 146
(Assuming the database character set is WE8MSWIN1252)
CHR(n)—Returns the character that the decimal value n represents in the current database character set.
Example: CHR(65)
Returns: A
CONCAT(string1, string2)—Returns a string that is the concatenation of string1 and string2. This is equivalent to the (||) concatenation operator.
Example: CONCAT('top', 'hat')
Returns: tophat
LOWER(string)—Returns string with all letters in lowercase.
Example: LOWER('Your VOICE!')
Returns: your voice!
LTRIM(string [, chars])—Returns string with all blanks preceding the first nonblank character removed. If a string chars is specified, any characters appearing in chars are removed up to the first character that does not appear in chars.
Example: LTRIM(' ...uh, hello?')
Returns: ...uh, hello?
Example: LTRIM(' ...uh, hello?', ' .')
Returns: uh, hello?
SUBSTR(string, start [, length])—Returns the portion of string beginning with the character at position start. (The first character in the string is 1.) If length is specified, only the number of characters corresponding to length are returned; otherwise, the remainder of the string is returned.
Example: SUBSTR('Supercalifragilisticexpialidocious', 21)
Returns: expialidocious
Example: SUBSTR('Supercalifragilisticexpialidocious', 15, 4)
Returns: list
UPPER(string)—Returns string with all letters in lowercase.
Example: UPPER('crust')
Returns: CRUST
Date Functions
ADD_MONTHS(date, n)—Returns a date n months later than date. If the day of the month in date is greater than the last day of the resulting month, the last day of the resulting month is returned.
Example: ADD_MONTHS('7-JUL-2000', 1)
Returns: 07-AUG-00
Example: ADD_MONTHS('31-DEC-2003', 2)
Returns: 28-FEB-04
CURRENT_DATE—Returns current date and time according to the current database session's time zone. Takes no arguments and, consequently, does not use parentheses. See TO_CHAR() below for formatting information.
Example: CURRENT_DATE
Returns: 14-SEP-02
LAST_DAY(date)—Returns the date of the last day of the month that contains date.
Example: LAST_DAY('19-DEC-2002')
Returns: 31-DEC-02
MONTHS_BETWEEN(date1, date2)—Returns the number of months between date1 and date2. This includes a fractional part unless they are the same day of the month or both are the last day of the month. The value is positive if date1 is later than date2, negative if date1 is earlier than date2.
Example: MONTHS_BETWEEN('21-MAR-2003', '15-JAN-2003')
Returns: 2.19354839
Example: MONTHS_BETWEEN('31-JAN-2003', '1-FEB-2003')
Returns: -.03225806
NEXT_DAY(date, dayOfWeek)—Returns the date of the next weekday corresponding to dayOfWeek (for example, Tuesday) following date. The weekday is in the current session language and can be abbreviated.
Example: NEXT_DAY('1-MAR-2003','FRI')
Returns: 07-MAR-03
SYSDATE—Returns current date and time according to the database. Takes no arguments and, consequently, does not use parentheses.
Example: SYSDATE
Returns: 14-SEP-02
TO_CHAR(date [, format])—Returns a string representing date. If format is not provided, the default date format is used. The optional format parameter is a string that provides a template for the date formatting. Table 3–9 lists the most common format elements that it can include:
TABLE 3–9 Most Common Format Elements
|
am |
Meridian indicator (lowercase) |
|
AM |
Meridian indicator (uppercase) |
|
Day |
Name (initial uppercase) |
|
DAY |
Name of day of week (uppercase) |
|
DD |
Day of month (1–31) |
|
DY |
Abbreviated day of week |
|
HH |
Hour of day (12-hour clock) |
|
HH12 |
Hour of day (12-hour clock) |
|
HH24 |
Hour of day (24-hour clock) |
|
MI |
Minutes (0–59) |
|
MM |
Month (01–12) |
|
MON |
Abbreviated name of month (uppercase) |
|
Mon |
Abbreviated name of month (initial uppercase) |
|
MONTH |
Name of month (uppercase) |
|
Month |
Name of month (initial uppercase) |
|
PM |
Same as AM |
|
pm |
Same as am |
|
SS |
Seconds (0–59) |
|
YYYY |
Year (four digits) |
|
YY |
Year (two digits) |
In addition to these elements, the format string can include punctuation and double-quoted text.
Example: TO_CHAR(TO_DATE('19-JAN-2003', 'Month DD,YYYY HH:MI:SS am')
Returns: January 19,2003 12:00:00 am
Example: TO_CHAR(CURRENT_DATE, '"Today is" Day')
Returns: Today is Saturday