(8.0.26)
引数: コロン区切りの発生比率
戻り値： 該当インデックス番号(1始まり)
drop function func1;
delimiter //
create function func1( param1 varchar(1000) )
returns bigint
READS SQL DATA
begin
declare input varchar(4000);
declare input2 varchar(4000);
declare colon_cnt int;
declare prob int;
declare prob_sum int;
declare ransu decimal(20,20);
declare bunsi_from int;
declare bunsi_to int;
declare ret int default 1;
declare vcol1 int;
declare vdone1 int;
declare cur1 cursor for select col1 from ar1;
declare continue handler for sqlstate '02000' set vdone1 = 1;
drop temporary table if exists ar1;
create temporary table if not exists ar1(col1 int );
-- コロンの数をカウント
set input := param1;
set input2 := replace(input,':','');
set colon_cnt := LENGTH(input) - LENGTH(input2);
-- コロンの数だけ繰り返し、発生確率を配列に格納
set prob_sum := 0;
set @i := 1;
while @i <= colon_cnt+1 do
if @i < colon_cnt+1 then
set prob := cast(substring(input,1, instr(input,':')-1 ) as unsigned int) ;
else
set prob := cast(input as unsigned int) ;
end if;
-- 次のループのために、取得済み部分を削除
set input := substring(input, instr(input,':')+1 );
insert into ar1 values(prob);
set prob_sum := prob_sum + prob;
set @i := @i+1;
end while;
-- 0～1の乱数を生成し、該当するインデックス番号を求める
set ransu := (select rand() );
set bunsi_from := 0;
set bunsi_to := 0;
set @i := 1;
set vdone1 := 0;
open cur1;
fetch cur1 into vcol1;
while vdone1 != 1 do
set bunsi_to := bunsi_to + vcol1;
if cast(bunsi_from/prob_sum as decimal(20,20) ) <= ransu and ransu < cast(bunsi_to/prob_sum as decimal(20,20) ) then
set ret := @i;
set vdone1 := 1;
end if;
set bunsi_from := bunsi_to;
fetch cur1 into vcol1;
set @i := @i+1;
end while;
close cur1;
return ret;
end
//
delimiter ;
select func1('1:2:3:4:5');
-- 動作確認
ファンクションを 百万回実行し、指定した発生確率となっていることを確認
drop procedure proc1;
delimiter //
create procedure proc1()
begin
declare i1 int default 0;
declare i2 int default 0;
declare i3 int default 0;
declare i4 int default 0;
declare i5 int default 0;
set @k := 1;
while @k <= 1000000 do
prepare stmt from "select func1('1:2:3:4:5') into @val";
execute stmt;
deallocate prepare stmt;
case @val
when 1 then set i1 := i1+1;
when 2 then set i2 := i2+1;
when 3 then set i3 := i3+1;
when 4 then set i4 := i4+1;
when 5 then set i5 := i5+1;
end case;
set @k:= @k+1;
end while;
select i1;
select i2;
select i3;
select i4;
select i5;
end
//
delimiter ;
call proc1();
※「select func1('1:2:3:4:5')」の戻り値を直接set文やinto句で変数に代入すると下記エラーとなった
「ERROR 1264 (22003): Out of range value for column '(null)' at row 1」
原因不明。
プリペアド文で実行すると正常実行できた。
なおバージョン5.6であれば、直接set文で代入できた。
(19c)
引数: コロン区切りの発生比率
戻り値： 該当インデックス番号(1始まり)
create or replace function func1(param1 in varchar2)
return integer
authid current_user
as
input varchar2(4000);
input2 varchar2(4000);
colon_cnt int;
type type1 is table of integer;
ar1 type1 := type1();
prob int;
prob_sum int;
ransu number;
bunsi_from int;
bunsi_to int;
ret int := 1;
begin
-- コロンの数をカウント
input := param1;
input2 := replace(input,':','');
colon_cnt := LENGTH(input) - LENGTH(input2);
-- コロンの数だけ繰り返し、発生確率を配列に格納
prob_sum := 0;
for i in 1..colon_cnt+1 loop
-- i番目の数値を取得
if i < colon_cnt+1 then
prob := to_number( substr(input,1, instr(input,':')-1 ) );
else
prob := to_number(input);
end if;
-- 次のループのために、取得済み部分を削除
input := substr(input, instr(input,':')+1 );
ar1.extend;
ar1(ar1.count) := prob;
prob_sum := prob_sum + prob;
end loop;
-- 0～1の乱数を生成し、該当するインデックス番号を求める
select dbms_random.value() into ransu from dual;
bunsi_from := 0;
bunsi_to := 0;
for i in ar1.first..ar1.last loop
bunsi_to := bunsi_to + ar1(i);
if bunsi_from/prob_sum <= ransu and ransu < bunsi_to/prob_sum then
ret := i;
exit;
end if;
bunsi_from := bunsi_to;
end loop;
return ret;
end;
/
show error;
select func1('1:2:3:4:5') from dual;
-- 動作確認
ファンクションを百万回実行し、指定した発生確率となっていることを確認
set serveroutput on
declare
i1 int :=0;
i2 int :=0;
i3 int :=0;
i4 int :=0;
i5 int :=0;
val int;
begin
for i in 1..1000000 loop
select func1('1:2:3:4:5') into val from dual;
case val
when 1 then i1 := i1+1;
when 2 then i2 := i2+1;
when 3 then i3 := i3+1;
when 4 then i4 := i4+1;
when 5 then i5 := i5+1;
end case;
end loop;
dbms_output.put_line('i1=' || to_char(i1) );
dbms_output.put_line('i2=' || to_char(i2) );
dbms_output.put_line('i3=' || to_char(i3) );
dbms_output.put_line('i4=' || to_char(i4) );
dbms_output.put_line('i5=' || to_char(i5) );
end;
/
(14)
引数: コロン区切りの発生比率
戻り値： 該当インデックス番号(1始まり)
create or replace function func1(param1 in varchar)
returns integer
language plpgsql
as $$
declare
input varchar(4000);
input2 varchar(4000);
colon_cnt int;
ar1 int[];
prob int;
prob_sum int;
ransu numeric;
bunsi_from int;
bunsi_to int;
ret int := 1;
begin
-- コロンの数をカウント
input := param1;
input2 := replace(input,':','');
colon_cnt := LENGTH(input) - LENGTH(input2);
-- コロンの数だけ繰り返し、発生確率を配列に格納
prob_sum := 0;
for i in 1..colon_cnt+1 loop
-- i番目の数値を取得
if i < colon_cnt+1 then
prob := substr(input,1, position(':' in input)-1 )::int ;
else
prob := input::int ;
end if;
-- 次のループのために、取得済み部分を削除
input := substr(input, position(':' in input)+1 );
ar1[i] := prob;
prob_sum := prob_sum + prob;
end loop;
-- 0～1の乱数を生成し、該当するインデックス番号を求める
select random() into ransu;
bunsi_from := 0;
bunsi_to := 0;
for i in array_lower(ar1,1)..array_upper(ar1, 1) loop
bunsi_to := bunsi_to + ar1[i];
if bunsi_from/prob_sum::numeric <= ransu and ransu < bunsi_to/prob_sum::numeric then
ret := i;
exit;
end if;
bunsi_from := bunsi_to;
end loop;
return ret;
end;
$$
;
select func1('1:2:3:4:5');
-- 動作確認
ファンクションを百万回実行し、指定した発生確率となっていることを確認
do $$
declare
i1 int :=0;
i2 int :=0;
i3 int :=0;
i4 int :=0;
i5 int :=0;
val int;
begin
for i in 1..1000000 loop
select func1('1:2:3:4:5') into val;
case val
when 1 then i1 := i1+1;
when 2 then i2 := i2+1;
when 3 then i3 := i3+1;
when 4 then i4 := i4+1;
when 5 then i5 := i5+1;
end case;
end loop;
raise info 'i1= %', i1;
raise info 'i2= %', i2;
raise info 'i3= %', i3;
raise info 'i4= %', i4;
raise info 'i5= %', i5;
end
$$
;
(2019)
引数: コロン区切りの発生比率
戻り値： 該当インデックス番号(1始まり)
※関数内でrandを実行できないため、ビューとして切り出す
create or alter view v_rand(col1) as select rand();
go
create or alter function func1(@param1 varchar(1000) )
returns integer
as
begin
declare @input varchar(4000);
declare @input2 varchar(4000);
declare @colon_cnt int;
declare @prob int;
declare @prob_sum int;
declare @ransu decimal(20,20);
declare @bunsi_from int;
declare @bunsi_to int;
declare @ret int = 1;
declare @vcol1 int;
declare @i int;
declare @ar1 table(
col1 int
);
declare cur1 cursor for select col1 from @ar1;
-- コロンの数をカウント
set @input = @param1;
set @input2 = replace(@input,':','');
set @colon_cnt = LEN(@input) - LEN(@input2);
-- コロンの数だけ繰り返し、発生確率を配列に格納
set @prob_sum = 0;
set @i = 1;
while @i <= @colon_cnt+1
begin
if @i < @colon_cnt+1
set @prob = cast(substring(@input,1, charindex(':',@input)-1 ) as int) ;
else
set @prob = cast(@input as int) ;
-- 次のループのために、取得済み部分を削除
set @input = substring(@input, charindex(':',@input)+1,1000 );
insert into @ar1 values(@prob);
set @prob_sum = @prob_sum + @prob;
set @i = @i+1;
end;
-- 0～1の乱数を生成し、該当するインデックス番号を求める
set @ransu = (select col1 from v_rand );
set @bunsi_from = 0;
set @bunsi_to = 0;
set @i = 1;
open cur1;
fetch next from cur1 into @vcol1;
while @@fetch_status = 0
begin
set @bunsi_to = @bunsi_to + @vcol1;
if @bunsi_from/cast(@prob_sum as decimal(30,20) ) <= @ransu and @ransu < @bunsi_to/ cast(@prob_sum as decimal(30,20) )
begin
set @ret = @i;
break;
end
set @bunsi_from = @bunsi_to;
fetch next from cur1 into @vcol1;
set @i = @i+1;
end;
close cur1;
deallocate cur1;
return @ret;
end
go
select dbo.func1('1:2:3:4:5');
-- 動作確認
ファンクションを 百万回実行し、指定した発生確率となっていることを確認
declare @counter int;
declare @i1 int = 0;
declare @i2 int = 0;
declare @i3 int = 0;
declare @i4 int = 0;
declare @i5 int = 0;
declare @k int;
declare @val int;
set @k = 1;
while @k <= 1000000
begin
set @val = (select dbo.func1('1:2:3:4:5') );
if @val = 1
set @i1 = @i1+1;
else if @val = 2
set @i2 = @i2+1;
else if @val = 3
set @i3 = @i3+1;
else if @val = 4
set @i4 = @i4+1;
else if @val = 5
set @i5 = @i5+1;
set @k = @k + 1;
end
print @i1;
print @i2;
print @i3;
print @i4;
print @i5;
go
