Oracle函数及其查询

1、Oracle函数与查询单行函数数可以出出现在SELECT语句的SELECT子句WHERE子句ORDERBY子句STARTWITH子句CONNECT BY子句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)SI

2、GN(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)返回数值值型的字字符函数数AS

3、CIIINSTRINSTRBLENGTHLENGTHBNLSSORT日期函数数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.SQ

4、LSELECTto_char(to_date(79-01-31,YY-MM-DD),YYYY-MM-DD)FROM dual;SELECTto_char(to_date(79-01-31,RR-MM-DD),RRRR-MM-DD)FROM dual;2079-01-311979-01-311.单行行函数Oracle数据库中中的类型型转换有有两种自动类型型转换：字符型型和日期期型、字字符型和和数值型型间强制类型型转换：使用类类型转换换函数常用的转转换函数数TO_CHAR (NUMBER|date,format)TO_NUMBER(char,format)TO_DATE (char,format)

5、常用的数数值格式式掩码9-数字字字符0-数据据有前导导0.-小数数点,-千位位分隔符符$-美元元符号$L-显示本地地货币符符号（人人民币为为￥）1.单行行函数其他常用用单行函函数空值(NULL)与其他数数据运算算的结果果一般均均为空值值根据需要要，一般般将空值值转换为为数值0或1，否则计计算表达达式将得得不到正正确的值值2.分组组函数分组函数数对一组组查询行行返回一一个结果果值除count(*)外，分组组函数均均忽略空空值如要包括括空值，须使用用空值转转换函数数分组函数数不可以以用在WHERE子句中用用以限定定查询的的结果，对分组组查询结结果的限限定应使使用HAVING子句在分组函函数中可可有

6、下面面两个选选项(1)DISTINCT选项：使使分组函函数只考考虑列表表达式中中的不同同值(2)ALL选项：使使分组函函数考虑虑全部值值，其中中包含重重复值2.分组组函数主要的分分组函数数例25分分组组函数综综合应用用。EXA_08_25.SQL3.DateTime函数在DATE类型的基基础上引引入三种种日期时时间类型型TIMESTAMPTIMESTAMPWITHTIME ZONE(TSTZ)TIMESTAMPWITHLOCALTIME ZONE(TSLTZ)时区参数数数据库时时区DBTIMEZONE会话时区区SESSIONTIMEZONE连接会话话参数TIME_ZONE设置会话话时区与与UT

7、C时间的时时间差TIME_ZONE=+|-hh:mm+表示比UTC提前，-表示比比UTC滞后北京时间间时区设设置ALTERSESSION SETTIME_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

8、)AS次序FROMemployeesGROUPBYdepartment_id,job_id;4用于数据据分析的的SQL多表插入入可将一一条源记记录根据据条件向向多个表表插入，以满足足不同的的数据观观察要求求INSERTALLFIRSTWHENcondition1THENINTO table1VALUES(column_val,.)WHENconditionXTHENINTO tableXVALUES(column_val,.)ELSEINTO tableYVALUES(column_val,.)SELECT.;ALL：对所有WHEN条件进行行检查，只要满满足条件件就执行行对应的的插入操操作FI

9、RST：找到第一一个满足足条件的的WHEN子句，执执行对应应插入操操作后即即结束当当前记录录的处理理源数据由由SELECT子句提供供1.多表表插入与与旋转插插入多表插入入操作有如如下限制制(1)只只能对对基表执执行多表表插入，不能对对视图和和实体化化视图执执行多表表插入操操作(2)不不能对对远程表表执行多多表插入入操作例28对对雇员员表中编编号大于于200的雇员员的记录录，将工工资超过过10000的的雇员记记录插入入到sal_history表中，将将其经理理的雇员员编号超超过200的雇雇员记录录插入到到mgr_history表中。EXA_08_28.SQL1.多表表插入与与旋转插插入旋转数据据

10、插入可将一个个规范化化程度不不高的表表中的数数据转换换到规范范化的表表中例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分析语法法SELECTcolumn_list,RO

11、WNUMFROM (SELECTcolumn_list FROM tableORDERBYTop-N_columnASC|DESC)WHEREROWNUM=N;取最大的的前N个值，ORDERBY子句需指指明DESC取最小的的前N个值，ORDERBY子句需指指明ASC用ROWNUM限制取得得的结果果记录数数3.ROLLUP与CUBE例31从从最细细的数据据颗粒(同时考考虑部门门、职位位和受聘聘时间3个因素素)到相相对高的的数据层层次(只只考虑部部门和职职位两个个因素)，再到到更高的的数据层层次(仅仅考虑部部门)和和整个单单位(不不考虑任任何因素素)4个个不同数数据层次次来观察察和分析析公司的的工

12、资情情况。EXA_08_31.SQLGROUPBY之后的列列(各维维)是有有层次的的，最右右边的为为最低层层，最左左边的为为最高层层ROLLUP对GROUPBY定义的维维分层计计算各个个层次汇汇总值CUBE的结果包包括ROLLUP产生的行行和交叉叉表行(维组合合的聚集集值)数据聚集集操作(ROLLUP)ROLLUP从右至左左计算GROUPBY定义的维维分组的的小计并并累计该该值至最最终的合合计。若给定n维分组，ROLLUP将产生n+1层汇总数数据3.ROLLUP与CUBE例32CUBE应用。EXA_08_32.SQLCUBE操作提供供了从数数据的不不同侧面面了解其其内涵的的方法，把CUBE称作

13、数据据立方体体操作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使使用用复合列列，跳过过某些数数据