在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 及以下

根据文档，您不能这样做:

对默认列值的限制 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 TRIGGERs

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