Quick query of Oracle function List 1

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 ()
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.