当前位置：文档之家 › oracle Rollup 和 Cube用法

oracle Rollup 和 Cube用法

  • 格式：doc
  • 大小：49.50 KB
  • 文档页数：9

oracle Rollup 和Cube用法Oracle的GROUP BY语句除了最基本的语法外，还支持ROLLUP和CUBE语句。

如果是ROLLUP(A, B, C)的话，首先会对(A、B、C)进行GROUP BY，然后对(A、B)进行GROUP BY，然后是(A)进行GROUP BY，最后对全表进行GROUP BY操作。

如果是GROUP BY CUBE(A, B, C)，则首先会对(A、B、C)进行GROUP BY，然后依次是(A、B)，(A、C)，(A)，(B、C)，(B)，(C)，最后对全表进行GROUP BY操作。

grouping_id()可以美化效果：Oracle的GROUP BY语句除了最基本的语法外，还支持ROLLUP和CUBE语句。

除本文内容外，你还可参考：分析函数参考手册：/post/419/33028分析函数使用例子介绍：/post/419/44634SQL> create table t as select * from dba_indexes;表已创建。

SQL> select index_type, status, count(*) from t group by index_type, status;INDEX_TYPE STATUS COUNT(*)--------------------------- -------- ----------LOB V ALID 51NORMAL N/A 25NORMAL V ALID 479CLUSTER V ALID 11下面来看看ROLLUP和CUBE语句的执行结果。

SQL> select index_type, status, count(*) from t group by rollup(index_type, status);INDEX_TYPE STATUS COUNT(*)--------------------------- -------- ----------LOB V ALID 51LOB 51NORMAL N/A 25NORMAL V ALID 479NORMAL 504CLUSTER V ALID 11CLUSTER 11566已选择8行。

SQL> select index_type, status, count(*) from t group by cube(index_type, status);INDEX_TYPE STA TUS COUNT(*)--------------------------- -------- ----------566N/A 25V ALID 541LOB 51LOB V ALID 51NORMAL 504NORMAL N/A 25NORMAL V ALID 479CLUSTER 11CLUSTER V ALID 11已选择10行。

查询结果不是很一目了然，下面通过Oracle提供的函数GROUPING来整理一下查询结果。

SQL> select grouping(index_type) g_ind, grouping(status) g_st, index_type, status, count(*)2 from t group by rollup(index_type, status) order by 1, 2;G_IND G_ST INDEX_TYPE STA TUS COUNT(*)---------- ---------- --------------------------- -------- ----------0 0 LOB VALID 510 0 NORMAL N/A 250 0 NORMAL VALID 4790 0 CLUSTER VALID 110 1 LOB 510 1 NORMAL 5040 1 CLUSTER 111 1 566已选择8行。

这个查询结果就直观多了，和不带ROLLUP语句的GROUP BY相比，ROLLUP增加了对INDEX_TYPE的GROUP BY统计和对所有记录的GROUP BY统计。

也就是说，如果是ROLLUP(A, B, C)的话，首先会对(A、B、C)进行GROUP BY，然后对(A、B)进行GROUP BY，然后是(A)进行GROUP BY，最后对全表进行GROUP BY操作。

下面看看CUBE语句。

SQL> select grouping(index_type) g_ind, grouping(status) g_st, index_type, status, count(*)2 from t group by cube(index_type, status) order by 1, 2;G_IND G_ST INDEX_TYPE STA TUS COUNT(*)---------- ---------- --------------------------- -------- ----------0 0 LOB V ALID 510 0 NORMAL N/A 250 0 NORMAL V ALID 4790 0 CLUSTER V ALID 110 1 LOB 510 1 NORMAL 5040 1 CLUSTER 111 0 N/A 251 0 V ALID 5411 1 566已选择10行。

和ROLLUP相比，CUBE又增加了对STA TUS列的GROUP BY统计。

如果是GROUP BY CUBE(A, B, C)，则首先会对(A、B、C)进行GROUP BY，然后依次是(A、B)，(A、C)，(A)，(B、C)，(B)，(C)，最后对全表进行GROUP BY操作。

除了使用GROUPING函数，还可以使用GROUPING_ID来标识GROUP BY结果。

SQL> select grouping_id(index_type, status) g_ind, index_type, status, count(*)2 from t group by rollup(index_type, status) order by 1;G_IND INDEX_TYPE STATUS COUNT(*)---------- --------------------------- -------- ----------0 LOB V ALID 510 NORMAL N/A 250 NORMAL V ALID 4790 CLUSTER V ALID 111 LOB 511 NORMAL 5041 CLUSTER 113 566已选择8行。

SQL> select grouping_id(index_type, status) g_ind, index_type, status, count(*) 2 from t group by cube(index_type, status) order by 1;G_IND INDEX_TYPE STA TUS COUNT(*)---------- --------------------------- -------- ----------0 LOB V ALID 510 NORMAL N/A 250 NORMAL V ALID 4790 CLUSTER V ALID 111 LOB 511 NORMAL 5041 CLUSTER 112 N/A 252 V ALID 5413 566已选择10行。

grouping_id()可以美化效果：select DECODE(GROUPING_ID(C1), 1, '合计', C1) D1,DECODE(GROUPING_ID(C1, C2), 1, '小计', C2) D2,DECODE(GROUPING_ID(C1, C2, C1 + C2), 1, '小计', C1 + C2) D3,count(*),GROUPING_ID(C1, C2, C1 + C2, C1 + 1, C2 + 1),GROUPING_ID(C1)from T2group by rollup(C1, C2, C1 + C2, C1 + 1, C2 + 1);===========================================================1.报表合计专用的Rollup函数销售报表广州1月2000元广州2月2500元广州4500元深圳1月1000元深圳2月2000元深圳3000元所有地区7500元以往的查询SQL:Select area,month,sum(money) from SaleOrder group by area,month然后广州，深圳的合计和所有地区合计都需要在程序里自行累计1.其实可以使用如下SQL:Select area,month,sum(total_sale) from SaleOrder group by rollup(area,month)就能产生和报表一模一样的纪录2.如果year不想累加，可以写成Select year,month,area,sum(total_sale) from SaleOrder group by year, rollup(month,area)另外Oracle 9i还支持如下语法:Select year,month,area,sum(total_sale) from SaleOrder group by rollup((year,month),area)3.如果使用Cube(area,month)而不是RollUp(area,month)，除了获得每个地区的合计之外，还将获得每个月份的合计，在报表最后显示。

4.Grouping让合计列更好读RollUp在显示广州合计时，月份列为NULL，但更好的做法应该是显示为"所有月份"Grouping就是用来判断当前Column是否是一个合计列，1为yes，然后用Decode把它转为"所有月份"Select Decode(Grouping(area),1,'所有地区',area) area, Decode(Grouping(month),1,'所有月份',month), sum(money) From SaleOrder Group by RollUp(area,month);2.对多级层次查询的start with.....connect by比如人员组织,产品类别,Oracle提供了很经典的方法SELECT LEVEL, name, emp_id,manager_emp_id FROM employee START WITH manager_emp_id is null CONNECT BY PRIOR emp_id = manager_emp_id;上面的语句demo了全部的应用,start with指明从哪里开始遍历树,如果从根开始,那么它的manager应该是Null,如果从某个职员开始,可以写成emp_id='11'CONNECT BY 就是指明父子关系,注意PRIOR位置。

合集下载

Oracle 分析函数的使用

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.。

update语句在oracle的用法

update语句在oracle的用法

update语句在oracle的用法下载温馨提示：该文档是我店铺精心编制而成，希望大家下载以后，能够帮助大家解决实际的问题。

文档下载后可定制随意修改，请根据实际需要进行相应的调整和使用，谢谢!并且，本店铺为大家提供各种各样类型的实用资料，如教育随笔、日记赏析、句子摘抄、古诗大全、经典美文、话题作文、工作总结、词语解析、文案摘录、其他资料等等，如想了解不同资料格式和写法，敬请关注!Download tips: This document is carefully compiled by the editor. I hope that after you download them, they can help you solve practical problems. The document can be customized and modified after downloading, please adjust and use it according to actual needs, thank you!In addition, our shop provides you with various types of practical materials, such as educational essays, diary appreciation, sentence excerpts, ancient poems, classic articles, topic composition, work summary, word parsing, copy excerpts, other materials and so on, want to know different data formats and writing methods, please pay attention!在数据库管理系统中，更新（update）操作是一个常见的功能，它允许用户修改数据库中已经存在的数据。

精通 oracle 10g plsql 编程-学习笔记

精通 oracle 10g plsql 编程-学习笔记

1.PL/SQL综述本章学习目标，了解如下内容：PL/SQL的功能和作用PL/SQL 的优点和特征；Oracle 10g、Oracle9i 的PL/SQL新特征1.1.SQL简介1.1.1.SQL语言特点SQL语言采用集合操作方式1.1.2.SQL语言分类●数据查询语言（SELECT语句）：检索数据库数据。

●数据操纵语言（DML）：用于改变数据库数据。

包括insert，update和delete三条语句。

●事务控制语言（TCL）：用于维护数据库的一致性，包括commit，rollback和savepoint 三条语句●数据定义语言（DDL）：用户建立、修改和删除数据库对象。

●数据控制语言（DDL）：用于执行权限授予和收回操作。

包括grant 和revoke两条命令。

1.1.3.SQL 语句编写规则●SQL关键字不区分大小写●对象名和列名不区分大小写●字符值和日期值区分大小写●书写格式随意1.2.PL/SQL简介1.3.Oracle 10G PL/SQL 新特征2.PL/SQL开发工具本章学习目标：学会使用SQL*PLUS学会使用PL/SQL developer；学会使用Procedure Builder。

2.1.SQL*PLUS在命令行运行SQL*PlusSqlplus [username]/[password] [@server]3.PL/SQL 基础学习目标：●了解PL/SQL块的基本结构以及PL/SQL块的分类；●学会在PL/SQL块中定义和使用变量●学会在PL/SQL块中编写可执行语句；●了解编写PL/SQL代码的指导方针；●了解Oracle 10g的新特征——新数据类型BINARY_FLOAT 和BINARY_DOUBLE，以及指定字符串文本的新方法。

3.1.PL/SQL 块简介3.1.1.PL/SQL块结构3.1.2.PL/SQL 块分类匿名块命名块子程序触发器3.2. 定义并使用变量3.2.1.标量变量3.2.2.复合变量3.2.3.参照变量3.2.4.LOB 变量3.2.5.非PL/SQL 变量3.3.编写 PL/SQL 代码3.3.1.PL/SQL 词汇单元分隔符标识符文本（数字文本，字符文本，字符串文本，布尔文本，日期时间文本）注释3.3.2.PL/SQL 代码编码规则标识符命名规则大小写规则代码缩进嵌套块和变量范围PL/SQL中可以使用的SQL函数4.使用SQL语句学习目标：学会使用SELECT语句去完成基本查询功能学会使用INSERT，UPDA TE和DELETE语句去操作数据库数据学会使用COMMIT，ROLLBACK和SA VEPOINT语句去控制事务学会使用SELECT语句去实现各种复杂查询功能（数据分组、连接查询、子查询、层次查询、合并查询等）4.1.使用基本查询处理NULL：函数nvl(expr1,expr2)，nvl2(expr1,expr2,expr3)4.2.使用DML语句使用多表插入数据语法：INSERT ALL insert_into_clause [value_clause] subquery;INSERT conditional_insert_clause subquery;示例1：使用ALL 操作符执行多表插入INSERT ALLWHEN deptno=10 THEN INTO dept10WHEN deptno=20 THEN INTO dept20WHEN deptno=30 THEN INTO dept30WHEN job=’CLERK’ THEN INTO clerkELSE INTO otherSelect * from emp;示例2：使用FIRST 操作符执行多表插入INSERT FIRSTWHEN deptno=10 THEN INTO dept10WHEN deptno=20 THEN INTO dept20WHEN deptno=30 THEN INTO dept30WHEN job=’CLERK’ THEN INTO clerkELSE INTO otherSELECT * FROM emp;4.3.使用事务控制语句4.3.1.事务和锁4.3.2.提交事务4.3.3.回退事务设置保存点：savepoint a;或者exec dbms_transaction.savepoint(‘a’)取消部分事务Rollback to a;或者Exec dbms_transaction.rollback_savepoint(‘a’)取消全部事务：Rollback; 或者exec dbms_transaction.rollback() 4.3.4.只读事务4.3.5.顺序事务4.4.数据分组4.4.1.分组函数MaxMinAvgSumCountVarianceStddev使用分组函数注意事项：●当使用分组函数时，除了函数Count(*) 之外，其他分组函数都会忽略NULL行。

二、理解over（）函数

二、理解over（）函数

⼆、理解over（）函数为了⽅便进⾏实践，特将演⽰表和数据罗列如下：⼀、创建表create table t(bill_month varchar2(12) ,area_code number,net_type varchar(2),local_fare number);⼆、插⼊数据insert into t values('200405',5761,'G', 7393344.04);insert into t values('200405',5761,'J', 5667089.85);insert into t values('200405',5762,'G', 6315075.96);insert into t values('200405',5762,'J', 6328716.15);insert into t values('200405',5763,'G', 8861742.59);insert into t values('200405',5763,'J', 7788036.32);insert into t values('200405',5764,'G', 6028670.45);insert into t values('200405',5764,'J', 6459121.49);insert into t values('200405',5765,'G', 13156065.77);insert into t values('200405',5765,'J', 11901671.70);insert into t values('200406',5761,'G', 7614587.96);insert into t values('200406',5761,'J', 5704343.05);insert into t values('200406',5762,'G', 6556992.60);insert into t values('200406',5762,'J', 6238068.05);insert into t values('200406',5763,'G', 9130055.46);insert into t values('200406',5763,'J', 7990460.25);insert into t values('200406',5764,'G', 6387706.01);insert into t values('200406',5764,'J', 6907481.66);insert into t values('200406',5765,'G', 13562968.81);insert into t values('200406',5765,'J', 12495492.50);insert into t values('200407',5761,'G', 7987050.65);insert into t values('200407',5761,'J', 5723215.28);insert into t values('200407',5762,'G', 6833096.68);insert into t values('200407',5762,'J', 6391201.44);insert into t values('200407',5763,'G', 9410815.91);insert into t values('200407',5763,'J', 8076677.41);insert into t values('200407',5764,'G', 6456433.23);insert into t values('200407',5764,'J', 6987660.53);insert into t values('200407',5765,'G', 14000101.20);insert into t values('200407',5765,'J', 12301780.20);insert into t values('200408',5761,'G', 8085170.84);insert into t values('200408',5761,'J', 6050611.37);insert into t values('200408',5762,'G', 6854584.22);insert into t values('200408',5762,'J', 6521884.50);insert into t values('200408',5763,'G', 9468707.65);insert into t values('200408',5763,'J', 8460049.43);insert into t values('200408',5764,'G', 6587559.23);insert into t values('200408',5764,'J', 7342135.86);insert into t values('200408',5765,'G', 14450586.63);insert into t values('200408',5765,'J', 12680052.38);commit;三、first_value()与last_value()：求最值对应的其他属性问题、取出每⽉通话费最⾼和最低的两个地区。