Oracle 生成随机数并比较数据库中的唯一性

嗨，汤姆，

我有一种情况，我必须创建一个函数，它将返回一个唯一的数字 (后缀R和7个数字)，这将比数据库中存在的数据与主键的特定列进行比较。如果函数生成的数字是唯一的，并且在数据库中不存在，则一个单独的过程将其插入数据库，否则它将通过一个错误，而不是函数需要生成一个新值。

我试过使用
'R' | | TRUNC(DBMS_RANDOM.VALUE (低 => 1，高 => 10))| | ROUND(DBMS_RANDOM.VALUE (低 => 100，高 => 9999))

但不能管理所有条件。


创建或替换函数FN_GET_NEW_IRD_NO (VARCHAR2中的pSeriesNo) 返回VARCHAR2

作为
里尔诺·瓦尔查尔2(50);
lDTH_Irdno VARCHAR2(14);
lIPTV_Irdno VARCHAR2(14);
返回VARCHAR2(200);
lSmartNoDTH VARCHAR2(15);
lSmartNoIPTV VARCHAR2(15);
lDecVal编号;

开始

如果长度 (pSeriesNo)> 5，则
返回 “ird系列编号应小于或等于5”;
如果结束;

lIrdNo := 'R' | | pSeriesNo | | TRUNC(DBMS_RANDOM.VALUE (低 => 1，高 => 10))| | ROUND(DBMS_RANDOM.VALUE (低 => 100，高 => 9999));

选择最大 (ird_no) 进入lDTH_Irdno
来自sapadmin。eqt_movement_app
其中ird_no LIKE 'R' | | lIrdNo;

选择最大 (ird_no) 进入lIPTV_Irdno
来自sapadmin。eqt_movement_app
其中ird_no = lIrdNo;

DBMS_OUTPUT.PUT_LINE (“确定”);

如果lDTH_Irdno不为NULL，则
在lSmartNoDTH中选择唯一的SMART_CARD_NO
来自sapadmin。eqt_movement_app
其中ird_no = lDTH_Irdno和ROWNUM = 1;
其他
DBMS_OUTPUT.PUT_LINE ('ird_no为空 ');
如果结束;

如果lIPTV_Irdno不为NULL，则
在lSmartNoIPTV中选择唯一的SMART_CARD_NO
来自sapadmin。eqt_movement_app
其中ird_no = lIPTV_Irdno和ROWNUM = 1;
如果结束;

从对偶选择HEX2DEC(lIPTV_Irdno) 进入lDecVal;
从对偶选择DEC2HEX(lDecVal 1) 到lIPTV_Irdno;
lDTH_Irdno := SUBSTR(lDTH_Irdno，2，长度 (lDTH_Irdno)) 1;
lSmartNoDTH := SUBSTR(lSmartNoDTH，2，LENGTH(lSmartNoDTH)) 1;
lSmartNoIPTV := SUBSTR(lSmartNoIPTV，2，长度 (lSmartNoIPTV)) 1;

返回值: = 'DTH IRD编号:' | | lDTH_Irdno | | '，IPTV IRD编号:' | | lIPTV_Irdno | | 'DTH Smart No : '
| | lSmartNoDTH | | '，IPTV Smart No :' | | lSmartNoIPTV;
返回lReturnVal;

结束FN_GET_NEW_IRD_NO;

这是一个奇怪的要求，因为你会在1000万后没有数字-我鼓励你重新考虑这种方法。

无论如何，由于它只有1000万个数字，因此这是一种不同的策略-我们将按随机顺序对它们进行预生成，而您只需根据需要分别选择一个。

我已经在下面做了一百万，但你明白了

SQL> create global temporary table t1
  2  on commit preserve rows as
  3  select 'R'||to_char(rownum,'FM0000000') tag
  4  from ( select 1 from dual connect by level <= 1000),
  5       ( select 1 from dual connect by level <= 1000)
  6  where rownum < 1000000
  7  order by dbms_random.value;

Table created.

SQL>
SQL> create table t (
  2    id  int          not null,
  3    tag varchar2(10) not null,
  4    constraint t_pk primary key ( id )
  5  )
  6  organization index;

Table created.

SQL>
SQL> insert into t
  2  select rownum, tag from t1;

999999 rows created.

SQL>
SQL> exec dbms_stats.gather_table_stats('','T')

PL/SQL procedure successfully completed.

SQL>
SQL> select /*+ index(t) first_rows(10) */ id, tag
  2  from t
  3  where rownum <= 10 ;

        ID TAG
---------- ----------
         1 R0919471
         2 R0594711
         3 R0403352
         4 R0535468
         5 R0954674
         6 R0585909
         7 R0254793
         8 R0868272
         9 R0046145
        10 R0077835

10 rows selected.

SQL>
SQL> variable my_key varchar2(10)
SQL> declare
  2    rc sys_refcursor;
  3    pk int;
  4  begin
  5    loop
  6      open rc for
  7        select /*+ index(t) first_rows(10) */ id, tag
  8        from t
  9        where rownum <= 5 for update skip locked;
 10
 11      fetch rc into pk, :my_key;
 12      if rc%found then
 13        delete from t where id = pk;
 14        close rc;
 15        exit;
 16      else
 17        close rc;
 18      end if;
 19   end loop;
 20  end;
 21  /

PL/SQL procedure successfully completed.

SQL> print my_key

MY_KEY
--------------------------------
R0919471

该会话将 * 拥有 * 该密钥，直到它提交或回滚。没有人能得到它。