当前位置： 文江博客话题详情

如何在 Oracle 上使用 AUTO_INCRMENT 创建 id？

发布于 2024-08-17 19:43:56 字数 94 浏览 3 评论 0原文

Oracle 中似乎没有 AUTO_INCRMENT 的概念，直到版本 11g（包括版本）。

如何在 Oracle 11g 中创建一个行为类似于自动增量的列？

原文

It appears that there is no concept of AUTO_INCREMENT in Oracle, up until and including version 11g.

How can I create a column that behaves like auto increment in Oracle 11g?

如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

扫码二维码加入Web技术交流群

发布评论

需要 登录 才能够评论， 你可以免费 注册 一个本站的账号。

评论（18）

薔薇婲 2024-08-24 19:43:57

这是自动增量异常/错误处理的完整解决方案，该解决方案向后兼容，并且适用于 11g 和 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;
/

Here is complete solution w.r.t exception/error handling for auto increment, this solution is backward compatible and will work on 11g & 12c, specifically if application is in production.

Please replace 'TABLE_NAME' with your appropriate 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;
/
回复 原文
如何视而不见 2024-08-24 19:43:57

oracle中创建自增查询。在下面的查询中，每当插入新行时， incrmnt 列值将自动递增

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);

Query to create auto increment in oracle. In below query incrmnt column value will be auto incremented wheneever a new row is inserted

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);
回复 原文
坏尐絯 2024-08-24 19:43:57

这就是我在现有表和列（名为 id）上执行此操作的方法：

UPDATE table SET id=ROWNUM;
DECLARE
  maxval NUMBER;
BEGIN
  SELECT MAX(id) INTO maxval FROM table;
  EXECUTE IMMEDIATE 'DROP SEQUENCE table_seq';
  EXECUTE IMMEDIATE 'CREATE SEQUENCE table_seq START WITH '|| TO_CHAR(TO_NUMBER(maxval)+1) ||' INCREMENT BY 1 NOMAXVALUE';
END;
CREATE TRIGGER table_trigger
  BEFORE INSERT ON table
  FOR EACH ROW
BEGIN
  :new.id := table_seq.NEXTVAL;
END;

This is how I did this on an existing table and column (named id):

UPDATE table SET id=ROWNUM;
DECLARE
  maxval NUMBER;
BEGIN
  SELECT MAX(id) INTO maxval FROM table;
  EXECUTE IMMEDIATE 'DROP SEQUENCE table_seq';
  EXECUTE IMMEDIATE 'CREATE SEQUENCE table_seq START WITH '|| TO_CHAR(TO_NUMBER(maxval)+1) ||' INCREMENT BY 1 NOMAXVALUE';
END;
CREATE TRIGGER table_trigger
  BEFORE INSERT ON table
  FOR EACH ROW
BEGIN
  :new.id := table_seq.NEXTVAL;
END;
回复 原文
聚集的泪 2024-08-24 19:43:57
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;

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;
回复 原文
遮了一弯 2024-08-24 19:43:57
FUNCTION UNIQUE2(
 seq IN NUMBER
) RETURN VARCHAR2
AS
 i NUMBER := seq;
 s VARCHAR2(9);
 r NUMBER(2,0);
BEGIN
  WHILE i > 0 LOOP
    r := MOD( i, 36 );
    i := ( i - r ) / 36;
    IF ( r < 10 ) THEN
      s := TO_CHAR(r) || s;
    ELSE
      s := CHR( 55 + r ) || s;
    END IF;
  END LOOP;
  RETURN 'ID'||LPAD( s, 14, '0' );
END;

FUNCTION UNIQUE2(
 seq IN NUMBER
) RETURN VARCHAR2
AS
 i NUMBER := seq;
 s VARCHAR2(9);
 r NUMBER(2,0);
BEGIN
  WHILE i > 0 LOOP
    r := MOD( i, 36 );
    i := ( i - r ) / 36;
    IF ( r < 10 ) THEN
      s := TO_CHAR(r) || s;
    ELSE
      s := CHR( 55 + r ) || s;
    END IF;
  END LOOP;
  RETURN 'ID'||LPAD( s, 14, '0' );
END;
回复 原文
贩梦商人 2024-08-24 19:43:57

创建序列：

CREATE SEQUENCE SEQ_CM_LC_FINAL_STATUS
MINVALUE 1 MAXVALUE 999999999999999999999999999 
INCREMENT BY 1 START WITH 1 CACHE 20 NOORDER  NOCYCLE;

添加触发器

CREATE OR REPLACE TRIGGER CM_LC_FINAL_STATUS_TRIGGER
BEFORE INSERT
ON CM_LC_FINAL_STATUS
FOR EACH ROW
BEGIN
:NEW.LC_FINAL_STATUS_NO := SEQ_CM_LC_FINAL_STATUS.NEXTVAL;
END;

第一步是在数据库中创建 SEQUENCE，它是多个用户可以访问以自动生成递增值的数据对象。正如文档中所讨论的，Oracle 中的序列可防止同时创建重复值，因为在生成每个序列项之前，多个用户实际上被迫“轮流”。 –

最后，我们将创建我们的序列，稍后将使用它来实际生成唯一的自动递增值。 –

虽然我们已经创建了表格并准备就绪，但到目前为止我们的序列只是坐在那里，但从未被使用过。这就是 TRIGGERS 的用武之地。与现代编程语言中的事件类似，Oracle 中的 TRIGGER 是一个存储过程，在特定事件发生时执行。通常，触发器将配置为在更新表或删除记录时触发，从而在必要时提供一些清理工作。 –

在我们的例子中，我们希望在 INSERT 到 CM_LC_FINAL_STATUS 表之前执行 TRIGGER，确保我们的 SEQUENCE 递增并且新值传递到我们的主键列。

Creating a Sequence:

CREATE SEQUENCE SEQ_CM_LC_FINAL_STATUS
MINVALUE 1 MAXVALUE 999999999999999999999999999 
INCREMENT BY 1 START WITH 1 CACHE 20 NOORDER  NOCYCLE;

Adding a Trigger

CREATE OR REPLACE TRIGGER CM_LC_FINAL_STATUS_TRIGGER
BEFORE INSERT
ON CM_LC_FINAL_STATUS
FOR EACH ROW
BEGIN
:NEW.LC_FINAL_STATUS_NO := SEQ_CM_LC_FINAL_STATUS.NEXTVAL;
END;

The first step is to create a SEQUENCE in your database, which is a data object that multiple users can access to automatically generate incremented values. As discussed in the documentation, a sequence in Oracle prevents duplicate values from being created simultaneously because multiple users are effectively forced to “take turns” before each sequential item is generated. –

Finally, we’ll create our SEQUENCE that will be utilized later to actually generate the unique, auto incremented value. –

While we have our table created and ready to go, our sequence is thus far just sitting there but never being put to use. This is where TRIGGERS come in. Similar to an event in modern programming languages, a TRIGGER in Oracle is a stored procedure that is executed when a particular event occurs. Typically a TRIGGER will be configured to fire when a table is updated or a record is deleted, providing a bit of cleanup when necessary. –

In our case, we want to execute our TRIGGER prior to INSERT into our CM_LC_FINAL_STATUS table, ensuring our SEQUENCE is incremented and that new value is passed onto our primary key column.

回复 原文
泅渡 2024-08-24 19:43:57
  create trigger t1_trigger
  before insert on AUDITLOGS
  for each row
   begin
     select t1_seq.nextval into :new.id from dual;
   end;

只是我只需将表名（AUDITLOGS）更改为您的表名，并将new.id更改为new.column_name

  create trigger t1_trigger
  before insert on AUDITLOGS
  for each row
   begin
     select t1_seq.nextval into :new.id from dual;
   end;

only I have to just change the table name (AUDITLOGS) with your table name and new.id with new.column_name

回复 原文
贪了杯 2024-08-24 19:43:57

也许只需尝试这个简单的脚本：

http://www.hlavaj.sk/ai.php

结果是：

CREATE SEQUENCE TABLE_PK_SEQ; 
CREATE OR REPLACE TRIGGER TR_SEQ_TABLE BEFORE INSERT ON TABLE FOR EACH ROW 

BEGIN
SELECT TABLE_PK_SEQ.NEXTVAL
INTO :new.PK
FROM dual;
END;

Maybe just try this simple script:

http://www.hlavaj.sk/ai.php

Result is:

CREATE SEQUENCE TABLE_PK_SEQ; 
CREATE OR REPLACE TRIGGER TR_SEQ_TABLE BEFORE INSERT ON TABLE FOR EACH ROW 

BEGIN
SELECT TABLE_PK_SEQ.NEXTVAL
INTO :new.PK
FROM dual;
END;
回复 原文
眼泪也成诗 2024-08-24 19:43:56

从 Oracle 11g 开始，Oracle 中不存在“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));

There is no such thing as "auto_increment" or "identity" columns in Oracle as of Oracle 11g. However, you can model it easily with a sequence and a trigger:

Table definition:

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;

Trigger definition:

CREATE OR REPLACE TRIGGER dept_bir 
BEFORE INSERT ON departments 
FOR EACH ROW

BEGIN
  SELECT dept_seq.NEXTVAL
  INTO   :new.id
  FROM   dual;
END;
/

UPDATE:

IDENTITY column is now available on Oracle 12c:

create table t1 (
    c1 NUMBER GENERATED by default on null as IDENTITY,
    c2 VARCHAR2(10)
    );

or specify starting and increment values, also preventing any insert into the identity column (GENERATED ALWAYS) (again, Oracle 12c+ only)

create table t1 (
    c1 NUMBER GENERATED ALWAYS as IDENTITY(START with 1 INCREMENT by 1),
    c2 VARCHAR2(10)
    );

Alternatively, Oracle 12 also allows to use a sequence as a default value:

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));
回复 原文
陌上青苔 2024-08-24 19:43:56

SYS_GUID 返回一个 GUID——全局唯一的 ID。 SYS_GUID 是 RAW( 16）。它不会生成递增的数值。

如果要创建递增数字键，则需要创建序列。

CREATE SEQUENCE name_of_sequence
  START WITH 1
  INCREMENT BY 1
  CACHE 100;

然后，您可以在 INSERT 语句中使用该序列

INSERT INTO name_of_table( primary_key_column, <<other columns>> )
  VALUES( name_of_sequence.nextval, <<other values>> );

，或者可以定义一个触发器，使用该序列自动填充主键值。

CREATE OR REPLACE TRIGGER trigger_name
  BEFORE INSERT ON table_name
  FOR EACH ROW
BEGIN
  SELECT name_of_sequence.nextval
    INTO :new.primary_key_column
    FROM dual;
END;

如果您使用的是 Oracle 11.1 或更高版本，则可以稍微简化触发器

CREATE OR REPLACE TRIGGER trigger_name
  BEFORE INSERT ON table_name
  FOR EACH ROW
BEGIN
  :new.primary_key_column := name_of_sequence.nextval;
END;

如果您确实想使用SYS_GUID

CREATE TABLE table_name (
  primary_key_column raw(16) default sys_guid() primary key,
  <<other columns>>
)

SYS_GUID returns a GUID-- a globally unique ID. A SYS_GUID is a RAW(16). It does not generate an incrementing numeric value.

If you want to create an incrementing numeric key, you'll want to create a sequence.

CREATE SEQUENCE name_of_sequence
  START WITH 1
  INCREMENT BY 1
  CACHE 100;

You would then either use that sequence in your INSERT statement

INSERT INTO name_of_table( primary_key_column, <<other columns>> )
  VALUES( name_of_sequence.nextval, <<other values>> );

Or you can define a trigger that automatically populates the primary key value using the sequence

CREATE OR REPLACE TRIGGER trigger_name
  BEFORE INSERT ON table_name
  FOR EACH ROW
BEGIN
  SELECT name_of_sequence.nextval
    INTO :new.primary_key_column
    FROM dual;
END;

If you are using Oracle 11.1 or later, you can simplify the trigger a bit

CREATE OR REPLACE TRIGGER trigger_name
  BEFORE INSERT ON table_name
  FOR EACH ROW
BEGIN
  :new.primary_key_column := name_of_sequence.nextval;
END;

If you really want to use SYS_GUID

CREATE TABLE table_name (
  primary_key_column raw(16) default sys_guid() primary key,
  <<other columns>>
)
回复 原文
悲喜皆因你 2024-08-24 19:43:56

在 Oracle 12c 及更高版本中，您可以执行类似的操作，

CREATE TABLE MAPS
(
  MAP_ID INTEGER GENERATED ALWAYS AS IDENTITY (START WITH 1 INCREMENT BY 1) NOT NULL,
  MAP_NAME VARCHAR(24) NOT NULL,
  UNIQUE (MAP_ID, MAP_NAME)
);

并且在 Oracle (Pre 12c) 中。

-- create table
CREATE TABLE MAPS
(
  MAP_ID INTEGER NOT NULL ,
  MAP_NAME VARCHAR(24) NOT NULL,
  UNIQUE (MAP_ID, MAP_NAME)
);

-- create sequence
CREATE SEQUENCE MAPS_SEQ;

-- create tigger using the sequence
CREATE OR REPLACE TRIGGER MAPS_TRG 
BEFORE INSERT ON MAPS 
FOR EACH ROW
WHEN (new.MAP_ID IS NULL)
BEGIN
  SELECT MAPS_SEQ.NEXTVAL
  INTO   :new.MAP_ID
  FROM   dual;
END;
/

In Oracle 12c onward you could do something like,

CREATE TABLE MAPS
(
  MAP_ID INTEGER GENERATED ALWAYS AS IDENTITY (START WITH 1 INCREMENT BY 1) NOT NULL,
  MAP_NAME VARCHAR(24) NOT NULL,
  UNIQUE (MAP_ID, MAP_NAME)
);

And in Oracle (Pre 12c).

-- create table
CREATE TABLE MAPS
(
  MAP_ID INTEGER NOT NULL ,
  MAP_NAME VARCHAR(24) NOT NULL,
  UNIQUE (MAP_ID, MAP_NAME)
);

-- create sequence
CREATE SEQUENCE MAPS_SEQ;

-- create tigger using the sequence
CREATE OR REPLACE TRIGGER MAPS_TRG 
BEFORE INSERT ON MAPS 
FOR EACH ROW
WHEN (new.MAP_ID IS NULL)
BEGIN
  SELECT MAPS_SEQ.NEXTVAL
  INTO   :new.MAP_ID
  FROM   dual;
END;
/
回复 原文
清晰传感 2024-08-24 19:43:56

这里有三种风格：

  1. 数字。简单递增的数值，例如 1,2,3,...
  2. GUID。全球通用标识符，作为 RAW 数据类型。
  3. GUID（字符串）。与上面相同，但作为字符串在某些语言中可能更容易处理。

x 是标识列。在每个示例中将 FOO 替换为您的表名称。

-- numerical identity, e.g. 1,2,3...
create table FOO (
    x number primary key
);
create sequence  FOO_seq;

create or replace trigger FOO_trg
before insert on FOO
for each row
begin
  select FOO_seq.nextval into :new.x from dual;
end;
/

-- GUID identity, e.g. 7CFF0C304187716EE040488AA1F9749A
-- use the commented out lines if you prefer RAW over VARCHAR2.
create table FOO (
    x varchar(32) primary key        -- string version
    -- x raw(32) primary key         -- raw version
);

create or replace trigger FOO_trg
before insert on FOO
for each row
begin
  select cast(sys_guid() as varchar2(32)) into :new.x from dual;  -- string version
  -- select sys_guid() into :new.x from dual;                     -- raw version
end;
/

更新：

Oracle 12c 引入了这两个不依赖于触发器的变体：

create table mytable(id number default mysequence.nextval);
create table mytable(id number generated as identity);

第一个以传统方式使用序列；第二个在内部管理价值。

Here are three flavors:

  1. numeric. Simple increasing numeric value, e.g. 1,2,3,....
  2. GUID. globally univeral identifier, as a RAW datatype.
  3. GUID (string). Same as above, but as a string which might be easier to handle in some languages.

x is the identity column. Substitute FOO with your table name in each of the examples.

-- numerical identity, e.g. 1,2,3...
create table FOO (
    x number primary key
);
create sequence  FOO_seq;

create or replace trigger FOO_trg
before insert on FOO
for each row
begin
  select FOO_seq.nextval into :new.x from dual;
end;
/

-- GUID identity, e.g. 7CFF0C304187716EE040488AA1F9749A
-- use the commented out lines if you prefer RAW over VARCHAR2.
create table FOO (
    x varchar(32) primary key        -- string version
    -- x raw(32) primary key         -- raw version
);

create or replace trigger FOO_trg
before insert on FOO
for each row
begin
  select cast(sys_guid() as varchar2(32)) into :new.x from dual;  -- string version
  -- select sys_guid() into :new.x from dual;                     -- raw version
end;
/

update:

Oracle 12c introduces these two variants that don't depend on triggers:

create table mytable(id number default mysequence.nextval);
create table mytable(id number generated as identity);

The first one uses a sequence in the traditional way; the second manages the value internally.

回复 原文
夏日落 2024-08-24 19:43:56

Oracle Database 12c 引入了 Identity，一个自动增量（系统生成）列。
在以前的数据库版本中（直到11g），您通常通过创建序列和触发器来实现身份。
从 12c 开始，您可以创建自己的表并定义必须生成为标识的列。

Oracle Database 12c introduced Identity, an auto-incremental (system-generated) column.
In the previous database versions (until 11g), you usually implement an Identity by creating a Sequence and a Trigger.
From 12c onward, you can create your own Table and define the column that has to be generated as an Identity.

回复 原文
嘿看小鸭子会跑 2024-08-24 19:43:56

假设您指的是像 SQL Server 标识列这样的列？

在 Oracle 中，您可以使用 SEQUENCE 来实现相同的功能。我会看看是否能找到一个好的链接并将其发布在这里。

更新：看起来你自己找到了。无论如何，这是链接：
http://www.techonthenet.com/oracle/sequences.php

Assuming you mean a column like the SQL Server identity column?

In Oracle, you use a SEQUENCE to achieve the same functionality. I'll see if I can find a good link and post it here.

Update: looks like you found it yourself. Here is the link anyway:
http://www.techonthenet.com/oracle/sequences.php

回复 原文
就此别过 2024-08-24 19:43:56

当您想要任何人都可以轻松阅读/记住/理解的序列号时，可以使用触发器和序列。但是如果你不想通过这种方式管理ID列（如emp_id），并且该列的值不是很大，你可以在建表时使用SYS_GUID()来获得自动增量，例如这。

CREATE TABLE <table_name> 
(emp_id RAW(16) DEFAULT SYS_GUID() PRIMARY KEY,
name VARCHAR2(30));

现在，您的 emp_id 列将接受“全局唯一标识符值”。
您可以通过忽略 emp_id 列来在表中插入值，如下所示。

INSERT INTO <table_name> (name) VALUES ('name value');

因此，它将向您的 emp_id 列插入唯一值。

Trigger and Sequence can be used when you want serialized number that anyone can easily read/remember/understand. But if you don't want to manage ID Column (like emp_id) by this way, and value of this column is not much considerable, you can use SYS_GUID() at Table Creation to get Auto Increment like this.

CREATE TABLE <table_name> 
(emp_id RAW(16) DEFAULT SYS_GUID() PRIMARY KEY,
name VARCHAR2(30));

Now your emp_id column will accept "globally unique identifier value".
you can insert value in table by ignoring emp_id column like this.

INSERT INTO <table_name> (name) VALUES ('name value');

So, it will insert unique value to your emp_id Column.

回复 原文
星星的轨迹 2024-08-24 19:43:56

它称为Identity Columns，并且仅在 Oracle Oracle 12c中可用，

CREATE TABLE identity_test_tab
(
   id            NUMBER GENERATED ALWAYS AS IDENTITY,
   description   VARCHAR2 (30)
);

插入Identity Columns的示例如下

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
身份栏

有用链接

it is called Identity Columns and it is available only from oracle Oracle 12c

CREATE TABLE identity_test_tab
(
   id            NUMBER GENERATED ALWAYS AS IDENTITY,
   description   VARCHAR2 (30)
);

example of insert into Identity Columns as below

INSERT INTO identity_test_tab (description) VALUES ('Just DESCRIPTION');

1 row created.

you can NOT do insert like below

INSERT INTO identity_test_tab (id, description) VALUES (NULL, 'ID=NULL and DESCRIPTION');

ERROR at line 1: ORA-32795: cannot insert into a generated always
identity column

INSERT INTO identity_test_tab (id, description) VALUES (999, 'ID=999 and DESCRIPTION');

ERROR at line 1: ORA-32795: cannot insert into a generated always
identity column

useful link

回复 原文
云雾 2024-08-24 19:43:56

从 Oracle 12c 开始，通过以下两种方式之一支持标识列：

  1. 序列 + 表 - 在此解决方案中，您仍然像平常一样创建序列，然后使用以下 DDL：

    创建表MyTable
    （ID 号默认 MyTable_Seq.NEXTVAL，
    ...)

  2. 仅表 - 在此解决方案中，没有显式指定顺序。您将使用以下 DDL：

    CREATE TABLE MyTable (ID NUMBER GENERATED AS IDENTITY, ...)

如果您使用第一种方式，它与现有的处理方式向后兼容。第二个更简单一些，并且与其他 RDMS 系统更加一致。

Starting with Oracle 12c there is support for Identity columns in one of two ways:

  1. Sequence + Table - In this solution you still create a sequence as you normally would, then you use the following DDL:

    CREATE TABLE MyTable
    (ID NUMBER DEFAULT MyTable_Seq.NEXTVAL,
    ...)

  2. Table Only - In this solution no sequence is explicitly specified. You would use the following DDL:

    CREATE TABLE MyTable (ID NUMBER GENERATED AS IDENTITY, ...)

If you use the first way it is backward compatible with the existing way of doing things. The second is a little more straightforward and is more inline with the rest of the RDMS systems out there.

回复 原文
~没有更多了~

关于作者

划一舟意中人

暂无简介

0 文章
0 评论
22 人气
发私信

相关话题

更多

热门标签

操作系统 程序设计 IT运维 Linux系统管理 JavaScript 服务器应用 solaris C/C++ PHP Shell BSD Vue.js aix Oracle Python HTML 系统管理 HTML5 CSS 前端
更多

推荐作者

1CH1MKgiKxn9p

文章 0 评论 0

ゞ记忆︶ㄣ

文章 0 评论 0

JackDx

文章 0 评论 0

信远

文章 0 评论 0

yaoduoduo1995

文章 0 评论 0

霞映澄塘

文章 0 评论 0

更多

友情链接

文江博客
    我们使用 Cookies 和其他技术来定制您的体验包括您的登录状态等。通过阅读我们的 隐私政策 了解更多相关信息。 单击 接受 或继续使用网站，即表示您同意使用 Cookies 和您的相关数据。
    原文