Oracle函数与表达式

1、Oracle函数与表达式Oracle中提供了大量的内置函数，以处理各种形式的运算。这些函数涵盖了字符串运算、数值运算、日期运算等方面。同样，Oracle允许使用数值运算、逻辑运算等基本的表达式运算，另外，提供了SQL标准所规定的特殊判式。Oracle中的字符串函数；Oracle中的数学函数；Oracle中的日期函数；第14章Oracle中的函数数与表达达式Oracle中的聚合合函数；Oracle中的运算算表达式式；Oracle中的特殊殊判式；Oracle中的高级级函数分析函数数与窗口口函数。14.1Oracle中的字符符串函数数Oracle提供了丰丰富的字字符串函函数，本本小节将将通过实实例讲

2、述述Oracle中各字符符串函数数的使用用。14.1.1lpad()函数lpad()函数用于于左补全全字符串串。在某某些情况况下，预预期的字字符串为为固定长长度，而而且格式式统一，此时可可以考虑虑使用lpad()函数。例例如，深深市股票票代码都都以0开头，并并且都为为6位，可以以利用lpad格式化股股票代码码，以保保证股票票代码的的格式。selectlpad(21,6,0)stock_code fromdual;需要注意意的是，当原字字符串的的长度大大于预期期长度时时，实际际进行的的是截取取字符串串操作。selectlpad(1234567,6,0) stock_codefromdual;14

3、.1.2rpad()函数与lpad()函数相反反，rpad()函数从右右端补齐齐字符串串。selectrpad(abc,10, *)fromdual;注意与说说明：lpad()和rpad()都用于填填充字符符串，lpad()从左端进进行填充充，而rpad()从右端进进行填充充，但是是，二者者在最终终截取字字符串时时，都是是从左端端开始截截取。selectrpad(abcdefg, 6, *)fromdual;14.1.3lower()函数返回小写写字符串串lower()函数用于于返回字字符串的的小写形形式。lower()函数在查查询语句句中经常常扮演重重要角色色。例如如，对于于用户名名和密码码

4、的校验验来说，用户名名一般并并不区分分大小写写，用户户无论输输入了大大写还是是小写形形式，都都被认为为是合法法用户。因此，在数据据库查询询时，应应该将数数据库中中用户名名与用户户输入的的用户名名进行统统一。selectuser_id,user_name fromt_userswherelower(user_name) =lower(Alex);14.1.4upper()函数返回大写写字符串串upper()函数用于于返回字字符串的的大写形形式。与与lower()函数类似似，upper()函数也可可以用在在查询语语句中，以统一一数据库库和查询询条件的的一致性性。selectuser_id,user

5、_name fromt_userswhereupper(user_name) =upper(ALEX);注意与说说明：upper()函数和lower()函数只针针对英文文字符其其作用，因为只只有英文文字符才才有大小小写之分分。14.1.5initcap()函数单词首字字母大写写initcap()函数将单单词的首首字母大大写。selectinitcap(big)fromdual;需要注意意的是，initcap()函数不能能自动识识别单词词selectinitcap(bigbigtiger)from dual;initcap()函数会将将参数中中的非单单词字符符作为单单词分隔隔符selectini

6、tcap(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()函数来获

7、获得其长长度。length()函数会首首先将参参数转换换为字符符串，然然后计算算其长度度。selectlength(12.51)from dual;14.1.7substr()函数截取字符符串substr()函数用于于截取字字符串。该函数数可以指指定截取取的起始始位置，截取长长度，可可以实现现灵活的的截取操操作，因因此，成成为字符符串操作作中最常常用的函函数之一一。例如，对对于字符符串“1234567890”，现欲截截取自第第5位开始的的4个字符。selectsubstr(1234567890,5,4)fromdual;需要注意意的是，Oracle中字符位位置从1开始，而而不是像像某些编编程语

8、言言（如Java）那样从从0开始。如果不指指定长度度，那么么substr()函数将获获取起始始位置参参数至字字符串结结尾处的的所有字字符。selectsubstr(1234567890,5)fromdual;14.1.8instr()函数获得字符符串出现现的位置置instr()函数用于于获得子子字符串串在父字字符串中中出现的的位置。selectinstr(bigbigtiger,big)fromdual;可以指定定额外的的参数，以命令令该函数数从指定定位置开开始搜索索。selectinstr(bigbigtiger,big,2)fromdual;还可以指指定出现现次数参参数，以以指定是是第几次

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

10、m(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 d

11、ual;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()函

12、数用于于将ascii码转换为为字符串串。通过过chr()函数，可可以对不不宜直接接输入的的字符进进行操作作。例如如，将回回车换行行符插入入到数据据中。insertintotest_datavalues (6,周林|chr(13)|chr(10)|梁军,20);select*from test_datawhereid= 6;14.1.14translate()函数替换字符符translate()函数用于于替换字字符串。替换的的规则类类似于翻翻译的过过程。selecttranslate(56338, 1234567890,avlihemoqr)resultfromdual;需要注意意的是，当字符符

13、不能被被成功“翻译”，那么么，Oracle将使用空空字符替替换它。利用此此特性，可以使使用translate()函数来删删除一个个含有数数字和英英文字母母的字符符串中的的所有字字母：selecttranslate(21343yuioioizf899dasiwpe58595oda0j098,#abcdefghijklmnopqrstuvwxyz,)reulstfrom dual;14.2Oracle中的数学学函数Oracle提供的数数学函数数可以处处理日常常使用到到的大多多数数学学运算。本小节节将讲述述Oracle中常用的的几种数数学函数数。14.2.1abs()函数返回数字字的绝对对值abs(

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

15、二个参数数为负数数，那么么，相当当于将数数值精确确到小数数点之前前的位数数。selectround(2745,-1) resultfrom dual;14.2.3ceil()函数向上取整整ceil()函数只能能有一个个参数。该函数数将参数数向上取取整，以以获得大大于等于于该参数数的最小小整数。selectceil(21.897) resultfrom dual;需要注意意的是该该函数针针对负数数的运算算：selectceil(-21.897)resultfromdual;因为ceil()函数返回回的是大大于等于于参数的的最小整整数，所所以，该该函数返返回的并并非-22，而是-21。14.2.4

16、floor()函数向下取整整与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()函数只有有一个参参数。该该函数将将返回参参数的

17、正正负性。若返回回值为1，表示该该参数大大于0；若返回回值为-1，表示该该参数小小于0；若返回回值为0，表示该该参数等等于0。selectsign(8)resultfromdual;selectsign(-8)result fromdual;selectsign(0)resultfromdual;sign()函数为判判断两个个数值的的大小关关系提供供了方便便。因为为在oracle中，利用用类似ifelse的结构来来判断两两个数值值之间的的大小关关系，并并不像编编程语言言中那样样方便，而且极极易造成成代码的的复杂化化。14.2.7sqrt()函数返回数字字的平方方根sqrt()函数也只只有一个个

18、参数。该函数数用于返返回参数数的平方方根。可可以利用用round()函数和sqrt()函数返回回某个数数值的近近似平方方根。selectround(sqrt(2),3)result fromdual;14.2.8power()函数乘方运算算power()函数有两两个参数数。该函函数用于于实现数数值的乘乘方运算算。selectpower(6,2)result fromdual;14.2.9trunc()函数截取数字字trunc()函数用于于截取部部分数字字。其工工作机制制非常类类似于round()函数。与与round()函数不同同的是，该函数数不对数数值做四四舍五入入处理，而是直直接截取取。se

19、lecttrunc(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()函数在返

20、返回的是是Oracle实际存储储数据的的字节数数，在实实际开发发中使用用的几率率也较小小。读者者可以不不必了解解Oracle本身的存存储机制制。14.2.11to_number()函数将字符串串转换为为数值类类型to_number()函数可以以将字符符串转换换为数值值型。selectto_number(257.90)resultfromdual;需要注意意的是，被转换换的字符符串必须须符合数数值类型型格式。如果被被转换的的字符串串不符合合数值型型格式，Oracle将抛出错错误提示示。selectto_number(a)result fromdual;14.3Oracle中的日期期函数Oracl

21、e提供了丰丰富的日日期函数数。利用用日期函函数可以以灵活的的对日期期进行运运算。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 du

22、al;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

23、 ()函数返回两个个日期所所差的月月数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(

24、)函数返回当前前会话时时区的当当前日期期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()

25、函数用于于返回当当前会话话时的区区时间戳戳。可以以结合sessiontimezone来查看其其用法。selectsessiontimezone,current_timestampfrom dual;14.3.7extract()函数返回日期期的某个个域日期由若若干域组组成，例例如年、月、日日、小时时等等。extract()函数可以以返回这这些域的的具体值值。为了了使用该该函数，除了要要指定原原日期外外，还应应该指定定要返回回的域名名。selectextract(year fromsysdate)result fromdual;需要注意意的是，year、month、day域只能从从日期（如sys

26、date）中获得得，而hour、minute、second只能从时时间型（如systimestamp）中获得得。14.4Oracle中的聚合合函数所谓聚合合函数是是指针对对多条记记录的函函数。Oracle最常用的的聚合函函数包括括，max()、min()、avg()、sum()和count()函数。本本节将讲讲述这些些函数的的用法。14.4.1max()函数求最大值值max()函数用于于获得记记录集在在某列的的最大值值。例如如，为了了返回员员工最高高工资，可以利利用max()函数。selectmax(salary)max_salaryfrom t_salary;需要注意意的是，聚合函函数往往往

27、是返回回记录集集的统计计值，因因此，不不能与其其中的单单条记录录同时出出现。例例如，不不能将max(salary)与具体列列一起查查询。selectemployee_id, max(salary) max_salaryfromt_salary;selectdistincte.employee_name,s.salaryfrom t_employees e, t_salary swheree.employee_id =s.employee_idands.salary=(select max(salary) fromt_salary)14.4.2min()函数求最小值值min()函数可以以用来获获

28、得记录录集在某某列上的的最小值值，其功功能与max()函数相反反。selectdistincte.employee_name,s.salaryfrom t_employees e, t_salary swheree.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 swheree.emplo

29、yee_id =s.employee_idgroupbye.employee_id,e.employee_name14.4.4sum()函数求和sum()函数用于于获得结结果集上上某列值值的和。selecte.employee_name,sum(salary)from t_employees e, t_salary swheree.employee_id =s.employee_idgroupbye.employee_id,e.employee_name14.4.5count()函数获得记录录数count()函数的作作用对象象同样为为记录集集。与其其他聚合合函数不不同的是是，count()函数

30、可以以有三种种方式来来进行计计数：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)进行计数数的速度度最快，但是特特别注意意的是，预期的的结果是是

31、针对整整行数据据，还是是某列的的数据。14.5Oracle中的其他他函数除了数值值函数、字符串串函数、日期函函数和聚聚合函数数外，Oracle还提供了了其他功功能性更更强的函函数。本本节将介介绍decode()、nvl()和cast()函数。14.5.1decode()函数多值判断断decode()函数用于于多值判判断。其其执行过过程类似似于解码码操作。该函数数最常见见的应用用为，实实现类似似ifelse的功能。例如，可以利利用decode()函数为员员工工资资添加标标识，工工资大于于6000者为高收收入，其其余的为为一般收收入。selecte.employee_id,e.employee_n

32、ame,decode(sign(avg(s.salary) -6000),1,高收入,一般收入入)incommingfrom t_employees e, t_salary swheree.employee_id =s.employee_idgroupbye.employee_id,e.employee_name14.5.2nvl()函数为空值重重新赋值值nvl()函数用于于处理某某列的值值。该函函数有两两个参数数，第一一个参数数为要处处理的列列。如果果其值为为空，则则返回第第二个参参数的值值，否则则，将返返回列值值。selectemployee_id, nvl(employee_name,未

33、知)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)salaryfrom t_employees e,

34、t_salary swheree.employee_id =s.employee_id(+)groupbye.employee_id,e.employee_name14.5.3cast()函数强制转换换数据类类型cast()函数用于于强制转转换数据据类型。Oracle会根据操操作符来来自动进进行数据据类型的的转换，例如：select123+200result fromdual;Oracle会根据运运算符“+”将123转换为数数值型123。select123|200 resultfrom dual;Oracle会根据运运算符“|”将数字200转换为字字符串200。cast()函数最常常用的场场景

35、是转转换列的的数据类类型，以以创建新新表createtabletmp_salaryasselectcast(salary_idasvarchar2(20)salary_id,cast(employee_id as varchar2(20)employee_id,cast(month as varchar2(20)month,cast(salaryasvarchar2(20) salaryfrom t_salarydesc tmp_salary;14.6Oracle中的运算算表达式式Oracle中的常用用运算包包括：数数学运算算、逻辑辑运算和和按位运运算。本本节将通通过范例例着重讲讲述这三三种运

36、算算的常用用运算符符和运算算规则。14.6.1数学运算算数学运算算是最常常用的运运算方式式，Oracle中的数学学运算符符包括：+、-、*、/，分别代代表了加加、减、乘除运运算。在在使用数数学运算算时，Oracle会自动将将其他数数据类型型转换为为数值型型，然后后再参与与运算。select5+3 resultfrom dual;select5-3 resultfrom dual;select5*2resultfromdual;select5/2 resultfrom dual;需要注意意的是，任何一一种运算算符与null的运算结结果均为为null。select5+null resultfrom

37、 dual;select5-null resultfrom dual;select5*null resultfrom dual;select5/null resultfrom dual;14.6.2逻辑运算算Oracle中的逻辑辑运算包包括：大于运运算，可可用于数数值型、日期型型和字符符串类型型；=：大于等等于运算算，可用用于数值值型、日日期型和和字符串串类型；：小于运运算，可可用于数数值型、日期型型和字符符串类型型；=：大于等等于运算算，可用用于数值值型、日日期型和和字符串串类型；=：等于，可用于于数值型型、日期期型和字字符串类类型；：不等于于，可用用于数值值型、日日期型和和字符串串类型；!

38、=：与用法相同同；NOT：取反操操作；AND：布尔值值的与操操作；OR：布尔值值的或操操作。14.6.2逻辑运算算需要注意意的是，Oracle中的逻辑辑运算符符只能作作为条件件判断，并不返返回值。为了查查询工资资在5000-7000之间的记记录，可可以利用用逻辑运运算符来来组合查查询条件件。select*from t_salary where salary=5000andsalary=7000;对于null值，需要要特别注注意的是是，无论论使用哪哪种运算算符，结结果都会会返回null。当比较较的结果果为null，并作为为条件出出现时，Oracle都会将其其解释为为false。select1re

39、sultfromdual where 1=null;select1resultfromdual where 1null;select1resultfromdual where null=null;select1resultfromdual where nullnull;14.6.3位运算从Oracle8i开始，系系统已经经提供了了位运算算符。最最常用的的莫过于于bitand运算符。selectbitand(192,100)resultfromdual;14.7Oracle中的特殊殊判式除了逻辑辑运算之之外，Oracle提供了一一些特殊殊判式。这些判判式可以以用来生生成更加加复杂和和灵活的的查询

40、条条件。本本节将着着重介绍绍以下几几种判式式。Between：取值范范围。In：集合成成员测试试。Like：模式匹匹配。isnull：空值判判断。all，some，any：数量判判断。exists：存在性性判断。14.7.1between范围测试试between判式，用用于判断断某个值值是否在在另外两两个值之之间。这这些值可可以为数数值型、字符串串和日期期型。使使用betwwen判式来获获得ID号在1-5之间的员员工信息息。select*from t_employees where employee_id between1and5;betwwen判式同样样可以应应用于字字符串和和日期型型。字符

41、符串是按按照字母母表的顺顺序进行行比较，而日期期型是按按照日期期的先后后顺序进进行比较较。select*from t_employees where bbetween bandc;select*from t_employees where bbetween bcand c;注意与说说明：between判式与=、=的组合是是等价关关系。但但是，效效率上要要比后者者差。14.7.2in集合成员员测试in用于判断断某个值值是否一一个集合合的成员员。select*from t_employees where statusin(NEW,ACT);值得注意意的是，in判式中的的集合的的成员的的数据类类型可

42、以以不一致致，例如如，select*from t_employees where statusin(NEW,ACT, sysdate, 1)中的数据据类型包包含了字字符串、日期型型和数值值型。14.7.3like模式匹配配like判式的最最大特点点在于，可以使使用通配配符。其其通常的的应用场场景为处处理模糊糊查询。select*from t_employees where employee_namelike钟%;如果要求求字符串串中含有有原义字字符“%”，例如，含有百百分比的的字符串串。那么么，like判式应写写作：like 钟%escape 。Oracle会首先解解释escape关键字，并将

43、其其后的字字符“”解释为转转义字符符。那么么在“钟钟%”中的“%”不再表示示通配符符，而是是表示原原义字符符“%”。“_”（下划线线）是可可用于like判式的另另一个通通配符，该通配配符表示示一个任任意的字字符。14.7.4is null空值判断断在逻辑判判断中，对于列列值为空空的判断断，不能能使用=或者。oracle对与空值值的判断断提供了了专门的的判式is null。例如，为了获获取表t_employees中员工信信息不全全的记录录，可以以利用如如下所示示的查询询语句。select*from t_employeeswhereemployee_idisnulloremployee_name

44、is nullorwork_years is nullorstatusisnull;14.7.5exists存在性判判断in判式用于于判断表表的列值值是否存存在于列列表（集集合）中中。而exists判式则可可用于判判断查询询结果集集合是否否为空。例如，为了查查询出表表t_employees所存储的的员工信信息中，哪些员员工存在在于工资资表中，即可利利用exists判式。select*from t_employees ewhereexists(select *fromt_salarywhereemployee_id= e.employee_id);14.7.6all，some，any数量判断断a

45、ll，some和any判式的作作用对象象为记录录集合。all表示，记记录集中中的所有有记录，some表示其中中的一些些记录，any判式则表表示其中中的任意意记录。例如，在员工工工资表表t_salary中，为了了查找高高于id为4和5的工资信信息，即即可使用用all判式。select*from t_salary where employee_id =4oremployee_id= 5;select*from t_salary where salary all(selectdistinctsalary fromt_salarywhereemployee_id=4 or employee_id =5

46、);select*from t_salary where salary some(select distinct salaryfrom t_salary where employee_id =4oremployee_id= 5);此时的some判式实际际相当于于逻辑运运算中的的or运算，即即salary6000orsalary7000。此时，使用any判式，将将返回同同样的结结果。14.8Oracle高级函数数分析函数数与窗口口函数Oracle中的分析析函数具具有非常常强大的的功能。分析函函数往往往与另一一类函数数窗口函数数同时使使用。窗窗口函数数总是为为查询过过程中的的当前记记录提供供一个相

47、相关记录录集，而而且随着着当前记记录的推推移，相相应的记记录集也也会随之之改变，这非常常类似于于“滑动动窗”的的概念。分析函函数的操操作对象象即为“滑动窗窗”所指指定的记记录集合合。本节节将通过过实例来来讲述分分析函数数和窗口口函数的的使用。14.8.1排名分析函数数中的排排名函数数可以针针对窗口口中的记记录生成成排序序序号。常常用的排排名函数数有rank()、dense_rank()和row_number()。rank()函数用于于返回当当前记录录在窗口口函数所所指定的的记录集集中的排排名。rank()函数在排排名过程程中，具具有跳跃跃的特点点。select*fromstudents;sel

48、ectstudent_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关键字可可以指定定分区窗窗口。现欲统计计各员工工的工资资在各自自部门的的高低情情况，则则可以利利用

49、partitionby进行分区区，然后后利用分分析函数数对分区区内的记记录进行行统计selectt.*,dense_rank()over(partitionbydepartmentorderbysalary) position fromsalary torderbyt.employee_id另外一种种常见需需求为，在获得得员工工工资的同同时，也也需要部部门所有有员工的的工资总总额selectt.*,sum(salary)over(partitionbydepartment)total_salary,round(avg(salary)over(partitionbydepartment)aver

50、age_salaryfromsalary torderbyemployee_id注意，avg(salary)over(partitionbydepartment)是不可分分割的一一个整体体。对于于数据表表salary中每条记记录都会会返回单单个值，因此，当使用用round()函数，函函数的作作用对象象应为avg(salary)over(partitionbydepartment)这个整体体，而不不能使用用诸如round(avg(salary)over(partitionbydepartment)等形式。另外，利用partitionby进行分区区之后，当前记记录总是是处于某某个分区区中，此此时的

51、窗窗口即为为该分区区。14.8.3窗口子句句对于每条条记录，一旦使使用了窗窗口函数数，都会会为其产产生一个个可操作作的记录录集合。而对于于该记录录集，可可以使用用窗口子子句，来来进一步步限制窗窗口范围围。常用用的窗口口子句包包括两类类：利用rows子句的行行方式进进行限制制；利用range子句的值值方式进进行限制制。rows子句selectemployee_id, employee_name,sum(salary)over(order by employee_id rowsbetween1 precedingand1following) three_total fromsalaryrows子句

52、因为为和位置置相关，因此，在窗口口函数中中必须含含有排序序子句orderby。如果未未使用orderby子句，而而直接使使用rows子句，Oracle将抛出错错误提示示，14.8.3窗口子句句注意，rows betwwen1precedingand 1following不一定返返回3条记录。例如，对于employee_id为1的记录，排序之之后，该该记录为为第一条条记录，不存在在前一条条记录，因此只只返回两两条记录录，而求求和操作作返回的的实际为为employee_id为1和2的员工的的工资总总和10500。2.range子句range子句按照照列值进进行窗口口的进一一步限制制。selectemployee_id, employee_name,count(1)over(parti