Oracle ×ÔÔö³¤×Ö¶Î
½¨SEQUENCEÐòÁÐ
CREATE SEQUENCE ÐòÁÐÃû×Ö£¨ÀýÈ磺"URLIMAGE_SEQ"£© MINVALUE 1 MAXVALUE 999999999999999999999999999 INCREMENT BY 1 START WITH 101 CACHE 10 NOORDER NOCYCLE ;
½¨trigger ´¥·¢Æ÷
create or replace trigger ´¥·¢Æ÷Ãû×Ö£¨ÀýÈ磺URLIMAGE_TRI £©
before insert
on Ó³ÉäµÄ±íÃû£¨ÀýÈ磺URLIMAGE £©
for each row
begin
select ¸Õ½¨µÄÐòÁÐÃû£¨ÀýÈ磺URLIMAGE_seq£©.nextval into :new.Ôö³¤×Ö¶ÎÃû£¨ÀýÈ磺PICID£© from dual;
end;
ͨ¹ý´´½¨ÐòÁÐÀ´ÊµÏÖ
ORACLE SEQUENCEµÄ¼òµ¥½éÉÜ
ÔÚoracleÖÐsequence¾ÍÊÇËùνµÄÐòÁкţ¬Ã¿´ÎÈ¡µÄʱºòËü»á×Ô¶¯Ôö¼Ó£¬Ò»°ãÓÃÔÚÐèÒª°´ÐòÁкÅÅÅÐòµÄµØ·½¡£
1¡¢Create Sequence
ÄãÊ×ÏÈÒªÓÐCREATE SEQUENCE»òÕßCREATE ANY SEQUENCEȨÏÞ£¬
CREATE SEQUENCE emp_sequence
INCREMENT BY 1 -- ÿ´Î¼Ó¼¸¸ö
START WITH 1 -- ´Ó1¿ªÊ¼¼ÆÊý
NOMAXVALUE -- ²»ÉèÖÃ×î´óÖµ
NOCYCLE -- Ò»Ö±ÀÛ¼Ó£¬²»Ñ»·
CACHE 10;
Ò»µ©¶¨ÒåÁËemp_sequence£¬Äã¾Í¿ÉÒÔÓÃCURRVAL£¬NEXTVAL
CURRVAL=·µ»Ø sequenceµÄµ±Ç°Öµ
NEXTVAL=Ôö¼ÓsequenceµÄÖµ£¬È»ºó·µ»Ø sequence Öµ
±ÈÈ磺
emp_sequence.CURRVAL
emp_sequence.NEXTVAL
¿ÉÒÔʹÓÃsequenceµÄµØ·½£º
- ²»°üº¬×Ó²éѯ¡¢snapshot¡¢VIEWµÄ SELECT Óï¾ä
- INSERTÓï¾äµÄ×Ó²éѯÖÐ
- NSERTÓï¾äµÄVALUESÖÐ
- UPDATE µÄ SETÖÐ
¿ÉÒÔ¿´ÈçÏÂÀý×Ó£º
INSERT INTO emp VALUES
(empseq.nextval, 'LEWIS', 'CLERK',7902, SYSDATE, 1200, NULL, 20);
SELECT empseq.currval from DUAL;
µ«ÊÇҪעÒâµÄÊÇ£º
- µÚÒ»´ÎNEXTVAL·µ»ØµÄÊdzõʼֵ£»ËæºóµÄNEXTVAL»á×Ô¶¯Ôö¼ÓÄ㶨ÒåµÄINCREMENT BYÖµ£¬È»ºó·µ»ØÔö¼ÓºóµÄÖµ¡£CURRVAL ×ÜÊÇ·µ»Øµ±Ç°SEQUENCEµÄÖµ£¬µ«ÊÇÔÚµÚÒ»´ÎNEXTVAL³õʼ»¯Ö®ºó²ÅÄÜʹÓÃCURRVAL£¬·ñÔò»á³ö´í¡£Ò»´ÎNEXTVAL»áÔö¼ÓÒ»´ÎSEQUENCEµÄÖµ£¬ËùÒÔÈç¹ûÄãÔÚͬһ¸öÓï¾äÀïÃæʹÓöà¸öNEXTVAL£¬ÆäÖµ¾ÍÊDz»Ò»ÑùµÄ¡£Ã÷°×£¿
- Èç¹ûÖ¸¶¨CACHEÖµ£¬ORACLE¾Í¿ÉÒÔÔ¤ÏÈÔÚÄÚ´æÀïÃæ·ÅÖÃһЩsequence£¬ÕâÑù´æÈ¡µÄ¿ìЩ¡£cacheÀïÃæµÄÈ¡Íêºó£¬oracle×Ô¶¯ÔÙÈ¡Ò»×éµ½cache¡£ ʹÓÃcache»òÐí»áÌøºÅ£¬ ±ÈÈçÊý¾Ý¿âͻȻ²»Õý³£downµô£¨shutdown abort),cacheÖеÄsequence¾Í»á¶ªÊ§. ËùÒÔ¿ÉÒÔÔÚcreate sequenceµÄʱºòÓÃnocache·ÀÖ¹ÕâÖÖÇé¿ö¡£
2¡¢Alter Sequence
Äã»ò
Ïà¹ØÎĵµ£º
---´´½¨±í¿Õ¼ä
create tablespace ±í¿Õ¼äÃû×Ö datafile 'F:\oracle\product\10.2.0\oradata\wsdata\yss01.dbf' size 4096M;
alter tablespace ±í¿Õ¼äÃû×Ö add datafile 'F:\oracle\product\10.2.0\oradata\wsdata\yss02.dbf' size 4096M;
alter tablespace ±í¿Õ¼äÃû×Ö add datafile 'F:\oracle\product\10.2.0\oradata\w ......
ÔÚOracleÖÐÒ»¸öÊÂÎñÊÇÓÉÒ»¸ö¿ÉÖ´ÐеÄSQLÓï¾ä¿ªÊ¼£¬Ò»¸ö¿ÉÖ´ÐÐSQLÓï¾ä²úÉú¶ÔʵÀýµÄµ÷Óá£ÔÚÊÂÎñ¿ªÊ¼Ê±£¬±»¸³¸øÒ»¸ö¿ÉÓûعö¶Î£¬¼Ç¼¸ÃÊÂÎñµÄ»Ø¹öÏî¡£Ò»¸öÊÂÎñÒÔÏÂÁÐÈκÎÒ»¸ö³öÏÖ¶ø½áÊø¡£
¡ôµ±COMMIT»òROLLBACK£¨Ã»ÓÐSAVEPOINT×Ӿ䣩Óï¾ä·¢³ö¡£
¡ôÒ»¸öDDLÓï¾ä±»Ö´ÐС£ÔÚDDLÓï¾äÖ´ÐÐÇ°¡¢ºó¶¼ÒþʽµØÌá½»¡£
¡ôÓû§³·Ïû¶ÔOra ......
OracleÈ«ÎÄË÷ÒýµÄ»ù±¾ÖªÊ¶
Ò»¡¢ÀúÊ·±³¾°
OracleÊý¾Ý¿âµÄÈ«ÎļìË÷¼¼ÊõÒѾ·Ç³£ÍêÃÀ£¬Oracle TextʹOracle9i¾ß±¸ÁËÇ¿´óµÄÎı¾¼ìË÷ÄÜÁ¦ºÍÖÇÄÜ»¯µÄÎı¾¹ÜÀíÄÜÁ¦¡£Oracle TextÊÇOracle9i²ÉÓõÄÐÂÃû³Æ£¬ÔÚOracle8/8iÖÐËü±»³Æ×÷Oracle interMedia Text£¬ÔÚOracle8ÒÔÇ°ËüµÄÃû³ÆÊÇOracle ConText Cartridge¡£
¶þ¡¢Oracle Text Ë÷Ò ......
Data Pump ·´Ó³ÁËÕû¸öµ¼³ö/µ¼Èë¹ý³ÌµÄÍêÈ«¸ïС£²»Ê¹Óó£¼ûµÄ SQL ÃüÁ¶øÊÇÓ¦ÓÃר
Óà API£¨direct path api etc) À´ÒÔ¸ü¿ìµÃ¶àµÄËٶȼÓÔغÍжÔØÊý¾Ý¡£
1.Data Pump µ¼³ö expdp
Àý
×Ó£º
sql>create directory dpdata1 as '/u02/ ......
ÔÚaccessÖÐÓÐ×Ô¶¯±àºÅµÄÊý¾ÝÀàÐÍ£¬MSSQLºÍMYSQLÒ²¶¼ÓÐ×Ô¶¯Ôö³¤µÄÊý¾ÝÀàÐÍ£¬²åÈë¼Ç¼ʱ²»ÓòÙ×÷´Ë×ֶΣ¬»á×Ô¶¯»ñµÃÊý¾ÝÖµ£¬¶øoracleûÓÐ×Ô¶¯Ôö³¤µÄÊý¾ÝÀàÐÍ£¬ÎÒÃÇÐèÒª½¨Á¢Ò»¸ö×Ô¶¯Ôö³¤µÄÐòÁкţ¬²åÈë¼Ç¼ʱҪ°ÑÐòÁкŵÄÏÂÒ»¸öÖµ¸³ÓÚ´Ë×ֶΣ¬¿ÉÒÔÔ¤¼ûµÄÊÇ£¬Óд˹¦ÄÜ£¬ÎÒÃÇ¿ÉÒÔ°ÑÊý¾Ý´ÓACCESS¡¢MSSQL»òMYSQ ......