1、Oracle函數(shù)與高級查詢二 Oracle的函數(shù)2.1 什么是函數(shù)函數(shù)：指對一堆邏輯的打包，能夠支持傳入的參數(shù)，根據(jù)參數(shù)得到一定的結(jié)果；那么,在oracle中，都有什么函數(shù)，有什么類型的函數(shù)，函數(shù)有什么分類？2.2 函數(shù)的分類2.2.1 單行與多行除了聚合(組)函數(shù)，其它全是單行函數(shù)Oracle的函數(shù)咱們可以簡單分成再種：多行函數(shù)與單行函數(shù)，如下圖:函數(shù)單行函數(shù)多行函數(shù)2.2.1.1 什么是單行函數(shù) 簡單理解：對單行數(shù)據(jù)進行是操作，得到單行數(shù)據(jù) ; 比如concat連接函數(shù)concat 連接函數(shù)得到的效果; 使用concat 不會影響行數(shù)，以前100行，使用concat之后得到的結(jié)果也是100

2、行。單行函數(shù) ：在執(zhí)行獲得結(jié)果后?？梢灾С謧魅?yún)?shù)【列和表達式】，針對每一行單獨做運算。最終不會影響結(jié)果的行數(shù)。單行函數(shù)特點:1接收參數(shù)返回一個值2對每一行返回行起作用3每一行返回一個結(jié)果4可以修改數(shù)據(jù)類型 5可以使用嵌套 concat(concat(col1,col2),col2) 除了concat 這樣的單行函數(shù)以外，還有其他的單行函數(shù)，比如字符處理函數(shù)；比如有時要求用戶名全部大寫或者小寫，或者需要得到一個人的姓，這些都需要字符函數(shù)進行處理，字符函數(shù)中，也存在分類2.2.1.2 什么是多行函數(shù)多行函數(shù)：簡單理解對多行數(shù)據(jù)進行操作，最后返回一個數(shù)據(jù)比如count函數(shù)：Count 針對過濾后的

3、結(jié)果 支持傳入表的列名或者常量，對所有行的記錄統(tǒng)一處理。最終只會得到一個結(jié)果,會影響結(jié)果行數(shù);多行函數(shù) : 在執(zhí)行獲得結(jié)果后。可以支持傳入?yún)?shù)【列和表達式】，針對每一行整體統(tǒng)計做運算。最終只會產(chǎn)生1行數(shù)據(jù)咱們所說的單行與多行是指這個函數(shù)操作的數(shù)據(jù)是單行還是多行(不是說的結(jié)果)2.2.1.3 單行、多行分別用在哪？擴展理解與練習比如concat 用在哪里? 可以放在select 后，把它的姓和名連接起來；單行函數(shù): select 關(guān)鍵字之后 where 之后的條件上 （where 之后單行函數(shù) 如下）姓名中有e或者a的員工?把姓名組合起來查詢select * from employeeswher

4、e first_name | last_name like '%e%'or first_name | last_name like '%a%'使用函數(shù)Select * from employees Where concat(first_name,last_name) like %e%Or first_name | last_name like %a%;多行函數(shù):針對Select 關(guān)鍵之后 ；比如count函數(shù) 統(tǒng)計所有行 ，放在select之后 使用多行函數(shù) 得到就一個結(jié)果，那我放在where 后面 有意義嗎？Where 是針對每一行， 而多行函數(shù) 返回結(jié)果只有

5、一行，如果放在where 之后，執(zhí)行一次，是一個結(jié)果，執(zhí)行二次還是同一個結(jié)果，所以沒有意義.2.2.2 函數(shù)功能分類可以分為：字符函數(shù)、數(shù)字函數(shù)，轉(zhuǎn)換函數(shù)，日期函數(shù)，組函數(shù)，其它函數(shù)字符(串)函數(shù)：拼接字符串，截取字符串，全變大寫，全變小寫，首字母大寫.數(shù)字(number)函數(shù)：四舍五入，舍掉 轉(zhuǎn)換函數(shù)：字符-日期 字符-數(shù)字日期函數(shù)：拿到年，月，日，時，分，秒組函數(shù)：sum,count,max.其它函數(shù)：2.3 字符函數(shù)字符函數(shù)分為大小寫轉(zhuǎn)換函數(shù)與字符處理函數(shù)2.3.1 大小寫轉(zhuǎn)換函數(shù)大小寫轉(zhuǎn)換函數(shù):Lower(列|表達式) 全部轉(zhuǎn)換小寫Upper(列|表達式) 全部轉(zhuǎn)換大寫Initcap(

6、列|表達式) 首字母大寫練習案例：select 'Hello world' from dual;大寫：select upper('Hello world') from dual;小寫：select lower('Hello world') from dual;練習案例：1,查詢first_name為randall的員工工資；分析：我們查詢的時候不需要關(guān)心名稱中的大小寫問題select * from employees where lower(first_name) ='randall'練習案例：2,客戶在輸入名字的時候，可以不會在

7、意名稱的大小寫，比如，Wang Xiaoer，假設(shè)現(xiàn)在在發(fā)送郵件，需要按照正規(guī)的格式輸出姓名；請使用SQL處理分析：我們可以插入一條小寫的名字，然后使用首字母大寫查詢出來，格式更完整insert into employees (employee_id,first_name,last_name,email,phone_number,hire_date,job_id) values (300,'wang','xiaoer','xxx','1895236541',sysdate,'SH_CLERK')查詢： select

8、 initcap(first_name)|' '|initcap(last_name) from employees;2.3.2 字符處理函數(shù)1、CONCAT(strexp, strexp): 連接兩個字符串 2、Substr(str,start_index,length) :從指定的位置截取指定長度的字符串 3、LENGTH(strexp):返回字符串的長度 LENGTHB(strexp):返回字節(jié)的長度4、LPAD( string1, padded_length, pad_string ) 在列的左邊粘貼字符 其中string1是需要粘貼字符的字符串 * padded_le

9、ngth是返回的字符串的數(shù)量，如果這個數(shù)量比原字符串的長度要短，lpad函數(shù)將會把字符串截取成padded_length; * pad_string是個可選參數(shù)，這個字符串是要粘貼到string1的左邊，如果這個參數(shù)未寫，lpad函數(shù)將會在string1的左邊粘貼空格。 5、RPAD(粘貼字符) RPAD  在列的右邊粘貼字符6、Trim():截取字符串兩端特殊字符 Trim('s' from 'string');可以指定從字符串兩邊要截取的特殊字符2.3.2.1 CONCAT(strexp, strexp):連接兩個字符串注意：在Oracle中只能連

10、接兩個字符串(MySQL中可以任何加字符串) 這里可以使用函數(shù)嵌套的方式錯誤方式：select concat('123','456',789) from dualORA-00909: 參數(shù)個數(shù)無效正確方式(嵌套)：select concat(concat('123','456'),'789') from dual;2.3.2.2 Substr(str,start_index,length) 從指定的位置截取指定長度的字符串Str:的字符串start_index：從第幾個開始(第1開始計算)如果是0，和1效果是一樣的

11、 如果是負數(shù)，從倒數(shù)第幾個字符開始Length：截取幾個字母(長度)案例一：select substr('123456789',0,4) from dual;結(jié)果：1234案例二：select substr('123456789',1,4) from dual;結(jié)果：1234上面兩個例子大家看到，兩個結(jié)果都是1234，在SQL中很多參數(shù)是應(yīng)該從1開始計算的，而且從1開始計算也更加符合我們的計算，因此建議大家從1開始計算。比如，我們拿到從4到7的數(shù)：案例三：select substr('123456789',4,4) from dual;結(jié)果：45

12、672.3.2.3 LENGTH(strexp)返回字符串的長度案例一：select length('admin') from dual; ->結(jié)果：5select length('中國') from dual; ->結(jié)果2還有一個針對字節(jié)的LENGTHB(strexp):返回字節(jié)的長度案例二：select lengthb('admin') from dual; ->結(jié)果：5select lengthb('中國') from dual; ->結(jié)果4練習案例：隱藏部分員工姓名，例如Julia Dellinge

13、r取前三位Jul加最后三位ger，中間補5位'*'，得到Jul*ger分析：拿到前三位，拿到后三位，中間補齊select subStr(first_name,1,3)|'*'|subStr(last_name,-3) from employees注意：起始位置是可以寫負數(shù)的(從負幾開始)思考： select subStr('123456789',-5,3) from dual; 值是多少現(xiàn)在問大家一個問題，如果我們要在中間加50個*，那怎么做？如果自己去寫的話，實在是太麻煩了，而且容易寫錯。因此，真有這個需求的話，可以用到下一個函數(shù)：LPAD2.

14、3.2.4 LPAD( string1, padded_length, pad_string ) 在列的左邊粘貼字符 其中string1是需要粘貼字符的字符串 * padded_length是返回的字符串的數(shù)量，如果這個數(shù)量比原字符串的長度要短，lpad函數(shù)將會把字符串截取成padded_length; * pad_string是個可選參數(shù)，這個字符串是要粘貼到string1的左邊，如果這個參數(shù)未寫，lpad函數(shù)將會在string1的左邊粘貼空格。 案例一：結(jié)果：加上Lap的結(jié)果在前面多了一些空格，而且是加上hello剛好10個字符案例二：select Lpad('hello'

15、,10,'*') from dual;結(jié)果：*hello案例三：完成前面提出的50個*select subStr(first_name,1,3)|Lpad('*',50,'*')|subStr(last_name,-3) from employees注意：第一個參數(shù)不能為空2.3.2.5 RPAD(粘貼字符) RPAD  在列的右邊粘貼字符 這個效果和LPAD一樣，只是字符加在右邊2.3.3.6 Trim():截取字符串兩端特殊字符(默認截取空格)Trim('s' from 'string');可以指定從

16、字符串兩邊要截取的特殊字符案例一：select trim(' hello world ') from dual;結(jié)果：hello world案例二：select trim('-' from '-hello world-') from dual;結(jié)果：hello world2.4 數(shù)字函數(shù)Round：傳回一個數(shù)值,該數(shù)值是按照指定的小數(shù)位數(shù)進行四舍五入運算的結(jié)果。TRUNC函數(shù)返回處理后的數(shù)值，其工作機制與ROUND函數(shù)極為類似，只是該函數(shù)不對指定小數(shù)前或后的部分做相應(yīng)舍入選擇處理，而統(tǒng)統(tǒng)截去mod（number1，number2）兩個數(shù)值相除并返

17、回其余數(shù)。運算符執(zhí)行 number1 除以 number2 操作。2.4.1 Round傳回一個數(shù)值,該數(shù)值是按照指定的小數(shù)位數(shù)進行四舍五入運算的結(jié)果。語法：ROUND( number, decimal_places )number : 需四舍五入處理的數(shù)值，decimal_places : 四舍五入 , 小數(shù)取幾位 ( 預(yù)設(shè)為 0 )案例：ROUND(45.926, 2) 45.93案例一：select round(445.3434,2) from dual; -> 445.34案例二：select round(445.3434,-2) from dual; -> 4002.4.

18、2 TRUNC函數(shù)返回處理后的數(shù)值，其工作機制與ROUND函數(shù)極為類似，只是該函數(shù)不對指定小數(shù)前或后的部分做相應(yīng)舍入選擇處理，而統(tǒng)統(tǒng)截去。語法：TRUNC( number, decimal_places )number : 要截取的數(shù)值，decimal_places : 小數(shù)取幾位 ( 預(yù)設(shè)為 0 )案例：TRUNC(45.926, 2) 45.92案例：select TRUNC(445.3484,2) from dual; -> 445.342.4.3 mod（number1，number2）兩個數(shù)值相除并返回其余數(shù)。運算符執(zhí)行 number1 除以 number2 操作。案例一：se

19、lect mod(100,5) from dual; -> 0案例二：select mod(100,6) from dual; -> 42.4.4 數(shù)字函數(shù)練習題作業(yè)題：在POS系統(tǒng)中，都會有這樣一個功能，在系統(tǒng)中可以設(shè)置找零的方式，比如常見的有下面幾種：1，角以下四舍五入2，元以下四舍五入3，不管分4，不管角5，不管元請思考實現(xiàn)這些功能的方法2.5 轉(zhuǎn)換函數(shù)轉(zhuǎn)換函數(shù)分兩種：隱式與顯示轉(zhuǎn)換函數(shù)：數(shù)字與字符之間的轉(zhuǎn)換/日期與字符之間的轉(zhuǎn)換2.5.1 隱式轉(zhuǎn)換看下面的案例理解：select '555' | 6 from dual; 5556select '555

20、'+6 from dual; 561必需知道內(nèi)部的機制才能知道最后的結(jié)果。咱們并不推薦使用這種方式。注：雖然可以使用隱式數(shù)據(jù)類型轉(zhuǎn)換，但是還是建議執(zhí)行顯式的數(shù)據(jù)類型轉(zhuǎn)換，以確保SQL語句的可靠性。2.5.2 顯示函數(shù)轉(zhuǎn)換從下圖咱們可以看到 NUMBER(數(shù)字)，CHARACTER(字符串)，DATE(日期)是可以相互轉(zhuǎn)換的TO_NUMBER : 字符串轉(zhuǎn)成數(shù)字TO_CHAR:數(shù)字轉(zhuǎn)換成字符串/日期轉(zhuǎn)換成字符串TO_DATE:字符串轉(zhuǎn)換成日期2.5.1 數(shù)字與字符的轉(zhuǎn)換2.5.1.1 to_charA 數(shù)字轉(zhuǎn)換成字符串語法：to_char(number,fmt) 大家應(yīng)該可以看得出來，前

21、面是number,后面是格式。Number很好理解，那么格式到底是怎么回事呢？請看下圖：數(shù)字格式控制符描述9代表一位數(shù)字，如果當前位有數(shù)字，顯示數(shù)字，否則不顯示(小數(shù)部分仍然會強制顯示0）0強制顯示該位，如果當前位有數(shù)字，顯示數(shù)字，否則顯示0$增加美元符號顯示L增加本地貨幣符號顯示.小數(shù)點符號,千分位符號  3,000,000,000.00咱們例出了下面幾個案例來幫助理解：案例一：把一個110.110 變成一個字符串select to_char(110.110) from dual; 這個得到的結(jié)果不正確的，因為默認轉(zhuǎn)換直接把最后一位省去了select to_char(11

22、0.110,'999.999') from dual; 9在這里代表的就是占位，并且小數(shù)點強制顯示案例二：把把一個110.110 變成一000110.110select to_char(110.110,'000999.999') from dual; 0也代表的就是占位，并且所有數(shù)字強制顯示從上面兩個案例中咱們可以想到，9肯定比0的情況要用得多一點。那么0可能會用到什么情況下呢？ 這就需要我們把0與下面的千分位符號結(jié)合進來使用：案例二：財務(wù)看千分位的工資等(財務(wù)看這個就很習慣了)select to_char(110.110,'000,000.000

23、9;) from dual;結(jié)果： 000,110.110下面再來研究$與L：使用美元打印用戶工資select first_name,to_char(salary,'$999999.99') from employees;使用本地貨幣符號打印工資select first_name,to_char(salary,'L999999.99') from employees;使用財務(wù)習慣打印工資select first_name,to_char(salary,'L000,000.00') from employees;2.5.1.2 to_number使

24、用to_number函數(shù)將字符轉(zhuǎn)換為數(shù)字語法：to_number(char , 'fmt')案例一：把￥3100.00 轉(zhuǎn)回成數(shù)字select to_number('￥3100.00','L9999.99') from dual;這個的轉(zhuǎn)換正好是和to_char是相應(yīng)的。格式設(shè)定一樣。2.5.2 日期與字符的轉(zhuǎn)換下面是轉(zhuǎn)換的格式：格式控制符描述YYYY  YYY YY以數(shù)字表示全年(分別代表4位、三位、兩位)的數(shù)字年YEAR年的拼寫MM(mm)兩位數(shù)字月MONTH月的全拼MON月名稱的縮寫DD數(shù)字日DAY星期的全拼D

25、 星期中的第幾天 DY表示三位縮寫的星期格式控制符描述HH，HH12一天中的第幾個小時，12進制表示法 HH24一天中的第幾個小時，取值為0023 MI一小時中的分鐘SS一分鐘中的秒AM顯示上午或下午2.5.2.1 TO_CHAR 函數(shù)操作日期TO_CHAR(date, fmt) ：是字符類型的函數(shù)，轉(zhuǎn)化日期為字符格式(fmt)1、必須用單引號括起來,并且是大小寫敏感2、可包含任何有效的日期格式3、fmt值的寬度正好能容納所有的有效數(shù)字 案例一：拿到日期的年Select to_char(sysdate,'YYYY') from dual;

26、Select to_char(sysdate,'Year') from dual;其它：咱們可以把上述表中的效果都顯示查看一下：1999年入職的員工.案例二：查詢出1999/10/15入職的員工select * from employees where to_char(HIRE_DATE,'YYYY-MM-dd')='1999-10-15'案例三：打印出"xxxx年xx月xx日 xx:xx:xx"格式的日期和時間 Select to_char(sysdate,'yyyy"年"mm"月&qu

27、ot;dd"日"') from dual注：如果添加中文，需要在中文上使用雙引號包裹案例四：查詢出1995年3月至2000年8月入職的員工select * from employees where to_char(hire_date,'YYYY-MM') between '1995-03' and '2000-06'剛才咱們講的都是將日期轉(zhuǎn)換成字符串，大家想一下，怎么把字符串轉(zhuǎn)成日期呢？select to_date('2005-05-12 18','yyyy-MM-dd hh24') f

28、rom dual;自己反推一下即可。2.6 日期函數(shù)add_months(date_value,number_of_months):用于從一個日期值增加或減少一些月份select add_months(sysdate,12) "明年今日" from dual;select add_months(sysdate,-12) "去年今日" from dual;current_date:返回當前會話時區(qū)中的當前日期select sessiontimezone,current_date from dual;alter session set time_zone=&

29、#39;-11:00'/修改當前會話時區(qū)extract(date_field from datetime_value):找出日期或間隔值的字段值select extract(month from sysdate) "當前月份" from dual;last_day(date_value):返回指定日期中的月份的最后一天的日期select last_day('2000-02-01') "潤月" from dual;next_day( date, weekday ):返回指定時間的下一個星期的指定星期對應(yīng)的日期select next_

30、day(sysdate,'星期一') from dual; 如果今天是星期三，下一個星期四就是明天months_between(f,s) 日期f和s間相差月數(shù)select months_between(sysdate,'04-5月-09')from dual;2.7 其它函數(shù)1 NVL函數(shù)NVL(expr1,expr2)將空值轉(zhuǎn)換為替換的值 支持多種數(shù)據(jù)格式可以是日期,字符,數(shù)字 NVL的兩個參數(shù)數(shù)據(jù)類型必須匹配 ,否則出錯。NVL(comm,0)NVL(hiredate,'01-JAN-97') NVL(job,'No Job Yet&

31、#39;)select NVL(null,'01-JAN-97') from dual案例：查詢員工所有工資2 NVL2函數(shù)NVL2(expr0,expr1,expr2)如果expr0不為Null,返回expr1, 為Null,返回expr2. expr0可以為任何數(shù)據(jù)類型研究下面代碼的結(jié)果：SELECT last_name, salary, commission_pct, NVL2(commission_pct, 'SAL+COMM', 'SAL') incomeFROM employees WHERE department_id IN (50

32、, 80);結(jié)果是： 如果這個人有獎金點，那就他的工資就是工資+獎金 如果這個人沒有獎金點，那就他的工資就沒有獎金3 NULLIFNULLIF(expr1,expr2)比較兩個表達式，如果相等返回空值，如果不等返回第一個表達式。研究下面代碼的結(jié)果：SELECT first_name, LENGTH(first_name) "expr1", last_name, LENGTH(last_name) "expr2", NULLIF(LENGTH(first_name), LENGTH(last_name) resultFROM employees;兩個名稱長

33、度相等，返回空，兩個名稱不相等 ，返回第一個名稱的長度4 COALESCECOALESCE(expr1,expr2,.,exprn)返回表達式列表里的第一個非空表達式的值，表達式列表可以包含很多表達式,當?shù)谝粋€滿足值非空時，返回這個表達式的值。 expr1,expr2,.,exprn的數(shù)據(jù)類型必須一致研究下面代碼的結(jié)果：SELECT last_name, COALESCE(commission_pct, salary, 10) commFROM employeesORDER BY commission_pct;如果獎金點存在，返回獎金點如果獎金點不存在，返回工資如果工資都不存在，給10塊錢以后

34、做開發(fā)有這么一個要求：用戶有幾個電話號碼字段，咋們要拿到其中一個有值的公司電話，手機，家里座機，手機2.Select coalesce(workphone,mobile,homephone) from employee5 CASE表達式實現(xiàn)邏輯的IF-THEN-ELSECASE expr WHEN comparison_expr1 THEN return_expr1 WHEN comparison_expr2 THEN return_expr2 WHEN comparison_exprn THEN return_exprn ELSE else_exprEND案例分析：SELECT last_n

35、ame, case job_id when '總經(jīng)理' then 1.15*salary when '副總經(jīng)理' then 1.20*salary else salary end 工資 FROM employees; 練習題：打印公司員工工資等級：5000以下D；5000-10000C;10000-15000B;15000以上Aselect last_name,salary, case trunc(nvl(salary,0)/5000) when 0 then 'D' when 1 then 'C' when 2 then 

36、9;B' else 'A' end from employees6 DECODE函數(shù) DECODE函數(shù)類似于一系列CASE 或 IF-THEN-ELSE 語句 DECODE(col/expression, search1, result1 , search2, result2,., , default)案例分析： SELECT last_name,salary, decode(job_id, '總經(jīng)理',1.15*salary, '副總經(jīng)理',1.10*salary,salary) 工資 FROM employees; 練習題：打印公司員

37、工工資等級：5000以下D；5000-10000C;10000-15000B;15000以上A select last_name,salary, decode(trunc(salary/5000), 0,'D', 1,'C', 2,'B','A') from employees2.8 分組函數(shù)分組函數(shù) 就我們所說的多行函數(shù) 我們看一下 max . 求員工表中工資的最高值，就相當于我們把整個公司的員工分了一個組 求最大值查詢公司員工工資的總和，工資的最大值，最小值，平均值2.8.1 常用分組函數(shù)(通用)AVG (DISTINCT|A

38、LLn)COUNT ( *|DISTINCT|ALLexpr)MAX (DISTINCT|ALLexpr)MIN (DISTINCT|ALLexpr)SUM (DISTINCT|ALLn)1,查詢出公司所有員工的平均工資2,計算公司平均獎金點(即提成比)注意：空值不參與運算這里咱們計算平均提成，可以看到差距是很大的 select avg(commission_pct),sum(commission_pct)/count(employee_id) from employees;正確的統(tǒng)計方式：select avg(nvl(commission_pct,0) from employees;2.8.

39、2 分組分析先分組再統(tǒng)計：查詢出每一個部門的最高工資select max(salary),department_id from employees group by department_id查詢出每一個部門的平均工資select avg(salary),department_id from employees group by department_id查詢平均工資高于8000的部門和其平均工資select avg(salary),department_id from employees group by department_idhaving avg(salary)>8000分析練習題：查詢在95，96，97，98年各進公司多少人select count(employee_id),to_char(hire_date,'yy') from employees group by to_char(hire_date,'yy'