¡¾oracleÐòÁÐרÌâ¡¿£¨×ªÔØ£©
ORACLEûÓÐÏóSQL SERVERÖÐÒ»ÑùµÄ×ÔÔö¼Ó×ֶΣ¬ÒªÊµÏÖÖ»ÄÜͨ¹ýSEQUENCEÀ´ÊµÏÖ
1.´´½¨ÐòÁУº
create sequence your_seq
nocycle
maxvalue 9999999999
start with 1;
2.ʹÓô¥·¢Æ÷ʵÏÖ×ÔÔö£º
create or replace trigger your_seq_tri
before insert on your_table1 for each row
declare
next_id number;
begin
select your_seq.nextval into next_id from dual;
:new.id := next_id;
end;
ÔÚoracleÖУ¬ÎªÁË·½±ãÎÒ³£³£Óô¥·¢Æ÷¼°ÐòÁнáºÏÆðÀ´ÊµÏÖ£¬ÏÂÃæ¾ÍÊÇÒ»¸öʾÀý¡£
ÏȽ¨±í¡¢ÔÙ½¨ÐòÁС¢È»ºóÊÇ´¥·¢Æ÷£¬×îºó²âÊÔ
=============================================
--ΪÖ÷¼üÖ¸¶¨ÐòÁбàºÅ
--2003-10-8 15:53
=============================================
conn scott/tiger@powermis
drop table foo;
create table foo(
id number primary key,
data varchar2(100));
create sequence foo_seq;
create or replace trigger bifer_foo_id_pk
before insert
on foo
for each row
begin
select foo_seq.nextval into :new.id from dual;
end;
/
insert into foo(data)
values('Chirstopher');
insert into foo(id,data)
values(5,'Sean');
select * from foo;
Oracle8iÒýÈëÁËSYS_GUIDÕâ¸ö¸ÅÄËüͬOracle¹ÜÀíÔ±ËùʹÓõĴ«Í³µÄÐòÁУ¨sequence£©Ïà±È¾ßÓÐÖî¶àÓÅÊÆ¡£
Ò»¸öÐòÁÐÉú³ÉÆ÷Ö»ÊǼòµ¥µØ´´½¨´Ó¸ø¶¨µÄÆðµã¿ªÊ¼µÄһϵÁÐÕûÊýÖµ£¬¶øÇÒËü±»ÓÃÔÚÑ¡Ôñ³ÂÊöʽµÄʱºò×Ô¶¯µØµÝ
Ôö¸ÃϵÁС£
ÐòÁÐÉú³ÉÆ÷ËùÉú³ÉµÄÊý×ÖÖ»Äܱ£Ö¤ÔÚµ¥¸öʵÀýÀïÊÇΨһµÄ£¬Õâ¾Í²»ÊʺϽ«ËüÓÃ×÷²¢ÐлòÕßÔ¶³Ì»·¾³ÀïµÄÖ÷¹Ø¼ü
×Ö£¬ÒòΪ¸÷×Ô»·¾³ÀïµÄÐòÁпÉÄÜ»áÉú³ÉÏàͬµÄÊý×Ö£¬´Ó¶øµ¼Ö³åÍ»µÄ·¢Éú¡£SYS_GUID»á±£Ö¤Ëü´´½¨µÄ±êʶ·ûÔÚ
ÿ¸öÊý¾Ý¿âÀﶼÊÇΨһµÄ¡£
´ËÍ⣬ÐòÁбØÐëÊÇDML³ÂÊöʽµÄÒ»²¿·Ö£¬Òò´ËËüÐèÒªÒ»¸öµ½Êý¾Ý¿âµÄÍù·µ¹ý³Ì£¨·ñÔòËü¾Í²»Äܱ£Ö¤ÆäÖµÊÇΨһ
µÄ£©¡£
SYS_GUIDÔ´×Ô²»ÐèÒª¶ÔÊý¾Ý¿â½øÐзÃÎʵÄʱ¼ä´ÁºÍ»úÆ÷±êʶ·û£¬Õâ¾Í½ÚÊ¡Á˲éѯµÄÏûºÄ¡£
create table use_seq_table(id integer);
create sequence use_seq_sequence;
insert into use_seq_table values (use_seq_sequence_value.nextval);
REM - for some reason, the documentation uses raw(32)
create table use_guid_table(id raw(16));
insert into use_guid_table(sys_guid());
ºÜ¶àÓ¦ÓóÌÐò¶¼ÒÀ¿¿ÐòÁÐÉú³ÉÆ÷À´´´½¨
Ïà¹ØÎĵµ£º
ËäȻѧϰJavaºÜ¾ÃÁË£¬×Ô¼ºÒ²Á¬½Ó¹ýһЩÊý¾Ý¿â£¬±ÈÈçmysqlÖ®ÀàµÄ£¬Èç½ñÄØ£¬Ò²Ñ§Ï°ÁËÒ»¶Îʱ¼äµÄOracle£¬È»¶øÄØ£¬½ñÌìÊÇÎÒµÚÒ»´ÎÁ¬½ÓOracle£¬ºÙºÙ£¬Ó¦¸Ã»¹²»ËãÌ«³Ù°É¡£
½ñÌìÄØ£¬Óе㱿׾£¬´ó¼ÒĪЦ£¡
ÎÒÕâÊÇÒ»¸ö²éѯÀý×Ó
Ê×ÏÈ£¬Ô ......
Ò»¸öÓû§¿ÉÒÔʹÓÃÒ»¸ö»ò¶à¸ö±í¿Õ¼ä£¬Ò»¸ö±í¿Õ¼äÒ²¿ÉÒÔ¹©¶à¸öÓû§Ê¹Óá£Óû§ºÍ±í¿Õ¼äûÓÐÁ¥Êô¹Øϵ£¬±í¿ÕÊÇÒ»¸öÓÃÀ´¹ÜÀíÊý¾Ý´æ´¢Âß¼¸ÅÄ±í¿Õ¼äÖ»ÊǺÍÊý¾ÝÎļþ·¢Éú¹Øϵ£¬Êý¾ÝÎļþÊÇÎïÀíµÄ£¬Ò»¸ö±í¿Õ¼ä¿ÉÒÔ°üº¬¶à¸öÊý¾ÝÎļþ£¬¶øÒ»¸öÊý¾ÝÎļþÖ»ÄÜÁ¥ÊôÒ»¸ö±í¿Õ¼ä¡£
Óû§ÊôÓÚÊý¾Ý¿âʵÀýµ ......
µÚ1Ò³
1. Oracle µÄʹÓÃ
1.1. SQLPLUS µÄÃüÁî
³õʼ»¯±íµÄλÖãº
set
NLS_LANG=american_america.us7ascii (ÉèÖñàÂë²Å¿ÉÒÔʹÓÃÏÂÃæ½Å±¾)
cd
$ORACLE_HOME/rdbms cd demo summit2.sql
********* ......
1.Oracleɾ³ýÖظ´¼Ç¼.
ɾ³ý±íÖжàÓàµÄÖظ´¼Ç¼£¬Öظ´¼Ç¼ÊǸù¾Ýµ¥¸ö×ֶΣ¨peopleId£©À´Åжϣ¬Ö»ÁôÓÐrowid×îСµÄ¼Ç¼.
delete from people
where peopleId in (select peopleId from people group by peopleId having count(peopleId) > 1)
and rowid not i ......