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

Oracle start with ... connect by prior Ó÷¨

 Óï·¨£º
select *
from ±íÃû
where Ìõ¼þ1
start with Ìõ¼þ2
connect by prior µ±Ç°±í×Ö¶Î=¼¶Áª±í×Ö¶Î
start withÓëconnect by priorÓï¾äÍê³ÉµÝ¹é¼Ç¼£¬ÐγÉÒ»¿ÃÊ÷Ðνṹ£¬Í¨³£¿ÉÒÔÔÚ¾ßÓвã´Î½á¹¹µÄ±íÖÐʹÓá£
start with±íʾ¿ªÊ¼µÄ¼Ç¼
connect by prior Ö¸¶¨Ó뵱ǰ¼Ç¼¹ØÁªÊ±µÄ×ֶιØÏµ
´úÂ룺
--´´½¨²¿ÃÅ±í£¬ÕâÊÇÒ»¸ö¾ßÓвã´Î½á¹¹µÄ±í£¬×ӼǼͨ¹ýparent_idÓ븸¼Ç¼µÄid½øÐйØÁª
create table DEPT(
ID NUMBER(9) PRIMARY KEY, --²¿ÃÅID
NAME VARCHAR2(100), --²¿ÃÅÃû³Æ
PARENT_ID NUMBER(9) --¸¸¼¶²¿ÃÅID£¬Í¨¹ý´Ë×Ö¶ÎÓëÉϼ¶²¿ÃŹØÁª
);
Ïò±íÖвåÈëÈçÏÂÊý¾Ý£¬ÎªÁËʹ´úÂë¼òµ¥£¬Ò»¸ö²¿ÃŽö¾ßÓÐÒ»¸öϼ¶²¿ÃÅ
¡ñ´Ó¸ù½Úµã¿ªÊ¼²éѯµÝ¹éµÄ¼Ç¼
select *
from dept
start with id=1
connect by prior id = parent_id;
ÏÂÃæÊDzéѯ½á¹û£¬start with id=1±íʾ´Óid=1µÄ¼Ç¼¿ªÊ¼²éѯ£¬ÏòÒ¶×ӵķ½ÏòµÝ¹é£¬µÝ¹éÌõ¼þÊÇid=parent_id£¬µ±Ç°¼Ç¼µÄidµÈÓÚ×ӼǼµÄparent_id
¡ñ´ÓÒ¶×ӽڵ㿪ʼ²éѯµÝ¹éµÄ¼Ç¼
select *
from dept
start with id=5
connect by prior parent_id = id;
ÏÂÃæÊDzéѯ½á¹û£¬µÝ¹éÌõ¼þ°´ÕÕµ±Ç°¼Ç¼µÄparent_idµÈÓ븸¼Ç¼µÄid
¡ñ¶Ô²éѯ½á¹û¹ýÂË
select *
from dept
where name like '%ÏúÊÛ%'
start with id=1
connect by prior id = parent_id;
ÔÚÏÂÃæµÄ²éѯ½á¹ûÖпÉÒÔ¿´µ½£¬Ê×ÏÈʹÓÃstart with... connect by prior²éѯ³öÊ÷ÐεĽṹ£¬È»ºówhereÌõ¼þ²ÅÉúЧ£¬¶ÔÈ«²¿²éѯ½á¹û½øÐйýÂË
¡ñpriorµÄ×÷ÓÃ
prior¹Ø¼ü×Ö±íʾ²»½øÐеݹé²éѯ£¬½ö²éѯ³öÂú×ãid=1µÄ¼Ç¼£¬ÏÂÃæÊǽ«µÚÒ»¸ö²éѯȥµôprior¹Ø¼ü×Öºó½á¹û
select *
from dept
start with id=1
connect by prior id = parent_id;


Ïà¹ØÎĵµ£º

Oracle job ¹ÜÀí£¨×ªÔØ£©


Oracle job ¹ÜÀí
 
 
SVRMGR> select * from dba_jobs;
³õʼ»¯Ïà¹Ø²ÎÊýjob_queue_processes
alter system set job_queue_processes=39 scope=spfile;//×î´óÖµ²»Äܳ¬¹ý1000  ;job_queue_interval = 10  //µ÷¶È×÷ҵˢÐÂÆµÂÊÃëΪµ¥Î»
DBA_JOBS describes all jobs in the database.
USER_JO ......

oracle±È½Ï¿ìµÄ·ÖÒ³sql

 ·½°¸1 ÊÊÓÃÓÚoracle9iÒÔÉÏ£¡
select * from
(select row_number() over(order by sendid desc) rn,m.* from xxt_msgreceive m )
where rn <1010 and rn>=1000
·½°¸2
SELECT * from (SELECT A.*, ROWNUM RN from (SELECT * from xxt_msg where sendstatus=1  order by msgid desc) A WHERE ROWNUM < ......

[·ÖÏí]OracleÊý¾Ýµ¼Èëµ¼³öimp/expÃüÁî

OracleÊý¾Ýµ¼Èëµ¼³öimp/expÃüÁî
    Oracle Êý¾Ýµ¼Èëµ¼³öimp/exp¾ÍÏ൱ÓÚoracleÊý¾Ý»¹Ô­Ó뱸·Ý¡£expÃüÁî¿ÉÒÔ°ÑÊý¾Ý´ÓÔ¶³ÌÊý¾Ý¿â·þÎñÆ÷µ¼³öµ½±¾µØµÄdmpÎļþ£¬impÃüÁî¿ÉÒÔ°Ñ dmpÎļþ´Ó±¾µØµ¼Èëµ½Ô¶´¦µÄÊý¾Ý¿â·þÎñÆ÷ÖС£ ÀûÓÃÕâ¸ö¹¦ÄÜ¿ÉÒÔ¹¹½¨Á½¸öÏàͬµÄÊý¾Ý¿â£¬Ò»¸öÓÃÀ´²âÊÔ£¬Ò»¸öÓÃÀ´ÕýʽʹÓá£
Ö´Ðл ......

Oracle ÀÛ¼ÓºÍʵÏÖ

============================Question================================
SQL> select * from emp;
     EMPID EMPNAME                  SALARY     DEPTID
---------- ----------- ......
© 2009 ej38.com All Rights Reserved. ¹ØÓÚE½¡ÍøÁªÏµÎÒÃÇ | Õ¾µãµØÍ¼ | ¸ÓICP±¸09004571ºÅ