Common Oracle Functions

Common Oracle Functions

It mainly summarizes the oracle functions used in the project and makes directories to facilitate quick search for subsequent projects and improve efficiency.

01. Round (rounding the value)

Description: returns a numeric value rounded to the specified number of decimal places.

SELECT Round(Number,[Decimal_Places])FROM Dual;

Param:
Number: the value to be processed.
Decimal_Places: number of decimal places (default: 0)
Sample:

SELECT Round(123.456,1)FROM Dual;

Output: 123.5

SELECT Round(123.4567,3)FROM Dual;

Output: 123.458

02. Case... When... (if... else... of oracle ...)

Case XX attribute (optional) The when XX attribute determines then XX else XX end the table attribute Sample you want to assign a value: CASE lanageWHEN lanage = 'java' then' 01 'else' 02 'end code; case... when is similar to if () {} else {} in programming language {}

03. Nvl (empty identification of data table fields)

Nvl (string, replace_with)
Param:
String: string to be processed
Replace_with: string to be replaced
If string is NULL, The NVL function returns the value of replace_with; otherwise, the value of string1 is returned. If both parameters are NULL, NULL is returned.
It can be used for processing operations after the field is null, such as the classroom title. If the field is empty, it is normal by default.
Sample:

Select nvl (t. title, 'normal') from teacher t;

04. Decode (field name conversion)

Decode (XX attribute, XX attribute value, 'string to be translated ') function:
Example:

Select decode (t. title, '01', 'ordinary ', '02', 'advanced', 'others') from teacher is used in many projects, compared with case_when .. actually a little similar

05. tree recursive query

Select... from .... start .... [start point for Recursive traversal] connect by prior [cascade link, such as lower-level link. parentid = parent. id] the current table field = cascade table field start with and connect by prior statements to complete recursive record to form a tree structure, which can usually be used in a table with a hierarchy.

06. Trunc (truncation date and number processing)

The syntax format is as follows:

1) Trunc (Date [, Fmt]) Trunc (To_Date ('24-Nov-199908: 00pm'), 'dd-Mon-YyyyHh: miam') = '24-Nov-199912: 00: 00Am '2) Trunc (ForNumber) -- The Trunc function returns the processed value, which works very similar to the Round function, however, this function does not round and select the portion before or after the specified decimal number. Trunc (89.985, 2) = 89.98 Trunc (89.985) = 89 Trunc (89.985,-1) = 80 if we do not need to retrieve from a specific table

The data in the table is simply to get the information we want and to complete the Select operation, we need to use an object, which is Dual;
It is to convert the numeric or date type to the numeric type.
Sample:

To_Char (Number, 'format') To_Char (Salary, '$99,999.99 ')

07. Regexp_Substr (Regular Expression matching)

Regexp_Substr (String, Pattern, Position, Occurrence, Modifier)
_ Srcstr: string for Regular Expression Processing
_ Pattern: Regular Expression for matching
_ Position: Start Position. The regular expression matches the number of characters starting from (default value: 1)
_ Occurrence: identifies the matching groups. The default value is 1.
_ Modifier: mode ('I' is case-insensitive for retrieval; 'C' is case-sensitive for retrieval. The default value is 'C '.)

Sample:

Select Regexp_Substr('17,20,23','[^,]+',1,1,'i') As Str From Dual;

08. Substr (string truncation)

Substr (string, starting position and length of truncation)
Substr ('helloworld',) // The returned results are 'H' * 0 and 1, indicating that the starting position of the truncation is the first character.
Substr ('helloworld', 2, 4) // The returned result is 'Ello'

09. Replace ('change string', 'replaced', 'replaced ')

Sample:

SelectReplace('111222333444','222','888')FromDual;output:'111888333444'

10. Instr (index position of a specific character in the string, starting from 1)

Instr (source string, target string, start position, matching serial number)
The Instr function returns the position of the string to be truncated in the source string. It is retrieved only once, that is, from the start of the character to the end of the character.

Param:

Instr (String1, String2 [, Start_Position [, Nth_Appearance]) String1 source string to be searched in this string. The string. Start_Position to be searched in String1 indicates the position of String1. This parameter is optional. If omitted, the default value is 1. The string index starts from 1. If this parameter is positive, it is retrieved from left to right. If this parameter is negative, from right to left, the start index of the string to be searched in the source string is returned. Nth_Appearance indicates the number of String2. this parameter is optional. If omitted, the default value is 1. If it is negative, an error is returned. SelectInstr ('syranmo', 's') FromDual; -- returns 1 SelectInstr ('syranmo', 'A') FromDual; -- returns 3

11. add_months (get a specific month)

Add_months (data, number) function input date plus the specified months to return a new date;
Sample:

add_months(to_date('15-Nov-1961','d-mon-yyyy'),1) 

Output: 15-Dec-1961
Add_months (sysdate,-1) returns the current date month-1, which is the last month.