在一个存储过程中如何多次使用同一个序列,

[lbd8848]

有序列: a

有语句:
insert into table1 select a.nextval,b,c,d||to_char(a.currval) from table2

出错!
如何在以上语句中多次使用序列a!

[ehsgs]

保存在变量里啊

想用多少次用多少次

[ehsgs]

另外我刚试了下

你先取nextval,然后在currval这种写法是正确的啊

[lbd8848]

谢谢
1.保存在变量里不行,因为insert into select 要读取多条,而a的序列值不会变.
2.使用currval,在存储过程里提示
Error: PLS-00357: 在此上下文中不允许表，视图或序列引用 'A.CURRVAL'

[ehsgs]

Cause: A reference to database table, view, or sequence was found in an inappropriate context. Such references can appear only in SQL statements or (excluding sequences) in %TYPE and %ROWTYPE declarations. Some valid examples follow: SELECT ename, emp.deptno, dname INTO my_ename, my_deptno, my_dept .FROM emp, dept WHERE emp.deptno = dept.deptno; DECLARE last_name emp.ename%TYPE; dept_rec dept%ROWTYPE;

Action: Remove or relocate the illegal reference.
看这个错误的解释

[wffffc]

再同一个sql里面seq的值不变的，如下所示，希望对你有点用：
SQL> select seq.nextval,seq.currval from dual;

NEXTVAL CURRVAL
---------- ----------
22 22

SQL> select seq.nextval,seq.nextval from dual;

NEXTVAL NEXTVAL
---------- ----------
23 23

SQL>

[Magina3]

太聪明了