oracle函数使用小结
ascii（x）（字符）chr（x）（字符）concat（x，y）（字符）hextoraw（x）（字符）initcap（x）（字符）
instr（char1,char2[,n[,m]]）（字符）instrb(w,x[,y[,z]])（字符）length（x）（字符）lengthb（x）（字符）lower（x）（字符）lpad（x，y[,z]）（字符）
ltrim（x[,y]）（字符）nls_initcap(char[,‘nls_param’])（字符）nls_lover(char[,‘nls_param’])（字符）
nls_sort(char[,‘nls_param’])
nls_upper（字符）regexp_replace(source_string,pattern[,position[,occurrence[,match_parameter]]])
字符函数regexp_substr(source_string,pattern[,position[,occurrence[,match_parameter]]])
replace(x,y[,z])（字符）rpad（x,y[,z]）（字符）rtrim（char，[，set]）（字符）soundex（x）（字符）substr(char,m,[,n])
translate(char,from_string[,to_string])upper(x)（字符）数值函数abs(x)（数值）acos(x)asin（x）atan（x）atan2（n，m）CEIL(x)cos（x）（数值）cosh（x）（数值）exp（x）（数值）floor（x）（数值）ln（x）（数值）log（x，y）（数值）mod（x,y）（数值）power（x,y）（数值）round（x[,y]）（数值）sign（x）（数值）sin（x）（数值）sinh（x）（数值）sqrt（x）（数值）tan（x）（数值）tanh（x）（数值）trunc(x[,y])（数值）add_months（x，y）（日期）current_datecurrent_timestampdbtimezone
extract
from_tzlast_day（x）（日期）localtimestamp
months_between（x,y）（日期）new_time（x,zone1, zone2 ）（日期）next_day（x,char）（日期）NUMTODSINTERVAL（n,char_expr）NUMTOYMINTERVAL(n，char_expr)ROUND (D[,char])
SESSIONTIMEZONE
日期函数SYS_EXTRACT_UTC（datetime_with_timezone）
sysdate（日期）systimestampto_dsinterval(char,'nls_param')
to_timestamp(char，[fmt[,nls_param]])
to_timestamp_tz(char，[fmt[,nls_param]])
to_yminterval(char)trunc(x[,y])（日期）
转换函数ASCIISTR(string)
bin_to_num[exp[,expr][,expr]……]
cast（expr as datetype）chartorowid（x）（转换）compose（string）
convert（char，dest_char_set,source_char_set）
hextorawrawtohex（x）（转换）rawtonhexrowidtochar（x）（转换）to_char(date[,fmt[,nlsparam]])（转换）to_char(n[,fmt[,nlsparam]])（转换）to_clob(char)to_date(x[,y])（转换）to_lob(longcoulumn)to_multi_byte(x)（转换）to_number(x,y[,z])（转换）to_single_byte(x)（转换）translate…usingusistr
avg([distinct|all]x]（聚组）corr（expr1，expr2）count（*|distinct|all]x）（聚组）
covar_pop(expr1,expr2)
covar_samp(expr1,expr2)max([distinct|all]x)（聚组）min([distinct|all]x)（聚组）stddev([distinct|all]x)（聚组）stddev_pop(expr)（聚组）stddev_samp(expr)（聚组）sum([distinct|all]x)（聚组）var_pop(expr)var_samp(expr)variance([distinct|all]x)（聚组）
bfilename('directory','filename')其他函数
聚组函数coalesce（expr1,expr2,expr3…）decode（expr,search,result1,result2）
dump（w,[x[,y[,z]]]x）
empty_blob()empty_clob()greatest（x[,y],[,..]）least（x[,y][,…]）nullif(expr1,expr2)nvl（x,y）nvl2(expr1,expr2,expr3)uiduseruserenv(x)返回字符串首字母的ascii码值将ascii码值转为字符返回串x和串y接结果，连接字符串类似于‘||’将含有16进制数据的字符串转换成二进制数据返回串x首字符大写，其余字符小写的结果返回子串char2在串char1中的位置，n为起始搜索位置，m为子串出现的次数，若n为负数则从尾部开始搜索，m为正整数，n，m默认为1同上，只在多字节字符时有用返回串x的长度返回串x的字节长度返回串x小写字母结果返回串x左边填充z至长度为y的结果返回串x从左边开始过滤字符集为y后的结果，省略y则过滤空格，如果有一次不匹配，则不进行检索select ltrim('dfffddffdd', 'd') from dual 结果：fffddffdd将字符x的首字母大写，其他小写,‘nls_param'的格式为‘nls_sort =sort’用于指定特定语言的特征基本同lower，只是考虑到语言
指按特定语言的要求进行排序
基本同upper，只是考虑到语言oracle10g新增函数，扩展了replace的功能，按照特定表达式的规则来替换字符串 source_string：源字符串，pattern：规则表达式 replace：替换的字符串，position：指定起始搜索位置，occurrence用于指定替换出现的第n个字符串，match_parameter:指定默认匹配操作的文本串
oracle常用sql函数小结oracle10g新增函数，扩展了substr的功能，按照特定表达式的规则来返回字符串的子串。 source_string：源字符串，pattern：规则表达式replace：替换的字符串，position：指定起始搜索位置，occurrence用于指定替换出现的第n个字符串，match_parameter:指定默认匹配操作的文本串
将x中出现y的地方替换成z返回串x右边填充z至长度为y的结果返回串char右边截去字符集为set后的结果返回x语言发音描述返回字符串的子串，m是起始位置，n为子串的长度
将字符串char中按照from_string和to_string的对应关系进行转换返回x的大写串返回x的绝对值返回x的反余弦值，输入范围为（-1～＋1），输出弧度返回x的反正弦值，输入范围为（-1～＋1），输出弧度返回x的反正切值，输入范围为任意数值，输出弧度返回n除以m的反正切值，输入m不能为0 的任意数字，输出弧度返回大于等于x 的最小整数返回x的余弦返回x的双曲余弦值返回e的x次幂返回小于或等于x的最大整数返回x的自然对数（n>=0）返回x为底的y的对数（x为除0和1的正整数，y为任意正整数）返回x除以y的余数 如果y为0返回x返回x的y次幂，如果x为负数，y必须为正数返回将x四舍五入成y位小数的值，y为空则返回整数用于检测数字的正负：x为正数、负数、0时分别为1、－1、0返回x（弧度）的正弦值返回x的双曲正弦值返回x的平方根返回x的正切返回x的双曲正切值用于截取数字，如果省略y则将x的小数部分截去，若y为正数，则将x截取至小数点后y位，若为负数，则将x截取至小数点前y位返回x截为y位小数的结果值
返回值为日期x加上y个月返回当前回话时区日期时间（oracle9i）返回当前回话时区日期时间（oracle9i）返回数据库所在时区（oracle9i）
从日期时间中取得所需要的数据（oracle9i）
用于将特定时区的timestamp值转变为 timestamp with zone值SELECT FROM_TZ(TIMESTAMP '2000-03-28 08:00:00', '3:00') FROM DUAL;结果：28-MAR-00 08.00.00 AM +03:00
返回x指定的那个月的最后一天返回当前会话时区的日期时间
返回x和y之间的月数返回时间区域zone1中的时间x在zone2区域的对应时间.注：zone1 ，zone2的形式可以参考oracle参考手册
返回日期x之后第一个名为y的那天的日期将n转换为INTERVARDAY TO SECOND,其中char_expr 可以为'DAY','HOUR','MINUTE','SECOND'将n转换为INTERVARDAY TO SECOND,其中char_expr 可以为'year','month'返回日期时间的四舍五入结果，若fmt指定年格式则以07.01为界，指定月则以16号为界，指定天则以中午12点为界
返回当前会话所在时区（oracle9i）
Oracle 分析函数的使用
Oracle 分析函数的使用Oracle 分析函数使用介绍分析函数是oracle816引入的一个全新的概念,为我们分析数据提供了一种简单高效的处理方式.在分析函数出现以前,我们必须使用自联查询,子查询或者内联视图,甚至复杂的存储过程实现的语句,现在只要一条简单的sql语句就可以实现了,而且在执行效率方面也有相当大的提高.下面我将针对分析函数做一些具体的说明.今天我主要给大家介绍一下以下几个函数的使用方法1. 自动汇总函数rollup,cube,2. rank 函数, rank,dense_rank,row_number3. lag,lead函数4. sum,avg,的移动增加,移动平均数5. ratio_to_report报表处理函数6. first,last取基数的分析函数基础数据Code: [Copy to clipboard]06:34:23 SQL> select * from t;BILL_MONTH AREA_CODE NET_TYPE LOCAL_FARE--------------- ---------- ---------- --------------200405 5761 G 7393344.04200405 5761 J 5667089.85200405 5762 G 6315075.96200405 5762 J 6328716.15200405 5763 G 8861742.59200405 5763 J 7788036.32200405 5764 G 6028670.45200405 5764 J 6459121.49200405 5765 G 13156065.77200405 5765 J 11901671.70200406 5761 G 7614587.96200406 5761 J 5704343.05200406 5762 G 6556992.60200406 5762 J 6238068.05200406 5763 G 9130055.46200406 5763 J 7990460.25200406 5764 G 6387706.01200406 5764 J 6907481.66200406 5765 G 13562968.81200406 5765 J 12495492.50200407 5761 G 7987050.65200407 5761 J 5723215.28200407 5762 G 6833096.68200407 5762 J 6391201.44200407 5763 G 9410815.91200407 5763 J 8076677.41200407 5764 G 6456433.23200407 5764 J 6987660.53200407 5765 G 14000101.20200407 5765 J 12301780.20200408 5761 G 8085170.84200408 5761 J 6050611.37200408 5762 G 6854584.22200408 5762 J 6521884.50200408 5763 G 9468707.65200408 5763 J 8460049.43200408 5764 G 6587559.23BILL_MONTH AREA_CODE NET_TYPE LOCAL_FARE --------------- ---------- ---------- --------------200408 5764 J 7342135.86200408 5765 G 14450586.63200408 5765 J 12680052.3840 rows selected.Elapsed: 00:00:00.001. 使用rollup函数的介绍Quote:下面是直接使用普通sql语句求出各地区的汇总数据的例子06:41:36 SQL> set autot on06:43:36 SQL> select area_code,sum(local_fare) local_fare06:43:50 2 from t06:43:51 3 group by area_code06:43:57 4 union all06:44:00 5 select '合计' area_code,sum(local_fare) local_fare06:44:06 6 from t06:44:08 7 /AREA_CODE LOCAL_FARE---------- --------------5761 54225413.045762 52039619.605763 69186545.025764 53156768.465765 104548719.19合计 333157065.316 rows selected.Elapsed: 00:00:00.03Execution Plan----------------------------------------------------------0 SELECT STATEMENT Optimizer=ALL_ROWS (Cost=7 Card=1310 Bytes=24884)1 0 UNION-ALL2 1 SORT (GROUP BY) (Cost=5 Card=1309 Bytes=24871)3 2 TABLE ACCESS (FULL) OF 'T' (Cost=2 Card=1309Bytes=24871)4 1 SORT (AGGREGATE)5 4 TABLE ACCESS (FULL) OF 'T' (Cost=2 Card=1309Bytes=17017)Statistics----------------------------------------------------------0 recursive calls0 db block gets6 consistent gets0 physical reads0 redo size561 bytes sent via SQL*Net to client503 bytes received via SQL*Net from client2 SQL*Net roundtrips to/from client1 sorts (memory)0 sorts (disk)6 rows processed下面是使用分析函数rollup得出的汇总数据的例子06:44:09 SQL> select nvl(area_code,'合计') area_code,sum(local_fare) local_fare06:45:26 2 from t06:45:30 3 group by rollup(nvl(area_code,'合计'))06:45:50 4 /AREA_CODE LOCAL_FARE---------- --------------5761 54225413.045762 52039619.605763 69186545.025764 53156768.465765 104548719.19333157065.316 rows selected.Elapsed: 00:00:00.00Execution Plan----------------------------------------------------------0 SELECT STATEMENT Optimizer=ALL_ROWS (Cost=5 Card=1309Bytes=24871)1 0 SORT (GROUP BY ROLLUP) (Cost=5 Card=1309 Bytes=24871)2 1 TABLE ACCESS (FULL) OF 'T' (Cost=2 Card=1309Bytes=24871)Statistics----------------------------------------------------------0 recursive calls0 db block gets4 consistent gets0 physical reads0 redo size557 bytes sent via SQL*Net to client503 bytes received via SQL*Net from client2 SQL*Net roundtrips to/from client1 sorts (memory)0 sorts (disk)6 rows processed从上面的例子我们不难看出使用rollup函数,系统的sql语句更加简单,耗用的资源更少,从6个consistent gets降到4个consistent gets,如果基表很大的话,结果就可想而知了.1. 使用cube函数的介绍Quote:为了介绍cube函数我们再来看看另外一个使用rollup的例子06:53:00 SQL> select area_code,bill_month,sum(local_fare) local_fare06:53:37 2 from t06:53:38 3 group by rollup(area_code,bill_month)06:53:49 4 /---------- --------------- --------------5761 200405 13060433.895761 200406 13318931.015761 200407 13710265.935761 200408 14135782.215761 54225413.045762 200405 12643792.115762 200406 12795060.655762 200407 13224298.125762 200408 13376468.725762 52039619.605763 200405 16649778.915763 200406 17120515.715763 200407 17487493.325763 200408 17928757.085763 69186545.025764 200405 12487791.945764 200406 13295187.675764 200407 13444093.765764 200408 13929695.095764 53156768.465765 200405 25057737.475765 200406 26058461.315765 200407 26301881.405765 200408 27130639.015765 104548719.19333157065.3126 rows selected.Elapsed: 00:00:00.00系统只是根据rollup的第一个参数area_code对结果集的数据做了汇总处理,而没有对bill_month做汇总分析处理,cube函数就是为了这个而设计的.下面,让我们看看使用cube函数的结果06:58:02 SQL> select area_code,bill_month,sum(local_fare) local_fare 06:58:30 2 from t06:58:32 3 group by cube(area_code,bill_month)06:58:42 4 order by area_code,bill_month nulls last06:58:57 5 /---------- --------------- --------------5761 200405 13060.435761 200406 13318.935761 200407 13710.275761 200408 14135.785761 54225.415762 200405 12643.795762 200406 12795.065762 200407 13224.305762 200408 13376.475762 52039.625763 200405 16649.785763 200406 17120.525763 200407 17487.495763 200408 17928.765763 69186.545764 200405 12487.795764 200406 13295.195764 200407 13444.095764 200408 13929.695764 53156.775765 200405 25057.745765 200406 26058.465765 200407 26301.885765 200408 27130.645765 104548.72200405 79899.53200406 82588.15200407 84168.03200408 86501.34333157.0530 rows selected.Elapsed: 00:00:00.01可以看到,在cube函数的输出结果比使用rollup多出了几行统计数据.这就是cube函数根据bill_month做的汇总统计结果1 rollup 和cube函数的再深入Quote:从上面的结果中我们很容易发现,每个统计数据所对应的行都会出现null,我们如何来区分到底是根据那个字段做的汇总呢,这时候,oracle的grouping函数就粉墨登场了.如果当前的汇总记录是利用该字段得出的,grouping函数就会返回1,否则返回01 select decode(grouping(area_code),1,'all area',to_char(area_code)) area_code,2 decode(grouping(bill_month),1,'all month',bill_month) bill_month,3 sum(local_fare) local_fare4 from t5 group by cube(area_code,bill_month)6* order by area_code,bill_month nulls last07:07:29 SQL> /AREA_CODE BILL_MONTH LOCAL_FARE---------- --------------- --------------5761 200405 13060.435761 200406 13318.935761 200407 13710.275761 200408 14135.785761 all month 54225.415762 200405 12643.795762 200406 12795.065762 200407 13224.305762 200408 13376.475762 all month 52039.625763 200405 16649.785763 200406 17120.525763 200407 17487.495763 200408 17928.765763 all month 69186.545764 200405 12487.795764 200406 13295.195764 200407 13444.095764 200408 13929.695764 all month 53156.775765 200405 25057.745765 200406 26058.465765 200407 26301.885765 200408 27130.645765 all month 104548.72all area 200405 79899.53all area 200406 82588.15all area 200407 84168.03all area 200408 86501.34all area all month 333157.0530 rows selected.Elapsed: 00:00:00.0107:07:31 SQL>可以看到,所有的空值现在都根据grouping函数做出了很好的区分,这样利用rollup,cube和grouping函数,我们做数据统计的时候就可以轻松很多了.2. rank函数的介绍介绍完rollup和cube函数的使用,下面我们来看看rank系列函数的使用方法.问题2.我想查出这几个月份中各个地区的总话费的排名.Quote:为了将rank,dense_rank,row_number函数的差别显示出来,我们对已有的基础数据做一些修改,将5763的数据改成与5761的数据相同.1 update t t1 set local_fare = (2 select local_fare from t t23 where t1.bill_month = t2.bill_month4 and _type = _type5 and t2.area_code = '5761'6* ) where area_code = '5763'07:19:18 SQL> /8 rows updated.Elapsed: 00:00:00.01我们先使用rank函数来计算各个地区的话费排名.07:34:19 SQL> select area_code,sum(local_fare) local_fare,07:35:25 2 rank() over (order by sum(local_fare) desc) fare_rank07:35:44 3 from t07:35:45 4 group by area_codee07:35:50 507:35:52 SQL> select area_code,sum(local_fare) local_fare,07:36:02 2 rank() over (order by sum(local_fare) desc) fare_rank07:36:20 3 from t07:36:21 4 group by area_code07:36:25 5 /AREA_CODE LOCAL_FARE FARE_RANK---------- -------------- ----------5765 104548.72 15761 54225.41 25763 54225.41 25764 53156.77 45762 52039.62 5Elapsed: 00:00:00.01我们可以看到红色标注的地方出现了,跳位,排名3没有出现下面我们再看看dense_rank查询的结果.07:36:26 SQL> select area_code,sum(local_fare) local_fare,07:39:16 2 dense_rank() over (order by sum(local_fare) desc ) fare_rank 07:39:39 3 from t07:39:42 4 group by area_code07:39:46 5 /AREA_CODE LOCAL_FARE FARE_RANK---------- -------------- ----------5765 104548.72 15761 54225.41 25763 54225.41 25764 53156.77 3 这是这里出现了第三名5762 52039.62 4Elapsed: 00:00:00.00在这个例子中,出现了一个第三名,这就是rank和dense_rank的差别,rank如果出现两个相同的数据,那么后面的数据就会直接跳过这个排名,而dense_rank则不会,差别更大的是,row_number哪怕是两个数据完全相同,排名也会不一样,这个特性在我们想找出对应没个条件的唯一记录的时候又很大用处1 select area_code,sum(local_fare) local_fare,2 row_number() over (order by sum(local_fare) desc ) fare_rank3 from t4* group by area_code07:44:50 SQL> /AREA_CODE LOCAL_FARE FARE_RANK---------- -------------- ----------5765 104548.72 15761 54225.41 25763 54225.41 35764 53156.77 45762 52039.62 5在row_nubmer函数中,我们发现,哪怕sum(local_fare)完全相同,我们还是得到了不一样排名,我们可以利用这个特性剔除数据库中的重复记录.这个帖子中的几个例子是为了说明这三个函数的基本用法的. 下个帖子我们将详细介绍他们的一些用法.2. rank函数的介绍a. 取出数据库中最后入网的n个用户select user_id,tele_num,user_name,user_status,create_datefrom (select user_id,tele_num,user_name,user_status,create_date,rank() over (order by create_date desc) add_rankfrom user_info)where add_rank <= :n;b.根据object_name删除数据库中的重复记录create table t as select obj#,name from sys.obj$;再insert into t1 select * from t1 数次.delete from t1 where rowid in (select row_id from (select rowid row_id,row_number() over (partition by obj# order by rowid ) rn ) where rn <> 1);c. 取出各地区的话费收入在各个月份排名.SQL> select bill_month,area_code,sum(local_fare) local_fare,2 rank() over (partition by bill_month order by sum(local_fare) desc) area_rank3 from t4 group by bill_month,area_code5 /BILL_MONTH AREA_CODE LOCAL_FARE AREA_RANK--------------- --------------- -------------- ----------200405 5765 25057.74 1200405 5761 13060.43 2200405 5763 13060.43 2200405 5762 12643.79 4200405 5764 12487.79 5200406 5765 26058.46 1200406 5761 13318.93 2200406 5763 13318.93 2200406 5764 13295.19 4200406 5762 12795.06 5200407 5765 26301.88 1200407 5761 13710.27 2200407 5763 13710.27 2200407 5764 13444.09 4200407 5762 13224.30 5200408 5765 27130.64 1200408 5761 14135.78 2200408 5763 14135.78 2200408 5764 13929.69 4200408 5762 13376.47 520 rows selected.SQL>3. lag和lead函数介绍取出每个月的上个月和下个月的话费总额1 select area_code,bill_month, local_fare cur_local_fare,2 lag(local_fare,2,0) over (partition by area_code order by bill_month ) pre_local_fare,3 lag(local_fare,1,0) over (partition by area_code order by bill_month ) last_local_fare,4 lead(local_fare,1,0) over (partition by area_code order by bill_month )next_local_fare,5 lead(local_fare,2,0) over (partition by area_code order by bill_month )post_local_fare6 from (7 select area_code,bill_month,sum(local_fare) local_fare8 from t9 group by area_code,bill_month10* )SQL> /AREA_CODE BILL_MONTH CUR_LOCAL_FARE PRE_LOCAL_FARELAST_LOCAL_FARE NEXT_LOCAL_FARE POST_LOCAL_FARE--------- ---------- -------------- -------------- --------------- --------------- ---------------5761 200405 13060.433 0 0 13318.93 13710 .2655761 200406 13318.93 0 13060.433 13710.265 14 135.7815761 200407 13710.265 13060.433 13318.93 14135.7815761 200408 14135.781 13318.93 13710.265 05762 200405 12643.791 0 0 12795.06 13224 .2975762 200406 12795.06 0 12643.791 13224.297 13 376.4685762 200407 13224.297 12643.791 12795.06 13376.4685762 200408 13376.468 12795.06 13224.297 05763 200405 13060.433 0 0 13318.93 13710 .2655763 200406 13318.93 0 13060.433 13710.265 14 135.7815763 200407 13710.265 13060.433 13318.93 14135.7815763 200408 14135.781 13318.93 13710.265 05764 200405 12487.791 0 0 13295.187 1344 4.0935764 200406 13295.187 0 12487.791 13444.093 1 3929.6945764 200407 13444.093 12487.791 13295.187 13929.6945764 200408 13929.694 13295.187 13444.093 05765 200405 25057.736 0 0 26058.46 26301 .8815765 200406 26058.46 0 25057.736 26301.881 27 130.6385765 200407 26301.881 25057.736 26058.46 27130.6385765 200408 27130.638 26058.46 26301.881 020 rows selected.利用lag和lead函数,我们可以在同一行中显示前n行的数据,也可以显示后n行的数据.4. sum,avg,max,min移动计算数据介绍计算出各个连续3个月的通话费用的平均数1 select area_code,bill_month, local_fare,2 sum(local_fare)3 over ( partition by area_code4 order by to_number(bill_month)5 range between 1 preceding and 1 following ) "3month_sum",6 avg(local_fare)7 over ( partition by area_code8 order by to_number(bill_month)9 range between 1 preceding and 1 following ) "3month_avg",10 max(local_fare)11 over ( partition by area_code12 order by to_number(bill_month)13 range between 1 preceding and 1 following ) "3month_max",14 min(local_fare)15 over ( partition by area_code16 order by to_number(bill_month)17 range between 1 preceding and 1 following ) "3month_min"18 from (19 select area_code,bill_month,sum(local_fare) local_fare20 from t21 group by area_code,bill_month22* )SQL> /AREA_CODE BILL_MONTH LOCAL_FARE 3month_sum 3month_avg3month_max 3month_min--------- ---------- ---------------- ---------- ---------- ---------- ----------5761 200405 13060.433 26379.363 13189.6815 13318.93 13060.433 5761 200406 13318.930 40089.628 13363.2093 13710.265 13060.433 5761 200407 13710.265 41164.976 13721.6587 14135.781 13318.93 40089.628 = 13060.433 + 13318.930 + 13710.26513363.2093 = (13060.433 + 13318.930 + 13710.265) / 313710.265 = max(13060.433 + 13318.930 + 13710.265)13060.433 = min(13060.433 + 13318.930 + 13710.265)5761 200408 14135.781 27846.046 13923.023 14135.781 13710.265 5762 200405 12643.791 25438.851 12719.4255 12795.06 12643.791 5762 200406 12795.060 38663.148 12887.716 13224.297 12643.791 5762 200407 13224.297 39395.825 13131.9417 13376.468 12795.06 5762 200408 13376.468 26600.765 13300.3825 13376.468 13224.297 5763 200405 13060.433 26379.363 13189.6815 13318.93 13060.433 5763 200406 13318.930 40089.628 13363.2093 13710.265 13060.433 5763 200407 13710.265 41164.976 13721.6587 14135.781 13318.935763 200408 14135.781 27846.046 13923.023 14135.781 13710.265 5764 200405 12487.791 25782.978 12891.489 13295.187 12487.791 5764 200406 13295.187 39227.071 13075.6903 13444.093 12487.791 5764 200407 13444.093 40668.974 13556.3247 13929.694 13295.187 5764 200408 13929.694 27373.787 13686.8935 13929.694 13444.093 5765 200405 25057.736 51116.196 25558.098 26058.46 25057.736 5765 200406 26058.460 77418.077 25806.0257 26301.881 25057.736 5765 200407 26301.881 79490.979 26496.993 27130.638 26058.46 5765 200408 27130.638 53432.519 26716.2595 27130.638 26301.88120 rows selected.5. ratio_to_report函数的介绍Quote:1 select bill_month,area_code,sum(local_fare) local_fare,2 ratio_to_report(sum(local_fare)) over3 ( partition by bill_month ) area_pct4 from t5* group by bill_month,area_codeSQL> break on bill_month skip 1SQL> compute sum of local_fare on bill_monthSQL> compute sum of area_pct on bill_monthSQL> /BILL_MONTH AREA_CODE LOCAL_FARE AREA_PCT---------- --------- ---------------- ----------200405 5761 13060.433 .1711492795762 12643.791 .1656894315763 13060.433 .1711492795764 12487.791 .1636451435765 25057.736 .328366866********** ---------------- ----------sum 76310.184 1200406 5761 13318.930 .1690507725762 12795.060 .1624015425763 13318.930 .1690507725764 13295.187 .1687494145765 26058.460 .330747499********** ---------------- ----------sum 78786.567 1200407 5761 13710.265 .1705451975762 13224.297 .1645001275763 13710.265 .1705451975764 13444.093 .1672342215765 26301.881 .327175257********** ---------------- ----------sum 80390.801 1200408 5761 14135.781 .1709111475762 13376.468 .1617305395763 14135.781 .1709111475764 13929.694 .1684194165765 27130.638 .328027751********** ---------------- ----------sum 82708.362 120 rows selected.6 first,last函数使用介绍Quote:取出每月通话费最高和最低的两个用户.1 select bill_month,area_code,sum(local_fare) local_fare,2 first_value(area_code)3 over (order by sum(local_fare) desc4 rows unbounded preceding) firstval,5 first_value(area_code)6 over (order by sum(local_fare) asc7 rows unbounded preceding) lastval8 from t9 group by bill_month,area_code10* order by bill_monthSQL> /BILL_MONTH AREA_CODE LOCAL_FARE FIRSTVAL LASTVAL ---------- --------- ---------------- --------------- ---------------200405 5764 12487.791 5765 5764200405 5762 12643.791 5765 5764200405 5761 13060.433 5765 5764200405 5765 25057.736 5765 5764200405 5763 13060.433 5765 5764200406 5762 12795.060 5765 5764200406 5763 13318.930 5765 5764200406 5764 13295.187 **** ****200406 5765 26058.460 5765 5764200406 5761 13318.930 5765 5764200407 5762 13224.297 5765 5764200407 5765 26301.881 5765 5764200407 5761 13710.265 5765 5764200407 5763 13710.265 5765 5764200407 5764 13444.093 5765 5764200408 5762 13376.468 5765 5764200408 5764 13929.694 5765 5764200408 5761 14135.781 5765 5764200408 5765 27130.638 5765 5764200408 5763 14135.781 5765 576420 rows selected.。
oracle进一法的函数
oracle进一法的函数摘要：一、Oracle 进一法函数简介1.函数定义2.常见用途二、Oracle 进一法函数的使用1.语法与参数2.示例与应用三、总结正文：一、Oracle 进一法函数简介在Oracle 数据库中，进一法函数（ROUNDUP）是一种四舍五入函数，用于将数字向上舍入到最接近的整数。
它可以将数值表达式的值四舍五入到最接近的整数，从而满足财务、统计等领域的需求。
1.函数定义Oracle 进一法函数的定义如下：```ROUNDUP(number, significance)```其中，`number` 是需要进行四舍五入的数值表达式，`significance` 是指定的舍入精度。
2.常见用途进一法函数在实际应用中主要用于以下场景：- 向上取整：对于任意实数`x`，`ROUNDUP(x)` 等于大于等于`x` 的最小整数。
- 设置精度：通过指定`significance` 参数，可以控制四舍五入的精度，例如保留两位小数、四舍五入到整百等。
二、Oracle 进一法函数的使用1.语法与参数进一法函数的语法如下：```ROUNDUP(number, significance)```参数说明：- `number`：需要进行四舍五入的数值表达式。
- `significance`：指定舍入精度，可以是一个整数或小数。
如果未指定或为NULL，则默认为0，表示四舍五入到整数。
2.示例与应用以下是一些Oracle 进一法函数的示例：- 向上取整：```SELECT ROUNDUP(5.5) FROM DUAL; -- 结果为6```- 保留两位小数：```SELECT ROUNDUP(5.555, 2) FROM DUAL; -- 结果为5.56```- 四舍五入到整百：```SELECT ROUNDUP(123.456, 100) FROM DUAL; -- 结果为1200```三、总结Oracle 进一法函数（ROUNDUP）是一种实用的四舍五入函数，可以满足不同场景下的需求。
oracle date函数用法
oracle date函数用法Oracle DATE函数是用于将一个字符串转换为日期格式的函数。
它有多种用法，以下是其中的几种：1. TO_DATE函数：将字符串转换为日期格式TO_DATE函数的语法如下：TO_DATE(string, format)其中，string是要转换的字符串，format是转换格式，它告诉Oracle如何将字符串转换为日期格式。
例如，如果字符串是'2022-01-01'，format应该是'YYYY-MM-DD'。
示例：SELECT TO_DATE('2022-01-01', 'YYYY-MM-DD') FROM DUAL;结果为：01-JAN-222. SYSDATE函数：返回当前日期和时间SYSDATE函数用于获取当前日期和时间。
它没有任何参数。
示例：SELECT SYSDATE FROM DUAL;结果为：21-JUL-21 02.14.45.000000 PM3. ADD_MONTHS函数：将月份加上指定的数量ADD_MONTHS函数用于将一个日期的月份加上指定的数量。
它的语法如下：ADD_MONTHS(date, n)其中，date是要处理的日期，n是要加上的月份数量。
示例：SELECT ADD_MONTHS(TO_DATE('2022-01-01', 'YYYY-MM-DD'), 6) FROM DUAL;结果为：01-JUL-22以上是Oracle DATE函数的常用用法，它们可以帮助您处理日期和时间相关的计算和操作。
oracle数据库nvl用法
oracle数据库nvl用法Oracle数据库中的NVL函数用于处理空值（NULL）。
它接受两个参数，如果第一个参数为NULL，则返回第二个参数；如果第一个参数不为NULL，则返回第一个参数。
NVL函数的语法如下：NVL(expr1, expr2)。
其中，expr1是要检查是否为NULL的表达式，expr2是在expr1为NULL时要返回的值。
NVL函数的使用可以从多个角度考虑：1. 处理空值：NVL函数最常见的用途是处理空值。
当我们需要在查询结果中将空值替换为特定的值时，可以使用NVL函数。
例如，假设有一个名为employees的表，其中的salary列可能包含空值。
我们可以使用NVL函数将空值替换为0，以便在计算总工资时不影响结果：SELECT SUM(NVL(salary, 0)) FROM employees;2. 字符串处理：NVL函数也可以用于字符串处理。
当我们需要在查询结果中将空字符串替换为特定的值时，可以使用NVL函数。
例如，假设有一个名为customers的表，其中的address列可能包含空字符串。
我们可以使用NVL函数将空字符串替换为"Unknown"，以便更好地显示地址信息：SELECT NVL(address, 'Unknown') FROM customers;3. 表达式计算：NVL函数还可以用于表达式计算。
当我们需要在计算中处理空值时，可以使用NVL函数。
例如，假设有一个名为products的表，其中的quantity列可能包含空值。
我们可以使用NVL函数将空值替换为1，以便在计算库存总量时不影响结果：SELECT SUM(NVL(quantity, 1)) FROM products;4. 嵌套使用：NVL函数还可以嵌套使用，以处理更复杂的情况。
例如，假设有一个名为orders的表，其中的ship_date列可能包含空值，而ship_date为空时，我们想要使用order_date作为替代。
oracle中to_date用法
oracle中to_date用法TO_DATE函数作为Oracle中的日期函数，用于将字符串转换为日期格式。
它的输入可以是一个字符串或者一个数字，可以指定一个格式字符串来格式化日期，也可以不指定，这取决于你需要的日期格式。
本文将详细介绍TO_DATE函数的使用，以帮助读者更好地理解并使用它。
一、 TO_DATE函数的基本格式TO_DATE函数的基本格式如下：TO_DATE(input_string, fmt, [nls_date_language])input_string：字符或数字，表示要转换为日期型的输入值。
fmt：格式字符串，用以定义input_string如何被格式化。
nls_date_language：可选参数，表示input_string的语言。
二、 TO_DATE的使用1.数字转换日期Oracle中的TO_DATE函数可以接受一个数字参数来转换成日期，数字参数的格式为YYYYMMDD，如果你要转换的日期是格式化过的，可以使用TO_CHAR函数将它转换为不带格式的字符串，然后再用TO_DATE函数转换为日期。
例如：把2021年4月7日转换为日期格式。
SELECT TO_DATE(20210407 YYYYMMDD FROM DUAL;运行结果：2021-04-072.字符串转换日期当input_string是字符串时，则需要指定一个fmt格式字符串，用以将input_string转换为日期格式。
例如：将字符串2021-04-07转换为日期格式：SELECT TO_DATE(2021-04-07 YYYY-MM-DD FROM DUAL;运行结果：2021-04-073. 使用日期格式掩码有时候，当input_string是一个比较复杂的日期字符串时，可以使用日期格式掩码来简化fmt参数，可以减少你使用的括号对数量，从而提高可读性。
例如：将字符串07-April-2021转换为日期格式：SELECT TO_DATE(07-April-2021 DD-Mon-YYYY FROM DUAL;运行结果：2021-04-074. 使用时间格式字符串除了将input_string转换为日期格式外，TO_DATE函数还可以将其转换为时间格式，只需要将fmt参数中的日期组件替换为时间组件即可，比如将DD替换为HH，MM替换为MI，YYYY替换为SS等。
oracle的chr函数
oracle的chr函数Oracle的CHR函数是一个非常有用的函数，它可以将数字转换为对应的字符，包括ASCII码和UNICODE码。
在处理字符类型数据时，CHR函数经常被使用，它通过数字转换为对应的字符，使字符数据的处理更加便捷、灵活。
本文将主要介绍Oracle的CHR函数，包括函数的语法、使用方法以及注意事项等内容，希望能够对读者带来帮助。
1. 函数语法Oracle的CHR函数的语法非常简单，其基本语法如下：CHR(n)其中n为数字，它代表了将要被转换为字符的数字。
CHR函数的返回值为字符，表示与这个数字对应的字符。
在使用CHR函数时，需要保证输入的参数为数字。
如果输入的参数不是数字，Oracle将会返回一个错误提示。
在Oracle中，CHR函数非常适合在SQL语句中使用，特别是在字符拼接、字符串替换等操作中。
下面我们通过一个示例来介绍CHR函数的使用方法。
示例1：将数字转换为字符，生成新的列假设我们有一个员工表，其中包含员工编号和对应的部门编号两列，现在需要将这些数字转换为对应的字符，生成一个新的列。
具体操作如下：SELECT EMPNO, DEPTNO, CHR(EMPNO), CHR(DEPTNO) FROM EMP;在以上的SQL语句中，我们使用了CHR函数将EMPNO列和DEPTNO列中的数字转换为了对应的字符，并通过生成的新列进行了展示。
示例2：在字符串拼接中使用CHR函数假设我们需要将员工的姓名、出生日期和部门名按照一定的格式进行拼接，形成一条新的字符串。
具体操作如下：SELECT ENAME || ',' || CHR(13) || CHR(10) || TO_CHAR(BIRTHDATE, 'YYYY-MM-DD') || ',' || DNAMEFROM EMP e, DEPT dWHERE e.DEPTNO = d.DEPTNO;在以上的SQL语句中，我们通过使用CHR函数将CHR(13)和CHR(10)字符进行了拼接，生成一个回车和换行的字符，用于调整输出格式。
Oracleinstrsubstrtranslate函数使用介绍
Oracleinstrsubstrtranslate函数使用介绍1. instr函数instr函数用于返回一个字符串中一些子字符串第一次出现的位置。
它的语法如下：instr(string, substring, start_position, occurrence)参数说明：- string：需要的字符串- substring：要查找的子字符串- start_position：开始的位置，默认为1- occurrence：要查找的子字符串在整个字符串中的第几次出现，默认为1示例：SELECT INSTR('Hello World', 'o') FROM DUAL;--返回结果为5，表示'o'第一次出现在字符串中的位置SELECT INSTR('Hello World', 'o', 6);--返回结果为7，表示'o'第二次出现在字符串中的位置2. substr函数substr函数用于从一个字符串中返回指定位置开始的若干个字符。
它的语法如下：substr(string, start_position, length)参数说明：- string：需要截取的字符串- start_position：开始截取的位置，默认为1- length：要截取的字符数，默认为整个字符串的长度示例：SELECT SUBSTR('Hello World', 7) FROM DUAL;-- 返回结果为'World'，表示从第7个位置开始截取的字符SELECT SUBSTR('Hello World', 7, 2) FROM DUAL;--返回结果为'Wo'，表示从第7个位置开始截取的2个字符3. translate函数translate函数用于将一个字符串中的一些字符替换为其他字符。
oracle sum函数的使用方法
oracle sum函数的使用方法Oracle是一种常用的关系型数据库管理系统，它提供了丰富的SQL 函数来处理和计算数据。
其中，sum函数是一种非常常用的聚合函数，用于计算某一列数据的总和。
本文将介绍Oracle sum函数的使用方法。
在Oracle中，sum函数的语法如下：```SELECT SUM(column_name) FROM table_name;```其中，column_name是要计算总和的列名，table_name是要查询的表名。
我们需要创建一个示例表来演示sum函数的使用。
假设我们有一个名为"sales"的表，其中包含了销售订单的信息，包括订单号（order_id）、产品名称（product_name）和销售金额（amount）。
我们可以使用以下SQL语句创建这个表：```CREATE TABLE sales (order_id INT,product_name VARCHAR(100),amount NUMBER(10, 2));```假设我们已经插入了一些销售订单的数据，现在我们想计算销售金额的总和。
我们可以使用以下SQL语句来实现：```SELECT SUM(amount) FROM sales;```这会返回一个包含总和的结果集。
如果我们只想获取总和的数值，可以使用以下SQL语句：```SELECT SUM(amount) AS total_amount FROM sales;```这会返回一个名为"total_amount"的列，其中包含总和的数值。
除了计算整个表的总和，我们还可以根据条件来计算部分数据的总和。
例如，我们可以使用以下SQL语句计算产品名称为"iPhone"的订单销售金额的总和：```SELECT SUM(amount) FROM sales WHERE product_name = 'iPhone'; ```这会返回一个包含满足条件的销售金额总和的结果集。
oracle常用函数trunc用法
oracle常用函数trunc用法Oracle中的TRUNC函数是用来对数字进行截取的常用函数。
它将一个数字截取到指定的精度上，并返回截取后的数字。
TRUNC函数的语法如下：TRUNC(number, [precision])其中，number表示要截取的数字，precision表示要截取的精度。
如果不指定precision，则默认为0，即截取到整数部分。
以下是TRUNC函数常用的几种用法：1.截取到整数部分可以直接使用TRUNC函数来截取一个数字的整数部分。
例如：2.截取到指定小数位数可以将precision参数设置为一个负数，表示截取小数点左边的位数。
例如：3.截取到指定日期精度TRUNC函数同样可以用于日期类型的截取。
可以将precision参数设置为'day'、'month'或'year'，表示截取到指定的日期精度。
例如：SELECT TRUNC(SYSDATE, 'month') FROM dual; -- 返回当月的第一天4.截取到指定时间精度TRUNC函数也可以用于时间类型的截取。
可以将precision参数设置为'hour'、'minute'或'second'，表示截取到指定的时间精度。
例如：SELECT TRUNC(SYSDATE, 'hour') FROM dual; -- 返回当前小时的整点时间5.用于表达式中的截取TRUNC函数不仅可以直接用于数字或日期，还可以用于表达式中。
例如：SELECT TRUNC(10.5 + 2.6) FROM dual; -- 返回12总结：TRUNC函数是Oracle中常用的截取函数，可以用于数字、日期和时间类型。
它能够截取到指定的精度上，并返回截取后的结果。
可以通过设置precision参数来实现不同的截取需求。
oracle常用函数trunc用法
oracle常用函数trunc用法Oracle中的TRUNC函数有多种用法，它主要用于截取日期、数值和字符串类型的数据。
下面将介绍常用的几种TRUNC函数的用法。
1.日期截取：TRUNC函数可以从一个日期表达式中截取出指定的部分，如年、月、日、小时、分钟等。
- TRUNC(date, 'YEAR')：截取日期表达式的年份部分，将月、日、时、分和秒均截取掉，返回结果为该年的1月1日的日期。
例如：SELECT TRUNC(SYSDATE, 'YEAR') FROM DUAL;- TRUNC(date, 'MONTH')：截取日期表达式的月份部分，将日、时、分和秒截取掉，返回结果为该月的1日的日期。
例如：SELECTTRUNC(SYSDATE, 'MONTH') FROM DUAL;- TRUNC(date, 'DD')：截取日期表达式的日部分，将时、分和秒截取掉，返回结果为当天的日期。
例如：SELECT TRUNC(SYSDATE, 'DD') FROM DUAL;2.数值截取：TRUNC函数可以截取出数值的整数部分或指定的小数位数，并将小数部分舍去。
- TRUNC(number)：截取出数值的整数部分，例如：SELECTTRUNC(3.14) FROM DUAL; -- 结果为3；3.字符串截取：TRUNC函数还可以用于截取字符串的指定长度。
- TRUNC(string, n)：截取字符串的前n个字符。
例如：SELECT TRUNC('Hello world', 5) FROM DUAL; -- 结果为'Hello'；以上是TRUNC函数的一些常用用法，下面再介绍一些更高级的用法：4.日期计算：TRUNC函数可以与其他日期函数结合使用，实现日期的计算。
- TRUNC(date, 'YYYY') + 1：计算出下一年的1月1日的日期。