使用 seq 和触发器在 oracle 中自动递增 - 无效的 sql 语句

2021-12-30 00:00:00 sql oracle11g oracle

我是 oracle 的新手，来自 MySQL，我试图让自动增量在 Oracle 中使用序列和触发器工作，以便每次执行插入时它都会将我的字段加一.

I am new to oracle, comming from MySQL, and I am trying to get autoincrement to work in Oracle with a sequence and a trigger, so that it increments my field by one, each time I do an insert.

CREATE SEQUENCE proposals_seq MINVALUE 1 
START WITH 1 INCREMENT BY 1 CACHE 10;

CREATE OR REPLACE TRIGGER proposals_before_insert
BEFORE INSERT
   ON proposals
   FOR EACH ROW
BEGIN
    SELECT proposals_seq.nextval INTO :new.proposal_id FROM dual;
END;

但是当我运行脚本时，出现错误:

But when I run the script, I get the error: 

Error code 900, SQL state 42000: ORA-00900: invalid SQL statement

如果我删除;"在 select 语句之后，我没有收到任何错误，直到我尝试将数据插入表中，然后我收到此错误:

If I remove the ";" after the select statement, I get no error, until I try to insert data into the table, then I get this error: 

INSERT INTO proposals (target_audience, line_of_business, activity_description, cost, comments, objectives_and_results) 
    VALUES ('test', 'test', 'test', 15, 'test', 'test');

Error code 4098, SQL state 42000: ORA-04098: trigger 'PROPOSALS_BEFORE_INSERT' is invalid and failed re-validation

我正在使用这个版本的 Oracle:Oracle 数据库 11g 企业版 11.2.0.1.0 版 - 64 位生产

I am using this version of Oracle: Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production

我找到的所有关于它的文章，似乎都是这样做的，这里也有答案:如何在Oracle 上用AUTO_INCREMENT 创建id?

All the articles I have found about it, seems to be doing it like this and answers in here aswell: How to create id with AUTO_INCREMENT on Oracle?

会不会是因为我的 Oracle 版本?我还有另一种方法可以自动增加它吗?或者我是否必须使用我的 sql 中的序列手动递增?

Can it be because of my version of Oracle? Is there another way for me to autoincrement this? Or will I have to increment manually with the sequence in my sql?

我的表是这样的:

CREATE TABLE proposals (
    proposal_id INT NOT NULL,
    target_audience VARCHAR2(50) NOT NULL,
    line_of_business VARCHAR2(50),
    activity_description VARCHAR2(250) NOT NULL,
    cost DECIMAL(19, 4) NOT NULL,
    comments VARCHAR2(250),
    objectives_and_results VARCHAR2(250),
    PRIMARY KEY (proposal_id)
);

推荐答案

我怀疑问题是你的客户端工具读取每个分号作为命令的结尾，导致 PL/SQL 代码(需要分号作为语句终止符)错误地传输到服务器.

I suspect the problem is that your client tool is reading every semicolon as the end of a command, causing PL/SQL code (which requires semicolons as statement terminators) to be incorrectly transmitted to the server.

当您删除分号时，语句会正确发送到服务器，但由于 PL/SQL 不正确，您最终会得到一个无效对象.

When you remove the semicolon, the statement is correctly sent to the server, but you end up with an invalid object because the PL/SQL is incorrect.

我在 SQL Fiddle 上复制了您的问题.然后我将语句终止符更改为 / 而不是 ; 并将代码更改为使用斜杠来执行每个语句，并且它没有错误地工作:

I duplicated your problem on SQL Fiddle. Then I change the statement terminator to / instead of ; and changed the code to use a slash to execute each statement, and it worked without error:

CREATE TABLE proposals (
    proposal_id INT NOT NULL,
    target_audience VARCHAR2(50) NOT NULL,
    line_of_business VARCHAR2(50),
    activity_description VARCHAR2(250) NOT NULL,
    cost NUMBER(19, 4),
    comments VARCHAR2(250),
    objectives_and_results VARCHAR2(250),
    PRIMARY KEY (proposal_id)
)
/

CREATE SEQUENCE proposals_seq MINVALUE 1 
START WITH 1 INCREMENT BY 1 CACHE 10
/

CREATE OR REPLACE TRIGGER proposals_before_insert
BEFORE INSERT ON proposals FOR EACH ROW
BEGIN
    select proposals_seq.nextval into :new.proposal_id from dual;
END;
/

相关文章