/*
一、函數的分類
SQL函數一般分為兩種
單行函數 基于單行的處理，一行産生一個結果
多行函數 基于多行的處理，對多行進行彙總，多行産生結果
二、函數形式
function_name [(arg1, arg2,...)]
三、常用的單行函數：
1. 字元函數：
lower(x) 轉小寫
upper(x) 轉大寫
initcap(x) 單詞首字母轉大寫
concat(x,y) 字元連接配接與| | 功能類似
substr(x,start [,length]) 取子串
格式: substr('asdfasdfasdfasddf',1,3) ——從第1個開始取，取得長度為3.
length(x) 取字元串長度
lpad | rpad(x,width [,pad_string]) 字元定長，（不夠長度時，左｜右填充）——x為字元串，width為顯示長度，pad_string為填充字元。
trim([trim_charFROM] x) 删除首部、尾部字元
格式：trim('h' from 'hello hello')
trim 預設删除方式是both
leading 隻删首部 trim(leading 'h' from 'hello helloh')——隻能删除一個字元‘h’ 裡面寫兩個竟然報錯，坑爹。
trailing 隻删尾部 trim(trailing 'h' from 'hello helloh')
ltrim(x[,trim_string]) ——從x左邊删除字元 等價于使用leading，但是比leading好的地方是可以删除多個字元。
rtrim(x[,trim_string]) ——從x右邊删除字元 等價于使用trailing，但是比leading好的地方是可以删除多個字元。
instr 傳回子字元串在字元串中的位置
格式：instr(string,substring,position,occurence) ——INSTR(源字元串,
目标字元串, 起始位置, 比對序号)
例如：INSTR('CORPORATE FLOOR','OR', 3, 2)中，源字元串為'CORPORATE FLOOR', 目标字元串為'OR'，起始位置為3，取第2個比對項的位置。
預設查找順序為從左到右。當起始位置為負數的時候，從右邊開始查找。
是以SELECT INSTR('CORPORATE FLOOR', 'OR', -1, 1) "Instring" FROM DUAL的顯示結果是
Instring
——————
14
replace(x,search_string,replace_string) 字元替換
格式：replace('字元1', '字元2'
,'字元3') ——将字元1中的字元2，替換成字元3
2. 數值函數：
round(x [,y]) ——四舍五入，x是數值（如：4.5646等），y表示保留幾位小數，y沒有的時候代表不保留小數。
trunc(x,[,y]) ——截斷，x是數值（如：4.5646等），y表示保留幾位小數，y沒有的時候代表不保留小數。
mod(m,n) —— 求餘
ceil(x) ——傳回特定的最小數（大于等于x的最小整數），我覺得我的了解更全面，小數也涵蓋了：向數軸上x的右邊找整數。
floor(x) —— 傳回特定的最大數（小于等于x的最大整數），向數軸上x的左邊找整數。
3. 日期函數：
sysdate 傳回系統目前日期
實際上ORACLE内部存儲日期的格式是：世紀，年，月，日，小，分鐘，秒。
不管如何輸入都這樣
9i開始，預設的日期格式是：DD-MON-RR,之前是DD-MON-YY
RR 和YY 都是世紀後的兩位，但有差別
ORACLE的有效日期範圍是：公元前年月日－年月日
RR日期格式：
1、如果目前年份最後兩位是：－，并且指定年份的最後兩位也為－，
則傳回本世紀
例：目前年：， 01－－，表示2008 年
2、如果目前年份最後兩位是：－，指定年份最後兩位為50－
則傳回上世紀。
例：目前年：，01－－，表示1998
3、如果目前年最後兩位為：－，指定年份最後兩位為0－，
則傳回下世紀。
例：目前年：，－－表示的是年
4、如果目前年最後兩位是：－，指定年份最後兩位為：－
則傳回本世紀。
months_between(x，y） 兩個日期之間相差的月數
例：查詢最近個月入職的員工
add_months(x,y) 傳回x上加上y個月後的結果
last_day(x) 傳回指定日期所在月最後一天的日期
next_day(x，day) 傳回指定日期的下一day的時間值，day是一個文本串，比如SATURDAY
extract ——提取日期
select extract(day from sysdate) from dual
select extract(month from sysdate) from dual;
select extract(year from sysdate) from dual;
4. 轉換函數：
TO_DATE(char[, 'format_model']) TO_DATE函數将一個字元串轉換成日期格式
函數有個fx 修飾語。這個修飾語為TO_DATE函數的字元函
數中的獨立變量和日期格式指定精确比對.
TO_CHAR(date, 'format_model') 轉換為CHAR類型，
必須與單引号一起嵌入，區分大小寫，
用逗号把日期數值分開，有一個fm 移除填補空白或者阻止零開頭
TO_CHAR(number, 'format_model')
TO_NUMBER(char[, 'format_model']) TO_NUMBER 函數将一個字元串轉換成一個數字格式:
select to_date('1999-09-23','yyyy-mm-dd') from dual;
資料類型的轉換分為隐式資料類型轉換和顯式資料類型轉換
在表達式中, Oracle伺服器能自動地轉換下列各項，即隐式轉換:
VARCHAR2 or CHAR =====〉NUMBER
VARCHAR2 or CHAR =====〉DATE
對表達式指派, Oracle伺服器能自動地轉換下列各項，即隐式轉換:
NUMBER =======〉VARCHAR2 or CHAR
DATE =======〉VARCHAR2 or CHAR
日期格式元素：
YYYY 數字年份
YEAR 英文年份
MM 數字月
MONTH 英文月
MON 英文縮寫
DD 數字日
DY 英文縮寫
DAY 英文
5. 通用函數
decode 條件判斷
格式：decode (col|expression,search1,result1 [,search2,result2,...] [,default])
判斷col|exporession的值，當search1比對時，則傳回，result1,
與search2比對時，傳回result2 ... 如果都不比對，傳回default。
select EMPNO,ENAME,JOB,SAL,
decode(job,'CLERK',SAL*1.15,'SALESMAN',SAL*1.1,SAL*1.12) NEW_SAL
FROM SCOTT.EMP;
if then else 條件判斷
case 表達式
CASE expr WHEN comparison_expr1 THEN return_expr1
[WHEN comparison_expr2 THEN return_expr2
WHEN comparison_exprn THEN return_exprn
ELSE else_expr]
END
四、示範*/
--lower函數
SQL> select lower('SQL') from dual;
LOW
---
sql
SQL> select EMPNO,ENAME,JOB from scott.emp where lower(ename) like 'a%';
EMPNO ENAME JOB
---------- ---------- ---------
7499 ALLEN SALESMAN
7876 ADAMS CLERK
SQL> insert into scott.emp(empno,ename) values(9999,'albert');
1 row created.
SQL> select * from scott.emp where lower(ename) like 'a%';
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
---------- ---------- --------- ---------- ---------- ---------- ---------- ----------
9999 albert
7499 ALLEN SALESMAN 7698 1981-02-20 1600 300 30
7876 ADAMS CLERK 7788 1987-05-23 1100 20
SQL> select * from scott.emp where ename like 'A%';
--upper函數
SQL> select upper('SQL Course') as Upper_Char from dual;
UPPER_CHAR
----------
SQL COURSE
--單詞首子母轉大寫
SQL> select initcap(ename) as initcap_name scott.emp where ename = 'albert';
INITCAP_NAME
Albert
--字元的拼接,||與concat等效
SQL> select ename || ' is an ' || job from scott.emp where ename = 'SCOTT';
ENAME||'ISAN'||JOB
---------------------------
SCOTT is an ANALYST
SQL> select concat(concat(ename,' is an '),job) as concat_str from scott.emp where ename = 'SCOTT';
CONCAT_STR
--------------------------
SCOTT is an ANALYST
--SUBSTR，截取子串，下面的例子從第個位置開始連續截取個字元
SQL> select substr('HelloWorld',2,3) from dual;
SUB
ell
--LENGTH 取字元串長度
SQL> select length('HelloWord') as String_length from dual;
STRING_LENGTH
-------------
9
-- lpad | rpad 字元串的填充
-- lpad，左填充，直到達到指定長度為止
SQL> select lpad('salary',10,'*') as String_Lpad from dual;
STRING_LPA
****salary
--指定長度為，多出的部分被截斷
SQL> select lpad('salary',4,'*') as String_Lpad from dual;
STRI
----
sala
--rpad，右填充，直到達到指定長度為止
SQL> select rpad('salary',10,'|') as String_Rpad from dual;
STRING_RPA
salary||||
SQL> select rpad('salary',5,'|') as String_Rpad from dual;
STRIN
-----
salar
-- trim 删除首尾字元，格式：trim('h' from 'hello hello')，預設的方式為both
SQL> select trim('h' from 'hello helloh') as String_Trim from dual;
STRING_TRI
ello hello
-- trim 删除首尾字元，指定leading隻删首部
SQL> select trim(leading 'h' from 'hello helloh') as Trim_Leading from dual;
TRIM_LEADIN
-----------
ello helloh
-- trim 删除首尾字元，指定trailing隻删尾部
SQL> select trim(trailing 'h' from 'hello helloh') as Trim_Trailling from dual;
TRIM_TRAILL
hello hello
--rtrim ,ltrim
SQL> select rtrim('hello helloh','h') as Rtrim_String ,
2 ltrim('hello helloh','h') as Ltrim_String
3 from dual;
RTRIM_STRIN LTRIM_STRIN
----------- -----------
hello hello ello helloh
--replace 字元替換
SQL> select replace('Jack and Johnson','J','Bl') as String_Replace from dual;
STRING_REPLACE
------------------
Black and Blohnson
--instr 下面的示例從第個字元開始，傳回第二個OR的位置
SQL> select instr('CORPORATE FOLLOR','OR',3,2) as Instring from dual;
INSTRING
15
--round 四舍五入函數
SQL> select round(102.253,2) as round_func from dual;
ROUND_FUNC
102.25
SQL> select round(102.253,0) as round_func from dual;
102
SQL> select round(102.253,-1) as round_func from dual;
100
--trunc 截斷函數
SQL> select trunc(2010.328) as trunc_func_1,
2 trunc(2010.328,1) as trunc_func_2,
3 trunc(2010.328,-1) as trunc_func_3
4 from dual;
TRUNC_FUNC_1 TRUNC_FUNC_2 TRUNC_FUNC_3
------------ ------------ ------------
2010 2010.3 2010
--#MOD(m,n) 取餘函數
SQL> select mod(2010,3) as mod_func from dual;
MOD_FUNC
SQL> select mod(5,3) as mod_func from dual;
2
--ceil(x) 傳回特定的最小數（大于等于x的最小整數）
SQL> select ceil(593.3) as ceil_func from dual;
CEIL_FUNC
594
--floor(x) 傳回特定的最大數（小于等于x的最大整數）
SQL> select floor(593.4) as floor_func from dual;
FLOOR_FUNC
593
--month_between(日期，日期)兩個日期相差的月數
SQL> select empno,ename,job,months_between(sysdate,hiredate) as diff_month from scott.emp;
EMPNO ENAME JOB DIFF_MONTH
---------- ---------- --------- ----------
7369 SMITH CLERK 351.370601
7499 ALLEN SALESMAN 349.273827
7521 WARD SALESMAN 349.209311
7566 JONES MANAGER 347.854472
7654 MARTIN SALESMAN 342
7698 BLAKE MANAGER 346.88673
7782 CLARK MANAGER 345.628666
7788 SCOTT ANALYST 275.306085
7839 KING PRESIDENT 340.370601
7844 TURNER SALESMAN 342.660924
SQL> select * from scott.emp where months_between(sysdate,hiredate) <= 300;
7788 SCOTT ANALYST 7566 1987-04-19 3000 20
--add_months(日期,n) 傳回在指定的日期後，加上n個月後的日期
SQL> select add_months(sysdate,5) from dual;
ADD_MONTHS
2010-08-28
--last_day(sysdate) 傳回指定日期所在月最後一天的日期
SQL> select last_day(sysdate) from dual;
LAST_DAY(S
2010-03-31
--next_day 傳回指定日期的下一day的時間值，day是一個文本串，比如SATURDAY
SQL> select next_day('05-FEB-2005','TUESDAY') as nextday from dual;
NEXTDAY
---------
08-FEB-05
/*EXTRACT*/
SQL> select extract(day from sysdate) from dual;
EXTRACT(DAYFROMSYSDATE)
-----------------------
28
SQL> select extract(month from sysdate) from dual;
EXTRACT(MONTHFROMSYSDATE)
-------------------------
3
SQL> select extract(year from sysdate) from dual;
EXTRACT(YEARFROMSYSDATE)
------------------------
2010
--使用ROUND 和TRUNC函數處理日期
--round(sysdate,'MONTH') 當月第一天
--round(sysdate,'YEAR') 當年的第一天
--trunc(sysdate,'MONTH') 當月第一天
--trunc(sysdate,'YEAR') 當年的第一天
SQL> select sysdate,round(sysdate,'MONTH'),round(sysdate,'YEAR'),
2 trunc(sysdate,'MONTH'),trunc(sysdate,'YEAR')
SYSDATE ROUND(SYS ROUND(SYS TRUNC(SYS TRUNC(SYS
--------- --------- --------- --------- ---------
15-APR-10 01-APR-10 01-JAN-10 01-APR-10
01-JAN-10
--類型轉換
-- to_char
SQL> select empno,ename,hiredate,to_char(hiredate,'fmDD
Month YYYY') as hiredate2,
2 to_char(hiredate,'DD MM YYYY') as hiredate3
3 from scott.emp
4 where sal > 2500;
EMPNO ENAME HIREDATE HIREDATE2 HIREDATE3
---------- ---------- --------- ----------------- ----------
7566 JONES 02-APR-81 2 April 1981 02 04 1981
7698 BLAKE 01-MAY-81 1 May 1981 01 05 1981
7788 SCOTT 19-APR-87 19 April 1987 19 04 1987
7839 KING 17-NOV-81 17 November 1981 17 11 1981
7902 FORD 03-DEC-81 3 December 1981 03 12 1981
SQL> select to_char(12345.67) as char1,to_char(12345.67,'99,999.99') as char2
2 from dual;
CHAR1 CHAR2
-------- ----------
12345.67 12,345.67
--當被轉換的資料位數超過格式指定位數，則出現錯誤。
SQL> select to_char(12345678.90,'99,999.99') as char1 from dual;
CHAR1
##########
--to_number
SQL> select to_number('970.13') as number1,
2 to_number('970.13') + 35.5 as nunber2,
3 to_number('-$12,345.67','$99,999.99') as number3
NUMBER1 NUNBER2 NUMBER3
---------- ---------- ----------
970.13 1005.63 -12345.67
--to_date
--注意：最終日期采用預設格式DD-MON—YY顯示
SQL> select to_date('05-JUL-2008') as date1,to_date('05-JUL-08') as date2,
2 to_date('July 5,2008','MONTH DD,YYYY') as date3,
3 to_date('7.4.08','MM.DD.YY') as date4
DATE1 DATE2 DATE3 DATE4
--------- --------- --------- ---------
05-JUL-08 05-JUL-08 05-JUL-08 04-JUL-08
--case when
SQL> select empno,ename,sal,deptno,case deptno when 20 then 1.10 * sal
2 when 30 then 1.20 * sal
3 else 1.30 * sal
4 end
5 as newsal
6 from scott.emp order by deptno;
EMPNO ENAME SAL DEPTNO NEWSAL
---------- ---------- ---------- ---------- ----------
7782 CLARK 2450 10 3185
7839 KING 5000 10 6500
7934 MILLER 1300 10 1690
7566 JONES 2975 20 3272.5
7902 FORD 3000 20 3300
7876 ADAMS 1100 20 1210
7369 SMITH 800 20 880
7788 SCOTT 3000 20 3300
7521 WARD 1250 30 1500
7844 TURNER 1500 30 1800
/*DECODE*/
SQL> select empno,ename,job,sal, decode(job,'CLERK',sal*1.5,'SALESMAN',sal*1.1,sal*1.2) as newsal from scott.emp;
EMPNO ENAME JOB SAL NEWSAL
---------- ---------- --------- ---------- ----------
7369 SMITH CLERK 800 1200
7499 ALLEN SALESMAN 1600 1760
7521 WARD SALESMAN 1250 1375
7566 JONES MANAGER 2975 3570
7654 MARTIN SALESMAN 1250 1375
7698 BLAKE MANAGER 2850 3420
7782 CLARK MANAGER 2450 2940
7788 SCOTT ANALYST 3000 3600
7839 KING PRESIDENT 5000 6000
7844 TURNER SALESMAN 1500 1650
![龍珠訓練營task04[圖]](data:image/gif;base64,R0lGODlhAQABAIAAAP///wAAACwAAAAAAQABAAACAkQBADs=)