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

Óú¯ÊýʵÏÖoracleµÄsys_connect_by_path¹¦ÄÜ

1.½¨±í£¬²åÈëÊý¾Ý
create table dept(deptno number,deptname varchar2(20),mgrno number);
insert into dept values(1,'×ܹ«Ë¾',null);
insert into dept values(2,'Õã½­·Ö¹«Ë¾',1);
insert into dept values(3,'º¼ÖÝ·Ö¹«Ë¾',2);
insert into dept values(4,'ºþ±±·Ö¹«Ë¾',1);
insert into dept values(5,'Î人·Ö¹«Ë¾',4);
2.oracle²éѯÓï¾ä
select *,sys_connect_by_path(deptname,'/') deptname  from dept
  start with  mgrno is null connect by prior deptno=mgrno;
----------------------------------------------------------------------------------------
1        ×ܹ«Ë¾        <NULL>        /×ܹ«Ë¾       
2        Õã½­·Ö¹«Ë¾        1           /×ܹ«Ë¾/Õã½­·Ö¹«Ë¾       
3        º¼ÖÝ·Ö¹«Ë¾        2           /×ܹ«Ë¾/Õã½­·Ö¹«Ë¾/º¼ÖÝ·Ö¹«Ë¾     
4        ºþ±±·Ö¹«Ë¾        1           /×ܹ«Ë¾/ºþ±±·Ö¹«Ë¾
5        Î人·Ö¹«Ë¾        4          /×ܹ«Ë¾/ºþ±±·Ö¹«Ë¾/Î人·Ö¹«Ë¾
  
----------------------------------------------------------------------------------------
3.´´½¨º¯ÊýʵÏÖÀàËÆ¹¦ÄÜ
CREATE or replace FUNCTION Branch
(    vdeptname    varchar(200),
     vDelimiter   IN VARCHAR(10) DEFAULT '/')
   return varchar(4000)
as
c1 cursor;
temp varchar(4000);
vename varchar(200);
begin
 temp=vDelimiter;
 open c1 for select  deptname from dept start with deptname
  =vdeptname connect by  deptno= prior mgrno;
 loop
   FETCH


Ïà¹ØÎĵµ£º

oracle¸´Ï°£¨Ò»£© Ö®OracleÌåϵ½á¹¹

      ѧϰOracle DBAÒ²°ë¸ö¶àѧÆÚÁË£¬½ñÌìÃÍÈ»²Å·¢ÏÖ£¬Ô­À´ÎÒµÄÊ黹ÊǺÜеģ¬ÉϿβÙ×÷ʱºòÒ²Ö»ÊÇÖªµÀ´ó¸ÅÔõô×ö£¬µ«ÊÇÒªÕæµÄÈ«²¿×Ô¼º×ö£¬¶ø²»È¥·­Ê黹ÊÇÓÐÒ»¶¨µÄÄѶȵģ¬ËùÒÔÄØ£¬½ñÌ쿪ʼ½«DBA´ÓÍ·¸´Ï°Ò»±é£¬Í¬Ê±ÔÙ²Ù×÷Ò»±é¡£
      µÚÒ»Õ£¬Ñ§µÄÊÇOracleµÄÌåϵ½á¹¹£ ......

Oracle¼òµ¥Êý¾ÝÒÆ¶¯

µ¼³ö. ÏëÒªµ¼³öÆäËûÓû§µÄ¶ÔÏóÐèÒªÓÐEXP_FULL_DATABASE½ÇÉ«
exp Óû§Ãû/ÃÜÂë file=·¾¶ tables=(±íÃû1,±íÃû2,...) µ¼Èë. ÏëÒªµ¼ÈëÆäËûÓû§µÄ¶ÔÏóÐèÒªÓÐIMP_FULL_DATABASE½ÇÉ«
imp Óû§Ãû/ÃÜÂë file=·¾¶ Êý¾Ý±Ãµ¼³ö
a.) Ö»Äܵ¼³öµ½Oracle¹Ü¿ØÄ¿Â¼. ¿ÉÒÔʹÓÃĬÈϵÄDATA_PUMP_DIR
b.) ²éѯ ......

OracleÊý¾Ý¿â10gÀ¬»ø±íÇå³ý×îз½·¨

¾­³£Ê¹ÓÃoracle10g£¬ÎÒÃÇ¿ÉÒÔ·¢ÏÖÒÔǰɾ³ýµÄ±íÔÚÊý¾Ý¿âÖгöÏÖÁËÌØ±ð¶àµÄÀ¬»ø±í£¬ÈçÏÂÀý£º
¡¡¡¡BINjR8PK5HhrrgMK8KmgQ9nw==
¡¡¡¡ÕâÒ»ÀàµÄ±íͨ³£ÎÞ·¨É¾³ý£¬²¢ÇÒÎÞ·¨ÓÃ"delete"ɾ³ý,ÕâÖÖÇé¿öµÄ³öÏÖ£¬
¡¡¡¡Ò»°ã²»»áÓ°ÏìÕý³£µÄʹÓ㬵«ÊÇÓÐÓöµ½ÒÔϼ¸ÖÖÇé¿öʱÔò±ØÐëɾµôËü¡£
¡¡¡¡¡ô1.ÕâЩ±íÕ¼Óÿռä
¡¡¡¡¡ô2.Èç¹ûʹÓÃMiddle ......

Oracle³£ÓÃÉÁ»Ø²Ù×÷

È·ÈÏÉÁ»ØÆôÓÃÖÐ
SHOW PARAMETER RECYCLEBIN; ÆôÓÃÉÁ»Ø
ALTER SYSTEM SET RECYCLEBIN = ON; ÉÁ»ØDROPµÄ±í
FLASHBACK TABLE xxx TO BEFORE DROP; ³¹µ×Çå³ýDROPµÄ±í,½«²»ÄÜÔÙÉÁ»Ø.
PURGE TABLE xxx; Ö±½Ó³¹µ×DROPµô±í
DROP TABLE xxx PURGE; Çå¿ÕËùÓÐDROPµÄ±í
PURGE RE ......

oracle vs. SQL ͬÒå´Êsynonym ±ðÃû alias

oracle ͬÒå´Ê synonym ΪÊý¾Ý¿â¶ÔÏó½¨Á¢Í¬Òå´Êºó,µ±dba¶ÔÊý¾Ý¿â¶ÔÏó(table,view,ÐòÁÐ)×öÁËÒ»¸ö¼òµ¥¸Ä¶¯Ö®ºó,ÈçÃû×Ö»òÕ߽ṹ¸Ä±ä,Ôò²»ÐèÒªÖØÐ¸ü¸Ä²¢±àÒëÓ¦ÓÃ.ÔÚÊý¾Ý¿â×ÖµäÖÐÓж¨Òå,²»ÐèÒª½øÐÐÈκδ洢.
oracle±ðÃûalias,With as¡¡Éú³ÉÒ»¸öÁÙʱ±í£¬¶ÔÓÚ¸´ÔÓµÄÊý¾Ý²éѯ£¬¿ÉÒÔ¼õÉÙ×ÊÔ´ÏûºÄ¡¢Ìá¸ßÐÔÄÜ¡£9iÒýÈë¡£
with alias ......
© 2009 ej38.com All Rights Reserved. ¹ØÓÚE½¡ÍøÁªÏµÎÒÃÇ | Õ¾µãµØÍ¼ | ¸ÓICP±¸09004571ºÅ