我正在尝试使用PL/SQL生成一个随机字符串，但是我想要避免像O、o、0等字母。听说在Oracle 11g中可以使用listagg函数来实现这个任务，但是我们的环境正在使用Oracle 9i，我希望能够在这个版本下实现。如果有任何建议，将不胜感激。
4个回答
3
尝试使用DBMS_RANDOM包生成随机字符串。
例如：dbms_random.string('x',10)可以生成长度为10的大写字母和数字组成的随机字符串。
但是您无法排除特定字符。您需要使用TRANSLATE或REPLACE函数来删除不必要的字符。
- Noel
3
这似乎是迄今为止最简单的方法——生成一个过长的随机字符集，然后简单地替换不需要的字符。我不知道为什么有人要费心去寻找奇特的解决方案。 - David Aldridge
@DavidAldridge - 但是如果要使用混合大小写和数字，您需要使用
p，然后还必须排除非字母数字字符（例如 使用 regex_replace）。这对于最初的要求来说还可以，除了您仍然必须列出允许的字符，但无法处理迟到的不重复部分。（我并不是说这些要求很明智... *8-) - Alex Poole1而且甚至 Oracle 版本也不是。 - David Aldridge
2
其中一种方法是创建一个用户自定义聚合函数来模拟listagg函数的行为。以下是一个示例：
Our user-defined aggregate function:
SQL> create or replace type stragg as object ( 2 3 str varchar2(4000), 4 5 static function ODCIAggregateInitialize(sctx in out stragg) 6 return number, 7 8 member function ODCIAggregateIterate (self in out stragg, 9 value in varchar2 ) 10 return number, 11 12 member function ODCIAggregateTerminate (self in stragg , 13 return_value out varchar2, 14 flags in number ) 15 return number, 16 17 member function ODCIAggregateMerge(self in out stragg, 18 ctx2 in stragg ) 19 return number 20 ) 21 / Type created SQL> create or replace type body stragg is 2 3 static function ODCIAggregateInitialize(sctx in out stragg) 4 return number is 5 begin 6 sctx := stragg(null); 7 return ODCIConst.Success; 8 end; 9 10 member function ODCIAggregateIterate( 11 self in out stragg, value in varchar2) 12 return number is 13 begin 14 str := str || value; 15 return ODCIConst.Success; 16 end; 17 18 member function ODCIAggregateTerminate(self in stragg, 19 return_value out varchar2, flags in number) return number is 20 begin 21 return_value := str; 22 return ODCIConst.Success; 23 end; 24 25 member function ODCIAggregateMerge(self in out stragg, 26 ctx2 in stragg) return number is 27 begin 28 str := str || ctx2.str; 29 return ODCIConst.Success; 30 end; 31 end; 32 / Type body created SQL> create or replace function str_agg (input varchar2) return varchar2 2 parallel_enable aggregate using stragg; 3 / Function created
CLOB数据类型代替varchar2。现在，您可以创建一个额外的函数，使用str_agg聚合函数为您生成随机字符串。由于您想要从结果字符串中排除一些字符，因此最好提供一组字符，用于生成所需的字符串。SQL> create or replace function Str_gen(p_CharSet varchar2, p_length number)
2 return varchar2
3 is
4 l_res_str varchar2(4000);
5 begin
6 select str_agg(symbol)
7 into l_res_str
8 from ( select substr(p_CharSet,
9 dbms_random.value(1, length(p_charset)), 1) as symbol
10 from dual
11 connect by level <= p_length
12 order by dbms_random.value
13 );
14
15 return l_res_str;
16 end;
17 /
Function created
以下是它的工作原理：
SQL> select str_gen('abcdefghijklmnpqrstuvwxyzABCDEFGHIJKLMNPQRSTUVWXYZ123456789',11) as res
2 from dual
3 connect by level <= 11
4 /
RES
--------------------------------------------------------------------------------
NBG5jK6G46G
fSrzmyq7ZLE
vdGE1dRXlah
1D2IsI54qzD
PhktBAh5rXu
JtRsarxFNiV
1sUGFpwmypQ
7giwfdV4I7s
I2WMhKzxvc2
NZpngmrq1gM
rFuZ8gSUDgL
11 rows selected
- Nick Krasnov
6
1不错的解决方案。但是这将仅返回与指定长度相同的输入字符串中的字符。在此示例中，长度为11，因此输出字符串仅包含从a到k的字符。那么，将查询“substr(p_CharSet，level，1)”中的“level”替换为随机数如何？ - Noel
@Noel "把查询中的级别 substr(p_CharSet，level，1) 替换为一个随机数如何？"。是的，这样做会更好。 - Nick Krasnov
Nicholas，上述方法只能生成全大写的随机字符串。我想要生成一个包含大写字母、小写字母和数字组合的字符串。有什么建议吗？ - Devakumar Mahadevan
@DevakumarMahadevan 为了能够生成混合大小写的字符串，您可以通过添加大写字母来扩展源字符集。例如：
abcdefghijklmnpqrstuvwxyzABCDEFGHIJKLMNPQRSTUVWXYZ123456789 - Nick KrasnovNicholas，有些字母会一遍又一遍地重复出现，即使生成的字符串长度为8。如何避免字符串中的重复字符？ - Devakumar Mahadevan
3消除重复的字母会使你的字符串不再是真正的随机。 - David Aldridge
2
借鉴A.B.Cade的回答（留言区已经离原问题有些远了）：
select xmlagg(xmlelement("r", ch)).extract('//text()').getstringval()
from
(
select distinct first_value(ch) over (partition by lower(ch)) as ch
from (
select substr('abcdefghijklmnpqrstuvwxyzABCDEFGHIJKLMNPQRSTUVWXYZ123456789',
level, 1) as ch
from dual
connect by level <= 59
order by dbms_random.value
)
where rownum <= dbms_random.value(10,13)
);
内部查询将值字符串中的每个字符随机排序；下一级使用first_value()和distinct来选择大写和小写字母对(a/A)中的第一个，以此避免重复，即使忽略大小写；然后限制为前10、11或12(不同的)行；最后使用相同的xmlagg调用将这些行转换为单个字符串。
- Alex Poole
1
感谢Alex宝贵的时间和回复。我已经得到了今天所需的内容... - Devakumar Mahadevan
1
create or replace function str_gen
( len in number)
return varchar2
as
str varchar2(4000);
valid_chars constant varchar2(50) := 'abcdefghijklmnpqrstuvwxyz';
begin
for i in 1..len
loop
str := str || substr( valid_chars, dbms_random.value(1, length(valid_chars)), 1);
end loop;
return str;
end;
/
不直接重复相同的字母
create or replace function str_gen
( len in number)
return varchar2
as
str varchar2(4000);
valid_chars constant varchar2(50) := 'abcdefghijklmnpqrstuvwxyz';
last_char varchar2(1);
new_char varchar2(1);
chars_len NUMBER;
num NUMBER;
begin
chars_len := length(valid_chars);
num := 0;
if len > 0 then
loop
new_char := substr( valid_chars, dbms_random.value(1, chars_len ), 1);
if num = 0 then
str := new_char;
num := num +1;
elsif new_char != last_char
then
str := str || new_char;
num := num +1;
end if;
last_char := new_char;
exit when num = len;
end loop;
end if;
return str;
end;
/
select str_gen(11) from dual;
- schurik
1
如何生成一个字符串，让某个字母不会一遍又一遍地重复出现？ - Devakumar Mahadevan
网页内容由stack overflow 提供, 点击上面的可以查看英文原文，
原文链接
原文链接