OracleÖÐstart with¡connect by prior×Ó¾äÓ÷¨
OracleÖÐstart with…connect by prior×Ó¾äÓ÷¨
connect by Êǽṹ»¯²éѯÖÐÓõ½µÄ£¬Æä»ù±¾Óï·¨ÊÇ£º
select … from tablename
start with Ìõ¼þ1
connect by Ìõ¼þ2
where Ìõ¼þ3;
Àý£º
select * from table
start with org_id = ‘HBHqfWGWPy’
connect by prior org_id = parent_id;
¼òµ¥ËµÀ´Êǽ«Ò»¸öÊ÷×´½á¹¹´æ´¢ÔÚÒ»ÕűíÀ±ÈÈçÒ»¸ö±íÖдæÔÚÁ½¸ö×Ö¶Î:
org_id£¬parent_idÄÇôͨ¹ý±íʾÿһÌõ¼Ç¼µÄparentÊÇË£¬¾Í¿ÉÒÔÐγÉÒ»¸öÊ÷×´½á¹¹¡£
ÓÃÉÏÊöÓï·¨µÄ²éѯ¿ÉÒÔÈ¡µÃÕâ¿ÃÊ÷µÄËùÓмǼ¡£
ÆäÖУº
Ìõ¼þ1 ÊǸù½áµãµÄÏÞ¶¨Óï¾ä£¬µ±È»¿ÉÒÔ·Å¿íÏÞ¶¨Ìõ¼þ£¬ÒÔÈ¡µÃ¶à¸ö¸ù½áµã£¬Êµ¼Ê¾ÍÊǶà¿ÃÊ÷¡£
Ìõ¼þ2 ÊÇÁ¬½ÓÌõ¼þ£¬ÆäÖÐÓÃPRIOR±íʾÉÏÒ»Ìõ¼Ç¼£¬±ÈÈç CONNECT BY PRIOR org_id = parent_id£»¾ÍÊÇ˵ÉÏÒ»Ìõ¼Ç¼µÄorg_id ÊDZ¾Ìõ¼Ç¼µÄparent_id£¬¼´±¾¼Ç¼µÄ¸¸Ç×ÊÇÉÏÒ»Ìõ¼Ç¼¡£
Ìõ¼þ3 ÊǹýÂËÌõ¼þ£¬ÓÃÓÚ¶Ô·µ»ØµÄËùÓмǼ½øÐйýÂË¡£
¼òµ¥½éÉÜÈçÏ£º
ÔÚɨÃèÊ÷½á¹¹±íʱ£¬ÐèÒªÒÀ´Ë·ÃÎÊÊ÷½á¹¹µÄÿ¸ö½Úµã£¬Ò»¸ö½ÚµãÖ»ÄÜ·ÃÎÊÒ»´Î£¬Æä·ÃÎʵIJ½ÖèÈçÏ£º
µÚÒ»²½£º´Ó¸ù½Úµã¿ªÊ¼£»
µÚ¶þ²½£º·ÃÎʸýڵ㣻
µÚÈý²½£ºÅжϸýڵãÓÐÎÞδ±»·ÃÎʵÄ×ӽڵ㣬ÈôÓУ¬ÔòתÏòËü×î×ó²àµÄδ±»·ÃÎʵÄ×Ó½Ú£¬²¢Ö´Ðеڶþ²½£¬·ñÔòÖ´ÐеÚËIJ½£»
µÚËIJ½£ºÈô¸Ã½ÚµãΪ¸ù½Úµã£¬Ôò·ÃÎÊÍê±Ï£¬·ñÔòÖ´ÐеÚÎå²½£»
µÚÎå²½£º·µ»Øµ½¸Ã½ÚµãµÄ¸¸½Úµã£¬²¢Ö´ÐеÚÈý²½Öè¡£
×ÜÖ®£ºÉ¨ÃèÕû¸öÊ÷½á¹¹µÄ¹ý³ÌÒ²¼´ÊÇÖÐÐò±éÀúÊ÷µÄ¹ý³Ì¡£
1£®Ê÷½á¹¹µÄÃèÊö
Ê÷½á¹¹µÄÊý¾Ý´æ·ÅÔÚ±íÖУ¬Êý¾ÝÖ®¼äµÄ²ã´Î¹Øϵ¼´¸¸×Ó¹Øϵ£¬Í¨¹ý±íÖеÄÁÐÓëÁмäµÄ¹ØϵÀ´ÃèÊö£¬ÈçEMP±íÖеÄEMPNOºÍMGR¡£EMPNO±íʾ¸Ã¹ÍÔ±µÄ±àºÅ£¬MGR±íʾÁìµ¼¸Ã¹ÍÔ±µÄÈ˵ıàºÅ£¬¼´×Ó½ÚµãµÄMGRÖµµÈÓÚ¸¸½ÚµãµÄEMPNOÖµ¡£ÔÚ±íµÄÿһÐÐÖж¼ÓÐÒ»¸ö±íʾ¸¸½ÚµãµÄMGR£¨³ý¸ù½ÚµãÍ⣩£¬Í¨¹ýÿ¸ö½ÚµãµÄ¸¸½Úµã£¬¾Í¿ÉÒÔÈ·¶¨Õû¸öÊ÷½á¹¹¡£
ÔÚSELECTÃüÁîÖÐʹÓÃCONNECT BY ºÍSTART WITH ×Ó¾ä¿ÉÒÔ²éѯ±íÖеÄÊ÷Ðͽṹ¹Øϵ¡£ÆäÃüÁî¸ñʽÈçÏ£º
SELECT . . .
CONNECT BY {PRIOR ÁÐÃû1=ÁÐÃû2|ÁÐÃû1=PRIOR ÁÑÃû2}
[START WITH]£»
ÆäÖУºCONNECT BY×Ó¾ä˵Ã÷ÿÐÐÊý¾Ý½«ÊÇ°´²ã´Î˳Ðò¼ìË÷£¬²¢¹æ¶¨½«±íÖеÄÊý¾ÝÁ¬ÈëÊ÷ÐͽṹµÄ¹ØϵÖС£PRIORÔËËã·û±ØÐë·ÅÖÃÔÚÁ¬½Ó¹ØϵµÄÁ½ÁÐÖÐijһ¸öµÄÇ°Ãæ¡£¶ÔÓÚ½Úµã¼äµÄ¸¸×Ó¹Øϵ£¬PRIORÔËËã·ûÔÚÒ»²à±íʾ¸¸½Úµã£¬ÔÚÁíÒ»²à±íʾ×Ó½Úµã£
Ïà¹ØÎĵµ£º
ÔÚÇ°ÃæѧϰOracleÊý¾Ý¿â»ù´¡¼Ü¹¹Ê±£¬ÒѾÁ˽âÁËOracleµÄ´æ´¢½á¹¹£¬Âß¼ÉÏ£¬OracleµÄÊý¾Ý´æ·ÅÔÚtablespacesÖУ¬ÎïÀíÉÏ´æ·ÅÔÚdatafilesÖС£Ò»¸ötablespaceÖ»ÄÜÊôÓÚÒ»¸öÊý¾Ý¿â(Ò»¸öÊý¾Ý¿â¿É°üÀ¨¶à¸ötablespace)£¬°üÀ¨ÁË1¸ö»ò¶à¸öÊý¾ÝÎļþ¡£Tablespace¿É½øÒ»²½·ÖΪsegments¡¢extentsºÍblocks¡£Ò»¸ödatafileÖ»ÊôÓÚÒ»¸öÊý¾Ý¿âµÄÒ» ......
ORACLEµÄѧϰÒѾ¸æÒ»¶Î茖£¬½ñÌìÒѾ¿ªÊ¼HIBERNATEµÄѧϰ£¬×ÜÌå¸Ð¾õͦÁ¼ºÃµÄ£¬ËäÈ»ºÜ¶àµÄ֪ʶ²»ÄܼÇס£¬µ«ÊÇÔÚʹÓõÄʱºòÄܹ»Ñ¸ËÙµÄÕÒ¶Ô·½·¨À´½â¾öÏàÓ¦µÄÎÊÌ⣬ֻҪÇÚ¼ÓÁ·Ï°£¬¾Í¿ÉÒÔ¼Çס¡£Ö»²»¹ý£¬Ê±¼äÓÐ珢£¬HIBERNATEµÄ¿Î³ÌÒ²Ö»ÓÐÈýÌìµÄʱ¼ä£¬ËùÒÔÔÚÕâÆÚ¼äÎÒµÄÈÎÎñÊǾ¡Á¿ÎüÒý£¬ÔÚ»¨·Å¼ÙµÄʱºòÀ´¸´Ï° ......
ÒÔÏÂÎÒ×östreammͬ²½Á½Ì¨»úÆ÷¼äµÄijһ¸öÓû§µÄ²Ù×÷²½Öè¡£ºÜ¼òµ¥,¿´µ½stream
»¹
ÓкܶàÒª¿´µÄ¡£
1.»·¾³½éÉÜ
Ö÷Êý¾Ý¿âSID : obpm
²Ù×÷ϵͳ : win 2003
IPµØÖ· : 192.168.0.1
Global_name :
oracle
version: 10.2.0.1
´ÓÊý¾Ý¿âSID ......