Oracle Common functions

The main is to summarize the Oracle functions used in the project, and make a directory, so that the follow-up project is quick to find and improve efficiency.

01.Round (rounding of values)

Description: Returns a numeric value that is the result of rounding operations by the specified number of decimal digits.

SELECT Round (number,[decimal_places])  from Dual;

Param
Number: The value to be processed
Decimal_places: Rounding, decimal number (preset to 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 ... (Oracle's If...else ...)

 CaseXX attribute (can not be filled) whenXX attribute judgment ThenXxElseXxEndyou want to assign a table property to the sample: CaseLanage whenLanage='Java'  Then' on'Else' Geneva'EndCode; Case... whenSimilar to the language of the programif(){}Else{}

03.NVL (empty processing of data table fields)

NVL (String, Replace_with)
Param
String: To Process a string
Replace_with: the string to replace
If string is null, the NVL function returns the value of Replace_with, otherwise returns the value of string1, or null if all two arguments are null
can be used for processing operations after a field is empty, such as a classroom title, if the field is empty, default to normal.
Sample:

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

04.Decode (Conversion of field names)

Decode (XX attribute, xx attribute value, ' string to translate to ') function:
Example:

Select decode (t.title,' A ', ' normal ','02  ',' advanced ',' other ' from Teacher This function is used more in the project, with the above case_when. Actually, it's similar .

05. Tree Recursive Query

Select  ...  from    with by prior [cascade relationships, such as subordinates. ParentID = Superior. id] Current table field = by  The prior statement completes a recursive record, forming a tree structure that can often be used in tables with hierarchies.

06.Trunc (intercept date and number processing)

Its specific 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, and its working mechanism is very similar to the round function, except that the function does not make the corresponding rounding selection processing of the specified fractional number before or after it, and it is truncated altogether. Trunc (89.985，2）=89.98Trunc (89.985）= theTrunc (89.985，-1）= theif we don't need to get from a specific table

Table data, but simply to get some of the information we want, and to complete through select, we need to use an object, this object is dual;
is to convert a numeric or date type into a character type.
Sample:

To_char (number,' format ') to_char (Salary, ' $999.99, ')

07.regexp_substr (Match of regular expression)

Regexp_substr (String,pattern,position,occurrence,modifier)
__SRCSTR: A string that requires regular processing
__pattern: Regular expression for matching
__position: Starting position, starting with the first character of the regular expression match (default = 1)
__occurrence: Identifies the first few matching groups, which defaults to 1
__modifier: The pattern (' I ' is not case-sensitive for retrieval; ' C ' is case-sensitive for retrieval. The default is ' C '. ）

Sample:

Select Regexp_substr ('17,20,23','[^,]+',1,1, ' I '  as Str  from Dual;

08.Substr (Interception of strings)

Substr (string, intercept start position, intercept length)
Substr (' HelloWorld ', 1, 1)//Returns the result for ' H ' *0 and 1 is the first character that represents the start position of the Intercept
Substr (' HelloWorld ', 2,4)//return result for ' Ello '

09.Replace (' To change the string ', ' be replaced ', ' replace ')

Sample:

Selectreplace ('111222333444','222','888  ') fromdual;output:'111888333444'

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

Instr (source string, target string, starting position, matching ordinal)
The InStr function returns the position of the string to intercept in the source string. It is retrieved only once, that is, from the beginning of the character to the end of the character.

Param:

Instr (string1,string2[, Start_position[,nth_appearance]]) String1 The source string to find in this string. String2 the string to find in String1. Start_position represents where String1 is starting to find. This parameter is optional if omitted defaults to 1. The string index starts at 1. If this parameter is positive, it is retrieved from left to right, and if this parameter is negative, right-to-left, returns the starting index of the string to find in the source string. Nth_appearance representative to find the first occurrence of the String2. This parameter is optional, and if omitted, the default is 1. If the system is negative, it will give an error. Selectinstr ('Syranmo','s') fromdual;--returns 1SELECTINSTR ('Syranmo','Ra') fromdual;--returns 3

11.add_months (get a specific month)

The add_months (data,number) function returns a new date with the specified number of months on the input 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, last month

Oracle Common functions