Classification of single-line functions in Oracle:
1. Character Functions
substr (): String intercept
Select substr (' I love you, you know? ', 0,4) from dual
Execution result: I love you,
Length function: string lengths
Select Length (' I love you, you know? ') from dual
Execution Result: 9
Replace () function: replaces the specified character
Select replace (' I love you, you know? ', ' You ', ' is he ') from dual
Execution result: I love him, he knows?
Upper functions: Uppercase letters
Select Upper (' Hello ') from dual;
Execution Result: HELLO
Lower function: letter to lowercase
Select lower (' HELLO ') from dual;
Execution Result: Hello
Initcap function: Capitalize first letter
Select Initcap (' HELLO ') from dual;
Execution Result: Hello
2. Numeric functions:
Round function: Rounding
Select Round (789.021,2) from dual;
Execution Result: 789.02
trunc function: Intercept decimal digits (default reserved integer digits)
Select Round (789.029,2) from dual;
Execution Result: 789.02
MoD function: Finding the remainder function (second bit cannot be 0)
Select mod (10,3) from dual;
Execution Result: 1
3. Date function:
Date-number = date;
Date + number = date;
Date-date = Number (indicates the number of days);
Months_between ()----Find the number of months in the specified range, such as:
SELECT empno, ename, round (Months_between (Sysdate, HireDate)) from EMP;
Add_months ()---Add the specified number of months to the specified date, such as:
SELECT add_months (Sysdate, 3) from dual;
Next_day ()----to find the number of weeks in the week that is the same as the week of the week, such as:
SELECT last_day (sysdate, ' Sunday ') from dual;
Last_day ()---to find the date of the last day of the month, such as:
SELECT Last_day (sysdate) from dual;
4. Conversion function:
To_char function: Converting to a string
Select To_char (sysdate, ' yyyy '), To_char (sysdate, ' MM '), To_char (sysdate, ' DD ') from dual;
Execution results: 2014 07 03
To_number function: Convert to Number
Select To_number (' 123 ') +to_number (' 123 ') from dual;
Execution Result: 246
To_date function: Convert to date function
Select To_date (' 2012-03-14 ', ' YYYY-MM-DD ') from dual
Convert to Oracle built-in date after execution like 2012/03/14
5. General functions:
NVL function: A function used to set a variable value to another default value
Select NVL (num, ') from user//If NUM is null-assigned to a value of 0
Decode (condition, value 1, return value 1, value 2, return value 2,... Value N, return value N, default value)
Like IfElse line by row judgment
Select Decode (1, 2, ' content is 2 ', 1, ' content is 1 ') from dual;
The result is: 1 content