《《精编》Oracle函数及其查询》由会员分享，可在线阅读，更多相关《《精编》Oracle函数及其查询（22页珍藏版）》请在金锄头文库上搜索。
1、Oracle函数与查询 单行函数可以出现在SELECT语句的SELECT子句WHERE子句ORDERBY子句STARTWITH子句CONNECTBY子句 1 单行函数 单行函数特点 1 基于单行返回结果 2 允许参数是其他单行函数产生的结果 3 对查询返回的各行分别进行计算 4 可以用常量 变量 列名以及表达式作为参数 数值函数接受数值型输入数据 并返回数值型的结果 1 单行函数 ABS n CEIL n COS n COSH n EXP n LN n LOG m n MOD m n POWER m n FLOOR n ROUND m n SIGN n SIN n SINH n SQRT n
2、TAN n TANH n TRUNC m n 字符函数 1 单行函数 LOWER char UPPER char INITCAP char CONCAT char1 char2 SUBSTR char m n LENGTH char INSTR char1 char2 m n LPAD char1 n char2 RPAD char1 n char2 TRIM leading trailing both trim charFROMtrim source REPLACE char1 char2 char3 返回数值型的字符函数ASCIIINSTRINSTRBLENGTHLENGTHBNLSSOR
3、T 日期函数 1 单行函数 MONTHS BETWEEN d1 d2 ADD MONTHS d n NEXT DAY d s LAST DAY d ROUND date fmt TRUNC date fmt 1 单行函数 Oracle使用内部的数值格式表示日期和时间 默认的日期显示格式是DD MON RR 日 月 年 可设定掩码指定日期型数据的格式 1 单行函数 RR格式表示的实际年份 与当前年份 在本世纪中的位置 给定的两位年份数有关 例 假设当前年份为2009 EXA 08 P167 RR SQL SELECTto char to date 79 01 31 YY MM DD YYYY M
4、M DD FROMdual SELECTto char to date 79 01 31 RR MM DD RRRR MM DD FROMdual 2079 01 31 1979 01 31 1 单行函数 Oracle数据库中的类型转换有两种自动类型转换 字符型和日期型 字符型和数值型间强制类型转换 使用类型转换函数 常用的转换函数TO CHAR NUMBER date format TO NUMBER char format TO DATE char format 常用的数值格式掩码9 数字字符0 数据有前导0 小数点 千位分隔符 美元符号 L 显示本地货币符号 人民币为 1 单行函数 其他
5、常用单行函数 空值 NULL 与其他数据运算的结果一般均为空值根据需要 一般将空值转换为数值0或1 否则计算表达式将得不到正确的值 2 分组函数 分组函数对一组查询行返回一个结果值 除count 外 分组函数均忽略空值如要包括空值 须使用空值转换函数 分组函数不可以用在WHERE子句中用以限定查询的结果 对分组查询结果的限定应使用HAVING子句 在分组函数中可有下面两个选项 1 DISTINCT选项 使分组函数只考虑列表达式中的不同值 2 ALL选项 使分组函数考虑全部值 其中包含重复值 2 分组函数 主要的分组函数 例25分组函数综合应用 EXA 08 25 SQL 3 DateTime函
6、数 在DATE类型的基础上引入三种日期时间类型TIMESTAMPTIMESTAMPWITHTIMEZONE TSTZ TIMESTAMPWITHLOCALTIMEZONE TSLTZ 时区参数数据库时区DBTIMEZONE会话时区SESSIONTIMEZONE 连接会话参数TIME ZONE设置会话时区与UTC时间的时间差TIME ZONE hh mm 表示比UTC提前 表示比UTC滞后 北京时间时区设置ALTERSESSIONSETTIME ZONE 8 00 3 DateTime函数 DateTime函数 例26DateTime函数应用EXA 08 26 SQL 4 OLAP函数 Orac
7、le提供了一组用于联机分析处理 OLAP 的SQL函数 例27对各部门各职位的工资总额进行从高到低排序 EXA 08 27 SQL SELECTdepartment id job id SUM salary RANK OVER ORDERBYSUM salary DESC AS次序FROMemployeesGROUPBYdepartment id job id 4用于数据分析的SQL 多表插入可将一条源记录根据条件向多个表插入 以满足不同的数据观察要求 INSERT ALL FIRST WHENcondition1THENINTOtable1VALUES column val WHENcond
8、itionXTHENINTOtableXVALUES column val ELSEINTOtableYVALUES column val SELECT ALL 对所有WHEN条件进行检查 只要满足条件就执行对应的插入操作FIRST 找到第一个满足条件的WHEN子句 执行对应插入操作后即结束当前记录的处理源数据由SELECT子句提供 1 多表插入与旋转插入 多表插入操作有如下限制 1 只能对基表执行多表插入 不能对视图和实体化视图执行多表插入操作 2 不能对远程表执行多表插入操作 例28对雇员表中编号大于200的雇员的记录 将工资超过10000的雇员记录插入到sal history表中 将其经
9、理的雇员编号超过200的雇员记录插入到mgr history表中 EXA 08 28 SQL 1 多表插入与旋转插入 旋转数据插入可将一个规范化程度不高的表中的数据转换到规范化的表中 例29现有雇员销售记录表SALES SOURCE DATA 包含雇员编号EMPLOYEE ID 星期编号WEEK ID 周一销量SALES MON 周二销量SALES TUE 周三销量SALES WED 周四销量SALES THUR 周五销量SALES FRI等列 EXA 08 29 SQL 2 Top n查询 Top n用于取某列数据中最大或最小的n个值 例30按从高到低的次序显示工资最高的5个人 EXA 08
10、 30 SQL Top n分析语法SELECT column list ROWNUMFROM SELECT column list FROMtableORDERBYTop N column ASC DESC WHEREROWNUM N 取最大的前N个值 ORDERBY子句需指明DESC取最小的前N个值 ORDERBY子句需指明ASC用ROWNUM限制取得的结果记录数 3 ROLLUP与CUBE 例31从最细的数据颗粒 同时考虑部门 职位和受聘时间3个因素 到相对高的数据层次 只考虑部门和职位两个因素 再到更高的数据层次 仅考虑部门 和整个单位 不考虑任何因素 4个不同数据层次来观察和分析公司的
11、工资情况 EXA 08 31 SQL GROUPBY之后的列 各维 是有层次的 最右边的为最低层 最左边的为最高层ROLLUP对GROUPBY定义的维分层计算各个层次汇总值CUBE的结果包括ROLLUP产生的行和交叉表行 维组合的聚集值 数据聚集操作 ROLLUP ROLLUP从右至左计算GROUPBY定义的维分组的小计并累计该值至最终的合计 若给定n维分组 ROLLUP将产生n 1层汇总数据 3 ROLLUP与CUBE 例32CUBE应用 EXA 08 32 SQL CUBE操作提供了从数据的不同侧面了解其内涵的方法 把CUBE称作数据立方体操作 CUBE将GROUPBY子句中的各维进行组合
12、 两两组合 三个组合 以此类推 形成交叉表 并计算各维组合的聚集值 如果GROUPBY子句有n个列名 也称n维 则CUBE操作将产生2n个分组组合 3 ROLLUP与CUBE 例33GROUPING函数的应用 EXA 08 33 SQL GROUPING函数用于标识某个列是否参与了聚集值的计算返回值0 表示对应的参数列参与了聚集值的计算返回值1 表示对应的参数列未参与聚集值的计算 例343个维分组 明确地告知系统只从这3个角度观察数据EXA 08 34 SQL 对分组的集合操作 GROUPINGSETS子句 在一个SELECT语句中定义多个维分组各维分组用 括起来 用逗号分隔各维分组分别计算每个维分组的值 然后执行UNIONALL操作 3 ROLLUP与CUBE 例35使用复合列 跳过某些数据层次 EXA 08 35 SQL 复合列 CompositeColumns 复合列用括号括起来 将它们当作一个整体看待使用复合列可跳过某些聚集层次 只处理用户关心的数据层次和组合关系 例36连接分组 EXA 08 36 SQL 连接分组 ConcatenatedGroups 连接分组提供了一种组合分组的简便方法 它将分组集合中的各分组进行连接 可产生大量的分组组合
