代码之家 › 专栏 › 技术社区 › Philippe Leybaert

Oracle序列:此处不允许使用CURRVAL?

sequence oracle

Philippe Leybaert · 技术社区 · 16 年前

ORA-02287:此处不允许使用序列号

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

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

5 回复 | 直到 16 年前

APC 16 年前

您已经发布了一些示例代码,因此不清楚您想要实现什么。如果你想知道分配的值,比如传递给其他过程,你可以这样做:

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>

编辑

Tony Andrews 16 年前

你不用说你使用的是哪个版本的Oracle。过去,在PL/SQL中使用序列的位置存在局限性,即使不是全部,也大多在11G中消失了。此外,SQL中也有限制-请参阅 this list .

SELECT Customer_Seq.currval INTO v_id FROM DUAL; 
SELECT * FROM Customer where CustomerID=v_id;

(评论后编辑)。

lcv 16 年前

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;
/

dpbradley 16 年前

您可能不会在WHERE子句中使用序列——它在您的上下文中看起来很自然,但Oracle不允许在比较表达式中引用。

[编辑]

这将是一个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;

-2

CHAITANYA 12 年前

您尚未创建任何

sequence

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

您无法在SQL中对序列执行Where子句,因为您无法筛选序列。使用@APC所说的程序