14章Oracle中的函数与表达式

,Click to edit Master title style,Click to edit Master text styles,第14章 Oracle中的函数与表达式,Oracle中提供了大量的内置函数，以处理各种形式的运算。这些函数涵盖了字符串运算、数值运算、日期运算等方面。同样，Oracle允许使用数值运算、逻辑运算等基本的表达式运算，另外，提供了SQL标准所规定的特殊判式。,Oracle中的字符串函数；,Oracle中的数学函数；,Oracle中的日期函数；,第14章Oracle中的函,数,数与表,达,达式,Oracle中的聚,合,合函数,；,；,Oracle中的运,算,算表达,式,式；,Oracle中的特,殊,殊判式,；,；,Oracle中的高,级,级函数分析函,数,数与窗,口,口函数,。,。,14.1Oracle中的字,符,符串函,数,数,Oracle提供了,丰,丰富的,字,字符串,函,函数，,本,本小节,将,将通过,实,实例讲,述,述Oracle中各字,符,符串函,数,数的使,用,用。,14.1.1lpad()函数,lpad()函数用,于,于左补,全,全字符,串,串。在,某,某些情,况,况下，,预,预期的,字,字符串,为,为固定,长,长度，,而,而且格,式,式统一,，,，此时,可,可以考,虑,虑使用lpad()函数。,例,例如，,深,深市股,票,票代码,都,都以0开头，,并,并且都,为,为6位，可,以,以利用lpad格式化,股,股票代,码,码，以,保,保证股,票,票代码,的,的格式,。,。,selectlpad(,21,6,0,)stock_code fromdual;,需要注,意,意的是,，,，当原,字,字符串,的,的长度,大,大于预,期,期长度,时,时，实,际,际进行,的,的是截,取,取字符,串,串操作,。,。,selectlpad(,1234567,6,0,),) stock_codefromdual;,14.1.2rpad()函数,与lpad()函数相,反,反，rpad()函数从,右,右端补,齐,齐字符,串,串。,selectrpad(,abc,10, ,*,*)fromdual;,注意与,说,说明：lpad()和rpad()都用于,填,填充字,符,符串，lpad()从左端,进,进行填,充,充，而rpad()从右端,进,进行填,充,充，但,是,是，二,者,者在最,终,终截取,字,字符串,时,时，都,是,是从左,端,端开始,截,截取。,selectrpad(,abcdefg, 6, ,*,*)fromdual;,14.1.3lower()函数返回小,写,写字符,串,串,lower(,),)函数用,于,于返回,字,字符串,的,的小写,形,形式。lower(,),)函数在,查,查询语,句,句中经,常,常扮演,重,重要角,色,色。例,如,如，对,于,于用户,名,名和密,码,码的校,验,验来说,，,，用户,名,名一般,并,并不区,分,分大小,写,写，用,户,户无论,输,输入了,大,大写还,是,是小写,形,形式，,都,都被认,为,为是合,法,法用户,。,。因此,，,，在数,据,据库查,询,询时，,应,应该将,数,数据库,中,中用户,名,名与用,户,户输入,的,的用户,名,名进行,统,统一。,selectuser_id,user_name fromt_userswherelower(user_name,),) =lower,(,(Alex,);,14.1.4upper()函数返回大,写,写字符,串,串,upper(,),)函数用,于,于返回,字,字符串,的,的大写,形,形式。,与,与lower(,),)函数类,似,似，upper(,),)函数也,可,可以用,在,在查询,语,语句中,，,，以统,一,一数据,库,库和查,询,询条件,的,的一致,性,性。,selectuser_id,user_name fromt_userswhereupper(user_name,),) =upper,(,(ALEX,);,注意与,说,说明：upper(,),)函数和lower(,),)函数只,针,针对英,文,文字符,其,其作用,，,，因为,只,只有英,文,文字符,才,才有大,小,小写之,分,分。,14.1.5initcap()函数单词首,字,字母大,写,写,initcap()函数将,单,单词的,首,首字母,大,大写。,selectinitcap(,big)fromdual;,需要注,意,意的是,，,，initcap()函数不,能,能自动,识,识别单,词,词,selectinitcap(,bigbigtiger,)from dual,;,;,initcap(),函,函数会,将,将参数,中,中的非,单,单词字,符,符作为,单,单词分,隔,隔符,selectinitcap(,big_big_tiger,),) fromdual;,selectinitcap(,big/big/tiger,),) fromdual;,selectinitcap(,big bigtiger,),) fromdual;,14.1.6length(,),)函数返回字,符,符串长,度,度,length,(,()函数用,于,于返回,字,字符串,的,的长度,。,。,selectlength(abcd,),) fromdual;,空字符,串,串的长,度,度不是0，而是null。因为,空,空字符,串,串被视,作,作null，所以,，,，length,(,(null)返回的,仍,仍然是null。,selectlength(,)from dual,;,;,对其其,他,他数据,类,类型，,照,照样可,以,以通过length,(,()函数来,获,获得其,长,长度。length,(,()函数会,首,首先将,参,参数转,换,换为字,符,符串，,然,然后计,算,算其长,度,度。,selectlength(12.51)from dual,;,;,14.1.7substr()函数截取字,符,符串,substr()函数用,于,于截取,字,字符串,。,。该函,数,数可以,指,指定截,取,取的起,始,始位置,，,，截取,长,长度，,可,可以实,现,现灵活,的,的截取,操,操作，,因,因此，,成,成为字,符,符串操,作,作中最,常,常用的,函,函数之,一,一。,例如，,对,对于字,符,符串“1234567890”，现欲,截,截取自,第,第5位开始,的,的4个字符,。,。,selectsubstr(1234567890,5,4)fromdual;,需要注,意,意的是,，,，Oracle中字符,位,位置从1开始，,而,而不是,像,像某些,编,编程语,言,言（如Java）那样,从,从0开始。,如果不,指,指定长,度,度，那,么,么substr,(,()函数将,获,获取起,始,始位置,参,参数至,字,字符串,结,结尾处,的,的所有,字,字符。,selectsubstr(1234567890,5)fromdual;,14.1.8instr()函数获得字,符,符串出,现,现的位,置,置,instr()函数用,于,于获得,子,子字符,串,串在父,字,字符串,中,中出现,的,的位置,。,。,selectinstr,(,(bigbigtiger,big)fromdual;,可以指,定,定额外,的,的参数,，,，以命,令,令该函,数,数从指,定,定位置,开,开始搜,索,索。,selectinstr,(,(bigbigtiger,big,2)fromdual;,还可以,指,指定出,现,现次数,参,参数，,以,以指定,是,是第几,次,次搜索,到,到子字,符,符串。,selectinstr,(,(bigbigtiger,big,2,2)fromdual;,14.1.9ltrim()函数删除字,符,符串首,部,部空格,ltrim()中的l代表left。该函,数,数用于,删,删除字,符,符串左,端,端的空,白,白符。,selectltrim,(,(abc)fromdual;,需要注,意,意的是,，,，空白,符,符不仅,仅,仅包括,了,了空格,符,符，还,包,包括TAB键、回,车,车符和,换,换行符,。,。,14.1.10rtrim()函数删除字,符,符串尾,部,部空格,rtrim()中的r代表right。该函,数,数用于,删,删除字,符,符串右,端,端空白,符,符。删,除,除字符,串,串首尾,空,空白符,可,可以结,合,合使用ltrm()和rtrim()函数。,selectrtrim,(,(ltrim,(,(abc,),)from dual,;,;,14.1.11trim()函数删除字,符,符串首,尾,尾空格,trim()函数可,用,用于删,除,除首尾,空,空格，,相,相当于ltrim()和rtrim()的组合,。,。,selecttrim(,abc,)fromdual;,14.1.12to_char()函数将其他,类,类型转,换,换为字,符,符类型,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.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码转换,为,为字符,串,串,chr()函数用,于,于将ascii码转换,为,为字符,串,串。通,过,过chr()函数，,可,可以对,不,不宜直,接,接输入,的,的字符,进,进行操,作,作。例,如,如，将,回,回车换,行,行符插,入,入到数,据,据中。,insertintotest_datavalues (6,周林,|chr,(,(13,),)|chr,(,(10,),)|,梁,梁军, 20);,select,*,*from test,_,_datawhereid,=,= 6,;,;,14.1.14translate,(,()函数替换字,符,符,translate,(,()函数用,于,于替换,字,字符串,。,。替换,的,的规则,类,类似于,翻,翻译的,过,过程。,selecttranslate(56338, 1234567890,avlihemoqr,)resultfromdual;,需要注,意,意的是,，,，当字,符,符不能,被,被成功,“,“翻译,”,”，那,么,么，Oracle将使用,空,空字符,替,替换它,。,。利用,此,此特性,，,，可以,使,使用translate,(,()函数来,删,删除一,个,个含有,数,数字和,英,英文字,母,母的字,符,符串中,的,的所有,字,字母：,selecttranslate(21343yuioioizf899dasiwpe58595oda0j098,#abcdefghijklmnopqrstuvwxyz,)reulst,from dual,;,;,14.2Oracle中的数,学,学函数,Oracle提供的,数,数学函,数,数可以,处,处理日,常,常使用,到,到的大,多,多数数,学,学运算,。,。本小,节,节将讲,述,述Oracle中常用,的,的几种,数,数学函,数,数。,14.2.1abs,(,()函数返回数,字,字的绝,对,对值,abs,(,()函数的,参,参数只,能,能是数,值,值型，,该,该参数,用,用于返,回,回参数,的,的绝对,值,值。,selectabs(-2.1,),) fromdual;,14.2.2round (,),)函数返回数,字,字的“,四,四舍五,入,入”值,round(,),)函数用,于,于返回,某,某个数,字,字的四,舍,舍五入,值,值。为,了,了使用,该,该函数,，,，除了,提,提供原,始,始值之,外,外，还,应,应提供,精,精确到,的,的位数,。,。精确,位,位数可,以,以为正,整,整数、0和负整,数,数。,selectround,(,(2745.173, 2,),) resultfrom dual,;,;,如果不,使,使用第,二,二个参,数,数，那,么,么，相,当,当于使,用,用了参,数,数0，即精,确,确到整,数,数。,selectround,(,(2745.173,),) resultfrom dual,;,;,如果第,二,二个参,数,数为负,数,数，那,么,么，相,当,当于将,数,数值精,确,确到小,数,数点之,前,前的位,数,数。,selectround,(,(2745,-,-1,),) resultfrom dual,;,;,14.2.3ceil,(,()函数向上取,整,整,ceil()函数只,能,能有一,个,个参数,。,。该函,数,数将参,数,数向上,取,取整，,以,以获得,大,大于等,于,于该参,数,数的最,小,小整数,。,。,selectceil(21.897,),) resultfrom dual,;,;,需要注,意,意的是,该,该函数,针,针对负,数,数的运,算,算：,selectceil(,-,-21,.,.897)resultfromdual;,因为ceil()函数返,回,回的是,大,大于等,于,于参数,的,的最小,整,整数，,所,所以，,该,该函数,返,返回的,并,并非-22，而是-21。,14.2.4floor()函数向下取,整,整,与ceil函数相,反,反，floor(,),)函数用,于,于返回,小,小于等,于,于某个,数,数值的,最,最大整,数,数。,selectfloor,(,(21,.,.897)resultfromdual;,selectfloor,(,(-21.897)result fromdual;,14.2.5mod,(,()函数取模操,作,作,mod,(,()函数有,两,两个参,数,数，第,一,一个参,数,数为被,除,除数，,第,第二个,参,参数为,除,除数。mod,(,()函数的,实,实际功,能,能为获,得,得两数,相,相除之,后,后的余,数,数。,selectmod(5,2)result fromdual;,14.2.6sign,(,()函数返回数,字,字的正,负,负性,sign()函数只,有,有一个,参,参数。,该,该函数,将,将返回,参,参数的,正,正负性,。,。若返,回,回值为1，表示,该,该参数,大,大于0；若返,回,回值为-1，表示,该,该参数,小,小于0；若返,回,回值为0，表示,该,该参数,等,等于0。,selectsign(8)resultfromdual;,selectsign(,-,-8)result fromdual;,selectsign(0)resultfromdual;,sign()函数为,判,判断两,个,个数值,的,的大小,关,关系提,供,供了方,便,便。因,为,为在oracle中，利,用,用类似ifelse的结构,来,来判断,两,两个数,值,值之间,的,的大小,关,关系，,并,并不像,编,编程语,言,言中那,样,样方便,，,，而且,极,极易造,成,成代码,的,的复杂,化,化。,14.2.7sqrt()函数返回数,字,字的平,方,方根,sqrt()函数也,只,只有一,个,个参数,。,。该函,数,数用于,返,返回参,数,数的平,方,方根。,可,可以利,用,用round(,),)函数和sqrt()函数返,回,回某个,数,数值的,近,近似平,方,方根。,selectround,(,(sqrt(2),3)result fromdual;,14.2.8power()函数乘方运,算,算,power(,),)函数有,两,两个参,数,数。该,函,函数用,于,于实现,数,数值的,乘,乘方运,算,算。,selectpower,(,(6,2)result fromdual;,14.2.9trunc()函数截取数,字,字,trunc()函数用,于,于截取,部,部分数,字,字。其,工,工作机,制,制非常,类,类似于round(,),)函数。,与,与round(,),)函数不,同,同的是,，,，该函,数,数不对,数,数值做,四,四舍五,入,入处理,，,，而是,直,直接截,取,取。,selecttrunc,(,(2745.173, 2,),) resultfrom dual,;,;,保留位,数,数的值,可,可以为0，当该,参,参数的,值,值为0时，将,保,保留到,整,整数。,selecttrunc,(,(2745.173,),) resultfrom dual,;,;,当保留,位,位数小,于,于0时，表,示,示保留,到,到小数,点,点之前,的,的位数,。,。,selecttrunc,(,(2745.173, -1)resultfromdual;,14.2.10vsize()函数返回数,据,据的存,储,储空间,vsize()函数根,据,据数据,库,库的存,储,储格式,，,，来返,回,回其所,占,占用的,存,存储空,间,间的字,节,节数。,selectvsize,(,(abc123,),) fromdual;,注意与,说,说明：vsize()函数在,返,返回的,是,是Oracle实际存,储,储数据,的,的字节,数,数，在,实,实际开,发,发中使,用,用的几,率,率也较,小,小。读,者,者可以,不,不必了,解,解Oracle本身的,存,存储机,制,制。,14.2.11to_number()函数将字符,串,串转换,为,为数值,类,类型,to_number()函数可,以,以将字,符,符串转,换,换为数,值,值型。,selectto,_,_number(257,.,.90,)resultfromdual;,需要注,意,意的是,，,，被转,换,换的字,符,符串必,须,须符合,数,数值类,型,型格式,。,。如果,被,被转换,的,的字符,串,串不符,合,合数值,型,型格式,，,，Oracle将抛出,错,错误提,示,示。,selectto,_,_number(a)result fromdual;,14.3Oracle中的日,期,期函数,Oracle提供了,丰,丰富的,日,日期函,数,数。利,用,用日期,函,函数可,以,以灵活,的,的对日,期,期进行,运,运算。,14.3.1to_date()函数将字符,串,串转换,为,为日期,型,型,to_date()函数用,于,于将字,符,符串转,换,换为日,期,期。被,转,转换的,字,字符串,必,必须符,合,合特定,的,的日期,格,格式。,selectto,_,_date(,12,/,/02,/,/09,mm,/,/dd,/,/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()函数将,接,接受一,个,个日期,参,参数。,该,该函数,首,首先获,得,得日期,参,参数所,在,在月的,信,信息，,然,然后获,得,得该月,最,最后一,天,天的日,期,期。,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.4months,_,_between()函数返回两,个,个日期,所,所差的,月,月数,months,_,_between()函数用,于,于获取,两,两个日,期,期所间,隔,隔的月,数,数。该,函,函数的,返,返回值,是,是一个,实,实数。,selectmonths_between,(,(sysdate,to,_,_date(,2009-02-08, yyyy-mm-dd),),) resultfrom dual,;,;,当第一,个,个日期,早,早于第,二,二个日,期,期，那,么,么返回,值,值将是,负,负值。,selectmonths_between,(,(to,_,_date(,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,)resultfromdual;,注意与,说,说明：current_date等无参,数,数函数,作,作为Oracle的关键,字,字存在,。,。在使,用,用时，,不,不能为,其,其添加,小,小括号,。,。即selectcurrent_date()from dual是错误,的,的SQL语句。,14.3.6current_timestamp()函数返回当,前,前会话,时,时区的,当,当前时,间,间戳,current_timestamp()函数用,于,于返回,当,当前会,话,话时的,区,区时间,戳,戳。可,以,以结合sessiontimezone来查看,其,其用法,。,。,selectsessiontimezone,current_timestampfrom dual,;,;,14.3.7extract,(,()函数返回日,期,期的某,个,个域,日期由,若,若干域,组,组成，,例,例如年,、,、月、,日,日、小,时,时等等,。,。extract()函数可,以,以返回,这,这些域,的,的具体,值,值。为,了,了使用,该,该函数,，,，除了,要,要指定,原,原日期,外,外，还,应,应该指,定,定要返,回,回的域,名,名。,selectextract(year fromsysdate)result fromdual;,需要注,意,意的是,，,，year、month、day域只能,从,从日期,（,（如sysdate）中获,得,得，而hour、minute、second只能从,时,时间型,（,（如systimestamp）中获,得,得。,14.4Oracle中的聚,合,合函数,所谓聚,合,合函数,是,是指针,对,对多条,记,记录的,函,函数。Oracle最常用,的,的聚合,函,函数包,括,括，max,(,()、min,(,()、avg()、sum,(,()和count(,),)函数。,本,本节将,讲,讲述这,些,些函数,的,的用法,。,。,14.4.1max(,),)函数求最大,值,值,max,(,()函数用,于,于获得,记,记录集,在,在某列,的,的最大,值,值。例,如,如，为,了,了返回,员,员工最,高,高工资,，,，可以,利,利用max,(,()函数。,selectmax(salary)max_salaryfrom t,_,_salary;,需要注,意,意的是,，,，聚合,函,函数往,往,往是返,回,回记录,集,集的统,计,计值，,因,因此，,不,不能与,其,其中的,单,单条记,录,录同时,出,出现。,例,例如，,不,不能将max,(,(salary)与具体,列,列一起,查,查询。,selectemployee,_,_id, max(salary,),) max_salaryfromt_salary;,selectdistincte.employee_name,s.salary,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,_,_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.employee_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(,),)函数的,作,作用对,象,象同样,为,为记录,集,集。与,其,其他聚,合,合函数,不,不同的,是,是，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;,selectcount,(,(1)fromt_employees;,一般来,说,说，利,用,用count(1)进行计,数,数的速,度,度最快,，,，但是,特,特别注,意,意的是,，,，预期,的,的结果,是,是针对,整,整行数,据,据，还,是,是某列,的,的数据,。,。,14.5Oracle中的其,他,他函数,除了数,值,值函数,、,、字符,串,串函数,、,、日期,函,函数和,聚,聚合函,数,数外，Oracle还提供,了,了其他,功,功能性,更,更强的,函,函数。,本,本节将,介,介绍decode,(,()、nvl()和cast()函数。,14.5.1decode(,),)函数多值判,断,断,decode,(,()函数用,于,于多值,判,判断。,其,其执行,过,过程类,似,似于解,码,码操作,。,。该函,数,数最常,见,见的应,用,用为，,实,实现类,似,似ifelse的功能,。,。例如,，,，可以,利,利用decode,(,()函数为,员,员工工,资,资添加,标,标识，,工,工资大,于,于6000者为高,收,收入，,其,其余的,为,为一般,收,收入。,selecte.employee_id,e.employee_name,decode,(,(sign(avg,(,(s.salary,),) -6000),1,高,收,收入, ,一,一般收,入,入)incomming,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(employee_name,未知,)employee_name fromt_employees,;,;,nvl,(,()函,数,数更常,见,见的用,途,途为判,断,断数值,是,是否为,空,空。因,为,为sum(),等,等函数,往,往往会,返,返回null,，,，例如,，,，表示,汇,汇率的,列,列一旦,为,为null，,那,那么最,终,终的货,币,币结算,额,额度也,为,为null，,所,所以，,必,必须对,汇,汇率列,进,进行nvl(,),)的处,理,理。在,统,统计员,工,工工资,时,时，null,同,同样是,不,不受欢,迎,迎的结,果,果，那,么,么可以,利,利用nvl(,),)函数,进,进行处,理,理。,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()函数用,于,于强制,转,转换数,据,据类型,。,。Oracle会根据,操,操作符,来,来自动,进,进行数,据,据类型,的,的转换,，,，例如,：,：,select,123,+,+200result fromdual;,Oracle,会,会根据,运,运算符,“,“+”,将,将123,转,转换为,数,数值型123,。,。,select,123,|,|200 resultfrom dual,;,;,Oracle,会,会根据,运,运算符,“,“|,”,”将数,字,字200转换,为,为字符,串,串200,。,。,cast(),函,函数最,常,常用的,场,场景是,转,转换列,的,的数据,类,类型，,以,以创建,新,新表,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中的常,用,用运算,包,包括：,数,数学运,算,算、逻,辑,辑运算,和,和按位,运,运算。,本,本节将,通,通过范,例,例着重,讲,讲述这,三,三种运,算,算的常,用,用运算,符,符和运,算,算规则,。,。,14.6.1数学运,算,算,数学运,算,算是最,常,常用的,运,运算方,式,式，Oracle中的数,学,学运算,符,符包括,：,：+、-、*、/，分别,代,代表了,加,加、减,、,、乘除,运,运算。,在,在使用,数,数学运,算,算时，Oracle会自动,将,将其他,数,数据类,型,型转换,为,为数值,型,型，然,后,后再参,与,与运算,。,。,select5+3 resultfrom dual,;,;,select5-3 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中的逻,辑,辑运算,包,包括：,：大于,运,运算，,可,可用于,数,数值型,、,、日期,型,型和字,符,符串类,型,型；,=：大于,等,等于运,算,算，可,用,用于数,值,值型、,日,日期型,和,和字符,串,串类型,；,；,：小于,运,运算，,可,可用于,数,数值型,、,、日期,型,型和字,符,符串类,型,型；,=,=5000andsalary=7000;,对于null值，需,要,要特别,注,注意的,是,是，无,论,论使用,哪,哪种运,算,算符，,结,结果都,会,会返回null。当比,较,较的结,果,果为null，并作,为,为条件,出,出现时,，,，Oracle都会将,其,其解释,为,为false。,select1resultfromdual where 1,=,=null;,select1resultfromdual where 1,null,;,;,select1resultfromdual where null,=,=null;,select1resultfromdual where null,=、, 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,=,= 5,),);,此时的some判式实,际,际相当,于,于逻辑,运,运算中,的,的or运算，,即,即salary,6000orsalary,7000。此时,，,，使用any判式，,将,将返回,同,同样的,结,结果。,14.8Oracle高级函,数,数分析函,数,数与窗,口,口函数,Oracle中的分,析,析函数,具,具有非,常,常强大,的,的功能,。,。分析,函,函数往,往,往与另,一,一类函,数,数窗口函,数,数同时,使,使用。,窗,窗口函,数,数总是,为,为查询,过,过程中,的,的当前,记,记录提,供,供一个,相,相关记,录,录集，,而,而且随,着,着当前,记,记录的,推,推移，,相,相应的,记,记录集,也,也会随,之,之改变,，,，这非,常,常类似,于,于“滑,动,动窗”,的,的概念,。,。分析,函,函数的,操,操作对,象,象即为,“,“滑动,窗,窗”所,指,指定的,记,记录集,合,合。本,节,节将通,过,过实例,来,来讲述,分,分析函,数,数和窗,口,口函数,的,的使用,。,。,14.8.1排名,分析函,数,数中的,排,排名函,数,数可以,针,针对窗,口,口中的,记,记录生,成,成排序,序,序号。,常,常用的,排,排名函,数,数有rank()、dense_rank()和row,_,_number()。,rank()函数用,于,于返回,当,当前记,录,录在窗,口,口函数,所,所指定,的,的记录,集,集中的,排,排名。rank()函数在,排,排名过,程,程中，,具,具有跳,跃,跃的特,点,点。,select,*,*fromstudents;,selectstudent_name,rank()over(orderbystudent_age)positionfromstudents;,selectstudent_name,dense_rank()over(orderbystudent_age)positionfromstudents;,selectstudent_name,row,_,_number()over(orderbystudent_age)positionfromstudents;,14.8.2分区窗,口,口,对于窗,口,口函数,，,，利用partitionby关键字,可,可以指,定,定分区,窗,窗口。现欲统,计,计各员,工,工的工,资,资在各,自,自部门,的,的高低,情,情况，,则,则可以,利,利用partitionby进行分,区,区，然,后,后利用,分,分析函,数,数对分,区,区内的,记,记录进,行,行统计,selectt.,*,*,dense_rank()over(partitionbydepartmentorderbysalary,),) position fromsalary torderbyt.employee_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)等