Óú¯ÊýʵÏÖ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 DBAÒ²°ë¸ö¶àѧÆÚÁË£¬½ñÌìÃÍÈ»²Å·¢ÏÖ£¬ÔÀ´ÎÒµÄÊ黹ÊǺÜеģ¬ÉϿβÙ×÷ʱºòÒ²Ö»ÊÇÖªµÀ´ó¸ÅÔõô×ö£¬µ«ÊÇÒªÕæµÄÈ«²¿×Ô¼º×ö£¬¶ø²»È¥·Ê黹ÊÇÓÐÒ»¶¨µÄÄѶȵģ¬ËùÒÔÄØ£¬½ñÌ쿪ʼ½«DBA´ÓÍ·¸´Ï°Ò»±é£¬Í¬Ê±ÔÙ²Ù×÷Ò»±é¡£
µÚÒ»Õ£¬Ñ§µÄÊÇOracleµÄÌåϵ½á¹¹£ ......
µ¼³ö. ÏëÒªµ¼³öÆäËûÓû§µÄ¶ÔÏóÐèÒªÓÐEXP_FULL_DATABASE½ÇÉ«
exp Óû§Ãû/ÃÜÂë file=·¾¶ tables=(±íÃû1,±íÃû2,...) µ¼Èë. ÏëÒªµ¼ÈëÆäËûÓû§µÄ¶ÔÏóÐèÒªÓÐIMP_FULL_DATABASE½ÇÉ«
imp Óû§Ãû/ÃÜÂë file=·¾¶ Êý¾Ý±Ãµ¼³ö
a.) Ö»Äܵ¼³öµ½Oracle¹Ü¿ØÄ¿Â¼. ¿ÉÒÔʹÓÃĬÈϵÄDATA_PUMP_DIR
b.) ²éѯ ......
¾³£Ê¹ÓÃoracle10g£¬ÎÒÃÇ¿ÉÒÔ·¢ÏÖÒÔǰɾ³ýµÄ±íÔÚÊý¾Ý¿âÖгöÏÖÁËÌØ±ð¶àµÄÀ¬»ø±í£¬ÈçÏÂÀý£º
¡¡¡¡BINjR8PK5HhrrgMK8KmgQ9nw==
¡¡¡¡ÕâÒ»ÀàµÄ±íͨ³£ÎÞ·¨É¾³ý£¬²¢ÇÒÎÞ·¨ÓÃ"delete"ɾ³ý,ÕâÖÖÇé¿öµÄ³öÏÖ£¬
¡¡¡¡Ò»°ã²»»áÓ°ÏìÕý³£µÄʹÓ㬵«ÊÇÓÐÓöµ½ÒÔϼ¸ÖÖÇé¿öʱÔò±ØÐëɾµôËü¡£
¡¡¡¡¡ô1.ÕâЩ±íÕ¼Óÿռä
¡¡¡¡¡ô2.Èç¹ûʹÓÃMiddle ......
È·ÈÏÉÁ»ØÆôÓÃÖÐ
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 ͬÒå´Ê synonym ΪÊý¾Ý¿â¶ÔÏó½¨Á¢Í¬Òå´Êºó,µ±dba¶ÔÊý¾Ý¿â¶ÔÏó(table,view,ÐòÁÐ)×öÁËÒ»¸ö¼òµ¥¸Ä¶¯Ö®ºó,ÈçÃû×Ö»òÕ߽ṹ¸Ä±ä,Ôò²»ÐèÒªÖØÐ¸ü¸Ä²¢±àÒëÓ¦ÓÃ.ÔÚÊý¾Ý¿â×ÖµäÖÐÓж¨Òå,²»ÐèÒª½øÐÐÈκδ洢.
oracle±ðÃûalias,With as¡¡Éú³ÉÒ»¸öÁÙʱ±í£¬¶ÔÓÚ¸´ÔÓµÄÊý¾Ý²éѯ£¬¿ÉÒÔ¼õÉÙ×ÊÔ´ÏûºÄ¡¢Ìá¸ßÐÔÄÜ¡£9iÒýÈë¡£
with alias ......