Oracle function list

PL/SQL single-row functions and group functions
A function is a program with zero or multiple parameters and a return value. Oracle has a series of built-in functions in SQL, which can be called SQL or PL/SQL statements. functions are mainly divided into two categories:

Single Row Function

Group functions

This article will discuss how to use single-row functions and use rules.

Single Row functions in SQL

SQL and PL/SQL contain many types of functions, including character, number, date, conversion, and hybrid functions. Therefore, these functions can be collectively called single-row functions. These functions can be used in clauses such as select, where, and order by. For example, the following example contains single-row functions such as to_char, upper, and soundex.
Select ename, to_char (hiredate, 'day, DD-mon-yyyy ') from empwhere upper (ename) Like 'al %' order by soundex (ename)

Single-row functions can also be used in other statements, such as the set clause of update, the values clause of insert, And the WHERE clause of delet. During the certification exam, pay special attention to using these functions in select statements, so our attention is also concentrated in the SELECT statement.

Null and single-row Functions

It is very difficult to understand null, and even a very experienced person is still confused about it. Null indicates an unknown data or a null value. Any operand of the arithmetic operator is null, and the result is a null value. This rule is also applicable to many functions, only Concat, decode, dump, nvl, and replace can return non-null values when the null parameter is called. Among these functions, nvl is the most important because it can directly process null values. nvl has two parameters: nvl (x1, x2), X1, and X2 expressions, if X1 is null, return X2; otherwise, return X1.

Let's take a look at the EMP data table. It includes two items: salary and bonus. We need to calculate the total compensation.
Column name emp_id salary bonuskey type PK nulls/unique NN, u nnfk table datatype Number numberlength 11.2 11.2

It is not simply to add up the salary and bonus. If a row is null, the result will be null. For example:
Update empset salary = (salary + bonus) * 1.1

In this statement, both the employee's salary and bonus will be updated to a new value, but if there is no bonus, that is, salary + null, the wrong conclusion will be drawn, in this case, the nvl function is used to exclude the impact of null values.
The correct statement is:
Update empset salary = (salary + nvl (bonus, 0) * 1.1
Single-line string functions

A single-line string function is used to manipulate string data. Most of them have one or more parameters, and most of them return strings.

ASCII ()
C1 is a string and returns the ASCII code of the first letter of C1. Its Inverse Function is CHR ()
Select ASCII ('A') big_a, ASCII ('Z') big_z from empbig_a big_z65 122

CHR (<I>) [nchar_cs]
I is a number. The function returns the characters in decimal format.
Select CHR (65), CHR (122), CHR (223) from empchr65 chr122 chr223a Z B

Concat (,)
C1 and c2 are strings. The function connects C2 to the end of C1. If C1 is null, C2. if C2 is null, C1 is returned. If c1 and c2 are both null, returns null. He and operator | the returned result is the same
Select Concat ('slobo', 'svoboda') username from dualusernameslobo syoboda

Initcap ()
C1 is a string. The function returns the first letter of each word in upper case and other letters in lower case. Words are limited by spaces, control characters, and punctuation marks.
Select initcap ('Veni, vedi, vici') Ceasar from dualceasarveni, vedi, vici

Instr (, [, <I> [,])
C1 and c2 are strings, and I and j are integers. The function returns the position where C2 appears for the nth occurrence of C1, and searches for the position starting from the nth occurrence of C1. If no expected character is found, 0 is returned. If I is a negative number, the search proceeds from right to left, but the position is calculated from left to right, the default values of I and j are 1.
Select instr ('Mississippi ',' I ', 3,3) from dualinstr ('Mississippi', 'I', 3,3) 11 select instr ('Mississippi ',' I ', -2, 3) from dualinstr ('Mississippi ',' I ', 3, 3) 2

Limit B (, [, I [, J])
Like the instr () function, it only returns bytes. For a single byte, bytes B () equals to instr ()

Length ()
C1 is a string and returns the length of C1. If C1 is null, null is returned.
Select length ('ipso facto') ergo from dualergo10

Lengthb ()
Returns bytes like length.

Lower ()
Returns the lowercase character of C, which is often found in the where substring.
Select lower (colorname) from itemdetail where lower (colorname) like '% White %' colornamewinterwhite

Lpad (, <I> [,])
C1 and c2 are strings and I is an integer. On the left side of C1, use the C2 string to supplement the length I, which can be repeated multiple times. If I is less than the length of C1, only the C1 characters that are as long as I are returned, and the others are truncated. The default value of C2 is single space. See rpad.
Select lpad (answer, 7, '') padded, answer unpadded from question; padded unpadded Yes yesno nomaybe maybe

Ltrim (,)
Remove the leftmost character from C1 so that the first character is not in C2. If there is no C2 character, C1 will not change.
Select ltrim ('Mississippi ', 'mis') from dualltrppi

Rpad (, <I> [,])
On the right side of C1, use the C2 string to supplement the length I, which can be repeated multiple times. If I is less than the length of C1, only the C1 characters that are as long as I are returned, and the others are truncated. The default value of C2 is a single space. Others are similar to lpad.

Rtrim (,)
Remove the rightmost character from C1 so that the last character is not in C2. If there is no C2 character, C1 will not change.

Replace (, [,])
C1, C2, and C3 are strings. The function uses C3 instead of C2 that appears in C1 and returns the result.
Select Replace ('uptown', 'up', 'low') from dualreplacedowntown

Stbstr (, <I> [,])
C1 is a string, where I and j are integers. Starting from the I-bit of C1, a substring with the length of J is returned. If J is empty, it is returned until the end of the string.
Select substr ('message', 1, 4) from dualsubsmess

Substrb (, <I> [,])
It is roughly the same as substr, except that I and J are calculated in bytes.

Soundex ()
Returns a word with the same pronunciation as C1.
Select soundex ('dawes') Dawes soundex ('daws') daws, soundex ('dawson') from dualdawes Daws dawsond200 d200 d250

Translate (,,)
Replace the characters in C1 with those in C2 with C3.
Select translate ('fumble ', 'U', 'ar') test from dualtextramble

Trim ([[] From C3)
Delete the first, last, or all of the C3 strings.
Select trim ('space padded') trim from dual trimspace padded

Upper ()
Returns the uppercase value of C1, which is often in the where substring.
Select name from dual where upper (name) Like 'Ki % 'nameking
Single-row numeric Functions

A single-row numeric function operates on numeric data and performs mathematical and arithmetic operations. All functions have numeric parameters and return numeric values. The operands and values of all trigonometric functions are radians rather than degrees. Oracle does not provide built-in radians and angle conversion functions.

ABS ()
Returns the absolute value of N.

ACOs ()
Returns the number between-1 and 1 in the anti-Yuxuan function. N indicates radians
Select ACOs (-1) PI, ACOs (1) zero from dualpi zero3.14159265 0

Asin ()
Returns-1 to 1, and N to indicate radians.

Atan ()
Returns the arc tangent value of N. N indicates radians.

Ceil ()
Returns the smallest integer greater than or equal to n.

Cos ()
Returns the remainder Xuan value of N, and N is a radian.

Cosh ()
Returns the hyperbolic remainder of n, where n is a number.
Select cosh (<1.4>) from dualcosh (1.4) 2.15089847

Exp ()
Returns the N power of E, E = 2.71828183.

Floor ()
Returns the largest integer less than or equal to n.

Ln ()
Returns the natural logarithm of N. N must be greater than 0.

Log (,)
Returns the base N2 logarithm of N1.

MoD ()
Returns the remainder of N1 divided by N2,

Power (,)
Returns the N2 power of N1.

Round (,)
Return the value of N1 rounded to the right of the decimal point. The default value of N2 is 0. This is the nearest integer to the decimal point. If N2 is a negative number, it is rounded to the corresponding digit on the left of the decimal point, n2 must be an integer.
Select round (12345,-2), round (12345.54321, 2) from dualround (12345,-2) round (12345.54321, 2) 12300 12345.54

Sign ()
If n is negative,-1 is returned. If n is positive, 1 is returned. If n = 0, 0 is returned.

Sin ()
Returns the positive and negative values of N, and N is a radian.

Sinh ()
Returns the hyperbolic Xuan value of N, which is radian.

SQRT ()
Returns the square root of N, and N is a radian.

Tan ()
Returns the tangent of N. N is a radian.

Tanh ()
Returns the hyperbolic tangent of N. N is a radian.

Trunc (,)
Return the N1 value from the ending point to the N2 decimal point. The default value of N2 is 0. When N2 is the default value, the ending point of N1 is an integer. If N2 is a negative value, it is truncated at the right of the decimal point.

Single Row date functions

A single-row date function operates on data types. The vast majority of data type parameters are available, and the vast majority of returned data types are also values.

Add_months (, <I>)
Returns the result after date D plus I months. I can make any integer. If I is a decimal point, the database implicitly converts it to an integer, and the part after the decimal point is truncated.

Last_day ()
Returns the last day of the month containing the date D.

Months_between (,)
Returns the number of months between D1 and D2. If the date of D1 and D2 is the same, or the last day of the month is used, an integer is returned, otherwise, the returned result contains a score.

New_time (,,)
D1 is a date data type. When the date and time in Zone tz1 are d, the date and time in Zone tz2. Tz1 and tz2 are strings.

Next_day (,)
Returns the first day of the condition given by Dow after the date D. Dow specifies a day in a week using the language given in the current session, and returns the same time component as the time component of D.
Select next_day ('01-Jan-2000 ', 'monday') "1st Monday", next_day ('01-Nov-2004', 'tuesday') + 7 "2nd Tuesday ") from dual; 1st Monday 2nd Tuesday03-Jan-2000 09-nov-2004

Round ([,])
Round the date d in the format specified by FMT, and FMT is a string.

Syadate
The function has no parameters. The current date and time are returned.

Trunc ([,])
Returns the date D of the unit specified by FMT.
Single Row Conversion Function

The single-row conversion function is used to operate on multiple data types and convert data types.

Chartorwid ()
C makes a string, and the function converts C to the rwid data type.
Select test_id from test_case where rowid = chartorwid ('aaaa0saacaaaaliaaa ')

Convert (, [,])
String at the end of C. dset and sset are two character sets. The function converts string C from the sset character set to the dset character set, and the sset is set to the database character set by default.

Hextoraw ()
X is a hexadecimal string. The function converts hexadecimal X to the raw data type.

Rawtohex ()
X is a raw data string. The function converts the raw data class to a hexadecimal data type.

Rowidtochar ()
The function converts the rowid data type to the char data type.

To_char ([[,)
X is a data or number data type. The function converts X to the char data type in the specified format of FMT. If X is the date nlsparm = nls_date_language, the language used to control the returned month and day. If X is the number nlsparm = nls_numeric_characters, it is used to specify the Separators for decimal places and kilobytes, as well as currency symbols.
Nls_numeric_characters = "DG", nls_currency = "string"

To_date ([,[,)
C Indicates a string, and FMT indicates a string in a special format. Returns the C language in the FMT Format. The nlsparm indicates the language used. The function converts string C to the date data type.

To_multi_byte ()
C Represents a string, and the function converts the truncation character of C to multi-byte characters.

To_number ([,[,)
C Indicates a string, FMT indicates a special string, and the function return value is displayed in the format specified by FMT. Nlsparm indicates the language, and the function returns numbers represented by C.

To_single_byte ()
Converts multiple characters in string C into equivalent single-byte characters. This function is used only when the database character set contains single-byte and multi-byte characters at the same time.

Other single-row Functions

Bfilename (
,)
DIR is a directory object, and file is a file name. The function returns an empty bfile Location value indicator. The function is used to initialize the bfile variable or bfile column.

Decode (, [, [])
X is an expression, M1 is a matching expression, and X is compared with M1. If M1 is equal to X, R1 is returned. Otherwise, X is compared with m2, and so on, m5 .... until a result is returned.

Dump (, [, [, [,])
X is an expression or character. FMT indicates octal, decimal, hexadecimal, or single character. The function returns a value of the varchar2 type that contains the internal representation of X. If N1 and N2 are specified, bytes starting from N1 with N2 will be returned.

Empty_blob ()
This function has no parameters. The function returns an empty blob location indicator. Function is used to initialize a blob variable or blob column.

Empty_clob ()
This function has no parameters. The function returns an empty clob location indicator. The function is used to initialize a clob variable or clob column.

Greatest ()
Exp_list is a column expression that returns the largest expression. Each expression is implicitly converted to the Data Type of the first expression. If the first expression is any of the string data types, the returned result is of the varchar2 data type, and the comparison is of the non-padding space type.

Least ()
Exp_list is a column expression that returns the smallest expression. Each expression is implicitly converted to the Data Type of the first expression. If the first expression is any of the string data types, the returned result is of the varchar2 data type, and the comparison is of the non-padding space type.

UID
This function has no parameters and returns an integer that uniquely identifies the current database user.

User
Returns the username of the current user.

Userenv ()
Returns information about the current session based on OPT. The optional value of OPT is:

Sysdba role response in isdba session, returns true
Sessionid returns the Audit Session identifier

Entryid returns available audit item identifiers
After the instance is connected to the session, the instance identifier is returned. This value is only used when the parallel server is running and multiple instances exist.
Language returns the character set of the language, region, and database.
Lang returns the ISO abbreviation of the language name.
Terminal is the identifier of the operating system returned by the terminal or computer used by the current session.

Vsize ()
X is an expression. Returns the number of bytes in X.
Group functions in SQL

A group function is also called a set function. It returns a single result based on multiple rows. The exact number of rows cannot be determined unless the query is executed and all results are included. Different from a single-row function, all rows are known during parsing. Due to this difference, the requirements and behaviors of group functions and single-row functions are slightly different.

Group (multiple rows) Function

Compared with single-row functions, Oracle provides a wide range of group-based, multi-row functions. These functions can be used in select or select having clauses. They are often used together with group by when used for select substrings.

AVG ([{disyinct | all}])
Returns the average value of a value. The default value is all.
Select AVG (SAL), AVG (all Sal), AVG (distinct Sal) from Scott. empavg (SAL) AVG (all Sal) AVG (distinct Sal) 1877.94118 1877.94118 1916.071413

Count ({* | distinct | all })
Returns the number of rows in the query. The default value is all. * indicates that all rows are returned.

Max ([{distinct | all}])
Returns the maximum value of the selected list item. If X is a string data type, a varchar2 data type is returned. If X is a data type, a date is returned. If X is a numeric data type, returns a number. Note that distinct and all do not work. The maximum value must be the same as the two settings.

Min ([{distinct | all}])
Returns the minimum value of the selected list.

Stddev ([{distinct | all}])
Returns the standard deviation of the selected item list. The so-called standard deviation is the square root of the variance.

Sum ([{distinct | all}])
Returns the total number of items in the selected list.

Variance ([{distinct | all}])
Returns the statistical variance of the selected items.

Group

As the question implies, a group function is used to operate data that has already been divided into groups. We tell the database how to group or classify data using group, when we use group functions in the select clause of the SELECT statement, we must place grouping or extraordinary series in the group by clause. If we do not use group by for special processing, the default classification is to set the entire result to a class.
Select stat, counter (*) zip_count from zip_codes group by State; ST zip_count -- --------- AK 360al 1212ar 1309az 768ca 3982

In this example, we use the state field for classification. If we want to sort the results by zip_codes, we can use the order by statement. The order by clause can use columns or group functions.
Select stat, counter (*) zip_count from zip_codes group by state order by count (*) DESC; ST count (*) -- -------- ny 4312 PA 4297tx 4123ca 3982

Use having clause to restrict grouped data

Now you know that you can use the primary function in the SELECT statement and order by clause of the query. Group functions can only be used in two substrings. Group functions cannot be used in where substrings, for example, the following query is incorrect:
Error select sales_clerk, Sun (sale_amount) from gross_sales where sales_dept = 'outline' and sum (sale_amount)> 10000 group by sales_clerk

In this statement, the database does not know what sum () is. When we need to instruct the database to group rows and then limit the output of rows after the grouping, the correct method is to use the having statement:
Select sales_clerk, Sun (sale_amount) from gross_sales where sales_dept = 'outline' group by sales_clerkhaving sum (sale_amount)> 10000;

Nested Functions

Functions can be nested. The output of a function can be the input of another function. The operands have an inherited execution process. However, the priority of a function is only based on the position, and the function follows the principle from inside to outside, from left to right. Nested technology is generally used for functions such as decode that can be used to logically determine the statement if... then... else.