ORACLE summary of commonly used functions "a"

PL / SQL single-line functions and group functions Xiangjie

Function is a zero or more parameters and a return value of the program. Oracle built-in SQL, a series of functions that can be called SQL or PL / SQL statement,

Function is divided into two categories:

One-way function

Group functions

This article will discuss how to use one-way functions and the use of rules.

SQL in a single function

SQL and PL / SQL, comes in a variety of functions, there are characters, numbers, date, conversion, and mixed single variety of functions for processing data, so these can be referred to as one-way function. These functions can be used for SELECT, WHERE, ORDER BY clause such as, for example, on the following example contains a TO_CHAR, UPPER, SOUNDEX and other one-way function.

SELECT ename, TO_CHAR (hiredate, 'day, DD-Mon-YYYY') FROM scott.emp Where UPPER (ename) Like 'AL%' ORDER BY SOUNDEX (ename)

One-way functions can also be used in other statements, such as the update of the SET clause, INSERT the VALUES clause, DELET the WHERE clause, the certification examination particular attention to the SELECT statement to use these functions, so our attention has been focused on SELECT statement in.

NVL (x1, x2)

NULL on how to begin to understand it is very difficult, even a very experienced people still feel confused. NULL value represents an unknown data or a null value, arithmetic operators operate any number of NULL values, the results are put a NULL value, the rule for many functions, only the CONCAT, DECODE, DUMP, NVL, REPLACE call a NULL parameter to return non-NULL value. NVL function in these in the most important because he can deal directly with the value NULL, NVL has two parameters: NVL (x1, x2), x1 and x2 are type expressions, when x1 is null returns X2, otherwise x1 .

Here we look at the data table emp which includes salary, bonus 2, need to calculate 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

Not simply add up the salary and bonuses can, and if a line is a null value then the result will be null, such as the following example:

update empset salary = (salary + bonus) * 1.1

This statement, the employee's salary and bonus will be updated to a new value, but without money, that is, salary + null, it would come to the wrong conclusions, this time we should use the nvl function to rule out the null value of .

Therefore, the correct statement is:

update empset salary = (salary + nvl (bonus, 0) * 1.1

Single string functions

Single string functions for manipulating string data, most of them have one or more parameters, most of which returns the string


c1 is a string, return c1 the first letter of the ASCII code, he inverse function is CHR ()




CHR (<i>) [NCHAR_CS]

i is a number, the function returns the decimal representation of the character

select CHR (65), CHR (122), CHR (223) FROM emp

CHR65 CHR122 CHR223

A z B


c1, c2 are the string, the function c1 c2 connected to the back, if c1 is null, will return to c2. If c2 is null, then return to c1, if c1, c2 are as null, it returns null. He and the results returned by the same operator

select concat ('slobo', 'Svoboda') username from dual




c1 is a string. Function to the first letter of each word capitalized other letters lowercase return. Words by spaces, control characters, punctuation marks separate.

select INITCAP ('veni, vedi, vici') Ceasar from dual


Veni, Vedi, Vici

SQL> select INITCAP ('veni, vedi, vici') Ceasar from dual;



Veni, Vedi, Vici

SQL> select INITCAP ('veni vedi, vici') Ceasar from dual;



Veni Vedi, Vici

SQL> select INITCAP ('veni vedi.vici') Ceasar from dual;



Veni Vedi.Vici

INSTR (, [, <i> [,]])

c1, c2 are string, i, j an integer. Function returns the first j c1 c2 in the second occurrence of the search from the first i characters c1 start. When the characters need not found return 0, if i is negative, then the search will be conducted from right to left, but the location calculation or from left to right, i and j default value of 1.

select INSTR ('Mississippi', 'i', 3,3) from dual;



select INSTR ('Mississippi', 'i', -2,3) from dual;



INSTRB (, [, i [, j])

And INSTR () function is the same, but he returned to the bytes for single-byte INSTRB () equivalent to INSTR ()


c1 a string, returns the length of c1, if c1 is null, it will return null value.

select LENGTH ('Ipso Facto') ergo from dual;




And LENGTH () as return bytes.

lower ()

Return c, lowercase characters, often sub-string in the where

select LOWER (colorname) from itemdetail WHERE LOWER (colorname) LIKE '% white%';



lpad (string1, padded_length, [pad_string])

Where string1 is the need to paste the string of characters

padded_length is the number of the returned string, if the length of the string than the original number of shorter, lpad function will be to intercept the string into padded_length;

pad_string is an optional parameter, this string is to be pasted into the left side of string1, if this parameter is not written, lpad function will be pasted in the left side of string1 space.

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', 15, 'z'); will return 'tech on the net'

lpad ('tech on the net', 16, 'z'); will return 'ztech on the net'

rpad (string1, padded_length, [pad_string])

The two functions used to format the output. When the output of the median than the required number of bits, the results using the LPAD function to add custom characters left append digits, use the RPAD function in the results of the right to add custom characters padded digits.

LPAD function to use:

LPAD (column names, the median, 'add the character')

RPAD use principle is the same.

For example: If the employee's wage is less than 15 digits, from the left side of the wage value added symbol '$' to append 15:

SELECT last_name, LPAD (salary, 15, '$') SALARY FROM employees Where last_name = 'OConnell';



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

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

C1 c2 in the right side with a length of string make up cause i, can be repeated, if i is less than the length of c1, then return only so long i c1 characters, the other will be truncated. The default value of c2 single space, similar to other LPAD


Look at a few examples:

SQL> select ltrim ('109224323 ', '109') from dual;

LTRIM ('109224323 ', '109')


224 323

This function should have known Oh ~ ~ look at one:

SQL> select ltrim ('10900094323 ', '109') from dual;

LTRIM ('10900094323 ', '109')



Is not a little puzzled? Naturally expect that the result should be 00094323 ~ ~ Well look at two contrasts:

SQL> select ltrim ('10900111000991110224323 ', '109') from dual;

LTRIM ('10900111000991110224323


224 323

SQL> select ltrim ('109200111000991110224323 ', '109') from dual;

LTRIM ('10920011100099111022432



Is there such a question: why the second query is no more than a 2 mile?

Look at one:

SQL> select ltrim ('902100111000991110224323 ', '109') from dual;

LTRIM ('90210011100099111022432



I think we all would like to: naturally expect that a cut-off value of 109, why the 90 was cut up?

Summary: ltrim (x, y) function is based on the characters in a y x characters in a truncated and started from the left, as long as any character in the face y, x in the characters will be truncated, until the x, y character is not encountered until the function of characters until the end of the command.

The most right character c1 removed, it is not the first character c2 the latter, if not c2, then c1 will not change.

REPLACE (,[,])

c1, c2, c3 are strings, the function instead of using c3 c2 in c1 in return.

select REPLACE ('uptown', 'up', 'down') from dualREPLACEdowntown

STBSTR (, <i> [,])

c1 is a string, i, j is an integer, c1 the first i bits from the start to return the substring of length j, if j is empty, then the string until the end.

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



SUBSTRB (, <i> [,])

And SUBSTR similar, only I, J is in bytes.


SOUNDEX function returns a string representation of voice parameters, relative to the more pronounced the same number, but spelling different words is very useful.

SOUNDEX function syntax:

SOUNDEX (string)

Voice of the algorithm calculation:

1. To retain the first letter of the string, but remove the a, e, h, i, o, w, y

2. Will in the following table corresponding to the number assigned to the 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 has the same string more than 2 figures (including 2) of the letters together (such as b and f), or only h or w, then delete the other, leaving only a

4. Only to return to the first 4 bytes, not filled with 0


soundex ('two'), soundex ('too'), soundex ('to'), their results are T000

soundex ('cap'), soundex ('cup'), their results are 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 (string, from_str, to_str)
Implementation, translate string in turn check to find the character of each character and then it exists in from_str If not, then the string of characters to be retained, that is to be returned
If there is, then, translate the characters will note the location in from_str,
The same position then to_str replace the string of characters in the character

from_str longer than the to_str, the number of characters is from_str more than the to_str in from_str in the extra characters as an "additional characters", that is, the location from_str
to_str can not be found, such as
from_str 'abc123'
to_str 'abc'
Here from_str in a position 4, but to_str the total length is 3, no 4,
So from_STR additional characters in the 123 are the additional characters if from_str appear in the string, then the string in the return of these characters will be removed

The following are examples
SQL> select translate ('ab12', 'ab2', '00 ') from dual;

Where a and b have been replaced with 00, as a middle position in from_str 1, then the location is to_str 1 character replacement
b is the same address, was to_str character in position 2 replaced, is still 0
string of 1 from_str not found, so are preserved
string of 2 from_str found, but is from_str additional characters, as in the 2 position in from_str 3
The to_str is not 3 this position, so string of 2 Although from_str has found, and at the 3 position
However TO_STR length is 2, not 3 this position, so 2 is deleted in return get the 001

Another to_str can not be NULL or'', otherwise it will return a null value
CLOB can not be used to translate

To put it simply, that is,
1, if the string of characters, if not in from_string, then return to be retained
2, if the string of characters is from_string the "extra characters", then return to be removed
3, if the string of characters found in from_string, and the corresponding position in the to_string in character,
Then return with the to_string replace the string of characters in the character

SQL> select TRANSLATE ('fumble', 'uf', 'ar') test from dual;




SQL> SELECT TRANSLATE ('SQL * Plus User''s Guide', '*/''','___') FROM DUAL;




SQL> select'*/''' from dual;

'* /


* / '

SQL> select 'a''b' from dual;




With the c1 c2 c3 instead of the same character to

select TRANSLATE ('fumble', 'uf', 'ar') test from dual;


TRIM ([[]] from c3)

C3 string to the first, last, or are deleted.

select TRIM ('space padded') trim from dual;


space padded


Return c1 of the capital, where children often appear in string

select name from dual where UPPER (name) LIKE 'KI%' NAMEKING

Single digital function

Single digital functions operate digital data, perform mathematical and arithmetic. All functions are numeric argument and returns a numeric value. Operands of all trigonometric functions and values are in radians instead of angles, oracle does not provide built-in curvature and angle of the transfer function.

ABS ()

Back to the absolute value of n


Anti-Yu Xuan function returns a number between -1 and 1. n expressed in radians

select ACOS (-1) pi, ACOS (1) ZERO FROM dualPI ZERO3.14159265 0


Anyway Hyun function returns -1 to 1, n that arc


Tangent function, return the arc tangent value of n, n expressed in radians.


Return greater than or equal to the smallest integer n.

COS ()

Return n, values of more than mysterious, n radians


Yu Hyun hyperbolic return value n, n is the number.

select COSH (<1.4>) FROM dualCOSH (1.4) 2.15089847

EXP ()

Back to e n-th power, e = 2.71828183.


Return the largest integer less than or equal N.

LN ()

Return the natural logarithm of N, N must be greater than 0

LOG (,)

Back to n1 n2 for the end of the logarithmic

MOD ()

Back to the remainder of n1 divided by n2,


Return n1, n2-th power


Back to the right of the decimal point rounding bit n1 n2 value, n2 default value is 0, this time to the nearest decimal integer, if n2 is negative, rounded to the decimal point on the appropriate spaces on the left, n2 must be an integer.

select ROUND (12345, -2), ROUND (12345.54321,2) FROM dualROUND (12345, -2) ROUND (12345.54321,2) 12300 12345.54


If n is negative, return -1, if n is positive, returns 1, if n = 0 return 0.

SIN ()

Return the value of n, is mysterious, n radians.


Return the hyperbolic n is mysterious value, n is in radians.


Back to the square root of n, n for the curvature

TAN ()

Return the tangent of n, n for the curvature


Return the hyperbolic tangent of n, n for the curvature


According to a specified accuracy of the interception of a number

SQL> select trunc (124.1666, -2) trunc1, trunc (124.16666,2) from dual;

TRUNC1 TRUNC (124.16666,2)

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

100 124.16

Back to n2 decimal places censored value of n1, n2 default setting is 0, when n2 is the default setting will be truncated to an integer n1, if n2 is negative, the decimal point on the left censored in the corresponding bit on .

Single date function

DATA single date data type function operation, most of all DATA data type parameters, most of the data type of the returned value is DATA.

ADD_MONTHS (, <i>)

Return date d plus i months after the results. i can make an arbitrary integer. If i is a decimal, then the database will implicitly convert an integer, he will be amputated part behind the decimal point.


D contains the date function returns the last day of the month


Return d1 and d2 between the number of months, if d1 and d2 are the same date of the day, or have to make the last day of the month, then will return an integer, otherwise it will return the results will contain a score.


d1 is a date data type, time zone tz1 the date and time is d, return the time zone tz2 the date and time. tz1 and tz2 when the string.


Return date d dow after the first day given the conditions, dow using the current session given language specifies a certain day of the week, return to the time component of the same amount of time with 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 ([,])

D the date designated in accordance with the format fmt rounding, fmt string.


Function has no parameters, returns the current date and time.

TRUNC ([,])

Back to the units specified by the fmt date d.

Single transfer function

Single transfer function for the operation of a data type conversion between data types.


c to a string, function c into RWID data type.

SELECT test_id from test_case where rowid = CHARTORWID ('AAAA0SAACAAAALiAAA')

CONVERT (,[,])

c end of the string, dset, sset are two character sets, the function string c converted from the character set dset sset character set, sset the default settings for the database character set.


x 16 hex string, the function will convert 16 hex x-RAW data type.


x is the RAW data type string, the function will convert the RAW data type data type 16 hex.


ROWID data type conversion function for the CHAR data type.

TO_CHAR ([[,)

x is a data or number data type, the function x into a specified format fmt char data type, if x is the date nlsparm = NLS_DATE_LANGUAGE control back to the month and day were the language used. If x is the number nlsparm = NLS_NUMERIC_CHARACTERS used to specify the decimal places and thousands of sub-bit separator, and currency symbols.


TO_DATE ([,[,)

c represents the string, fmt, said a special format string. Return fmt format in accordance with the c, nlsparm that the use of language. Function to convert a string c date data type.


c represents a string, the function c of the burden of cut characters into multi-byte character.

TO_NUMBER ([,[,)

c represents the string, fmt, said a special format string, the function return value in accordance with the format fmt specified. nlsparm that language, the function will return c represent numbers.


Will be in a much-byte character string c converted into an equivalent single-byte characters. This function only if the database character set contains both single-byte and multi-byte characters when using

Other one-way function


dir is a Directory object type, file for a file name. Function returns an empty value BFILE location indicator, the function used to initialize the BFILE variable or a BFILE column.

DECODE (,,[,,,[])

x is an expression, m1 is a matching expression, x and m1 comparison, if m1 is equal to x, then return r1, otherwise, x compared with m2, and so on m3, m4, m5 .... until one returns the result.

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

x is an expression or a character, fmt said the 8 hex, 10 hex, 16 hex, or the single characters. Function returns contain information about the internal representation of x, the value of type VARCHAR2. If you specify n1, n2 then the length from the beginning of n2 n1 bytes will be returned.


This function has no parameters, the function returns an empty BLOB location indicator. Function is used to initialize a BLOB variable or BLOB column.


This function has no parameters, the function returns an empty CLOB position indicator. Function is used to initialize a CLOB variable or CLOB column.


exp_list is an expression that returns the largest of which expressions, each expression are the first expression implicit conversion of data types, if the first expression is a string data type, any one of, then returned The result is varchar2 data type, using the relatively non-filled spaces in the type of comparison.


exp_list is an expression that returns the smallest expression, each expression are the first expression implicit conversion of data types, if the first expression is a string data type in any one of the returned The result is varchar2 data type, using the relatively non-filled spaces in the type of comparison.


This function has no parameters, returns the current database that uniquely identifies the user's integer.


Returns the current user's user name


Opt to return based on information that contains the current session. opt for the optional value:

ISDBA foot color response SYSDBA session, return TRUE

SESSIONID return to audit the session identifier

ENTRYID returns available auditing entry identifier

INSTANCE connection in the session, return an instance identifier. This value is only used to run the Parallel Server and multiple instances of situations.

LANGUAGE return to language, geography, set the database character set.

LANG returns the ISO language name abbreviation.

TERMINAL for the current session to use the terminal or computer to return the operating system identifier.


x is an expression. Return x internal representation of the number of bytes.

The group function SQL

Set of functions, also called set functions to return a single result based on multiple lines, the exact number of rows can not be determined, unless the query is executed and all results are included. And one-way function is different is that when parsing all the rows are known. Because of this difference in function and single function to set the requirements and behavior are slightly different.
标签: column name, select statement, sql statement, null value, yyyy, single line, concat, null values, arithmetic operators, pl sql, emp, null parameter, x1, line functions, group functions, null returns, type expressions, bonus 2, soundex, uniqu
分类: Database
时间: 2010-07-28


  1. 110 Oracle summary of commonly used functions [change]

    Summarized: 110 summary of Oracle common functions /*================================================ ======== ...
  2. Oracle --- summary of commonly used functions (1)

    We all know, more a function of the database, and different database vendors in the sql standard, but also exp ...
  3. ORACLE summary of commonly used functions <1>

    1, 2, function Daquan 3, http://www ...
  4. jQuery summary of commonly used functions

    jQuery provides us a lot of useful methods and properties, their summary of some commonly used functions and m ...
  5. SQLServer and Oracle Comparison of commonly used functions *

    1. The absolute value of S: select abs (-1) value O: select abs (-1) value from dual 2. Take the whole (large) ...
  6. oracle of several commonly used functions sum (), avg (), min (), count ()

    Sum () the sum function. Avg () function is averaged. Max () function to find the maximum value. Min () for th ...
  7. Theme: SQLServer and Oracle compare commonly used functions

    SQLServer and Oracle databases are all frequently used, in this summary of these commonly used functions for y ...
  8. Ultra-detailed summary of SQL commonly used functions

    Sql Summary of Sql - commonly used functions A string function is the most widely used kind of oracle function ...
  9. oracle 110 commonly used functions

    110 Oracle common transfer function order summary [] 1. ASCII Returns the character corresponding to the speci ...
  10. [Reserved] 110 commonly used functions finishing Summary

    1. ASCII Returns the character corresponding to the specified decimal number; SQL> select ascii (A) A, asci ...
  11. Subject: SQLServer and Oracle compare commonly used functions

    SQLServer and Oracle databases are all frequently used in this summary of these common functions for your refe ...
  12. mysql and examples of commonly used functions Xiangjie

    Combined with the help of MySQL comes with MySQL database documentation series about a number of commonly used ...
  13. sql commonly used functions (Oracle10g):

    sql commonly used functions (Oracle10g): 1.1 Function Syntax SELECT function (column) FROM table 1.2 Common Fu ...
  14. oracle of the difference between functions and stored procedures

    One, oracle of the difference between functions and stored procedures? A: function must return a value, while ...
  15. Transact SQL statements and commonly used functions

    Transact SQL statements and commonly used functions Transact SQL statement function ========================== ...
  16. sql server commonly used functions

    sql server commonly used functions Articles Category: Java Programming sql select statement can only be used t ...
  17. mysql commonly used functions

    mysql commonly used functions Articles Category: Database 1, Math Functions ABS (x) returns the absolute value ...
  18. Visual Studio 2010 summary diagrams of practical functions

    Visual Studio 2010 summary diagrams of practical functions Visual Studio 2010 has released a very long time, Y ...
  19. C, commonly used functions

    C, commonly used functions - common string manipulation functions int strlen (const char * a) the length of th ...