3 个解决方案
#1
生成随机五个不重复字母的字符串,参考
SELECT
REPLACE(TO_CHAR(WMSYS.WM_CONCAT(S)),',',''),--各版本通用的写法
LISTAGG(S,'')WITHIN GROUP(ORDER BY RN)--11g之后版本可以选择这样写
FROM(
select chr(ascii('A')+ROWNUM-1) S,ROW_NUMBER()OVER(ORDER BY dbms_random.value) RN from dual connect by rownum<=26
)WHERE RN<=5;
#2
需要关键字的话还是建议使用序列实现,这种随机生成的方式不建议使用
#3
with T AS(
select chr(ascii('A')+ROWNUM-1) S from dual connect by rownum<=26
UNION ALL
select chr(ascii('a')+ROWNUM-1) S from dual connect by rownum<=26
UNION ALL
select chr(ascii('0')+ROWNUM-1) S from dual connect by rownum<=10
)
SELECT
REPLACE(TO_CHAR(WMSYS.WM_CONCAT(S)),',',''),--各版本通用的写法
LISTAGG(S,'')WITHIN GROUP(ORDER BY RN)--11g之后版本可以选择这样写
FROM(
select S,ROW_NUMBER()OVER(ORDER BY dbms_random.value) RN from T
)WHERE RN<=5;
结合你实际业务的表可以一次取出符合要求的一个字符串,假设你的表名为表1,字段为keyid
with T AS(
select chr(ascii('A')+ROWNUM-1) S from dual connect by rownum<=26
UNION ALL
select chr(ascii('a')+ROWNUM-1) S from dual connect by rownum<=26
UNION ALL
select chr(ascii('0')+ROWNUM-1) S from dual connect by rownum<=10
)
select T1.S||T2.S||T3.S||T4.S||T5.S
from T T1,T T2,T T3,T T4, T T5
WHERE NOT EXITS(SELECT 1 FROM 表1 where keyid=T1.S||T2.S||T3.S||T4.S||T5.S)
AND ROWNUM=1
#1
生成随机五个不重复字母的字符串,参考
SELECT
REPLACE(TO_CHAR(WMSYS.WM_CONCAT(S)),',',''),--各版本通用的写法
LISTAGG(S,'')WITHIN GROUP(ORDER BY RN)--11g之后版本可以选择这样写
FROM(
select chr(ascii('A')+ROWNUM-1) S,ROW_NUMBER()OVER(ORDER BY dbms_random.value) RN from dual connect by rownum<=26
)WHERE RN<=5;
#2
需要关键字的话还是建议使用序列实现,这种随机生成的方式不建议使用
#3
with T AS(
select chr(ascii('A')+ROWNUM-1) S from dual connect by rownum<=26
UNION ALL
select chr(ascii('a')+ROWNUM-1) S from dual connect by rownum<=26
UNION ALL
select chr(ascii('0')+ROWNUM-1) S from dual connect by rownum<=10
)
SELECT
REPLACE(TO_CHAR(WMSYS.WM_CONCAT(S)),',',''),--各版本通用的写法
LISTAGG(S,'')WITHIN GROUP(ORDER BY RN)--11g之后版本可以选择这样写
FROM(
select S,ROW_NUMBER()OVER(ORDER BY dbms_random.value) RN from T
)WHERE RN<=5;
结合你实际业务的表可以一次取出符合要求的一个字符串,假设你的表名为表1,字段为keyid
with T AS(
select chr(ascii('A')+ROWNUM-1) S from dual connect by rownum<=26
UNION ALL
select chr(ascii('a')+ROWNUM-1) S from dual connect by rownum<=26
UNION ALL
select chr(ascii('0')+ROWNUM-1) S from dual connect by rownum<=10
)
select T1.S||T2.S||T3.S||T4.S||T5.S
from T T1,T T2,T T3,T T4, T T5
WHERE NOT EXITS(SELECT 1 FROM 表1 where keyid=T1.S||T2.S||T3.S||T4.S||T5.S)
AND ROWNUM=1