在Oracle/PostgreSQL中使用没有ID的插入语句的序列。

18 浏览
0 Comments

在Oracle/PostgreSQL中使用没有ID的插入语句的序列。

我试图创建一个带有聪明的序列生成器的表，以便使用以下插入结构：

insert into SOMEUSERS (SOMEUSERS_NAME, SOMEUSERS_PASSWORD) 
values ('Artem', 'PracTimPatie');

而不是这个结构：

insert into SOMEUSERS (SOMEUSERS_ID, SOMEUSERS_NAME, SOMEUSERS_PASSWORD) 
values (2, 'Artem', 'PracTimPatie');

或者这个结构：

insert into SOMEUSERS (SOMEUSERS_ID, SOMEUSERS_NAME, SOMEUSERS_PASSWORD) 
values (GEN_ID_SOMEUSERS.nextval, 'Artem', 'PracTimPatie');

当我执行以下SQL脚本时：

create sequence gen_id_someUsers START WITH 1 INCREMENT BY 1 NOCACHE NOCYCLE;
CREATE TABLE loc_db.someUsers
( someUsers_id number(10) DEFAULT gen_id_someUsers.NEXTVAL NOT NULL, --因为这一行
  someUsers_name varchar2(50) NOT NULL,
  someUsers_password varchar2(50),
  CONSTRAINT someUsers_pk PRIMARY KEY (someUsers_id)
);

我收到以下提示：

错误报告 - SQL错误：ORA-00984：此处不允许列

00984. 00000 - "此处不允许列"

为了清楚起见，说一下在这种情况下：

    ...
CREATE TABLE loc_db.someUsers
( someUsers_id number(10) NOT NULL, --更正此行
...

序列GEN_ID_SOMEUSERS已创建。

表LOC_DB.SOMEUSERS已创建。

我如何配置舒适的序列生成器？

（对于PostgreSQL也是如此。如果可能的话，尽量不使用触发器）

0

3 答案

0 Comments

在Oracle中，可以使用序列（Sequence）来生成唯一的自增ID，然后在插入语句中使用这个序列。但在PostgreSQL中，没有直接的序列（Sequence）概念，而是使用SERIAL类型来实现类似的功能。

在PostgreSQL中，可以通过以下方式来创建一个表并定义一个SERIAL类型的列：

CREATE TABLE SOMEUSERS (
  SOMEUSERS_ID SERIAL NOT NULL,
  SOMEUSERS_NAME TEXT,
  SOMEUSERS_PASSWORD TEXT
);

在插入数据时，可以直接指定要插入的列，并且不需要指定SERIAL类型的列。例如：

INSERT INTO SOMEUSERS (SOMEUSERS_NAME, SOMEUSERS_PASSWORD) 
VALUES ('Artem', 'PracTimPatie');

如果需要查询序列的值，可以像查询其他关系表一样进行查询。

0
0 Comments

在Oracle 11.2或更早版本中，无法在INSERT语句中使用序列的NEXTVAL作为DEFAULT值，因此需要使用触发器来解决这个问题。具体操作如下：

首先，根据Oracle文档的说明，DEFAULT子句允许指定一个值，以便在后续的INSERT语句中，如果省略了该列的值，则被赋予这个值。表达式的数据类型必须与列的数据类型匹配，并且列的长度足够容纳这个表达式。

但是，DEFAULT表达式不能包含对PL/SQL函数或其他列的引用，也不能包含伪列CURRVAL、NEXTVAL、LEVEL、PRIOR和ROWNUM，或者未完全指定的日期常量。

由于无法将sequence.NEXTVAL作为DEFAULT值，所以我们需要使用触发器来解决这个问题。触发器的代码如下：

CREATE OR REPLACE TRIGGER SOMEUSERS_BI
  BEFORE INSERT
  ON LOC_DB.SOMEUSERS
  FOR EACH ROW
BEGIN
  IF :NEW.SOMEUSERS_ID THEN
    :NEW.SOMEUSERS_ID := GEN_ID_SOMEUSERS.NEXTVAL;
  END IF;
END SOMEUSERS_BI;

在我使用Oracle 11.2或更早版本的经验中，没有可靠的替代方法可以解决这个问题。祝好运！

0
0 Comments

在Oracle 12c之前的版本中，如果想要在插入语句中使用序列(sequence)而不是ID来生成唯一标识符，需要使用触发器和序列来实现。在Oracle 12c中，已经引入了Identity列的概念，可以更方便地生成唯一标识符。

以下是在Oracle 12c中使用Identity列的示例代码：

CREATE TABLE SOMEUSERS (
  SOMEUSERS_ID       NUMBER(10) GENERATED ALWAYS AS IDENTITY
                     CONSTRAINT SOMEUSERS__SOMEUSERS_ID__PK PRIMARY KEY,
  SOMEUSERS_NAME     VARCHAR2(50)
                     CONSTRAINT SOMEUSERS__SOMEUSERS_NAME__NN NOT NULL,
  SOMEUSERS_PASSWORD VARCHAR2(50)
);

在此示例中，SOMEUSERS_ID列被定义为Identity列，它将自动为每个插入的行生成唯一的标识符。

在Oracle 12c之前的版本中，可以使用触发器和序列来实现类似的功能。以下是示例代码：

CREATE TABLE SOMEUSERS (
  SOMEUSERS_ID       NUMBER(10)
                     CONSTRAINT SOMEUSERS__SOMEUSERS_ID__PK PRIMARY KEY,
  SOMEUSERS_NAME     VARCHAR2(50)
                     CONSTRAINT SOMEUSERS__SOMEUSERS_NAME__NN NOT NULL,
  SOMEUSERS_PASSWORD VARCHAR2(50)
);
CREATE SEQUENCE gen_id_someUsers START WITH 1 INCREMENT BY 1 NOCACHE NOCYCLE;
CREATE OR REPLACE TRIGGER SOMEUSERS__ID__TRG 
BEFORE INSERT ON SOMEUSERS
FOR EACH ROW
BEGIN
  :new.SOMEUSERS_ID := gen_id_someUsers.NEXTVAL;
END;

在此示例中，我们创建了一个名为gen_id_someUsers的序列，并创建了一个名为SOMEUSERS__ID__TRG的触发器。在每次插入行之前，触发器将自动为SOMEUSERS_ID列赋予下一个序列值。

你可以使用以下代码将数据插入到SOMEUSERS表中：

INSERT INTO SOMEUSERS (
  SOMEUSERS_NAME,
  SOMEUSERS_PASSWORD
) VALUES (
  'Name',
  'Password'
);

以上是在Oracle中使用序列来插入数据的示例代码。无论是使用Identity列还是触发器和序列的组合，都可以实现在插入语句中使用序列而不是ID来生成唯一标识符。

0