Oracle 序列：此处不允许 CURRVAL？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/1438454/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Oracle sequences: CURRVAL not allowed here?
提问by Philippe Leybaert
The following Oracle SQL code generates the error "ORA-02287: sequence number not allowed here":
以下 Oracle SQL 代码生成错误“ ORA-02287：此处不允许使用序列号”：
INSERT INTO Customer (CustomerID,Name) VALUES (Customer_Seq.nextval,'AAA');
SELECT * FROM Customer where CustomerID=Customer_Seq.currval;
The error occurs on the second line (SELECT statement). I don't really understand the problem, because this does work:
错误发生在第二行（SELECT 语句）。我真的不明白这个问题，因为这确实有效：
INSERT INTO Customer (CustomerID,Name) VALUES (Customer_Seq.nextval,'AAA');
SELECT Customer_Seq.currval from dual;
回答by APC
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>
Edit
编辑
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.
我们可以使用 RETURNING 子句获取任何列的值，包括使用默认值或触发器代码设置的值。
回答by Tony Andrews
You don't say what version of Oracle you are using. There have in the past been limitations on where sequences can be used in PL/SQL - mostly if not all gone in 11G. Also, there are restrictions in SQL - see this list.
您没有说明您使用的是哪个版本的 Oracle。过去在 PL/SQL 中可以使用序列的位置存在限制 - 大多数情况下，如果不是全部都在 11G 中消失的话。此外，SQL 中也有一些限制 - 请参阅此列表。
In this case you may need to write:
在这种情况下，您可能需要编写：
SELECT Customer_Seq.currval INTO v_id FROM DUAL;
SELECT * FROM Customer where CustomerID=v_id;
(Edited after comments).
（评论后编辑）。
回答by lcv
This doesn't really directly answer your question, but maybe what you want to do can be resolved using a the INSERT's RETURNING clause?
这并不能真正直接回答您的问题，但也许您想要做的事情可以使用 INSERT 的 RETURNING 子句来解决？
DECLARE -- ... last_rowid rowid; -- ... BEGIN -- ... INSERT INTO Customer (CustomerID,Name) VALUES (Customer_Seq.nextval,'AAA') RETURNING rowid INTO last_rowid; SELECT * FROM Customer where rowid = last_rowid; -- ... END; /
回答by dpbradley
You may not use a sequence in a WHERE clause - it does look natural in your context, but Oracle does not allow the reference in a comparison expression.
您不能在 WHERE 子句中使用序列 - 它在您的上下文中看起来很自然，但 Oracle 不允许在比较表达式中引用。
[Edit]
[编辑]
This would be a PL/SQL implementation:
这将是一个 PL/SQL 实现：
declare
v_custID number;
cursor custCur is
select customerid, name from customer
where customerid = v_custID;
begin
select customer_seq.nextval into v_custID from dual;
insert into customer (customerid, name) values (v_custID, 'AAA');
commit;
for custRow in custCur loop
dbms_output.put_line(custRow.customerID||' '|| custRow.name);
end loop;
end;
回答by CHAITANYA
You have not created any
您还没有创建任何
sequence
First create any sequence its cycle and cache. This is some basic example
首先创建任何序列的循环和缓存。这是一些基本的例子
Create Sequence seqtest1
Start With 0 -- This Is Hirarchy Starts With 0
Increment by 1 --Increments by 1
Minvalue 0 --With Minimum value 0
Maxvalue 5 --Maximum Value 5. So The Cycle Of Creation Is Between 0-5
Nocycle -- No Cycle Means After 0-5 the Insertion Stopes
Nocache --The cache Option Specifies How Many Sequence Values Will Be Stored In Memory For Faster Access
You cannot do Where Clause on Sequence in SQL beacuse you cannot filter a sequence . Use procedures like @APC said
您不能在 SQL 中对序列执行 Where 子句，因为您无法过滤序列。使用像@APC 所说的程序
