SQLPlus AUTO_INCREMENT 错误

发布于 2024-12-12 22:39:12 字数 407 浏览 5 评论 0原文

当我尝试在 SQLPlus 中运行以下命令时：

CREATE TABLE Hotel
(hotelNo NUMBER(4) NOT NULL AUTO_INCREMENT,
hotelName VARCHAR(20) NOT NULL,
city VARCHAR(50) NOT NULL,
CONSTRAINT hotelNo_pk PRIMARY KEY (hotelNo));

我收到以下错误：

(hotelNo NUMBER(4) NOT NULL AUTO_INCREMENT,
                        *
ERROR at line 2:
ORA-00907: missing right parenthesis

我做错了什么？

原文

When I try and run the following command in SQLPlus:

CREATE TABLE Hotel
(hotelNo NUMBER(4) NOT NULL AUTO_INCREMENT,
hotelName VARCHAR(20) NOT NULL,
city VARCHAR(50) NOT NULL,
CONSTRAINT hotelNo_pk PRIMARY KEY (hotelNo));

I get the following error:

(hotelNo NUMBER(4) NOT NULL AUTO_INCREMENT,
                        *
ERROR at line 2:
ORA-00907: missing right parenthesis

What am I doing wrong?

分享到QQ

分享到微博

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

发布评论

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

烟酒忠诚 2024-12-19 22:39:13

或者 - 从 Oracle 12.1 开始 - 您可以简单地拥有：

CREATE TABLE employee 
(
    id NUMBER GENERATED by default on null as IDENTITY
    ....
)

Or - starting with Oracle 12.1 - you can simply have:

CREATE TABLE employee 
(
    id NUMBER GENERATED by default on null as IDENTITY
    ....
)

回复收藏 0 原文

空心↖ 2024-12-19 22:39:12

许多人会抱怨这不是 Oracle 中的标准功能，但是当它就像在 CREATE TABLE 命令之后再加两个命令一样简单时，我看不出有任何充分的理由在每次插入时使用花哨的 SQL。
首先让我们创建一个简单的表格来玩。

SQL> CREATE TABLE test
(id NUMBER PRIMARY KEY,
name VARCHAR2(30));
Table created.

现在我们假设我们希望 ID 成为一个自动递增字段。首先我们需要一个序列来从中获取值。

SQL> CREATE SEQUENCE test_sequence
START WITH 1
INCREMENT BY 1;
Sequence created.

现在我们可以在表的 BEFORE INSERT 触发器中使用该序列。

CREATE OR REPLACE TRIGGER test_trigger
BEFORE INSERT
ON test
REFERENCING NEW AS NEW
FOR EACH ROW
BEGIN
SELECT test_sequence.nextval INTO :NEW.ID FROM dual;
END;
/


SQL> INSERT INTO test (name) VALUES ('Jon');
1 row created.

SQL> INSERT INTO test (name) VALUES (’Bork’);
1 row created.

SQL> INSERT INTO test (name) VALUES (’Matt’);
1 row created.

SQL> SELECT * FROM test;

ID NAME
———- ——————————
1 Jon
2 Bork
3 Matt

Many will gripe about this not being a standard feature in Oracle, but when it’s as easy as two more commands after your CREATE TABLE command I can’t see any good reason to use fancy SQL on every insert.
First let’s create a simple table to play with.

SQL> CREATE TABLE test
(id NUMBER PRIMARY KEY,
name VARCHAR2(30));
Table created.

Now we’ll assume we want ID to be an auto increment field. First we need a sequence to grab values from.

SQL> CREATE SEQUENCE test_sequence
START WITH 1
INCREMENT BY 1;
Sequence created.

Now we can use that sequence in a BEFORE INSERT trigger on the table.

CREATE OR REPLACE TRIGGER test_trigger
BEFORE INSERT
ON test
REFERENCING NEW AS NEW
FOR EACH ROW
BEGIN
SELECT test_sequence.nextval INTO :NEW.ID FROM dual;
END;
/


SQL> INSERT INTO test (name) VALUES ('Jon');
1 row created.

SQL> INSERT INTO test (name) VALUES (’Bork’);
1 row created.

SQL> INSERT INTO test (name) VALUES (’Matt’);
1 row created.

SQL> SELECT * FROM test;

ID NAME
———- ——————————
1 Jon
2 Bork
3 Matt

回复收藏 0 原文