oracle常用函数.docx
- 文档编号:7105369
- 上传时间:2023-01-17
- 格式:DOCX
- 页数:12
- 大小:22.35KB
oracle常用函数.docx
《oracle常用函数.docx》由会员分享,可在线阅读,更多相关《oracle常用函数.docx(12页珍藏版)》请在冰豆网上搜索。
oracle常用函数常用函数Oracle常用SQL函数单行函数包括,字符、数字、日期、转换和普通函数。
字符函数举例:
全小写LOWER(SQLCoursesqlcourse全大写UPPER(SQLCourseSQLCOURSEb5E2RGbCAP首字母大写INITCAP(SQLCourseSqlCourse拼接CONCAT(Good,StringGoodString取子串SUBSTR(String,1,3Str求长度LENGTH(String6p1EanqFDPw数字函数举例:
ROUND-对数值进行四舍五入操作TRUNC-截断。
注意截断和舍入的位数,可以是负数。
ROUND(45.923,245.92ROUND(45.923,046ROUND(45.923,-150TRUNC(45.923,245.92TRUNC(45.92345TRUNC(45.923,-140DXDiTa9E3dMOD-返回两数相除的余数MOD(1600,300-100日期函数:
Oracle将日期按照内部格式将日期存成以下七个字节-世纪、年、月、日、时、分、秒。
在Oracle中日期缺省的显示格式为:
DD-MON-YY。
可以使用系统变量SYSDATE获取系统时间。
查看SYSDATE时,可以使用虚拟表DUAL。
RTCrpUDGiT举例:
求两个日期间有多少个月MONTHS_BETWEEN(01-SEP-95,11-JAN-941.9774194在日期上加指定的月数ADD_MONTHS(11-JAN-94,611-JUL-94下一个星期几是什么时候NEXT_DAY(01-SEP-95,FRIDAY08-SEP-95某月最后一天LAST_DAY(01-SEP-9530-SEP-955PCzVD7HxA把日期四舍五入到月份ROUND(25-MAY-95,MONTH01-JUN-95ROUND(25-MAY-95,YEAR01-JAN-95把日期截断到月份TRUNC(25-MAY-95,MONTH01-MAY-95TRUNC(25-MAY-95,YEAR01-JAN-95jLBHrnAILg转换函数TO_CHAR将数字或日期转换为字符串TO_NUMBER将字符串转换成数字TO_DATE将字符串转换成日期在转换函数中会使用格式串xHAQX74J0XTO_CHAR(date,fmt格式串包含在中,大小写敏感,可以是任意有效的日期格式格式串:
YYYY表示完整的四位数字年YEAR表示英文拼写的年MM表示两位数字月MONTH表示英文全拼的年DY表示三位缩写的星期DAY表示英文全拼的星期LDAYtRyKfE一些特殊用法:
时间格式HH24:
MI:
SSAM15:
45:
32PM在格式串中加入字符串DDofMONTH12ofOCTOBER用后缀拼出整个日期ddspthfourteenthZzz6ZB2LtkTO_CHAR(number,fmt使用此函数将数字转换成数字9表示数字0强制为0$设置美元符号L使用当前字符集的货币符号.小数点,千位分隔符dvzfvkwMI1可以使用TO_NUMBER将字符串转换成数字可以使用TO_DATE将字符串转换成格式日期TO_DATE(10September1992,ddMonthYYYYrqyn14ZNXI使用格式串TO_DATE(char,fmt普通函数NULL值处理函数NVL(expr1,expr2如果expr1为NULL,返回expr2NVL2(expr1,expr2,expr3如果expr1为非空,则返回expr2,如果expr1为NULL,则返回expr3NULLIF(expr1,expr2如果expr1=expr2,返回NULL,否则返回expr1EmxvxOtOcoCOALESCE(expr1,expr2,.,exprn返回表达式列表中的第一个非空表达式SixE2yXPq5条件表达式,用case表达式或decode实现条件逻辑CASE表达式CASEexprWHENcomparison_expr1THENreturn_expr1WHENcomparison_expr2THENreturn_expr2WHENcomparison_exprnTHENreturn_exprnELSEelse_exprEND6ewMyirQFLdecode函数DECODE(col|expression,search1,result1,search2,result2,.,defaultkavU42VRUs分组函数:
AVG(DISTINCT|ALL|nCOUNT(DISTINCT|ALL|expr|*MAX(DISTINCT|ALL|exprMIN(DISTINCT|ALL|exprSTDDEV(DISTINCT|ALL|nSUM(DISTINCT|ALL|nVARIANCE(DISTINCT|ALL|ny6v3ALoS89常用SQL字符串函数问题集锦出处:
开店乐电子商务研究KaiDianLe.Com作者:
开店乐电更新时间:
2007-12-817:
57:
52M2ub6vSTnP问:
请教一个问题:
select*fromitemcodewherecodelike40%如何让code=40101001变成code=401-01-001目前有666个是类似40101001用什么语句能把它变成401-01-001答:
updateitemcodesetcode=replace(code,left(code,8,left(code,3+-+substring(code,4,2+-+substring(code,6,3wherecodelike40%0YujCfmUCwupdateitemcodesetcode=replace(code,left(code,8,left(code,3|-|substr(code,4,2|-|substr(code,6,3wherecodelike40%(PL/SQL语法eUts8ZQVRd问:
replace(code,left(code,8,left(code,3+-+substring(code,4,2+-+substring(code,6,3是什么意思?
sQsAEJkW5T答:
拿code=123456789做例子a=left(code,8=12345678b=left(code,3=123c=substring(code,4,2=45d=substring(code,6,3=678e=b+-+c+-+e=123-45-678f=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子句以及表达式中使用字符串函数。
常用的字符串函数有:
GMsIasNXkA一、字符转换函数1、ASCII(返回字符表达式最左端字符的ASCII码值。
在ASCII将ASCII码转换为字符。
如果没有输入0255之间的ASCII码值,CHAR和UPPER(LOWER(将字符串全部转为小写;UPPER(将字符串全部转为大写。
4、STR(把数值型数据转换为字符型数据。
STR(,length,length指定返回的字符串的长度,decimal指定返回的小数位数。
如果没有指定长度,缺省的length值为10,decimal缺省值为0。
lzq7IGf02E当length或者decimal为负值时,返回NULL;当length小于小数点左边把字符串头部的空格去掉。
2、RTRIM(把字符串尾部的空格去掉。
三、取子串函数1、left(LEFT(,zvpgeqJ1hk返回character_expression左起integer_expression个字符。
2、RIGHT(RIGHT(,NrpoJac3v1返回character_expression右起integer_expression个字符。
3、SUBSTRING(SUBSTRING(,length1nowfTG4KI返回从字符串左边第starting_position个字符起length个字符的部分。
四、字符串比较函数1、CHARINDEX(返回字符串中某个指定的子串出现的开始位置。
CHARINDEX(,其中substring_expression是所要查找的字符表达式,expression可为字符串也可为列名表达式。
如果没有发现子串,则返回0值。
fjnFLDa5Zo此函数不能用于TEXT和IMAGE数据类型。
2、PATINDEX(返回字符串中某个指定的子串出现的开始位置。
PATINDEX(,其中子串表达式前后必须有百分号“%”否则返回值为0。
tfnNhnE6e5与CHARINDEX函数不同的是,PATINDEX函数的子串中可以使用通配符,且此函数可用于CHAR、VARCHAR和TEXT数据类型。
HbmVN777sL五、字符串操作函数1、QUOTENAME(返回被特定字符括起来的字符串。
QUOTENAME(,quote_character其中quote_character标明括字符串所用的字符,缺省值为“”。
V7l4jRB8Hs2、REPLICATE(返回一个重复character_expression指定次数的字符串。
REPLICATE(character_expressioninteger_expression如果integer_expression值为负值,则返回NULL。
83lcPA59W93、REVERSE(将指定的字符串的字符排列顺序颠倒。
REVERSE(其中character_expression可以是字符串、常数或一个列的值。
mZkklkzaaP4、REPLACE(返回被替换了指定子串的字符串。
REPLACE(,用string_expression3替换在string_expression1中的子串string_expression2。
AVktR43bpw4、SPACE(返回一个有指定长度的空白字符串。
SPACE(如果integer_expression值为负值,则返回NULL。
ORjBnOwcEd5、STUFF(用另一子串替换字符串指定位置、长度的子串。
STUFF(,2MiJTy0dTT如果起始位置为负或长度值为负,或者起始位置大于character_expression1的长度,则返回NULL值。
gIiSpiue7A如果length长度大于character_expression1中start_position以右的长度,则character_expression1只保留首字符。
uEh0U1Yfmh六、数据类型转换函数1、CAST(CAST(ASlength2、CONVERT(CONVERT(length,styleIAg9qLsgBX1)data_type为SQLServer系统定义的数据类型,用户自定义的数据类型不能在此使用。
2)length用于指定数据的长度,缺省值为30。
3)把CHAR或VARCHAR类型转换为诸如INT或SAMLLINT这样的INTEGER类型、结果必须是带正号或负号的数值。
WwghWvVhPE4)TEXT类型到CHAR或VARCHAR类型转换最多为8000个字符,即CHAR或VARCHAR数据类型是最大长度。
asfpsfpi4k5)IMAGE类型存储的数据转换到BINARY或VARBINARY类型,最多为8000个字符。
6)把整数值转换为MONEY或SMALLMONEY类型,按定义的国家的货币单位来处理,如人民币、美元、英镑等。
ooeyYZTjj17)BIT类型的转换把非零值转换为1,并仍以BIT类型存储。
8)试图转换到不同长度的数据类型,会截短转换值并在转换值后显示“+”,以标识发生了这种截断。
9)用CONVERT返回date_expression中的日期值2、month(date_expression返回date_expression中的月份值3、year(date_expression返回date_expression中的年份值4、DATEADD(DATEADD(,返回指定日期date加上指定的额外日期间隔number产生的新日期。
参数“datepart”取值如下:
PgdO0sRlMo5、DATEDIFF(DATEDIFF(,返回两个指定日期在datepart方面的不同之处,即date2超过date1的差距值,其结果值是一个带有正负号的整数值。
3cdXwckm156、DATENAME(DATENAME(,以字符串的形式返回日期的指定部分此部分。
由datepart来指定。
7、DATEPART(DATEPART(,以整数值的形式返回日期的指定部分。
此部分由datepart来指定。
DATEPART(dd,date等同于DAY(dateDATEPART(mm,date等同于MONTH(dateDATEPART(yy,date等同于YEAR(date8、GETDATE(以DATETIME的缺省格式返回系统当前的日期和时间REGEXP_REPLACEselectregexp_replace(123,232,4343,32423,3252,553,23423,5435,2354,234,54535,h8c52WOngM(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.42、ORACLE安装路径为:
C:
ORANT含义解释:
decode(条件,值1,翻译值1,值2,翻译值2,.值n,翻译值n,缺省值该函数的含义如下:
IF条件=值1THENRETURN(翻译值1ELSIF条件=值2THENRETURN(翻译值2.ELSIF条件=值nTHENRETURN(翻译值nELSERETURN(缺省值ENDIF使用方法:
1、比较大小selectdecode(sign(变量1-变量2,-1,变量1,变量2fromdual。
-取较小值sign(函数根据某个值是0、正数还是负数,分别返回0、1、-1例如:
变量1=10,变量2=20则sign(变量1-变量2返回-1,decode解码结果为“变量1”,达到了取较小值的目的。
2、表、视图结构转化现有一个商品销售表sale,表结构为:
monthchar(6-月份sellnumber(10,2-月销售金额现有数据为:
2000011000200002110020000312002000041300200005140020000615002000071600200101110020020212002003011300想要转化为以下结构的数据:
yearchar(4-年份month1number(10,2-1月销售金额month2number(10,2-2月销售金额month3number(10,2-3月销售金额month4number(10,2-4月销售金额month5number(10,2-5月销售金额month6number(10,2-6月销售金额month7number(10,2-7月销售金额month8number(10,2-8月销售金额month9number(10,2-9月销售金额month10number(10,2-10月销售金额month11number(10,2-11月销售金额month12number(10,2-12月销售金额结构转化的SQL语句为:
createorreplaceviewv_sale(year,month1,month2,month3,month4,month5,month6,month7,month8,month9,month10,month11,month12asselectsubstrb(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(substrb(month,5,2,04,sell,0,v4bdyGiousoraclesignoracle-sign取数字n的符号,大于0返回1,小于0返回-1,等于0返回0SQLselectsign(100,sign(-100,sign(0fromdual。
SIGN(123SIGN(-100SIGN(0-1-10SQLselectsign(100,sign(-100,sign(0fromdual。
SIGN(123SIGN(-100SIGN(0-1-10J0bm4qMpJ9给用户授可执行函数权限grantexecuteonDATE_TESTtoinms。
申明:
所有资料为本人收集整理,仅限个人学习使用,勿做商业用途。
- 配套讲稿:
如PPT文件的首页显示word图标,表示该PPT已包含配套word讲稿。双击word图标可打开word文档。
- 特殊限制:
部分文档作品中含有的国旗、国徽等图片,仅作为作品整体效果示例展示,禁止商用。设计者仅对作品中独创性部分享有著作权。
- 关 键 词:
- oracle 常用 函数
![提示](https://static.bdocx.com/images/bang_tan.gif)