Quick query of common functions in Oracle Database 1

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 and group Function

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. Network Technology Co., http://www.chinaccna.com.

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 () Network Technology Co., http://www.chinaccna.com.

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