oracle函数Word格式.docx

oracle函数Word格式.docx

《oracle函数Word格式.docx》由会员分享，可在线阅读，更多相关《oracle函数Word格式.docx（48页珍藏版）》请在冰点文库上搜索。

3、LPAD（c1,n[,c2]）返回指定长度=n的字符串，需要注意的有几点：

●如果n<

c1.length则从右到左截取指定长度返回；

●如果n>

c1.lengthandc2isnull，以空格从左向右补充字符长度至n并返回；

c1.lengthandc2isnotnull，以指定字符c2从左向右补充c1长度至n并返回；

SELECTLPAD（'

5）,LPAD（'

25）,LPAD（'

25,'

-'

最后大家再猜一猜，如果n<

0，结果会怎么样

4、RPAD（c1,n[,c2]）返回指定长度=n的字符串，基本与上同，不过补充字符是从右向左方向正好与上相反；

SELECTRPAD（'

5）,RPAD（'

25）,RPAD（'

5、TRIM（[[LEADING||TRAILING||BOTH]c2FROM]c1）哈哈，被俺无敌的形容方式搞晕头了吧，这个地方还是看图更明了一些。

看起来很复杂，理解起来很简单：

●如果没有指定任何参数则oracle去除c1头尾空格

SELECTTRIM（'

WhaTistHis'

●如果指定了c2参数，则oracle去掉c1头尾c2（这个建议细致测试，有多种不同情形的哟）

W'

FROM'

WhaTistHiswW'

●如果指定了leading参数则会去掉c1头部c2

SELECTTRIM（leading'

●如果指定了trailing参数则会去掉c1尾部c2

SELECTTRIM（trailing'

●如果指定了both参数则会去掉c1头尾c2（跟不指定有区别吗？

没区别！

）

SELECTTRIM（both'

注意：

c2长度=1

6、LTRIM（c1[,c2]）千万表以为与上面那个长的像，功能也与上面的类似，本函数是从字符串c1左侧截取掉与指定字符串c2相同的字符并返回。

如果c2为空则默认截取空格。

SELECTLTRIM（'

WWhhhhhaTistHiswW'

'

Wh'

7、RTRIM（c1,c2）与上同，不过方向相反

SELECTRTRIM（'

Ww'

8、REPLACE（c1,c2[,c3]）将c1字符串中的c2替换为c3，如果c3为空，则从c1中删除所有c2。

SELECTREPLACE（'

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填充

SELECTSOUNDEX（'

dog'

）,soundex（'

boy'

10、SUBSTR（c1,n1[,n2]）截取指定长度的字符串。

稍不注意就可能充满了陷阱的函数。

n1=开始长度；

n2=截取的字符串长度，如果为空，默认截取到字符串结尾；

●如果n1=0thenn1=1

●如果n1>

0，则oracle从左向右确认起始位置截取

SELECTSUBSTR（'

Whatisthis'

5,3）FROMDUAL;

●如果n1<

0，则oracle从右向左数确认起始位置

-5,3）FROMDUAL;

c1.length则返回空

50,3）FROMDUAL;

然后再请你猜猜，如果n2<

1，会如何返回值呢

11、TRANSLATE（c1,c2,c3）就功能而言，此函数与replace有些相似。

但需要注意的一点是，translate是绝对匹配替换，这点与replace函数具有非常大区别。

什么是绝对匹配替换呢？

简单的说，是将字符串c1中按一定的格式c2替换为c3。

如果文字形容仍然无法理解，我们通过几具实例来说明：

SELECTTRANSLATE（'

'

结果都是空。

来试试这个：

'

再来看这个：

ait'

-*'

是否明白了点呢？

Replace函数理解比较简单，它是将字符串中指定字符替换成其它字符，它的字符必须是连续的。

而translate中，则是指定字符串c1中出现的c2，将c2中各个字符替换成c3中位置顺序与其相同的c3中的字符。

明白了？

Replace是替换，而translate则像是过滤。

（3）.字符型函数返回数字值（CharacterFunctionsReturningNumberValues）

本类函数支持所有的数据类型

1、INSTR（c1,c2[,n1[,n2]]）返回c2在c1中位置

●c1:

原字符串

●c2:

要寻找的字符串

●n1:

查询起始位置，正值表示从左到右，负值表示从右到左（大小表示位置,比如3表示左面第3处开始,-3表示右面第3处开始）。

黑黑，如果为0的话，则返回的也是0

●n2:

第几个匹配项。

大于0

SELECTINSTR（'

abcdefg'

e'

-3）FROMDUAL;

2、LENGTH（c）返回指定字符串的长度。

如果

SELECTLENGTH（'

A123中'

猜猜SELECTLENGTH（'

的返回值是什么

（4）.日期函数（DatetimeFunctions）

本类函数中，除months_between返回数值外，其它都将返回日期。

1、ADD_MONTHS（）返回指定日期月份+n之后的值，n可以为任何整数。

SELECTADD_MONTHS（sysdate,12）,ADD_MONTHS（sysdate,-12）FROMDUAL;

2、CURRENT_DATE返回当前session所在时区的默认时间

SQL>

altersessionsetnls_date_format='

mm-dd-yyyy'

;

selectcurrent_datefromdual;

3、SYSDATE功能与上相同，返回当前session所在时区的默认时间。

但是需要注意的一点是，如果同时使用sysdate与current_date获得的时间不一定相同，某些情况下current_date会比sysdate快一秒。

经过与xyf_tck（兄台的大作ORACLE的工作机制写的很好，深入浅出）的短暂交流，我们认为current_date是将current_timestamp中毫秒四舍五入后的返回，虽然没有找到文档支持，但是想来应该八九不离十。

同时，仅是某些情况下会有一秒的误差，一般情况下并不会对你的操作造成影响，所以了解即可。

SELECTSYSDATE,CURRENT_DATEFROMDUAL;

4、LAST_DAY（d）返回指定时间所在月的最后一天

SELECTlast_day（SYSDATE）FROMDUAL;

5、NEXT_DAY（d,n）返回指定日期后第一个n的日期，n为一周中的某一天。

但是，需要注意的是n如果为字符的话，它的星期形式需要与当前session默认时区中的星期形式相同。

三思用的中文nt，nls_language值为SIMPLIFIEDCHINESE

SELECTNEXT_DAY（SYSDATE,5）FROMDUAL;

SELECTNEXT_DAY（SYSDATE,'

星期四'

两种方式都可以取到正确的返回，但是：

Thursday'

则会执行出错，提供你说周中的日无效，就是这个原因了。

6、MONTHS_BETWEEN（d1,d2）返回d1与d2间的月份差，视d1,d2的值大小，结果可正可负，当然也有可能为0

SELECTmonths_between（SYSDATE,sysdate）,

months_between（SYSDATE,add_months（sysdate,-1））,

months_between（SYSDATE,add_months（sysdate,1））

FROMDUAL;

7、ROUND（d[,fmt]）前面讲数值型函数的时候介绍过ROUND，此处与上功能基本相似，不过此处操作的是日期。

如果不指定fmt参数，则默认返回距离指定日期最近的日期。

SELECTROUND（SYSDATE,'

HH24'

8、TRUNC（d[,fmt]）与前面介绍的数值型TRUNC原理相同，不过此处也是操作的日期型。

SELECTTRUNC（SYSDATE,'

（5）.转换函数（ConversionFunctions）

转换函数将指定字符从一种类型转换为另一种，通常这类函数遵循如下惯例：

函数名称后面跟着待转换类型以及输出类型。

1、TO_CHAR（）本函数又可以分三小类，分别是

●转换字符->

字符TO_CHAR（c）：

将nchar,nvarchar2,clob,nclob类型转换为char类型；

SELECTTO_CHAR（'

AABBCC'

●转换时间->

字符TO_CHAR（d[,fmt]）：

将指定的时间（data,timestamp,timestampwithtimezone）按照指定格式转换为varchar2类型；

SELECTTO_CHAR（sysdate,'

yyyy-mm-ddhh24:

mi:

ss'

●转换数值->

字符TO_CHAR（n[,fmt]）：

将指定数值n按照指定格式fmt转换为varchar2类型并返回；

SELECTTO_CHAR（-100,'

L99G999D99MI'

2、TO_DATE（c[,fmt[,nls]]）将char,nchar,varchar2,nvarchar2转换为日期类型，如果fmt参数不为空，则按照fmt中指定格式进行转换。

注意这里的fmt参数。

如果ftm为'

J'

则表示按照公元制（Julianday）转换，c则必须为大于0并小于5373484的正整数。

SELECTTO_DATE（2454336,'

SELECTTO_DATE（'

2007-8-2323:

25:

00'

'

3、TO_NUMBER（c[,fmt[,nls]]）将char,nchar,varchar2,nvarchar2型字串按照fmt中指定格式转换为数值类型并返回。

SELECTTO_NUMBER（'

-100.00'

9G999D99'

（6）.其它辅助函数（MiscellaneousSingle-RowFunctions）

1、DECODE（exp,s1,r1,s2,r2..s,r[,def]）可以把它理解成一个增强型的ifelse，只不过它并不通过多行语句，而是在一个函数内实现ifelse的功能。

exp做为初始参数。

s做为对比值，相同则返回r，如果s有多个，则持续遍历所有s，直到某个条件为真为止，否则返回默认值def（如果指定了的话），如果没有默认值，并且前面的对比也都没有为真，则返回空。

毫无疑问，decode是个非常重要的函数，在实现行转列等功能时都会用到，需要牢记和熟练使用。

selectdecode（'

a2'

a1'

true1'

true2'

default'

）fromdual;

2、GREATEST（n1,n2,...n）返回序列中的最大值

SELECTGREATEST（15,5,75,8）"

Greatest"

3、LEAST（n1,n2....n）返回序列中的最小值

SELECTLEAST（15,5,75,8）LEASTFROMDUAL;

4、NULLIF（c1,c2）

Nullif也是个很有意思的函数。

逻辑等价于：

CASEWHENc1=c2THENNULLELSEc1END

SELECTNULLIF（'

a'

b'

）,NULLIF（'

5、NVL（c1,c2）逻辑等价于IFc1isnullTHENc2ELSEc1END。

c1,c2可以是任何类型。

如果两者类型不同，则oracle会自动将c2转换为c1的类型。

SELECTNVL（null,'

12'

6、NVL2（c1,c2,c3）大家可能都用到nvl，但你用过nvl2吗？

如果c1非空则返回c2,如果c1为空则返回c3

selectnvl2（'

c'

）isNull,nvl2（null,'

）isNotNullfromdual;

7、SYS_CONNECT_BY_PATH（col,c）该函数只能应用于树状查询。

返回通过c1连接的从根到节点的路径。

该函数必须与connectby子句共同使用。

createtabletmp3（

rootcolvarchar2（10）,

nodecolvarchar2（10）

）;

insertintotmp3values（'

a001'

b001'

a002'

a004'

a003'

a005'

a008'

b003'

b005'

selectlpad（'

level*10,'

='

）||'

>

||sys_connect_by_path（nodecol,'

/'

fromtmp3

startwithrootcol='

connectbypriornodecol=rootcol;

8、SYS_CONTEXT（c1,c2[,n]）将指定命名空间c1的指定参数c2的值按照指定长度n截取后返回。

Oracle9i提供内置了一个命名空间USERENV，描述了当前session的各项信息，其拥有下列参数：

●CURRENT_SCHEMA:

当前模式名；

●CURRENT_USER:

当前用户；

●IP_ADDRESS:

当前客户端IP地址；

●OS_USER:

当前客户端操作系统用户；

等等数十项，更详细的参数列还请大家直接参考OracleOnlineDocuments

SELECTSYS_CONTEXT（'

USERENV'

SESSION_USER'

（7）.数值型函数（NumberFunctions）

4、MOD（n1,n2）返回n1除n2的余数，如果n2=0则返回n1的值。

5、ROUND（n1[,n2]）返回四舍五入小数点右边n2位后n1的值，n2缺省值为0，如果n2为负数就舍入到小数点左边相应的位上（虽然oracledocuments上提到n2的值必须为整数，事实上执行时此处的判断并不严谨，即使n2为非整数，它也会自动将n2取整后做处理，但是我文档中其它提到必须为整的地方需要特别注意，如果不为整执行时会报错的）。

6、TRUNC（n1[,n2]返回截尾到n2位小数的n1的值，n2缺省设置为0，当n2为缺省设置时会将n1截尾为整数，如果n2为负值，就截尾在小数点左边相应的位上。

（8）.字符型函数返回字符值（CharacterFunctionsReturningCharacterValues）

12、LOWER（c）将指定字符串内字符变为小写，支持CHAR,VARCHAR2,NCHAR,NVARCHAR2,CLOB,NCLOB类型

13、UPPER（c）将指定字符串内字符变为大写，支持CHAR,VARCHAR2,NCHAR,NVARCHAR2,CLOB,NCLOB类型

14、LPAD（c1,n[,c2]）返回指定长度=n的字符串，需要注意的有几点：

c1.lengthandc2isnull，以空格从