OracleǧÍòÌõ¼Ç¼²åÈëÓë²éѯС½á
×î½ü×öÁ˸öÏîÄ¿£¬ÊµÏÖ¶Ô´æÔÚǧÍòÌõ¼Ç¼µÄ¿â±í½øÐвåÈë¡¢²éѯ²Ù×÷¡£ÔÒÔΪ¶ÔÊý¾Ý¿âµÄ²åÈë¡¢²éѯÊǼþºÜÈÝÒ×µÄÊ£¬¿É²»Öªµ±Êý¾Ý´ïµ½°ÙÍòÉõÖÁǧÍòÌõ¼¶±ðµÄʱºò£¬ÕâÒ»ÇÐËƺõ±äµÃÏ൱À§ÄÑ¡£¼¸¾ÕÛÌÚ£¬×ÜËãÍê³ÉÁËÈÎÎñ¡£
¡¡¡¡1¡¢±ÜÃâʹÓÃHibernate¿ò¼Ü
¡¡¡¡HibernateÓÃÆðÀ´ËäÈ»·½±ã£¬µ«¶ÔÓÚº£Á¿Êý¾ÝµÄ²Ù×÷ÏÔµÃÁ¦²»´ÓÐÄ¡£
¡¡¡¡¹ØÓÚ²åÈ룺
¡¡¡¡ÊÔ¹ýÓÃHibernateÒ»´ÎÐÔ½øÐÐ5ÍòÌõ×óÓÒÊý¾ÝµÄ²åÈ룬ÈôIDʹÓÃsequence·½Ê½Éú³É£¬Hibernate½«·Ö5Íò´Î´ÓÊý¾Ý¿âÈ¡µÃ5Íò¸ösequence£¬¹¹Ôì³ÉÏàÓ¦¶ÔÏóºó£¬ÔÙ·ÖÎåÍò´Î½«Êý¾Ý±£´æµ½Êý¾Ý¿â¡£»¨ÁËÎÒÊ®·ÖÖÓʱ¼ä¡£Ö÷ÒªµÄʱ¼ä²»ÊÇ»¨ÔÚ²åÈëÉÏ£¬¶øÊÇ»¨ÔÚ5Íò´Î´ÓÊý¾Ý¿âÈ¡sequenceÉÏ£¬ÅªµÃÎÒÏ൱ÓôÃÆ¡£ËäÈ»ºóÀ´°ÑIDÉú³É·½Ê½¸Ä³Éincrease½â¾öÁËÎÊÌ⣬µ«»¹ÊǶÔÄÇÊ®·ÖÖӵĵȴýÐÄÓÐÓà¼Â¡£
¡¡¡¡¹ØÓÚ²éѯ£º
¡¡¡¡Hibernate¶ÔÊý¾Ý¿â²éѯµÄÖ÷Ҫ˼Ï뻹ÊÇÃæÏò¶ÔÏóµÄ£¬Õ⽫ʹÐí¶àÎÒÃDz»ÐèÒª²éѯµÄÊý¾ÝÕ¼ÓÃÁË´óÁ¿µÄϵͳ×ÊÔ´£¨°üÀ¨Êý¾Ý¿â×ÊÔ´ºÍ±¾µØ×ÊÔ´£©¡£ÓÉÓÚ¶ÔHibernateµÄÆ«°®£¬±¾×Ų»Å×Æú¡¢²»·ÅÆúµÄ×÷·ç£¬×öÁË°üÀ¨ÅäSQL£¬¸Ä½øSQLµÈµÈµÄÏ൱¶àµÄ³¢ÊÔ£¬¿É¶¼ÒÔʧ°Ü¸æÖÕ£¬²»µÃ²»ÈÌÍ´¸î°®ÁË¡£
¡¡¡¡2¡¢Ð´²éѯÓï¾äʱ£¬Òª°Ñ²éѯµÄ×Ö¶ÎÒ»Ò»Áгö
¡¡¡¡²éѯʱ²»ÒªÊ¹ÓÃÀàËÆselect * from x_tableµÄÓï¾ä£¬Òª¾¡Á¿Ê¹ÓÃselect id,name from x_table£¬ÒÔ±ÜÃâ²éѯ³ö²»ÐèÒªµÄÊý¾ÝÀË·Ñ×ÊÔ´¡£¶ÔÓÚº£Á¿Êý¾Ý¶øÑÔ£¬Ò»¸ö×Ö¶ÎËùÕ¼ÓõÄ×ÊÔ´ºÍ²éѯʱ¼äÊÇÏ൱¿É¹ÛµÄ¡£
¡¡¡¡3¡¢¼õÉÙ²»±ØÒªµÄ²éѯÌõ¼þ
¡¡¡¡µ±ÎÒÃÇÔÚ×ö²éѯʱ£¬³£³£ÊÇǰ̨Ìá½»Ò»¸ö²éѯ±íµ¥µ½ºǫ́£¬ºǫ́½âÎöÕâ¸ö±íµ¥£¬¶øºó½øÐвéѯ²Ù×÷¡£ÔÚÎÒÃǽâÎö±íµ¥Ê±£¬ÎªÁË·½±ãÆð¼û£¬³£³£Ï²»¶½«Ò»Ð©²»ÐèÒª²éѯµÄÌõ¼þÓÃÓÀÕæµÄÌõ¼þÀ´´úÌ棨È磺select count(id) from x_table where name like ‘%’£©£¬ÆäʵÕâÑùµÄSQL¶Ô×ÊÔ´µÄÀË·ÑÊÇÏ൱¿Éŵġ£ÎÒÊÔ¹ý¶ÔÓÚͬÑùµÄ½üһǧÍòÌõ¼Ç¼µÄ²éѯÀ´Ëµ£¬Ê¹ÓÃselect count(id) from x_table ½øÐбí²éѯÐèÒª11Ã룬¶øʹÓÃselect count(id) from x_table where name like ‘%’È´»¨ÁË33Ãë¡£
¡¡¡¡4¡¢±ÜÃâÔÚ²éѯʱʹÓñíÁ¬½Ó
¡¡¡¡ÔÚ×öº£Á¿Êý¾Ý²éѯʱ£¬Ó¦¾¡Á¿±ÜÃâ±íÁ¬½Ó£¨ÌرðÊÇ×ó¡¢ÓÒÁ¬½Ó£©£¬Íò²»µÃÒÑÒª½øÐбíÁ¬½Óʱ£¬±»Á¬½ÓµÄÁíÒ»ÕűíÊý¾ÝÁ¿Ò»¶¨²»ÄÜÌ«´ó£¬ÈôÁ¬½ÓµÄÁíÒ»ÕűíÒ²ÊÇÊýÍòÌõµÄ»°£¬ÄǹÀ¼Æ¿ÉÒÔ¿¼ÂÇÖØÐÂÉè¼Æ¿â±íÁË£¬ÒòΪÄÇÐèÒªµÈ´ýµÄʱ¼ä¾ö²»ÊÇÕý³£Óû§ËùÄÜÈÌÊܵġ£
¡¡¡¡5¡¢Ç¶Ìײéѯʱ£¬¾¡¿ÉÄܵØÔÚµÚÒ»´Îselect¾Í°Ñ²éѯ·¶Î§Ëõµ½×îС
¡¡¡¡ÔÚÓÐ
Ïà¹ØÎĵµ£º
Oracle Connect ByÓ÷¨
oracleÖеÄselectÓï¾ä¿ÉÒÔÓÃSTART WITH...CONNECT BY PRIOR×Ó¾äʵÏֵݹé²éѯ£¬connect by Êǽṹ»¯²éѯÖÐÓõ½µÄ£¬Æä»ù±¾Óï·¨ÊÇ£º
select ... from <TableName>
where <Conditional-1>
start with <Conditional-2>
connect by <Conditional-3>
;
<Conditi ......
select * from (
select cw2.NO WHOLENO,sp1.ARGUMENTNAME TYPENAME,sp2.ARGUMENTNAME BRANDNAME,sp3.ARGUMENTNAME STYLENAME,sp4.ARGUMENTNAME DEPART,so.OPERATORNAME USERNAME
from COM_WHOLE_INFO cw2,SYS_OPERATOR so, SYS_PUBLICARGUMENT sp1, SYS_PUBLICARGUMEN ......
OracleÖеÄÐòÁкŹËÃû˼Òå¾ÍÊÇ´´½¨Ò»¸öÐòÁкţ¬¿ÉÒÔÔÚ²åÈë»òÕ߸üеÄʱºòµ÷Óã¬Ï൱ÓÚÊÇÒ»¸öÉú³ÉÆ÷
´´½¨Óï·¨£º
create sequence myse
increment by 1 --Ôö³¤¶È
start with
1 --´ÓÄÄÀïÔö¼Ó£¬¾ÍÊÇ˵ÏÂÒ»¸ö»ñÈ¡µÄÖµ´ÓÕâ¸öÖµ ......
ºÜ¶àÈ˶¼ÖªµÀ£¬OracleµÄ¼àÌýÆ÷Ò»Ö±´æÔÚ×ÅÒ»¸ö°²È«Òþ»¼£¬¼ÙÈç²»ÉèÖð²È«´ëÊ©£¬ÄÇôÄܹ»·ÃÎʵÄÓû§¾Í¿ÉÒÔÔ¶³Ì¹Ø±Õ¼àÌýÆ÷¡£
Ïà¹ØʾÀý£º
D:\>lsnrctl stop eygle
LSNRCTL for 32-bit Windows: Version 10.2.0.3.0 - Production on 28-11ÔÂ-2007 10:02:40
Copyright (c) 1991, 2006, Oracle. All rights reserved.
......
ÃÀ¹úPVH DB´æÔÚÐÐÁ´½Ó.ͨ¹ýÎҵļì²é,·¢ÏÖ,ÎÒÃǵÄDBͬÑù´æÔÚÐÐǨÒƺÍÐÐÁ´½Ó.ORACLEµÄdbms_stats.gather_schema_statsÖ»»áÊÕ¼¯ÓÅ»¯Æ÷ͳ¼ÆÐÅÏ¢,²»»á¼ì²â±íµÄ¼Ç¼ÊÇ·ñ´æÔÚÐÐǨÒƺÍÐÐÁ´½Ó.(ÎÒÔÏȱ»Õâ¸ö°ü¸øÆÁË,ËùÒÔÖ´ÐÐÍêdbms_stats.gather_schema_statsºóÒ»²éÊý¾Ý×Öµä:USER_TABLES,ÆäÖеÄ×Ö¶Îchain_cntֵȫΪ0).Òª¼ì²â±íµÄ¼Ç ......