Oracle ÖеÄÊ÷²éѯºÍ connect by
Oracle ÖеÄÊ÷²éѯºÍ connect by
ʹÓà connect by ºÍ start with À´½¨Á¢ÀàËÆÓÚÊ÷µÄ±¨±í²¢²»ÄÑ£¬Ö»Òª×ñÑÒÔÏ»ù±¾ÔÔò¼´¿É£º
ʹÓà connect by ʱ¸÷×Ó¾äµÄ˳ÐòӦΪ£º
select
from
where
start with
connect by
order by
prior ʹ±¨±íµÄ˳ÐòΪ´Ó¸ùµ½Ò¶£¨Èç¹û prior ÁÐÊǸ¸±²£©»ò´ÓÒ¶µ½¸ù£¨Èç¹û prior ÁÐÊǺó´ú£©¡£
where ×Ó¾ä¿ÉÒÔ´ÓÊ÷ÖÐÅųý¸öÌ壬µ«²»ÅųýËüÃǵÄ×ÓË»òÕß׿ÏÈ£¬Èç¹û prior ÁÐÊǺó´ú£©¡£
connect by ÖеÄÌõ¼þ£¨ÓÈÆäÊDz»µÈÓÚ£©Ïû³ý¸öÌåºÍËüËùÓеÄ×ÓË»ò׿ÏÈ£¬ÒÀÀµÓÚÔõÑù¸ú×ÙÊ÷£©¡£
connect by ²»ÄÜÓë where ×Ó¾äÖеıíÁ¬½ÓÔÚÒ»ÆðʹÓá£
ÏÂÃæÊǼ¸¸öÀý×Ó
1. ´Ó¸ùµ½Ò¶±éÀú
SELECT n_parendid, n_name, (LEVEL - 1), n_id
from navigation
WHERE n_parendid IS NOT NULL
START WITH n_id = 0
CONNECT BY n_parendid = PRIOR n_id;
2. ´ÓÒ¶µ½¸ù±éÀú
SELECT n_parendid, n_name, (LEVEL - 1), n_id
from navigation
WHERE n_parendid IS NOT NULL
START WITH n_id = 300
CONNECT BY n_id = PRIOR n_parendid;
3. Åųý¸öÌ壬µ«²»ÅųýËüÃǵÄ×ÓËï
SELECT n_parendid, n_name, (LEVEL - 1), n_id
from navigation
WHERE n_parendid IS NOT NULL AND n_id != 2
START WITH n_id = 0
CONNECT BY n_parendid = PRIOR n_id;
4. Ïû³ý¸öÌåºÍËüËùÓеÄ×ÓËï
SELECT n_parendid, n_name, (LEVEL - 1), n_id
from navigation
WHERE n_parendid IS NOT NULL
START WITH n_id = 0
CONNECT BY n_parendid = PRIOR n_id AND n_id != 2;
5. ¸Ä±äÏÔʾ˳Ðò
SELECT n_parendid, n_name, (LEVEL - 1), n_id
from navigation
WHERE n_parendid IS NOT NULL
START WITH n_id = 0
CONNECT BY n_parendid = PRIOR n_id
ORDER BY n_viewnum DESC;
±¾ÎÄת×Ôcsdn:http://blog.csdn.net/wzy0623/archive/2007/06/18/1656345.aspx
Ïà¹ØÎĵµ£º
ǰЩÈÕ×Ó£¬Êý¾Ý¿â¿Õ¼ä±¬Âú£¬ÒѾÔö³¤µ½´æ´¢¿Õ¼äµ¥¸ö´æ´¢ÎļþµÄ×î´óÖµ32G¡£µ«ÊÇ£¬²ÉÓÃÁ˺ܶà°ì·¨²ÅÊͷŵô±í¿Õ¼ä£¬Ö÷ÒªÊÇϵͳÖдóÁ¿Ê¹Ó÷ÖÇø±í£¬¶øÕë¶Ô·ÖÇø±íÇå³ýÊý¾Ý£¬²»»áÊͷűí¿Õ¼ä£¬±ØÐë°Ñ·ÖÇødropµô£¬²Å»áÊͷſռ䡣¼Ç¼һϵ±Ê±²Ù×÷ʱѧϰºÍʹÓõÄһЩÓï¾ä£º
Ò»¡¢dr ......
oracle´Ó9i¿ªÊ¼Ö§³ÖÒ»ÖÖеÄÊý¾ÝÀàÐÍ---- xmltype£¬ÓÃÓÚ´æ´¢ºÍ¹ÜÀíxmlÊý¾Ý£¬²¢ÌṩÁ˺ܶàµÄfunctions£¬ÓÃÀ´Ö±½Ó¶ÁÈ¡xmlÎĵµºÍ¹ÜÀí½Úµã¡£ÏÂÃæ½«½éÉÜxmltypeµÄһЩ»ù±¾Ê¹Óá£
1.½¨Á¢º¬ÓÐxmltypeÊý¾ÝÀàÐ͵ıí
create table abc (id number,xmldoc sys.xmltype);
ÉùÃ÷xmltypeÐÍ×Ö¶ÎÓÃ:sys.xmltype
2.Ïò´øÓÐxmltypeÀàÐ͵ıí ......
Basic Steps for Manual Online Reorganization Commands and procedures used:
1.DBMS_REDEFINITION.CAN_REDEF_TABLE
2.CREATE TABLE …
3.DBMS_REDEFINITION.START_REDEF_TABLE
4.DBMS_REDEFINITION.COPY_TABLE_DEPENDENTS and DBMS_REDEFINITION.CONS_ORIG_PAGRAMS
SELECT object_name,base_table_name, ......
[¾«»ª] ORACLEµÄ¼¸ÖÖÆô¶¯·½Ê½
http://www.chinaunix.net ×÷Õß:wuwenlong ·¢±íÓÚ£º2003-07-27 16:58:57
¡¾·¢±íÆÀÂÛ¡¿ ¡¾²é¿´ÔÎÄ¡¿ ¡¾OracleÌÖÂÛÇø¡¿¡¾¹Ø±Õ¡¿
1¡¢startup nomount
·Ç°²×°Æô¶¯£¬ÕâÖÖ·½Ê½Æô¶¯Ï¿ÉÖ´ÐУºÖؽ¨¿ØÖÆÎļþ¡¢Öؽ¨Êý¾Ý¿â
¶ÁÈ¡ ......
ORACLEÊǶàÓû§ÏµÍ³£¬ËüÔÊÐíÐí¶àÓû§¹²Ïíϵͳ×ÊÔ´¡£ÎªÁ˱£Ö¤Êý¾Ý¿âϵͳµÄ°²È«£¬Êý¾Ý¿â¹ÜÀíϵͳÅäÖÃÁËÁ¼ºÃµÄ°²È«»úÖÆ¡£
2. 1 ORACLEÊý¾Ý¿â°²È«²ßÂÔ
½¨Á¢ÏµÍ³¼¶µÄ°²È«±£Ö¤
ϵͳ¼¶ÌØÈ¨ÊÇͨ¹ýÊÚÓèÓ ......