时间戳

首页 > 解决方案 > Oracle: Statements running into each other

oracle - Oracle: Statements running into each other

问题描述

I am not fluent with Oracle, and am testing out an auto-incrementing trigger required for older versions, where IDENTITY is not yet available.

CREATE TABLE stuff (
    id int PRIMARY KEY,
    data varchar(255)
);

CREATE SEQUENCE stuff_sequence;
CREATE TRIGGER test
BEFORE INSERT ON stuff
FOR EACH ROW
BEGIN
    SELECT stuff_sequence.nextval INTO :new.id FROM dual;
END;

INSERT INTO stuff(data) VALUES('test');

Using SQL Developer, I can run the CREATE TABLE statement, and then the CREATE SEQUENCE statement, but when I run the CREATE TRIGGER statement, it highlights the INSERT statement as well, and then complains about `Encountered the symbol "INSERT". Obviously, if I try to run the whole lot as a single script I get the same error.

What is happening here, and how do I fix it?

标签： oracleoracle-sqldeveloperdatabase-trigger

解决方案

只需放在/触发代码的末尾即可。这代表它上面的代码的结尾。

CREATE TRIGGER test
BEFORE INSERT ON stuff
FOR EACH ROW
BEGIN
    SELECT stuff_sequence.nextval INTO :new.id FROM dual;
END;
/ -- this

INSERT INTO stuff(data) VALUES('test');

/在输入 DML 或 DDL 或 PL/SQL 期间意味着“终止当前语句，执行它并将其存储到 SQLPLUS 缓冲区”，并且/需要在多行代码中表示代码到此结束。

干杯！！

推荐阅读