OracleÖÐStart with...Connect ByÀí½â¼°Ó÷¨
oracleÖÐconnect by priorʵÏֵݹé²éѯ
ÊÕ¼¯µÄ¼¸ÌõÔÚoracleÖÐͨ¹ýconnect by priorÀ´ÊµÏֵݹé²éѯ
Start with...Connect By×Ó¾äµÝ¹é²éѯһ°ãÓÃÓÚÒ»¸ö±íά»¤Ê÷ÐνṹµÄÓ¦Óá£
´´½¨Ê¾Àý±í£º
CREATE TABLE TBL_TEST
(
ID NUMBER,
NAME VARCHAR2(100 BYTE),
PID NUMBER DEFAULT 0
);
²åÈë²âÊÔÊý¾Ý£º
INSERT INTO TBL_TEST(ID,NAME,PID) VALUES('1','10','0');
INSERT INTO TBL_TEST(ID,NAME,PID) VALUES('2','11','1');
INSERT INTO TBL_TEST(ID,NAME,PID) VALUES('3','20','0');
INSERT INTO TBL_TEST(ID,NAME,PID) VALUES('4','12','1');
INSERT INTO TBL_TEST(ID,NAME,PID) VALUES('5','121','2');
´ÓRootÍùÊ÷Ä©Éҵݹé
select * from TBL_TEST
start with id=1
connect by prior id = pid
´ÓÄ©ÉÒÍùÊ÷ROOTµÝ¹é
select * from TBL_TEST
start with id=5
connect by prior pid = id
===============================================================================================================
ÓÐÒ»Õűí t
×ֶΣº
parent
child
Á½¸ö×ֶεĹØÏµÊǸ¸×Ó¹ØÏµ
дһ¸ösqlÓï¾ä£¬²éѯ³öÖ¸¶¨¸¸ÏÂÃæµÄËùÓеÄ×Ó
±ÈÈç
a b
a c
a e
b b1
b b2
c c1
e e1
e e3
d d1
Ö¸¶¨parent=a£¬Ñ¡³ö
a b
a c
a e
b b1
b b2
c c1
e e1
e e3
&
Ïà¹ØÎĵµ£º
oracle±í¿Õ¼ä²Ù×÷Ïê½â
1
2
3×÷Õߣº À´Ô´£º ¸üÐÂÈÕÆÚ£º2006-01-04
5
6
7½¨Á¢±í¿Õ¼ä
8
9CREATE TABLESPACE data01
10DATAFILE '/ora ......
·½·¨Ò»£¬Ê¹ÓÃSQL*Loader
Õâ¸öÊÇÓõĽ϶àµÄ·½·¨£¬Ç°Ìá±ØÐëoracleÊý¾ÝÖÐÄ¿µÄ±íÒѾ´æÔÚ¡£
´óÌå²½ÖèÈçÏ£º
1 ½«excleÎļþÁí´æÎªÒ»¸öÐÂÎļþ±ÈÈçÎļþÃûΪtext.txt£¬ÎļþÀàÐÍÑ¡Îı¾Îļþ£¨ÖƱí·û·Ö¸ô£©£¬ÕâÀïÑ¡ÔñÀàÐÍΪcsv£¨¶ººÅ·Ö¸ô£©Ò²ÐУ¬µ«ÊÇÔÚдºóÃæµÄcontrol. ......
˵µ½Èí½âÎö£¨soft prase£©ºÍÓ²½âÎö£¨hard prase£©£¬¾Í²»Äܲ»ËµÒ»ÏÂOracle¶ÔsqlµÄ´¦Àí¹ý³Ì¡£µ±Äã·¢³öÒ»ÌõsqlÓï¾ä½»¸¶Oracle£¬ÔÚÖ´ÐкͻñÈ¡½á¹ûǰ£¬Oracle¶Ô´Ësql½«½øÐм¸¸ö²½ÖèµÄ´¦Àí¹ý³Ì£º
1¡¢Óï·¨¼ì²é£¨syntax check£©
¼ì²é´ËsqlµÄƴдÊÇ·ñÓï ......
¡¶1¡·DDLÓï¾ä(Êý¾Ý¶¨ÒåÓïÑÔ) Data Define Language
create
alter
drop
truncate ¿ªÍ·µÄÓï¾ä truncate table <±íÃû>
ÌØµã:<1>½¨Á¢ºÍÐÞ¸ÄÊý¾Ý¶ÔÏó
&nb ......
Ò»¡£Oracle·þÎñÆ÷µÄ³£ÓÃÃüÁîÐÐÏêϸ½²½â
1.Êý¾Ý¿â¹ÜÀíÏà¹Ø
¡¡¡¡bbed.exe¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡Êý¾Ý¿éä¯ÀÀºÍ±à¼¹¤¾ß
¡¡¡¡dbv.exe¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡Êý¾ÝÎļþÂß¼¼ì²â¹¤¾ß
¡¡¡¡exp.exe¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡Êý¾Ýµ¼³ö¹¤¾ß
¡¡¡¡imp.exe¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡Êý¾Ýµ¼È빤¾ß
¡¡¡¡mig.exe¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡Êý¾Ý¿âÒÆÖ²¹¤¾ß
¡¡¡ ......