oracle sum 数据类型,五、Oracle内置函数

一.数值型函数

1.绝对值，取余，正负判断函数

绝对值：ABS(n),

取余：MOD(n2,n1),

正负判断：SIGN(n),

2.指数函数

平方根：SQRT(n)

幂函数：POWER(n1,n2)

对数：LOG(n1,n2)

3.四舍五入截取函数

四舍五入：ROUND(n,integer)

取真值，无关四舍五入：TRUNC(n,integer)

二.字符型函数

1.ASCII码与字符转换函数：ASCII转换成字符：CHR(n)字符转换成ASCII码：ASCII(char)2.获取字符串长度函数LENGTH(char)The LENGTH functionsreturn the length of char. LENGTH  calculates length usingcharacters as defined by the input character set.--返回以字符为单位的长度.LENGTHB usesbytes instead of characters.--返回以字节为单位的长度.LENGTHC usesUnicode complete characters.--返回以Unicode完全字符为单位的长度.LENGTH2 usesUCS2 code points.--返回以UCS2代码点为单位的长度.LENGTH4 usesUCS4 code points.--返回以UCS4代码点为单位的长度.3.字符串截取函数SUBSTR(char,position,substring_length)SUBSTR返回以字符为单位的长度.SUBSTRB返回以字节为单位的长度.SUBSTRC返回以Unicode完全字符为单位的长度.SUBSTR2返回以UCS2代码点为单位的长度.SUBSTR4返回以UCS4代码点为单位的长度.4.字符串连接函数CONCAT(char1,char2)5.字符串搜索函数INSTR(string,position,occurrence)6.字母大小写转换函数指定参数全部转换成大写字母：UPPER(char)指定参数全部转换成小写字母：LOWER(char)所有单词的首字母转化成大写字母：INITCAP(char)7.带排序参数的字母大小写转换函数NLS_INITCAP(char[,nlsparam]) 将指定参数的第一个字母转换成写。

nlsparam:

NLS_SORT = SCHINESE_STROKE_M按笔画，部首排序

NLS_SORT = SCHINESE_PINYIN_M按拼音排序

NLS_SORT =SCHINESE_RADICAL_M按照部首，笔划排8.为指定参数排序函数NLSSORT(char[,nlsparam])

9.替换字符串函数REPLACE(char,search_string[,replacement_string])10.字符串填充函数RPAD(expr1,n[,expr2])  右方向填充LPAD(expr1,n[,expr2])   左方向填充注：expr2总是从左到右填充。

11.删除字符串首届指定字符的函数TRIM([LEADING|TRAILING|BOTH][trim_character FROM] trim_source)leading:删除trim_source的前缀字符trailing:删除trim_source的后缀字符both:删除trim_source的前后缀字符trim_character:删除的指定字段，默认删除空格trim_source:被操作的字符串。例：select trim(leading 't' from 'test') from dual;RTRIM(char[,set])LTRIM(char[,set])例：select rtrim('lentimdgsdgfgs','dgfs*') from dual12.字符集名称和ID互换函数NLS_CHARSET_ID(sring)

NLS_CHARSET_NAME(number)

三、日期型函数

1.SYSDATE  系统时间函数

select to_char(sysdate,'YYYY-MM-DD HH24:MI:SS') from dual;

2.systimestamp带时区的系统时间，精确到微秒。

3.dbtimezone数据库时区

4.ADD_MONTHS(date,integer)为日期加上指定的月份函数。

Select to_char(add_months(to_date('2015-1-30','YYYY-MM-DD'),'1'),'YYYY-MM-DD');

5.LAST_DAY(date)返回指定月份最后一天函数

select last_day(to_char(add_months(sysdate,3))) from dualselect last_day(to_char(add_months(sysdate,3))) from dual;

6.NEST_DAY(date,char)返回指定日期后一周的日期函数

select sysdate,next_day(sysdate,'星期一') from dual

7.CURRENT_DATE返回会话所在时区当前日期函数

select sessiontimezone,to_char(current_date,'YYYY-MM-DD HH24:MI:SS') from dual;

8.extract(datetime)提取指定日期特定部分的函数

select  extract(year from sysdate) year,

extract (minute from timestamp ' 2015-05-04 23:15:23 ') min,

extract (second from timestamp ' 2015-05-12 23:15:23 ') sec

from dual;

9.MONTHS_BETWEEN(date1,date2)得到两个日期之间的份数

select months_between(to_date('2015-05-06', 'YYYY-MM-DD'), to_date('2015-06-06', 'YYYY-MM-DD')) one,

months_between(to_date('2015-05-31', 'YYYY-MM-DD'), to_date('2015-06-30', 'YYYY-MM-DD')) two,

months_between(to_date('2015-05-06','YYYY-MM-DD') ,to_date('2015-08-06','YYYY-MM-DD')) three

from dual

10.NEW_TIME(date,timezone1,timezone2) 时区时间转换函数

11.ROUND(date[,fmt]) 日期四舍五入

TRUNC(date[,fmt]) 日期截取函数

四、转换函数

1.ASCIISTR(char) 字符串转ASCII类型字符串函数

2.BIN_TO_NUM(data[,data...]) 二进制转十进制函数

3.CAST(expr as type_name) 数据类型转换函数

4.字符串和rowid互相转换函数

chartorowid(char)字符串转rowid

rowidtochar(rowid)rowid转字符串

rowidtonchar(rowid)rowid转成字符串，返回类型为nvarchar2。

5.convert(char,转变后的字符,转变前的字符)

6.十六进制字符串与RAW类型相互转换函数

hextoraw(char) 十六进制字符串转换成raw类型的数据

rawtohex(raw) raw类型字符转换成十六进制字符串，返回varchar2类型

rawtonhex(raw)raw类型字符转换成十六进制字符串，返回nvarchar2类型

7.数值转换成字符型函数

to_char(number[,fmt[,nlsparam]]) 数值转换成字符

to_char(date[,fmt[,nlsparam]]) 日期转换成字符

例： select to_char(44,'99.9') from dual;

select to_char(sysdate,'YYYY-MM-DD') ,

to_char(sysdate,'HH:MI:ss') ,

to_char(sysdate,'month','nls_date_language=English')

from dual;

8.to_date(char[,fmt[,nlsparam]]) 字符转日期型函数

例：select to_char(to_date('2015-05-06','YYYY-MM-DD'),'month') from dual;

9.to_number(expr[,fmt[,nlsparam]]) 字符串转数字函数

10.to_single_byte(char) 将函数从全角转成半角。

五、NULL函数

1.coalesce(expr) 返回列表中第一个不为null的函数

例：select coalesce(null,9-7,null,9);

2.lnnvl(condition)排除指定条件函数

例：select * from table_name where lnnvl(condition[as column_name>10]);

3.nvl(expr1,expr2) 表示如果expr1为null值时，则返回expr2的值，否则返回expr1的值。

nvl2(expr1,expr2,expr3) 同nvl类似，不同的是当expr1为null时，函数返回expr3的值，当expr1不为空时，返回expr2的值。

六、集合函数

1.avg([distinct|all]expr) 平均数函数

dustinct:去除重复的值

all:表示所有的值，包括重复的值，也是默认值。

expr:表达式。只能是数值类型。

2.count(*|[distinct][all]expr) 该函数用来计算记录的数量或某列的个数。

3.max([distinct|all]expr) 返回最大值函数。

min([distinct|all]expr) 返回最小值函数。

4.sum([distinct|all]expr) 求和函数

七、其他函数

1.user 返回登录名函数

例：select user from dual;

2.userenv(parameter) 返回当前会话的信息。

parameter:参数。(当参数为Language时返回语言，字符集;sessionid返回会话id;isdba返回当前用户是否dba)

例：select userenv('isdba') from dual;

3.sys_context(namespace,parameter) 上下文信息函数

例：selectSYS_CONTEXT('USERENV','TERMINAL') terminal,SYS_CONTEXT('USERENV','LANGUAGE') language,SYS_CONTEXT('USERENV','SESSIONID') sessionid,SYS_CONTEXT('USERENV','INSTANCE') instance,SYS_CONTEXT('USERENV','ENTRYID') entryid,SYS_CONTEXT('USERENV','ISDBA') isdba,SYS_CONTEXT('USERENV','NLS_TERRITORY') nls_territory,SYS_CONTEXT('USERENV','NLS_CURRENCY') nls_currency,SYS_CONTEXT('USERENV','NLS_CALENDAR') nls_calendar,SYS_CONTEXT('USERENV','NLS_DATE_FORMAT') nls_date_format,SYS_CONTEXT('USERENV','NLS_DATE_LANGUAGE') nls_date_language,SYS_CONTEXT('USERENV','NLS_SORT') nls_sort,SYS_CONTEXT('USERENV','CURRENT_USER') current_user,SYS_CONTEXT('USERENV','CURRENT_USERID') current_userid,SYS_CONTEXT('USERENV','SESSION_USER') session_user,SYS_CONTEXT('USERENV','SESSION_USERID') session_userid,SYS_CONTEXT('USERENV','PROXY_USER') proxy_user,SYS_CONTEXT('USERENV','PROXY_USERID') proxy_userid,SYS_CONTEXT('USERENV','DB_DOMAIN') db_domain,SYS_CONTEXT('USERENV','DB_NAME') db_name,SYS_CONTEXT('USERENV','HOST') host,SYS_CONTEXT('USERENV','OS_USER') os_user,SYS_CONTEXT('USERENV','EXTERNAL_NAME') external_name,SYS_CONTEXT('USERENV','IP_ADDRESS') ip_address,SYS_CONTEXT('USERENV','NETWORK_PROTOCOL') network_protocol,SYS_CONTEXT('USERENV','BG_JOB_ID') bg_job_id,SYS_CONTEXT('USERENV','FG_JOB_ID') fg_job_id,SYS_CONTEXT('USERENV','AUTHENTICATION_TYPE')  authentication_type,SYS_CONTEXT('USERENV','AUTHENTICATION_DATA') authentication_datafrom dual;

4.decode(条件,值1,返回值1,值2,返回值2,...值n,返回值n,缺省值) 表达式匹配函数

例：比如我要查询某班男生和女生的数量分别是多少?

通常我们这么写:

select count(*) from 表 where 性别 ＝ 男；

select count(*) from 表 where 性别 ＝ 女；

要想显示到一起还要union一下，太麻烦了

用decode呢，只需要一句话

select decode(性别，男，1，0)，decode(性别，女，1，0) from 表

使用方法：

1、比较大小

select decode(sign(变量1-变量2),-1,变量1,变量2) from dual; --取较小值

sign()函数根据某个值是0、正数还是负数，分别返回0、1、-1

例如：

变量1=10，变量2=20

则sign(变量1-变量2)返回-1，decode解码结果为“变量1”，达到了取较小值的目的。

2、此函数用在SQL语句中，功能介绍如下：

Decode函数与一系列嵌套的 IF-THEN-ELSE语句相似。base_exp与compare1,compare2等等依次进行比较。如果base_exp和 第i 个compare项匹配，就返回第i 个对应的value 。如果base_exp与任何的compare值都不匹配，则返回default。每个compare值顺次求值，如果发现一个匹配，则剩下的compare值(如果还有的话)就都不再求值。一个为NULL的base_exp被认为和NULL compare值等价。如果需要的话，每一个compare值都被转换成和第一个compare 值相同的数据类型，这个数据类型也是返回值的类型。

Decode函数在实际开发中非常的有用

结合Lpad函数，如何使主键的值自动加1并在前面补0

select LPAD(decode(count(记录编号),0,1,max(to_number(记录编号)+1)),14,'0') 记录编号 from tetdmis

eg:

select decode(dir,1,0,1) from a1_interval

dir 的值是1变为0，是0则变为1

比如我要查询某班男生和女生的数量分别是多少?

通常我们这么写:

select count(*) from 表 where 性别 ＝ 男；

select count(*) from 表 where 性别 ＝ 女；

要想显示到一起还要union一下，太麻烦了

用decode呢，只需要一句话

select decode(性别，男，1，0)，decode(性别，女，1，0) from 表

3，order by对字符列进行特定的排序

大家还可以在Order by中使用Decode。

例：表table_subject，有subject_name列。要求按照：语、数、外的顺序进行排序。这时，就可以非常轻松的使用Decode完成要求了。

select * from table_subject order by decode(subject_name, '语文', 1, '数学', 2, , '外语',3)