在 oracle 11g 中使用触发器自动递增:ORA-00900:无效的 SQL 语句 [脚本位置:363 - 367

Question

我想创建一个表，并在oracle 11g中自动递增id。 dbvisualizer，我知道在oracle 12c中，身份是为此而存在的，但是 在 11g 中，我遵循以下情况并面临问题。

CREATE TABLE sample (
id NUMBER NOT NULL,
price number
);
ALTER TABLE sample
ADD (
CONSTRAINT sample_pk PRIMARY KEY (id)
);

CREATE SEQUENCE sample_sequence START WITH 1 INCREMENT BY 1 NOCACHE NOCYCLE;

但是当我执行下面的触发器时，会出现以下错误。

CREATE OR REPLACE TRIGGER sample_on_insert
BEFORE INSERT ON sample
FOR EACH ROW
BEGIN
select sample_sequence.nextval 
into :new.id from dual;
END;

错误：

20:16:19  SUCCESS  [CREATE - 0 rows, 0.339 secs]  OK. No rows were affected  
CREATE OR REPLACE TRIGGER sample_on_insert
BEFORE INSERT ON sample
FOR EACH ROW
BEGIN
select sample.nextval 
into :new.id from dual;
20:16:19  FAILED  [END - 0 rows, 0.830 secs]  [Code: 900, SQL State: 42000]  ORA-00900: invalid SQL statement
[Script position: 363 - 367]  
END;
20:16:20  END Execution 2 statement(s) executed, 0 row(s) affected, exec/fetch time: 1.169/0.000 secs   [1 successful, 1 errors]

当我在触发器中仅执行以下内容时：

select sample_sequence.nextval 
into :new.id from dual;

错误：

20:31:07  FAILED  [SELECT - 0 rows, 0.956 secs]  [Code: 1008, SQL State: 72000]  ORA-01008: not all variables bound
[Script position: 230 - 244]  
select sample.nextval into :new.id from dual;
20:31:08  END Execution 1 statement(s) executed, 0 row(s) affected, exec/fetch time: 0.956/0.000 secs   [0 successful, 1 errors]

即使我也以这种方式这样做，错误也是相同的：

CREATE OR REPLACE TRIGGER sample_on_insert
BEFORE INSERT ON sample
FOR EACH ROW
BEGIN
:NEW.id := sample_sequence.NextVal;
END;

Answer 1

试试这个触发器。请注意添加的"引用"子句：

create or replace trigger sample_on_insert
before insert on sample
referencing old as old and new as new
for each row
begin
:new.id := sample_sequence.nextval;
end;
/

另请注意，该错误指示您的创建触发器命令被分解为单独的提交到数据库。 dbvisualizer 需要对多行 PL/SQL 命令(多个 ";"(，如下所述：http://confluence.dbvis.com/display/UG100/Executing+Complex+Statements

Answer 2

你的代码在 11g 中工作，没问题。可能出现的问题是缺少斜杠(请参阅语句create trigger中的第 #8 行(。

SQL> select * From v$version;
BANNER
--------------------------------------------------------------------------------
Oracle Database 11g Express Edition Release 11.2.0.2.0 - 64bit Production
PL/SQL Release 11.2.0.2.0 - Production
CORE    11.2.0.2.0      Production
TNS for 64-bit Windows: Version 11.2.0.2.0 - Production
NLSRTL Version 11.2.0.2.0 - Production
SQL>

复制/粘贴代码：

SQL> CREATE TABLE sample (
2      id NUMBER NOT NULL,
3      price number
4      );
Table created.
SQL> ALTER TABLE sample
2    ADD (
3      CONSTRAINT sample_pk PRIMARY KEY (id)
4    );
Table altered.
SQL>  CREATE SEQUENCE sample_sequence START WITH 1 INCREMENT BY 1 NOCACHE NOCYCLE;
Sequence created.
SQL> CREATE OR REPLACE TRIGGER sample_on_insert
2  BEFORE INSERT ON sample
3  FOR EACH ROW
4    BEGIN
5      select sample_sequence.nextval
6      into :new.id from dual;
7    END;
8  /                                             --> this
Trigger created.

测试：

SQL> insert into sample (price) values (100);
1 row created.
SQL> select * from sample;
ID      PRICE
---------- ----------
1        100
SQL>