在Oracle中似乎没有AUTO_INCREMENT的概念,直到并且包括版本11g。
如何在Oracle 11g中创建一个行为像自动递增的列?
在Oracle中似乎没有AUTO_INCREMENT的概念,直到并且包括版本11g。
如何在Oracle 11g中创建一个行为像自动递增的列?
当前回答
它被称为Identity Columns,只能从oracle 12c获得
CREATE TABLE identity_test_tab
(
id NUMBER GENERATED ALWAYS AS IDENTITY,
description VARCHAR2 (30)
);
插入到标识列的示例如下
INSERT INTO identity_test_tab (description) VALUES ('Just DESCRIPTION');
已创建1行。
你不能像下面这样插入
INSERT INTO identity_test_tab (id, description) VALUES (NULL, 'ID=NULL and DESCRIPTION');
第1行错误:ORA-32795:不能插入生成的always 标识列
INSERT INTO identity_test_tab (id, description) VALUES (999, 'ID=999 and DESCRIPTION');
第1行错误:ORA-32795:不能插入生成的always 标识列
有用的链接
其他回答
Oracle数据库12c引入了Identity,一个自动增量(系统生成)列。 在以前的数据库版本中(直到11g),您通常通过创建Sequence和Trigger来实现Identity。 从12c开始,您可以创建自己的Table并定义必须作为Identity生成的列。
这里是完整的解决方案w.r.t异常/错误处理自动增量,这个解决方案向后兼容,将工作在11g和12c,特别是如果应用程序在生产中。
请用合适的表名替换'TABLE_NAME'
--checking if table already exisits
BEGIN
EXECUTE IMMEDIATE 'DROP TABLE TABLE_NAME';
EXCEPTION WHEN OTHERS THEN NULL;
END;
/
--creating table
CREATE TABLE TABLE_NAME (
ID NUMBER(10) PRIMARY KEY NOT NULL,
.
.
.
);
--checking if sequence already exists
BEGIN
EXECUTE IMMEDIATE 'DROP SEQUENCE TABLE_NAME_SEQ';
EXCEPTION WHEN OTHERS THEN NULL;
END;
--creating sequence
/
CREATE SEQUENCE TABLE_NAME_SEQ START WITH 1 INCREMENT BY 1 MINVALUE 1 NOMAXVALUE NOCYCLE CACHE 2;
--granting rights as per required user group
/
GRANT SELECT, INSERT, UPDATE, DELETE ON TABLE_NAME TO USER_GROUP;
-- creating trigger
/
CREATE OR REPLACE TRIGGER TABLE_NAME_TS BEFORE INSERT OR UPDATE ON TABLE_NAME FOR EACH ROW
BEGIN
-- auto increment column
SELECT TABLE_NAME_SEQ.NextVal INTO :New.ID FROM dual;
-- You can also put some other required default data as per need of your columns, for example
SELECT SYS_CONTEXT('USERENV', 'SESSIONID') INTO :New.SessionID FROM dual;
SELECT SYS_CONTEXT('USERENV','SERVER_HOST') INTO :New.HostName FROM dual;
SELECT SYS_CONTEXT('USERENV','OS_USER') INTO :New.LoginID FROM dual;
.
.
.
END;
/
在Oracle 11g中没有“auto_increment”或“identity”列这样的东西。然而,你可以很容易地用一个序列和一个触发器来建模:
表定义:
CREATE TABLE departments (
ID NUMBER(10) NOT NULL,
DESCRIPTION VARCHAR2(50) NOT NULL);
ALTER TABLE departments ADD (
CONSTRAINT dept_pk PRIMARY KEY (ID));
CREATE SEQUENCE dept_seq START WITH 1;
触发器定义:
CREATE OR REPLACE TRIGGER dept_bir
BEFORE INSERT ON departments
FOR EACH ROW
BEGIN
SELECT dept_seq.NEXTVAL
INTO :new.id
FROM dual;
END;
/
更新:
IDENTITY列现在可以在Oracle 12c上使用:
create table t1 (
c1 NUMBER GENERATED by default on null as IDENTITY,
c2 VARCHAR2(10)
);
或者指定起始值和增量值,同时防止任何插入标识列(GENERATED ALWAYS)(同样,仅适用于Oracle 12c+)
create table t1 (
c1 NUMBER GENERATED ALWAYS as IDENTITY(START with 1 INCREMENT by 1),
c2 VARCHAR2(10)
);
另外,Oracle 12也允许使用一个序列作为默认值:
CREATE SEQUENCE dept_seq START WITH 1;
CREATE TABLE departments (
ID NUMBER(10) DEFAULT dept_seq.nextval NOT NULL,
DESCRIPTION VARCHAR2(50) NOT NULL);
ALTER TABLE departments ADD (
CONSTRAINT dept_pk PRIMARY KEY (ID));
查询如何在oracle中创建自动增量。在下面的查询中,每当插入新行时,列值将自动递增
CREATE TABLE table1(
id RAW(16) NOT NULL ENABLE,
incrmnt NUMBER(10,0) GENERATED ALWAYS AS IDENTITY
MINVALUE 1 MAXVALUE 999999999999999999999999999 INCREMENT BY 1 START WITH 1 NOORDER NOCYCLE NOT NULL ENABLE,
CONSTRAINT PK_table1 PRIMARY KEY (id) ENABLE);
FUNCTION GETUNIQUEID_2 RETURN VARCHAR2
AS
v_curr_id NUMBER;
v_inc NUMBER;
v_next_val NUMBER;
pragma autonomous_transaction;
begin
CREATE SEQUENCE sequnce
START WITH YYMMDD0000000001
INCREMENT BY 1
NOCACHE
select sequence.nextval into v_curr_id from dual;
if(substr(v_curr_id,0,6)= to_char(sysdate,'yymmdd')) then
v_next_val := to_number(to_char(SYSDATE+1, 'yymmdd') || '0000000000');
v_inc := v_next_val - v_curr_id;
execute immediate ' alter sequence sequence increment by ' || v_inc ;
select sequence.nextval into v_curr_id from dual;
execute immediate ' alter sequence sequence increment by 1';
else
dbms_output.put_line('exception : file not found');
end if;
RETURN 'ID'||v_curr_id;
END;