oracle 的自增字段。

原来不熟 oracle 。现在发现，居然 9i2 用不了 自增字段。目前的 jdbc driver 也当然的不支持 getGeneratedKeys() 。真不方便。

为此，上网找资料。

1. 发现，一般用 序列 + 触发器 实现类似效果。
2. 10g2 的 jdbc driver 开始支持 getGeneratedKeys() 。

记一笔。

---

AskTom "Difference using Auto-increment col and ..."

http://asktom.oracle.com/pls/ask/f?p=4950:8:::::F4950_P8_DISPLAYID:500421805606

You Asked (Jump to Tom's latest followup)

Hi Tom,

   I am doing some Data Modelling and my target database is Oracle. Unlike other Databases like SQLServer and Access, Oracle does not define a Auto-Increment Column. I may need to use an Auto-Incrementing column as a Primary key for a table but I can also choose another attribute of datatype Varchar2(30) which is unique also in the table. Can you tell me why Oracle does not implement Auto-increment Columns as a DataType and the advantages and disadvantages of using both the auto-increment column and the attribute as primary keys? Thanks Tet Cheng

and we said...

We offer a sequence which has a little more flexibility and control. To get an autoincrement column, I would:

create table T ( x int primary key, .... );
create sequence t_seq;
create trigger t_trigger before insert on T for each row
begin
   if ( :new.x is null ) then
      select t_seq.nextval into :new.x from dual;
   end if;
end;
/

to automatically populate when a value for X was not supplied (allowing me to easily override the "auto" part of it) or I would:

create table T ( x int primary key, .... );
create sequence t_seq;

and then:

insert into t ( x, ... ) values ( t_seq.nextval, .... );

The nice thing about sequences is when you are populating a parent/child table -- you have immediate access to t_seq.CURRVAL which returns the value of the last NEXTVAL you selected.

As for "..and the advantages and disadvantages of using both the auto-increment column and the attribute as primary keys?" I don't fully understand that as an "auto increment" column is in fact an attribute as well....

……

---

Oracle, auto-increment column and pre-defined values 

http://blogs.wdevs.com/qc/archive/2005/03/21/2836.aspx

……

I rewrote it so that when an INSERT statement define the ID value, 
the triggeer checks if this value is greather than the current 
sequence value and in this case automatically reset the sequence 
to the inserted value.
/* create table */
CREATE TABLE all_Increments (
   id   INT NOT NULL ,
   test VARCHAR2(100) ,
   CONSTRAINT PK_all_Increments
   PRIMARY KEY ( id )
);
/* create sequence */
CREATE SEQUENCE SEQ_all_Increments
   START WITH 1
   MAXVALUE 1.0E28
   MINVALUE 1
   NOCYCLE
   NOCACHE
   ORDER;
/* create trigger */
CREATE OR REPLACE TRIGGER TRG_BI_all_Increments
   BEFORE INSERT
   ON all_Increments
   FOR EACH ROW
DECLARE
   last_Sequence NUMBER;
   last_InsertID NUMBER;
BEGIN
   IF (:NEW.id IS NULL) THEN
      SELECT SEQ_all_Increments.NEXTVAL INTO :NEW.id FROM DUAL;
   ELSE
      SELECT NVL(Last_Number, 0) INTO last_Sequence
        FROM User_Sequences
       WHERE UPPER(Sequence_Name) = UPPER('SEQ_all_Increments');
      SELECT :NEW.id INTO last_InsertID FROM DUAL;
      WHILE (last_InsertID > last_Sequence) LOOP
         SELECT SEQ_all_Increments.NEXTVAL INTO last_Sequence FROM DUAL;
      END LOOP;
   END IF;
END;
/* test values */
INSERT INTO all_Increments (id, test) values (1, 'one');
INSERT INTO all_Increments (id, test) values (2, 'two');
INSERT INTO all_Increments (id, test) values (3, 'three');
INSERT INTO all_Increments (id, test) values (10, 'ten');
INSERT INTO all_Increments (test) values ('eleven?');
INSERT INTO all_Increments (id, test) values (5, 'five?');
INSERT INTO all_Increments (test) values ('twelve?');
……

---

Java Forums - Getting back new generated key instead of using getGeneratedKeys method?

http://forum.java.sun.com/thread.jspa?threadID=673257&messageID=3933774

Re: Getting back new generated key instead of using getGeneratedKeys method Author: StuDerby  Posts: 531   Registered: 7/6/05

Oct 16, 2005 7:16 PM (reply 8 of 11)

I was poking around a little more in the Oracle docs and it looks like getGeneratedKeys() came in with the 10.2 driver.
(PDF - http://www.oracle.com/technology/tech/java/sqlj_jdbc/pdf/twp_appdev_java_whats_new_4_java_jdbc_web_services.pdf )


The various compatability docs say that the driver is compatible with older DBs:
http://www.oracle.com/technology/tech/java/sqlj_jdbc/htdocs/jdbc_faq.html#02_02
http://www.oracle.com/technology/tech/java/sqlj_jdbc/index.html
These, combined with the Metalink notes, say that the 10g drivers should work with database 10.x, 9.2.x, and 8.1.7.x (Older 10g drivers only play nice with 8.1.7.4 but that's fixed in the latest driver).

However, in preliminary testing, I have seen some things change/break jumping from the old classes12 driver to the 10g driver, using the same 8.1.7.4 database, so be careful upgrading drivers.

……