14章Oracle中的函数与表达式

《14章Oracle中的函数与表达式》由会员分享，可在线阅读，更多相关《14章Oracle中的函数与表达式（69页珍藏版）》请在装配图网上搜索。

1、,Click to edit Master title style,Click to edit Master text styles,第14章 Oracle中的函数与表达式,Oracle中提供了大量的内置函数，以处理各种形式的运算。这些函数涵盖了字符串运算、数值运算、日期运算等方面。同样，Oracle允许使用数值运算、逻辑运算等基本的表达式运算，另外，提供了SQL标准所规定的特殊判式。,Oracle中的字符串函数；,Oracle中的数学函数；,Oracle中的日期函数；,,第14章Oracle中的函,数,数与表,达,达式,Oracle中的聚,合,合函数,；,；,Oracle中的运,算,算表达

2、,式,式；,Oracle中的特,殊,殊判式,；,；,Oracle中的高,级,级函数——分析函,数,数与窗,口,口函数,。,。,14.1Oracle中的字,符,符串函,数,数,Oracle提供了,丰,丰富的,字,字符串,函,函数，,本,本小节,将,将通过,实,实例讲,述,述Oracle中各字,符,符串函,数,数的使,用,用。,14.1.1lpad()函数,lpad()函数用,于,于左补,全,全字符,串,串。在,某,某些情,况,况下，,预,预期的,字,字符串,为,为固定,长,长度，,而,而且格,式,式统一,，,，此时,可,可以考,虑,虑使用lpad()函数。,例,例如，,深,深市股,票,票代码,都

3、,都以0开头，,并,并且都,为,为6位，可,以,以利用lpad格式化,股,股票代,码,码，以,保,保证股,票,票代码,的,的格式,。,。,selectlpad(,','21,',',6,,,,'0,',')stock_code fromdual;,需要注,意,意的是,，,，当原,字,字符串,的,的长度,大,大于预,期,期长度,时,时，实,际,际进行,的,的是截,取,取字符,串,串操作,。,。,selectlpad(,','1234567',,,,6,,','0',),) stock_codefromdual;,,14.1.2rpad()函数,与lpad()函数相,反,反，rpad()函数从,右

4、,右端补,齐,齐字符,串,串。,selectrpad(,','abc',10,,,, ',*,*')fromdual;,注意与,说,说明：lpad()和rpad()都用于,填,填充字,符,符串，lpad()从左端,进,进行填,充,充，而rpad()从右端,进,进行填,充,充，但,是,是，二,者,者在最,终,终截取,字,字符串,时,时，都,是,是从左,端,端开始,截,截取。,selectrpad(,','abcdefg',,,, 6,,,, ',*,*')fromdual;,14.1.3lower()函数——返回小,写,写字符,串,串,lower(,),)函数用,于,于返回,字,字符串,的,的小

5、写,形,形式。lower(,),)函数在,查,查询语,句,句中经,常,常扮演,重,重要角,色,色。例,如,如，对,于,于用户,名,名和密,码,码的校,验,验来说,，,，用户,名,名一般,并,并不区,分,分大小,写,写，用,户,户无论,输,输入了,大,大写还,是,是小写,形,形式，,都,都被认,为,为是合,法,法用户,。,。因此,，,，在数,据,据库查,询,询时，,应,应该将,数,数据库,中,中用户,名,名与用,户,户输入,的,的用户,名,名进行,统,统一。,selectuser_id,user_name fromt_userswherelower(user_name,),) =lower,(,

6、('Alex,',');,14.1.4upper()函数——返回大,写,写字符,串,串,upper(,),)函数用,于,于返回,字,字符串,的,的大写,形,形式。,与,与lower(,),)函数类,似,似，upper(,),)函数也,可,可以用,在,在查询,语,语句中,，,，以统,一,一数据,库,库和查,询,询条件,的,的一致,性,性。,selectuser_id,user_name fromt_userswhereupper(user_name,),) =upper,(,('ALEX,',');,注意与,说,说明：upper(,),)函数和lower(,),)函数只,针,针对英,文,文字符,

7、其,其作用,，,，因为,只,只有英,文,文字符,才,才有大,小,小写之,分,分。,14.1.5initcap()函数——单词首,字,字母大,写,写,initcap()函数将,单,单词的,首,首字母,大,大写。,selectinitcap(,','big')fromdual;,需要注,意,意的是,，,，initcap()函数不,能,能自动,识,识别单,词,词,selectinitcap(,','bigbigtiger,',')from dual,;,;,initcap(),函,函数会,将,将参数,中,中的非,单,单词字,符,符作为,单,单词分,隔,隔符,selectinitcap(,','big

8、_big_tiger',),) fromdual;,selectinitcap(,','big/big/tiger',),) fromdual;,selectinitcap(,','big bigtiger',),) fromdual;,14.1.6length(,),)函数——返回字,符,符串长,度,度,length,(,()函数用,于,于返回,字,字符串,的,的长度,。,。,selectlength('abcd,',',),) fromdual;,空字符,串,串的长,度,度不是0，而是null。因为,空,空字符,串,串被视,作,作null，所以,，,，length,(,(null)返回的,

9、仍,仍然是null。,selectlength(',',')from dual,;,;,对其其,他,他数据,类,类型，,照,照样可,以,以通过length,(,()函数来,获,获得其,长,长度。length,(,()函数会,首,首先将,参,参数转,换,换为字,符,符串，,然,然后计,算,算其长,度,度。,selectlength(12.51)from dual,;,;,14.1.7substr()函数——截取字,符,符串,substr()函数用,于,于截取,字,字符串,。,。该函,数,数可以,指,指定截,取,取的起,始,始位置,，,，截取,长,长度，,可,可以实,现,现灵活,的,的截取,操,操

10、作，,因,因此，,成,成为字,符,符串操,作,作中最,常,常用的,函,函数之,一,一。,例如，,对,对于字,符,符串“1234567890”，现欲,截,截取自,第,第5位开始,的,的4个字符,。,。,selectsubstr('1234567890',5,4)fromdual;,需要注,意,意的是,，,，Oracle中字符,位,位置从1开始，,而,而不是,像,像某些,编,编程语,言,言（如Java）那样,从,从0开始。,如果不,指,指定长,度,度，那,么,么substr,(,()函数将,获,获取起,始,始位置,参,参数至,字,字符串,结,结尾处,的,的所有,字,字符。,selectsubstr

11、('1234567890',5)fromdual;,14.1.8instr()函数——获得字,符,符串出,现,现的位,置,置,instr()函数用,于,于获得,子,子字符,串,串在父,字,字符串,中,中出现,的,的位置,。,。,selectinstr,(,('bigbigtiger,',',,','big')fromdual;,可以指,定,定额外,的,的参数,，,，以命,令,令该函,数,数从指,定,定位置,开,开始搜,索,索。,selectinstr,(,('bigbigtiger,',',,','big',2)fromdual;,还可以,指,指定出,现,现次数,参,参数，,以,以指定,是,是

12、第几,次,次搜索,到,到子字,符,符串。,selectinstr,(,('bigbigtiger,',',,','big',2,2)fromdual;,14.1.9ltrim()函数——删除字,符,符串首,部,部空格,ltrim()中的l代表left。该函,数,数用于,删,删除字,符,符串左,端,端的空,白,白符。,selectltrim,(,('abc')fromdual;,需要注,意,意的是,，,，空白,符,符不仅,仅,仅包括,了,了空格,符,符，还,包,包括TAB键、回,车,车符和,换,换行符,。,。,14.1.10rtrim()函数——删除字,符,符串尾,部,部空格,rtrim()中的

13、r代表right。该函,数,数用于,删,删除字,符,符串右,端,端空白,符,符。删,除,除字符,串,串首尾,空,空白符,可,可以结,合,合使用ltrm()和rtrim()函数。,selectrtrim,(,(ltrim,(,('abc,',',),))from dual,;,;,14.1.11trim()函数——删除字,符,符串首,尾,尾空格,trim()函数可,用,用于删,除,除首尾,空,空格，,相,相当于ltrim()和rtrim()的组合,。,。,selecttrim(,','abc,',')fromdual;,14.1.12to_char()函数——将其他,类,类型转,换,换为字,符,

14、符类型,to_char()函数用,于,于将其,他,他数据,类,类型的,数,数据转,换,换为字,符,符型，,这,这些类,型,型主要,包,包括数,值,值型、,日,日期型,。,。,1.将数值,型,型转换,为,为字符,串,串,selectto,_,_char(120,,,, '99999',),) resultfrom dual,;,;,selectto,_,_char(0.96,,','9.99',),) resultfrom dual,;,;,selectto,_,_char(0.96,,','0.00',),) resultfrom dual,;,;,selectto,_,_char(5897.

15、098,,','999,999,,,,999.000',),) resultfrom dual,;,;,selectto,_,_char(5987.098,,','$999,,,,999,999.000,',')resultfromdual;,2.将日期,型,型转换,为,为字符,串,串,selectto,_,_char(sysdate,,','yyyy-mm-dd',),) resultfrom dual,;,;,selectto,_,_char(sysdate,,','YYYY-MON,-,-DD,',')from dual,;,;,14.1.13chr()函数——将ascii码转换,为,为

16、字符,串,串,chr()函数用,于,于将ascii码转换,为,为字符,串,串。通,过,过chr()函数，,可,可以对,不,不宜直,接,接输入,的,的字符,进,进行操,作,作。例,如,如，将,回,回车换,行,行符插,入,入到数,据,据中。,insertintotest_datavalues (6,,','周林,','||chr,(,(13,),)||chr,(,(10,),)|',梁,梁军',,,, 20);,,select,*,*from test,_,_datawhereid,=,= 6,;,;,14.1.14translate,(,()函数——替换字,符,符,translate,(,()函

17、数用,于,于替换,字,字符串,。,。替换,的,的规则,类,类似于,翻,翻译的,过,过程。,selecttranslate('56338',,,, '1234567890',,','avlihemoqr,',')resultfromdual;,需要注,意,意的是,，,，当字,符,符不能,被,被成功,“,“翻译,”,”，那,么,么，Oracle将使用,空,空字符,替,替换它,。,。利用,此,此特性,，,，可以,使,使用translate,(,()函数来,删,删除一,个,个含有,数,数字和,英,英文字,母,母的字,符,符串中,的,的所有,字,字母：,selecttranslate('21343yui

18、oioizf899dasiwpe58595oda0j098',,','#abcdefghijklmnopqrstuvwxyz',,,,',',')reulst,from dual,;,;,14.2Oracle中的数,学,学函数,Oracle提供的,数,数学函,数,数可以,处,处理日,常,常使用,到,到的大,多,多数数,学,学运算,。,。本小,节,节将讲,述,述Oracle中常用,的,的几种,数,数学函,数,数。,14.2.1abs,(,()函数——返回数,字,字的绝,对,对值,abs,(,()函数的,参,参数只,能,能是数,值,值型，,该,该参数,用,用于返,回,回参数,的,的绝对,值,值。,

19、selectabs(-2.1,),) fromdual;,14.2.2round (,),)函数——返回数,字,字的“,四,四舍五,入,入”值,round(,),)函数用,于,于返回,某,某个数,字,字的四,舍,舍五入,值,值。为,了,了使用,该,该函数,，,，除了,提,提供原,始,始值之,外,外，还,应,应提供,精,精确到,的,的位数,。,。精确,位,位数可,以,以为正,整,整数、0和负整,数,数。,selectround,(,(2745.173,,,, 2,),) resultfrom dual,;,;,如果不,使,使用第,二,二个参,数,数，那,么,么，相,当,当于使,用,用了参,数,数

20、0，即精,确,确到整,数,数。,selectround,(,(2745.173,),) resultfrom dual,;,;,如果第,二,二个参,数,数为负,数,数，那,么,么，相,当,当于将,数,数值精,确,确到小,数,数点之,前,前的位,数,数。,selectround,(,(2745,,-,-1,),) resultfrom dual,;,;,14.2.3ceil,(,()函数——向上取,整,整,ceil()函数只,能,能有一,个,个参数,。,。该函,数,数将参,数,数向上,取,取整，,以,以获得,大,大于等,于,于该参,数,数的最,小,小整数,。,。,selectceil(21.89

21、7,),) resultfrom dual,;,;,需要注,意,意的是,该,该函数,针,针对负,数,数的运,算,算：,selectceil(,-,-21,.,.897)resultfromdual;,因为ceil()函数返,回,回的是,大,大于等,于,于参数,的,的最小,整,整数，,所,所以，,该,该函数,返,返回的,并,并非-22，而是-21。,14.2.4floor()函数——向下取,整,整,与ceil函数相,反,反，floor(,),)函数用,于,于返回,小,小于等,于,于某个,数,数值的,最,最大整,数,数。,selectfloor,(,(21,.,.897)resultfromdua

22、l;,selectfloor,(,(-21.897)result fromdual;,14.2.5mod,(,()函数——取模操,作,作,mod,(,()函数有,两,两个参,数,数，第,一,一个参,数,数为被,除,除数，,第,第二个,参,参数为,除,除数。mod,(,()函数的,实,实际功,能,能为获,得,得两数,相,相除之,后,后的余,数,数。,selectmod(5,,,,2)result fromdual;,14.2.6sign,(,()函数——返回数,字,字的正,负,负性,sign()函数只,有,有一个,参,参数。,该,该函数,将,将返回,参,参数的,正,正负性,。,。若返,回,回值为

23、1，表示,该,该参数,大,大于0；若返,回,回值为-1，表示,该,该参数,小,小于0；若返,回,回值为0，表示,该,该参数,等,等于0。,selectsign(8)resultfromdual;,selectsign(,-,-8)result fromdual;,selectsign(0)resultfromdual;,sign()函数为,判,判断两,个,个数值,的,的大小,关,关系提,供,供了方,便,便。因,为,为在oracle中，利,用,用类似ifelse的结构,来,来判断,两,两个数,值,值之间,的,的大小,关,关系，,并,并不像,编,编程语,言,言中那,样,样方便,，,，而且,极,极易

24、造,成,成代码,的,的复杂,化,化。,14.2.7sqrt()函数——返回数,字,字的平,方,方根,sqrt()函数也,只,只有一,个,个参数,。,。该函,数,数用于,返,返回参,数,数的平,方,方根。,可,可以利,用,用round(,),)函数和sqrt()函数返,回,回某个,数,数值的,近,近似平,方,方根。,selectround,(,(sqrt(2),3)result fromdual;,14.2.8power()函数——乘方运,算,算,power(,),)函数有,两,两个参,数,数。该,函,函数用,于,于实现,数,数值的,乘,乘方运,算,算。,selectpower,(,(6,2)r

25、esult fromdual;,14.2.9trunc()函数——截取数,字,字,trunc()函数用,于,于截取,部,部分数,字,字。其,工,工作机,制,制非常,类,类似于round(,),)函数。,与,与round(,),)函数不,同,同的是,，,，该函,数,数不对,数,数值做,四,四舍五,入,入处理,，,，而是,直,直接截,取,取。,selecttrunc,(,(2745.173,,,, 2,),) resultfrom dual,;,;,保留位,数,数的值,可,可以为0，当该,参,参数的,值,值为0时，将,保,保留到,整,整数。,selecttrunc,(,(2745.173,),)

26、resultfrom dual,;,;,当保留,位,位数小,于,于0时，表,示,示保留,到,到小数,点,点之前,的,的位数,。,。,selecttrunc,(,(2745.173,,,, -1)resultfromdual;,14.2.10vsize()函数——返回数,据,据的存,储,储空间,vsize()函数根,据,据数据,库,库的存,储,储格式,，,，来返,回,回其所,占,占用的,存,存储空,间,间的字,节,节数。,selectvsize,(,('abc123',),) fromdual;,注意与,说,说明：vsize()函数在,返,返回的,是,是Oracle实际存,储,储数据,的,的字节

27、,数,数，在,实,实际开,发,发中使,用,用的几,率,率也较,小,小。读,者,者可以,不,不必了,解,解Oracle本身的,存,存储机,制,制。,14.2.11to_number()函数——将字符,串,串转换,为,为数值,类,类型,to_number()函数可,以,以将字,符,符串转,换,换为数,值,值型。,selectto,_,_number('257,.,.90,',')resultfromdual;,需要注,意,意的是,，,，被转,换,换的字,符,符串必,须,须符合,数,数值类,型,型格式,。,。如果,被,被转换,的,的字符,串,串不符,合,合数值,型,型格式,，,，Oracle将抛出,

28、错,错误提,示,示。,selectto,_,_number('a')result fromdual;,14.3Oracle中的日,期,期函数,Oracle提供了,丰,丰富的,日,日期函,数,数。利,用,用日期,函,函数可,以,以灵活,的,的对日,期,期进行,运,运算。,14.3.1to_date()函数——将字符,串,串转换,为,为日期,型,型,to_date()函数用,于,于将字,符,符串转,换,换为日,期,期。被,转,转换的,字,字符串,必,必须符,合,合特定,的,的日期,格,格式。,selectto,_,_date(,','12,/,/02,/,/09,',',,','mm,/,/dd,

29、/,/yy,',')resultfromdual;,14.3.2add,_,_months()函数——为日期,加,加上特,定,定月份,add,_,_months()函数将,为,为日期,添,添加特,定,定月份,，,，并获,得,得新的,日,日期。,selectto,_,_char(add,_,_months(sysdate,,,, 2,),),,','yyyy-mm-dd',),) resultfrom dual,;,;,14.3.3last_day()函数——返回特,定,定日期,所,所在月,的,的最后,一,一天,last_day()函数将,接,接受一,个,个日期,参,参数。,该,该函数,首,首先

30、获,得,得日期,参,参数所,在,在月的,信,信息，,然,然后获,得,得该月,最,最后一,天,天的日,期,期。,selectto,_,_char(last_day(sysdate),,','yyyy,-,-mm,-,-dd,',')resultfromdual;,可以综,合,合利用add,_,_months()函数来,获,获得若,干,干月之,后,后的月,份,份的最,后,后一天,。,。,selectto,_,_char(last_day(add,_,_months(sysdate,,,, 3,),)),,','yyyy,-,-mm,-,-dd,',')resultfromdual;,14.3.4m

31、onths,_,_between()函数——返回两,个,个日期,所,所差的,月,月数,months,_,_between()函数用,于,于获取,两,两个日,期,期所间,隔,隔的月,数,数。该,函,函数的,返,返回值,是,是一个,实,实数。,selectmonths_between,(,(sysdate,to,_,_date(,','2009-02-08',,,, 'yyyy-mm-dd'),),) resultfrom dual,;,;,当第一,个,个日期,早,早于第,二,二个日,期,期，那,么,么返回,值,值将是,负,负值。,selectmonths_between,(,(to,_,_dat

32、e(,','2009-02-08',,,, 'yyyy-mm-dd'),,,, to_date,(,('2009,-,-03,-,-08,',',,','yyyy-mm-dd',),))resultfromdual;,14.3.5current_date()函数——返回当,前,前会话,时,时区的,当,当前日,期,期,current_date()函数用,于,于返回,当,当前会,话,话时区,的,的当前,日,日期。,selectsessiontimezone,to_char(current,_,_date,,','yyyy,-,-mm,-,-ddhh,:,:mi,:,:ss,',')resultfr

33、omdual;,注意与,说,说明：current_date等无参,数,数函数,作,作为Oracle的关键,字,字存在,。,。在使,用,用时，,不,不能为,其,其添加,小,小括号,。,。即selectcurrent_date()from dual是错误,的,的SQL语句。,14.3.6current_timestamp()函数——返回当,前,前会话,时,时区的,当,当前时,间,间戳,current_timestamp()函数用,于,于返回,当,当前会,话,话时的,区,区时间,戳,戳。可,以,以结合sessiontimezone来查看,其,其用法,。,。,selectsessiontimezone

34、,current_timestampfrom dual,;,;,14.3.7extract,(,()函数——返回日,期,期的某,个,个域,日期由,若,若干域,组,组成，,例,例如年,、,、月、,日,日、小,时,时等等,。,。extract()函数可,以,以返回,这,这些域,的,的具体,值,值。为,了,了使用,该,该函数,，,，除了,要,要指定,原,原日期,外,外，还,应,应该指,定,定要返,回,回的域,名,名。,selectextract(year fromsysdate)result fromdual;,需要注,意,意的是,，,，year、month、day域只能,从,从日期,（,（如sys

35、date）中获,得,得，而hour、minute、second只能从,时,时间型,（,（如systimestamp）中获,得,得。,14.4Oracle中的聚,合,合函数,所谓聚,合,合函数,是,是指针,对,对多条,记,记录的,函,函数。Oracle最常用,的,的聚合,函,函数包,括,括，max,(,()、min,(,()、avg()、sum,(,()和count(,),)函数。,本,本节将,讲,讲述这,些,些函数,的,的用法,。,。,14.4.1max(,),)函数——求最大,值,值,max,(,()函数用,于,于获得,记,记录集,在,在某列,的,的最大,值,值。例,如,如，为,了,了返回,

36、员,员工最,高,高工资,，,，可以,利,利用max,(,()函数。,selectmax(salary)max_salaryfrom t,_,_salary;,需要注,意,意的是,，,，聚合,函,函数往,往,往是返,回,回记录,集,集的统,计,计值，,因,因此，,不,不能与,其,其中的,单,单条记,录,录同时,出,出现。,例,例如，,不,不能将max,(,(salary)与具体,列,列一起,查,查询。,selectemployee,_,_id,,,, max(salary,),) max_salaryfromt_salary;,selectdistincte.employee_name,s.sa

37、lary,from t,_,_employees e,,,, t,_,_salary s,wheree.employee_id =s.employee_idands.salary,=,=,(,(select max(salary,),) fromt_salary,),),14.4.2min(,),)函数——求最小,值,值,min,(,()函数可,以,以用来,获,获得记,录,录集在,某,某列上,的,的最小,值,值，其,功,功能与max,(,()函数相,反,反。,selectdistincte.employee_name,s.salary,from t,_,_employees e,,,, t,_

38、,_salary s,wheree.employee_id =s.employee_idands.salary,=,=,(,(select min(salary,),) fromt_salary,),),14.4.3avg()函数——求平均,值,值,avg()函数用,于,于获得,记,记录集,在,在某列,上,上的平,均,均值。,selecte.employee_name,avg,(,(salary),from t,_,_employees e,,,, t,_,_salary s,wheree.employee_id =s.employee_id,groupbye.employee_id,e.em

39、ployee_name,14.4.4sum(,),)函数——求和,sum,(,()函数用,于,于获得,结,结果集,上,上某列,值,值的和,。,。,selecte.employee_name,sum,(,(salary),from t,_,_employees e,,,, t,_,_salary s,wheree.employee_id =s.employee_id,groupbye.employee_id,e.employee_name,14.4.5count()函数——获得记,录,录数,count(,),)函数的,作,作用对,象,象同样,为,为记录,集,集。与,其,其他聚,合,合函数,不,不

40、同的,是,是，count(,),)函数可,以,以有三,种,种方式,来,来进行,计,计数：count(,*,*)—,—,—计算行,数,数、count(column)——计算某,列,列和count(1)—,—,—累加1。,insertintot_employeesvalues,(,(16,null,null,null,),);,selectcount,(,(*)fromt_employees;,selectcount,(,(employee,_,_id,),) fromt_employees,;,;,selectcount,(,(employee,_,_name)fromt_employees;,

41、selectcount,(,(1)fromt_employees;,一般来,说,说，利,用,用count(1)进行计,数,数的速,度,度最快,，,，但是,特,特别注,意,意的是,，,，预期,的,的结果,是,是针对,整,整行数,据,据，还,是,是某列,的,的数据,。,。,,14.5Oracle中的其,他,他函数,除了数,值,值函数,、,、字符,串,串函数,、,、日期,函,函数和,聚,聚合函,数,数外，Oracle还提供,了,了其他,功,功能性,更,更强的,函,函数。,本,本节将,介,介绍decode,(,()、nvl()和cast()函数。,14.5.1decode(,),)函数——多值判,断,

42、断,decode,(,()函数用,于,于多值,判,判断。,其,其执行,过,过程类,似,似于解,码,码操作,。,。该函,数,数最常,见,见的应,用,用为，,实,实现类,似,似ifelse的功能,。,。例如,，,，可以,利,利用decode,(,()函数为,员,员工工,资,资添加,标,标识，,工,工资大,于,于6000者为高,收,收入，,其,其余的,为,为一般,收,收入。,selecte.employee_id,e.employee_name,decode,(,(sign(avg,(,(s.salary,),) -6000),,,,1,,','高,收,收入',,,, ',一,一般收,入,入')in

43、comming,from t,_,_employees e,,,, t,_,_salary s,wheree.employee_id =s.employee_id,groupbye.employee_id,e.employee_name,14.5.2nvl()函数——为空值,重,重新赋,值,值,nvl()函数用,于,于处理,某,某列的,值,值。该,函,函数有,两,两个参,数,数，第,一,一个参,数,数为要,处,处理的,列,列。如,果,果其值,为,为空，,则,则返回,第,第二个,参,参数的,值,值，否,则,则，将,返,返回列,值,值。,selectemployee,_,_id,,,, nvl(e

44、mployee_name,,','未知,',')employee_name fromt_employees,;,;,nvl,(,()函,数,数更常,见,见的用,途,途为判,断,断数值,是,是否为,空,空。因,为,为sum(),等,等函数,往,往往会,返,返回null,，,，例如,，,，表示,汇,汇率的,列,列一旦,为,为null，,那,那么最,终,终的货,币,币结算,额,额度也,为,为null，,所,所以，,必,必须对,汇,汇率列,进,进行nvl(,),)的处,理,理。在,统,统计员,工,工工资,时,时，null,同,同样是,不,不受欢,迎,迎的结,果,果，那,么,么可以,利,利用nvl(,)

45、,)函数,进,进行处,理,理。,selecte.employee_id,nvl(e,.,.employee,_,_name,,','未,知,知')employee,_,_name,nvl(sum(s.salary),,,, 0,),) salary,from t,_,_employees e,,,, t,_,_salary s,wheree.employee_id =s.employee_id(,+,+),groupbye.employee_id,e.employee_name,14.5.3cast,(,()函数——强制转,换,换数据,类,类型,cast()函数用,于,于强制,转,转换数,据,

46、据类型,。,。Oracle会根据,操,操作符,来,来自动,进,进行数,据,据类型,的,的转换,，,，例如,：,：,select,','123',+,+200result fromdual;,Oracle,会,会根据,运,运算符,“,“+”,将,将‘123’,转,转换为,数,数值型123,。,。,select,','123',|,||200 resultfrom dual,;,;,Oracle,会,会根据,运,运算符,“,“||,”,”将数,字,字200转换,为,为字符,串,串‘200’,。,。,cast(),函,函数最,常,常用的,场,场景是,转,转换列,的,的数据,类,类型，,以,以创建,新

47、,新表,createtabletmp_salaryas,selectcast(salary,_,_idasvarchar2,(,(20,),))salary,_,_id,,,,,cast(employee_id as varchar2(20))employee,_,_id,,,,,cast(month as varchar2(20))month,,,,,cast(salaryasvarchar2(20),),) salary,from t,_,_salary,desc tmp_salary,;,;,14.6Oracle中的运,算,算表达,式,式,Oracle中的常,用,用运算,包,包括：,数,

48、数学运,算,算、逻,辑,辑运算,和,和按位,运,运算。,本,本节将,通,通过范,例,例着重,讲,讲述这,三,三种运,算,算的常,用,用运算,符,符和运,算,算规则,。,。,14.6.1数学运,算,算,数学运,算,算是最,常,常用的,运,运算方,式,式，Oracle中的数,学,学运算,符,符包括,：,：+、-、*、/，分别,代,代表了,加,加、减,、,、乘除,运,运算。,在,在使用,数,数学运,算,算时，Oracle会自动,将,将其他,数,数据类,型,型转换,为,为数值,型,型，然,后,后再参,与,与运算,。,。,select5+3 resultfrom dual,;,;,,select5-3

49、resultfrom dual,;,;,,select5*2resultfromdual;,,select5/2 resultfrom dual,;,;,,需要注,意,意的是,，,，任何,一,一种运,算,算符与null的运算,结,结果均,为,为null。,select5+null resultfrom dual,;,;,,select5-null resultfrom dual,;,;,,select5*null resultfrom dual,;,;,,select5/null resultfrom dual,;,;,14.6.2逻辑运,算,算,Oracle中的逻,辑,辑运算,包,包括：,>

50、：大于,运,运算，,可,可用于,数,数值型,、,、日期,型,型和字,符,符串类,型,型；,>=：大于,等,等于运,算,算，可,用,用于数,值,值型、,日,日期型,和,和字符,串,串类型,；,；,<：小于,运,运算，,可,可用于,数,数值型,、,、日期,型,型和字,符,符串类,型,型；,<=：大于,等,等于运,算,算，可,用,用于数,值,值型、,日,日期型,和,和字符,串,串类型,；,；,=：等于,，,，可用,于,于数值,型,型、日,期,期型和,字,字符串,类,类型；,：不等,于,于，可,用,用于数,值,值型、,日,日期型,和,和字符,串,串类型,；,；,!=：与用法相,同,同；,NOT：取反,

51、操,操作；,AND：布尔,值,值的与,操,操作；,OR：布尔,值,值的或,操,操作。,14.6.2逻辑运,算,算,需要注,意,意的是,，,，Oracle中的逻,辑,辑运算,符,符只能,作,作为条,件,件判断,，,，并不,返,返回值,。,。为了,查,查询工,资,资在5000-7000之间的,记,记录，,可,可以利,用,用逻辑,运,运算符,来,来组合,查,查询条,件,件。,select,*,*from t,_,_salary where salary>,=,=5000andsalary<=7000;,对于null值，需,要,要特别,注,注意的,是,是，无,论,论使用,哪,哪种运,算,算符，,结,结

52、果都,会,会返回null。当比,较,较的结,果,果为null，并作,为,为条件,出,出现时,，,，Oracle都会将,其,其解释,为,为false。,select1resultfromdual where 1,=,=null;,select1resultfromdual where 1,<,null,;,;,select1resultfromdual where null,=,=null;,select1resultfromdual where null,<,null,;,;,14.6.3位运算,从Oracle8i开始，,系,系统已,经,经提供,了,了位运,算,算符。,最,最常用,的,的莫过,

53、于,于bitand运算符,。,。,selectbitand(192,100)resultfromdual;,14.7Oracle中的特,殊,殊判式,除了逻,辑,辑运算,之,之外，Oracle提供了,一,一些特,殊,殊判式,。,。这些,判,判式可,以,以用来,生,生成更,加,加复杂,和,和灵活,的,的查询,条,条件。,本,本节将,着,着重介,绍,绍以下,几,几种判,式,式。,Between：取值,范,范围。,In：集合,成,成员测,试,试。,Like：模式,匹,匹配。,isnull：空值,判,判断。,all，some，any：数量,判,判断。,exists：存在,性,性判断,。,。,14.7.1

54、between,—,——范围测,试,试,between判式，,用,用于判,断,断某个,值,值是否,在,在另外,两,两个值,之,之间。,这,这些值,可,可以为,数,数值型,、,、字符,串,串和日,期,期型。,使,使用betwwen判式来,获,获得ID号在1-5之间的,员,员工信,息,息。,select,*,*from t,_,_employees where employee_id between1and5;,betwwen判式,同,同样可,以,以应用,于,于字符,串,串和日,期,期型。,字,字符串,是,是按照,字,字母表,的,的顺序,进,进行比,较,较，而,日,日期型,是,是按照,日,日期的,

55、先,先后顺,序,序进行,比,比较。,select,*,*from t,_,_employees where 'b'between 'b'and,','c,',';,select,*,*from t,_,_employees where 'b'between 'bc'and 'c';,注意与,说,说明：between判式与>=、<=的组合,是,是等价,关,关系。,但,但是，,效,效率上,要,要比后,者,者差。,14.7.2in——集合成,员,员测试,in用于判,断,断某个,值,值是否,一,一个集,合,合的成,员,员。,select,*,*from t,_,_employees where sta

56、tusin(,','NEW',,','ACT',),);,值得注,意,意的是,，,，in判式中,的,的集合,的,的成员,的,的数据,类,类型可,以,以不一,致,致，例,如,如，select,*,*fromt_employeeswherestatusin(,','NEW',,','ACT',sysdate, 1,),)中的数,据,据类型,包,包含了,字,字符串,、,、日期,型,型和数,值,值型。,14.7.3like,—,——模式匹,配,配,like判式的,最,最大特,点,点在于,，,，可以,使,使用通,配,配符。,其,其通常,的,的应用,场,场景为,处,处理模,糊,糊查询,。,。,select

57、,*,*from t,_,_employees where employee_namelike,','钟%,',';,如果要,求,求字符,串,串中含,有,有原义,字,字符“%”，例如,，,，含有,百,百分比,的,的字符,串,串。那,么,么，like判式应,写,写作：like '钟\%'escape ',\,\'。Oracle会首先,解,解释escape关键字,，,，并将,其,其后的,字,字符“\”解释为,转,转义字,符,符。那,么,么在“,钟,钟\%”中的“%”不再表,示,示通配,符,符，而,是,是表示,原,原义字,符,符“%”。,“_”（下划,线,线）是,可,可用于like判式的,另,另一个

58、,通,通配符,，,，该通,配,配符表,示,示一个,任,任意的,字,字符。,14.7.4is null,—,——空值判,断,断,在逻辑,判,判断中,，,，对于,列,列值为,空,空的判,断,断，不,能,能使用=或者。oracle对与空,值,值的判,断,断提供,了,了专门,的,的判式——is null。例如,，,，为了,获,获取表t_employees中员工,信,信息不,全,全的记,录,录，可,以,以利用,如,如下所,示,示的查,询,询语句,。,。,select,*,*from t,_,_employees,whereemployee_idisnull,oremployee_name is null

59、,orwork_years is null,orstatusisnull;,14.7.5exists—,—,—存在性,判,判断,in判式用,于,于判断,表,表的列,值,值是否,存,存在于,列,列表（,集,集合）,中,中。而exists判式则,可,可用于,判,判断查,询,询结果,集,集合是,否,否为空,。,。例如,，,，为了,查,查询出,表,表t_employees所存储,的,的员工,信,信息中,，,，哪些,员,员工存,在,在于工,资,资表中,，,，即可,利,利用exists判式。,select,*,*from t,_,_employees e,whereexists,(,(select *fr

60、omt_salarywhereemployee_id,=,= e,.,.employee,_,_id,),);,14.7.6all，some，any,—,——数量判,断,断,all，some和any判式的,作,作用对,象,象为记,录,录集合,。,。all表示，,记,记录集,中,中的所,有,有记录,，,，some表示其,中,中的一,些,些记录,，,，any判式则,表,表示其,中,中的任,意,意记录,。,。例如,，,，在员,工,工工资,表,表t_salary中，为,了,了查找,高,高于id为4和5的工资,信,信息，,即,即可使,用,用all判式。,select,*,*from t,_,_salar

61、y where employee_id =4oremployee_id,=,= 5,;,;,select,*,*from t,_,_salary where salary,>,> all(selectdistinctsalary fromt_salarywhereemployee,_,_id,=,=4 or employee_id =5),;,;,select,*,*from t,_,_salary where salary,>,> some,(,(select distinct salaryfrom t,_,_salary where employee_id =4oremployee_id,

62、=,= 5,),);,此时的some判式实,际,际相当,于,于逻辑,运,运算中,的,的or运算，,即,即salary,>,>6000orsalary,>,>7000。此时,，,，使用any判式，,将,将返回,同,同样的,结,结果。,14.8Oracle高级函,数,数——分析函,数,数与窗,口,口函数,Oracle中的分,析,析函数,具,具有非,常,常强大,的,的功能,。,。分析,函,函数往,往,往与另,一,一类函,数,数——窗口函,数,数同时,使,使用。,窗,窗口函,数,数总是,为,为查询,过,过程中,的,的当前,记,记录提,供,供一个,相,相关记,录,录集，,而,而且随,着,着当前,记,记录

63、的,推,推移，,相,相应的,记,记录集,也,也会随,之,之改变,，,，这非,常,常类似,于,于“滑,动,动窗”,的,的概念,。,。分析,函,函数的,操,操作对,象,象即为,“,“滑动,窗,窗”所,指,指定的,记,记录集,合,合。本,节,节将通,过,过实例,来,来讲述,分,分析函,数,数和窗,口,口函数,的,的使用,。,。,14.8.1排名,分析函,数,数中的,排,排名函,数,数可以,针,针对窗,口,口中的,记,记录生,成,成排序,序,序号。,常,常用的,排,排名函,数,数有rank()、dense_rank()和row,_,_number()。,rank()函数用,于,于返回,当,当前记,录,

64、录在窗,口,口函数,所,所指定,的,的记录,集,集中的,排,排名。rank()函数在,排,排名过,程,程中，,具,具有跳,跃,跃的特,点,点。,select,*,*fromstudents;,selectstudent_name,rank()over(orderbystudent_age)positionfromstudents;,selectstudent_name,dense_rank()over(orderbystudent_age)positionfromstudents;,selectstudent_name,row,_,_number()over(orderbystudent_ag

65、e)positionfromstudents;,14.8.2分区窗,口,口,对于窗,口,口函数,，,，利用partitionby关键字,可,可以指,定,定分区,窗,窗口。现欲统,计,计各员,工,工的工,资,资在各,自,自部门,的,的高低,情,情况，,则,则可以,利,利用partitionby进行分,区,区，然,后,后利用,分,分析函,数,数对分,区,区内的,记,记录进,行,行统计,selectt.,*,*,dense_rank()over(partitionbydepartmentorderbysalary,),) position fromsalary torderbyt.employee_

66、id,另外一,种,种常见,需,需求为,，,，在获,得,得员工,工,工资的,同,同时，,也,也需要,部,部门所,有,有员工,的,的工资,总,总额,selectt.,*,*,,sum,(,(salary)over(partitionbydepartment)total_salary,,,,,round(avg,(,(salary)over(partitionbydepartment))average_salaryfromsalary torderbyemployee,_,_id,注意，avg,(,(salary)over(partitionbydepartment)是不可,分,分割的,一,一个整,体,体。对,于,于数据,表,表salary中每条,记,记录都,会,会返回,单,单个值,，,，因此,，,，当使,用,用round(,),)函数，,函,函数的,作,作用对,象,象应为avg,(,(salary)over(partitionbydepartment)这个整,体,体，而,不,不能使,用,用诸如round(avg,(,(salary))over(partitionbydepartment)等