版权说明:本文档由用户提供并上传,收益归属内容提供方,若内容存在侵权,请进行举报或认领
文档简介
第14章Oracle中的函数与表达式Oracle中提供了大量的内置函数,以处理各种形式的运算。这些函数涵盖了字符串运算、数值运算、日期运算等方面。同样,Oracle允许使用数值运算、逻辑运算等基本的表达式运算,另外,提供了SQL标准所规定的特殊判式。Oracle中的字符串函数;Oracle中的数学函数;Oracle中的日期函数;第14章Oracle中的函数与表达式Oracle中提供了第14章Oracle中的函数与表达式Oracle中的聚合函数;Oracle中的运算表达式;Oracle中的特殊判式;Oracle中的高级函数——分析函数与窗口函数。第14章Oracle中的函数与表达式Oracle中的聚合14.1Oracle中的字符串函数Oracle提供了丰富的字符串函数,本小节将通过实例讲述Oracle中各字符串函数的使用。14.1Oracle中的字符串函数Oracle提供了丰富14.1.1lpad()函数lpad()函数用于左补全字符串。在某些情况下,预期的字符串为固定长度,而且格式统一,此时可以考虑使用lpad()函数。例如,深市股票代码都以0开头,并且都为6位,可以利用lpad格式化股票代码,以保证股票代码的格式。selectlpad('21',6,'0')stock_codefromdual;需要注意的是,当原字符串的长度大于预期长度时,实际进行的是截取字符串操作。selectlpad('1234567',6,'0')stock_codefromdual;14.1.1lpad()函数lpad()函数用于左补全字14.1.2rpad()函数与lpad()函数相反,rpad()函数从右端补齐字符串。selectrpad('abc',10,'*')fromdual;注意与说明:lpad()和rpad()都用于填充字符串,lpad()从左端进行填充,而rpad()从右端进行填充,但是,二者在最终截取字符串时,都是从左端开始截取。selectrpad('abcdefg',6,'*')fromdual;14.1.2rpad()函数与lpad()函数相反,rp14.1.3lower()函数——返回小写字符串lower()函数用于返回字符串的小写形式。lower()函数在查询语句中经常扮演重要角色。例如,对于用户名和密码的校验来说,用户名一般并不区分大小写,用户无论输入了大写还是小写形式,都被认为是合法用户。因此,在数据库查询时,应该将数据库中用户名与用户输入的用户名进行统一。selectuser_id,user_namefromt_userswherelower(user_name)=lower('Alex');14.1.3lower()函数——返回小写字符串lowe14.1.4upper()函数——返回大写字符串upper()函数用于返回字符串的大写形式。与lower()函数类似,upper()函数也可以用在查询语句中,以统一数据库和查询条件的一致性。selectuser_id,user_namefromt_userswhereupper(user_name)=upper('ALEX');注意与说明:upper()函数和lower()函数只针对英文字符其作用,因为只有英文字符才有大小写之分。14.1.4upper()函数——返回大写字符串uppe14.1.5initcap()函数——单词首字母大写initcap()函数将单词的首字母大写。selectinitcap('big')fromdual;需要注意的是,initcap()函数不能自动识别单词
selectinitcap('bigbigtiger')fromdual;initcap()函数会将参数中的非单词字符作为单词分隔符selectinitcap('big_big_tiger')fromdual;selectinitcap('big/big/tiger')fromdual;selectinitcap('bigbigtiger')fromdual;14.1.5initcap()函数——单词首字母大写i14.1.6length()函数——返回字符串长度length()函数用于返回字符串的长度。selectlength('abcd')fromdual;空字符串的长度不是0,而是null。因为空字符串被视作null,所以,length(null)返回的仍然是null。selectlength('')fromdual;对其其他数据类型,照样可以通过length()函数来获得其长度。length()函数会首先将参数转换为字符串,然后计算其长度。selectlength(12.51)fromdual;14.1.6length()函数——返回字符串长度len14.1.7substr()函数——截取字符串substr()函数用于截取字符串。该函数可以指定截取的起始位置,截取长度,可以实现灵活的截取操作,因此,成为字符串操作中最常用的函数之一。例如,对于字符串“1234567890”,现欲截取自第5位开始的4个字符。selectsubstr('1234567890',5,4)fromdual;需要注意的是,Oracle中字符位置从1开始,而不是像某些编程语言(如Java)那样从0开始。
如果不指定长度,那么substr()函数将获取起始位置参数至字符串结尾处的所有字符。
selectsubstr('1234567890',5)fromdual;14.1.7substr()函数——截取字符串subst14.1.8instr()函数——获得字符串出现的位置instr()函数用于获得子字符串在父字符串中出现的位置。selectinstr('bigbigtiger','big')fromdual;可以指定额外的参数,以命令该函数从指定位置开始搜索。selectinstr('bigbigtiger','big',2)fromdual;还可以指定出现次数参数,以指定是第几次搜索到子字符串。selectinstr('bigbigtiger','big',2,2)fromdual;14.1.8instr()函数——获得字符串出现的位置i14.1.9ltrim()函数——删除字符串首部空格ltrim()中的l代表left。该函数用于删除字符串左端的空白符。selectltrim('abc')fromdual;需要注意的是,空白符不仅仅包括了空格符,还包括TAB键、回车符和换行符。14.1.9ltrim()函数——删除字符串首部空格lt14.1.10rtrim()函数——删除字符串尾部空格rtrim()中的r代表right。该函数用于删除字符串右端空白符。删除字符串首尾空白符可以结合使用ltrm()和rtrim()函数。selectrtrim(ltrim('abc'))fromdual;14.1.10rtrim()函数——删除字符串尾部空格r14.1.11trim()函数——删除字符串首尾空格trim()函数可用于删除首尾空格,相当于ltrim()和rtrim()的组合。selecttrim('abc')fromdual;14.1.11trim()函数——删除字符串首尾空格tr14.1.12to_char()函数——将其他类型转换为字符类型to_char()函数用于将其他数据类型的数据转换为字符型,这些类型主要包括数值型、日期型。1.将数值型转换为字符串selectto_char(120,'99999')resultfromdual;selectto_char(0.96,'9.99')resultfromdual;selectto_char(0.96,'0.00')resultfromdual;selectto_char(5897.098,'999,999,999.000')resultfromdual;selectto_char(5987.098,'$999,999,999.000')resultfromdual;2.将日期型转换为字符串selectto_char(sysdate,'yyyy-mm-dd')resultfromdual;selectto_char(sysdate,'YYYY-MON-DD')fromdual;
14.1.12to_char()函数——将其他类型转换为14.1.13chr()函数——将ascii码转换为字符串chr()函数用于将ascii码转换为字符串。通过chr()函数,可以对不宜直接输入的字符进行操作。例如,将回车换行符插入到数据中。insertintotest_datavalues(6,'周林'||chr(13)||chr(10)|'梁军',20);select*fromtest_datawhereid=6;14.1.13chr()函数——将ascii码转换为字符14.1.14translate()函数——替换字符translate()函数用于替换字符串。替换的规则类似于翻译的过程。selecttranslate('56338','1234567890','avlihemoqr')resultfromdual;需要注意的是,当字符不能被成功“翻译”,那么,Oracle将使用空字符替换它。利用此特性,可以使用translate()函数来删除一个含有数字和英文字母的字符串中的所有字母:selecttranslate('21343yuioioizf899dasiwpe58595oda0j098','#abcdefghijklmnopqrstuvwxyz','')reulstfromdual;14.1.14translate()函数——替换字符tr14.2Oracle中的数学函数Oracle提供的数学函数可以处理日常使用到的大多数数学运算。本小节将讲述Oracle中常用的几种数学函数。14.2Oracle中的数学函数Oracle提供的数学函14.2.1abs()函数——返回数字的绝对值abs()函数的参数只能是数值型,该参数用于返回参数的绝对值。selectabs(-2.1)fromdual;14.2.1abs()函数——返回数字的绝对值abs(14.2.2round()函数——返回数字的“四舍五入”值round()函数用于返回某个数字的四舍五入值。为了使用该函数,除了提供原始值之外,还应提供精确到的位数。精确位数可以为正整数、0和负整数。selectround(2745.173,2)resultfromdual;如果不使用第二个参数,那么,相当于使用了参数0,即精确到整数。selectround(2745.173)resultfromdual;如果第二个参数为负数,那么,相当于将数值精确到小数点之前的位数。
selectround(2745,-1)resultfromdual;14.2.2round()函数——返回数字的“四舍五入14.2.3ceil()函数——向上取整ceil()函数只能有一个参数。该函数将参数向上取整,以获得大于等于该参数的最小整数。selectceil(21.897)resultfromdual;需要注意的是该函数针对负数的运算:
selectceil(-21.897)resultfromdual;因为ceil()函数返回的是大于等于参数的最小整数,所以,该函数返回的并非-22,而是-21。14.2.3ceil()函数——向上取整ceil()函数14.2.4floor()函数——向下取整与ceil函数相反,floor()函数用于返回小于等于某个数值的最大整数。selectfloor(21.897)resultfromdual;selectfloor(-21.897)resultfromdual;14.2.4floor()函数——向下取整与ceil函数14.2.5mod()函数——取模操作mod()函数有两个参数,第一个参数为被除数,第二个参数为除数。mod()函数的实际功能为获得两数相除之后的余数。selectmod(5,2)resultfromdual;14.2.5mod()函数——取模操作mod()函数有14.2.6sign()函数——返回数字的正负性sign()函数只有一个参数。该函数将返回参数的正负性。若返回值为1,表示该参数大于0;若返回值为-1,表示该参数小于0;若返回值为0,表示该参数等于0。selectsign(8)resultfromdual;selectsign(-8)resultfromdual;selectsign(0)resultfromdual;sign()函数为判断两个数值的大小关系提供了方便。因为在oracle中,利用类似ifelse的结构来判断两个数值之间的大小关系,并不像编程语言中那样方便,而且极易造成代码的复杂化。14.2.6sign()函数——返回数字的正负性sign14.2.7sqrt()函数——返回数字的平方根sqrt()函数也只有一个参数。该函数用于返回参数的平方根。可以利用round()函数和sqrt()函数返回某个数值的近似平方根。selectround(sqrt(2),3)resultfromdual;14.2.7sqrt()函数——返回数字的平方根sqrt14.2.8power()函数——乘方运算power()函数有两个参数。该函数用于实现数值的乘方运算。selectpower(6,2)resultfromdual;14.2.8power()函数——乘方运算power()14.2.9trunc()函数——截取数字trunc()函数用于截取部分数字。其工作机制非常类似于round()函数。与round()函数不同的是,该函数不对数值做四舍五入处理,而是直接截取。selecttrunc(2745.173,2)resultfromdual;保留位数的值可以为0,当该参数的值为0时,将保留到整数。selecttrunc(2745.173)resultfromdual;当保留位数小于0时,表示保留到小数点之前的位数。
selecttrunc(2745.173,-1)resultfromdual;14.2.9trunc()函数——截取数字trunc()14.2.10vsize()函数——返回数据的存储空间vsize()函数根据数据库的存储格式,来返回其所占用的存储空间的字节数。selectvsize('abc123')fromdual;注意与说明:vsize()函数在返回的是Oracle实际存储数据的字节数,在实际开发中使用的几率也较小。读者可以不必了解Oracle本身的存储机制。14.2.10vsize()函数——返回数据的存储空间v14.2.11to_number()函数——将字符串转换为数值类型to_number()函数可以将字符串转换为数值型。selectto_number('257.90')resultfromdual;需要注意的是,被转换的字符串必须符合数值类型格式。如果被转换的字符串不符合数值型格式,Oracle将抛出错误提示。selectto_number('a')resultfromdual;14.2.11to_number()函数——将字符串转换14.3Oracle中的日期函数Oracle提供了丰富的日期函数。利用日期函数可以灵活的对日期进行运算。14.3Oracle中的日期函数Oracle提供了丰富的14.3.1to_date()函数——将字符串转换为日期型to_date()函数用于将字符串转换为日期。被转换的字符串必须符合特定的日期格式。selectto_date('12/02/09','mm/dd/yy')resultfromdual;14.3.1to_date()函数——将字符串转换为日期14.3.2add_months()函数——为日期加上特定月份add_months()函数将为日期添加特定月份,并获得新的日期。selectto_char(add_months(sysdate,2),'yyyy-mm-dd')resultfromdual;14.3.2add_months()函数——为日期加上特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.3last_day()函数——返回特定日期所在14.3.4months_between()函数——返回两个日期所差的月数months_between()函数用于获取两个日期所间隔的月数。该函数的返回值是一个实数。selectmonths_between(sysdate,to_date('2009-02-08','yyyy-mm-dd'))resultfromdual;当第一个日期早于第二个日期,那么返回值将是负值。selectmonths_between(to_date('2009-02-08','yyyy-mm-dd'),to_date('2009-03-08','yyyy-mm-dd'))resultfromdual;14.3.4months_between()函数——返14.3.5current_date()函数——返回当前会话时区的当前日期current_date()函数用于返回当前会话时区的当前日期。selectsessiontimezone,to_char(current_date,'yyyy-mm-ddhh:mi:ss')resultfromdual;注意与说明:current_date等无参数函数作为Oracle的关键字存在。在使用时,不能为其添加小括号。即selectcurrent_date()fromdual是错误的SQL语句。14.3.5current_date()函数——返回当前14.3.6current_timestamp()函数——返回当前会话时区的当前时间戳current_timestamp()函数用于返回当前会话时的区时间戳。可以结合sessiontimezone来查看其用法。selectsessiontimezone,current_timestampfromdual;14.3.6current_timestamp()函数—14.3.7extract()函数——返回日期的某个域日期由若干域组成,例如年、月、日、小时等等。extract()函数可以返回这些域的具体值。为了使用该函数,除了要指定原日期外,还应该指定要返回的域名。selectextract(yearfromsysdate)resultfromdual;需要注意的是,year、month、day域只能从日期(如sysdate)中获得,而hour、minute、second只能从时间型(如systimestamp)中获得。
14.3.7extract()函数——返回日期的某个域日14.4Oracle中的聚合函数所谓聚合函数是指针对多条记录的函数。Oracle最常用的聚合函数包括,max()、min()、avg()、sum()和count()函数。本节将讲述这些函数的用法。14.4Oracle中的聚合函数所谓聚合函数是指针对多条14.4.1max()函数——求最大值max()函数用于获得记录集在某列的最大值。例如,为了返回员工最高工资,可以利用max()函数。selectmax(salary)max_salaryfromt_salary;需要注意的是,聚合函数往往是返回记录集的统计值,因此,不能与其中的单条记录同时出现。例如,不能将max(salary)与具体列一起查询。selectemployee_id,max(salary)max_salaryfromt_salary;selectdistincte.employee_name,s.salaryfromt_employeese,t_salaryswheree.employee_id=s.employee_idands.salary=(selectmax(salary)fromt_salary)14.4.1max()函数——求最大值max()函数用于14.4.2min()函数——求最小值min()函数可以用来获得记录集在某列上的最小值,其功能与max()函数相反。selectdistincte.employee_name,s.salaryfromt_employeese,t_salaryswheree.employee_id=s.employee_idands.salary=(selectmin(salary)fromt_salary)14.4.2min()函数——求最小值min()函数可以14.4.3avg()函数——求平均值avg()函数用于获得记录集在某列上的平均值。selecte.employee_name,avg(salary)fromt_employeese,t_salaryswheree.employee_id=s.employee_idgroupbye.employee_id,e.employee_name14.4.3avg()函数——求平均值avg()函数用于14.4.4sum()函数——求和sum()函数用于获得结果集上某列值的和。selecte.employee_name,sum(salary)fromt_employeese,t_salaryswheree.employee_id=s.employee_idgroupbye.employee_id,e.employee_name14.4.4sum()函数——求和sum()函数用于获得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.4.5count()函数——获得记录数count(14.5Oracle中的其他函数除了数值函数、字符串函数、日期函数和聚合函数外,Oracle还提供了其他功能性更强的函数。本节将介绍decode()、nvl()和cast()函数。14.5Oracle中的其他函数除了数值函数、字符串函数14.5.1decode()函数——多值判断decode()函数用于多值判断。其执行过程类似于解码操作。该函数最常见的应用为,实现类似ifelse的功能。例如,可以利用decode()函数为员工工资添加标识,工资大于6000者为高收入,其余的为一般收入。selecte.employee_id,e.employee_name,decode(sign(avg(s.salary)-6000),1,'高收入','一般收入')incommingfromt_employeese,t_salaryswheree.employee_id=s.employee_idgroupbye.employee_id,e.employee_name14.5.1decode()函数——多值判断decode14.5.2nvl()函数——为空值重新赋值nvl()函数用于处理某列的值。该函数有两个参数,第一个参数为要处理的列。如果其值为空,则返回第二个参数的值,否则,将返回列值。selectemployee_id,nvl(employee_name,'未知')employee_namefromt_employees;nvl()函数更常见的用途为判断数值是否为空。因为sum()等函数往往会返回null,例如,表示汇率的列一旦为null,那么最终的货币结算额度也为null,所以,必须对汇率列进行nvl()的处理。在统计员工工资时,null同样是不受欢迎的结果,那么可以利用nvl()函数进行处理。selecte.employee_id,nvl(e.employee_name,'未知')employee_name,nvl(sum(s.salary),0)salaryfromt_employeese,t_salaryswheree.employee_id=s.employee_id(+)groupbye.employee_id,e.employee_name14.5.2nvl()函数——为空值重新赋值nvl()函14.5.3cast()函数——强制转换数据类型cast()函数用于强制转换数据类型。Oracle会根据操作符来自动进行数据类型的转换,例如:select'123'+200resultfromdual;Oracle会根据运算符“+”将‘123’转换为数值型123。select'123'||200resultfromdual;Oracle会根据运算符“||”将数字200转换为字符串‘200’。cast()函数最常用的场景是转换列的数据类型,以创建新表createtabletmp_salaryasselectcast(salary_idasvarchar2(20))salary_id,cast(employee_idasvarchar2(20))employee_id,cast(monthasvarchar2(20))month,cast(salaryasvarchar2(20))salaryfromt_salarydesctmp_salary;14.5.3cast()函数——强制转换数据类型cast14.6Oracle中的运算表达式Oracle中的常用运算包括:数学运算、逻辑运算和按位运算。本节将通过范例着重讲述这三种运算的常用运算符和运算规则。14.6Oracle中的运算表达式Oracle中的常用运14.6.1数学运算数学运算是最常用的运算方式,Oracle中的数学运算符包括:+、-、*、/,分别代表了加、减、乘除运算。在使用数学运算时,Oracle会自动将其他数据类型转换为数值型,然后再参与运算。select5+3resultfromdual;select5-3resultfromdual;select5*2resultfromdual;select5/2resultfromdual;需要注意的是,任何一种运算符与null的运算结果均为null。
select5+nullresultfromdual;select5-nullresultfromdual;select5*nullresultfromdual;select5/nullresultfromdual;14.6.1数学运算数学运算是最常用的运算方式,Orac14.6.2逻辑运算Oracle中的逻辑运算包括:>:大于运算,可用于数值型、日期型和字符串类型;>=:大于等于运算,可用于数值型、日期型和字符串类型;<:小于运算,可用于数值型、日期型和字符串类型;<=:大于等于运算,可用于数值型、日期型和字符串类型;=:等于,可用于数值型、日期型和字符串类型;<>:不等于,可用于数值型、日期型和字符串类型;!=:与<>用法相同;NOT:取反操作;AND:布尔值的与操作;OR:布尔值的或操作。14.6.2逻辑运算Oracle中的逻辑运算包括:14.6.2逻辑运算需要注意的是,Oracle中的逻辑运算符只能作为条件判断,并不返回值。为了查询工资在5000-7000之间的记录,可以利用逻辑运算符来组合查询条件。select*fromt_salarywheresalary>=5000andsalary<=7000;对于null值,需要特别注意的是,无论使用哪种运算符,结果都会返回null。当比较的结果为null,并作为条件出现时,Oracle都会将其解释为false。select1resultfromdualwhere1=null;select1resultfromdualwhere1<>null;select1resultfromdualwherenull=null;select1resultfromdualwherenull<>null;14.6.2逻辑运算需要注意的是,Oracle中的逻辑运14.6.3位运算
从Oracle8i开始,系统已经提供了位运算符。最常用的莫过于bitand运算符。selectbitand(192,100)resultfromdual;14.6.3位运算从Oracle8i开始,系统14.7Oracle中的特殊判式除了逻辑运算之外,Oracle提供了一些特殊判式。这些判式可以用来生成更加复杂和灵活的查询条件。本节将着重介绍以下几种判式。Between:取值范围。In:集合成员测试。Like:模式匹配。isnull:空值判断。all,some,any:数量判断。exists:存在性判断。14.7Oracle中的特殊判式除了逻辑运算之外,Ora14.7.1between——范围测试between判式,用于判断某个值是否在另外两个值之间。这些值可以为数值型、字符串和日期型。使用betwwen判式来获得ID号在1-5之间的员工信息。select*fromt_employeeswhereemployee_idbetween1and5;betwwen判式同样可以应用于字符串和日期型。字符串是按照字母表的顺序进行比较,而日期型是按照日期的先后顺序进行比较。select*fromt_employeeswhere'b'between'b'and'c';select*fromt_employeeswhere'b'between'bc'and'c';注意与说明:between判式与>=、<=的组合是等价关系。但是,效率上要比后者差。14.7.1between——范围测试between判式14.7.2in——集合成员测试in用于判断某个值是否一个集合的成员。select*fromt_employeeswherestatusin('NEW','ACT');值得注意的是,in判式中的集合的成员的数据类型可以不一致,例如,select*fromt_employeeswherestatusin('NEW','ACT',sysdate,1)中的数据类型包含了字符串、日期型和数值型。14.7.2in——集合成员测试in用于判断某个值是否一14.7.3like——模式匹配like判式的最大特点在于,可以使用通配符。其通常的应用场景为处理模糊查询。select*fromt_employeeswhereemployee_namelike'钟%';如果要求字符串中含有原义字符“%”,例如,含有百分比的字符串。那么,like判式应写作:like'钟\%'escape'\'。Oracle会首先解释escape关键字,并将其后的字符“\”解释为转义字符。那么在“钟\%”中的“%”不再表示通配符,而是表示原义字符“%”。“_”(下划线)是可用于like判式的另一个通配符,该通配符表示一个任意的字符。14.7.3like——模式匹配like判式的最大特点在14.7.4isnull——空值判断在逻辑判断中,对于列值为空的判断,不能使用=或者<>。oracle对与空值的判断提供了专门的判式——isnull。例如,为了获取表t_employees中员工信息不全的记录,可以利用如下所示的查询语句。select*fromt_employeeswhereemployee_idisnulloremployee_nameisnullorwork_yearsisnullorstatusisnull;14.7.4isnull——空值判断在逻辑判断中,对于14.7.5exists——存在性判断in判式用于判断表的列值是否存在于列表(集合)中。而exists判式则可用于判断查询结果集合是否为空。例如,为了查询出表t_employees所存储的员工信息中,哪些员工存在于工资表中,即可利用exists判式。select*fromt_employeesewhereexists(select*fromt_salarywhereemployee_id=e.employee_id);14.7.5exists——存在性判断in判式用于判断表14.7.6all,some,any——数量判断all,some和any判式的作用对象为记录集合。all表示,记录集中的所有记录,some表示其中的一些记录,any判式则表示其中的任意记录。例如,在员工工资表t_salary中,为了查找高于id为4和5的工资信息,即可使用all判式。select*fromt_salarywhereemployee_id=4oremployee_id=5;select*fromt_salarywheresalary>all(selectdistinctsalaryfromt_salarywhereemployee_id=4oremployee_id=5);select*fromt_salarywheresalary>some(selectdistinctsalaryfromt_salarywhereemployee_id=4oremployee_id=5);此时的some判式实际相当于逻辑运算中的or运算,即salary>6000orsalary>7000。此时,使用any判式,将返回同样的结果。
14.7.6all,some,any——数量判断all,14.8Oracle高级函数——分析函数与窗口函数Oracle中的分析函数具有非常强大的功能。分析函数往往与另一类函数——窗口函数同时使用。窗口函数总是为查询过程中的当前记录提供一个相关记录集,而且随着当前记录的推移,相应的记录集也会随之改变,这非常类似于“滑动窗”的概念。分析函数的操作对象即为“滑动窗”所指定的记录集合。本节将通过实例来讲述分析函数和窗口函数的使用。14.8Oracle高级函数——分析函数与窗口函数Ora14.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.1排名分析函数中的排名函数可以针对窗口中的记录14.8.2分区窗口对于窗口函数,利用partitionby关键字可以指定分区窗口。现欲统计各员工的工资在各自部门的高低情况,则可以利用partitionby进行分区,然后利用分析函数对分区内的记录进行统计
selectt.*,dense_rank()over(partitionbydepartmentorderbysalary)positionfromsalarytorderbyt.employee_id另外一种常见需求为,在获得员工工资的同时,也需要部门所有员工的工资总额
selectt.*,sum(salary)over(partitionbydepartment)total_salary,round(avg(salary)over(partitionbydepartment))average_salaryfromsalarytorderbyemployee_id注意,avg(salary)over(partitionbydepartment)是不可分割的一个整体。对于数据表salary中每条记录都会返回单个值,因此,当使用round()函数,函数的作用对象应为avg(salary)over(partitionbydepartment)这个整体,而不能使用诸如round(avg(salary))over(partitionbydepartment)等形式。另外,利用partitionby进行分区之后,当前记录总是处于某个分区中,此时的窗口即为该分区。14.8.2分区窗口对于窗口函数,利用partition14.8.3窗口子句对于每条记录,一旦使用了窗口函数,都会为其产生一个可操作的记录集合。而对于该记录集,可以使用窗口子句,来进一步限制窗口范围。常用的窗口子句包括两类:利用rows子句的行方式进行限制;利用range子句的值方式进行限制。rows子句selectemployee_id,employee_name,sum(salary)over(orderbyemployee_idrowsbetween1precedingand1following)three_totalfromsalaryrows子句因为和位置相关,因此,在窗口函数中必须含有排序子句orderby。如果未使用orderby子句,而直接使用rows子句,Oracle将抛出错误提示,14.8.3窗口子句对于每条记录,一旦使用了窗口函数,都14.8.3窗口子句注意,rowsbetwwen1precedingand1following不一定返回3条记录。例如,对于employee_id为1的记录,排序之后,该记录为第一条记录,不存在前一条记录,因此只返回两条记录,而求和操作返回的实际为employee_id为1和2的员工的工资总和10500。2.range子句range子句按照列值进行窗口的进一步限制。selectemployee_id,employee_name,count(1)over(partitionbydepartmentorderbysalaryrangebetween500precedingand500following)asemployee_countfromsalaryorderbyemployee_id3.unbounded和currentrow在rows和range子句中,除了使用具体的数值来决定窗口的大小之外,还可以使用关键字unbounded和currentrow。unbounded可以直接代替数值,表示没有任何限制;currentrow则直接代表当前行。
14.8.3窗口子句注意,rowsbetwwen114.8.4主要的分析函数分析函数作用对象为窗口函数所捕获的记录集,因此,分析函数具有聚合函数的特点,大多数的聚合函数,如sum()、count()、max()等都能作为分析函数出现。Oracle还提供了专门针对窗口函数的分析函数,本小节着重讲述常用的几种——fist_value()、last_value()、lag()和lead()。1.fist_value()函数的使用selectdistinctdepartment,first_value(employee_name)over(partitionbydepartmentorderbysalary)employee_name,first_value(salary)over(partitionbydepartmentorderbysalary)salaryfromsalary2.last_value()函数的使用3.lead()函数的使用selectemployee_id,employee_name,salary,lead(employee_name,1,'N/A')over(partitionbydepartmentorderbysalary)prev_namefromsalaryorderbyemployee_id4.lag()函数的使用14.8.4主要的分析函数分析函数作用对象为窗口函数所捕14.9本章实例Oracle虽然内置了很多函数,但是并不能满足日常开发中的应用。此时,需要开发者自定义函数,而内置函数往往成为自定函数的基石。createorreplacefunctionis_date(paramvarchar2)returnvarchar2isvaldate;beginval:=to_date(nvl(param,''),'yyyy-mm-ddhh24:mi:ss');return'Y';exceptionwhenothersthenreturn'N';end;selectis_date('abc')fromdual;selectis_date('2009-09-01')fromdual;14.9本章实例Oracle虽然内置了很多函数,但是并不14.10本章小结本章通过详尽的实例讲述了Oracle中常用的内置函数、表达式以及特殊判式。对于内置函数,特别需要注意的是聚合函数的使用。聚合函数最常用的场景为分组查询;聚合函数不能与单条记录的列并列作为查询结果。对于特殊判式,尤其应该注意的是like判式的使用,like判式使用中,通配符只有“%”和“_”两种,要注意通配符和正则表达式的区别。14.10本章小结本章通过详尽的实例讲述了Oracle中14.10本章小结在Oracle高级函数中,重点介绍了分析函数和窗口函数,这两个函数总是结合使用,为数据表中单条记录提供新的结果集的方法。对于窗口函数,要重点理解分区和排序的工作流程,尤其需要注意的是,对于排序中,具有相同列值的记录的处理。相较之下,分析函数非常类似于聚合函数,比较容易理解和掌握。在统计和生成复杂报表时,分析函数和窗口函数有着广泛的应用,尤其对于复杂统计,利用这两种函数往往可以起到事半功倍的效果。14.10本章小结在Oracle高级函数中,重点介绍了分14.11习题1.简述null作为函数参数时的特点。2.简述like判式的使用方法。3.简述isnull判式的意义。4.简述窗口函数的特点。14.11习题1.简述null作为函数参数时的特点。第14章Oracle中的函数与表达式Oracle中提供了大量的内置函数,以处理各种形式的运算。这些函数涵盖了字符串运算、数值运算、日期运算等方面。同样,Oracle允许使用数值运算、逻辑运算等基本的表达式运算,另外,提供了SQL标准所规定的特殊判式。Oracle中的字符串函数;Oracle中的数学函数;Oracle中的日期函数;第14章Oracle中的函数与表达式Oracle中提供了第14章Oracle中的函数与表达式Oracle中的聚合函数;Oracle中的运算表达式;Oracle中的特殊判式;Oracle中的高级函数——分析函数与窗口函数。第14章Oracle中的函数与表达式Oracle中的聚合14.1Oracle中的字符串函数Oracle提供了丰富的字符串函数,本小节将通过实例讲述Oracle中各字符串函数的使用。14.1Oracle中的字符串函数Oracle提供了丰富14.1.1lpad()函数lpad()函数用于左补全字符串。在某些情况下,预期的字符串为固定长度,而且格式统一,此时可以考虑使用lpad()函数。例如,深市股票代码都以0开头,并且都为6位,可以利用lpad格式化股票代码,以保证股票代码的格式。selectlpad('21',6,'0')stock_codefromdual;需要注意的是,当原字符串的长度大于预期长度时,实际进行的是截取字符串操作。selectlpad('1234567',6,'0')stock_codefromdual;14.1.1lpad()函数lpad()函数用于左补全字14.1.2rpad()函数与lpad()函数相反,rpad()函数从右端补齐字符串。selectrpad('abc',10,'*')fromdual;注意与说明:lpad()和rpad()都用于填充字符串,lpad()从左端进行填充,而rpad()从右端进行填充,但是,二者在最终截取字符串时,都是从左端开始截取。selectrpad('abcdefg',6,'*')fromdual;14.1.2rpad()函数与lpad()函数相反,rp14.1.3lower()函数——返回小写字符串lower()函数用于返回字符串的小写形式。lower()函数在查询语句中经常扮演重要角色。例如,对于用户名和密码的校验来说,用户名一般并不区分大小写,用户无论输入了大写还是小写形式,都被认为是合法用户。因此,在数据库查询时,应该将数据库中用户名与用户输入的用户名进行统一。selectuser_id,user_namefromt_userswherelower(user_name)=lower('Alex');14.1.3lower()函数——返回小写字符串lowe14.1.4upper()函数——返回大写字符串upper()函数用于返回字符串的大写形式。与lower()函数类似,upper()函数也可以用在查询语句中,以统一数据库和查询条件的一致性。selectuser_id,user_namefromt_userswhereupper(user_name)=upper('ALEX');注意与说明:upper()函数和lower()函数只针对英文字符其作用,因为只有英文字符才有大小写之分。14.1.4upper()函数——返回大写字符串uppe14.1.5initcap()函数——单词首字母大写initcap()函数将单词的首字母大写。selectinitcap('big')fromdual;需要注意的是,initcap()函数不能自动识别单词
selectinitcap('bigbigtiger')fromdual;initcap()函数会将参数中的非单词字符作为单词分隔符selectinitcap('big_big_tiger')fromdual;selectinitcap('big/big/tiger')fromdual;selectinitcap('bigbigtiger')fromdual;14.1.5initcap()函数——单词首字母大写i14.1.6length()函数——返回字符串长度length()函数用于返回字符串的长度。selectlength('abcd')fromdual;空字符串的长度不是0,而是null。因为空字符串被视作null,所以,length(null)返回的仍然是null。selectlength('')fromdual;对其其他数据类型,照样可以通过length()函数来获得其长度。length()函数会首先将参数转换为字符串,然后计算其长度。selectlength(12.51)fromdual;14.1.6length()函数——返回字符串长度len14.1.7substr()函数——截取字符串substr()函数用于截取字符串。该函数可以指定截取的起始位置,截取长度,可以实现灵活的截取操作,因此,成为字符串操作中最常用的函数之一。例如,对于字符串“1234567890”,现欲截取自第5位开始的4个字符。selectsubstr('1234567890',5,4)fromdual;需要注意的是,Oracle中字符位置从1开始,而不是像某些编程语言(如Java)那样从0开始。
如果不指定长度,那么substr()函数将获取起始位置参数至字符串结尾处的所有字符。
selectsubstr('1234567890',5)fromdual;14.1.7substr()函数——截取字符串subst14.1.8instr()函数——获得字符串出现的位置instr()函数用于获得子字符串在父字符串中出现的位置。selectinstr('bigbigtiger','big')fromdual;可以指定额外的参数,以命令该函数从指定位置开始搜索。selectinstr('bigbigtiger','big',2)fromdual;还可以指定出现次数参数,以指定是第几次搜索到子字符串。selectinstr('bigbigtiger','big',2,2)fromdual;14.1.8instr()函数——获得字符串出现的位置i14.1.9ltrim()函数——删除字符串首部空格ltrim()中的l代表left。该函数用于删除字符串左端的空白符。selectltrim('abc')fromdual;需要注意的是,空白符不仅仅包括了空格符,还包括TAB键、回车符和换行符。14.1.9ltrim()函数——删除字符串首部空格lt14.1.10rtrim()函数——删除字符串尾部空格rtrim()中的r代表right。该函数用于删除字符串右端空白符。删除字符串首尾空白符可以结合使用ltrm()和rtrim()函数。selectrtrim(ltrim('abc'))fromdual;14.1.10rtrim()函数——删除字符串尾部空格r14.1.11trim()函数——删除字符串首尾空格trim()函数可用于删除首尾空格,相当于ltrim()和rtrim()的组合。selecttrim('abc')fromdual;14.1.11trim()函数——删除字符串首尾空格tr14.1.12to_char()函数——将其他类型转换为字符类型to_char()函数用于将其他数据类型的数据转换为字符型,这些类型主要包括数值型、日期型。1.将数值型转换为字符串selectto_char(120,'99999')resultfromdual;selectto_char(0.96,'9.99')resultfromdual;selectto_char(0.96,'0.00')resultfromdual;selectto_char(5897.098,'999,999,999.000')resultfromdual;selectto_char(5987.098,'$999,999,999.000')resultfromdual;2.将日期型转换为字符串selectto_char(sysdate,'yyyy-mm-dd')resultfromdual;selectto_char(sysdate,'YYYY-MON-DD')fromdual;
14.1.12to_char()函数——将其他类型转换为14.1.13chr()函数——将ascii码转换为字符串chr()函数用于将ascii码转换为字符串。通过chr()函数,可以对不宜直接输入的字符进行操作。例如,将回车换行符插入到数据中。insertintotest_datavalues(6,'周林'||chr(13)||chr(10)|'梁军',20);select*fromtest_datawhereid=6;14.1.13chr()函数——将ascii码转换为字符14.1.14translate()函数——替换字符translate()函数用于替换字符串。替换的规则类似于翻译的过程。selecttranslate('56338','1234567890','avlihemoqr')resultfromdual;需要注意的是,当字符不能被成功“翻译”,那么,Oracle将使用空字符替换它。利用此特性,可以使用translate()函数来删除一个含有数字和英文字母的字符串中的所有字母:selecttranslate('21343yuioioizf899dasiwpe58595oda0j098','#abcdefghijklmnopqrstuvwxyz','')reulstfromdual;14.1.14translate()函数——替换字符tr14.2Oracle中的数学函数Oracle提供的数学函数可以处理日常使用到的大多数数学运算。本小节将讲述Oracle中常用的几种数学函数。14.2Oracle中的数学函数Oracle提供的数学函14.2.1abs()函数——返回数字的绝对值abs()函数的参数只能是数值型,该参数用于返回参数的绝对值。selectabs(-2.1)fromdual;14.2.1abs()函数——返回数字的绝对值abs(14.2.2round()函数——返回数字的“四舍五入”值round()函数用于返回某个数字的四舍五入值。为了使用该函数,除了提供原始值之外,还应提供精确到的位数。精确位数可以为正整数、0和负整数。selectround(2745.173,2)resultfromdual;如果不使用第二个参数,那么,相当于使用了参数0,即精确到整数。selectround(2745.173)resultfromdual;如果第二个参数为负数,那么,相当于将数值精确到小数点之前的位数。
selectround(2745,-1)resultfromdual;14.2.2round()函数——返回数字的“四舍五入14.2.3ceil()函数——向上取整ceil()函数只能有一个参数。该函数将参数向上取整,以获得大于等于该参数的最小整数。selectceil(21.897)resultfromdual;需要注意的是该函数针对负数的运算:
selectceil(-21.897)resultfromdual;因为ceil()函数返回的是大于等于参数的最小整数,所以,该函数返回的并非-22,而是-21。14.2.3ceil()函数——向上取整ceil()函数14.2.4floor()函数——向下取整与ceil函数相反,floor()函数用于返回小于等于某个数值的最大整数。selectfloor(21.897)resultfromdual;selectfloor(-21.897)resultfromdual;14.2.4floor()函数——向下取整与ceil函数14.2.5mod()函数——取模操作mod()函数有两个参数,第一个参数为被除数,第二个参数为除数。mod()函数的实际功能为获得两数相除之后的余数。selectmod(5,2)resultfromdual;14.2.5mod()函数——取模操作mod()函数有14.2.6sign()函数——返回数字的正负性sign()函数只有一个参数。该函数将返回参数的正负性。若返回值为1,表示该参数大于0;若返回值为-1,表示该参数小于0;若返回值为0,表示该参数等于0。selectsign(8)resultfromdual;selectsign(-8)resultfromdual;selectsign(0)resultfromdual;sign()函数为判断两个数值的大小关系提供了方便。因为在oracle中,利用类似ifelse的结构来判断两个数值之间的大小关系,并不像编程语言中那样方便,而且极易造成代码的复杂化。14.2.6sign()函数——返回数字的正负性sign14.2.7sqrt()函数——返回数字的平方根sqrt()函数也只有一个参数。该函数用于返回参数的平方根。可以利用round()函数和sqrt()函数返回某个数值的近似平方根。selectround(sqrt(2),3)resultfromdual;14.2.7sqrt()函数——返回数字的平方根sqrt14.2.8power()函数——乘方运算power()函数有两个参数。该函数用于实现数值的乘方运算。selectpower(6,2)resultfromdual;14.2.8power()函数——乘方运算power()14.2.9trunc()函数——截取数字trunc()函数用于截取部分数字。其工作机制非常类似于round()函数。与round()函数不同的是,该函数不对数值做四舍五入处理,而是直接截取。selecttrunc(2745.173,2)resultfromdual;保留位数的值可以为0,当该参数的值为0时,将保留到整数。selecttrunc(2745.173)resultfromdual;当保留位数小于0时,表示保留到小数点之前的位数。
selecttrunc(2745.173,-1)resultfromdual;14.2.9trunc()函数——截取数字trunc()14.2.10vsize()函数——返回数据的存储空间vsize()函数根据数据库的存储格式,来返回其所占用的存储空间的字节数。selectvsize('abc123')fromdual;注意与说明:vsize()函数在返回的是Oracle实际存储数据的字节数,在实际开发中使用的几率也较小。读者可以不必了解Oracle本身的存储机制。14.2.10vsize()函数——返回数据的存储空间v14.2.11to_number()函数——将字符串转换为数值类型to_number()函数可以将字符串转换为数值型。selectto_number('257.90')resultfromdual;需要注意的是,被转换的字符串必须符合数值类型格式。如果被转换的字符串不符合数值型格式,Oracle将抛出错误提示。selectto_number('a')resultfromdual;14.2.11to_number()函数——将字符串转换14.3Oracle中的日期函数Oracle提供了丰富的日期函数。利用日期函数可以灵活的对日期进行运算。14.3Oracle中的日期函数Oracle提供了丰富的14.3.1to_date()函数——将字符串转换为日期型to_date()函数用于将字符串转换为日期。被转换的字符串必须符合特定的日期格式。selectto_date('12/02/09','mm/dd/yy')resultfromdual;14.3.1to_date()函数——将字符串转换为日期14.3.2add_months()函数——为日期加上特定月份add_months()函数将为日期添加特定月份,并获得新的日期。selectto_char(add_months(sysdate,2),'yyyy-mm-dd')resultfromdual;14.3.2add_months()函数——为日期加上特14.3.3last_day()函数——返回特定日期所在月的最后一天last_day()函数将接受一个日期参数
温馨提示
- 1. 本站所有资源如无特殊说明,都需要本地电脑安装OFFICE2007和PDF阅读器。图纸软件为CAD,CAXA,PROE,UG,SolidWorks等.压缩文件请下载最新的WinRAR软件解压。
- 2. 本站的文档不包含任何第三方提供的附件图纸等,如果需要附件,请联系上传者。文件的所有权益归上传用户所有。
- 3. 本站RAR压缩包中若带图纸,网页内容里面会有图纸预览,若没有图纸预览就没有图纸。
- 4. 未经权益所有人同意不得将文件中的内容挪作商业或盈利用途。
- 5. 人人文库网仅提供信息存储空间,仅对用户上传内容的表现方式做保护处理,对用户上传分享的文档内容本身不做任何修改或编辑,并不能对任何下载内容负责。
- 6. 下载文件中如有侵权或不适当内容,请与我们联系,我们立即纠正。
- 7. 本站不保证下载资源的准确性、安全性和完整性, 同时也不承担用户因使用这些下载资源对自己和他人造成任何形式的伤害或损失。
最新文档
- 国开00747+22003基础会计期末复习资料
- 第四单元 乘与除(复习课件)北师大版三年级数学上册课件+练习 (素养达标课件+教案+练习)三年级数学上册同步备课 (北师大版)
- 四川省内江市威远中学2024-2025学年高一上学期12月月考地理试题(含答案)
- 辽宁省鞍山市海城市西部集团2024-2025学年八年级上学期12月第三次质量监测英语试题(含答案无听力原文及音频)
- 河北省唐山市路南区2024-2025学年八年级上学期12月月考数学试题(无答案)
- 广东省梅州市梅雁中学2024-2025学年高三上学期12月月考地理试题(含答案)
- 2024-2025学年高一【数学(人教A版)】指数函数的图象和性质-教学设计
- 自动控制原理及应用知到智慧树章节测试课后答案2024年秋新疆工程学院
- 建筑力学知到智慧树章节测试课后答案2024年秋江西理工大学
- 高考英语3000词词性转换清单
- 年产12000吨水合肼(100%)项目环评报告书
- 城市规划原理课件(完整版)
- 2022年西南医院医护人员招聘笔试模拟试题及答案解析
- 初中音乐 人音版 八年级上册 多彩音乐剧-云中的城堡部优课件
- 围术期过敏反应诊治的专家共识(全文)
- 防汛应急抢险指挥与实务课件(PPT 89页)
- 农村信用社县级联社费用管控指导意见
- (完整版)八年级上综合性学习-我们的互联网时代-练习卷(含答案)
- 义务教育(数学)新课程标准(2022年修订版)
- 立式圆筒形钢制焊接储罐施工及验收规范》
- 地灾治理全套表格
评论
0/150
提交评论