¹ØÓÚ·Ö×éºó×Ö¶ÎÆ´½ÓµÄÎÊÌâ (Oracle)
£££££¹ØÓÚ·Ö×éºó×Ö¶ÎÆ´½ÓµÄÎÊÌâ
À´×Ô:www.itpub.net
×î½üÔÚÂÛ̳ÉÏ£¬¾³£»á¿´µ½¹ØÓÚ·Ö×éºó×Ö¶ÎÆ´½ÓµÄÎÊÌ⣬
´ó¸ÅÊÇÀàËÆÏÂÁеÄÇéÐΣº
SQL> select no,q from test
2 /
NO Q
---------- ------------------------------
001 n1
001 n2
001 n3
001 n4
001 n5
002 m1
003 t1
003 t2
003 t3
003 t4
003 t5
003 t6
12 rows selected
×îºóÒªµÃµ½ÀàËÆÓÚÈçϵĽá¹û£º
001 n1;n2;n3;n4;n5
002 m1
003 t1;t2;t3;t4;t5;t6
ͨ³£´ó¼Ò¶¼ÈÏΪÕâÀàÎÊÌâÎÞ·¨ÓÃÒ»¾äSQL½â¾ö£¬±¾À´ÎÒÒ²ÕâôÈÏΪ£¬¿ÉÊǽñÌìÎÞÒâÖÐͻȻÓÐÁËÁé¸Ð£¬ÔÀ´ÊÇ¿ÉÒÔÕâô×öµÄ£º
ǰ¼¸ÌìÓÐÈËÌáµ½¹ýsys_connect_by_pathµÄÓ÷¨£¬ÎÒÏëÕâÀïÊDz»ÊÇÒ²ÄÜÓõ½Õâ¸ö·½·¨£¬Èç¹ûÄÜ×öµ½µÄ»°£¬²»Óú¯Êý»ò´æÖü¹ý³ÌÒ²¿ÉÒÔ×öµ½ÁË£»ÒªÓõ½sys_connect_by_path£¬Ê×ÏÈÒª×Ô¼º¹¹½¨Ê÷Ð͵Ľṹ£¬²¢ÇÒÊ÷µÄÿ¸ö·ÖÖ§¶¼Êǵ¥¸ùµÄ£¬ÀýÈç1-¡µ2-¡µ3-¡µ4£¬²»»á´æÔÚ1-¡µ2£¬1-¡µ3µÄÇé¿ö£»
ÎÒÊÇÕâô¹¹½¨Ê÷£¬ºÜ¼òµ¥µÄ£¬¿´ÏÂÃæµÄ½á¹û¾Í»áÖªµÀÁË£º
SQL> select no,q,rn,lead(rn) over(partition by no order by rn) rn1
2 from (select no,q,row_number() over(order by no,q desc) rn from test)
3 /
NO Q RN RN1
---------- ------------------------------ ---------- ----------
001 n5 1 2
001 n4 2 3
001 n3 3 4
001 n2 4 5
001 n1 5
002 m1 6
003 t6 7 8
003 t5 8 9
003 t4 9 10
003 t3 10 11
003 t2 11 12
003 t1 12
12 rows selected
ÓÐÁËÕâ¸öÊ÷Ð͵Ľṹ£¬½ÓÏÂÀ´µÄʾͺðìÁË£¬Ö»ÒªÈ¡³öÓµÓÐȫ·¾¶µÄÄǸöpath£¬ÎÊÌâ¾Í½â¾öÁË£¬ÏÈ¿´no=‘001’µÄ·Ö×飺
select no,sys_connect_by_path(q,';') result from
(select no,q,rn,lead(rn) over(partition by no order by rn) rn1
from (select no,q,row_number() over(order by no,q desc) rn from test)
)
start with no = '001' and rn1 is null connect by rn1 = prior rn
SQL>
6 /
NO RESULT
---------- --------------------------------------------------------------------------------
001 ;n1
001 ;n1;n2
001 ;n1;n2;n3
001 ;n1;n2;n3;n4
001 ;n1;n2;n3;n4;n5
ÉÏÃæ½á¹ûµÄ×îºó1Ìõ¾ÍÊÇÎÒÃÇÒªµÃ½á¹ûÁË
ÒªµÃµ½Ã¿×éµÄ½á¹û£¬¿ÉÒÔÏÂÃæÕâÑù
´úÂë:
select t.*,
(
select max(sys_connect_by_
Ïà¹ØÎĵµ£º
Æäʵ£¬ÕâÆª¶ÌÎÄ£¬ÎÒÔç¾ÍÓ¦¸ÃдÁË¡£ÒòΪ£¬java´æ´¢¹ý³Ì½ñºóÔÚ¸÷´óÊý¾Ý¿â³§ÉÌÖÐÔ½À´Ô½Á÷ÐУ¬¹¦ÄÜÒ²Ô½À´Ô½Ç¿´ó¡£ÕâÀïÒÔOracleΪÀý£¬½éÉÜÒ»ÏÂjava´æ´¢¹ý³ÌµÄ¾ßÌåÓ÷¨¡£
Ò»¡¢ÈçºÎ´´½¨java´æ´¢¹ý³Ì£¿
ͨ³£ÓÐÈýÖÖ·½·¨À´´´½¨java´æ´¢¹ý³Ì¡£
1. ʹÓÃoracleµÄsqlÓï¾äÀ´´´½¨£º
e.g. ʹÓÃcreate or replace and compile java source ......
ÓÃOracle²éѯÓï¾äÓ¦ÓþÑé×ܽá
¡¡ÒÔϵÄÎÄÕÂÖ÷ÒªÊǽéÉÜOracle²éѯÓï¾äµÄʵ¼ÊÓ¦ÓõľÑé×ܽᣬÄãÈç¹û¶ÔOracle²éѯÓï¾äµÄʵ¼ÊÓ¦ÓøÐÐËȤµÄ»°Äã¾Í¿ÉÒÔµã»÷ÒÔϵÄÎÄÕ½øÐйۿ´ÁË¡£ Ï£Íû»á¸øÄã´øÀ´Ò»Ð©°ïÖúÔÚ´Ë·½Ãæ¡£
¡¡¡¡1. select * from table_name where rownum>begin and rownum< end
¡¡¡¡2.sql = "select * fr ......
OracleÓëSQL ServerÊÂÎñ´¦ÀíµÄ±È½Ï
ÊÂÎñ´¦ÀíÊÇËùÓдóÐÍÊý¾Ý¿â²úÆ·µÄÒ»¸ö¹Ø¼üÎÊÌ⣬¸÷Êý¾Ý¿â³§É̶¼ÔÚÕâ¸ö·½Ã滨·ÑÁ˺ܴó
¾«Á¦£¬²»Í¬µÄÊÂÎñ´¦Àí·½Ê½»áµ¼ÖÂÊý¾Ý¿âÐÔÄܺ͹¦ÄÜÉϵľ޴ó²îÒì¡£
ÊÂÎñ´¦ÀíÒ²ÊÇÊý¾Ý¿â¹ÜÀíÔ±ÓëÊý¾Ý¿âÓ¦ÓóÌÐò¿ª·¢ÈËÔ±±ØÐëÉî¿ÌÀí½âµÄÒ»¸öÎÊÌ⣬¶ÔÕâ¸öÎÊÌâµÄÊèºö¿ÉÄܻᵼÖÂÓ¦ÓóÌÐòÂß¼´í ......
OracleÊý¾Ý¿âµ¼Èë±ÃºÍµ¼³ö±ÃimpdpºÍexpdp£¨1£©
Oracleµ¼Èëµ¼³ö¹Ù·½¶¨Òå
ÈçºÎ²é¿´ImpdpºÍExpdpÃüÁ
ǰÌ᣺°²×°OracleÊý¾Ý¿â£¨µ¼Èë±ÃºÍµ¼³ö±ÃÖ»ÄÜÔÚOracleÊý¾Ý¿â·þÎñÆ÷¶ËʹÓã©¡£
Ò»¡¢ IMPDPÃüÁî
1.   ......
ÔÚOracleÊý¾Ý¿â¹ÜÀíϵͳÖУ¬´´½¨¿â±í£¨table£©Ê±Òª·ÖÅäÒ»¸ö±í¿Õ¼ä£¨tablespace£©£¬Èç¹ûδָ¶¨±í¿Õ¼ä£¬ÔòʹÓÃϵͳÓû§È·Ê¡µÄ±í¿Õ¼ä¡£
ÔÚOracleʵ¼ÊÓ¦ÓÃÖУ¬ÎÒÃÇ¿ÉÄÜ»áÓöµ½ÕâÑùµÄÎÊÌâ¡£´¦ÓÚÐÔÄÜ»òÕ߯äËû·½ÃæµÄ¿¼ÂÇ£¬ÐèÒª¸Ä±äij¸ö±í»òÕßÊÇij¸öÓû§µÄËùÓбíµÄ±í¿Õ¼ä¡£Í¨³£µÄ×ö·¨¾ÍÊÇÊ×ÏȽ«±íɾ³ý£¬È»ºóÖØÐ½¨±í£¬ÔÚн¨±íʱ½«± ......