The use of a single line function of Oracle and Multiline functions (aggregate functions, grouped functions) ___ static functions

To introduce the character function first:
Case Related:
1.

Select lower (' Hellow '), Upper (' Hehhlo '), Initcap (' Oello ') from
dual;--dual empty table.

Results:

Hellow  Hehhlo  Oello

and Case Independent:
1.

SELECT trim (' h ' from ' hellowhh '), LTrim (' hellowhh ', ' h '), LTrim (' hhellowhh ', ' HH '), RTrim (' hellowhh ', ' h ') from
dual ;

Note that the trim is different from the ltirm and RTrim usage, and the function means to delete ' H '

Ellow   ellowhh ellowhh hellow

2.

Select Lpad (1000, 7, ' * '), Rpad (1000,7, ' & ') from
dual;

Is Lpad (1000,7, ""), is to the left of 1000 "to be filled."
Results: ***1000 1000&&&

3. Connection string:

Select Concat (' Hello ', ' World ') from
dual;

Hello World

4. Find the substring

Select substr (' Hello worldlkasjdf ', 3, 7) from
dual;

Llo wor

5. Find the location of the string

Select InStr (' Hellow laksd ', ' l ') from
dual;

3

6.replace Replacement string

Select replace (' Hellow ', ' h ', ' mmm ') from
dual;

Results:

Mmmellow

Math function:
Round () rounded.

Select Round (123.456,2), round (123.456,-2), round (123.45,0), round (123.45) from
dual;

Round (123.456,2), said to keep two digits after the decimal point, if-2, is to represent the first two decimal places.

123.46  100 123 123

4.select trunc (1234.5678, 2)
from dual;
Remove Trunc ()

1234.56

Common date Functions:
1. You can add and subtract a value directly on the date data that represents the number of days before and after the date.
2.SYSDATE: Get the current system time
3.months_between: Gets the number of months between two dates
4.add_month: Get a date after a few months
5.last_day: Get the date of this month's left day
6.EXTRACT: Get a part of the value from this month

1.sysdate ()

Select Sysdate + 2 from
dual;

Today, October 18, 2015

20月-October-15

2.add_months ()

Select Add_months (Sysdate, 4) from
dual;

1 August-February-16

Combined use of 3.trunc and Months_between

Select Ename, Trunc (Months_between (Sysdate, HireDate)) from
EMP;

Results

SMITH   418
ALLEN   415
WARD    415
JONES   414
MARTIN  408

5.extract () remove data from date

Select Extract (Sysdate), extract (month from sysdate), extract (Day from sysdate) from
dual;

Results:

2015  18

Other functions:
Case key word:

Select Ename, Deptno,sal, case when
       deptno=10 then Sal + after deptno=20
       then Sal + when
       deptno=30 Then Sal +
       ELSE sal end salary from
EMP;

The results are:

SMITH  1000
ALLEN  1600    1900
WARD  1250    1550
JONES  2975    3175
MARTIN  1250    1550
BLAKE  2850    3150
CLARK  2450    2550
SCOTT  3000    3200
KING  5000    5100
TURNER  1500    1800
ADAMS  1100    1300
JAMES  950 1250
FORD  3000    3200

Null-value conversion function:
NAL: ("no value. ”）

Select Ename, Deptno, Comm, NVL (comm,0) from
EMP;

NVL (null,0); This requires that the values of the two columns are the same, because the comm here is numeric, so 0 is OK, if you convert to string ' 0 ', then the first argument is To_char (comm).

      0
MARTIN, 0 ALLEN at SMITH  1400    1400
BLAKE      0
CLARK      0
SCOTT      0
KING      0
TURNER  0   0
ADAMS      0   JAMES      0
FORD      0
MILLER      0