oracle常用函数.docx
- 文档编号:7937179
- 上传时间:2023-01-27
- 格式:DOCX
- 页数:12
- 大小:21.53KB
oracle常用函数.docx
《oracle常用函数.docx》由会员分享,可在线阅读,更多相关《oracle常用函数.docx(12页珍藏版)》请在冰豆网上搜索。
oracle常用函数
Oracle常用SQL函数
单行函数包括,字符、数字、日期、转换和普通函数。
字符函数举例:
•全小写 LOWER('SQLCourse') sqlcourse
•全大写UPPER('SQLCourse') SQLCOURSE
•首字母大写INITCAP('SQLCourse') SqlCourse
拼接CONCAT('Good','String') GoodString
取子串 SUBSTR('String',1,3) Str
求长度 LENGTH('String') 6
数字函数举例:
ROUND--对数值进行四舍五入操作
TRUNC--截断。
注意截断和舍入的位数,可以是负数。
ROUND(45.923,2) 45.92
ROUND(45.923,0) 46
ROUND(45.923,-1) 50
TRUNC(45.923,2) 45.92
TRUNC(45.923) 45
TRUNC(45.923,-1) 40
MOD--返回两数相除的余数
MOD(1600,300)--100
日期函数:
•Oracle将日期按照内部格式将日期存成以下七个字节-世纪、年、月、日、时、分、秒。
•在Oracle中日期缺省的显示格式为:
DD-MON-YY。
•可以使用系统变量 SYSDATE获取系统时间•。
查看SYSDATE时,可以使用虚拟表DUAL。
举例:
求两个日期间有多少个月MONTHS_BETWEEN('01-SEP-95','11-JAN-94')1.9774194
在日期上加指定的月数ADD_MONTHS('11-JAN-94',6)'11-JUL-94'
下一个星期几是什么时候NEXT_DAY('01-SEP-95','FRIDAY')'08-SEP-95'
某月最后一天LAST_DAY('01-SEP-95')'30-SEP-95'
把日期四舍五入到月份ROUND('25-MAY-95','MONTH') 01-JUN-95
ROUND('25-MAY-95','YEAR') 01-JAN-95
把日期截断到月份 TRUNC('25-MAY-95','MONTH')01-MAY-95
TRUNC('25-MAY-95','YEAR') 01-JAN-95
转换函数
TO_CHAR将数字或日期转换为字符串
TO_NUMBER将字符串转换成数字
TO_DATE将字符串转换成日期
在转换函数中会使用格式串
TO_CHAR(date,'fmt')
格式串包含在''中,大小写敏感,可以是任意有效的日期
格式
格式串:
YYYY表示完整的四位数字年
YEAR表示英文拼写的年
MM表示两位数字月
MONTH表示英文全拼的年
DY表示三位缩写的星期
DAY表示英文全拼的星期
一些特殊用法:
时间格式
HH24:
MI:
SSAM 15:
45:
32PM
在格式串中加入字符串
DD"of"MONTH 12ofOCTOBER
用后缀拼出整个日期
ddspth fourteenth
TO_CHAR(number,'fmt')
使用此函数将数字转换成数字
9 表示数字
0 强制为0
$ 设置美元符号
L 使用当前字符集的货币符号
. 小数点
, 千位分隔符
可以使用TO_NUMBER将字符串转换成数字
可以使用TO_DATE将字符串转换成格式日期
TO_DATE('10September1992','ddMonthYYYY')
使用格式串
TO_DATE(char [,'fmt'])
普通函数
NULL值处理函数
NVL(expr1,expr2)
如果expr1为NULL,返回expr2
NVL2(expr1,expr2,expr3)
如果expr1为非空,则返回expr2,如果expr1为NULL,则返回expr3
NULLIF(expr1,expr2)
如果expr1=expr2,返回NULL,否则返回expr1
COALESCE(expr1,expr2,...,exprn)
返回表达式列表中的第一个非空表达式
条件表达式,用case表达式或decode实现条件逻辑
CASE表达式
CASEexprWHENcomparison_expr1THENreturn_expr1
[WHENcomparison_expr2THENreturn_expr2
WHENcomparison_exprnTHENreturn_exprn
ELSEelse_expr]
END
decode函数
DECODE(col|expression,search1,result1
[,search2,result2,...,]
[,default])
分组函数:
AVG(DISTINCT|ALL|n)
COUNT(DISTINCT|ALL|expr|*)
MAX(DISTINCT|ALL|expr)
MIN(DISTINCT|ALL|expr)
STDDEV(DISTINCT|ALL|n)
SUM(DISTINCT|ALL|n)
VARIANCE(DISTINCT|ALL|n)
常用SQL字符串函数问题集锦
出处:
开店乐电子商务研究KaiDianLe.Com作者:
开店乐电…更新时间:
2007-12-817:
57:
52
问:
请教一个SQL 2000 SEVER问题:
select*fromitemcode
wherecodelike'40%'
如何让code=40101001
变成code=401-01-001
目前有666个CODE是类似40101001
用什么语句能把它变成401-01-001
答:
updateitemcodesetcode=replace(code,left(code,8),left(code,3)+'-'+substring(code,4,2)+'-'+substring(code,6,3))wherecodelike'40%'
updateitemcodesetcode=replace(code,left(code,8),left(code,3)||'-'||substr(code,4,2)||'-'||substr(code,6,3))wherecodelike'40%'(PL/SQL语法)
问:
replace(code,left(code,8),left(code,3)+'-'+substring(code,4,2)+'-'+substring(code,6,3))是什么意思?
答:
拿code=123456789做例子
a=left(code,8)=12345678
b=left(code,3)=123
c=substring(code,4,2)=45
d=substring(code,6,3)=678
e=b+'-'+c+'-'+e=123-45-678
f=replace(code,a,e)=123-45-6789
配合下边的来看,应该能明白了
1、left(code,8)取前8位
2、substring(code,4,2)从第4位开始取2位
3、+是字符串连接符
4、replace(a,str1,str2)将a中的str1替换为str2
几问几答下来,我就想着要整理一份完整的SQL字符串函数出来,借以学习和方便以后查询。
SQL字符串函数
字符串函数对二进制数据、字符串和表达式执行不同的运算。
此类函数作用于CHAR、VARCHAR、BINARY、和VARBINARY数据类型以及可以隐式转换为CHAR或VARCHAR的数据类型。
可以在SELECT语句的SELECT和WHERE子句以及表达式中使用字符串函数。
常用的字符串函数有:
一、字符转换函数
1、ASCII()
返回字符表达式最左端字符的ASCII码值。
在ASCII()函数中,纯数字的字符串可不用‘’括起来,但含其它字符的字符串必须用‘’括起来使用,否则会出错。
2、CHAR()
将ASCII码转换为字符。
如果没有输入0~255之间的ASCII码值,CHAR()返回NULL。
3、LOWER()和UPPER()
LOWER()将字符串全部转为小写;UPPER()将字符串全部转为大写。
4、STR()
把数值型数据转换为字符型数据。
STR(
length指定返回的字符串的长度,decimal指定返回的小数位数。
如果没有指定长度,缺省的length值为10,decimal缺省值为0。
当length或者decimal为负值时,返回NULL;
当length小于小数点左边(包括符号位)的位数时,返回length个*;
先服从length,再取decimal;
当返回的字符串位数小于length,左边补足空格。
二、去空格函数
1、LTRIM()把字符串头部的空格去掉。
2、RTRIM()把字符串尾部的空格去掉。
三、取子串函数
1、left()
LEFT(
返回character_expression左起integer_expression个字符。
2、RIGHT()
RIGHT(
返回character_expression右起integer_expression个字符。
3、SUBSTRING()
SUBSTRING(
返回从字符串左边第starting_position个字符起length个字符的部分。
四、字符串比较函数
1、CHARINDEX()
返回字符串中某个指定的子串出现的开始位置。
CHARINDEX(<’substring_expression’>,
其中substring_expression是所要查找的字符表达式,expression可为字符串也可为列名表达式。
如果没有发现子串,则返回0值。
此函数不能用于TEXT和IMAGE数据类型。
2、PATINDEX()
返回字符串中某个指定的子串出现的开始位置。
PATINDEX(<’%substring_expression%’>,
与CHARINDEX函数不同的是,PATINDEX函数的子串中可以使用通配符,且此函数可用于CHAR、VARCHAR和TEXT数据类型。
五、字符串操作函数
1、QUOTENAME()
返回被特定字符括起来的字符串。
QUOTENAME(<’character_expression’>[,quote_character])其中quote_character标明括字符串所用的字符,缺省值为“[]”。
2、REPLICATE()
返回一个重复character_expression指定次数的字符串。
REPLICATE(character_expressioninteger_expression)如果integer_expression值为负值,则返回NULL。
3、REVERSE()
将指定的字符串的字符排列顺序颠倒。
REVERSE(
4、REPLACE()
返回被替换了指定子串的字符串。
REPLACE(
4、SPACE()
返回一个有指定长度的空白字符串。
SPACE(
5、STUFF()
用另一子串替换字符串指定位置、长度的子串。
STUFF(
如果起始位置为负或长度值为负,或者起始位置大于character_expression1的长度,则返回NULL值。
如果length长度大于character_expression1中start_position以右的长度,则character_expression1只保留首字符。
六、数据类型转换函数
1、CAST()
CAST(
2、CONVERT()
CONVERT(
1)data_type为SQLServer系统定义的数据类型,用户自定义的数据类型不能在此使用。
2)length用于指定数据的长度,缺省值为30。
3)把CHAR或VARCHAR类型转换为诸如INT或SAMLLINT这样的INTEGER类型、结果必须是带正号或负号的数值。
4)TEXT类型到CHAR或VARCHAR类型转换最多为8000个字符,即CHAR或VARCHAR数据类型是最大长度。
5)IMAGE类型存储的数据转换到BINARY或VARBINARY类型,最多为8000个字符。
6)把整数值转换为MONEY或SMALLMONEY类型,按定义的国家的货币单位来处理,如人民币、美元、英镑等。
7)BIT类型的转换把非零值转换为1,并仍以BIT类型存储。
8)试图转换到不同长度的数据类型,会截短转换值并在转换值后显示“+”,以标识发生了这种截断。
9)用CONVERT()函数的style选项能以不同的格式显示日期和时间。
style是将DATATIME和SMALLDATETIME数据转换为字符串时所选用的由SQLServer系统提供的转换样式编号,不同的样式编号有不同的输出格式。
七、日期函数
1、day(date_expression)
返回date_expression中的日期值
2、month(date_expression)
返回date_expression中的月份值
3、year(date_expression)
返回date_expression中的年份值
4、DATEADD()
DATEADD(
返回指定日期date加上指定的额外日期间隔number产生的新日期。
参数“datepart”取值如下:
5、DATEDIFF()
DATEDIFF(
返回两个指定日期在datepart方面的不同之处,即date2超过date1的差距值,其结果值是一个带有正负号的整数值。
6、DATENAME()
DATENAME(
以字符串的形式返回日期的指定部分此部分。
由datepart来指定。
7、DATEPART()
DATEPART(
以整数值的形式返回日期的指定部分。
此部分由datepart来指定。
DATEPART(dd,date)等同于DAY(date)
DATEPART(mm,date)等同于MONTH(date)
DATEPART(yy,date)等同于YEAR(date)
8、GETDATE()
以DATETIME的缺省格式返回系统当前的日期和时间
REGEXP_REPLACE
selectregexp_replace('123,232,4343,32423,3252,553,23423,5435,2354,234,54535',
'(\d+,\d+,\d+),',
'\1#')
fromdual;
Oracle中Decode()函数使用技巧
文章分类:
数据库
DECODE函数是ORACLEPL/SQL是功能强大的函数之一,目前还只有ORACLE公司的SQL提供了此函数,其他数据库厂商的SQL实现还没有此功能。
DECODE有什么用途呢?
先构造一个例子,假设我们想给智星职员加工资,其标准是:
工资在8000元以下的将加20%;工资在8000元以上的加15%,通常的做法是,先选出记录中的工资字段值?
selectsalaryintovar-salaryfromemployee,然后对变量var-salary用if-then-else或choosecase之类的流控制语句进行判断。
如果用DECODE函数,那么我们就可以把这些流控制语句省略,通过SQL语句就可以直接完成。
如下:
selectdecode(sign(salary-8000),1,salary*1.15,-1,salary*1.2,salaryfromemployee是不是很简洁?
DECODE的语法:
DECODE(value,if1,then1,if2,then2,if3,then3,...,else),表示如果value等于if1时,DECODE函数的结果返回then1,...,如果不等于任何一个if值,则返回else。
初看一下,DECODE只能做等于测试,但刚才也看到了,我们通过一些函数或计算替代value,是可以使DECODE函数具备大于、小于或等于功能。
decode()函数使用技巧
·软件环境:
1、WindowsNT4.0+ORACLE8.0.4
2、ORACLE安装路径为:
C:
\ORANT
·含义解释:
decode(条件,值1,翻译值1,值2,翻译值2,...值n,翻译值n,缺省值)
该函数的含义如下:
IF条件=值1THEN
RETURN(翻译值1)
ELSIF条件=值2THEN
RETURN(翻译值2)
......
ELSIF条件=值nTHEN
RETURN(翻译值n)
ELSE
RETURN(缺省值)
ENDIF
·使用方法:
1、比较大小
selectdecode(sign(变量1-变量2),-1,变量1,变量2)fromdual;--取较小值
sign()函数根据某个值是0、正数还是负数,分别返回0、1、-1
例如:
变量1=10,变量2=20
则sign(变量1-变量2)返回-1,decode解码结果为“变量1”,达到了取较小值的目的。
2、表、视图结构转化
现有一个商品销售表sale,表结构为:
month char(6) --月份
sell number(10,2) --月销售金额
现有数据为:
200001 1000
200002 1100
200003 1200
200004 1300
200005 1400
200006 1500
200007 1600
200101 1100
200202 1200
200301 1300
想要转化为以下结构的数据:
year char(4) --年份
month1 number(10,2) --1月销售金额
month2 number(10,2) --2月销售金额
month3 number(10,2) --3月销售金额
month4 number(10,2) --4月销售金额
month5 number(10,2) --5月销售金额
month6 number(10,2) --6月销售金额
month7 number(10,2) --7月销售金额
month8 number(10,2) --8月销售金额
month9 number(10,2) --9月销售金额
month10 number(10,2) --10月销售金额
month11 number(10,2) --11月销售金额
month12 number(10,2) --12月销售金额
结构转化的SQL语句为:
createorreplaceview
v_sale(year,month1,month2,month3,month4,month5,month6,month7,month8,month9,month10,month11,month12)
as
select
substrb(month,1,4),
sum(decode(substrb(month,5,2),'01',sell,0)),
sum(decode(substrb(month,5,2),'02',sell,0)),
sum(decode(substrb(month,5,2),'03',sell,0)),
sum(decode(
- 配套讲稿:
如PPT文件的首页显示word图标,表示该PPT已包含配套word讲稿。双击word图标可打开word文档。
- 特殊限制:
部分文档作品中含有的国旗、国徽等图片,仅作为作品整体效果示例展示,禁止商用。设计者仅对作品中独创性部分享有著作权。
- 关 键 词:
- oracle 常用 函数