Ò׽ؽØÍ¼Èí¼þ¡¢µ¥Îļþ¡¢Ãâ°²×°¡¢´¿ÂÌÉ«¡¢½ö160KB

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()¼ÌÐøµÝ¹é


Ïà¹ØÎĵµ£º

´«ÖDz¥¿Í——Êý¾Ý¿âÖ®ORACLE£¨¶þ£©


¡¡¡¡1£© ÓÃSELECTÓï¾ä´Ó±íÖÐÌáÈ¡²éѯÊý¾Ý¡£Ó﷨Ϊ
¡¡¡¡SELECT [DISTINCT] {column1,column2,…} from tablename WHERE {conditions} GROUP BY {conditions} ORDER BY {expressions} [ASC/DESC]; 
¡¡¡¡ËµÃ÷£ºSELECT×Ó¾äÓÃÓÚÖ¸¶¨¼ìË÷Êý¾Ý¿âµÄÖÐÄÄЩÁУ¬from×Ó¾äÓÃÓÚÖ¸¶¨´ÓÄÄÒ»¸ö±í»òÊÓͼÖмìË÷Êý¾Ý¡£
¡¡¡¡2£© ......

Ïòoracle±íÖвåÈë´óÁ¿Êý¾Ý

ÐèÒª´óÁ¿oracle²âÊÔÊý¾Ýʱ£¬¿ÉÒÔʹÓÃÒÔÏ·½·¨¡£
DECLARE
 i INT;
BEGIN
i := 0;
WHILE(i < 100000)
LOOP
 i := i + 1;
 INSERT INTO TEST_TABLE(ID, XM) VALUES(i, 'ÐÕÃû' || i);
END LOOP;
COMMIT;
 END; ......

OracleÊý¾Ý¿âÉè¼Æ

Êý¾ÝownerºÍÊý¾Ý²Ù×÷ÕßÒÔ¼°´úÂë·ÃÎÊʹÓò»Í¬µÄÓû§·ÃÎÊ£¬Ê¹ÓÃÊÚȨµÄ·½Ê½½øÐзÃÎÊ¡£
Êý¾Ý±í´´½¨Ê±Ê¹ÓÃͬÒå´Ê´´½¨
ÊÚȨʱ×îºÃ²»ÒªÊ¹ÓÃwith grant option
ÒÔǰ²»Ì«Ã÷°×ͬÒå´ÊµÄʹÓã¬ÏÖÔÚÖªµÀÁË¡£±È½ÏºÃµÄÓ¦ÓþÍÊÇ´´½¨±íµÄ±ðÃû
create public ͬÒå´Ê tableName for owner.tableName
 ¸øÊÓͼÊÚȨ¿ÉÒÔʹµÃÓû§·ÃÎʲ»¾ß ......

½ÌÄãÈçºÎÓÃOMSʵÏÖOracleÖÐÊý¾Ý±¸·Ý

ÔÚOracle 9iÖпÉÒÔ·½±ãµÄ°ÑÊý¾Ýµ¼³öΪÎļþ£¬»òÕß´ÓÎļþµ¼È롣ͨ¹ýOracleµÄÖÎÀí·þÎñÆ÷£¨Oracle Management Server£¬OMS£©¿ÉÒÔ·½±ãµÄʵÏÖ£¬Õû¸ö²Ù×÷¹ý³ÌÖÐÓÐºÜ¶àµØ·½ÐèÒª½âÊÍ˵Ã÷£¬µ«ÊÇÍêÕûµÄÖ¸µ¼×ÊÁϲ»¶à¡£¸÷´óÂÛ̳ÉϺܶàËùνµÄ¸ßÊÖ£¬¶Ô¸ÃÖ÷ÌâµÄ»Ø´ðÍùÍùÊÇ£¬¸ßÊÖÊDz»ÓÃOMSµÄ£¬ÓÃeXP/impl°É¡£¼ÙÈçÄãÖ´Òâ×·ÎÊ£¬Ëû¶à°ë»á¸æËßÄ㣠......
© 2009 ej38.com All Rights Reserved. ¹ØÓÚE½¡ÍøÁªÏµÎÒÃÇ | Õ¾µãµØÍ¼ | ¸ÓICP±¸09004571ºÅ