Oracle Learning Second-one-line function

1 character functions

length   character length                  LENGTHB byte length lower to lowercase upper into uppercase initcap First letter capital	select Lower (' Xun Ying ') lowercase, Upper (' Xun Ying ') uppercase,   initcap (' Xun Ying ') capitalize from dual
Lpad left padding rpad right padding Trim removes the specified character before and after Replace InStr finds the substring in the string, finds the return subscript (starting at 1), otherwise returns 0	1 Select Lpad (' ABCD ', 10, ' * ') left, Rpad (' ABCD ', 10, ' * ') right from dual; 2 Select Trim (' H ' from ' Hello WORLDH ') from dual; 3 Select replace (' Hello world ', ' l ', ' * ') from dual; 4 Select InStr (' Hello world ', ' ll ') from dual;

2 Numeric functions

round (x,d) rounding X, D to control its number of digits	round (45.926, 2) round (45.926, 1) 45.9  round (45.926, 0) round (45.926,-1) 50 round (45.926,-2) 0
TRUNC (X,D) Rounding up a number by truncation	TRUNC (45.926,-1) 40 TRUNC (45.926, 1) 45.9
Mod (A, B) A%b

3 General functions

parameter can be any data type

1 NVL filter Null value function NVL (comm,0)

2 Nvl2 (a,b,c) when A=null, return C, otherwise return B

3 Nullif (A, B) when a=b, returns NULL, otherwise returns a

4th-Period function

Operation of the date 1) date plus minus one number, this number refers to the day; two dates add no meaning 2) The difference between the days after the date is reduced You can approximate the seniority by adding and subtraction.

        SelectSysdate fromDualTen-November- -        SelectSysdate-1Yesterday, Sysdate today, sysdate+1Tomorrow fromDualSelectTo_char (Sysdate,'YYYY-MM-DD Hh24:mi:ss') fromDualSelectTo_char (Systimestamp,'YYYY-MM-DD hh24:mi:ss*ff') fromdual; Select(sysdate-HireDate)/ -One, Months_between (sysdate,hiredate) Two fromEmp

5 Conditional expressions

1 CASE Expression SQL99 syntax, similar to basic	Grammar: Case Column when exp then return_value When exp and then Return_value ELSE Last_value End
2 decode function: Oracle's own syntax, similar to Java	Grammar: DECODE (EXP|COLUMN,SEARCH1,RESULT1,.... ）

such as: According to the position raises the salary, the President + the

    SelectEname,job,sal salary before the rise, CaseJob when 'President'  ThenSal+ +                   when 'MANAGER'  ThenSal+ -                 ElseSal+ -          Endsalary after rise fromEMP; Selectename,job,sal salary, decode (job,'President', Sal+ +,             'MANAGER', Sal+ -, Sal+ -) pay rise after fromEmp

Oracle Learning Second-one-line function