在oracle中使用sequence.nextval创建表 [英] create table with sequence.nextval in oracle

问题描述

我使用以下查询创建了一个序列，

i created a sequence using the following query,

create sequence qname_id_seq start with 1 increment by 1 nocache;

现在当我尝试创建一个使用上述序列的表时，会抛出以下错误，

Now when i try to create a table which uses the above sequence, it is throwing the following error,

Error report:
SQL Error: ORA-00907: missing right parenthesis
00907. 00000 -  "missing right parenthesis"

我使用以下查询创建一个包含sequence.nextval的表，

I used the following query to create a table with sequence.nextval,

CREATE TABLE qname
(
    qname_id integer NOT NULL default qname_id_seq.nextval PRIMARY KEY,
    qname    VARCHAR2(4000) NOT NULL -- CONSTRAINT qname_uk UNIQUE
);

推荐答案

Oracle 12c

我们现在终于有了许多其他数据库的 IDENTITY 列，以便在后台自动生成一个序列。此解决方案比基于触发器的解决方案快得多，可以

Oracle 12c

We now finally have IDENTITY columns like many other databases, in case of which a sequence is auto-generated behind the scenes. This solution is much faster than a trigger-based one as can be seen in this blog post.

因此，您的表格创建将如下所示：

So, your table creation would look like this:

CREATE TABLE qname
(
    qname_id integer GENERATED BY DEFAULT AS IDENTITY (START WITH 1) NOT NULL PRIMARY KEY,
    qname    VARCHAR2(4000) NOT NULL -- CONSTRAINT qname_uk UNIQUE
);

Oracle 11g及以下

文档，您不能这样做：

Oracle 11g and below

According to the documentation, you cannot do that:

默认列值的限制DEFAULT表达式不能包含对PL / SQL函数或其他列的引用，伪列CURRVAL，NEXTVAL，LEVEL ，PRIOR和ROWNUM，或未完全指定的日期常量。

Restriction on Default Column Values A DEFAULT expression cannot contain references to PL/SQL functions or to other columns, the pseudocolumns CURRVAL, NEXTVAL, LEVEL, PRIOR, and ROWNUM, or date constants that are not fully specified.

在Oracle中有自动增量列的标准方法是使用触发器，例如

The standard way to have "auto increment" columns in Oracle is to use triggers, e.g.

CREATE OR REPLACE TRIGGER my_trigger
  BEFORE INSERT 
  ON qname
  FOR EACH ROW
  -- Optionally restrict this trigger to fire only when really needed
  WHEN (new.qname_id is null)
DECLARE
  v_id qname.qname_id%TYPE;
BEGIN
  -- Select a new value from the sequence into a local variable. As David
  -- commented, this step is optional. You can directly select into :new.qname_id
  SELECT qname_id_seq.nextval INTO v_id FROM DUAL;

  -- :new references the record that you are about to insert into qname. Hence,
  -- you can overwrite the value of :new.qname_id (qname.qname_id) with the value
  -- obtained from your sequence, before inserting
  :new.qname_id := v_id;
END my_trigger;

详细了解文档中的Oracle TRIGGER

这篇关于在oracle中使用sequence.nextval创建表的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！