OracleÖÐstart with¡connect by prior×Ó¾äÓ÷¨
OracleÖÐstart with…connect by prior×Ó¾äÓ÷¨
connect by Êǽṹ»¯²éѯÖÐÓõ½µÄ£¬Æä»ù±¾Óï·¨ÊÇ£º
select … from tablename
start with Ìõ¼þ1
connect by Ìõ¼þ2
where Ìõ¼þ3;
Àý£º
select * from table
start with org_id = ‘HBHqfWGWPy’
connect by prior org_id = parent_id;
¼òµ¥ËµÀ´Êǽ«Ò»¸öÊ÷×´½á¹¹´æ´¢ÔÚÒ»ÕűíÀ±ÈÈçÒ»¸ö±íÖдæÔÚÁ½¸ö×Ö¶Î:
org_id£¬parent_idÄÇôͨ¹ý±íʾÿһÌõ¼Ç¼µÄparentÊÇË£¬¾Í¿ÉÒÔÐγÉÒ»¸öÊ÷×´½á¹¹¡£
ÓÃÉÏÊöÓï·¨µÄ²éѯ¿ÉÒÔÈ¡µÃÕâ¿ÃÊ÷µÄËùÓмǼ¡£
ÆäÖУº
Ìõ¼þ1 ÊǸù½áµãµÄÏÞ¶¨Óï¾ä£¬µ±È»¿ÉÒÔ·Å¿íÏÞ¶¨Ìõ¼þ£¬ÒÔÈ¡µÃ¶à¸ö¸ù½áµã£¬Êµ¼Ê¾ÍÊǶà¿ÃÊ÷¡£
Ìõ¼þ2 ÊÇÁ¬½ÓÌõ¼þ£¬ÆäÖÐÓÃPRIOR±íʾÉÏÒ»Ìõ¼Ç¼£¬±ÈÈç CONNECT BY PRIOR org_id = parent_id£»¾ÍÊÇ˵ÉÏÒ»Ìõ¼Ç¼µÄorg_id ÊDZ¾Ìõ¼Ç¼µÄparent_id£¬¼´±¾¼Ç¼µÄ¸¸Ç×ÊÇÉÏÒ»Ìõ¼Ç¼¡£
Ìõ¼þ3 ÊǹýÂËÌõ¼þ£¬ÓÃÓÚ¶Ô·µ»ØµÄËùÓмǼ½øÐйýÂË¡£
¼òµ¥½éÉÜÈçÏ£º
ÔÚɨÃèÊ÷½á¹¹±íʱ£¬ÐèÒªÒÀ´Ë·ÃÎÊÊ÷½á¹¹µÄÿ¸ö½Úµã£¬Ò»¸ö½ÚµãÖ»ÄÜ·ÃÎÊÒ»´Î£¬Æä·ÃÎʵIJ½ÖèÈçÏ£º
µÚÒ»²½£º´Ó¸ù½Úµã¿ªÊ¼£»
µÚ¶þ²½£º·ÃÎʸýڵ㣻
µÚÈý²½£ºÅжϸýڵãÓÐÎÞδ±»·ÃÎʵÄ×ӽڵ㣬ÈôÓУ¬ÔòתÏòËü×î×ó²àµÄδ±»·ÃÎʵÄ×Ó½Ú£¬²¢Ö´Ðеڶþ²½£¬·ñÔòÖ´ÐеÚËIJ½£»
µÚËIJ½£ºÈô¸Ã½ÚµãΪ¸ù½Úµã£¬Ôò·ÃÎÊÍê±Ï£¬·ñÔòÖ´ÐеÚÎå²½£»
µÚÎå²½£º·µ»Øµ½¸Ã½ÚµãµÄ¸¸½Úµã£¬²¢Ö´ÐеÚÈý²½Öè¡£
×ÜÖ®£ºÉ¨ÃèÕû¸öÊ÷½á¹¹µÄ¹ý³ÌÒ²¼´ÊÇÖÐÐò±éÀúÊ÷µÄ¹ý³Ì¡£
1£®Ê÷½á¹¹µÄÃèÊö
Ê÷½á¹¹µÄÊý¾Ý´æ·ÅÔÚ±íÖУ¬Êý¾ÝÖ®¼äµÄ²ã´Î¹Øϵ¼´¸¸×Ó¹Øϵ£¬Í¨¹ý±íÖеÄÁÐÓëÁмäµÄ¹ØϵÀ´ÃèÊö£¬ÈçEMP±íÖеÄEMPNOºÍMGR¡£EMPNO±íʾ¸Ã¹ÍÔ±µÄ±àºÅ£¬MGR±íʾÁìµ¼¸Ã¹ÍÔ±µÄÈ˵ıàºÅ£¬¼´×Ó½ÚµãµÄMGRÖµµÈÓÚ¸¸½ÚµãµÄEMPNOÖµ¡£ÔÚ±íµÄÿһÐÐÖж¼ÓÐÒ»¸ö±íʾ¸¸½ÚµãµÄMGR£¨³ý¸ù½ÚµãÍ⣩£¬Í¨¹ýÿ¸ö½ÚµãµÄ¸¸½Úµã£¬¾Í¿ÉÒÔÈ·¶¨Õû¸öÊ÷½á¹¹¡£
ÔÚSELECTÃüÁîÖÐʹÓÃCONNECT BY ºÍSTART WITH ×Ó¾ä¿ÉÒÔ²éѯ±íÖеÄÊ÷Ðͽṹ¹Øϵ¡£ÆäÃüÁî¸ñʽÈçÏ£º
SELECT . . .
CONNECT BY {PRIOR ÁÐÃû1=ÁÐÃû2|ÁÐÃû1=PRIOR ÁÑÃû2}
[START WITH]£»
ÆäÖУºCONNECT BY×Ó¾ä˵Ã÷ÿÐÐÊý¾Ý½«ÊÇ°´²ã´Î˳Ðò¼ìË÷£¬²¢¹æ¶¨½«±íÖеÄÊý¾ÝÁ¬ÈëÊ÷ÐͽṹµÄ¹ØϵÖС£PRIORÔËËã·û±ØÐë·ÅÖÃÔÚÁ¬½Ó¹ØϵµÄÁ½ÁÐÖÐijһ¸öµÄÇ°Ãæ¡£¶ÔÓÚ½Úµã¼äµÄ¸¸×Ó¹Øϵ£¬PRIORÔËËã·ûÔÚÒ»²à±íʾ¸¸½Úµã£¬ÔÚÁíÒ»²à±íʾ×Ó½Úµã£
Ïà¹ØÎĵµ£º
ÔÚÇ°ÃæѧϰOracleÊý¾Ý¿â»ù´¡¼Ü¹¹Ê±£¬ÒѾÁ˽âÁËOracleµÄ´æ´¢½á¹¹£¬Âß¼ÉÏ£¬OracleµÄÊý¾Ý´æ·ÅÔÚtablespacesÖУ¬ÎïÀíÉÏ´æ·ÅÔÚdatafilesÖС£Ò»¸ötablespaceÖ»ÄÜÊôÓÚÒ»¸öÊý¾Ý¿â(Ò»¸öÊý¾Ý¿â¿É°üÀ¨¶à¸ötablespace)£¬°üÀ¨ÁË1¸ö»ò¶à¸öÊý¾ÝÎļþ¡£Tablespace¿É½øÒ»²½·ÖΪsegments¡¢extentsºÍblocks¡£Ò»¸ödatafileÖ»ÊôÓÚÒ»¸öÊý¾Ý¿âµÄÒ» ......
×î½üÓöµ½ÔÚwindows 2008ÏÂIEÆäËüµÄÍøÒ³¶¼¿ÉÒÔ·ÃÎÊ,ΨÓÐoracle.com²»ÄÜ·ÃÎÊ,ÕæÓôÃÆÁË,ºóÀ´ÕÒÒ»´úÀí,·ÃÎÊËٶȻ¹¿ÉÒÔ,ÅäÖÃÈçÏÂ:
Õâ¸ö´úÀíµÚÒ»´Î·ÃÎʵĻ°£¬ÐèÒªÌøµ½ËûµÄÖ÷Ò³£¬¹ý¸öÊ®¼¸ÃëÖоͿÉÒÔÌøתµ½OracleµÄ¹Ù·½ÍøÕ¾ÁË¡£
—————————————&md ......
´´½¨±í
create table person (pid int,name varchar2(20) unique,
age int check(age between 0 and 150),sex varchar(4) default 'ÄÐ',tp int,
address varchar(200),constraint person_pid_pk primary key(pid));
²åÈëÊý¾Ý
insert into person values(0011,'ÁõÈý½ã',25,'ÄÐ',13930123021,'ºÓ±±Ê¡ÇúÑôÏØ');
´´½¨×Ó±í ......
Ê×ÏÈÓÃÒ»¸öÃüÁÓèuserÓû§connect½ÇÉ«ºÍresource½ÇÉ«£º
grant connect,resource to user;
ÔËÐгɹ¦ºóÓû§°üÀ¨µÄȨÏÞ:
CONNECT½ÇÉ«£º --ÊÇÊÚÓè×îÖÕÓû§µÄµäÐÍȨÀû£¬×î»ù±¾µÄ
ALTER SESSION --Ð޸ĻỰ
CREATE CLUSTER --½¨Á¢¾Û´Ø
CREATE DATABASE LINK --½¨Á¢Êý¾Ý¿âÁ´½Ó
CREATE SEQUENCE --½¨Á¢ÐòÁÐ
CREAT ......