實(shí)用！快速學(xué)習(xí)Oracle數(shù)據(jù)庫(kù)函數(shù)使用技巧！(oracle數(shù)據(jù)庫(kù)函數(shù)怎么用)
Oracle數(shù)據(jù)庫(kù)是目前市場(chǎng)上使用最廣泛的數(shù)據(jù)庫(kù)之一，也是許多企業(yè)級(jí)系統(tǒng)的首選數(shù)據(jù)庫(kù)。在處理數(shù)據(jù)時(shí)，Oracle數(shù)據(jù)庫(kù)中的函數(shù)是非常重要的一部分。函數(shù)可以提供一種簡(jiǎn)單的方法來處理數(shù)據(jù)，使得代碼更清晰，操作更快捷。在使用Oracle數(shù)據(jù)庫(kù)時(shí)，掌握一些函數(shù)使用技巧就顯得尤為重要。
創(chuàng)新互聯(lián)專業(yè)為企業(yè)提供三元網(wǎng)站建設(shè)、三元做網(wǎng)站、三元網(wǎng)站設(shè)計(jì)、三元網(wǎng)站制作等企業(yè)網(wǎng)站建設(shè)、網(wǎng)頁(yè)設(shè)計(jì)與制作、三元企業(yè)網(wǎng)站模板建站服務(wù)，10年三元做網(wǎng)站經(jīng)驗(yàn)，不只是建網(wǎng)站，更提供有價(jià)值的思路和整體網(wǎng)絡(luò)服務(wù)。
1. 常用函數(shù)
（1）NVL函數(shù)：該函數(shù)用于檢查一個(gè)表達(dá)式是否為NULL，如果是NULL則返回一個(gè)特定的值。它的語法如下：
NVL ( expr1, expr2 )
其中，expr1用來檢測(cè)是否為NULL，expr2用來指定表達(dá)式的值。
例如：假設(shè)我們?cè)诓樵円粋€(gè)員工表中的工資，但有些員工的工資字段為NULL，則我們可以使用如下語句：
SELECT NVL(SALARY, 0) FROM EMPLOYEE;
其中的0即為特定的值。
（2）TO_CHAR函數(shù)：該函數(shù)用于將一個(gè)日期、數(shù)字等數(shù)據(jù)類型轉(zhuǎn)換為字符型。其語法如下：
TO_CHAR ( value, format )
其中，value為要轉(zhuǎn)換的值，format定義了轉(zhuǎn)換的樣式。
例如：假設(shè)我們要查詢一個(gè)員工的工齡，但是數(shù)據(jù)庫(kù)存儲(chǔ)的是入職時(shí)間的日期型數(shù)據(jù)，我們可以使用如下語句：
SELECT TO_CHAR(MONTHS_BETWEEN(sysdate,EMPLOYEE.HIRE_DATE)/12,’FM999D99′) “年數(shù)” FROM EMPLOYEE;
其中，F(xiàn)M999D99用于定義年數(shù)的格式。
（3）SUM函數(shù)：該函數(shù)用于對(duì)數(shù)據(jù)庫(kù)中某一列的值進(jìn)行求和操作。其語法如下：
SUM ([DISTINCT|ALL] expression)
其中，expression為要求和的列。
例如：假設(shè)我們要求出員工表中所有員工的工資總和，則我們可以使用如下語句：
SELECT SUM(SALARY) FROM EMPLOYEE;
2. 高級(jí)函數(shù)
（1）LAG/LEAD函數(shù)：這兩個(gè)函數(shù)用于訪問同一行中的前一行和后一行數(shù)據(jù)。其語法如下：
LAG/LEAD (expr, [offset [,default]])
其中，expr為要訪問的數(shù)據(jù)，offset表示訪問的偏移量，默認(rèn)為1，即訪問前一行或后一行數(shù)據(jù)。default表示當(dāng)無法訪問到數(shù)據(jù)時(shí)返回的默認(rèn)值。
例如：假設(shè)我們要查詢員工表中每個(gè)員工的前一行工資情況，則我們可以使用如下語句：
SELECT EMPLOYEE.ID, EMPLOYEE.SALARY, LAG(SALARY, 1) OVER (ORDER BY ID) AS “前一行工資情況” FROM EMPLOYEE;
其中，OVER子句表示排序方式。
（2）CAST函數(shù)：該函數(shù)用于將一種數(shù)據(jù)類型轉(zhuǎn)換為另一種數(shù)據(jù)類型。其語法如下：
CAST (expression AS data_type)
其中，expression為要轉(zhuǎn)換的值，data_type表示轉(zhuǎn)換后的數(shù)據(jù)類型。
例如：假設(shè)我們要將員工表中的字符串類型的工資字段轉(zhuǎn)換為數(shù)字類型，我們可以使用如下語句：
SELECT CAST(SALARY AS NUMBER) FROM EMPLOYEE;
（3）DECODE函數(shù)：該函數(shù)用于根據(jù)條件返回不同的結(jié)果。其語法如下：
DECODE(expr,search,result,[search,result]……[default])
例如：假設(shè)我們要查詢員工表中“性別”一列，但是該表存儲(chǔ)“性別”的值為0/1，我們想要將它們轉(zhuǎn)換為“男”/“女”，則可以使用如下語句：
SELECT NAME, DECODE(SEX, 0, ‘男’, 1, ‘女’) AS “性別” FROM EMPLOYEE;
其中，0和1表示檢測(cè)到的值，‘男’和‘女’表示要返回的結(jié)果。
本文介紹了Oracle數(shù)據(jù)庫(kù)中常用的一些函數(shù)及其使用技巧，這些函數(shù)在處理數(shù)據(jù)時(shí)是非常實(shí)用的，能夠提高效率、節(jié)省時(shí)間。需要注意的是，在使用這些函數(shù)時(shí)需要根據(jù)具體的情況選擇最適合的函數(shù)進(jìn)行操作。熟練掌握這些函數(shù)的使用方法，能夠更好地發(fā)揮Oracle數(shù)據(jù)庫(kù)的優(yōu)勢(shì)，提高工作效率和數(shù)據(jù)處理能力。
相關(guān)問題拓展閱讀：
- oracle中的over函數(shù)怎么用的，什么意思
oracle中的over函數(shù)怎么用的，什么意思
Oracle從8.1.6開始提供分析函數(shù)，分析函數(shù)汪賣用于計(jì)算基于組的某種聚合值，它和聚合函數(shù)的不同之處是對(duì)于每個(gè)組返回多行，而聚合函數(shù)對(duì)于每個(gè)組只返回一行。
一、 over函數(shù)
over函數(shù)指定了分析函數(shù)工作的數(shù)據(jù)窗口的大小，這個(gè)數(shù)據(jù)窗口大小可能會(huì)隨著行的變化而變化，例如：
over(order by salary)按照salary排序進(jìn)行累計(jì)，order by是個(gè)默認(rèn)的開窗函數(shù)
over(partition by deptno) 按照部門分區(qū)
over(order by salary range between 50 preceding and 150 following)每行對(duì)應(yīng)的數(shù)據(jù)窗口是之前行幅度值不超過50，之后行幅度值不超過150的數(shù)據(jù)記錄
over(order by salary rows between 50 perceding and 150 following)前50行，后150行
over（order by salary rows between unbounded preceding and unbounded following）所有行
over（order by salary range between unbounded preceding and unbounded following）所有行
二、 sum函數(shù)
功能描述：該函數(shù)計(jì)算組中表達(dá)式的累積和。
SAMPLE：下例計(jì)算同一經(jīng)理下員工的薪水累積值
SELECT manager_id, last_name, salary,
SUM (salary) OVER (PARTITION BY manager_id ORDER BY salary
RANGE UNBOUNDED PRECEDING) l_csum
FROM employees
WHERE manager_id in (101,103,108);
三、 應(yīng)用實(shí)例
1， 測(cè)試環(huán)境設(shè)置
設(shè)有銷售表t_sales (subcompany,branch,region,customer,sale_qty); 存儲(chǔ)客戶的銷售明細(xì)，記錄如下所示。
Subcompany Branch Region Customer Sale_qty
北京分公司 北京經(jīng)營(yíng)部 片區(qū)1 客戶1 1
北京分公司 北京經(jīng)營(yíng)部 片區(qū)1 客戶1 1
北京分公司 北京經(jīng)營(yíng)部 片區(qū)1 客戶2 1
北京分公司 北京經(jīng)營(yíng)部 片區(qū)1 客戶2 1
北京分公司 北京經(jīng)營(yíng)部 片區(qū)2 客戶1 1
北京分公司 北京經(jīng)營(yíng)部 片區(qū)2 客戶1 1
北京分公司 北京經(jīng)營(yíng)部 片區(qū)2 客戶2 1
北京分公司 北京經(jīng)營(yíng)部 片區(qū)2 客戶2 1
北京分公司 其他經(jīng)營(yíng)部 片區(qū)1 客戶1 1
北京分公司 其他經(jīng)營(yíng)部 片區(qū)1 客戶1 1
北京分公司 其他經(jīng)營(yíng)部 片區(qū)1 客戶2 1
北京分公司 其他經(jīng)營(yíng)部 片區(qū)1 客戶2 1
北京分公司 其他經(jīng)營(yíng)部 片區(qū)2 客戶1 1
北京分公司 其他經(jīng)營(yíng)部 片區(qū)2 客戶1 1
北京分公司 其他經(jīng)營(yíng)部 片區(qū)2 客戶2 1
北京分公司 其他經(jīng)營(yíng)部 片區(qū)2 客戶2 1
create table t_sales(
subcompany varchar2(40),
branch varchar2(40),
region varchar2(40),
customer varchar2(40),
sale_qty numeric(18,4)
);
comment on table t_sales is ‘銷售表，分析函數(shù)測(cè)試’激悉;
comment on column t_sales.subcompany is ‘分公司’;
comment on column t_sales.branch is ‘經(jīng)營(yíng)部’;
comment on column t_sales.region is ‘片區(qū)’;
comment on column t_sales.customer is ‘客戶’;
comment on column t_sales.sale_qty is ‘銷售數(shù)量’;
2，問題提出
現(xiàn)在要求給出銷售匯總報(bào)表，報(bào)表中需要提供的數(shù)據(jù)包括客戶明陵乎匯總，和客戶在其上級(jí)機(jī)構(gòu)中的銷售比例。
Subcompany Branch Region Customer Sale_qty Rate
北京分公司 北京經(jīng)營(yíng)部 片區(qū)1 客戶1 2 50％
北京分公司 北京經(jīng)營(yíng)部 片區(qū)1 客戶2 2 50％
北京分公司 北京經(jīng)營(yíng)部 片區(qū)1 小計(jì) 4 50％
北京分公司 北京經(jīng)營(yíng)部 片區(qū)2 客戶1 2 50％
北京分公司 北京經(jīng)營(yíng)部 片區(qū)2 客戶2 2 50％
北京分公司 北京經(jīng)營(yíng)部 片區(qū)2 小計(jì) 4 50％
北京分公司 北京經(jīng)營(yíng)部 小計(jì) 小計(jì) 8 50％
北京分公司 北京經(jīng)營(yíng)部 片區(qū)1 客戶1 2 50％
北京分公司 北京經(jīng)營(yíng)部 片區(qū)1 客戶2 2 50％
北京分公司 北京經(jīng)營(yíng)部 片區(qū)1 小計(jì) 4 50％
北京分公司 北京經(jīng)營(yíng)部 片區(qū)2 客戶1 2 50％
北京分公司 北京經(jīng)營(yíng)部 片區(qū)2 客戶2 2 50％
北京分公司 北京經(jīng)營(yíng)部 片區(qū)2 小計(jì) 4 50％
北京分公司 北京經(jīng)營(yíng)部 小計(jì) 小計(jì) 8 50％
北京分公司 小計(jì) 小計(jì) 小計(jì)％
3，解決方案（方案1）
首先我們可以使用oracle對(duì)group by 的擴(kuò)展功能rollup得到如下的聚合匯總結(jié)果。
select
subcompany,
branch,
region,
customer,
sum(sale_qty) sale_qty
from t_sales
group by rollup(subcompany,branch,region,customer);
Subcompany Branch Region Customer Sale_qty
北京分公司 北京經(jīng)營(yíng)部 片區(qū)1 客戶1 2
北京分公司 北京經(jīng)營(yíng)部 片區(qū)1 客戶2 2
北京分公司 北京經(jīng)營(yíng)部 片區(qū)1 4
北京分公司 北京經(jīng)營(yíng)部 片區(qū)2 客戶1 2
北京分公司 北京經(jīng)營(yíng)部 片區(qū)2 客戶2 2
北京分公司 北京經(jīng)營(yíng)部 片區(qū)2 4
北京分公司 北京經(jīng)營(yíng)部 8
北京分公司 其他經(jīng)營(yíng)部 片區(qū)1 客戶1 2
北京分公司 其他經(jīng)營(yíng)部 片區(qū)1 客戶2 2
北京分公司 其他經(jīng)營(yíng)部 片區(qū)1 4
北京分公司 其他經(jīng)營(yíng)部 片區(qū)2 客戶1 2
北京分公司 其他經(jīng)營(yíng)部 片區(qū)2 客戶2 2
北京分公司 其他經(jīng)營(yíng)部 片區(qū)2 4
北京分公司 其他經(jīng)營(yíng)部 8
北京分公司 16
16
分析上面的臨時(shí)結(jié)果，我們看到：
明細(xì)到客戶的匯總信息，其除數(shù)為當(dāng)前的sum(sale_qty),被除數(shù)應(yīng)該是到片區(qū)的小計(jì)信息。
明細(xì)到片區(qū)的匯總信息，其除數(shù)為片區(qū)的sum(sale_qty),被除數(shù)為聚合到經(jīng)營(yíng)部的匯總數(shù)據(jù)。
。。。
考慮到上述因素，我們可以使用oracle的開窗函數(shù)over，將數(shù)據(jù)定位到我們需要定位的記錄。如下代碼中，我們利用開窗函數(shù)over直接將數(shù)據(jù)定位到其上次的小計(jì)位置。
over(partition by decode(f_branch, 1, null, subcompany), decode(f_branch, 1, null, decode(f_region, 1, null, branch)), decode(f_branch, 1, null, decode(f_region, 1, null, decode(f_customer, 1, null, region))), null)
經(jīng)整理后的查詢語句如下。
select subcompany,
decode(f_branch, 1,subcompany||'(D???)’, branch),
decode(f_region,1,branch||'(D???)’,region),
decode(f_customer,1,region||'(D???)’, customer),
sale_qty,
trim(to_char(round(sale_qty/
sum(sale_qty) over(partition by decode(f_branch, 1, null, subcompany), decode(f_branch, 1, null, decode(f_region, 1, null, branch)), decode(f_branch, 1, null, decode(f_region, 1, null, decode(f_customer, 1, null, region))), null),2) *100,99990.99))
from (select grouping(branch) f_branch,
grouping(region) f_region,
grouping(customer) f_customer,
subcompany,
branch,
region,
customer,
sum(sale_qty) sale_qty
from t_sales
group by subcompany, rollup(branch, region, customer))
Subcompany Branch Region Customer Sale_qty Rate
北京分公司 北京經(jīng)營(yíng)部 片區(qū)1 客戶1 2 50.00
北京分公司 北京經(jīng)營(yíng)部 片區(qū)1 客戶2 2 50.00
北京分公司 北京經(jīng)營(yíng)部 片區(qū)2 客戶1 2 50.00
北京分公司 北京經(jīng)營(yíng)部 片區(qū)2 客戶2 2 50.00
北京分公司 北京經(jīng)營(yíng)部 片區(qū)1 片區(qū)1(小計(jì)) 4 50.00
北京分公司 北京經(jīng)營(yíng)部 片區(qū)2 片區(qū)2(小計(jì)) 4 50.00
北京分公司 其他經(jīng)營(yíng)部 片區(qū)1 客戶1 2 50.00
北京分公司 其他經(jīng)營(yíng)部 片區(qū)1 客戶2 2 50.00
北京分公司 其他經(jīng)營(yíng)部 片區(qū)2 客戶1 2 50.00
北京分公司 其他經(jīng)營(yíng)部 片區(qū)2 客戶2 2 50.00
北京分公司 其他經(jīng)營(yíng)部 片區(qū)1 片區(qū)1(小計(jì)) 4 50.00
北京分公司 其他經(jīng)營(yíng)部 片區(qū)2 片區(qū)2(小計(jì)) 4 50.00
北京分公司 北京經(jīng)營(yíng)部 北京經(jīng)營(yíng)部(小計(jì)) (小計(jì)) 8 50.00
北京分公司 其他經(jīng)營(yíng)部 其他經(jīng)營(yíng)部(小計(jì)) (小計(jì)) 8 50.00
北京分公司 北京分公司(小計(jì)) (小計(jì)) (小計(jì)).00
北京分公司 北京經(jīng)營(yíng)部 片區(qū)1 客戶1 2 50.00
4，可能的另外一種解決方式（方案2）
select subcompany,
decode(f_branch, 1,subcompany||'(D???)’, branch),
decode(f_region,1,branch||'(D???)’,region),
decode(f_customer,1,region||'(D???)’, customer),
sale_qty,
/* trim(to_char(round(sale_qty/*/
decode(f_branch+f_region+f_customer,
,
(sum(sale_qty) over(partition by subcompany,branch,region))/2,
,
(sum(sale_qty) over(partition by subcompany,branch))/3,
,
(sum(sale_qty) over(partition by subcompany))/4 ,
sum(sale_qty) over()/4
)/*
,2) *100,99990.99))*/
from (select grouping(branch) f_branch,
grouping(region) f_region,
grouping(customer) f_customer,
subcompany,
branch,
region,
customer,
sum(sale_qty) sale_qty
from t_sales
group by subcompany, rollup(branch, region, customer))
在上面的解決方式中，更大的問題在于開窗函數(shù)過大。導(dǎo)致每次計(jì)算涉及到的行數(shù)過多，影響到執(zhí)行的速度和效率。并且需要額外的計(jì)算處理清除多余疊加進(jìn)去的數(shù)值 。
over函做拿并數(shù)是oracle中的分析函數(shù)，分析函數(shù)是對(duì)行集組進(jìn)行聚合計(jì)算，但是不敏歲像普通聚合仗函數(shù)那樣每組只返回一個(gè)值，分析函數(shù)可以為每組返回多個(gè)值。
使用方法為：over(partition by排 列名1
order by
列名2 )，括號(hào)中的兩個(gè)關(guān)鍵詞partition by 和order by 可以只出現(xiàn)一個(gè)。over() 前面是一個(gè)函數(shù)，如果是
聚合函數(shù)
，那么order by 不能一起使用。
擴(kuò)展資料
在SQL語句中，很多查詢語句需要進(jìn)行
GROUP BY
分組匯總，但是一旦經(jīng)過分組，SELECT返回的記錄孢數(shù)就會(huì)減少。為了保留所有原始行記錄，并且仍可以進(jìn)行分組
數(shù)據(jù)分析
，分析函數(shù)應(yīng)運(yùn)而生。
oracle數(shù)據(jù)庫(kù)函數(shù)，分析函數(shù)用于為行定義一個(gè)窗口，對(duì)一組值進(jìn)行操作，不需要使用GROUP BY子句對(duì)數(shù)據(jù)進(jìn)行分組，能夠在同一行中同時(shí)返回基礎(chǔ)行的列和聚合列。
RANK（）也為每一組的行生成一個(gè)序號(hào)，與ROW_NUMBER()不同的是如果按照ORDER BY的排序，如果有相同的值會(huì)生成相同的序號(hào)，并且接下來的序號(hào)是不連序的。例如兩個(gè)相同的行生成序號(hào)3，那么接下來會(huì)生成序號(hào)。
DENSE_RANK（）和RANK（）類似，不同的是如果有相同的序號(hào)，那么接下來的序號(hào)不會(huì)間斷。也就是說如果兩個(gè)相同的行生成序號(hào)，那么接下來生成的序號(hào)還是。
參考資純跡料來源：
百度百科-分析函數(shù)
oracle數(shù)據(jù)庫(kù)函數(shù)怎么用的介紹就聊到這里吧，感謝你花時(shí)間閱讀本站內(nèi)容，更多關(guān)于oracle數(shù)據(jù)庫(kù)函數(shù)怎么用,實(shí)用！快速學(xué)習(xí)Oracle數(shù)據(jù)庫(kù)函數(shù)使用技巧！,oracle中的over函數(shù)怎么用的，什么意思的信息別忘了在本站進(jìn)行查找喔。
香港服務(wù)器選創(chuàng)新互聯(lián)，2H2G首月10元開通。
創(chuàng)新互聯(lián)（www.cdcxhl.com）互聯(lián)網(wǎng)服務(wù)提供商,擁有超過10年的服務(wù)器租用、服務(wù)器托管、云服務(wù)器、虛擬主機(jī)、網(wǎng)站系統(tǒng)開發(fā)經(jīng)驗(yàn)。專業(yè)提供云主機(jī)、虛擬主機(jī)、域名注冊(cè)、VPS主機(jī)、云服務(wù)器、香港云服務(wù)器、免備案服務(wù)器等。
網(wǎng)站題目：實(shí)用！快速學(xué)習(xí)Oracle數(shù)據(jù)庫(kù)函數(shù)使用技巧！(oracle數(shù)據(jù)庫(kù)函數(shù)怎么用)
新聞來源：http://m.jiaotiyi.com/article/coidsjd.html