Oracle built-in functions

Three. Character functions (can be used for literal characters or database columns)

1, String intercept
Select substr (' abcdef ', 1,3) from dual

2, finding the substring location
Select InStr (' Abcfdgfdhd ', ' FD ') from dual

3, String connection
Select ' HELLO ' | | ' Hello world ' from dual;

4, 1) Remove spaces from the string
Select LTrim (' abc ') s1,
RTrim (' Zhang ') s2,
Trim (' Zhang ') S3 from dual
2) Remove the preamble and suffix
Select Trim (leading 9 from 9998767999) S1,
Trim (trailing 9 from 9998767999) S2,
Trim (9 from 9998767999) S3 from dual;

5, returns the ASCII value of the first letter of the string
Select ASCII (' a ') from dual

6, returns the letter corresponding to the ASCII value
Select CHR from dual

7, calculating the string length
Select Length (' abcdef ') from dual

8,initcap (capitalized), lower (lowercase), upper (uppercase)
Select lower (' ABC ') s1,
Upper (' def ') S2,
Initcap (' EFG ') S3
from dual;

9,replace
Select replace (' abc ', ' B ', ' XY ') from dual;

10,translate
Select Translate (' abc ', ' B ', ' xx ') from dual; --X is 1 bits

11,lpad [Left refill] rpad [right padding] (for controlling output format)
Select Lpad (' Func ', s1, ' = '), Rpad (' func ', '-') ' S2 from dual;
Select Lpad (dname,14, ' = ') from dept;

The decode[implements If. Then logic] Note: The first is an expression, and the last is a value that does not meet any one of the criteria
Select Deptno,decode (deptno,10, ' 1 ', 20, ' 2 ', 30, ' 3 ', ' other ') from dept;
Cases:
Select Seed,account_name,decode (seed,111,1000,200,2000,0) from t_userinfo//if Seed is 111, take 1000; 200, fetch 2000; other FETCH 0
Select Seed,account_name,decode (seed-111), 1, ' Big Seed ', -1, ' Little seed ', ' equal Seed ') from t_userinfo//if seed >111, the display is large; 200, the display is small;

Show equal

case[implementation switch. Case Logic]
SELECT Case X-field
When X-field < Max then ' X-field less than 40 '
When X-field < ' X-field less than 50 '
When X-field < ' X-field less than 60 '
ELSE ' Unbeknown '
END
From DUAL

Note: Case statements are very flexible when dealing with similar problems. Decode is more concise when only a small number of values need to be matched.

Four. Numeric functions
1, take the whole function (ceil up, floor down rounding)
Select Ceil (66.6) N1,floor (66.6) N2 from dual;

2, exponentiation (Power) and square root (sqrt)
Select Power (3,2) n1,sqrt (9) N2 from dual;

3, redundancy
Select mod (9,5) from dual;

4, returns a fixed number of decimal digits (round: Rounding, Trunc: direct truncation)
Select Round (66.667,2) N1,trunc (66.667,2) N2 from dual;

5, the sign of the return value (positive return is 1, negative number is-1)
Select sign ( -32), sign (293) from dual;

Five. Conversion functions
1,to_char () [Converts date and number types to character types]
1) Select To_char (sysdate) s1,
To_char (sysdate, ' yyyy-mm-dd ') s2,
To_char (sysdate, ' yyyy ') S3,
To_char (sysdate, ' Yyyy-mm-dd hh12:mi:ss ') S4,
To_char (sysdate, ' Hh24:mi:ss ') S5,
To_char (sysdate, ' Day ') S6
from dual;
2) Select Sal,to_char (Sal, ' $99999 ') N1,to_char (Sal, ' $99,999 ') N2 from EMP

2, To_date () [Convert character type to date type]
INSERT into EMP (empno,hiredate) VALUES (8000,to_date (' 2004-10-10 ', ' yyyy-mm-dd '));

3, To_number () converted to numeric type
Select To_number (To_char (sysdate, ' Hh12 ')) from dual; Number of hours displayed as a number

Six. Other functions
1.user:
Returns the user name of the login
Select User from Dual;

2.vsize:
The number of bytes required to return an expression
Select Vsize (' HELLO ') from dual;

3.NVL (EX1,EX2):
EX1 value is null returns EX2, otherwise the value itself is returned EX1 (common)
Example: If an employee does not have a commission, 0 is displayed, otherwise the Commission is displayed
Select COMM,NVL (comm,0) from EMP;

4.nullif (EX1,EX2):
value is equal to NULL, otherwise the first value is returned
Example: If wages and commissions are equal, the display is empty, otherwise the wages are displayed
Select Nullif (Sal,comm), sal,comm from EMP;

5.COALESCE:
Returns the first non-empty expression in a list
Select Comm,sal,coalesce (comm,sal,sal*10) from EMP;

6.NVL2 (EX1,EX2,EX3):
If Ex1 is not empty, display ex2, otherwise ex3
such as: View the names of employees with commissions and their commissions
Select NVL2 (Comm,ename, ') as Havecommname,comm from EMP;


seven. Grouping functions
Max min avg count sum
1, the entire result set is a group
1) 30 of the department's maximum wage, minimum wage, average wage, total number, number of people working, number of jobs and sum of wages
Select Max (ename), Max (SAL),
Min (ename), Min (Sal),
AVG (SAL),
Count (*), COUNT (Job), COUNT (Distinct (job)),
Sum (SAL) from EMP where deptno=30;
2, group by and having groups
1) The highest wage, minimum wage, total number, number of workers, number of jobs and sum of wages in accordance with the Department group
Select Deptno, Max (ename), Max (SAL),
Min (ename), Min (Sal),
AVG (SAL),
Count (*), COUNT (Job), COUNT (Distinct (job)),
Sum (SAL) from the EMP group by DEPTNO;

2) Department 30 maximum wage, minimum wage, total number, number of jobs, number of jobs and sum of wages
Select Deptno, Max (ename), Max (SAL),
Min (ename), Min (Sal),
AVG (SAL),
Count (*), COUNT (Job), COUNT (Distinct (job)),
Sum (SAL) from the EMP group by DEPTNO have deptno=30;

3, StdDev returns the standard deviation of a set of values
Select Deptno,stddev (SAL) from the EMP group by DEPTNO;
Variance returns the variance difference of a set of values
Select Deptno,variance (SAL) from the EMP group by DEPTNO;

4, GROUP BY with rollup and cube operators
Rollup statistics and final subtotals by the first column in a group
Cube statistics and final subtotal by all columns grouped
Select Deptno,job, sum (SAL) from the EMP group by Deptno,job;
Select Deptno,job, sum (SAL) from the EMP Group by Rollup (Deptno,job);
Cube generates statistics and final subtotals for all columns within a group
Select Deptno,job, sum (SAL) from the EMP Group by Cube (Deptno,job);

Viii. Temporary tables
A table that exists only during a session or during transaction processing.
Temporary tables allocate space dynamically when inserting data
Create global temporary table temp_dept
(DNO number,
Dname VARCHAR2 (10))
on commit delete rows;
INSERT into temp_dept values (' ABC ');
Commit
SELECT * from Temp_dept; -No data display, automatic data removal
On commit preserve rows: The table can always exist during the session (preserves data)
On commit Delete rows: Transaction end purge data (automatically delete table data at end of transaction)

Oracle built-in functions