ORACLE常用数值函数、转换函数、字符串函数介绍
(一)本文更多将会介绍三思在日常中经常会用到的,或者虽然很少用到,但是感觉挺有意思
的一些函数。分二类介绍,分别是:
著名函数篇-经常用到的函数
非著名函数篇-即虽然很少用到,但某些情况下却很实用
注:N表示数字型,C表示字符型,D表示日期型,[]表示内中参数可被忽略,fmt 表示格式。
单值函数在查询中返回单个值,可被应用到select,where子句,start with以及connect by 子句和having子句。
(一).数值型函数(Number Functions)
数值型函数输入数字型参数并返回数值型的值。多数该类函数的返回值支持38位小数点,诸如:COS, COSH, EXP, LN, LOG, SIN, SINH, SQRT, TAN, and TANH 支持36位小数点。ACOS, ASIN, A TAN, and A TAN2支持30位小数点。
1、MOD(n1,n2) 返回n1除n2的余数,如果n2=0则返回n1的值。
例如:SELECT MOD(24,5) FROM DUAL;
2、ROUND(n1[,n2]) 返回四舍五入小数点右边n2位后n1的值,n2缺省值为0,如果
n2为负数就舍入到小数点左边相应的位上(虽然oracle documents上提到n2的值必须为整数,事实上执行时此处的判断并不严谨,即使n2为非整数,它也会自动将n2取整后做处理,但是我文档中其它提到必须为整的地方需要特别注意,如果不为整执行时会报错的)。
例如:SELECT ROUND(23.56),ROUND(23.56,1),ROUND(23.56,-1) FROM DUAL;
3、TRUNC(n1[,n2] 返回截尾到n2位小数的n1的值,n2缺省设置为0,当n2为缺省
设置时会将n1截尾为整数,如果n2为负值,就截尾在小数点左边相应的位上。
例如:SELECT TRUNC(23.56),TRUNC(23.56,1),TRUNC(23.56,-1) FROM DUAL; (二).字符型函数返回字符值(Character Functions Returning Character Values) 该类函数返回与输入类型相同的类型。
返回的CHAR类型值长度不超过2000字节;λ
返回的VCHAR2类型值长度不超过4000字节;λ
如果上述应返回的字符长度超出,oracle并不会报错而是直接截断至最大可支持长度返回。
返回的CLOB类型值长度不超过4G;λ
对于CLOB类型的函数,如果返回值长度超出,oracle不会返回任何错误而是直接抛出错误。
1、LOWER(c) 将指定字符串内字符变为小写,支持CHAR,V ARCHAR2,NCHAR,NV ARCHAR2,CLOB,NCLOB类型
例如:SELECT LOWER('WhaT is tHis') FROM DUAL;
2、UPPER(c) 将指定字符串内字符变为大写,支持CHAR,V ARCHAR2,NCHAR,NV ARCHAR2,CLOB,NCLOB类型
例如:SELECT UPPER('WhaT is tHis') FROM DUAL;
3、LPAD(c1,n[,c2]) 返回指定长度=n的字符串,需要注意的有几点:
如果nλ 如果nλ>c1.length and c2 is null,以空格从左向右补充字符长度至n并返回; 如果nλ>c1.length and c2 is not null,以指定字符c2从左向右补充c1长度至n并返回; 例如:SELECT LPAD('WhaT is tHis',5),LPAD('WhaT is tHis',25),LPAD('WhaT is tHis',25,'-') FROM DUAL; 最后大家再猜一猜,如果n<0,结果会怎么样 4、RPAD(c1,n[,c2]) 返回指定长度=n的字符串,基本与上同,不过补充字符是从右向左方向正好与上相反; 例如:SELECT RPAD('WhaT is tHis',5),RPAD('WhaT is tHis',25),RPAD('WhaT is tHis',25,'-') FROM DUAL; 5、TRIM([[LEADING||TRAILING||BOTH] c2 FROM] c1) 哈哈,被俺无敌的形容方式搞晕头了吧,这个地方还是看图更明了一些。 看起来很复杂,理解起来很简单: 如果没有指定任何参数则oracle去除c1头尾空格λ 例如:SELECT TRIM(' WhaT is tHis ') FROM DUAL; 如果指定了c2参数,则oracle去掉c1头尾c2(这个建议细致测试,有多种不同情形的哟)λ例如:SELECT TRIM('W' FROM 'WhaT is tHis w W') FROM DUAL; 如果指定了leading参数则会去掉c1头部c2λ 例如:SELECT TRIM(leading 'W' FROM 'WhaT is tHis w W') FROM DUAL; 如果指定了trailing参数则会去掉c1尾部c2λ 例如:SELECT TRIM(trailing 'W' FROM 'WhaT is tHis w W') FROM DUAL; 如果指定了both参数则会去掉c1头尾c2(跟不指定有区别吗?没区别!)λ 例如:SELECT TRIM(both 'W' FROM 'WhaT is tHis w W') FROM DUAL; 注意:c2长度=1 6、LTRIM(c1[,c2]) 千万表以为与上面那个长的像,功能也与上面的类似,本函数是从字符串c1左侧截取掉与指定字符串c2相同的字符并返回。如果c2为空则默认截取空格。 例如:SELECT LTRIM('WWhhhhhaT is tHis w W','Wh') FROM DUAL; 7、RTRIM(c1,c2)与上同,不过方向相反 例如:SELECT RTRIM('WWhhhhhaT is tHis w W','W w') FROM DUAL; 8、REPLACE(c1,c2[,c3]) 将c1字符串中的c2替换为c3,如果c3为空,则从c1中删除所有c2。 例如:SELECT REPLACE('WWhhhhhaT is tHis w W','W','-') FROM DUAL; 9、SOUNDEX(c) 神奇的函数啊,该函数返回字符串参数的语音表示形式,对于比较一些读音相同,但是拼写不同的单词非常有用。计算语音的算法如下: 保留字符串首字母,但删除a、e、h、i、o、w、y。λ 将下表中的数字赋给相对应的字母:λ 1:b、f、p、v 2:c、g、k、q、s、x、z 3:d、t 4:l 5:m、n 6:R 如果字符串中存在拥有相同数字的2个以上(包含2个)的字母在一起(例如b和f),或者只有h或w,则删除其他的,只保留1个;λ 只返回前4个字节,不够用0填充λ 例如:SELECT SOUNDEX('dog'),soundex('boy') FROM DUAL; 10、SUBSTR(c1,n1[,n2]) 截取指定长度的字符串。稍不注意就可能充满了陷阱的函数。 n1=开始长度; n2=截取的字符串长度,如果为空,默认截取到字符串结尾; 如果n1=0 then n1=1λ 如果n1λ>0,则oracle从左向右确认起始位置截取 例如:SELECT SUBSTR('What is this',5,3) FROM DUAL; 如果n1λ<0,则oracle从右向左数确认起始位置 例如:SELECT SUBSTR('What is this',-5,3) FROM DUAL; 如果n1λ>c1.length则返回空 例如:SELECT SUBSTR('What is this',50,3) FROM DUAL; 然后再请你猜猜,如果n2<1,会如何返回值呢 11、TRANSLATE(c1,c2,c3) 就功能而言,此函数与replace有些相似。但需要注意的一点是,translate是绝对匹配替换,这点与replace函数具有非常大区别。什么是绝对匹配替换呢?简单的说,是将字符串c1中按一定的格式c2替换为c3。如果文字形容仍然无法理解,我们通过几具实例来说明: 例如: SELECT TRANSLATE('What is this','','-') FROM DUAL; SELECT TRANSLATE('What is this','-','') FROM DUAL; 结果都是空。来试试这个: SELECT TRANSLATE('What is this',' ',' ') FROM DUAL; 再来看这个: SELECT TRANSLATE('What is this','ait','-*') FROM DUAL; 是否明白了点呢?Replace函数理解比较简单,它是将字符串中指定字符替换成其它字符,它的字符必须是连续的。而translate中,则是指定字符串c1中出现的c2,将c2中各个字符替换成c3中位置顺序与其相同的c3中的字符。明白了?Replace是替换,而translate则像是过滤 (三).字符型函数返回数字值(Character Functions Returning Number Values) 本类函数支持所有的数据类型 1、INSTR(c1,c2[,n1[,n2]]) 返回c2在c1中位置 c1:原字符串λ c2:要寻找的字符串λ n1:查询起始位置,正值表示从左到右,负值表示从右到左(大小表示位置,比如3表示左面第3处开始,-3表示右面第3处开始)。黑黑,如果为0的话,则返回的也是0λ n2:第几个匹配项。大于0λ 例如:SELECT INSTR('abcdefg','e',-3) FROM DUAL; 2、LENGTH(c) 返回指定字符串的长度。如果 例如:SELECT LENGTH('A123中') FROM DUAL; 猜猜SELECT LENGTH('') FROM DUAL;的返回值是什么 (四).日期函数(Datetime Functions) 本类函数中,除months_between返回数值外,其它都将返回日期。 1、ADD_MONTHS() 返回指定日期月份+n之后的值,n可以为任何整数。 例如:SELECT ADD_MONTHS(sysdate,12),ADD_MONTHS(sysdate,-12) FROM DUAL; 2、CURRENT_DATE 返回当前session所在时区的默认时间 例如: SQL> alter session set nls_date_format = 'mm-dd-yyyy' ; SQL> select current_date from dual; 3、SYSDATE 功能与上相同,返回当前session所在时区的默认时间。但是需要注意的一点是,如果同时使用sysdate与current_date获得的时间不一定相同,某些情况下current_date 会比sysdate快一秒。经过与xyf_tck(兄台的大作ORACLE的工作机制写的很好,深入浅出)的短暂交流,我们认为current_date是将current_timestamp中毫秒四舍五入后的返回,虽然没有找到文档支持,但是想来应该八九不离十。同时,仅是某些情况下会有一秒的误差,一般情况下并不会对你的操作造成影响,所以了解即可。 例如:SELECT SYSDA TE,CURRENT_DA TE FROM DUAL; 4、LAST_DAY(d) 返回指定时间所在月的最后一天 例如:SELECT last_day(SYSDA TE) FROM DUAL; 5、NEXT_DAY(d,n) 返回指定日期后第一个n的日期,n为一周中的某一天。但是,需要注意的是n如果为字符的话,它的星期形式需要与当前session默认时区中的星期形式相同。例如:三思用的中文nt,nls_language值为SIMPLIFIED CHINESE SELECT NEXT_DAY(SYSDATE,5) FROM DUAL; SELECT NEXT_DAY(SYSDATE,'星期四') FROM DUAL; 两种方式都可以取到正确的返回,但是: SELECT NEXT_DAY(SYSDATE,'Thursday') FROM DUAL; 则会执行出错,提供你说周中的日无效,就是这个原因了。 6、MONTHS_BETWEEN(d1,d2) 返回d1与d2间的月份差,视d1,d2的值大小,结果可正可负,当然也有可能为0 例如: SELECT months_between(SYSDA TE, sysdate), months_between(SYSDATE, add_months(sysdate, -1)), months_between(SYSDATE, add_months(sysdate, 1)) FROM DUAL; 7、ROUND(d[,fmt]) 前面讲数值型函数的时候介绍过ROUND,此处与上功能基本相似,不过此处操作的是日期。如果不指定fmt参数,则默认返回距离指定日期最近的日期。 例如:SELECT ROUND(SYSDA TE,'HH24') FROM DUAL; 8、TRUNC(d[,fmt]) 与前面介绍的数值型TRUNC原理相同,不过此处也是操作的日期型。例如:SELECT TRUNC(SYSDA TE,'HH24') FROM DUAL; (五).转换函数(Conversion Functions) 转换函数将指定字符从一种类型转换为另一种,通常这类函数遵循如下惯例:函数名称后面跟着待转换类型以及输出类型。 1、TO_CHAR() 本函数又可以分三小类,分别是 转换字符-λ>字符TO_CHAR(c):将nchar,nvarchar2,clob,nclob类型转换为char类型; 例如:SELECT TO_CHAR('AABBCC') FROM DUAL; 转换时间-λ>字符TO_CHAR(d[,fmt]):将指定的时间(data,timestamp,timestamp with time zone)按照指定格式转换为varchar2类型; 例如:SELECT TO_CHAR(sysdate,'yyyy-mm-dd hh24:mi:ss') FROM DUAL; 转换数值-λ>字符TO_CHAR(n[,fmt]):将指定数值n按照指定格式fmt转换为varchar2类型并返回; 例如:SELECT TO_CHAR(-100, 'L99G999D99MI') FROM DUAL; 2、TO_DATE(c[,fmt[,nls]]) 将char,nchar,varchar2,nvarchar2转换为日期类型,如果fmt参数不为空,则按照fmt中指定格式进行转换。注意这里的fmt参数。如果ftm为'J'则表示按照公元制(Julian day)转换,c则必须为大于0并小于5373484的正整数。 例如: SELECT TO_DATE(2454336, 'J') FROM DUAL; SELECT TO_DATE('2007-8-23 23:25:00', 'yyyy-mm-dd hh24:mi:ss') FROM DUAL; 为什么公元制的话,c的值必须不大于5373484呢?因为Oracle的DATE类型的取值范围是公元前4712年1月1日至公元9999年12月31日。看看下面这个语句: SELECT TO_CHAR(TO_DATE('9999-12-31','yyyy-mm-dd'),'j') FROM DUAL; 3、TO_NUMBER(c[,fmt[,nls]]) 将char,nchar,varchar2,nvarchar2型字串按照fmt中指定格式转换为数值类型并返回。 例如:SELECT TO_NUMBER('-100.00', '9G999D99') FROM DUAL; (六).其它辅助函数(Miscellaneous Single-Row Functions) 1、DECODE(exp,s1,r1,s2,r2..s,r[,def]) 可以把它理解成一个增强型的if else,只不过它并不通过多行语句,而是在一个函数内实现if else的功能。 exp做为初始参数。s做为对比值,相同则返回r,如果s有多个,则持续遍历所有s,直到某个条件为真为止,否则返回默认值def(如果指定了的话),如果没有默认值,并且前面的对比也都没有为真,则返回空。 毫无疑问,decode是个非常重要的函数,在实现行转列等功能时都会用到,需要牢记和熟练使用。 例如:select decode('a2','a1','true1','a2','true2','default') from dual; 2、GREATEST(n1,n2,...n) 返回序列中的最大值 例如:SELECT GREATEST(15,5,75,8) "Greatest" FROM DUAL; 3、LEAST(n1,n2....n) 返回序列中的最小值 例如:SELECT LEAST(15,5,75,8) LEAST FROM DUAL; 4、NULLIF(c1,c2) Nullif也是个很有意思的函数。逻辑等价于:CASE WHEN c1 = c2 THEN NULL ELSE c1 END 例如:SELECT NULLIF('a','b'),NULLIF('a','a') FROM DUAL; 5、NVL(c1,c2) 逻辑等价于IF c1 is null THEN c2 ELSE c1 END。c1,c2可以是任何类型。如果两者类型不同,则oracle会自动将c2转换为c1的类型。 例如:SELECT NVL(null, '12') FROM DUAL; 6、NVL2(c1,c2,c3) 大家可能都用到nvl,但你用过nvl2吗?如果c1非空则返回c2,如果c1为空则返回c3 例如:select nvl2('a', 'b', 'c') isNull,nvl2(null, 'b', 'c') isNotNull from dual; 7、SYS_CONNECT_BY_PATH(col,c) 该函数只能应用于树状查询。返回通过c1连接的从根到节点的路径。该函数必须与connect by 子句共同使用。 例如: create table tmp3( rootcol varchar2(10), nodecol varchar2(10) ); insert into tmp3 values ('','a001'); insert into tmp3 values ('','b001'); insert into tmp3 values ('a001','a002'); insert into tmp3 values ('a002','a004'); insert into tmp3 values ('a001','a003'); insert into tmp3 values ('a003','a005'); insert into tmp3 values ('a005','a008'); insert into tmp3 values ('b001','b003'); insert into tmp3 values ('b003','b005'); select lpad(' ', level*10,'=') ||'>'|| sys_connect_by_path(nodecol,'/') from tmp3 start with rootcol = 'a001' connect by prior nodecol =rootcol; 8、SYS_CONTEXT(c1,c2[,n]) 将指定命名空间c1的指定参数c2的值按照指定长度n截取后返回。 Oracle9i提供内置了一个命名空间USERENV,描述了当前session的各项信息,其拥有下列参数: CURRENT_SCHEMA:当前模式名;λ CURRENT_USER:当前用户;λ IP_ADDRESS:当前客户端IP地址;λ OS_USER:当前客户端λ操作系统用户; 等等数十项,更详细的参数列还请大家直接参考Oracle Online Documents 例如:SELECT SYS_CONTEXT('USERENV', 'SESSION_USER') FROM DUAL; 注:N表示数字型,C表示字符型,D表示日期型,[]表示内中参数可被忽略,fmt表示格式。 单值函数在查询中返回单个值,可被应用到select,where子句,start with以及connect by 子句和having子句。 (一).数值型函数(Number Functions) 数值型函数输入数字型参数并返回数值型的值。多数该类函数的返回值支持38位小数点,诸如:COS, COSH, EXP, LN, LOG, SIN, SINH, SQRT, TAN, and TANH 支持36位小数点。ACOS, ASIN, ATAN, and A TAN2支持30位小数点。 1、ABS(n) 返回数字的绝对值 例如:SELECT ABS(-1000000.01) FROM DUAL; 2、COS(n) 返回n的余弦值 例如:SELECT COS(-2) FROM DUAL; 3、ACOS(n) 反余弦函数,n between -1 and 1,返回值between 0 and pi。 例如:SELECT ACOS(0.9) FROM DUAL; 4、BITAND(n1,n2) 位与运算,这个太有意思了,虽然没想到可能用到哪里,详细说明一下: 假设3,9做位与运算,3的二进制形式为:0011,9的二进制形式为:1001,则结果是0001,转换成10进制数为1。 例如:SELECT BITAND(3,9) FROM DUAL; 5、CEIL(n) 返回大于或等于n的最小的整数值 例如:SELECT ceil(18.2) FROM DUAL; 考你一下,猜猜ceil(-18.2)的值会是什么呢 6、FLOOR(n) 返回小于等于n的最大整数值 例如:SELECT FLOOR(2.2) FROM DUAL; 再猜猜floor(-2.2)的值会是什么呢 7、BIN_TO_NUM(n1,n2,....n) 二进制转向十进制 例如:SELECT BIN_TO_NUM(1),BIN_TO_NUM(1,0),BIN_TO_NUM(1,1) FROM DUAL; 8、SIN(n) 返回n的正玄值,n为弧度。 例如:SELECT SIN(10) FROM DUAL; 9、SINH(n) 返回n的双曲正玄值,n为弧度。 例如:SELECT SINH(10) FROM DUAL; 10、ASIN(n) 反正玄函数,n between -1 and 1,返回值between pi/2 and -pi/2。 例如:SELECT ASIN(0.8) FROM DUAL; 11、TAN(n) 返回n的正切值,n为弧度 例如:SELECT TAN(0.8) FROM DUAL; 12、TANH(n) 返回n的双曲正切值,n为弧度 例如:SELECT TANH(0.8) FROM DUAL; 13、A TAN(n) 反正切函数,n表示弧度,返回值between pi/2 and -pi/2。 例如:SELECT ATAN(-444444.9999999) FROM DUAL; 14、EXP(n) 返回e的n次幂,e = 2.71828183 ... 例如:SELECT EXP(3) FROM DUAL; 15、LN(n) 返回n的自然对数,n>0 例如:SELECT LN(0.9) FROM DUAL; 16、LOG(n1,n2) 返回以n1为底n2的对数,n1 >0 and not 1 ,n2>0 例如:SELECT LOG(1.1,2.2) FROM DUAL; 17、POWER(n1,n2) 返回n1的n2次方。n1,n2可以为任意数值,不过如果m是负数,则n 必须为整数 例如:SELECT POWER(2.2,2.2) FROM DUAL; 18、SIGN(n) 如果n<0返回-1,如果n>0返回1,如果n=0返回0. 例如:SELECT SIGN(14),SIGN(-14),SIGN(0) FROM DUAL; 19、SQRT(n) 返回n的平方根,n为弧度。n>=0 例如:SELECT SQRT(0.1) FROM DUAL; (二).字符型函数返回字符值(Character Functions Returning Character Values) 该类函数返回与输入类型相同的类型。 返回的CHAR类型值长度不超过2000字节;λ 返回的VCHAR2类型值长度不超过4000字节;λ 如果上述应返回的字符长度超出,oracle并不会报错而是直接截断至最大可支持长度返回。 返回的CLOB类型值长度不超过4G;λ 对于CLOB类型的函数,如果返回值长度超出,oracle不会返回任何错误而是直接抛出错误。 1、CHR(N[ USING NCHAR_CS]) 返回指定数值在当前字符集中对应的字符 例如:SELECT CHR(95) FROM DUAL; 2、CONCAT(c1,c2) 连接字符串,等同于|| 例如:SELECT concat('aa','bb') FROM DUAL; 3、INITCAP(c) 将字符串中单词的第一个字母转换为大写,其它则转换为小写 例如:SELECT INITCAP('whaT is this') FROM DUAL; 4、NLS_INITCAP(c) 返回指定字符串,并将字符串中第一个字母变大写,其它字母变小写例如:SELECT NLS_INITCAP('中华miNZHu') FROM DUAL; 它还具有一个参数:Nlsparam用来指定排序规则,可以忽略,默认状态该参数为当前session 的排序规则。 (三).字符型函数返回数字值(Character Functions Returning Number Values) 本类函数支持所有的数据类型 1、ASCII(c) 与chr函数的用途刚刚相反,本函数返回指定字符在当前字符集下对应的数值。例如:SELECT ASCII('_') FROM DUAL; (四).日期函数(Datetime Functions) 本类函数中,除months_between返回数值外,其它都将返回日期。 1、CURRENT_TIMESTAMP([n]) 返回当前session所在时区的日期和时间。n表示毫秒级的精度,不大于6 例如:SELECT CURRENT_TIMESTAMP(3) FROM DUAL; 2、LOCALTIMESTAMP([n]) 与上同,返回当前session所在时区的日期和时间。n表示毫秒级的精度,不大于6 例如:SELECT LOCALTIMESTAMP(3) FROM DUAL; 3、SYSTIMESTAMP([n]) 与上同,返回当前数据库所在时区的日期和时间,n表示毫秒级的精度,>0 and <6 例如:SELECT SYSTIMESTAMP(4) FROM DUAL; 4、DBTIMEZONE 返回数据库的当前时区 例如:SELECT DBTIMEZONE FROM DUAL; 5、SESSIONTIMEZONE 返回当前session所在时区 例如:SELECT SESSIONTIMEZONE FROM DUAL; 6、EXTRACT(key from date) key=(year,month,day,hour,minute,second) 从指定时间提到指定日期列 例如:SELECT EXTRACT(year from sysdate) FROM DUAL; 7、TO_TIMESTAMP(c1[,fmt]) 将指定字符按指定格式转换为timestamp格式。 例如:SELECT TO_TIMESTAMP('2007-8-22', 'YYYY-MM-DD HH:MI:SS') FROM DUAL; (五).转换函数(Conversion Functions) 转换函数将指定字符从一种类型转换为另一种,通常这类函数遵循如下惯例:函数名称后面跟着待转换类型以及输出类型。 1、BIN_TO_NUM(n1,n2...n) 将一组位向量转换为等价的十进制形式。 例如:SELECT BIN_TO_NUM(1,1,0) FROM DUAL; 2、CAST(c as newtype) 将指定字串转换为指定类型,基本只对字符类型有效,比如char,number,date,rowid等。此类转换有一个专门的表列明了哪种类型可以转换为哪种类型,此处就不作酹述。 例如:SELECT CAST('1101' AS NUMBER(5)) FROM DUAL; 3、CHARTOROWID(c) 将字符串转换为rowid类型 例如:SELECT CHARTOROWID('A003D1ABBEFAABSAA0') FROM DUAL; 4、ROWIDTOCHAR(rowid) 转换rowid值为varchar2类型。返回串长度为18个字节。 例如:SELECT ROWIDTOCHAR(rowid) FROM DUAL; 5、TO_MULTI_BYTE(c) 将指定字符转换为全角并返回char类型字串 例如:SELECT TO_MULTI_BYTE('ABC abc中华') FROM DUAL; 6、TO_SINGLE_BYTE(c) 将指定字符转换为半角并返回char类型字串 例如:SELECT TO_SINGLE_BYTE('ABCabc中华') FROM DUAL; (六).其它辅助函数(Miscellaneous Single-Row Functions) 1、COALESCE(n1,n2,....n) 返回序列中的第一个非空值 例如:SELECT COALESCE(null,5,6,null,9) FROM DUAL; 2、DUMP(exp[,fmt[,start[,length]]]) dump是个功能非常强悍的函数,对于深入了解oracle存储的人而言相当有用。所以对于我们这些仅仅只是应用的人而言就不知道能将其应用于何处了。此处仅介绍用法,不对其功能做深入分析。 如上所示,dump拥有不少参数。其本质是以指定格式,返回指定长度的exp的内部表示形式的varchar2值。fmt含4种格式:8||10||16||17,分别表示8进制,10进制,16进制和单字符,默认为10进制。start参数表示开始位置,length表示以,分隔的字串数。 例如:SELECT DUMP('abcdefg',17,2,4) FROM DUAL; 3、EMPTY_BLOB,EMPTY_CLOB 这两个函数都是返回空lob类型,通常被用于insert和update等语句以初始化lob列,或者将其置为空。EMPTY表示LOB已经被初始化,只不过还没有用来存储数据。 4、NLS_CHARSET_NAME(n) 返回指定数值对应的字符集名称。 例如:SELECT NLS_CHARSET_NAME(1) FROM DUAL; 5、NLS_CHARSET_ID(c) 返回指定字符对应的字符集id。 例如:SELECT NLS_CHARSET_ID('US7ASCII') FROM DUAL; 6、NLS_CHARSET_DECL_LEN(n1,n2) 返回一个NCHAR值的声明宽度(以字符为单位).n1是该值以字节为单位的长度,n2是该值的字符集ID 例如:SELECT NLS_CHARSET_DECL_LEN(100, nls_charset_id('US7ASCII')) FROM DUAL; 7、SYS_EXTRACT_UTC(timestamp) 返回标准通用时间即格林威治时间。 例如:SELECT SYS_EXTRACT_UTC(current_timestamp) FROM DUAL; 8、SYS_TYPEID(object_type) 返回对象类型对应的id。 例如:这个这个,没有建立过自定义对象,咋做示例? 9、UID 返回一个唯一标识当前数据库用户的整数。 例如:SELECT UID FROM DUAL; 10、USER 返回当前session用户 例如:SELECT USER FROM DUAL; 11、USERENV(c) 该函数用来返回当前session的信息,据oracle文档的说明,userenv是为了保持向下兼容的遗留函数。oracle公司推荐你使用sys_context函数调用USERENV命名空间来获取相关信息,所以大家了解下就行了。 例如:SELECT USERENV('LANGUAGE') FROM DUAL; 12、VSIZE(c) 返回c的字节数。 例如:SELECT VSIZE('abc中华') FROM DUAL; 许多软件公司都理解开发不依赖于特定数据库类型(例如Oracle、SQL Server、DB2)的应用程序的重要性,它可以让客户们选择自己习惯的平台。一般来说,软件开发人员都能够识别出他们的负责数据库维护的客户,和必须使用现有平台和个性化的客户。 关于Oracle和SQL Server之间的区别,已经有很多篇文章从企业的角度和数据库管理员的角度出发描写了两者之间的一般差别。在这篇文章里面,我将会从应用程序的角度向你描述SQL erver 和oracle平台之间的区别,并且将会讨论开发不依赖于数据库环境的应用程序的几种可能的方法。与此同时,我不会再论讨对于应用程序来说,两个平台之间显而易见的区别,例如表的分割和索引。 定义通用接口和语言有很少的几种通用语言和接口可以让应用程序不依赖于数据库,想来也可以以同样的方式应用在关系型数据库上面: ANSI是美国国家标准局定义的,这是一家志愿者成员的组织(用私人基金运转),他们在有关设备和程序等广泛领域内开发了国家承认的标准。在数据库领域, ANSI定义了编写SQL命令的标准,假设命令可以运行在任何的数据库上,而不需要更改命令的语法。 ODBC是开放数据库连接(ODBC)接口,微软定义的,它可以让应用程序访问数据库管理系统(DBMS)中的数据,使用SQL作为访问数据的标准。 ODBC允许最大的互联性,这意味着一个单个的应用程序可以访问不同的数据库管理系统。然后,应用程序终端用户可以添加开放数据库连接(ODBC)数据库驱动来将应用程序与他们所选择的数据库管理系统链接上。 OLE DB 是ODBC的继承者,是一组允许例如基于VB、C++、Access等任何可以连接到类似SQL Server、Oracle、DB2、MySQL等后台的“前台”的软件组件。在许多种情况下,OLE DB组件都比原有的ODBC提供了好得多的性能。 JDBC(Java数据库连接)应用程序接口是Java编程语言和广泛范围的数据库,SQL数据库和其他表列数据源(例如电子表格或者普通文本文件)之间,进行不依赖于数据库的连接的行业标准。JDBD应用程序接口为基于SQL的数据库访问提供了调用级别的应用程序接口。真实世界中的通用接口不幸的是,并不是所有数据库级别的命令都是ANSI,每个数据库平台都有自己的扩展功能。对于ANSI或者通用接口,一般来说都代表着几本功能,因此也可能意味着丧失了性能方面的竞争力。对于小型数据库和小型应用程序来说,要维护对数据库的通用访问是简单的,但是当数据库和/或应用程序变得越来越大,越来越复杂,你就不得不向代码中添加功能。 SQL Server和Oracle的常用函数对比 ---------数学函数 1.绝对值 S:select abs(-1) value O:select abs(-1) value from dual 2.取整(大) S:select ceiling(-1.001) value O:select ceil(-1.001) value from dual Oracle中分析函数用法小结 一.分析函数适用场景: ○1需要对同样的数据进行不同级别的聚合操作 ○2需要在表内将多条数据和同一条数据进行多次的比较 ○3需要在排序完的结果集上进行额外的过滤操作 二.分析函数语法: FUNCTION_NAME( Oracle常见数值函数 ----***特别说明***: x 可以是纯的数值,也可以是数值型表达式/* ABS(x)返回x绝对值 eg. */ selectabs(100),abs(-100) from dual; /* sign(x)判断x的正负,正数返回1,负数返回-1,0返回0; eg. */ selectsign(100),sign(-100),sign(0) from dual; /* round(x[,n])对x进行四舍五入,保留n位小数,其中n采用其整数部分; 没有n时默认四舍五入到整数位,n为负数时,四舍五入保留小数点左边n位(补零), eg. */ selectround(5555.6666, 2.1), round(5555.6666, -2.6), round(5555.6666) from dual; /* trunc(x)对x进行直接截取,保留n位小数,其中n采用其整数部分; 没有n时默认截取到整数位,n为负数时,截取保留小数点左边n位(补零), eg. */ selecttrunc(5555.66666,2.1), trunc(5555.66666,-2.6), trunc(5555.033333) from dual; /* ceil(x)对x进行向上取整,返回不小于x的最小整数(可以是整数x本身)。 eg. */ selectceil(3.1), ceil(2.8+1.3), ceil(0) from dual; /* floor(x)对x进行向下取整,返回不大于x的最大整数(可以是整数x本身)。eg. */ selectfloor(3.1), floor(2.8+1.3), floor(0) from dual; /* mod(x,y)求x除以y的余数,x,y为数字型表达式。 eg. */ selectmod(23,8),mod(24,8) from dual; Oracle常用函数/过程说明主要介绍Oracle的系统函数、过程和包。 SQL常用函数: 数值函数: ABS Purpose 返回绝对值 Returns the absolute value of n. Example SELECT ABS(-15) "Absolute" FROM DUAL; Absolute ---------- 15 CEIL Purpose 取最小整数 Returns smallest integer greater than or equal to n. Example SELECT CEIL(15.7) "Ceiling" FROM DUAL; Ceiling ---------- 16 * MOD Syntax MOD(m,n) Purpose 取余 Returns remainder of m divided by n. Returns m if n is 0. Example SELECT MOD(11,4) "Modulus" FROM DUAL; Modulus ---------- 3 * ROUND Syntax ROUND(n[,m]) Purpose 取四舍五入信息 Returns n rounded to m places right of the decimal point; if m is omitted, to 0 places. m can be negative to round off digits left of the decimal point. m must be an integer. oracle中常用函数大全 1、数值型常用函数 函数返回值样例显示 ceil(n) 大于或等于数值n的最小整数select ceil(10.6) from dual; 11 floor(n) 小于等于数值n的最大整数select ceil(10.6) from dual; 10 mod(m,n) m除以n的余数,若n=0,则返回m select mod(7,5) from dual; 2 power(m,n) m的n次方select power(3,2) from dual; 9 round(n,m) 将n四舍五入,保留小数点后m位select round(1234.5678,2) from dual; 1234.57 sign(n) 若n=0,则返回0,否则,n>0,则返回1,n<0,则返回-1 select sign(12) from dual; 1 sqrt(n) n的平方根select sqrt(25) from dual ; 5 2、常用字符函数 initcap(char) 把每个字符串的第一个字符换成大写select initicap('mr.ecop') from dual; Mr.Ecop lower(char) 整个字符串换成小写select lower('MR.ecop') from dual; mr.ecop replace(char,str1,str2) 字符串中所有str1换成str2 select replace('Scott','s','Boy') from dual; Boycott substr(char,m,n) 取出从m字符开始的n个字符的子串select substr('ABCDEF',2,2) from dual; CD length(char) 求字符串的长度select length('ACD') from dual; 3 || 并置运算符select 'ABCD'||'EFGH' from dual; ABCDEFGH 3、日期型函数 sysdate当前日期和时间select sysdate from dual; Oracle笔记 实现中英文转换 Alter session set nls_language=‘ADMIN’ 从已知表中复制数据和结构 Create table test as select * from dept; 从已知表中复制数据和结构,但是不包括数据 Create table test as select * from dept where 1=2 插入数据 Insert into test select * from dept 二、运算符 +——*/可以在select 语句中使用 ||连接两个字段,select deptno ||dname from dept 比较运算付:> >= = !=< <= 逻辑运算符:not ,or 集合运算符:intersect,union,union all,minus, 使用ord er by 的时候必须使用位置序号,不能使用列名 Select *from emp intersect select * from emp where deptno=10; Select *from emp minus select * from emp where deptno=10; ---不包括重复行 Select *from emp where deptno=10 union select * from emp where deptno in(10,20);----包括重复行 Select *from emp where deptno=10 union all select * from emp where deptno in(10,20); 三、常用ORACLE函数 Oracle常用函数及使用案例(珍藏版) 一:sql函数: lower(char):将字符串转化为小写的格式。 upper(char):将字符串转化为大写的格式。 length(char):返回字符串的长度。 substr(char,m,n):取字符串的字串。 案例1.将所有员工的名字按小写的方式显示 select lower(ename),sal from emp; 案例2.将所有员工的名字按大写的方式显示。 select upper(ename),sal from emp; 案例3.显示正好为五个字符的的员工的姓名。 select * from emp where length(ename)=5; 案例4.显示所有员工姓名的前三个字符。 select substr(ename,1,3) from emp;//从名字的第一个字符开始取,向后取三个字符。 案例5.以首字母为大写的方式显示所有员工的姓名。 (1)首字母大写:select upper(substr(ename,1,1)) from emp; (2)完成后面字母小写。select lower(substr(ename,2,length(ename)-1)) from emp; (3)合并select upper(substr(ename,1,1))||lower(substr(ename,2,length(ename)-1)) from emp; 案例6.以首字母为小写的方式显示所有员工的姓名。(需要有较高的灵活度,细心分析和清晰思路) (1)首字母小写:select upper(substr(ename,1,1)) from emp; (2)完成后面字母大写。select lower(substr(ename,2,length(ename)-1)) from emp; (3)合并select lower(substr(ename,1,1))||upper(substr(ename,2,length(ename)-1)) from emp; 案例7.函数(替换):replace(char1,search_string,replace_string); 显示所有员工的姓名,用“我要替换A”替代所有“A”。 select replace(ename,'A','我是老鼠')from emp; 案例8.以首字母为小写的方式显示所有员工的姓名。 select replace(ename,substr(ename,1,1),lower(substr(ename,1,1)))from emp; 案例9.以首字母为大写的方式显示所有员工的姓名。 Select replace(ename,substr(ename,2,length(ename)-1),lower(substr(ename,2,length(ename) -1)))from emp; 二:数学函数:(在财务中用的比较多) ronud(sal)用于四舍五默认取整; ronud(sal,1)用于四舍五留一位小数。 trunc(sal)取整,忽略小数。截去小数部分。 trunc(sal,1)截取;小数点留一位,之后的右边的省去。 trunc(sal,-1)截取;只留整数,个位数取零。 floor(sal)向下最接近取整;比如1.1值为1. Oracle常用函数列表速查 PL/SQL单行函数和组函数详解 函数是一种有零个或多个参数并且有一个返回值的程序。在SQL中Oracle内建了一系列函数,这些函数都可被称为SQL或PL/SQL语句,函数主要分为两大类: 单行函数 组函数 本文将讨论如何利用单行函数以及使用规则。 SQL中的单行函数 SQL和PL/SQL中自带很多类型的函数,有字符、数字、日期、转换、和混合型等多种函数用于处理单行数据,因此这些都可被统称为单行函数。这些函数均可用于SELECT,WHERE、ORDER BY 等子句中,例如下面的例子中就包含了TO_CHAR,UPPER,SOUNDEX等单行函数。 SELECT ename,TO_CHAR(hiredate,'day,DD-Mon-YYYY')FROM empWhere UPPER(ename) Like 'AL%'ORDER BY SOUNDEX(ename) 单行函数也可以在其他语句中使用,如update的SET子句,INSERT的V ALUES子句,DELET 的WHERE子句,认证考试特别注意在SELECT语句中使用这些函数,所以我们的注意力也集中在SELECT语句中。 NULL和单行函数 在如何理解NULL上开始是很困难的,就算是一个很有经验的人依然对此感到困惑。NULL值表示一个未知数据或者一个空值,算术操作符的任何一个操作数为NULL值,结果均为提个NULL值,这个规则也适合很多函数,只有CONCAT,DECODE,DUMP,NVL,REPLACE在调用了NULL参数时能够返回非NULL值。在这些中NVL函数时最重要的,因为他能直接处理NULL值,NVL有两个参数:NVL(x1,x2),x1和x2都式表达式,当x1为null时返回X2,否则返回x1。 下面我们看看emp数据表它包含了薪水、奖金两项,需要计算总的补偿 column name emp_id salary bonuskey type pk nulls/unique nn,u nnfk table datatype number number numberlength 11.2 11.2 不是简单的将薪水和奖金加起来就可以了,如果某一行是null值那么结果就将是null,比如下面的例子: update empset salary=(salary+bonus)*1.1 这个语句中,雇员的工资和奖金都将更新为一个新的值,但是如果没有奖金,即salary + null,那么就会得出错误的结论,这个时候就要使用nvl函数来排除null值的影响。 所以正确的语句是: update empset salary=(salary+nvl(bonus,0)*1.1 /*此文章可以作为sql脚本直接运行,某些函数限于oracle数据库!前面建表和数据插入可以不看,直接看后面红色部分的代码,前面的表只是提供一些数据供大家练习,不用自己再去建表了,真正的内容在后面*/ --人员表 create table person( personID number , pname varchar2(20) constraint NN_pname not null, psex char(2) , pbirth date , constraint PK_personID primary key (personID) , constraint CK_psex check (psex='男' or psex='女') ); --增加数据 create sequence seq_personID ; insert into person values (seq_personID.Nextval,'david','男',to_date('1990-09-10','yyyy-mm- dd')); insert into person values (seq_personID.Nextval,'ggyy','男',to_date('1991-10-10','yyyy-mm-dd')); insert into person values (seq_personID.Nextval,'朱 刀','男',to_date('1970-10-10','yy-mm-dd')); insert into person values (seq_personID.Nextval,'泥 巴','女',to_date('1991-10-10','yy-mm-dd')); insert into person values (seq_personID.Nextval,'憨 坨','女',to_date('1991-10-10','yy-mm-dd')); insert into person values (seq_personID.Nextval,'李静 芳','女',to_date('1990-09-10','yyyy-mm-dd')); insert into person (personID,pname,psex) values (seq_personID.Nextval,'胖子','男'); insert into person (personID,pname,psex) values (seq_personID.Nextval,'小成成','男'); insert into person (personID,pname,psex) values (seq_personID.Nextval,'乐姐','女'); insert into person (personID,pname,psex) values (seq_personID.Nextval,'靓崽波','男'); --登录账号表 create table loginzh( userID number , personID number , zhanghao varchar2(20) , passwd varchar2(20) , logintime date , loginIP varchar2(15) , constraint PK_userID primary key (userID) , constraint FK_personID foreign key (personID) references person (personID) ); --为这个表加点数据 create sequence seq_userID ; insert into loginzh values (seq_userID.Nextval,1,'111111','123456',sysdate,'127.0.0.1'); --sysdate为系统当前时间 oracle SQL里常用的时间函数,经典推荐 相信很多人都有过统计某些数据的经历,比如,要统计财务的情况,可能要按每年,每季度,每月,甚至每个星期来分别统计。那在oracle 中应该怎么来写sql语句呢,这个时候Oracle的日期函数会给我们很多帮助。 常用日期型函数 1。Sysdate 当前日期和时间 SQL> Select sysdate from dual; SYSDATE ---------- 21-6月-05 2。Last_day 本月最后一天 SQL> Select last_day(sysdate) from dual; LAST_DAY(S ---------- 30-6月-05 3。Add_months(d,n) 当前日期d后推n个月 用于从一个日期值增加或减少一些月份 date_value:=add_months(date_value,number_of_months) SQL> Select add_months(sysdate,2) from dual; ADD_MONTHS ---------- 21-8月-05 4。Months_between(f,s) 日期f和s间相差月数 SQL> select months_between(sysdate,to_date('2005-11-12','yyyy-mm-dd'))fro m dual; MONTHS_BETWEEN(SYSDATE,TO_DATE('2005-11-12','YYYY-MM-D D')) ---------------------------------------------------------- -4.6966741 5。NEXT_DAY(d, day_of_week) 返回由"day_of_week"命名的,在变量"d"指定的日期之后的第一个工作日的日期。参数"day_of_week"必须为该星期中的某一天。 --1、选择部门30中的雇员 select * from emp where deptno=30; --2、列出所有办事员的姓名、编号和部门 select ename,empno,dname from emp e inner join dept d on e.deptno = d.deptno where job=upper('clerk?); --3、找出佣金高于薪金的雇员 select * from emp where comm>sal; --4、找出佣金高于薪金60%的雇员 select * from emp where comm>sal*0.6 --5、找出部门10中所有经理和部门20中的所有办事员的详细资料 select * from emp where (deptno=10 and job=upper('manager')) or (deptno=20 and job=upper('clerk ')); --6、找出部门10中所有经理、部门20中所有办事员,既不是经理又不是办事员但其薪金>=2000的所有雇员的详细资料 select * from emp where (deptno=10 and job=upper('manager')) or (deptno=20 and job=upper('clerk ')) or (job<>upper(…manager?) and job<>upper(…clerk?) and sal>=2000) --7、找出收取佣金的雇员的不同工作 select distinct job from emp where comm>0; --8、找出不收取佣金或收取的佣金低于100的雇员 select * from emp where nvl(comm,0)<100; --9、找出各月最后一天受雇的所有雇员 select * from emp where hiredate= last_day(hiredate); --10、找出早于25年之前受雇的雇员 Oracle函数 Oracle SQL提供了用于执行特定操作的专用函数,这些函数大大增强了SQL语言的功能。函数可以接受零个或者多个输入参数,并返回一个输出结果。Oracle数据库中主要使用两种类型的函数: 1.单行函数:对每一个函数应用在表的记录中时,只能输入一行结果,返回一个结 果,比如:MOD(x,y)返回x除以y的余数(x和y可以是两个整数,也可以是表中 的整数列)。常用的单行函数有: 字符函数:对字符串操作。 数字函数:对数字进行计算,返回一个数字。 转换函数:可以将一种数据类型转换为另外一种数据类型。 日期函数:对日期和时间进行处理。 2.聚合函数:聚合函数同时可以对多行数据进行操作,并返回一个结果。比如SUM(x) 返回结果集中x列的总合。 目录大纲 Oracle函数 (1) ?字符函数 (2) ?数字函数 (3) ?日期函数 (4) ?转换函数 (6) ?其他单行函数 (8) ?聚合函数 (9) 字符函数接受字符参数,这些参数可以是表中的列,也可以是一个字符串表达式。下表列出了常用的字符函数。 表1 字符函数 表2 字符函数示例 数字函数接受数字参数,参数可以来自表中的一列,也可以是一个数字表达式。 表3 数字函数 说明: 1.ROUND(X[,Y]),四舍五入。 在缺省y时,默认y=0;比如:ROUND(3.56)=4。 y是正整数,就是四舍五入到小数点后y位。ROUND(5.654,2)=5.65。 y是负整数,四舍五入到小数点左边|y|位。ROUND(351.654,-2)=400。 2.TRUNC(x[,y]),直接截取,不四舍五入。 在缺省y时,默认y=0;比如:TRUNC (3.56)=3。 y是正整数,就是四舍五入到小数点后y位。TRUNC (5.654,2)=5.65。 y是负整数,四舍五入到小数点左边|y|位。TRUNC (351.654,-2)=300。 附录Ⅱ Oracle11g SQL函数 函数名 返回 类型 说明 字符串函数 ASCII(s) 数值 返回s首位字母的ASCII码 CHR(i) 字符 返回数值i的ASCII字符 CONCAT(s1,s2) 字符 将s2连接到字符串s1的后面 INITCAP(s) 字符 将每个单词首位字母大写其它字母小写 INSTR(s1,s2[,i[,j]]) 数值 返回s2在s1中第i位开始第j次出现的位置 INSTRB(s1,s2[,i[,j]]) 数值 与INSTR(s)函数相同,但按字节计算 LENGTH(s) 数值 返回s的长度。 LENGTHb(s) 数值 与LENGTH(s)相同,但按字节计算。 lower(s) 字符 返回s的小写字符 LPAD(s1,i[,s2]) 字符 在s1的左侧用s2字符串补足到总长度i LTRIM(s1,s2) 字符 循环去掉在s2中存在的s1左边字符 RPAD(s1,i[,s2]) 字符 在s1的右侧用s2字符串补足到总长度i RTRIM(s1,s2) 字符 循环去掉在s2中存在的s1右边字符 REPLACE(s1,s2[,s3]) 字符 用s3替换出现在s1中的s2 REVERSE(s) 字符 返回s倒排的字符串 SUBSTR(s,i[,j]) 字符 从s的第i位开始截得长度j的子字符串 SUBSTRB(s,i[,j]) 字符 与SUBSTR相同,但i,j按字节计算。 SOUNDEX(s) 返回与s发音相似的词 TRANSLATE(s1,s2,s3) 字符 将s1中与s2相同的字符以s3代替 TRIM(s) 字符 删除s的首部和尾部空格 UPPER(s) 字符 返回s的大写 正则表达式函数 REGEXP_LIKE() 布尔 功能与LIKE的功能相似 REGEXP_INSTR() 数值 功能与INSTR的功能相似 REGEXP_SUBSTR() 字符 功能与SUBSTR的功能相似 REGEXP_REPLACE() 字符 功能与REPLACE的功能相似 数字函数 ABS(i) 数值 返回i的绝对值 ACOS(i) 数值 反余玄函数,返回-1到1之间的数 ASIN(i) 数值 反正玄函数,返回-1到1之间的数 ATAN(i) 数值 反正切函数,返回i的反正切值 数值函数 日期函数 SELECT TO_CHAR(sysdate,'YYYY-MM-DD HH24:MI:SS AM DY') FROM dual; SELECT TO_CHAR(sysdate,'YYYY"年"MM"月"DD"日"') FROM dual; 其他函数 5.高级查询(多表联合查询) 例子: SELECT job 职务 , SUM(sal) 工资总和 FROM emp WHERE job != 'PRESIDENT' GROUP BY job HAVING SUM(sal)>4500 ORDER BY SUM(sal); 分析函数 以下三个分析函数用于计算一个行在一组有序行中的排位,序号从1开始 ROW_NUMBER返回连续的排位,不论值是否相等 RANK具有相等值的行排位相同,序数随后跳跃 DENSE_RANK 具有相等值的行排位相同,序号是连续的 例子: SELECT deptno, ename, sal, comm, RANK() OVER (PARTITION BY deptno ORDER BY sal DESC, comm) RANK FROM emp; SELECT ename, job, deptno, sal, ROW_NUMBER() OVER (ORDER BY sal DESC) AS SAL_RANK FROM SCOTT.EMP; SELECT d.dname, e.ename, e.sal, DENSE_RANK() OVER (PARTITION BY e.deptno ORDER BY e.sal DESC) AS DENRANK FROM emp e, dept d WHERE e.deptno = d.deptno; ORACLE内置函数使用手册 ASCII()——返回字符的ASCCII码; CHAR()——当给定ASCII码返回字符; concat()——连接函数,相当于运算符“||” e.g:select CONCAT(CONCAT(name,'is a'),job) "Job" From emp result:James is a recorder initcap()——返回函数内的单词,每个词首字母大写,其他字母转化为小写,不影响非字母字符; e.g:select INITCAP('the emp') Form Emp result:The Emp INSTR(char1,char2[,n[,m]])——返回char1中包含char2的位置。n为负数时,从右往左查,第m次的结果被返回,返回其下标,缺省值都是1,若没找到,返回0。 e.g: select INSTR('CORPORATE FLOOR','OR',3,2) from table result:14 select INSTR('CORPORATE FLOOT','OR',-3,2) FROM TABLE RESULT:2 INSTRB类似INSTR,区别于,对于单字节字符系统,返回的值以字节为单位; LENGTH(CHAR)——以字节返回字符串char的长度,包括填充的字符。若char有数据型char,则字符串的长度包括其结尾的空格。若char为NULL,则该函数返回NULL; LENGTHB()——类似LENGTH(),区别于,对于单字节字符集,返回的值以字节为单位; lower()——转化小写 e.g:select lower('HELLO') from table result:hello lpad(char1,length[,char2])—— char1左边增长到length,左边补char2。char2未设定时,默认空格,当char2小于length时,仅前几个被复制 例:select lpad('hugo',10,'*#') from table result:*#*#*#hugo LTRIM('char [,set])——删除char左边出现的set字符。 例:Select ltrim('abaabhi','ab') 运行返回:aabhi Oracle常用及非常用函数详解 转载自:https://www.360docs.net/doc/6612282000.html,/blog/192292 感于总有些网友提出一些非常基础的问题,比如有没有实现某某功能的函数啊,某某函数是做什么用的啊,格式是什么等等,同时也感受到自己对oracle函数认识的不足,于是集中月余时间专注于oracle函数,小有心得不敢私藏,发之与诸公共享。 本文并不准备介绍全部的oracle函数,当前情势下,俺也还没这个时间,需要学习的东西太多了,要把多数时间花在学习经常能用上的技术方面:),所以如果是准备深入了解所有oracle函数的朋友,还是去关注:Oracle SQL Reference官方文档更靠谱一些。 本文更多将会介绍三思在日常中经常会用到的,或者虽然很少用到,但是感觉挺有意思的一些函数。分二类介绍,分别是: 著名函数篇-经常用到的函数 非著名函数篇-即虽然很少用到,但某些情况下却很实用 注:N表示数字型,C表示字符型,D表示日期型,[ ]表示内中参数可被忽略,fmt表示格式。 单值函数在查询中返回单个值,可被应用到select,where子句,start with以及connect by 子句和having子句。 (一).数值型函数(Number Functions) 数值型函数输入数字型参数并返回数值型的值。多数该类函数的返回值支持38位小数点,诸如:COS, COSH, EXP, LN, LOG, SIN, SINH, SQRT, TAN, and TANH 支持36位小数点。ACOS, ASIN, ATAN, and ATAN2支持30位小数点。 1、MOD(n1,n2) 返回n1除n2的余数,如果n2=0则返回n1的值。 例如:SELECT MOD(24,5) FROM DUAL; 2、ROUND(n1[,n2]) 返回四舍五入小数点右边n2位后n1的值,n2缺省值为0,如果n2为负数就舍入到小数点左边相应的位上(虽然oracle documents上提到n2的值必须为整数,事实上执行时此处的判断并不严谨,即使n2为非整数,它也会自动将n2取整后做处理,但是我文档中其它提到必须为整的地方需要特别注意,如果不为整执行时会报错的)。 例如:SELECT ROUND(23.56),ROUND(23.56,1),ROUND(23.56,-1) FROM DUAL; 3、TRUNC(n1[,n2] 返回截尾到n2位小数的n1的值,n2缺省设置为0,当n2为缺省设置时会将n1截尾为整数,如果n2为负值,就截尾在小数点左边相应的位上。 例如:SELECT TRUNC(23.56),TRUNC(23.56,1),TRUNC(23.56,-1) FROM DUAL; (二).字符型函数返回字符值(Character Functions Returning Character Values) 该类函数返回与输入类型相同的类型。 返回的CHAR类型值长度不超过2000字节; 返回的VCHAR2类型值长度不超过4000字节; 如果上述应返回的字符长度超出,oracle并不会报错而是直接截断至最大可支持长度返回。 (一)本文更多将会介绍三思在日常中经常会用到的,或者虽然很少用到,但是感觉挺有意思 的一些函数。分二类介绍,分别是: 著名函数篇-经常用到的函数 非著名函数篇-即虽然很少用到,但某些情况下却很实用 注:N表示数字型,C表示字符型,D表示日期型,[]表示内中参数可被忽略,fmt 表示格式。 单值函数在查询中返回单个值,可被应用到select,where子句,start with以及connect by 子句和having子句。 (一).数值型函数(Number Functions) 数值型函数输入数字型参数并返回数值型的值。多数该类函数的返回值支持38位小数点,诸如:COS, COSH, EXP, LN, LOG, SIN, SINH, SQRT, TAN, and TANH 支持36位小数点。ACOS, ASIN, A TAN, and A TAN2支持30位小数点。 1、MOD(n1,n2) 返回n1除n2的余数,如果n2=0则返回n1的值。 例如:SELECT MOD(24,5) FROM DUAL; 2、ROUND(n1[,n2]) 返回四舍五入小数点右边n2位后n1的值,n2缺省值为0,如果 n2为负数就舍入到小数点左边相应的位上(虽然oracle documents上提到n2的值必须为整数,事实上执行时此处的判断并不严谨,即使n2为非整数,它也会自动将n2取整后做处理,但是我文档中其它提到必须为整的地方需要特别注意,如果不为整执行时会报错的)。 例如:SELECT ROUND(23.56),ROUND(23.56,1),ROUND(23.56,-1) FROM DUAL; 3、TRUNC(n1[,n2] 返回截尾到n2位小数的n1的值,n2缺省设置为0,当n2为缺省 设置时会将n1截尾为整数,如果n2为负值,就截尾在小数点左边相应的位上。 例如:SELECT TRUNC(23.56),TRUNC(23.56,1),TRUNC(23.56,-1) FROM DUAL; (二).字符型函数返回字符值(Character Functions Returning Character Values) 该类函数返回与输入类型相同的类型。 返回的CHAR类型值长度不超过2000字节;λ 返回的VCHAR2类型值长度不超过4000字节;λ 如果上述应返回的字符长度超出,oracle并不会报错而是直接截断至最大可支持长度返回。 返回的CLOB类型值长度不超过4G;λ 对于CLOB类型的函数,如果返回值长度超出,oracle不会返回任何错误而是直接抛出错误。 1、LOWER(c) 将指定字符串内字符变为小写,支持CHAR,V ARCHAR2,NCHAR,NV ARCHAR2,CLOB,NCLOB类型 oracle常用函数使用大全 一、运算符 算术运算符:+ - * / 可以在select 语句中使用 连接运算符:|| select deptno|| dname from dept; 比较运算符:> >= = != < <= like between is null in 逻辑运算符:not and or 集合运算符:intersect ,union,union all,minus 要求:对应集合的列数和数据类型相同 查询中不能包含long 列 列的标签是第一个集合的标签 使用order by时,必须使用位置序号,不能使用列名 例:集合运算符的使用: intersect ,union,union all,minus select * from emp intersect select * from emp where deptno=10 ; select * from emp minus select * from emp where deptno=10; select * from emp where deptno=10 union select * from emp where deptno in (10,20); --不包括重复行 select * from emp where deptno=10 union all select * from emp where deptno in (10,20); --包括重复行 二.ORACLE日期时间函数大全 TO_DATE格式(以时间:2007-11-02 13:45:25为例) Year: yy two digits 两位年显示值:07 yyy three digits 三位年显示值:007 yyyy four digits 四位年显示值:2007 Month: mm number 两位月显示值:11 mon abbreviated 字符集表示显示值:11月,若是英文版,显示nov month spelled out 字符集表示显示值:11月,若是英文版,显示novemberoracle函数
Oracle中分析函数用法小结
Oracle 常见函数(一)——数值函数
Oracle函数详解(经典)
oracle中常用函数大全
ORACLE常用函数和一些项目中常用的SQL语句
Oracle常用函数及使用案例(珍藏版)
oracle常用函数列表速查 (1)
oracle数据库之常用的函数练习
oracle SQL里常用的时间函数
oracle常用函数习题及答案
oracle常用函数介绍及其使用
Oracle函数大全
Oracle常用函数
ORACLE内置函数使用手册
AAA-Oracle常用及非常用函数详解
ORACLE常用数值函数、转换函数、字符串函数介绍
oracle常用函数使用大全