选择自 AppleBBS 的 Blog
1
SQL中的单记录函数
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
1
SQL中的单记录函数2
1.ASCII3
返回与指定的字符对应的十进制数;4
SQL> select ascii('A') A,ascii('a') a,ascii('0') zero,ascii(' ') space from dual;5
6
A A ZERO SPACE7
--------- --------- --------- ---------8
65 97 48 329
10
11
2.CHR12
给出整数,返回对应的字符;13
SQL> select chr(54740) zhao,chr(65) chr65 from dual;14
15
ZH C16
-- -17
赵 A18
19
3.CONCAT20
连接两个字符串;21
SQL> select concat('010-','88888888')||'转23' 高乾竞电话 from dual;22
23
高乾竞电话24
----------------25
010-88888888转2326
27
4.INITCAP28
返回字符串并将字符串的第一个字母变为大写;29
SQL> select initcap('smith') upp from dual;30
31
UPP32
-----33
Smith34
35
36
5.INSTR(C1,C2,I,J)37
在一个字符串中搜索指定的字符,返回发现指定的字符的位置;38
C1 被搜索的字符串39
C2 希望搜索的字符串40
I 搜索的开始位置,默认为141
J 出现的位置,默认为142
SQL> select instr('oracle traning','ra',1,2) instring from dual;43
44
INSTRING45
---------46
947
48
49
6.LENGTH50
返回字符串的长度;51
SQL> select name,length(name),addr,length(addr),sal,length(to_char(sal)) from gao.nchar_tst;52
53
NAME LENGTH(NAME) ADDR LENGTH(ADDR) SAL LENGTH(TO_CHAR(SAL))54
------ ------------ ---------------- ------------ --------- --------------------55
高乾竞 3 北京市海锭区 6 9999.99 756
57
58
59
7.LOWER60
返回字符串,并将所有的字符小写61
SQL> select lower('AaBbCcDd')AaBbCcDd from dual;62
63
AABBCCDD64
--------65
aabbccdd66
67
68
8.UPPER69
返回字符串,并将所有的字符大写70
SQL> select upper('AaBbCcDd') upper from dual;71
72
UPPER73
--------74
AABBCCDD75
76
77
78
9.RPAD和LPAD(粘贴字符)79
RPAD 在列的右边粘贴字符80
LPAD 在列的左边粘贴字符81
SQL> select lpad(rpad('gao',10,'*'),17,'*')from dual;82
83
LPAD(RPAD('GAO',184
-----------------85
*******gao*******86
不够字符则用*来填满87
88
89
10.LTRIM和RTRIM90
LTRIM 删除左边出现的字符串91
RTRIM 删除右边出现的字符串92
SQL> select ltrim(rtrim(' gao qian jing ',' '),' ') from dual;93
94
LTRIM(RTRIM('95
-------------96
gao qian jing97
98
99
11.SUBSTR(string,start,count)100
取子字符串,从start开始,取count个101
SQL> select substr('13088888888',3,8) from dual;102
103
SUBSTR('104
--------105
08888888106
107
108
12.REPLACE('string','s1','s2')109
string 希望被替换的字符或变量 110
s1 被替换的字符串111
s2 要替换的字符串112
SQL> select replace('he love you','he','i') from dual;113
114
REPLACE('H115
----------116
i love you117
118
119
13.SOUNDEX120
返回一个与给定的字符串读音相同的字符串121
SQL> create table table1(xm varchar(8));122
SQL> insert into table1 values('weather');123
SQL> insert into table1 values('wether');124
SQL> insert into table1 values('gao');125
126
SQL> select xm from table1 where soundex(xm)=soundex('weather');127
128
XM129
--------130
weather131
wether132
133
134
14.TRIM('s' from 'string')135
LEADING 剪掉前面的字符136
TRAILING 剪掉后面的字符137
如果不指定,默认为空格符 138
139
15.ABS140
返回指定值的绝对值141
SQL> select abs(100),abs(-100) from dual;142
143
ABS(100) ABS(-100)144
--------- ---------145
100 100146
147
148
16.ACOS149
给出反余弦的值150
SQL> select acos(-1) from dual;151
152
ACOS(-1)153
---------154
3.1415927155
156
157
17.ASIN158
给出反正弦的值159
SQL> select asin(0.5) from dual;160
161
ASIN(0.5)162
---------163
.52359878164
165
166
18.ATAN167
返回一个数字的反正切值168
SQL> select atan(1) from dual;169
170
ATAN(1)171
---------172
.78539816173
174
175
19.CEIL176
返回大于或等于给出数字的最小整数177
SQL> select ceil(3.1415927) from dual;178
179
CEIL(3.1415927)180
---------------181
4182
183
184
20.COS185
返回一个给定数字的余弦186
SQL> select cos(-3.1415927) from dual;187
188
COS(-3.1415927)189
---------------190
-1191
192
193
21.COSH194
返回一个数字反余弦值195
SQL> select cosh(20) from dual;196
197
COSH(20)198
---------199
242582598200
201
202
22.EXP203
返回一个数字e的n次方根204
SQL> select exp(2),exp(1) from dual;205
206
EXP(2) EXP(1)207
--------- ---------208
7.3890561 2.7182818209
210
211
23.FLOOR212
对给定的数字取整数213
SQL> select floor(2345.67) from dual;214
215
FLOOR(2345.67)216
--------------217
2345218
219
220
24.LN221
返回一个数字的对数值222
SQL> select ln(1),ln(2),ln(2.7182818) from dual;223
224
LN(1) LN(2) LN(2.7182818)225
--------- --------- -------------226
0 .69314718 .99999999227
228
229
25.LOG(n1,n2)230
返回一个以n1为底n2的对数 231
SQL> select log(2,1),log(2,4) from dual;232
233
LOG(2,1) LOG(2,4)234
--------- ---------235
0 2236
237
238
26.MOD(n1,n2)239
返回一个n1除以n2的余数240
SQL> select mod(10,3),mod(3,3),mod(2,3) from dual;241
242
MOD(10,3) MOD(3,3) MOD(2,3)243
--------- --------- ---------244
1 0 2245
246
247
27.POWER248
返回n1的n2次方根249
SQL> select power(2,10),power(3,3) from dual;250
251
POWER(2,10) POWER(3,3)252
----------- ----------253
1024 27254
255
256
28.ROUND和TRUNC257
按照指定的精度进行舍入258
SQL> select round(55.5),round(-55.4),trunc(55.5),trunc(-55.5) from dual;259
260
ROUND(55.5) ROUND(-55.4) TRUNC(55.5) TRUNC(-55.5)261
----------- ------------ ----------- ------------262
56 -55 55 -55263
264
265
29.SIGN266
取数字n的符号,大于0返回1,小于0返回-1,等于0返回0267
SQL> select sign(123),sign(-100),sign(0) from dual;268
269
SIGN(123) SIGN(-100) SIGN(0)270
--------- ---------- ---------271
1 -1 0272
273
274
30.SIN275
返回一个数字的正弦值276
SQL> select sin(1.57079) from dual;277
278
SIN(1.57079)279
------------280
1281
282
283
31.SIGH284
返回双曲正弦的值285
SQL> select sin(20),sinh(20) from dual;286
287
SIN(20) SINH(20)288
--------- ---------289
.91294525 242582598290
291
292
32.SQRT293
返回数字n的根294
SQL> select sqrt(64),sqrt(10) from dual;295
296
SQRT(64) SQRT(10)297
--------- ---------298
8 3.1622777299
300
301
33.TAN302
返回数字的正切值303
SQL> select tan(20),tan(10) from dual;304
305
TAN(20) TAN(10)306
--------- ---------307
2.2371609 .64836083308
309
310
34.TANH311
返回数字n的双曲正切值312
SQL> select tanh(20),tan(20) from dual;313
314
TANH(20) TAN(20)315
--------- ---------316
1 2.2371609317
318
319
320
35.TRUNC321
按照指定的精度截取一个数322
SQL> select trunc(124.1666,-2) trunc1,trunc(124.16666,2) from dual;323
324
TRUNC1 TRUNC(124.16666,2)325
--------- ------------------326
100 124.16327
328
329
330
36.ADD_MONTHS331
增加或减去月份332
SQL> select to_char(add_months(to_date('199912','yyyymm'),2),'yyyymm') from dual;333
334
TO_CHA335
------336
200002337
SQL> select to_char(add_months(to_date('199912','yyyymm'),-2),'yyyymm') from dual;338
339
TO_CHA340
------341
199910342
343
344
37.LAST_DAY345
返回日期的最后一天346
SQL> select to_char(sysdate,'yyyy.mm.dd'),to_char((sysdate)+1,'yyyy.mm.dd') from dual;347
348
TO_CHAR(SY TO_CHAR((S349
---------- ----------350
2004.05.09 2004.05.10351
SQL> select last_day(sysdate) from dual;352
353
LAST_DAY(S354
----------355
31-5月 -04356
357
358
38.MONTHS_BETWEEN(date2,date1)359
给出date2-date1的月份360
SQL> select months_between('19-12月-1999','19-3月-1999') mon_between from dual;361
362
MON_BETWEEN363
-----------364
9365
SQL>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_BETW368
---------369
-60370
371
372
39.NEW_TIME(date,'this','that')373
给出在this时区=other时区的日期和时间374
SQL> select to_char(sysdate,'yyyy.mm.dd hh24:mi:ss') bj_time,to_char(new_time375
2 (sysdate,'PDT','GMT'),'yyyy.mm.dd hh24:mi:ss') los_angles from dual;376
377
BJ_TIME LOS_ANGLES378
------------------- -------------------379
2004.05.09 11:05:32 2004.05.09 18:05:32380
381
382
40.NEXT_DAY(date,'day')383
给出日期date和星期x之后计算下一个星期的日期384
SQL> select next_day('18-5月-2001','星期五') next_day from dual;385
386
NEXT_DAY387
----------388
25-5月 -01389
390
391
392
41.SYSDATE393
用来得到系统的当前日期394
SQL> select to_char(sysdate,'dd-mm-yyyy day') from dual;395
396
TO_CHAR(SYSDATE,'397
-----------------398
09-05-2004 星期日399
trunc(date,fmt)按照给出的要求将日期截断,如果fmt='mi'表示保留分,截断秒400
SQL> 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
403
HH HHMM404
------------------- -------------------405
2004.05.09 11:00:00 2004.05.09 11:17:00406
407
408
409
42.CHARTOROWID410
将字符数据类型转换为ROWID类型411
SQL> select rowid,rowidtochar(rowid),ename from scott.emp;412
413
ROWID ROWIDTOCHAR(ROWID) ENAME414
------------------ ------------------ ----------415
AAAAfKAACAAAAEqAAA AAAAfKAACAAAAEqAAA SMITH416
AAAAfKAACAAAAEqAAB AAAAfKAACAAAAEqAAB ALLEN417
AAAAfKAACAAAAEqAAC AAAAfKAACAAAAEqAAC WARD418
AAAAfKAACAAAAEqAAD AAAAfKAACAAAAEqAAD JONES419
420
421
43.CONVERT(c,dset,sset)422
将源字符串 sset从一个语言字符集转换到另一个目的dset字符集423
SQL> select convert('strutz','we8hp','f7dec') "conversion" from dual;424
425
conver426
------427
strutz428
429
430
44.HEXTORAW431
将一个十六进制构成的字符串转换为二进制432
433
434
45.RAWTOHEXT435
将一个二进制构成的字符串转换为十六进制436
437
438
439
46.ROWIDTOCHAR440
将ROWID数据类型转换为字符类型441
442
443
444
47.TO_CHAR(date,'format')445
SQL> select to_char(sysdate,'yyyy/mm/dd hh24:mi:ss') from dual;446
447
TO_CHAR(SYSDATE,'YY448
-------------------449
2004/05/09 21:14:41450
451
452
453
48.TO_DATE(string,'format')454
将字符串转化为ORACLE中的一个日期455
456
457
49.TO_MULTI_BYTE458
将字符串中的单字节字符转化为多字节字符459
SQL> select to_multi_byte('高') from dual;460
461
TO462
--463
高464
465
466
50.TO_NUMBER467
将给出的字符转换为数字468
SQL> select to_number('1999') year from dual;469
470
YEAR471
---------472
1999473
474
475
51.BFILENAME(dir,file)476
指定一个外部二进制文件477
SQL>insert into file_tb1 values(bfilename('lob_dir1','image1.gif'));478
479
480
52.CONVERT('x','desc','source')481
将x字段或变量的源source转换为desc482
SQL> 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 CMD493
--------- --------- ------------------------------ ------494
1 1 none495
2 1 none496
3 1 none497
4 1 none498
5 1 none499
6 1 none500
7 1275 none501
8 1275 none502
9 20 GAO select503
10 40 GAO none504
505
506
53.DUMP(s,fmt,start,length)507
DUMP函数以fmt指定的内部数字格式返回一个VARCHAR2类型的值508
SQL> col global_name for a30509
SQL> col dump_string for a50510
SQL> set lin 200511
SQL> select global_name,dump(global_name,1017,8,5) dump_string from global_name;512
513
GLOBAL_NAME DUMP_STRING514
------------------------------ --------------------------------------------------515
ORACLE.WORLD Typ=1 Len=12 CharacterSet=ZHS16GBK: W,O,R,L,D516
517
518
54.EMPTY_BLOB()和EMPTY_CLOB()519
这两个函数都是用来对大数据类型字段进行初始化操作的函数520
521
522
55.GREATEST523
返回一组表达式中的最大值,即比较字符的编码大小.524
SQL> select greatest('AA','AB','AC') from dual;525
526
GR527
--528
AC529
SQL> select greatest('啊','安','天') from dual;530
531
GR532
--533
天534
535
536
56.LEAST537
返回一组表达式中的最小值 538
SQL> select least('啊','安','天') from dual;539
540
LE541
--542
啊543
544
545
57.UID546
返回标识当前用户的唯一整数547
SQL> show user548
USER 为"GAO"549
SQL> select username,user_id from dba_users where user_id=uid;550
551
USERNAME USER_ID552
------------------------------ ---------553
GAO 25554
555
556
557
58.USER558
返回当前用户的名字559
SQL> select user from dual;560
561
USER562
------------------------------563
GAO564
565
566
59.USEREVN567
返回当前用户环境的信息,opt可以是:568
ENTRYID,SESSIONID,TERMINAL,ISDBA,LABLE,LANGUAGE,CLIENT_INFO,LANG,VSIZE569
ISDBA 查看当前用户是否是DBA如果是则返回true570
SQL> select userenv('isdba') from dual;571
572
USEREN573
------574
FALSE575
SQL> select userenv('isdba') from dual;576
577
USEREN578
------579
TRUE580
SESSION581
返回会话标志582
SQL> select userenv('sessionid') from dual;583
584
USERENV('SESSIONID')585
--------------------586
152587
ENTRYID588
返回会话人口标志589
SQL> select userenv('entryid') from dual;590
591
USERENV('ENTRYID')592
------------------593
0594
INSTANCE595
返回当前INSTANCE的标志596
SQL> select userenv('instance') from dual;597
598
USERENV('INSTANCE')599
-------------------600
1601
LANGUAGE602
返回当前环境变量603
SQL> select userenv('language') from dual;604
605
USERENV('LANGUAGE')606
----------------------------------------------------607
SIMPLIFIED CHINESE_CHINA.ZHS16GBK608
LANG609
返回当前环境的语言的缩写610
SQL> select userenv('lang') from dual;611
612
USERENV('LANG')613
----------------------------------------------------614
ZHS615
TERMINAL616
返回用户的终端或机器的标志617
SQL> select userenv('terminal') from dual;618
619
USERENV('TERMINA620
----------------621
GAO622
VSIZE(X)623
返回X的大小(字节)数624
SQL> select vsize(user),user from dual;625
626
VSIZE(USER) USER627
----------- ------------------------------628
6 SYSTEM629
630
631
632
60.AVG(DISTINCT|ALL)633
all表示对所有的值求平均值,distinct只对不同的值求平均值634
SQLWKS> create table table3(xm varchar(8),sal number(7,2));635
语句已处理。636
SQLWKS> insert into table3 values('gao',1111.11);637
SQLWKS> insert into table3 values('gao',1111.11);638
SQLWKS> insert into table3 values('zhu',5555.55);639
SQLWKS> commit;640
641
SQL> select avg(distinct sal) from gao.table3;642
643
AVG(DISTINCTSAL)644
----------------645
3333.33646
647
SQL> select avg(all sal) from gao.table3;648
649
AVG(ALLSAL)650
-----------651
2592.59652
653
654
61.MAX(DISTINCT|ALL)655
求最大值,ALL表示对所有的值求最大值,DISTINCT表示对不同的值求最大值,相同的只取一次656
SQL> select max(distinct sal) from scott.emp;657
658
MAX(DISTINCTSAL)659
----------------660
5000661
662
663
62.MIN(DISTINCT|ALL)664
求最小值,ALL表示对所有的值求最小值,DISTINCT表示对不同的值求最小值,相同的只取一次665
SQL> select min(all sal) from gao.table3;666
667
MIN(ALLSAL)668
-----------669
1111.11670
671
672
63.STDDEV(distinct|all)673
求标准差,ALL表示对所有的值求标准差,DISTINCT表示只对不同的值求标准差674
SQL> select stddev(sal) from scott.emp;675
676
STDDEV(SAL)677
-----------678
1182.5032679
680
SQL> select stddev(distinct sal) from scott.emp;681
682
STDDEV(DISTINCTSAL)683
-------------------684
1229.951685
686
687
688
64.VARIANCE(DISTINCT|ALL)689
求协方差 690
691
SQL> select variance(sal) from scott.emp;692
693
VARIANCE(SAL)694
-------------695
1398313.9696
697
698
65.GROUP BY699
主要用来对一组数进行统计700
SQL> select deptno,count(*),sum(sal) from scott.emp group by deptno;701
702
DEPTNO COUNT(*) SUM(SAL)703
--------- --------- ---------704
10 3 8750705
20 5 10875706
30 6 9400707
708
709
710
66.HAVING711
对分组统计再加限制条件712
SQL> select deptno,count(*),sum(sal) from scott.emp group by deptno having count(*)>=5;713
714
DEPTNO COUNT(*) SUM(SAL)715
--------- --------- ---------716
20 5 10875717
30 6 9400718
SQL> select deptno,count(*),sum(sal) from scott.emp having count(*)>=5 group by deptno ;719
720
DEPTNO COUNT(*) SUM(SAL)721
--------- --------- ---------722
20 5 10875723
30 6 9400724
725
726
67.ORDER BY727
用于对查询到的结果进行排序输出728
SQL> select deptno,ename,sal from scott.emp order by deptno,sal desc;729
730
DEPTNO ENAME SAL731
--------- ---------- ---------732
10 KING 5000733
10 CLARK 2450734
10 MILLER 1300735
20 SCOTT 3000736
20 FORD 3000737
20 JONES 2975738
20 ADAMS 1100739
20 SMITH 800740
30 BLAKE 2850741
30 ALLEN 1600742
30 TURNER 1500743
30 WARD 1250744
30 MARTIN 1250745
30 JAMES 950
