- Data Definition Language
- Data Manipulation Language
- SQL Functions
- Transactions

## 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