1SQL中的單記錄函數
  21.ASCII
  3返回與指定的字符對應的十進制數;
  4SQL> select ascii('A') A,ascii('a') a,ascii('0') zero,ascii(' ') space from dual;
  5
  6        A         A      ZERO     SPACE
  7--------- --------- --------- ---------
  8       65        97        48        32
  9
 10
 112.CHR
 12給出整數,返回對應的字符;
 13SQL> select chr(54740) zhao,chr(65) chr65 from dual;
 14
 15ZH C
 16-- -
 17趙 A
 18
 193.CONCAT
 20連接兩個字符串;
 21SQL> select concat('010-','88888888')||'轉23'  高乾競電話 from dual;
 22
 23高乾競電話
 24----------------
 25010-88888888轉23
 26
 274.INITCAP
 28返回字符串并將字符串的第一個字母變為大寫;
 29SQL> select initcap('smith') upp from dual;
 30
 31UPP
 32-----
 33Smith
 34
 35
 365.INSTR(C1,C2,I,J)
 37在一個字符串中搜索指定的字符,返回發現指定的字符的位置;
 38C1    被搜索的字符串
 39C2    希望搜索的字符串
 40I     搜索的開始位置,默認為1
 41J     出現的位置,默認為1
 42SQL> select instr('oracle traning','ra',1,2) instring from dual;
 43
 44 INSTRING
 45---------
 46        9
 47
 48
 496.LENGTH
 50返回字符串的長度;
 51SQL> select name,length(name),addr,length(addr),sal,length(to_char(sal)) from gao.nchar_tst;
 52
 53NAME   LENGTH(NAME) ADDR             LENGTH(ADDR)       SAL LENGTH(TO_CHAR(SAL))
 54------ ------------ ---------------- ------------ --------- --------------------
 55高乾競            3 北京市海錠區                6   9999.99                    7
 56
 57 
 58
 597.LOWER
 60返回字符串,并將所有的字符小寫
 61SQL> select lower('AaBbCcDd')AaBbCcDd from dual;
 62
 63AABBCCDD
 64--------
 65aabbccdd
 66
 67
 688.UPPER
 69返回字符串,并將所有的字符大寫
 70SQL> select upper('AaBbCcDd') upper from dual;
 71
 72UPPER
 73--------
 74AABBCCDD
 75
 76 
 77
 789.RPAD和LPAD(粘貼字符)
 79RPAD  在列的右邊粘貼字符
 80LPAD  在列的左邊粘貼字符
 81SQL> select lpad(rpad('gao',10,'*'),17,'*')from dual;
 82
 83LPAD(RPAD('GAO',1
 84-----------------
 85*******gao*******
 86不夠字符則用*來填滿
 87
 88
 8910.LTRIM和RTRIM
 90LTRIM  刪除左邊出現的字符串
 91RTRIM  刪除右邊出現的字符串
 92SQL> select ltrim(rtrim('   gao qian jing   ',' '),' ') from dual;
 93
 94LTRIM(RTRIM('
 95-------------
 96gao qian jing
 97
 98
 9911.SUBSTR(string,start,count)
100取子字符串,從start開始,取count個
101SQL> select substr('13088888888',3,8) from dual;
102
103SUBSTR('
104--------
10508888888
106
107
10812.REPLACE('string','s1','s2')
109string   希望被替換的字符或變量 
110s1       被替換的字符串
111s2       要替換的字符串
112SQL> select replace('he love you','he','i') from dual;
113
114REPLACE('H
115----------
116i love you
117
118
11913.SOUNDEX
120返回一個與給定的字符串讀音相同的字符串
121SQL> create table table1(xm varchar(8));
122SQL> insert into table1 values('weather');
123SQL> insert into table1 values('wether');
124SQL> insert into table1 values('gao');
125
126SQL> select xm from table1 where soundex(xm)=soundex('weather');
127
128XM
129--------
130weather
131wether
132
133
13414.TRIM('s' from 'string')
135LEADING   剪掉前面的字符
136TRAILING  剪掉后面的字符
137如果不指定,默認為空格符 
138
13915.ABS
140返回指定值的絕對值
141SQL> select abs(100),abs(-100) from dual;
142
143 ABS(100) ABS(-100)
144--------- ---------
145      100       100
146
147
14816.ACOS
149給出反余弦的值
150SQL> select acos(-1) from dual;
151
152 ACOS(-1)
153---------
1543.1415927
155
156
15717.ASIN
158給出反正弦的值
159SQL> select asin(0.5) from dual;
160
161ASIN(0.5)
162---------
163.52359878
164
165
16618.ATAN
167返回一個數字的反正切值
168SQL> select atan(1) from dual;
169
170  ATAN(1)
171---------
172.78539816
173
174
17519.CEIL
176返回大于或等于給出數字的最小整數
177SQL> select ceil(3.1415927) from dual;
178
179CEIL(3.1415927)
180---------------
181              4
182
183
18420.COS
185返回一個給定數字的余弦
186SQL> select cos(-3.1415927) from dual;
187
188COS(-3.1415927)
189---------------
190             -1
191
192
19321.COSH
194返回一個數字反余弦值
195SQL> select cosh(20) from dual;
196
197 COSH(20)
198---------
199242582598
200
201
20222.EXP
203返回一個數字e的n次方根
204SQL> select exp(2),exp(1) from dual;
205
206   EXP(2)    EXP(1)
207--------- ---------
2087.3890561 2.7182818
209
210
21123.FLOOR
212對給定的數字取整數
213SQL> select floor(2345.67) from dual;
214
215FLOOR(2345.67)
216--------------
217          2345
218
219
22024.LN
221返回一個數字的對數值
222SQL> select ln(1),ln(2),ln(2.7182818) from dual;
223
224    LN(1)     LN(2) LN(2.7182818)
225--------- --------- -------------
226        0 .69314718     .99999999
227
228
22925.LOG(n1,n2)
230返回一個以n1為底n2的對數 
231SQL> select log(2,1),log(2,4) from dual;
232
233 LOG(2,1)  LOG(2,4)
234--------- ---------
235        0         2
236
237
23826.MOD(n1,n2)
239返回一個n1除以n2的余數
240SQL> select mod(10,3),mod(3,3),mod(2,3) from dual;
241
242MOD(10,3)  MOD(3,3)  MOD(2,3)
243--------- --------- ---------
244        1         0         2
245
246
24727.POWER
248返回n1的n2次方根
249SQL> select power(2,10),power(3,3) from dual;
250
251POWER(2,10) POWER(3,3)
252----------- ----------
253       1024         27
254
255
25628.ROUND和TRUNC
257按照指定的精度進行舍入
258SQL> select round(55.5),round(-55.4),trunc(55.5),trunc(-55.5) from dual;
259
260ROUND(55.5) ROUND(-55.4) TRUNC(55.5) TRUNC(-55.5)
261----------- ------------ ----------- ------------
262         56          -55          55          -55
263
264
26529.SIGN
266取數字n的符號,大于0返回1,小于0返回-1,等于0返回0
267SQL> select sign(123),sign(-100),sign(0) from dual;
268
269SIGN(123) SIGN(-100)   SIGN(0)
270--------- ---------- ---------
271        1         -1         0
272
273
27430.SIN
275返回一個數字的正弦值
276SQL> select sin(1.57079) from dual;
277
278SIN(1.57079)
279------------
280           1
281
282
28331.SIGH
284返回雙曲正弦的值
285SQL> select sin(20),sinh(20) from dual;
286
287  SIN(20)  SINH(20)
288--------- ---------
289.91294525 242582598
290
291
29232.SQRT
293返回數字n的根
294SQL> select sqrt(64),sqrt(10) from dual;
295
296 SQRT(64)  SQRT(10)
297--------- ---------
298        8 3.1622777
299
300
30133.TAN
302返回數字的正切值
303SQL> select tan(20),tan(10) from dual;
304
305  TAN(20)   TAN(10)
306--------- ---------
3072.2371609 .64836083
308
309
31034.TANH
311返回數字n的雙曲正切值
312SQL> select tanh(20),tan(20) from dual;
313
314 TANH(20)   TAN(20)
315--------- ---------
316        1 2.2371609
317
318 
319
32035.TRUNC
321按照指定的精度截取一個數
322SQL> select trunc(124.1666,-2) trunc1,trunc(124.16666,2) from dual;
323
324   TRUNC1 TRUNC(124.16666,2)
325--------- ------------------
326      100             124.16
327
328 
329
33036.ADD_MONTHS
331增加或減去月份
332SQL> select to_char(add_months(to_date('199912','yyyymm'),2),'yyyymm') from dual;
333
334TO_CHA
335------
336200002
337SQL> select to_char(add_months(to_date('199912','yyyymm'),-2),'yyyymm') from dual;
338
339TO_CHA
340------
341199910
342
343
34437.LAST_DAY
345返回日期的最后一天
346SQL> select to_char(sysdate,'yyyy.mm.dd'),to_char((sysdate)+1,'yyyy.mm.dd') from dual;
347
348TO_CHAR(SY TO_CHAR((S
349---------- ----------
3502004.05.09 2004.05.10
351SQL> select last_day(sysdate) from dual;
352
353LAST_DAY(S
354----------
35531-5月 -04
356
357
35838.MONTHS_BETWEEN(date2,date1)
359給出date2-date1的月份
360SQL> select months_between('19-12月-1999','19-3月-1999') mon_between from dual;
361
362MON_BETWEEN
363-----------
364          9
365SQL>selectmonths_between(to_date('2000.05.20','yyyy.mm.dd'),to_date('2005.05.20','yyyy.mm.dd')) mon_betw from dual;
366
367 MON_BETW
368---------
369      -60
370
371
37239.NEW_TIME(date,'this','that')
373給出在this時區=other時區的日期和時間
374SQL> select to_char(sysdate,'yyyy.mm.dd hh24:mi:ss') bj_time,to_char(new_time
375  2  (sysdate,'PDT','GMT'),'yyyy.mm.dd hh24:mi:ss') los_angles from dual;
376
377BJ_TIME             LOS_ANGLES
378------------------- -------------------
3792004.05.09 11:05:32 2004.05.09 18:05:32
380
381
38240.NEXT_DAY(date,'day')
383給出日期date和星期x之后計算下一個星期的日期
384SQL> select next_day('18-5月-2001','星期五') next_day from dual;
385
386NEXT_DAY
387----------
38825-5月 -01
389
390 
391
39241.SYSDATE
393用來得到系統的當前日期
394SQL> select to_char(sysdate,'dd-mm-yyyy day') from dual;
395
396TO_CHAR(SYSDATE,'
397-----------------
39809-05-2004 星期日
399trunc(date,fmt)按照給出的要求將日期截斷,如果fmt='mi'表示保留分,截斷秒
400SQL> select to_char(trunc(sysdate,'hh'),'yyyy.mm.dd hh24:mi:ss') hh,
401  2  to_char(trunc(sysdate,'mi'),'yyyy.mm.dd hh24:mi:ss') hhmm from dual;
402
403HH                  HHMM
404------------------- -------------------
4052004.05.09 11:00:00 2004.05.09 11:17:00
406
407 
408
40942.CHARTOROWID
410將字符數據類型轉換為ROWID類型
411SQL> select rowid,rowidtochar(rowid),ename from scott.emp;
412
413ROWID              ROWIDTOCHAR(ROWID) ENAME
414------------------ ------------------ ----------
415AAAAfKAACAAAAEqAAA AAAAfKAACAAAAEqAAA SMITH
416AAAAfKAACAAAAEqAAB AAAAfKAACAAAAEqAAB ALLEN
417AAAAfKAACAAAAEqAAC AAAAfKAACAAAAEqAAC WARD
418AAAAfKAACAAAAEqAAD AAAAfKAACAAAAEqAAD JONES
419
420
42143.CONVERT(c,dset,sset)
422將源字符串 sset從一個語言字符集轉換到另一個目的dset字符集
423SQL> select convert('strutz','we8hp','f7dec') "conversion" from dual;
424
425conver
426------
427strutz
428
429
43044.HEXTORAW
431將一個十六進制構成的字符串轉換為二進制
432
433
43445.RAWTOHEXT
435將一個二進制構成的字符串轉換為十六進制
436
437 
438
43946.ROWIDTOCHAR
440將ROWID數據類型轉換為字符類型
441
442 
443
44447.TO_CHAR(date,'format')
445SQL> select to_char(sysdate,'yyyy/mm/dd hh24:mi:ss') from dual;
446
447TO_CHAR(SYSDATE,'YY
448-------------------
4492004/05/09 21:14:41
450
451 
452
45348.TO_DATE(string,'format')
454將字符串轉化為ORACLE中的一個日期
455
456
45749.TO_MULTI_BYTE
458將字符串中的單字節字符轉化為多字節字符
459SQL>  select to_multi_byte('高') from dual;
460
461TO
462--
463高
464
465
46650.TO_NUMBER
467將給出的字符轉換為數字
468SQL> select to_number('1999') year from dual;
469
470     YEAR
471---------
472     1999
473
474
47551.BFILENAME(dir,file)
476指定一個外部二進制文件
477SQL>insert into file_tb1 values(bfilename('lob_dir1','image1.gif'));
478
479
48052.CONVERT('x','desc','source')
481將x字段或變量的源source轉換為desc
482SQL> select sid,serial#,username,decode(command,
483  2  0,'none',
484  3  2,'insert',
485  4  3,
486  5  'select',
487  6  6,'update',
488  7  7,'delete',
489  8  8,'drop',
490  9  'other') cmd  from v$session where type!='background';
491
492      SID   SERIAL# USERNAME                       CMD
493--------- --------- ------------------------------ ------
494        1         1                                none
495        2         1                                none
496        3         1                                none
497        4         1                                none
498        5         1                                none
499        6         1                                none
500        7      1275                                none
501        8      1275                                none
502        9        20 GAO                            select
503       10        40 GAO                            none
504
505
50653.DUMP(s,fmt,start,length)
507DUMP函數以fmt指定的內部數字格式返回一個VARCHAR2類型的值
508SQL> col global_name for a30
509SQL> col dump_string for a50
510SQL> set lin 200
511SQL> select global_name,dump(global_name,1017,8,5) dump_string from global_name;
512
513GLOBAL_NAME                    DUMP_STRING
514------------------------------ --------------------------------------------------
515ORACLE.WORLD                   Typ=1 Len=12 CharacterSet=ZHS16GBK: W,O,R,L,D
516
517
51854.EMPTY_BLOB()和EMPTY_CLOB()
519這兩個函數都是用來對大數據類型字段進行初始化操作的函數
520
521
52255.GREATEST
523返回一組表達式中的最大值,即比較字符的編碼大小.
524SQL> select greatest('AA','AB','AC') from dual;
525
526GR
527--
528AC
529SQL> select greatest('啊','安','天') from dual;
530
531GR
532--
533天
534
535
53656.LEAST
537返回一組表達式中的最小值 
538SQL> select least('啊','安','天') from dual;
539
540LE
541--
542啊
543
544
54557.UID
546返回標識當前用戶的唯一整數
547SQL> show user
548USER 為"GAO"
549SQL> select username,user_id from dba_users where user_id=uid;
550
551USERNAME                         USER_ID
552------------------------------ ---------
553GAO                                   25
554
555 
556
55758.USER
558返回當前用戶的名字
559SQL> select user from  dual;
560
561USER
562------------------------------
563GAO
564
565
56659.USEREVN
567返回當前用戶環境的信息,opt可以是:
568ENTRYID,SESSIONID,TERMINAL,ISDBA,LABLE,LANGUAGE,CLIENT_INFO,LANG,VSIZE
569ISDBA  查看當前用戶是否是DBA如果是則返回true
570SQL> select userenv('isdba') from dual;
571
572USEREN
573------
574FALSE
575SQL> select userenv('isdba') from dual;
576
577USEREN
578------
579TRUE
580SESSION
581返回會話標志
582SQL> select userenv('sessionid') from dual;
583
584USERENV('SESSIONID')
585--------------------
586                 152
587ENTRYID
588返回會話人口標志
589SQL> select userenv('entryid') from dual;
590
591USERENV('ENTRYID')
592------------------
593                 0
594INSTANCE
595返回當前INSTANCE的標志
596SQL> select userenv('instance') from dual;
597
598USERENV('INSTANCE')
599-------------------
600                  1
601LANGUAGE
602返回當前環境變量
603SQL> select userenv('language') from dual;
604
605USERENV('LANGUAGE')
606----------------------------------------------------
607SIMPLIFIED CHINESE_CHINA.ZHS16GBK
608LANG
609返回當前環境的語言的縮寫
610SQL> select userenv('lang') from dual;
611
612USERENV('LANG')
613----------------------------------------------------
614ZHS
615TERMINAL
616返回用戶的終端或機器的標志
617SQL> select userenv('terminal') from dual;
618
619USERENV('TERMINA
620----------------
621GAO
622VSIZE(X)
623返回X的大小(字節)數
624SQL> select vsize(user),user from dual;
625
626VSIZE(USER) USER
627----------- ------------------------------
628          6 SYSTEM
629
630 
631
63260.AVG(DISTINCT|ALL)
633all表示對所有的值求平均值,distinct只對不同的值求平均值
634SQLWKS> create table table3(xm varchar(8),sal number(7,2));
635語句已處理。
636SQLWKS>  insert into table3 values('gao',1111.11);
637SQLWKS>  insert into table3 values('gao',1111.11);
638SQLWKS>  insert into table3 values('zhu',5555.55);
639SQLWKS> commit;
640
641SQL> select avg(distinct sal) from gao.table3;
642
643AVG(DISTINCTSAL)
644----------------
645         3333.33
646
647SQL> select avg(all sal) from gao.table3;
648
649AVG(ALLSAL)
650-----------
651    2592.59
652
653
65461.MAX(DISTINCT|ALL)
655求最大值,ALL表示對所有的值求最大值,DISTINCT表示對不同的值求最大值,相同的只取一次
656SQL> select max(distinct sal) from scott.emp;
657
658MAX(DISTINCTSAL)
659----------------
660            5000
661
662
66362.MIN(DISTINCT|ALL)
664求最小值,ALL表示對所有的值求最小值,DISTINCT表示對不同的值求最小值,相同的只取一次
665SQL> select min(all sal) from gao.table3;
666
667MIN(ALLSAL)
668-----------
669    1111.11
670
671
67263.STDDEV(distinct|all)
673求標準差,ALL表示對所有的值求標準差,DISTINCT表示只對不同的值求標準差
674SQL> select stddev(sal) from scott.emp;
675
676STDDEV(SAL)
677-----------
678  1182.5032
679
680SQL> select stddev(distinct sal) from scott.emp;
681
682STDDEV(DISTINCTSAL)
683-------------------
684           1229.951
685
686 
687
68864.VARIANCE(DISTINCT|ALL)
689求協方差 
690
691SQL> select variance(sal) from scott.emp;
692
693VARIANCE(SAL)
694-------------
695    1398313.9
696
697
69865.GROUP BY
699主要用來對一組數進行統計
700SQL> select deptno,count(*),sum(sal) from scott.emp group by deptno;
701
702   DEPTNO  COUNT(*)  SUM(SAL)
703--------- --------- ---------
704       10         3      8750
705       20         5     10875
706       30         6      9400
707
708 
709
71066.HAVING
711對分組統計再加限制條件
712SQL> select deptno,count(*),sum(sal) from scott.emp group by deptno having count(*)>=5;
713
714   DEPTNO  COUNT(*)  SUM(SAL)
715--------- --------- ---------
716       20         5     10875
717       30         6      9400
718SQL> select deptno,count(*),sum(sal) from scott.emp having count(*)>=5 group by deptno ;
719
720   DEPTNO  COUNT(*)  SUM(SAL)
721--------- --------- ---------
722       20         5     10875
723       30         6      9400
724
725
72667.ORDER BY
727用于對查詢到的結果進行排序輸出
728SQL> select deptno,ename,sal from scott.emp order by deptno,sal desc;
729
730   DEPTNO ENAME            SAL
731--------- ---------- ---------
732       10 KING            5000
733       10 CLARK           2450
734       10 MILLER          1300
735       20 SCOTT           3000
736       20 FORD            3000
737       20 JONES           2975
738       20 ADAMS           1100
739       20 SMITH            800
740       30 BLAKE           2850
741       30 ALLEN           1600
742       30 TURNER          1500
743       30 WARD            1250
744       30 MARTIN          1250
745       30 JAMES            950