Oracle中常用的函数与表达式.ppt

14.8.3 窗口子句 对于每条记录，一旦使用了窗口函数，都会为其产生一个可操作的记录集合。而对于该记录集，可以使用窗口子句，来进一步限制窗口范围。常用的窗口子句包括两类： 利用rows子句的行方式进行限制； 利用range子句的值方式进行限制。 rows子句 select employee_id, employee_name, sum(salary) over(order by employee_id rows between 1 preceding and 1 following) three_total from salary rows子句因为和位置相关，因此，在窗口函数中必须含有排序子句order by。如果未使用order by子句，而直接使用rows子句，Oracle将抛出错误提示， 14.8.3 窗口子句 注意，rows betwwen 1 preceding and 1 following不一定返回3条记录。例如，对于employee_id为1的记录，排序之后，该记录为第一条记录，不存在前一条记录，因此只返回两条记录，而求和操作返回的实际为employee_id为1和2的员工的工资总和10500。 2. range子句 range子句按照列值进行窗口的进一步限制。 select employee_id, employee_name, count(1) over(partition by department order by salary range between 500 preceding and 500 following) as employee_count from salary order by employee_id 3. unbounded和current row 在rows和range子句中，除了使用具体的数值来决定窗口的大小之外，还可以使用关键字unbounded和current row。unbounded可以直接代替数值，表示没有任何限制；current row则直接代表当前行。 14.8.4 主要的分析函数 分析函数作用对象为窗口函数所捕获的记录集，因此，分析函数具有聚合函数的特点，大多数的聚合函数，如sum()、count()、max()等都能作为分析函数出现。Oracle还提供了专门针对窗口函数的分析函数，本小节着重讲述常用的几种——fist_value()、last_value()、lag()和lead()。 1．fist_value()函数的使用 select distinct department, first_value(employee_name) over(partition by department order by salary) employee_name, first_value(salary) over(partition by department order by salary) salary from salary 2．last_value()函数的使用 3．lead()函数的使用 select employee_id, employee_name, salary, lead(employee_name, 1, N/A) over (partition by department order by salary) prev_name from salary order by employee_id 4．lag()函数的使用 14.9 本章实例 Oracle虽然内置了很多函数，但是并不能满足日常开发中的应用。此时，需要开发者自定义函数，而内置函数往往成为自定函数的基石。 create or replace function is_date (param varchar2) return varchar2 is val date; begin val := to_date (nvl (param, ), yyyy-mm-dd hh24:mi:ss); return Y; exception when others then return N; end; select is_date(abc) from dual; select is_date(2009-09-01) from dual; 14.10 本章小结 本章通过详尽的实例讲述了Oracle中常用的内置函数、表达式以及特殊判式。对于内置函数，特别需要注意的是聚合函数的使用。聚合函数最常用的场景为分组查询；聚合函数不能与单条记录的列并列作为查询结果。对于特殊判式，尤其应该注意的是like判式的使用，like判式使用中，通配符只有“%”和“_”两种，要注意通配符和正则