Oracle:生成以特定数字开头的数字序列

huangapple go评论73阅读模式
英文:

Oracle: generate a numeric sequence whose values start with a certain digit

问题

我需要在Oracle表的一列中定义一个数字序列。
我收到的唯一约束是所有值都必须以9开头。
因此,自动生成的值列表应该从以下开始:

9,90,91,92...99,900,901...999,9000,9001...9999,90000...

有没有关于如何通过SQL(通过适当创建和编辑序列)和/或Java代码解决这个问题的想法?
在我在网上阅读的一些帖子中,人们建议以这种方式修改序列:

ALTER SEQUENCE gokhan.sample_seq INCREMENT BY -500;
SELECT gokhan.sample_seq.NEXTVAL FROM dual;
ALTER SEQUENCE gokhan.sample_seq INCREMENT BY 1;

这需要每次计算新的增量。如果可能,我想编写一部分SQL代码来自动化这个过程。

谢谢支持。

英文:

I need to define a numeric sequence for a column of my table in Oracle.
The only contraint I've been given requires all values to start with 9.
So, the list of autogenerated values should start like the following:

9, 90, 91, 92...99, 900, 901...999, 9000, 9001...9999, 90000,...

Any idea about how to solve this problem via SQL (by creating opportunely and editing by sequence) and/or by Java code?
In some posts I've read on web, people suggest to modify sequence this way:

ALTER SEQUENCE gokhan.sample_seq INCREMENT BY -500;
SELECT gokhan.sample_seq.NEXTVAL FROM dual;
ALTER SEQUENCE gokhan.sample_seq INCREMENT BY 1;

This requires the computation of new increment every time. If possible, I'd like to write a portion of SQL code that automates such process.

Thanks for support.

答案1

得分: 1

以下是您要翻译的代码部分:

public class Test {

    //检查给定数字是否为十的幂的方法:
    public static boolean powerOf10(int n) {
        int max_power10 = 1_000_000_000;
        if (n > max_power10 ) return false;
        int i = 1;
        while (i < n) i *= 10;   
        return i == n;
    }
 
    //然后,您可以通过加一或加一并乘以9来计算下一个值,如果上一个值加一后是十的幂的话。
    public static int nextVal(int lastVal){        
        if(!powerOf10(lastVal + 1)){
            return lastVal + 1;
        }
        return 9 * (lastVal + 1);
    }

    //注意:为了提高可读性,我省略了检查上一个值是否为有效值(以9开头的数字)的部分。

    //演示
    public static void main(String[] args){
        int MIN_VALUE = 9;
        for(int i = MIN_VALUE; i < 10000; i = nextVal(i)){
            System.out.println(i);
        }
    }
}
英文:

The only difficulty I see if you want to implement it in java is to calculate the next value for a given number. But luckily your borders are the powers of ten (10, 100, 1000 ...) otherwise you increment by one. Something like the following should give you an initial approach

public class Test {

//A method to check if a given number is a power of ten :

    public static boolean powerOf10(int n) {
        int max_power10 = 1_000_000_000;
        if (n &gt; max_power10 ) return false;
        int i = 1;
        while (i &lt; n) i *= 10;   
        return i == n;
    }

 //Then you can calculate the next value by either adding one or 
 //adding one and multipling by 9 if last value plus one is a power of ten

    public static int nextVal(int lastVal){        
        if(!powerOf10(lastVal + 1)){
            return lastVal + 1;
        }
        return 9 * (lastVal + 1);
    }

//Note: for the sake of readability i omitted the check if the last value is a valid value (a number starting with 9)

//Demo 
    public static void main(String[] args){
        int MIN_VALUE = 9;
        for(int i = MIN_VALUE; i &lt; 10000; i = nextVal(i)){
            System.out.println(i);
        }
    }
}

答案2

得分: 0

使用正常序列 0、1、2、3、……,对于从此序列中检索到的任何 x,直接计算您的花式序列的映射元素。

当序列的初始值为0时,元素1-10映射到90-99,元素11-110映射到900-999,元素111-1110映射到9000-9999,依此类推。因此,对于输入序列的每个 x(例如500),找到区间的下限(111),计算下限的对等值(900)并计算对等值的偏移量(9389)。

代码:

with sequence (x) as (
  select level - 1 from dual connect by level &lt; 1001
), o (x,ones) as (
  select x, case x when 0 then 0 else to_number(rpad(&#39;1&#39;,to_char(1 + trunc(log(10,x))),&#39;1&#39;)) end as ones
  from sequence
), m (x,ones,min) as (
  select x, ones, case when x &gt;= ones then ones else (ones - 1) / 10 end as min 
  from o
)
select x, case x when 0 then 9 else x - min + 9 * power(10, 1 + trunc(log(10,min))) end as result from m;

将其翻译为PLSQL由您决定。请注意,建议将 9 连接到正常序列的数字中并不能产生所需的结果,因为这样的序列增长速度会快10/9倍(在元素为9、90、900、...时,数字的位数会增加1,而在您的序列中,位数在元素为10、100、1000、...时增加1)。尽管您没有描述这种逻辑的原因,但我仍然建议考虑用更简单的逻辑来替换。

英文:

Use normal sequence 0,1,2,3,... and for any x retrieved from this sequence compute the mapped element of your fancy sequence directly.

When initial value of sequence is 0, then elements 1-10 map to 90-99, elements 11-110 to 900-999, elements 111-1110 to 9000-9999 etc. Hence for each x of input sequence (for example 500) find the lower bound of the interval (111), compute peer of lower bound (900) and compute offset of peer (9389).

Code:

with sequence (x) as (
  select level - 1 from dual connect by level &lt; 1001
), o (x,ones) as (
  select x, case x when 0 then 0 else to_number(rpad(&#39;1&#39;,to_char(1 + trunc(log(10,x))),&#39;1&#39;)) end as ones
  from sequence
), m (x,ones,min) as (
  select x, ones, case when x &gt;= ones then ones else (ones - 1) / 10 end as min 
  from o
)
select x, case x when 0 then 9 else x - min + 9 * power(10, 1 + trunc(log(10,min))) end as result from m;

Translating to PLSQL is up to you. Note the proposal of concatenating 9 to digits of normal sequence doesn't produce required result since such sequence would grow 10/9 times quicker (number of digits would increase by 1 after 9,90,900,... elements whereas in your sequence the number of digits increases by 1 after 10,100,1000,... elements). It would be lots simpler though. Despite you didn't describe reasons for such a logic, I would consider replacing by simpler logic anyway.

huangapple
  • 本文由 发表于 2020年8月28日 18:52:56
  • 转载请务必保留本文链接:https://go.coder-hub.com/63632385.html
匿名

发表评论

匿名网友

:?: :razz: :sad: :evil: :!: :smile: :oops: :grin: :eek: :shock: :???: :cool: :lol: :mad: :twisted: :roll: :wink: :idea: :arrow: :neutral: :cry: :mrgreen:

确定