ORA

问题

1select SEQ_TT_EVENT.nextval,A.* from (
2    select 'a1','b1' from dual union all
3    select 'a2','b2' from dual union all
4    select 'a3','b3' from dual
5) A;

上面的语法是正确的，结果如下:

1select A.* from (
2    select SEQ_TT_EVENT.nextval,'a1','b1' from dual union all
3    select SEQ_TT_EVENT.nextval,'a2','b2' from dual union all
4    select SEQ_TT_EVENT.nextval,'a3','b3' from dual
5) A;

这样的语法报错：ORA-02287: 此处不允许序号.

这样也报错:

原因

是oracle不让这样使用，具体说明如下：

1Restrictions on Sequence Values You cannot use CURRVAL and NEXTVAL in the
2
3following constructs:
4■ A subquery in a DELETE, SELECT, or UPDATE statement
5■ A query of a view or of a materialized view
6■ A SELECT statement with the DISTINCT operator
7■ A SELECT statement with a GROUP BY clause or ORDER BY clause   
8■ A SELECT statement that is combined with another SELECT statement with the
9UNION, INTERSECT, or MINUS set operator
10■ The WHERE clause of a SELECT statement
11■ The DEFAULT value of a column in a CREATE TABLE or ALTER TABLE statement
12■ The condition of a CHECK constrain

解决方案就是用第一种sql那种写法。

另一种解决方案:

很多oracle语句在使用的时候会有限制，但是Function在大多数情况下没有限制，我们可以通过程序来获取nextval以及currval.

1-- 获取序列下一个值  
2create or replace function get_seq_next (seq_name in varchar2) return number  
3is  
4  seq_val number ;  
5begin  
6  execute immediate 'select '|| seq_name|| '.nextval from dual' into seq_val ;  
7  return seq_val ;  
8end get_seq_next;  
9
10SELECT id,name FROM (select get_seq_next('SEQ_B_LOG_ID') id , 'elong_deo' name from dual);  
11

https://blog.csdn.net/qq525099302/article/details/43053291

https://blog.csdn.net/ludonqin/article/details/51450719

https://blog.csdn.net/dr_lf/article/details/41380303

https://www.cnblogs.com/chaizp/p/5121601.html