Oracle sql函数精解（二）

31.SIGH

返回双曲正弦的值

SQL> select sin(20),sinh(20) from dual;

SIN(20) SINH(20)

--------- ---------

.91294525 242582598

 

返回数字n的根

32.SQRT

SQL> select sqrt(64),sqrt(10) from dual;

SQRT(64) SQRT(10)

--------- ---------

8 3.1622777

 

33.TAN

返回数字的正切值

SQL> select tan(20),tan(10) from dual;

TAN(20) TAN(10)

--------- ---------

2.2371609 .64836083

 

34.TANH

返回数字n的双曲正切值

SQL> select tanh(20),tan(20) from dual;

TANH(20) TAN(20)

--------- ---------

1 2.2371609

 

35.TRUNC按照指定的精度截取一个数

SQL> select trunc(124.1666,-2)trunc1,trunc(124.16666,2) from dual;

TRUNC1 TRUNC(124.16666,2)

--------- ------------------

100 124.16

 

36.ADD_MONTHS增加或减去月份

SQL> selectto_char(add_months(to_date(199912,yyyymm),2),yyyymm) from dual;

TO_CHA

------

200002

SQL> selectto_char(add_months(to_date(199912,yyyymm),-2),yyyymm) from dual;

TO_CHA

------

199910

 

37.LAST_DAY返回日期的最后一天

SQL> selectto_char(sysdate,yyyy.mm.dd),to_char((sysdate)+1,yyyy.mm.dd) from dual;

TO_CHAR(SY TO_CHAR((S

---------- ----------

2004.05.09 2004.05.10

SQL> select last_day(sysdate) from dual;

LAST_DAY(S

----------

31-5月 -04

 

38.MONTHS_BETWEEN(date2,date1) 给出date2-date1的月份

SQL> select months_between(19-12月-1999,19-3月-1999)mon_between from dual;

MON_BETWEEN

-----------

9

SQL>selectmonths_between(to_date(2000.05.20,yyyy.mm.dd),to_date(2005.05.20,yyyy.mm.dd))mon_betw from dual;

MON_BETW

---------

-60

 

39.NEW_TIME(date,this,that)

给出在this时区=other时区的日期和时间

SQL> select to_char(sysdate,yyyy.mm.ddhh24:mi:ss) bj_time,to_char(new_time

2 (sysdate,PDT,GMT),yyyy.mm.dd hh24:mi:ss)los_angles from dual;

BJ_TIME LOS_ANGLES

------------------- -------------------

2004.05.09 11:05:32 2004.05.09 18:05:32

 

40.NEXT_DAY(date,day) 给出日期date和星期x之后计算下一个星期的日期

SQL> select next_day('18-5月-2001','星期五')next_day from dual;

NEXT_DAY

----------

25-5月 -01

 

41.SYSDATE用来得到系统的当前日期

SQL> select to_char(sysdate,dd-mm-yyyy day) from dual;

TO_CHAR(SYSDATE,

-----------------

09-05-2004 星期日

trunc(date,fmt)按照给出的要求将日期截断,如果fmt=mi表示保留分,截断秒

SQL> selectto_char(trunc(sysdate,hh),yyyy.mm.dd hh24:mi:ss) hh,

2 to_char(trunc(sysdate,mi),yyyy.mm.ddhh24:mi:ss) hhmm from dual;

HH HHMM

------------------- -------------------

2004.05.09 11:00:00 2004.05.09 11:17:00

 

42.CHARTOROWID

将字符数据类型转换为ROWID类型

SQL> selectrowid,rowidtochar(rowid),ename from scott.emp;

ROWID ROWIDTOCHAR(ROWID) ENAME

------------------ ----------------------------

AAAAfKAACAAAAEqAAA AAAAfKAACAAAAEqAAA SMITH

AAAAfKAACAAAAEqAAB AAAAfKAACAAAAEqAAB ALLEN

AAAAfKAACAAAAEqAAC AAAAfKAACAAAAEqAAC WARD

AAAAfKAACAAAAEqAAD AAAAfKAACAAAAEqAAD JONES

 

43.CONVERT(c,dset,sset)

将源字符串 sset从一个语言字符集转换到另一个目的dset字符集

SQL> select convert(strutz,we8hp,f7dec)"conversion" from dual;

conver

------

strutz

 

44.HEXTORAW

将一个十六进制构成的字符串转换为二进制

 

45.RAWTOHEXT

将一个二进制构成的字符串转换为十六进制

 

46.ROWIDTOCHAR

将ROWID数据类型转换为字符类型

 

47.TO_CHAR(date,format)

SQL> select to_char(sysdate,yyyy/mm/ddhh24:mi:ss) from dual;

TO_CHAR(SYSDATE,YY

-------------------

2004/05/09 21:14:41

48.TO_DATE(string,format)

将字符串转化为ORACLE中的一个日期

 

49.TO_MULTI_BYTE

将字符串中的单字节字符转化为多字节字符

SQL> select to_multi_byte(高) from dual;

TO

--

高

 

50.TO_NUMBER

将给出的字符转换为数字

SQL> select to_number(1999) year fromdual;

YEAR

---------

1999

51.BFILENAME(dir,file)

指定一个外部二进制文件

SQL>insert into file_tb1values(bfilename(lob_dir1,image1.gif));

 

52.CONVERT(x,desc,source)

将x字段或变量的源source转换为desc

SQL> selectsid,serial#,username,decode(command,

2 0,none,

3 2,insert,

4 3,

5 select,

6 6,update,

7 7,delete,

8 8,drop,

9 other) cmd from v$session wheretype!=background;

SID SERIAL# USERNAME CMD

--------- --------------------------------------- ------

1 1 none

2 1 none

3 1 none

4 1 none

5 1 none

6 1 none

7 1275 none

8 1275 none

9 20 GAO select

10 40 GAO none

 

53.DUMP(s,fmt,start,length)

DUMP函数以fmt指定的内部数字格式返回一个VARCHAR2类型的值

SQL> col global_name for a30

SQL> col dump_string for a50

SQL> set lin 200

SQL> selectglobal_name,dump(global_name,1017,8,5) dump_string from global_name;

GLOBAL_NAME DUMP_STRING

--------------------------------------------------------------------------------

ORACLE.WORLD Typ=1 Len=12CharacterSet=ZHS16GBK: W,O,R,L,D

 

54.EMPTY_BLOB()和EMPTY_CLOB()

这两个函数都是用来对大数据类型字段进行初始化操作的函数

 

55.GREATEST

返回一组表达式中的最大值,即比较字符的编码大小.

SQL> select greatest(AA,AB,AC) fromdual;

GR

--

AC

SQL> select greatest(啊,安,天) from dual;

GR

--

天

 

56.LEAST

返回一组表达式中的最小值

SQL> select least(啊,安,天) from dual;

LE

--

啊

 

57.UID

返回标识当前用户的唯一整数

SQL> show user

USER 为"GAO"

SQL> select username,user_id fromdba_users where user_id=uid;

USERNAME USER_ID

------------------------------ ---------

GAO 25

 

58.USER

返回当前用户的名字

SQL> select user from dual;

USER

------------------------------

GAO

 

59.USEREVN

返回当前用户环境的信息,opt可以是:

ENTRYID,SESSIONID,TERMINAL,ISDBA,LABLE,LANGUAGE,CLIENT_INFO,LANG,VSIZE

ISDBA 查看当前用户是否是DBA如果是则返回true

SQL> select userenv(isdba) from dual;

USEREN

------

FALSE

SQL> select userenv(isdba) from dual;

USEREN

------

TRUE

SESSION

返回会话标志

SQL> select userenv(sessionid) fromdual;

USERENV(SESSIONID)

--------------------

152

ENTRYID

返回会话人口标志

SQL> select userenv(entryid) from dual;

USERENV(ENTRYID)

------------------

0

INSTANCE

返回当前INSTANCE的标志

SQL> select userenv(instance) from dual;

USERENV(INSTANCE)

-------------------

1

LANGUAGE

返回当前环境变量

SQL> select userenv(language) from dual;

USERENV(LANGUAGE)

----------------------------------------------------

SIMPLIFIED CHINESE_CHINA.ZHS16GBK

LANG

返回当前环境的语言的缩写

SQL> select userenv(lang) from dual;

USERENV(LANG)

----------------------------------------------------

ZHS

TERMINAL

返回用户的终端或机器的标志

SQL> select userenv(terminal) from dual;

USERENV(TERMINA

----------------

GAO

VSIZE(X)

返回X的大小(字节)数

SQL> select vsize(user),user from dual;

VSIZE(USER) USER

----------- ------------------------------

6 SYSTEM

60.AVG(DISTINCT|ALL)

all表示对所有的值求平均值,distinct只对不同的值求平均值

SQLWKS> create table table3(xmvarchar(8),sal number(7,2));

语句已处理。

SQLWKS> insert into table3values(gao,1111.11);

SQLWKS> insert into table3values(gao,1111.11);

SQLWKS> insert into table3values(zhu,5555.55);

SQLWKS> commit;

SQL> select avg(distinct sal) fromgao.table3;

AVG(DISTINCTSAL)

----------------

3333.33

SQL> select avg(all sal) fromgao.table3;

AVG(ALLSAL)

-----------

2592.59

 

61.MAX(DISTINCT|ALL)

求最大值,ALL表示对所有的值求最大值,DISTINCT表示对不同的值求最大值,相同的只取一次

SQL> select max(distinct sal) fromscott.emp;

MAX(DISTINCTSAL)

----------------

5000

 

62.MIN(DISTINCT|ALL)

求最小值,ALL表示对所有的值求最小值,DISTINCT表示对不同的值求最小值,相同的只取一次

SQL> select min(all sal) fromgao.table3;

MIN(ALLSAL)

-----------

1111.11

 

63.STDDEV(distinct|all)

求标准差,ALL表示对所有的值求标准差,DISTINCT表示只对不同的值求标准差

SQL> select stddev(sal) from scott.emp;

STDDEV(SAL)

-----------

1182.5032

SQL> select stddev(distinct sal) fromscott.emp;

STDDEV(DISTINCTSAL)

-------------------

1229.951

 

64.VARIANCE(DISTINCT|ALL)

求协方差

SQL> select variance(sal) fromscott.emp;

VARIANCE(SAL)

-------------

1398313.9

 

65.GROUP BY

主要用来对一组数进行统计

SQL> select deptno,count(*),sum(sal)from scott.emp group by deptno;

DEPTNO COUNT(*) SUM(SAL)

--------- --------- ---------

10 3 8750

20 5 10875

30 6 9400