Functions Form ever follows function. Louis Henri Sullivan, The Tall Office Building Artistically Considered Functions are similar to operators in that they manipulate data items and return a result. Functions differ from operators in the format in which they appear with their arguments. This format allows them to operate on zero, one, two, or more arguments: function(argument, argument, ...) This chapter describes two types of functions: SQL Functions User-Defined Functions SQL Functions SQL functions are built into Oracle and are available for use in various appropriate SQL statements. Do not confuse SQL functions with user functions written in PL/SQL. User functions are described in "User-Defined Functions". For information about functions used with Oracle interMedia, see Oracle8i interMedia Audio, Image, and Video User's Guide and Reference. If you call a SQL function with an argument of a datatype other than the datatype expected by the SQL function, Oracle implicitly converts the argument to the expected datatype before performing the SQL function. See "Data Conversion". If you call a SQL function with a null argument, the SQL function automatically returns null. The only SQL functions that do not follow this rule are CONCAT, DECODE, DUMP, NVL, and REPLACE. In the syntax diagrams for SQL functions, arguments are indicated by their datatypes, following the conventions described in "Syntax Diagrams and Notation" in the Preface of this reference. When the parameter "function" appears in SQL syntax, replace it with one of the functions described in this section. Functions are grouped by the datatypes of their arguments and their return values. The general syntax is as follows: function::=
Table 4-1 lists the built-in SQL functions in each of the groups illustrated above except user-defined functions. All of the built-in SQL functions are then described in alphabetical order. User-defined functions are described at the end of this chapter. Table 4-1 SQL Function Groups Group Functions Description Single-Row Functions Single-row functions return a single result row for every row of a queried table or view. Single-row functions can appear in select lists (if the SELECT statement does not contain a GROUP BY clause), WHERE clauses, START WITH clauses, and CONNECT BY clauses.
Number Functions Number functions accept numeric input and return numeric values. Most of these functions return values that are accurate to 38 decimal digits. The transcendental functions COS, COSH, EXP, LN, LOG, SIN, SINH, SQRT, TAN, and TANH are accurate to 36 decimal digits. The transcendental functions ACOS, ASIN, ATAN, and ATAN2 are accurate to 30 decimal digits. ABS ACOS ADD_MONTHS ATAN ATAN2 CEIL COS COSH EXP FLOOR LN LOG MOD POWER ROUND (Number Function) SIGN SIN SINH SQRT TAN TANH TRUNC (Number Function)
Character Functions returning character values Character functions that return character values, unless otherwise noted, return values with the datatype VARCHAR2 and are limited in length to 4000 bytes. Functions that return values of datatype CHAR are limited in length to 2000 bytes. If the length of the return value exceeds the limit, Oracle truncates it and returns the result without an error message. CHR CONCAT INITCAP LOWER LPAD LTRIM NLS_INITCAP NLS_LOWER NLSSORT NLS_UPPER REPLACE RPAD RTRIM SOUNDEX SUBSTR SUBSTRB TRANSLATE TRIM UPPER
Character Functions All of the functions listed below return number values.
returning number values ASCII INSTR INSTRB LENGTH LENGTHB
Date Functions Date functions operate on values of the DATE datatype. All date functions return a value of DATE datatype, except the MONTHS_BETWEEN function, which returns a number. ADD_MONTHS LAST_DAY MONTHS_BETWEEN NEW_TIME NEXT_DAY ROUND (Date Function) SYSDATE TRUNC (Date Function)
Conversion Functions Conversion functions convert a value from one datatype to another. Generally, the form of the function names follows the convention datatype TO datatype. The first datatype is the input datatype. The second datatype is the output datatype. This section lists the SQL conversion functions. CHARTOROWID CONVERT HEXTORAW RAWTOHEX ROWIDTOCHAR TO_CHAR (date conversion) TO_CHAR (number conversion) TO_DATE TO_LOB TO_MULTI_BYTE TO_NUMBER TO_SINGLE_BYTE TRANSLATE ... USING
Miscellaneous Single Row Functions The following single-row functions do not fall into any of the other single-row function categories.
BFILENAME DUMP EMPTY_[B | C]LOB GREATEST LEAST NLS_CHARSET_DECL_LEN NLS_CHARSET_ID NLS_CHARSET_NAME NVL SYS_CONTEXT SYS_GUID UID USER USERENV VSIZE
Object Reference Functions Object functions manipulate REFs, which are references to objects of specified object types. For more information about REFs, see Oracle8i Concepts and Oracle8i Application Developer's Guide - Fundamentals.
DEREF MAKE_REF REF REFTOHEX VALUE
Aggregate Functions Aggregate functions return a single row based on groups of rows, rather than on single rows. Aggregate functions can appear in select lists and HAVING clauses. If you use the GROUP BY clause in a SELECT statement, Oracle divides the rows of a queried table or view into groups. In a query containing a GROUP BY clause, all elements of the select list must be expressions from the GROUP BY clause, expressions containing aggregate functions, or constants. Oracle applies the aggregate functions in the select list to each group of rows and returns a single result row for each group. If you omit the GROUP BY clause, Oracle applies aggregate functions in the select list to all the rows in the queried table or view. You use aggregate functions in the HAVING clause to eliminate groups from the output based on the results of the aggregate functions, rather than on the values of the individual rows of the queried table or view. For more information on the GROUP BY clause and HAVING clauses, see the "GROUP BY Examples" and the "HAVING" clause.
Many aggregate functions accept these options: DISTINCT causes an aggregate function to consider only distinct values of the argument expression. ALL causes an aggregate function to consider all values, including all duplicates. For example, the DISTINCT average of 1, 1, 1, and 3 is 2; the ALL average is 1.5. If neither option is specified, the default is ALL.
All aggregate functions except COUNT(*) and GROUPING ignore nulls. You can use the NVL in the argument to an aggregate function to substitute a value for a null. If a query with an aggregate function returns no rows or only rows with nulls for the argument to the aggregate function, the aggregate function returns null.
Form ever follows function. Louis Henri Sullivan, The Tall Office Building Artistically Considered Functions are similar to operators in that they manipulate data items and return a result. Functions differ from operators in the format in which they appear with their arguments. This format allows them to operate on zero, one, two, or more arguments: function(argument, argument, ...)
This chapter describes two types of functions: SQL Functions User-Defined Functions SQL Functions
SQL functions are built into Oracle and are available for use in various appropriate SQL statements. Do not confuse SQL functions with user functions written in PL/SQL. User functions are described in "User-Defined Functions". For information about functions used with Oracle interMedia, see Oracle8i interMedia Audio, Image, and Video User's Guide and Reference. If you call a SQL function with an argument of a datatype other than the datatype expected by the SQL function, Oracle implicitly converts the argument to the expected datatype before performing the SQL function. See "Data Conversion". If you call a SQL function with a null argument, the SQL function automatically returns null. The only SQL functions that do not follow this rule are CONCAT, DECODE, DUMP, NVL, and REPLACE. In the syntax diagrams for SQL functions, arguments are indicated by their datatypes, following the conventions described in "Syntax Diagrams and Notation" in the Preface of this reference. When the parameter "function" appears in SQL syntax, replace it with one of the functions described in this section. Functions are grouped by the datatypes of their arguments and their return values. The general syntax is as follows: function::=
Table 4-1 lists the built-in SQL functions in each of the groups illustrated above except user-defined functions. All of the built-in SQL functions are then described in alphabetical order. User-defined functions are described at the end of this chapter. Table 4-1 SQL Function Groups
Group Functions Description
Single-Row Functions
Single-row functions return a single result row for every row of a queried table or view. Single-row functions can appear in select lists (if the SELECT statement does not contain a GROUP BY clause), WHERE clauses, START WITH clauses, and CONNECT BY clauses.
Number Functions Number functions accept numeric input and return numeric values. Most of these functions return values that are accurate to 38 decimal digits. The transcendental functions COS, COSH, EXP, LN, LOG, SIN, SINH, SQRT, TAN, and TANH are accurate to 36 decimal digits. The transcendental functions ACOS, ASIN, ATAN, and ATAN2 are accurate to 30 decimal digits.
ABS ACOS ADD_MONTHS ATAN ATAN2 CEIL COS COSH
EXP FLOOR LN LOG MOD POWER ROUND (Number Function)
SIGN SIN SINH SQRT TAN TANH TRUNC (Number Function)
Character Functions returning character values Character functions that return character values, unless otherwise noted, return values with the datatype VARCHAR2 and are limited in length to 4000 bytes. Functions that return values of datatype CHAR are limited in length to 2000 bytes. If the length of the return value exceeds the limit, Oracle truncates it and returns the result without an error message.
CHR CONCAT INITCAP LOWER LPAD LTRIM NLS_INITCAP
NLS_LOWER NLSSORT NLS_UPPER REPLACE RPAD RTRIM SOUNDEX
SUBSTR SUBSTRB TRANSLATE TRIM UPPER
Character Functions All of the functions listed below return number values.
returning number values ASCII INSTR
INSTRB LENGTH
LENGTHB
Date Functions Date functions operate on values of the DATE datatype. All date functions return a value of DATE datatype, except the MONTHS_BETWEEN function, which returns a number.
ADD_MONTHS LAST_DAY MONTHS_BETWEEN
NEW_TIME NEXT_DAY ROUND (Date Function)
SYSDATE TRUNC (Date Function)
Conversion Functions Conversion functions convert a value from one datatype to another. Generally, the form of the function names follows the convention datatype TO datatype. The first datatype is the input datatype. The second datatype is the output datatype. This section lists the SQL conversion functions.
CHARTOROWID CONVERT HEXTORAW RAWTOHEX ROWIDTOCHAR
TO_CHAR (date conversion) TO_CHAR (number conversion) TO_DATE
TO_LOB TO_MULTI_BYTE TO_NUMBER TO_SINGLE_BYTE TRANSLATE ... USING
Miscellaneous Single Row Functions The following single-row functions do not fall into any of the other single-row function categories.
BFILENAME DUMP EMPTY_[B | C]LOB GREATEST LEAST
NLS_CHARSET_DECL_LEN NLS_CHARSET_ID NLS_CHARSET_NAME NVL SYS_CONTEXT
SYS_GUID UID USER USERENV VSIZE
Object Reference Functions
Object functions manipulate REFs, which are references to objects of specified object types. For more information about REFs, see Oracle8i Concepts and Oracle8i Application Developer's Guide - Fundamentals.
DEREF MAKE_REF
REF REFTOHEX
VALUE
Aggregate Functions
Aggregate functions return a single row based on groups of rows, rather than on single rows. Aggregate functions can appear in select lists and HAVING clauses. If you use the GROUP BY clause in a SELECT statement, Oracle divides the rows of a queried table or view into groups. In a query containing a GROUP BY clause, all elements of the select list must be expressions from the GROUP BY clause, expressions containing aggregate functions, or constants. Oracle applies the aggregate functions in the select list to each group of rows and returns a single result row for each group. If you omit the GROUP BY clause, Oracle applies aggregate functions in the select list to all the rows in the queried table or view. You use aggregate functions in the HAVING clause to eliminate groups from the output based on the results of the aggregate functions, rather than on the values of the individual rows of the queried table or view. For more information on the GROUP BY clause and HAVING clauses, see the "GROUP BY Examples" and the "HAVING" clause.
Many aggregate functions accept these options: DISTINCT causes an aggregate function to consider only distinct values of the argument expression. ALL causes an aggregate function to consider all values, including all duplicates. For example, the DISTINCT average of 1, 1, 1, and 3 is 2; the ALL average is 1.5. If neither option is specified, the default is ALL.
All aggregate functions except COUNT(*) and GROUPING ignore nulls. You can use the NVL in the argument to an aggregate function to substitute a value for a null. If a query with an aggregate function returns no rows or only rows with nulls for the argument to the aggregate function, the aggregate function returns null.
AVG COUNT GROUPING
MAX MIN STDDEV