第14章Oracle中的函数与表达式.ppt

第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_codefromdual;需要注意的是，当原字符串的长度大于预期长度时，实际进行的是截取字符串操作。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_namefromt_userswherelower(user_name)=lower(Alex);,14.1.4upper()函数返回大写字符串,upper()函数用于返回字符串的大写形式。与lower()函数类似，upper()函数也可以用在查询语句中，以统一数据库和查询条件的一致性。selectuser_id,user_namefromt_userswhereupper(user_name)=upper(ALEX);注意与说明：upper()函数和lower()函数只针对英文字符其作用，因为只有英文字符才有大小写之分。,14.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.6length()函数返回字符串长度,length()函数用于返回字符串的长度。selectlength(abcd)fromdual;空字符串的长度不是0，而是null。因为空字符串被视作null，所以，length(null)返回的仍然是null。selectlength()fromdual;对其其他数据类型，照样可以通过length()函数来获得其长度。length()函数会首先将参数转换为字符串，然后计算其长度。selectlength(12.51)fromdual;,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)fromdual;,14.1.11trim()函数删除字符串首尾空格,trim()函数可用于删除首尾空格，相当于ltrim()和rtrim()的组合。selecttrim(abc)fromdual;,14.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.13chr()函数将ascii码转换为字符串,chr()函数用于将ascii码转换为字符串。通过chr()函数，可以对不宜直接输入的字符进行操作。例如，将回车换行符插入到数据中。insertintotest_datavalues(6,周林|chr(13)|chr(10)|梁军,20);select*fromtest_datawhereid=6;,14.1.14translate()函数替换字符,translate()函数用于替换字符串。替换的规则类似于翻译的过程。selecttranslate(56338,1234567890,avlihemoqr)resultfromdual;需要注意的是，当字符不能被成功“翻译”，那么，Oracle将使用空字符替换它。利用此特性，可以使用translate()函数来删除一个含有数字和英文字母的字符串中的所有字母：selecttranslate(21343yuioioizf899dasiwpe58595oda0j098,#abcdefghijklmnopqrstuvwxyz,)reulstfromdual;,14.2Oracle中的数学函数,Oracle提供的数学函数可以处理日常使用到的大多数数学运算。本小节将讲述Oracle中常用的几种数学函数。,14.2.1abs()函数返回数字的绝对值,abs()函数的参数只能是数值型，该参数用于返回参数的绝对值。selectabs(-2.1)fromdual;,14.2.2round()函数返回数字的“四舍五入”值,round()函数用于返回某个数字的四舍五入值。为了使用该函数，除了提供原始值之外，还应提供精确到的位数。精确位数可以为正整数、0和负整数。selectround(2745.173,2)resultfromdual;如果不使用第二个参数，那么，相当于使用了参数0，即精确到整数。selectround(2745.173)resultfromdual;如果第二个参数为负数，那么，相当于将数值精确到小数点之前的位数。selectround(2745,-1)resultfromdual;,14.2.3ceil()函数向上取整,ceil()函数只能有一个参数。该函数将参数向上取整，以获得大于等于该参数的最小整数。selectceil(21.897)resultfromdual;需要注意的是该函数针对负数的运算：selectceil(-21.897)resultfromdual;因为ceil()函数返回的是大于等于参数的最小整数，所以，该函数返回的并非-22，而是-21。,14.2.4floor()函数向下取整,与ceil函数相反，floor()函数用于返回小于等于某个数值的最大整数。selectfloor(21.897)resultfromdual;selectfloor(-21.897)resultfromdual;,14.2.5mod()函数取模操作,mod()函数有两个参数，第一个参数为被除数，第二个参数为除数。mod()函数的实际功能为获得两数相除之后的余数。selectmod(5,2)resultfromdual;,14.2.6sign()函数返回数字的正负性,sign()函数只有一个参数。该函数将返回参数的正负性。若返回值为1，表示该参数大于0；若返回值为-1，表示该参数小于0；若返回值为0，表示该参数等于0。selectsign(8)resultfromdual;selectsign(-8)resultfromdual;selectsign(0)resultfromdual;sign()函数为判断两个数值的大小关系提供了方便。因为在oracle中，利用类似ifelse的结构来判断两个数值之间的大小关系，并不像编程语言中那样方便，而且极易造成代码的复杂化。,14.2.7sqrt()函数返回数字的平方根,sqrt()函数也只有一个参数。该函数用于返回参数的平方根。可以利用round()函数和sqrt()函数返回某个数值的近似平方根。selectround(sqrt(2),3)resultfromdual;,14.2.8power()函数乘方运算,power()函数有两个参数。该函数用于实现数值的乘方运算。selectpower(6,2)resultfromdual;,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.10vsize()函数返回数据的存储空间,vsize()函数根据数据库的存储格式，来返回其所占用的存储空间的字节数。selectvsize(abc123)fromdual;注意与说明：vsize()函数在返回的是Oracle实际存储数据的字节数，在实际开发中使用的几率也较小。读者可以不必了解Oracle本身的存储机制。,14.2.11to_number()函数将字符串转换为数值类型,to_number()函数可以将字符串转换为数值型。selectto_number(257.90)resultfromdual;需要注意的是，被转换的字符串必须符合数值类型格式。如果被转换的字符串不符合数值型格式，Oracle将抛出错误提示。selectto_number(a)resultfromdual;,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)resultfromdual;,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)resultfromdual;当第一个日期早于第二个日期，那么返回值将是负值。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()fromdual是错误的SQL语句。,14.3.6current_timestamp()函数返回当前会话时区的当前时间戳,current_timestamp()函数用于返回当前会话时的区时间戳。可以结合sessiontimezone来查看其用法。selectsessiontimezone,current_timestampfromdual;,14.3.7extract()函数返回日期的某个域,日期由若干域组成，例如年、月、日、小时等等。extract()函数可以返回这些域的具体值。为了使用该函数，除了要指定原日期外，还应该指定要返回的域名。selectextract(yearfromsysdate)resultfromdual;需要注意的是，year、month、day域只能从日期（如sysdate）中获得，而hour、minute、second只能从时间型（如systimestamp）中获得。,14.4Oracle中的聚合函数,所谓聚合函数是指针对多条记录的函数。Oracle最常用的聚合函数包括，max()、min()、avg()、sum()和count()函数。本节将讲述这些函数的用法。,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.2min()函数求最小值,min()函数可以用来获得记录集在某列上的最小值，其功能与max()函数相反。selectdistincte.employee_name,s.salaryfromt_employeese,t_salaryswheree.employee_id=s.employee_idands.salary=(selectmin(salary)fromt_salary),14.4.3avg()函数求平均值,avg()函数用于获得记录集在某列上的平均值。selecte.employee_name,avg(salary)fromt_employeese,t_salaryswheree.employee_id=s.employee_idgroupbye.employee_id,e.employee_name,14.4.4sum()函数求和,sum()函数用于获得结果集上某列值的和。selecte.employee_name,sum(salary)fromt_employeese,t_salaryswheree.employee_id=s.employee_idgroupbye.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,高收入,一般收入)incommingfromt_employeese,t_salaryswheree.employee_id=s.employee_idgroupbye.employee_id,e.employee_name,14.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_name,14.5.3cast()函数强制转换数据类型,cast()函数用于强制转换数据类型。Oracle会根据操作符来自动进行数据类型的转换，例如：select123+200resultfromdual;Oracle会根据运算符“+”将123转换为数值型123。select123|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.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.2逻辑运算,Oracle中的逻辑运算包括：大于运算，可用于数值型、日期型和字符串类型；=：大于等于运算，可用于数值型、日期型和字符串类型；：不等于，可用于数值型、日期型和字符串类型；!=：与用法相同；NOT：取反操作；AND：布尔值的与操作；OR：布尔值的或操作。,14.6.2逻辑运算,需要注意的是，Oracle中的逻辑运算符只能作为条件判断，并不返回值。为了查询工资在5000-7000之间的记录，可以利用逻辑运算符来组合查询条件。select*fromt_salarywheresalary=5000andsalarynull;select1resultfromdualwherenull=null;select1resultfromdualwherenullnull;,14.6.3位运算,从Oracle8i开始，系统已经提供了位运算符。最常用的莫过于bitand运算符。selectbitand(192,100)resultfromdual;,14.7Oracle中的特殊判式,除了逻辑运算之外，Oracle提供了一些特殊判式。这些判式可以用来生成更加复杂和灵活的查询条件。本节将着重介绍以下几种判式。Between：取值范围。In：集合成员测试。Like：模式匹配。isnull：空值判断。all，some，any：数量判断。exists：存在性判断。,14.7.1between范围测试,between判式，用于判断某个值是否在另外两个值之间。这些值可以为数值型、字符串和日期型。使用betwwen判式来获得ID号在1-5之间的员工信息。select*fromt_employeeswhereemployee_idbetween1and5;betwwen判式同样可以应用于字符串和日期型。字符串是按照字母表的顺序进行比较，而日期型是按照日期的先后顺序进行比较。select*fromt_employeeswherebbetweenbandc;select*fromt_employeeswherebbetweenbcandc;注意与说明：between判式与=、all(selectdistinctsalaryfromt_salarywhereemployee_id=4oremployee_id=5);select*fromt_salarywheresalarysome(selectdistinctsalaryfromt_salarywhereemployee_id=4oremployee_id=5);此时的some判式实际相当于逻辑运算中的or运算，即salary6000orsalary7000。此时，使用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)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.3窗口子句,对于每条记录，一旦使用了窗口函数，都会为其产生一个可操作的记录集合。而对于该记录集，可以使用窗口子句，来进一步限制窗口范围。常用的窗口子句包括两类：利用rows子句的行方式进行限制；利用range子句的值方式进行限制。rows子句selectemployee_id,employee_name,sum(salary)over(orderbyemployee_idrowsbetween1precedingand1following)three_totalfromsalaryrows子句因为和位置相关，因此，在窗口函数中必须含有排序子句orderby。如果未使用orderby子句，而直接使用rows子句，Oracle将抛出错误提示，,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