OraclePL/SQL單行函數和組函數詳解

正在看的ORACLE教程是:OraclePL/SQL單行函數和組函數詳解。 1　　函數是一種有零個或多個參數並且有一個傳回值的程式。在SQL中Oracle內建了一系列函數，這些函數都可被稱為SQL或PL/SQL語句，函數主要分為兩大類：
2
3　　 單行函數
4
5　　 組函數
6
7　　本文將討論如何利用單行函數以及使用規則。
8
9　　SQL中的單行函數
10
11　　SQL和PL/SQL中內建很多類型的函數，有字元、數字、日期、轉換、和混合型等多種函數用於處理單行資料，因此這些都可被統稱為單行函數。這些函數均可用於SELECT,WHERE、ORDER BY等子句中，例如下面的例子中就包含了TO_CHAR,UPPER,SOUNDEX等單行函數。
12
13SELECT ename,TO_CHAR(hiredate,'day,DD-Mon-YYYY')
14FROM emp
15Where UPPER(ename) Like 'AL%'
16ORDER BY SOUNDEX(ename)
17
18　　單行函數也可以在其他語句中使用，如update的SET子句，INSERT的VALUES子句，DELET的WHERE子句,認證考試特別注意在SELECT語句中使用這些函數，所以我們的注意力也集中在SELECT語句中。
19
20　　NULL和單行函數
21
22　　在如何理解NULL上開始是很困難的，就算是一個很有經驗的人依然對此感到困惑。NULL值表示一個未知資料或者一個空值，算術操作符的任何一個運算元為NULL值，結果均為提個NULL值,這個規則也適合很多函數，只有CONCAT,DECODE,DUMP,NVL,REPLACE在調用了NULL參數時能夠返回非NULL值。在這些中NVL函數時最重要的，因為他能直接處理NULL值，NVL有兩個參數：NVL(x1,x2),x1和x2都式運算式，當x1為null時返回X2,否則返回x1。
23
24　　下面我們看看emp資料表它包含了薪水、獎金兩項，需要計算總的補償
25
26column name emp_id salary bonus
27
28key type pk
29nulls/unique nn,u nn
30fk table
31datatype number number number
32length 11.2 11.2
33
34　　不是簡單的將薪水和獎金加起來就可以了，如果某一行是null值那麼結果就將是null，比如下面的例子：
35
36update emp
37set salary=(salary+bonus)*1.1
38
39　　這個語句中，僱員的工資和獎金都將更新為一個新的值，但是如果沒有獎金，即 salary + null,那麼就會得出錯誤的結論，這個時候就要使用nvl函數來排除null值的影響。
40所以正確的語句是：
41
42update emp
43set salary=(salary+nvl(bonus,0)*1.1
44
45　　單行字串函數
46
47　　單行字串函數用於操作字串資料，他們大多數有一個或多個參數，其中絕大多數返回字串
48
49　　ASCII()
50　　c1是一字串，返回c1第一個字母的ASCII碼，他的逆函數是CHR()
51
52SELECT ASCII('A') BIG_A,ASCII('z') BIG_z FROM emp
53
54BIG_A BIG_z
5565 122
56
57　　CHR(＜i＞)[NCHAR_CS]
58　　i是一個數字，函數返回十進位表示的字元
59
60select CHR(65),CHR(122),CHR(223) FROM emp
61
62CHR65 CHR122 CHR223
63A z B
64
65　　CONCAT(,)
66　　c1,c2均為字串，函數將c2串連到c1的後面，如果c1為null,將返回c2.如果c2為null,則返回c1，如果c1、c2都為null，則返回null。他和操作符||返回的結果相同
67
68select concat('slobo ','Svoboda') username from dual
69
70username
71
72slobo Syoboda
73
74
75　　INITCAP()
76　　c1為一字串。函數將每個單詞的第一個字母大寫其它字母小寫返回。單詞由空格，控制字元，標點符號限制。
77
78select INITCAP('veni,vedi,vici') Ceasar from dual
79
80Ceasar
81
82Veni,Vedi,Vici
83
84
85　　INSTR(,[,＜i＞[,]])
86　　c1,c2均為字串，i,j為整數。函數返回c2在c1中第j次出現的位置，搜尋從c1的第i個字元開始。當沒有發現需要的字元時返回0,如果i為負數，那麼搜尋將從右至左進行，但是位置的計算還是從左至右，i和j的預設值為1.
87
88select INSTR('Mississippi','i',3,3) from dual
89
90INSTR('MISSISSIPPI','I',3,3)
91
9211
93
94select INSTR('Mississippi','i',-2,3) from dual
95
96INSTR('MISSISSIPPI','I',3,3)
97
982
99
100
101　　INSTRB(,[,i[,j])
102　　與INSTR（）函數一樣，只是他返回的是位元組，對於單位元組INSTRB()等於INSTR()
103
104　　LENGTH()
105　　c1為字串，返回c1的長度，如果c1為null，那麼將返回null值。
106
107select LENGTH('Ipso Facto') ergo from dual
108
109ergo
110
11110
112
113　　LENGTHb()
114　　與LENGTH()一樣，返回位元組。
115
116　　lower()
117　　返回c的小寫字元，經常出現在where子串中
118
119select LOWER(colorname) from itemdetail WHERE LOWER(colorname) LIKE '%white%'
120
121COLORNAME
122
123Winterwhite
124
125
126　　LPAD(,＜i＞[,])
127　　c1,c2均為字串，i為整數。在c1的左側用c2字串補足致長度i,可多次重複，如果i小於c1的長度，那麼只返回i那麼長的c1字元，其他的將被截去。c2的預設值為單空格，參見RPAD。
128
129select LPAD(answer,7,'') padded,answer unpadded from question;
130
131PADDED UNPADDED
132
133Yes Yes
134NO NO
135Maybe maybe
136
137
138　　LTRIM(,)
13

[1] [2] [3] [4] 下一頁

正在看的ORACLE教程是:OraclePL/SQL單行函數和組函數詳解。9　　把c1中最左邊的字元去掉，使其第一個字元不在c2中，如果沒有c2，那麼c1就不會改變。
140
141select LTRIM('Mississippi','Mis') from dual
142
143LTR
144
145ppi
146
147　　RPAD(,＜i＞[,])
148　　在c1的右側用c2字串補足致長度i,可多次重複，如果i小於c1的長度，那麼只返回i那麼長的c1字元，其他的將被截去。c2的預設值為單空格,其他與LPAD相似
149
150　　RTRIM(,)
151　　把c1中最右邊的字元去掉，使其第後一個字元不在c2中，如果沒有c2，那麼c1就不會改變。
152
153　　REPLACE(,[,])
154　　c1,c2,c3都是字串，函數用c3代替出現在c1中的c2後返回。
155
156select REPLACE('uptown','up','down') from dual
157
158REPLACE
159
160downtown
161
162　　STBSTR(,＜i＞[,])
163　　c1為一字串，i,j為整數，從c1的第i位開始返回長度為j的子字串，如果j為空白，則直到串的尾部。
164
165select SUBSTR('Message',1,4) from dual
166
167SUBS
168
169Mess
170
171
172　　SUBSTRB(,＜i＞[,])
173　　與SUBSTR大致相同，只是I,J是以位元組計算。
174
175　　SOUNDEX()
176　　返回與c1發音相似的詞
177
178select SOUNDEX('dawes') Dawes SOUNDEX('daws') Daws, SOUNDEX('dawson') from dual
179
180Dawes Daws Dawson
181
182D200 D200 D250
183
184　　TRANSLATE(,,)
185　　將c1中與c2相同的字元以c3代替
186
187select TRANSLATE('fumble','uf','ar') test from dual
188
189TEXT
190
191ramble
192
193　　TRIM([[]] from c3)
194　　將c3串中的第一個，最後一個，或者都刪除。
195
196select TRIM(' space padded ') trim from dual
197
198TRIM
199
200space padded
201
202　　UPPER()
203　　返回c1的大寫，常出現where子串中
204
205select name from dual where UPPER(name) LIKE 'KI%'
206
207NAME
208
209KING
210
211　　單行數字函數
212
213　　單行數字函數運算元字資料，執行數學和算術運算。所有函數都有數字參數並返回數字值。所有三角函數的運算元和值都是弧度而不是角度，oracle沒有提供內建的弧度和角度的轉換函式。
214
215　　ABS()
216　　返回n的絕對值
217
218　　ACOS()
219　　反餘玄函數，返回-1到1之間的數。n表示弧度
220
221select ACOS(-1) pi,ACOS(1) ZERO FROM dual
222
223PI ZERO
224
2253.14159265 0
226
227　　ASIN()
228　　反正玄函數，返回-1到1，n表示弧度
229
230　　ATAN()
231　　反正切函數，返回n的反正切值，n表示弧度。
232
233　　CEIL()
234　　返回大於或等於n的最小整數。
235
236　　COS()
237　　返回n的餘玄值，n為弧度
238
239　　COSH()
240　　返回n的雙曲餘玄值，n 為數字。
241
242select COSH(<1.4>) FROM dual
243
244COSH(1.4)
245
2462.15089847
247
248　　EXP()
249　　返回e的n次冪，e=2.71828183.
250
251　　FLOOR()
252　　返回小於等於N的最大整數。
253
254　　LN()
255　　返回N的自然對數，N必須大於0
256
257　　LOG(,)
258　　返回以n1為底n2的對數
259
260　　MOD()
261　　返回n1除以n2的餘數，
262
263　　POWER(,)
264　　返回n1的n2次方
265
266　　ROUND(,)
267　　返回舍入小數點右邊n2位的n1的值，n2的預設值為0，這回將小數點最接近的整數，如果n2為負數就舍入到小數點左邊相應的位上，n2必須是整數。

[NextPage]

268
269select ROUND(12345,-2),ROUND(12345.54321,2) FROM dual
270
271ROUND(12345,-2) ROUND(12345.54321,2)
272
27312300 12345.54
274
275　　SIGN()
276　　如果n為負數，返回-1,如果n為正數，返回1，如果n=0返回0.
277
278　　SIN（)
279　　返回n的正玄值,n為弧度。
280
281　　SINH()
282　　返回n的雙曲正玄值,n為弧度。
283
284　　SQRT()
285　　返回n的平方根,n為弧度
286
287　　TAN（)
288　　返回n的正切值,n為弧度
289
290　　TANH()
291　　返回n的雙曲正切值,n為弧度
292
293　　TRUNC(,)
294　　返回截尾到n2位小數的n1的值，n2預設設定為0，當n2為預設設定時會將n1截尾為整數，如果n2為負值，就截尾在小數點左邊相應的位上。
295
296　　單行日期函數
297
298　　單行日期函數操作DATA資料類型，絕大多數都有DATA資料類型的參數，絕大多數返回的也是DATA資料類型的值。
299
300　　ADD_MONTHS(,＜i＞)
301　　返回日期d加上i個月後的結果。i可以使任意整數。如果i是一個小數，那麼資料庫將隱式的他轉換成整數，將會截去小數點後面的部分。
302
303　　LAST_DAY()
304　　函數返回包含日期d的月份的最後一天
305
306　　MONTHS_BETWEEN(,)
307　　返回d1和d2之間月的數目,如果d1和d2的日的日期都相同，或者都使該月的最後一天，那麼將返回一個整數，否則會返回的結果將包含一個分數。
308
309　　NEW_TIME(,,)
310　　d1是一個日期資料類型，當時區tz1中的日期和時間是d時，返回時區tz2中的日期和時間。tz1和tz2時字串。
311
312　　NEXT_DAY(,)
313　　返回日期d後由dow給出的條件的第一天，dow使用當前會話中給出的語言指定了一周中的某一天，返回的時間分量與d的時間分量相同。
314
315select NEXT_DAY('01-Jan-2000','Monday') "1st Monday",NEXT_DAY('01-Nov-2004','Tuesday')+7 "2nd Tuesday") from dual;
316
3171st Monday 2nd Tuesday
318
31903-Jan-2000 09-Nov-2004
320
321　　ROUND([,])
322　　將日期d按照fmt指定的格式舍入，fmt為字串。
323
324　　SYADATE
325　　函數沒有參數，返回當前日期和時間。
326
327　　TRUNC([,])
328　　返回由fmt指定的單位的日期d.
329
330　　單行轉換函式
331
332

上一頁 [1] [2] [3] [4] 下一頁

正在看的ORACLE教程是:OraclePL/SQL單行函數和組函數詳解。　　單行轉換函式用於操作多資料類型，在資料類型之間進行轉換。
333
334　　CHARTORWID()
335　　c 使一個字串，函數將c轉換為RWID資料類型。
336
337SELECT test_id from test_case where rowid=CHARTORWID('AAAA0SAACAAAALiAAA')
338
339　　CONVERT(,[,])
340　　c尾字串，dset、sset是兩個字元集，函數將字串c由sset字元集轉換為dset字元集，sset的預設設定為資料庫的字元集。
341
342　　HEXTORAW()
343　　x為16進位的字串，函數將16進位的x轉換為RAW資料類型。
344
345　　RAWTOHEX()
346　　x是RAW資料類型字串，函數將RAW資料類轉換為16進位的資料類型。
347
348　　ROWIDTOCHAR()
349　　函數將ROWID資料類型轉換為CHAR資料類型。
350
351　　TO_CHAR([[,)
352　　x是一個data或number資料類型，函數將x轉換成fmt指定格式的char資料類型，如果x為日期nlsparm=NLS_DATE_LANGUAGE 控制返回的月份和日份所使用的語言。如果x為數字nlsparm=NLS_NUMERIC_CHARACTERS 用來指定小數位和千分位的分隔字元，以及貨幣符號。
353
354NLS_NUMERIC_CHARACTERS ="dg", NLS_CURRENCY="string"
355
356　　TO_DATE([,[,）
357　　c表示字串，fmt表示一種特殊格式的字串。返回按照fmt格式顯示的c,nlsparm表示使用的語言。函數將字串c轉換成date資料類型。
358
359　　TO_MULTI_BYTE()
360　　c表示一個字串，函數將c的擔子截字元轉換成多位元組字元。
361
362　　TO_NUMBER([,[,)
363　　c表示字串，fmt表示一個特殊格式的字串，函數傳回值按照fmt指定的格式顯示。nlsparm表示語言，函數將返回c代表的數字。
364
365　　TO_SINGLE_BYTE()
366　　將字串c中得多位元組字元轉化成等價的單位元組字元。該函數僅當資料庫字元集同時包含單位元組和多位元組字元時才使用
367
368　　其它單行函數
369
370　　BFILENAME(
371,)
372　　dir是一個directory類型的對象，file為一檔案名稱。函數返回一個空的BFILE位置值指示符，函數用於初始化BFILE變數或者是BFILE列。
373
374　　DECODE(,,[,,,[])
375　　x是一個運算式，m1是一個匹配運算式，x與m1比較，如果m1等於x，那麼返回r1,否則,x與m2比較，依次類推m3,m4,m5.直到有返回結果。
376
377　　DUMP(,[,[,[,]]])
378　　x是一個運算式或字元，fmt表示8進位、10進位、16進位、或則單字元。函數返回包含了有關x的內部表示資訊的VARCHAR2類型的值。如果指定了n1,n2那麼從n1開始的長度為n2的位元組將被返回。
379
380　　EMPTY_BLOB()
381　　該函數沒有參數，函數返回 一個空的BLOB位置指示符。函數用於初始化一個BLOB變數或BLOB列。
382
383　　EMPTY_CLOB()
384　　該函數沒有參數，函數返回 一個空的CLOB位置指示符。函數用於初始化一個CLOB變數或CLOB列。
385
386　　GREATEST()
387　　exp_list是一列運算式，返回其中最大的運算式，每個運算式都被隱含的轉換第一個運算式的資料類型，如果第一個運算式是字串資料型別中的任何一個，那麼返回的結果是varchar2資料類型， 同時使用的比較是非填充空格類型的比較。
388
389　　LEAST()
390　　exp_list是一列運算式，返回其中最小的運算式，每個運算式都被隱含的轉換第一個運算式的資料類型，如果第一個運算式是字串資料型別中的任何一個，將返回的結果是varchar2資料類型， 同時使用的比較是非填充空格類型的比較。
391
392　　UID
393　　該函數沒有參數，返回唯一標示當前資料庫使用者的整數。
394
395　　USER
396　　返回目前使用者的使用者名稱
397
398　　USERENV()
399　　基於opt返回包含當前會話資訊。opt的可選值為：
400
401　　ISDBA　 　　會話中SYSDBA腳色響應，返回TRUE
402　　SESSIONID 　返回審計會話標示符
403　　ENTRYID 　　返回可用的審計項標示符
404　　INSTANCE　　在會話串連後，返回執行個體標示符。該值只用於運行Parallel 伺服器並且有 多個執行個體的情況下使用。
405　　LANGUAGE　　返回語言、地區、資料庫設定的字元集。
406　　LANG　　　　返回語言名稱的ISO縮寫。
407　　TERMINAL　　為當前會話使用的終端或電腦返回作業系統的標示符。
408
409　　VSIZE()
410　　x是一個運算式。返回x內部表示的位元組數。
411
412　　SQL中的組函數
413
414　　組函數也叫集合函數，返回基於多個行的單一結果，行的準確數量無法確定，除非查詢被執行並且所有的結果都被包含在內。與單行函數不同的是，在解析時所有的行都是已知的。由於這種差別使組函數與單行函數有在要求和行為上有微小的差異.
415
416　　組（多行）函數
417
418　　與單行函數相比，oracle提供了豐富的基於組的，多行的函數。這些函數可以在select或select的having子句中使用，當用於select子串時常常都和GROUP BY一起使用。
419
420　　AVG([{DISYINCT|ALL}])
421　　返回數值的平均值。預設設定為ALL.
422
423SELECT AVG(sal),AVG(ALL sal),AVG(DISTINCT sal) FROM scott.emp
424
425AVG(SAL) AVG(ALL SAL) AVG(DISTINCT SAL)
426
4271877.94118 1877.94118 1916.071413
428
429
430　　COUNT({*|DISTINCT|ALL} )
431　　返回查詢中行的數目，預設設定是ALL,*表示返回所有的行。
432
433　　MAX([{DISTINCT|ALL}])
434　　返回挑選清單項目的最大值，如果x是字串資料型別，他返回一個VARCHAR2資料類型，如果X是一個DATA資料類型，返回一個日期，如果X是numeric資料類型，返回一個數字。注意distinct和all不起作用，應為最大值與這兩種設定是相同的。
435
436　　MIN([{DISTINCT|ALL}])
437　　返回挑選清單項目的最小值。
438
439　　STDDEV([{DISTINCT|ALL}])
440　　返回選者的清單項目的標準差，所謂標準差是方差的平方根。
441
442　　SUM([{DISTINCT|ALL}])
443　　返回挑選清單項目的數值的總和。
444
445　　VARIANCE([{DISTINCT|ALL}])
446　　返回挑選清單項目的統計方差。
447
448　　用GROUP BY給資料分組
449
450　　正如題目暗示的那樣組函數就是操作那些已經分好組的資料，我們告訴資料庫用GROUP BY怎樣給資料分組或者分類，當我們在SELECT語句的SELECT子句中使用組函數時，我們必須把為分組或非常數列放置在GROUP BY子句中，如果沒有用group by進行專門處理，那麼預設的分類是將整個結果設為一類。
451
452select stat,counter(*) zip_count from zip_codes GROUP BY state;
453
454ST ZIP_COUNT
455-- ---------
456AK 360
457AL 1212
458AR 1309
459AZ 768
460CA 3982
461
462　　在這個例子中，我們用state欄位分類；如果我們要將結果按照zip_codes排序,可以用ORDER BY語句，ORDER BY子句可以使用列或組函數。
463
464sel

上一頁 [1] [2] [3] [4] 下一頁

正在看的ORACLE教程是:OraclePL/SQL單行函數和組函數詳解。ect stat,counter(*) zip_count from zip_codes GROUP BY state ORDER BY COUNT(*) DESC;
465
466ST COUNT(*)
467-- --------
468NY 4312
469PA 4297
470TX 4123
471CA 3982
472
473　　用HAVING子句限制分組資料
474
475　　現在你已經知道了在查詢的SELECT語句和ORDER BY子句中使用主函數，組函數只能用於兩個子串中，組函數不能用於WHERE子串中，例如下面的查詢是錯誤的 ：
476
477錯誤
478SELECT sales_clerk,SUN(sale_amount) FROM gross_sales WHERE sales_dept='OUTSIDE' AND SUM(sale_amount)>10000 GROUP BY sales_clerk
479
480
481　　這個語句中資料庫不知道SUM()是什麼，當我們需要指示資料庫對行分組，然後限制分組後的行的輸出時，正確的方法是使用HAVING語句：
482
483SELECT sales_clerk,SUN(sale_amount)
484FROM gross_sales
485WHERE sales_dept='OUTSIDE'
486GROUP BY sales_clerk
487HAVING SUM(sale_amount)>10000;
488
489　　嵌套函數
490
491　　函數可以嵌套。一個函數的輸出可以是另一個函數的輸入。運算元有一個可繼承的執行過程。但函數的優先權只是基於位置，函數遵循由內到外，由左到右的原則。嵌套技術一般用於象DECODE這樣的能被用於邏輯判斷語句IF.THENELSE的函數。
492
493　　嵌套函數可以包括在組函數中嵌套單行函數，或者組函數嵌套入單行函數或組函數中。比如下面的例子：
494
495SELECT deptno, GREATEST(COUNT(DISTINCT job),COUNT(DISTINCT mgr) cnt,
496COUNT(DISTINCT job) jobs,
497COUNT(DISTINCT mgr) mgrs
498FROM emp
499GROUP BY deptno;
500
501DEPTNO CNT JOBS MGRS
502------ --- ---- ----
50310 4 4 2
50420 4 3 4
50530 3 3 2

上一頁

上一頁 [1] [2] [3] [4]