OracleÖÐconnect by...start with...µÄʹÓÃ
Ò»¡¢Óï·¨
´óÖÂд·¨£ºselect * from some_table [where Ìõ¼þ1] connect by [Ìõ¼þ2] start with [Ìõ¼þ3];
ÆäÖÐ connect by Óë start with Óï¾ä°Ú·ÅµÄÏȺó˳Ðò²»Ó°Ïì²éѯµÄ½á¹û£¬[where Ìõ¼þ1]¿ÉÒÔ²»ÐèÒª¡£
[where Ìõ¼þ1]¡¢[Ìõ¼þ2]¡¢[Ìõ¼þ3]¸÷×Ô×÷Óõķ¶Î§¶¼²»Ïàͬ£º
[where Ìõ¼þ1]ÊÇÔÚ¸ù¾Ý“connect by [Ìõ¼þ2] start with [Ìõ¼þ3]”Ñ¡Ôñ³öÀ´µÄ¼Ç¼ÖнøÐйýÂË£¬ÊÇÕë¶Ôµ¥Ìõ¼Ç¼µÄ¹ýÂË£¬ ²»»á¿¼ÂÇÊ÷µÄ½á¹¹£»
[Ìõ¼þ2]Ö¸¶¨¹¹ÔìÊ÷µÄÌõ¼þ£¬ÒÔ¼°¶ÔÊ÷·ÖÖ§µÄ¹ýÂËÌõ¼þ£¬ÔÚÕâÀïÖ´ÐеĹýÂË»á°Ñ·ûºÏÌõ¼þµÄ¼Ç¼¼°ÆäϵÄËùÓÐ×ӽڵ㶼¹ýÂ˵ô£»
[Ìõ¼þ3]ÏÞ¶¨×÷ΪËÑË÷ÆðʼµãµÄÌõ¼þ£¬Èç¹ûÊÇ×ÔÉ϶øÏµÄËÑË÷ÔòÊÇÏÞ¶¨×÷Ϊ¸ù½ÚµãµÄÌõ¼þ£¬Èç¹ûÊÇ×Ô϶øÉϵÄËÑË÷ÔòÊÇÏÞ¶¨×÷ΪҶ×Ó½ÚµãµÄÌõ¼þ£»
ʾÀý£º
¼ÙÈçÓÐÈçϽṹµÄ±í£ºsome_table(id,p_id,name)£¬ÆäÖÐp_id±£´æ¸¸¼Ç¼µÄid¡£
select * from some_table t where t.id!=123 connect by prior t.p_id=t.id and t.p_id!=321 start with t.p_id=33 or t.p_id=66;
¶ÔpriorµÄ˵Ã÷£º
prior´æÔÚÓÚ[Ìõ¼þ2]ÖУ¬¿ÉÒÔ²»Òª£¬²»ÒªµÄʱºòÖ»ÄܲéÕÒµ½·ûºÏ“start with [Ìõ¼þ3]”µÄ¼Ç¼£¬²»»áÔÚѰÕÒÕâЩ¼Ç¼µÄ×ӽڵ㡣ҪµÄʱºòÓÐÁ½ÖÖд·¨£ºconnect by prior t.p_id=t.id »ò connect by t.p_id=prior t.id£¬Ç°Ò»ÖÖд·¨±íʾ²ÉÓÃ×ÔÉ϶øÏµÄËÑË÷·½Ê½£¨ÏÈÕÒ¸¸½ÚµãÈ»ºóÕÒ×ӽڵ㣩£¬ºóÒ»ÖÖд·¨±íʾ²ÉÓÃ×Ô϶øÉϵÄËÑË÷·½Ê½£¨ÏÈÕÒÒ¶×Ó½ÚµãÈ»ºóÕÒ¸¸½Úµã£©¡£
¶þ¡¢Ö´ÐÐÔÀí
connect by...start with...µÄÖ´ÐÐÔÀí¿ÉÒÔÓÃÒÔÏÂÒ»¶Î³ÌÐòµÄÖ´ÐÐÒÔ¼°¶Ô´æ´¢¹ý³ÌRECURSE()µÄµ÷ÓÃÀ´ËµÃ÷£º
/* ±éÀú±íÖеÄÿÌõ¼Ç¼£¬¶Ô±ÈÊÇ·ñÂú×ãstart withºóµÄÌõ¼þ£¬Èç¹û²»Âú×ãÔò¼ÌÐøÏÂÒ»Ìõ£¬
Èç¹ûÂú×ãÔòÒԸüÇ¼Ϊ¸ù½Úµã£¬È»ºóµ÷ÓÃRECURSE()µÝ¹éѰÕҸýڵãϵÄ×ӽڵ㣬
Èç´ËÑ»·Ö±µ½±éÀúÍêÕû¸ö±íµÄËùÓмǼ ¡£*/
for rec in (select * from some_table) loop
if FULLFILLS_START_WITH_CONDITION(rec) then
RECURSE(rec, rec.child);
end if;
end loop;
/* ѰÕÒ×Ó½ÚµãµÄ´æ´¢¹ý³Ì*/
procedure RECURSE (rec in MATCHES_SELECT_STMT, new_parent IN field_type) is
begin
APPEND_RESULT_LIST(rec); /*°Ñ¼Ç¼¼ÓÈë½á¹û¼¯ºÏÖÐ*/
/*ÔٴαéÀú±íÖеÄËùÓмǼ£¬¶Ô±ÈÊÇ·ñÂú×ãconnect byºóµÄÌõ¼þ£¬Èç¹û²»Âú×ãÔò¼ÌÐøÏÂÒ»Ìõ£¬
Èç¹ûÂú×ãÔòÔÙÒԸüÇ¼Ϊ¸ù½Úµã£¬È»ºóµ÷ÓÃRECURSE()¼ÌÐøµÝ¹é
Ïà¹ØÎĵµ£º
Oracle RAC
¡¡¡¡RAC£¬È«³Æreal application clusters£¬ÒëΪ“ÕæÕýÓ¦Óü¯Èº”£¬ ÊÇOracleаæÊý¾Ý¿âÖвÉÓõÄÒ»Ïîм¼Êõ£¬Ò²ÊÇOracleÊý¾Ý¿âÖ§³ÖÍø¸ñ¼ÆËã»·¾³µÄºËÐļ¼Êõ¡£
¡¡¡¡Oracle RACÖ÷ÒªÖ§³ÖOracle9i¡¢10g¡¢11g°æ±¾£¬¿ÉÒÔ¿ÉÒÔÖ§³Ö24 x 7 ÓÐЧµÄÊý¾Ý¿âÓ¦Óà ϵͳ£¬Ôڵͳɱ¾·þÎñÆ÷ÉϹ¹½¨¸ß¿ÉÓÃÐÔÊý¾Ý¿âϵͳ£¬²¢Ç ......
Êý¾Ý¿â´¥·¢Æ÷
´¥·¢Æ÷Óï¾ä
ÖÆ¶¨´¥·¢Æ÷¶¨Ê±¡¢Ê¼þ¡¢±íÃû¼°ÀàÐÍ
´¥·¢Æ÷Ö÷Ìå
ÊÇpl/sql¿ì»ò¶Ô¹ý³ÌµÄµ÷ÓÃ
´¥·¢Æ÷ÏÞÖÆ
¿ÉÒÔͨ¹ýwhen×Ó¾äʵÏÖ
DML(insert update delete)
DDL(create alter drop)
Êý¾Ý¿â²Ù×÷(servererror logo ......
LinuxÏÂÆô¶¯oracle10gµÄemºÍisqlplusµÄ·½·¨:
×¢:$ORACLE_HOMEΪoracleµÄ°²×°Â·¾¶
1,ʹÓÃEM(enterprise managment)
1.1Æô¶¯EM
$ORACLE_HOME/bin/emctl start dbconsole
Èç¹û³öÏÖÏÂÃæÏà¹ØÐÅÏ¢,˵Ã÷·þÎñÆô¶¯
Z set to PRC
Oracle Enterprise Manager 10g Database Control Release 10.1.0.3.0
Copyright (c) 1996, 200 ......
alter table µÄ¹¦ÄÜÊÇÐ޸ıí¸ñ¡£°üÀ¨ÖØÃûÃü£¬¼Ó¼õ×Ö¶Î,ÐÞ¸Ä×Ö¶ÎÀàÐͺʹóС£¬´¦Àí Ô¼ÊøµÈµÈ¡£±¾Àý×ÓÖ®´¦Àí±íÃûºÍ×ֶΣ¬´úÂëÈçÏ£º
create table liu(a varchar2(20),b number(2))
alter table liu rename to jin
rename jin to cai
alter table cai add c varchar2(30)
alter table cai add (d varchar2(3 ......
ʱ¼ä: 2006-06-15 À´×Ô£ºCSDN
< type="text/javascript"><!--
google_ad_client = "pub-0103898066674568";
google_ad_width = 728;
google_ad_height = 90;
google_ad_format = "728x90_as";
google_ad_type = "text";
google_ad_channel = "";
google_color_ ......