Генерация случайной строки в PL/SQL(oracle 9i) с ограниченными буквами
Я пытаюсь сгенерировать случайную строку, используя PL/SQL с некоторыми запрещенными буквами, потому что я хочу избежать таких букв, как O, o, 0 и т. Д. Я слышал, что эту задачу можно выполнить с помощью функции listagg, доступной в oracle 11g. Но я хочу добиться этого с помощью Oracle 9i, поскольку мы используем это в нашей среде. Любые предложения будут оценены.
4 ответа
Получив ответ от ABCade (он слишком далеко от оригинала, чтобы оставлять его в комментариях):
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 вызов, чтобы превратить эти строки в одну строку.
Попробуйте использовать пакет DBMS_RANDOM для генерации случайных строк.
Пример: dbms_random.string('x',10) дает заглавную буквенно-цифровую строку символов длиной 10.
Но вы не можете исключить определенные символы. Вам придется использовать TRANSLATE или же REPLACE функции для удаления нежелательных символов.
Одним из подходов было бы создание пользовательской агрегатной функции, которая имитирует listagg поведение функции. Вот пример:
Наша пользовательская агрегатная функция:
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
Если вы планируете работать со строками длиной более 4000, вы можете использовать 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
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;