Ò׽ؽØÍ¼Èí¼þ¡¢µ¥Îļþ¡¢Ãâ°²×°¡¢´¿ÂÌÉ«¡¢½ö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()¼ÌÐøµÝ¹é


Ïà¹ØÎĵµ£º

±±´óÇàÄñoracleѧϰ±Ê¼Ç25

¹ý³ÌÖеÄÊÂÎñ
¶¨Òå¹ý³Ìp1
create or replace procedure p1
as
begin
insert into student values(5,'xdh','m',sysdate);
rollback;
end;
¶¨Òå¹ý³Ìp2
create or replace procedure p2
as
begin
update student set stu_sex = 'a' where stu_id = 3;
p1;
end;
Ö´Ðйý³Ìp2

exec p2;
Ö´ÐÐÍê±Ï·¢ÏÖ ......

¹ØÓÚplsqlÖеÄdefine±äÁ¿ÒÔ¼°Oracle±äÁ¿·ÖÀàС½á

¹ØÓÚplsqlÖеÄdefine±äÁ¿ÒÔ¼°Oracle±äÁ¿·ÖÀàС½á
2009-07-29 15:18
ÏȼÇÔØ¸ÕÀ§ÈÅÎÒµÄÒ»¸öÎÊÌ⣬×î½üѧϰplsql£¬ÓÉÓÚËùÓÃѧϰÊé¼®ºóÃæÌṩÌâÄ¿³£Óõ½define±äÁ¿£¬µ«ÓÉÓÚÕâÒ»±äÁ¿µÄʹÓÃÌØÊâÐÔ£¬×Ô¼º±ãѰ˼ÕâÒ»±äÁ¿ËùÊéÀà±ð£¬OracleÌṩµÄ±äÁ¿·ÖÀ๲ÓÐËÄÀࣺ
1£©±êÁ¿£¨scalar£©ÀàÐÍ
2£©¸´ºÏ£¨composite£©ÀàÐÍ
3£©²ÎÕÕ£¨re ......

Ïò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 R12¹ËÎÊÈÏÖ¤

²©ÑåÅàѵ²¿ÊÇĿǰ¹úÄÚΨһµÄ Oracle ¹Ù·½ÊÚȨ ERP ÈÏÖ¤Åàѵ»ú¹¹
    Ŀǰ£¬Oracle Ó¦ÓÃϵͳÔÚÈ«Çò¿ç¹ú¹«Ë¾µÃµ½¹ã·ºÓ¦Óã¬ÖîÈçÖйúÒÆ¶¯¡¢ÉîÛÚ»ªÎª¡¢»ôÄáΤ¶û¡¢¿µÃ÷˹Öйú¡¢ÃÀ¹úÂÁÒµ¡¢DHL ºÍ±¦ÐÅÈí¼þµÈÖªÃû¹«Ë¾¡£Îª´Ë£¬Oracle ×Éѯ¹ËÎÊÊÇÈ«ÇòºÍÖйúÊг¡ÉÏ×î½ôȱµÄÈ˲ÅÖ®Ò»¡£Í¨¹ýOracle ÉÌÎñÌ×¼þÈÏÖ¤µÄ×Éѯ¹ËÎ ......

oracleÖеĽÇÉ«


oracle
ÖеĽÇÉ«
Ò»¡¢ºÎΪ½ÇÉ«£¿
¡¡¡¡ÎÒÔÚÇ°ÃæµÄƪ·ùÖÐ˵Ã÷ȨÏÞºÍÓû§¡£ÂýÂýµÄÔÚʹÓÃÖÐÄã»á·¢ÏÖÒ»¸öÎÊÌ⣺Èç¹ûÓÐÒ»×éÈË£¬
ËûÃǵÄËùÐèµÄȨÏÞÊÇÒ»ÑùµÄ£¬µ±¶ÔËûÃǵÄȨÏÞ½øÐйÜÀíµÄʱºò»áºÜ²»·½±ã¡£ÒòΪÄãÒª¶ÔÕâ×éÖеÄÿ¸öÓû§µÄȨÏÞ¶¼½øÐйÜÀí¡£
¡¡¡¡ÓÐÒ»¸öºÜºÃµÄ½â¾ö°ì·¨¾Í
ÊÇ£º½ÇÉ«¡£½ÇÉ«ÊÇÒ»×éȨÏ޵ļ¯ºÏ£¬½«½ÇÉ«¸³ ......
© 2009 ej38.com All Rights Reserved. ¹ØÓÚE½¡ÍøÁªÏµÎÒÃÇ | Õ¾µãµØÍ¼ | ¸ÓICP±¸09004571ºÅ