Oracle Common Functions Summary

PL/SQL single-line functions and group functions

A function is a program that has 0 or more parameters and has a return value. Oracle built a series of functions in SQL that can be called SQL or PL

Functions are divided into two main categories:

Single-line function

Group functions

This article discusses how to take advantage of single-line functions and usage rules.

Single-line functions in SQL

There are many types of functions, including characters, numbers, dates, transformations, and blends, that are used to process single-line data, so these can be collectively referred to as single-line functions. These functions can be used in clauses such as Select,where, ORDER by, etc., such as the following example, which includes a single-line function such as To_char,upper,soundex.

SELECT ename, To_char (hiredate, ' day,dd-mon-yyyy ') from Scott.emp Where UPPER (ename) as ' al% ' ORDER by SOUNDEX (ename)

A single-line function can also be used in other statements, such as the SET clause of the update, the insert's values clause, the WHERE clause of the delet, and the certification exam takes special care to use these functions in the SELECT statement, so our attention is also focused on the SELECT statement.

NVL (X1,X2)

It is difficult to begin with the understanding of NULL, even if a very experienced person is still confused. A null value represents an unknown number or a null value, and any one operand of the arithmetic operator is a null value, and the result is a null value, which is also suitable for many functions, only CONCAT,DECODE,DUMP,NVL, Replace can return a non-null value when a null parameter is called. The NVL function is most important in these, because he can handle null values directly, NVL has two parameters: NVL (x1,x2), X1 and X2 expressions, X1 is returned when X2 is NULL, otherwise returns X1.

Let's take a look at the EMP data sheet, which contains two of the salary, the bonus, and the total compensation.

Column name emp_id salary bonuskey type pk nulls/unique nn,u NNFK Table datatype number number Numberlength 11.2 11.2

Instead of simply adding up the salary and bonus, if a row is a null value then the result will be null, such as the following example:

Update Empset salary= (Salary+bonus) *1.1

In this statement, the employee's salary and bonus will be updated to a new value, but if there is no bonus, i.e. salary + NULL, then the wrong conclusion will be drawn, and the NVL function should be used to exclude the effect of the null value.

So the correct statement is:

Update Empset salary= (SALARY+NVL (bonus,0) *1.1

Single-line String functions

Single-line string functions are used to manipulate string data, most of which have one or more parameters, most of which return a string

ASCII ()

C1 is a string that returns the ASCII code of the first letter of C1, and his inverse function is Chr ()

SELECT ASCII (' A ') big_a,ascii (' z ') big_z from EMP

Big_a big_z

65 122

CHR () [Nchar_cs]

I is a number, and the function returns the character in decimal notation

Select Chr, Chr (122), Chr (223) from EMP

CHR65 CHR122 CHR223

A Z B

CONCAT (,)

C1,C2 are strings, the function connects C2 to the back of C1, and if C1 is NULL, it returns C2. If C2 is null, C1 is returned, and NULL is returned if C1, C2 are null. He returns the same result as the operator

Select Concat (' Slobo ', ' Svoboda ') Username from dual

Username

Slobosyoboda

Initcap ()

C1 is a string. The function returns the first letter of each word in uppercase and other letters in lowercase. Words are separated by spaces, control characters, punctuation marks.

Select Initcap (' Veni,vedi,vici ') Ceasar from dual

Ceasar

Veni,vedi,vici

Sql> Select Initcap (' Veni,vedi,vici ') Ceasar from dual;

Ceasar

--------------

Veni,vedi,vici

Sql> Select Initcap (' Veni Vedi,vici ') Ceasar from dual;

Ceasar

--------------

Veni Vedi,vici

Sql> Select Initcap (' Veni Vedi.vici ') Ceasar from dual;

Ceasar

--------------

Veni Vedi.vici

INSTR (, [, [,]])

C1,C2 are all strings and i,j are integers. The function returns the position of C2 in C1, where the search begins with the first character of the C1. When no characters are found to return 0, if I is negative, the search will proceed from right to left, but the position is calculated from left to right, and the default value for I and J is 1.

Select INSTR (' Mississippi ', ' I ', 3,3) from dual;

INSTR (' Mississippi ', ' I ', 3, 3)

11

Select INSTR (' Mississippi ', ' I ', -2,3) from dual;

INSTR (' Mississippi ', ' I ', 3, 3)

2

INSTRB (, [, I[,j])

As with the InStr () function, it is just that he returns a byte, for a single byte InStrB () equals InStr ()

LENGTH ()

C1 is a string that returns the length of the C1, and if C1 is NULL, a null value is returned.

Select LENGTH (' Ipso facto ') ergo from dual;

Ergo

10

LENGTHB ()

As with length (), returns the byte.

Lower ()

lowercase characters that return C, often appearing in the Where substring

Select LOWER (colorname) from Itemdetail WHERE LOWER (colorname) like '%white% ';

Winter

White

Lpad (string1, padded_length, [pad_string])

Where string1 is a string that requires pasting characters

Padded_length is the number of strings returned, and if this number is shorter than the length of the original string, the Lpad function will intercept the string into padded_length;

Pad_string is an optional argument, the string is to be pasted to the left of string1, if this argument is not written, the Lpad function will paste a space on the left side of string1.

For example:

Lpad (' Tech ', 7); will return ' tech '

Lpad (' Tech ', 2); will return ' Te '

Lpad (' Tech ', 8, ' 0 '); will return ' 0000tech '

Lpad (' Tech on the net ', ' Z '); Will return ' Tech on the net '

Lpad (' Tech on the net ', +, ' z '); will return ' Ztech on the net '

Rpad (string1, padded_length, [pad_string])

These two functions are used to format the output result. When the output result is less than the specified number of digits, use the Lpad function to add a custom character completion number to the left of the result, and use the Rpad function to add a custom character-completion number to the right of the result.

How to use the Lpad function:

Lpad (column name, number of digits, ' added characters ')

The principle of use of rpad is the same.

For example, if a worker's salary is less than 15 digits, the symbol ' $ ' will be added to the left of the wage value to make 15 bits:

SELECT last_name, Lpad (Salary, ' $ ') salary from employees Where last_name = ' OConnell ';

Result:

Last_Name SALARY

---------- --------

OConnell $$$$$$$$$$$2400

On the right side of C1 with the C2 string to complement the length I, can be repeated many times, if I is less than the length of C1, then only return I so long C1 character, the others will be truncated. The default value for C2 is single space, others are similar to Lpad

LTRIM (,)

Let's look at a few examples:

Sql> Select LTrim (' 109224323 ', ' 109 ') from dual;

LTRIM (' 109224323 ', ' 109 ')

------------------------

224323

This function should all know OH ~ ~ to see one more:

Sql> Select LTrim (' 10900094323 ', ' 109 ') from dual;

LTRIM (' 10900094323 ', ' 109 ')

---------------------------

4323

Are you a little confused? According to reason should be 00094323 of the results of ~ ~ ~ again to see the two contrast:

Sql> Select LTrim (' 10900111000991110224323 ', ' 109 ') from dual;

LTRIM (' 10900111000991110224323

------------------------------

224323

Sql> Select LTrim (' 109200111000991110224323 ', ' 109 ') from dual;

LTRIM (' 10920011100099111022432

------------------------------

200111000991110224323

Is there such a question: Why the second query statement more than a 2 is not truncated it?

Let's look at one more:

Sql> Select LTrim (' 902100111000991110224323 ', ' 109 ') from dual;

LTRIM (' 90210011100099111022432

------------------------------

2100111000991110224323

I think everyone will think: according to reason is cut 109 of the value, why 90 is also truncated?

Summary: The LTrim (x, y) function is to truncate the characters in X by one of the characters in Y, and is executed from the left, as long as the characters in Y are encountered, the characters in X are truncated until the function command ends with characters in X that are not in Y.

Remove the rightmost character from the C1 so that its first character is not in C2, and if there is no C2, then C1 will not change.

REPLACE (, [,])

C1,C2,C3 are strings, and functions are returned with C3 instead of C2 appearing in C1.

Select REPLACE (' Uptown ', ' up ', ' off ') from Dualreplacedowntown

Stbstr (, [,])

C1 is a string, I,j is an integer, and a substring of length j is returned starting from the first bit of the C1, if J is empty, until the end of the string.

Select SUBSTR (' Message ', 1,4) from dual

SUBS

Mess

SUBSTRB (, [,])

is roughly the same as substr, except that i,j is calculated in bytes.

SOUNDEX ()

The SOUNDEX function returns the phonetic representation of a string argument, but it is useful to spell a different word than to compare some of the same pronunciations.

Syntax for the SOUNDEX function:

SOUNDEX (String)

Algorithms for calculating speech:

1. Retain the first letter of the string, but delete a, E, H, I, O, W, y

2. Assign the numbers in the following table to the corresponding letters

(1) 1:b, F, p, V

(2) 2:c, G, K, q, S, X, Z

(3) 3:d, T

(4) 4:l

(5) 5:m, n

(6) 6:r

3. If there are more than 2 (2) letters in the string that have the same number (for example, B and f), or only H or W, delete the others, leaving only 1

4. Return only the first 4 bytes, not enough with 0 padding

Example:

Soundex (' both '), Soundex (' too '), Soundex (' to '), their results are T000

Soundex (' cap '), Soundex (' Cup '), their results are all C100

Soundex (' house '), Soundex (' horse '), their results are h200,h620

Select SOUNDEX (' Dawes ') Dawes, SOUNDEX (' Daws ') Daws, SOUNDEX (' Dawson ') from dual

Dawes Daws Dawson

D200 D200 D250

TRANSLATE (,,)

Translate (STRING,FROM_STR,TO_STR)
When executed, translate checks each character in the string in turn
Then find out if this character exists in From_str
If it does not exist, then the characters in this string are preserved, that is, returned,
If so, translate will note the position of the character in From_str,
Then replace the character in string with the character of the same position in the TO_STR.

From_str can be longer than to_str, that is, the number of characters from_str can be more than to_str
The extra characters in the from_str are called "additional characters", that is, the position in the FROM_STR
Not found in to_str, such as
From_str ' abc123 '
To_str ' abc '
The position of 1 in From_str is 4, but the total length of to_str is 3, not 4,
So 123 in From_str are extra characters.
If the extra characters in the from_str appear in a string, the characters in the string are deleted when they are returned

Examples are as follows
Sql> Select Translate (' ab12 ', ' ab2 ', ' xx ') from dual;

TRA
---
001
Here A and B are replaced by 00, because a in the FROM_STR position is 1, then the position in the To_str 1 characters replaced
B is also the same processing, is replaced by the character in the TO_STR position 2, is still 0
The 1 in string was not found in From_str, so it was preserved.
2 in string is found in From_str, but is an extra character for From_str because 2 is 3 in the FROM_STR position.
There is no 3 in the TO_STR, so 2 of the string, although found in From_str, is in the 3 position,
But the length of the TO_STR is 2, not 3, so 2 is deleted when it returns.
Got 001.

Another to_str cannot be null or ', otherwise it will return a null value
Translate also cannot be used for CLOB

It's a little easier to say
1. If the character in string is not in from_string, then the return is preserved
2. If the character in string is an "extra character" in from_string, it is deleted when returned
3. If the characters in string are found in from_string, and there is a corresponding position in the to_string character,
Then replace the characters in the string with the characters in the to_string when returning

Sql> Select TRANSLATE (' Fumble ', ' uf ', ' ar ') test from dual;

TEST

------

Ramble

sql> SELECT TRANSLATE (' Sql*plus User ' s Guide ', '/* ', ' ___ ') from DUAL;

TRANSLATE (' Sql*plusu

--------------------

Sql_plus_users_guide

Sql> Select ' */' from dual;

‘*/

---

*/‘

Sql> Select ' A ' B ' from dual;

A

---

A ' b

Replace the same characters in C1 with the C2 in C3

Select TRANSLATE (' Fumble ', ' uf ', ' ar ') test from dual;

Textramble

TRIM ([[]] from C3)

Remove the first, last, or both of the C3 strings.

Select Trim (' Space padded ') trim from dual;

TRIM

Space padded

UPPER ()

Returns the uppercase of the C1, often appearing in the Where substring

Select name from dual where UPPER (name) like ' ki% ' nameking

Single-line numeric functions

Single-line numeric functions manipulate numeric data to perform 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 angles, and Oracle does not provide built-in radians and angular conversion functions.

ABS ()

Returns the absolute value of n

ACOS ()

Inverse-remainder function, which returns the number from 1 to 1. n = radians

Select ACOS ( -1) Pi,acos (1) ZERO from Dualpi ZERO3.14159265 0

ASIN ()

Anyway, the Xuan function returns 1 to 1,n to represent radians.

ATAN ()

The inverse tangent function returns the inverse tangent of N, which represents radians.

Ceil ()

Returns the smallest integer greater than or equal to N.

COS ()

Returns the remaining value of N, n radians

COSH ()

Returns the hyperbolic remainder value of n, which 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 logarithm of the bottom n2 of the N1

MOD ()

Returns the remainder of N1 divided by N2,

POWER (,)

Returns the N2 of N1

ROUND (,)

Returns the N1 value of the N2 bit to the right of the decimal point, N2 the default value of 0, this returns the nearest integer to the decimal point, and if N2 is negative, rounds to the corresponding bit to the left of the decimal point, and 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, returns 1, if n is a positive number, returns 1 if N=0 returns 0.

SIN ()

Returns the positive value of N, and N is the radian.

SINH ()

Returns the hyperbolic positive value of n, and N is the radian.

SQRT ()

Returns the square root of N, where N is radians

TAN ()

Returns the tangent of N, N radians

TANH ()

Returns the hyperbolic tangent of N, N radians

TRUNC (,)

Intercepts a number according to the specified precision

Sql> Select Trunc (124.1666,-2) Trunc1,trunc (124.16666,2) from dual;

TRUNC1 TRUNC (124.16666,2)

--------- ------------------

100 124.16

Returns the value of N1 that is truncated to N2 decimal place, N2 the default setting of 0, and when N2 is the default setting n1 truncate to an integer, and if N2 is negative, it is truncated at the corresponding bit to the left of the decimal point.

Single-line Date function

The single-line date function operates data data types, most of which have parameters of the data type, and most return the values of the data type.

Add_months (, )

Returns the result of a date D plus I months later. I can make any integer. If I is a decimal, then the database will be implicitly converted to an integer, which will intercept the part after the decimal point.

Last_day ()

function returns the last day of the month that contains 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 if both make the last day of the month, then an integer is returned, otherwise the result returned will contain a fraction.

New_time (,,)

D1 is a date data type that returns the date and time in the time zone tz2 when the date and time in the zone tz1 is D. TZ1 and TZ2 strings.

Next_day (,)

Returns the first day of the condition given by Dow after the date D, Dow uses the language given in the current session to specify the day of the week, and the time component returned is the same 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 ([,])

The date D is rounded in the format specified by FMT, and the FMT is a string.

Syadate

The function has no parameters and returns the current date and time.

TRUNC ([,])

Returns the date d of the unit specified by the FMT.

Single-line conversion function

A single-line conversion function is used to manipulate multiple data types and convert between data types.

Chartorwid ()

C causes a string that functions to convert C to the RWID data type.

SELECT test_id from Test_case where Rowid=chartorwid (' aaaa0saacaaaaliaaa ')

CONVERT (, [,])

The C-tailed string, Dset, Sset is two character sets, and the function converts the string C from the Sset character set to the Dset character set, and the default setting for Sset is the database's character set.

Hextoraw ()

X is a 16-based string, and the function converts the 16-binary x to the raw data type.

Rawtohex ()

X is the raw data type string, and the function converts the raw data class to a 16-binary data type.

Rowidtochar ()

The ROWID function converts the data type to a char data type.

To_char ([[,)

X is a data or number datatype, and the function converts x to the char data type specified in FMT, if X is the date nlsparm=nls_date_language controls the language used for the returned month and day. If X is a numeric nlsparm=nls_numeric_characters used to specify the separator for the decimal and the kilobits, as well as the currency symbol.

nls_numeric_characters = "DG", nls_currency= "string"

to_date ([, [,)

C represents a string, and FMT represents a special-format string. Returns the language used by the c,nlsparm shown in the FMT format. The function converts the string C to the date data type.

To_multi_byte ()

C represents a string in which the function converts the load truncation character of C into multibyte characters.

To_number ([, [,)

C represents a string, FMT represents a specially formatted string, and the function return value is displayed in the format specified by FMT. Nlsparm represents the language, and the function returns the number represented by C.

To_single_byte ()

Converts multibyte characters in string C to equivalent single-byte characters. This function is used only if the database character set contains both single-byte and multibyte characters

Other single-line functions

Bfilename (,)

Dir is a directory-type object, file is a filename. The function returns an empty BFILE position value indicator, which is used to initialize the bfile variable or the bfile column.

DECODE (,, [,,, [])

X is an expression, M1 is a match expression, X is compared to M1, if M1 equals X, then R1 is returned, otherwise, X is compared with M2, and so on m3,m4,m5 .... Until there is a return result.

DUMP (, [, [, [,]]])

X is an expression or a character, and the FMT represents 8 binary, 10 binary, 16 binary, or one-word characters. The function returns a value that contains the VARCHAR2 type for the internal representation information of X. If N1,N2 is specified, then the byte length N2 from N1 will be returned.

Empty_blob ()

The function has no arguments, and the function returns an empty BLOB position indicator. function is used to initialize a BLOB variable or BLOB column.

Empty_clob ()

The function has no arguments, and the function returns an empty CLOB position indicator. The function is used to initialize a CLOB variable or CLOB column.

Greatest ()

Exp_list is a column of expressions that returns the largest of the expressions, each of which is implicitly converted to the data type of the first expression, and if the first expression is any of the string data types, the returned result is the VARCHAR2 data type. Comparisons that are used at the same time are non-padded space types.

LEAST ()

Exp_list is a column of expressions that returns the smallest expression in which each expression is implicitly converted to the data type of the first expression, and if the first expression is any of the string data types, the result is the VARCHAR2 data type. Comparisons that are used at the same time are non-padded space types.

Uid

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

USER

Returns the user name of the current user

USERENV ()

The opt-based return contains the current session information. Optional values for opt are:

ISDBA session SYSDBA-color response, return True

SESSIONID return Audit Session identifier

ENTRYID returns the available audit entry identifiers

INSTANCE returns the instance identifier after the session is connected. This value is used only when the parallel server is running and there are multiple instances.

LANGUAGE returns the character set of the language, geography, and database settings.

LANG returns the ISO abbreviation for the language name.

TERMINAL returns the identifier of the operating system for the terminal or computer used by the current session.

Vsize ()

X is an expression. Returns the number of bytes represented inside X.

Group functions in SQL

Group functions, also called aggregate functions, return a single result based on multiple rows, and the exact number of rows cannot be determined unless the query is executed and all results are included. Unlike a single-line function, all rows are known at parse time. Because of this difference, there is a slight difference in requirements and behavior between the group function and the single-line function.

Oracle Common Functions Summary