IT技术博客

业务需求，通常在Oracle里主键都是通过序列实现，现在要求使用四位的字母组合来实现。也就是范围从A-ZZZZ，SQL脚本如下：

with a as (
select chr(l) al from (
select level l from dual connect by level<=90) t where t.l>=65),
b as (
select al from a 
union all
select t1.al||t2.al from a t1,a t2
union all
select t1.al||t2.al||t3.al from a t1,a t2,a t3
union all
select t1.al||t2.al||t3.al||t4.al from a t1,a t2,a t3,a t4),
c as (select al,rownum id from b) ,
d as (
select al pre ,lead(al,1,'ZZZZ') over(order by id) next  from c )
select next 下一个主键 from d where d.pre='ZZZC'

这里主要使用了Oracle的lead函数，它的主要作用是将某一列的数据下移。