PROSAGA码农传奇-plsql-实现一个按函数值递增的序列

<div class =“post-text”itemprop =“text”>
  
    这个怎么样？不是真的
    
      序列
    
    ，但是 - 可能适合您的需求。它是一个选择随机数并将其存储到具有主键的表中的函数。如果已使用该号码，则跳过该号码。该函数检查是否已使用所有数字;如果是这样，它会引发错误。
  
  
    在这个例子中，我正在创建一个5个数字的“序列”（因此很快就会失败）。
  
  
    表＆amp;功能：
  
   <pre>
 <code>
 SQL> create table t_seq (supseq number constraint pk_tseq primary key);

Table created.

SQL> create or replace function f_supseq
 2 return number
 3 as
 4 l_range number := 5; -- number of values in a "sequence"
 5 l_seq number; -- a new "sequence" number
 6 l_cnt number; -- number of used numbers
 7 pragma autonomous_transaction;
 8 begin
 9 select count(*) into l_cnt from t_seq;
 10 if l_cnt < l_range then
 11 -- there are still some available numbers so - let's get them
 12
 13 -- don't let anyone mess with the table
 14 lock table t_seq in exclusive mode;
 15 while l_seq is null loop
 16 begin
 17 insert into t_seq (supseq) values
 18 (round(dbms_random.value(1, l_range)))
 19 returning supseq into l_seq;
 20 exception
 21 when dup_val_on_index then
 22 -- that number has already been used; skip it
 23 null;
 24 end;
 25 end loop;
 26 commit;
 27 else
 28 raise_application_error(-20001, 'No more available numbers');
 29 end if;
 30
 31 return l_seq;
 32 end;
 33 /

Function created.

</code>
 </pre>
  
    获取随机“序列”值：
  
   <pre>
 <code>
 SQL> select f_supseq from dual;

F_SUPSEQ
----------
         2

SQL> select f_supseq from dual;

F_SUPSEQ
----------
         4

SQL> select f_supseq from dual;

F_SUPSEQ
----------
         1

SQL> select f_supseq from dual;

F_SUPSEQ
----------
         3

SQL> select f_supseq from dual;

F_SUPSEQ
----------
         5

SQL> select f_supseq from dual;
select f_supseq from dual
       *
ERROR at line 1:
ORA-20001: No more available numbers
ORA-06512: at "SCOTT.F_SUPSEQ", line 28

</code>
 </pre>
  
    表内容：
  
   <pre>
 <code>
 SQL> select * From t_seq;

SUPSEQ
----------
         1
         2
         3
         4
         5

SQL>

</code>
 </pre>
</DIV>