oracleÖÐʵÏֵݹé²éѯµÄSQL£¨connect by prior£©
Ç°¶Îʱ¼ä×öÁËÒ»¸öÐéÄâ×éÖ¯µÄ´´½¨¡£Õâ¸öÐéÄâ×éÖ¯µÄ´´½¨ÊÇ´ÓÏÖÓеÄ×éÖ¯ÖжÁÈ¡Êý¾ÝÈ»ºó½øÐзÖÎö±£´æµ½ÐéÄâ×éÖ¯±íÖС£±£´æÖ®ºóдÈÕÖ¾·½±ãͬ²½µ½ÆäËüϵͳÖС£¹«Ë¾Ê¹ÓõÄÖ÷Òª¼¼ÊõÊÇSSH¡£ÓÉÓÚÊÇÐÂÈËûÓÐʲô¾Ñ飬¼Ó֮ʱ¼ä±È½Ï½ôÆÈ£¬×îºóʹÓÃÒ»°ãµÄÂ߼˼Ïëͨ¹ýµÝ¹éÔÚhibernateÏÂ˳Àû´´½¨ÁË¡£¿ÉÊÇ´ÓÏÖÓеÄ×éÖ¯ÖжÁÈ¡10¶àÍòÌõµÄ¼Ç¼£¬È»ºóÖðÌõ·ÖÎö£¬±£´æ£¬ÔÚдÈÕÖ¾¼Ç¼£¬ÔÚΪÿ¸öÓ¦ÓÃдÏ·¢¶ÓÁнá¹ûÐÔÄܷdz£µÍ¡£ÎÒÃǵÄϵͳÖеÄÊý¾ÝҪϷ¢µ½300¶à¸öÓ¦ÓÃϵͳ£¬Ò²¾ÍÊÇ˵¶ÁÈ¡10¶àÍòÌõ¼Ç¼¾ÍÒª·ÖÎöÊ®¶àÍò´Î£¬È»ºóÖðÌõд3000¶àÍòÌõ¼Ç¼£¬Ð§ÂÊ¿ÉÏë¶øÖªÁË¡£ºóÀ´Ê¹ÓöàÏß³ÌÀ´ÊµÐÐͬ²½£¬¿ÉÊDZ£´æµÄ¹ý³ÌÒÀÈ»ÓÃÊ®¼¸·ÖÖÓ¡£Õâ¸ö¹ý³ÌÊDz»ÄÜÈÃÈ˽ÓÊܵġ£
Ö®ºóÓÅ»¯ÐÔÄܵŤ×÷תµ½ÁËÎÒµ¼Ê¦µÄÊÖÖС£ÎÒµ¼Ê¦µÄְλÊÇϵͳ¼Ü¹¹Ê¦£¬Äê½üËÄÊ®£¬¿ÉÒÔ˵ÊÇjavaÔªÀϼ¶ÈËÎïÁË¡£Á½ÌìÖ®ºó¹ûÈ»´«³öÕñ·ÜÈËÐĵÄÏûÏ¢¡£±£´æ¹¤×÷´ÓÔÀ´µÄºÄʱ10¶à·ÖÖÓ±ä³ÉÁË°ë·ÖÖÓ¡£ÕæÊÇ´óϲ°¡£¡
½ñÔçÀ´Éϰ࣬µÚÒ»¼þʾÍÊÇ¿´¿´ÊÇʲôʵÏֵġ£µÚÒ»¾ÍÊDz»Ê¹Óù«Ë¾·â×°ºÃµÄÊý¾Ý¿âÁ¬½Ó£¬²»ÊÊÓÃHQL¶øÊÇsqlÅúÁ¿±£´æ¡£Õâ¸öµ±È»ÄÜÌá¸ßºÜ´óµÄÐÔÄÜÁË¡£Ò²Åå·þµ¼Ê¦Ö®»¨ÁËÁ½ÌìµÄʱ¼ä°ÑÎÒÊ®ÌìµÄд³öÀ´µÄÂß¼ÖØÐÂÕûÀíÁËÒ»±é£¬ÕæÊDz»¼òµ¥°¡£¡×îºó¿´µ½Ò»ÌõSQLÈçÏ£ºselect * from um_org_organization_info start with ouguid=? connect by prior ouguid=parentouguid¡£¿ªÊ¼¾õµÃºÜÒÉ»ó£¬ÒÔÎÒ֮ǰËùѧϰµÄSQ֪ʶ£¬²»Ôø¿´¼û¹ýstart with ...connect by prior,ÒÉ»óÖ®¼ÊÉÏÍø¿´¿´¡£µÃÖª“Start with...Connect By prior ×Ó¾äµÝ¹é²éѯһ°ãÓÃÓÚÒ»¸ö±íά»¤Ê÷ÐνṹµÄÓ¦ÓÃ" ¡£ÖÕÓÚ´óÎòÁË¡£¸ßÊÖ¾ÍÊDz»Ò»Ñù£¬¾Ñé¶à¼ûʶ¹ã°¡¡£½ñÌìÓÖѧÁËÐÂ֪ʶÁË¡£ºÙºÙ£¡
Ïà¹ØÎĵµ£º
create table emp2_log(
uname varchar2(20),
action varchar(10),
atime date
);
create or replace trigger trig
after insert or delete or update on emp2 for each row //°óÔÚÒ»ÕűíÉÏ,before after ¶¼¿É
ÒÔ,beforeÊÇ ²åÊý¾Ý֮ǰ£¬afterÊÇÖ®ºó
begin
&nb ......
·ÖÎöº¯ÊýÊÇoracle816ÒýÈëµÄÒ»¸öȫеĸÅÄî,ΪÎÒÃÇ·ÖÎöÊý¾ÝÌṩÁËÒ»ÖÖ¼òµ¥¸ßЧµÄ´¦Àí·½Ê½.ÔÚ·ÖÎöº¯Êý³öÏÖÒÔÇ°,ÎÒÃDZØÐëʹÓÃ×ÔÁª²éѯ,×Ó²éѯ»òÕßÄÚÁªÊÓͼ,ÉõÖÁ¸´ÔӵĴ洢¹ý³ÌʵÏÖµÄÓï¾ä,ÏÖÔÚÖ»ÒªÒ»Ìõ¼òµ¥µÄsqlÓï¾ä¾Í¿ÉÒÔʵÏÖÁË,¶øÇÒÔÚÖ´ÐÐЧÂÊ·½ÃæÒ²ÓÐÏ൱´óµÄÌá¸ß.
ÏÂÃæÖ÷Òª½éÉÜÒ»ÏÂÒÔϼ¸¸öº¯ÊýµÄʹÓà ......
¡¾ÏÈ˼¿¼Ò»¸öÎÊÌ⣺Áû×ÓºÍϹ×ÓÔÚÒ»Æð»áÓÐʲô½á¹û£¿¡¿
×òÌìд³ÌÐò£¬ÓиöСģ¿éÊÇͨ¹ý·ÖÎöÊýÊ®ÍòÌõ¼Ç¼£¬ÓëÒì¹¹µÄÊý¾Ý±í½øÐбȶԣ¬ÕÒ³ö·ûºÏÌõ¼þµÄÐÅÏ¢¡£
ÔÚµÚÒ»Ìõ²éѯÖУ¬ÐèÒª²éѯËùÓмǼµÄÒ»¸ö×ֶΣ¬²¢ÌáÈ¡¸Ã×Ö¶ÎÖеĵÚ5ÖÁµÚ10¸ö×Ö·û´®£¬ÒªÓëÒì¹¹Êý¾Ý±í±È½ÏµÄ¾ÍÊÇÕâ¸ö×Ö´®¡£ÔÚ×î³õµÄ°æ±¾ÖУ¬¶ÔÓÚÐÔÄܲ¢Ã»ÓÐ×ö¸ü¶àµÄ¿¼Â ......
1¡¢²éÕÒ±íÖжàÓàµÄÖظ´¼Ç¼£¬Öظ´¼Ç¼ÊǸù¾Ýµ¥¸ö×ֶΣ¨peopleId£©À´ÅжÏ
select * from people
where peopleId in (select peopleId from people group by peopleId having count(peopleId) > 1)
2¡¢É¾³ý±íÖжàÓàµÄÖظ´¼Ç¼£¬Öظ´¼Ç¼ÊǸù¾Ýµ¥¸ö× ......
(1)SEQNAME.NEXTVALÀïÃæµÄÖµÈçºÎ¶Á³öÀ´£¿¿ÉÒÔÖ±½ÓÔÚinsert into test values(SEQNAME.NEXTVAL) ÊÇ¿ÉÒÔÓà ÕâÑù£º
SELECT tmp#_seq.NEXTVAL
INTO id_temp
from DUAL; È»ºó¿ÉÒ ......