Oracle序列:此处不允许使用CURRVAL吗? [英] Oracle sequences: CURRVAL not allowed here?

问题描述

以下Oracle SQL代码生成错误" ORA-02287:此处不允许使用序列号 ":

The following Oracle SQL code generates the error "ORA-02287: sequence number not allowed here":

INSERT INTO Customer (CustomerID,Name) VALUES (Customer_Seq.nextval,'AAA');
SELECT * FROM Customer where CustomerID=Customer_Seq.currval;

该错误发生在第二行(SELECT语句).我不太了解这个问题，因为这确实可行:

The error occurs on the second line (SELECT statement). I don't really understand the problem, because this does work:

INSERT INTO Customer (CustomerID,Name) VALUES (Customer_Seq.nextval,'AAA');
SELECT Customer_Seq.currval from dual;

推荐答案

您已发布了一些示例代码，因此尚不清楚您要实现的目标.如果您想知道分配的值，请说要传递给其他程序，您可以执行以下操作:

You have posted some sample code, so it is not clear what you are trying to achieve. If you want to know the assigned value, say for passing to some other procedure you could do something like this:

SQL> var dno number
SQL> insert into dept (deptno, dname, loc)
  2      values (deptno_seq.nextval, 'IT', 'LONDON')
  3      returning deptno into :dno
  4  /

1 row created.

SQL> select * from dept
  2  where deptno = :dno
  3  /

    DEPTNO DNAME          LOC
---------- -------------- -------------
        55 IT             LONDON

SQL>

修改

我们可以使用RETURNING子句获取任何列的值，包括那些使用默认值或触发代码设置的列.

We can use the RETURNING clause to get the values of any column, including those which have been set with default values or by trigger code.

这篇关于Oracle序列:此处不允许使用CURRVAL吗?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！