ORACLE GROUPINGº¯ÊýµÄʹÓÃ
GROUPINGº¯Êý¿ÉÒÔ½ÓÊÜÒ»ÁУ¬·µ»Ø0»òÕß1¡£Èç¹ûÁÐֵΪ¿Õ£¬ÄÇôGROUPING()·µ»Ø1£»Èç¹ûÁÐÖµ·Ç¿Õ£¬ÄÇô·µ»Ø0¡£GROUPINGÖ»ÄÜÔÚʹÓÃROLLUP»òCUBEµÄ²éѯÖÐʹÓᣵ±ÐèÒªÔÚ·µ»Ø¿ÕÖµµÄµØ·½ÏÔʾij¸öֵʱ£¬GROUPING()¾Í·Ç³£ÓÐÓá£
¹ØÓÚROLLUPºÍCUBEº¯ÊýµÄʹÓã¬Çë²Î¼ûÎÒµÄÁíһƪÎÄÕ¡£
http://blog.csdn.net/wh62592855/archive/2009/11/16/4817920.aspx
1¡¢ÔÚROLLUPÖжԵ¥ÁÐʹÓÃGROUPING()
SQL> select division_id,sum(salary)
2 from employees2
3 group by rollup(division_id)
4 order by division_id;
DIV SUM(SALARY)
--- -----------
BUS 1610000
OPE 1320000
SAL 4936000
SUP 1015000
8881000
¼ÓÉÏGROUPINGÀ´¿´¿´
SQL> select grouping(division_id),division_id,sum(salary)
2 from employees2
3 group by rollup(division_id)
4 order by division_id;
GROUPING(DIVISION_ID) DIV SUM(SALARY)
--------------------- --- -----------
0 BUS 1610000
0 OPE 1320000
0 SAL 4936000
0 SUP 1015000
1 8881000
¿ÉÒÔ¿´µ½£¬Îª¿ÕµÄµØ·½·µ»Ø1£¬·Ç¿ÕµÄµØ·½·µ»Ø0¡£
2¡¢Ê¹ÓÃCASEת»»GROUPING()µÄ·µ»ØÖµ
¿ÉÄÜÄã»á¾õµÃÇ°ÃæµÄ0ºÍ1Ì«¿ÝÔïÁË£¬´ú±í²»ÁËÈκÎÒâÒ壬˵°×Á˾ÍÊDz»¹»ÈËÐÔ»¯£¬ºÇºÇ¡£Õâ¸öʱºòÎÒÃÇ¿ÉÒÔʹÓÃCASEÀ´×ª»»ÎªÒ
Ïà¹ØÎĵµ£º
oracle±í¿Õ¼ä²Ù×÷Ïê½â
1
2
3×÷Õߣº À´Ô´£º ¸üÐÂÈÕÆÚ£º2006-01-04
5
6
7½¨Á¢±í¿Õ¼ä
8
9CREATE TABLESPACE data01
10DATAFILE '/ora ......
ÔÎĵØÖ·£ºhttp://oracle.chinaitlab.com/induction/725752.html
Ò»¡¢logminerµÄÓÃ;
¡¡¡¡ÈÕÖ¾ÎļþÖдæ·Å×ÅËùÓнøÐÐÊý¾Ý¿â»Ö¸´µÄÊý¾Ý£¬¼Ç¼ÁËÕë¶ÔÊý¾Ý¿â½á¹¹µÄÿһ¸ö±ä»¯£¬Ò²¾ÍÊǶÔÊý¾Ý¿â²Ù×÷µÄËùÓÐDMLÓï¾ä¡£
¡¡¡¡logminer ¹¤¾ß¼´¿ÉÒÔÓÃÀ´·ÖÎöÔÚÏߣ¬Ò²¿ÉÒÔÓÃÀ´·ÖÎöÀëÏßÈÕÖ¾Îļþ£¬¼´¿ÉÒÔ·ÖÎö±¾Éí×Ô¼ºÊý¾Ý¿âµÄÖØ×÷ÈÕÖ¾ÎÄ ......
ÓÐÁËÓα꣬¾Í¿ÉÒÔ²»ÓÃÿÌõÊý¾ÝÈ¥¼ì²éÊÇ·ñ·ûºÏÌõ¼þ£¬ÏÈ¿´Ò»ÏÂÏÂÃæµÄ£º
--ÓαêʹÓã¨ÓαêÆäʵÊÇÒ»¸ö·ÅÈëÄÚ´æÁÙʱ±í£©
declare
money cms3_simcard.card_fee%type :=0; --¶¨ÒåÓë±í×Ö¶ÎÏàͬÀàÐÍ
cursor mycursor is --¶¨ÒåÓαê
select ......
¡¡¡¡1¡¢Òì³£µÄÓŵã
¡¡¡¡
¡¡¡¡Èç¹ûûÓÐÒì³££¬ÔÚ³ÌÐòÖУ¬Ó¦µ±¼ì²éÿ¸öÃüÁîµÄ³É¹¦»¹ÊÇʧ°Ü£¬Èç
¡¡¡¡BEGIN
¡¡¡¡SELECT ...
¡¡¡¡-- check for ’no data found’ error
¡¡¡¡SELECT ...
¡¡¡¡-- check for ’no data found’ error
¡¡¡¡SELECT ...
¡¡¡¡-- check for ’no data found’ err ......
--ÔÚÓ¦ÓóÌÐòÖÐÍùÍùÓÐЩ¹Ì¶¨µÄÊý¾Ý¿â²Ù×÷£¬ÎÒÃdz£³£ÎªÖÂл¹Ì¶¨µÄ²Ù×÷¶¨ÒåÁËһЩ´æ´¢¹ý³Ì¡£ÕâЩ´æ´¢¹ý³Ì²»½ö
--¿ÉÒÔ¼ò»¯¿Í»§¶ËµÄÓ¦ÓóÌÐòµÄ¿ª·¢ºÍά»¤¡£¶øÇÒ»¹¿ÉÒÔÌá¸ßÓ¦ÓõÄÔËÐÐÐÔÄÜ¡£
-- ´´½¨´æ´¢¹ý³ÌµÄÓï·¨¡£
create [or replace] procedure <¹ý³ÌÃû>[(²ÎÊý)]
is|as
[¾Ö²¿±äÁ¿ÉùÃ÷]
begin
......