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()¼ÌÐøµÝ¹é
Ïà¹ØÎĵµ£º
Êý¾Ý¿â´¥·¢Æ÷
´¥·¢Æ÷Óï¾ä
ÖÆ¶¨´¥·¢Æ÷¶¨Ê±¡¢Ê¼þ¡¢±íÃû¼°ÀàÐÍ
´¥·¢Æ÷Ö÷Ìå
ÊÇpl/sql¿ì»ò¶Ô¹ý³ÌµÄµ÷ÓÃ
´¥·¢Æ÷ÏÞÖÆ
¿ÉÒÔͨ¹ýwhen×Ó¾äʵÏÖ
DML(insert update delete)
DDL(create alter drop)
Êý¾Ý¿â²Ù×÷(servererror logo ......
ÏÂÃæÁ½ÆªÎÄÕÂÈ«²¿ÊÇתÌû~
ÔµØÖ·£ºhttp://www.im80hou.com/html/oracle/2009/0722/824.html
ÀíÂÛ£º
ÄÚ´æÓëÓ²Å̵ÄËٶȲîÒ죬´ÓÄÚ´æÖжÁÈ¡Êý¾ÝÒª±È´ÓÓ²ÅÌÖжÁÈ¡Êý¾Ý¿ì10000±¶
ÖÚËùÖÜÖª£¬´ÓÄÚ´æÖжÁÈ¡Êý¾ÝÒª±È´ÓÓ²ÅÌÖжÁÈ¡Êý¾Ý¿ì10000±¶¡£ÕâÖ÷ÒªÊÇÄÚ´æÓëÓ²Å̵ÄËٶȲîÒìËùÔì³ÉµÄ¡£Îª´ËÔÚOracleÊý¾Ý¿âÖÐÌá³öÒ»¸öÊý¾Ý»º´æµÄ ......
Oracle¹ÜÀíÔ±µÄÁ½ÖÖÈÏÖ¤·½·¨
×¢Òâ£ºÖØÒª
parameter ÖÐremote_login_passwordfile
1¡¢alter system set remote_login_passwordfile= none scope =spfile£»
º¬Ò壺·ÇÃÜÂëÈÏÖ¤£¨´Ëʱ½öosÈÏÖ¤ £©
2¡¢alter system set remote_login_passwordfile= exclusive ......
Õâ´ÎµÄ¾ÀúÊÇ×Ô¼ºÕÒÀ´µÄ£¬ÔÚ°²×°ÁË10gµÄÄÇ̨»úÉÏÉý¼¶ÏÂÄÚ´æÒ²±ãÄܺöÓÆÄÇȺÓÑ£¬²»¹ýÏëÏ뻹ÊÇÕÛÌÚϰɣ¬¸øÒƵ½ÅäÖúõãµÄServerÉÏÃæ£¬ËûÃÇÓÃÆðÀ´Ë³ÐÄ£¬ÎÒ×Ô¼ºÒ²ÄÜÕÛÌÚµã¾Ñé¡£
132µÄϵͳÊÇSuSE10.2µÄ£¬Ö®Ç°ÒѾ°²×°ÁËOracle 9i£¬Õâ¾ÍÒâζ×űØÐëÏÈÐ¶ÔØµô9i£¬±¾ÒÔΪ°²×°10gµÄrunInstallerÄܹ»Ð¶Ôصô9i£¬¿É³¢ÊÔÁ˺þö¼ÊdzöÏÖex ......
OracleÊý¾Ýµ¼Èëµ¼³öimp/exp¾ÍÏ൱ÓÚoracleÊý¾Ý»¹ÔÓ뱸·Ý¡£expÃüÁî¿ÉÒÔ°ÑÊý¾Ý´ÓÔ¶³ÌÊý¾Ý¿â·þÎñÆ÷µ¼³öµ½±¾µØµÄdmpÎļþ£¬impÃüÁî¿ÉÒÔ°ÑdmpÎļþ´Ó±¾µØµ¼Èëµ½Ô¶´¦µÄÊý¾Ý¿â·þÎñÆ÷ÖС£ ÀûÓÃÕâ¸ö¹¦ÄÜ¿ÉÒÔ¹¹½¨Á½¸öÏàͬµÄÊý¾Ý¿â£¬Ò»¸öÓÃÀ´²âÊÔ£¬Ò»¸öÓÃÀ´ÕýʽʹÓá£
Ö´Ðл·¾³£º¿ÉÒÔÔÚSQLPLUS.EXE»òÕßDOS£¨ÃüÁîÐУ©ÖÐÖ´Ð ......