Oracle函数与查询

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)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])返回数值型的字符函数ASCIIINSTRINSTRBLENGTHLENGTHBNLSSORT日期函数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.SQLSELECTto_char(to_date('79-01-31','YY-MM-DD'),'YYYY-MM-DD')FROMdual;SELECTto_char(to_date('79-01-31','RR-MM-DD'),'RRRR-MM-DD')FROMdual;2079-01-311979-01-311.单行函数Oracle数据库中的类型转换有两种自动类型转换：字符型和日期型、字符型和数值型间强制类型转换：使用类型转换函数常用的转换函数TO_CHAR(NUMBER|date,'format')TO_NUMBER(char,'format')TO_DATE(char,'format')常用的数值格式掩码9-数字字符0-数据有前导0.-小数点,-千位分隔符$-美元符号$L-显示本地货币符号（人民币为￥）1.单行函数其他常用单行函数空值(NULL)与其他数据运算的结果一般均为空值根据需要，一般将空值转换为数值0或1，否则计算表达式将得不到正确的值2.分组函数分组函数对一组查询行返回一个结果值除count(*)外，分组函数均忽略空值如要包括空值，须使用空值转换函数分组函数不可以用在WHERE子句中用以限定查询的结果，对分组查询结果的限定应使用HAVING子句在分组函数中可有下面两个选项(1)DISTINCT选项：使分组函数只考虑列表达式中的不同值(2)ALL选项：使分组函数考虑全部值，其中包含重复值2.分组函数主要的分组函数例25分组函数综合应用。EXA_08_25.SQL3.DateTime函数在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.SQL4.OLAP函数Oracle提供了一组用于联机分析处理(OLAP)的SQL函数例27对各部门各职位的工资总额进行从高到低排序。EXA_08_27.SQLSELECTdepartment_id,job_id,SUM(salary),RANK()OVER(ORDERBYSUM(salary)DESC)AS次序FROMemployeesGROUPBYdepartment_id,job_id;4用于数据分析的SQL多表插入可将一条源记录根据条件向多个表插入，以满足不同的数据观察要求INSERT[ALL][FIRST]WHENcondition1THENINTOtable1VALUES(column_val,...)……WHENconditionXTHENINTOtableXVALUES(column_val,...)ELSEINTOtableYVALUES(column_val,...)SELECT...;ALL：对所有WHEN条件进行检查，只要满足条件就执行对应的插入操作FIRST：找到第一个满足条件的WHEN子句，执行对应插入操作后即结束当前记录的处理源数据由SELECT子句提供1.多表插入与旋转插入多表插入操作有如下限制(1)只能对基表执行多表插入，不能对视图和实体化视图执行多表插入操作(2)不能对远程表执行多表插入操作例28对雇员表中编号大于200的雇员的记录，将工资超过10000的雇员记录插入到sal_history表中，将其经理的雇员编号超过200的雇员记录插入到mgr_history表中。EXA_08_28.SQL1.多表插入与旋转插入旋转数据插入可将一个规范化程度不高的表中的数据转换到规范化的表中例29现有雇员销售记录表SALES_SOURCE_DATA，包含雇员编号EMPLOYEE_ID，星期编号WEEK_ID，周一销量SALES_MON，周二销量SALES_TUE，周三销量SALES_WED，周四销量SALES_THUR，周五销量SALES_FRI等列。。EXA_08_29.SQL2.Top-n查询Top-n用于取某列数据中最大或最小的n个值例30按从高到低的次序显示工资最高的5个人。EXA_08_30.SQLTop-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个不同数据层次来观察和分析公司的工资情况。EXA_08_31.SQLGROUPBY之后的列(各维)是有层次的，最右边的为最低层，最左边的为最高层ROLLUP对GROUPBY定义的维分层计算各个层次汇总值CUBE的结果包括ROLLUP产生的行和交叉表行(维组合的聚集值)数据聚集操作(ROLLUP)ROLLUP从右至左计算GROUPBY定义的维分组的小计并累计该值至最终的合计。若给定n维分组，ROLLUP将产生n+1层汇总数据3.ROLLUP与CUBE例32CUBE应用。EXA_08_32.SQLCUBE操作提供了从数据的不同侧面了解其内涵的方法，把CUBE称作数据立方体操作CUBE将GROUPBY子句中的各维进行组合(两两组合、三个组合，以此类推)，形成交叉表，并计算各维组合的聚集值如果GROUPBY子句有n个列名(也称n维)，则CUBE操作将产生2n个分组组合3.ROLLUP与CUBE例33GROUPING函数的应用。EXA_08_33.SQLGROUPING函数用于标识某个列是否参与了聚集值的计算返回值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)连接分组提供了一种组合分组的简便方法，它将分组集合中的各分组进行连接，可产生大量的分组组合