oracle查询最大值最小值,查找字符串中的最大最小值

修改之后：

create or replace function calc_test(p_temp_list varchar2)

return obj_test

as

before_45 varchar2(1000) := ','||substr(p_temp_list, 1, instr(p_temp_list, ',', 1, 45) - 1)||',';

after_45 varchar2(1000) := ','||substr(p_temp_list, instr(p_temp_list, ',', 1, 45) + 1)||',';

total_1 number := 0;

total_2 number := 0;

min_1 number;

max_1 number;

min_2 number;

max_2 number;

v_num1 number;

v_num2 number;

begin

for n in 1 .. 45 loop

v_num1 := to_number(substr(before_45,instr(before_45,',',1,n)+1,instr(before_45,',',1,n+1)-instr(before_45,',',1,n)-1));

v_num2 := to_number( substr(after_45,instr(after_45,',',1,n)+1,instr(after_45,',',1,n+1)-instr(after_45,',',1,n)-1));

total_1 := total_1 + v_num1;

total_2 := total_2 + v_num2;

if min_1 is null or v_num1

min_1 := v_num1;

end if;

if max_1 is null or v_num1>max_1 then

max_1 := v_num1;

end if;

if min_2 is null or v_num2

min_2 := v_num2;

end if;

if max_2 is null or v_num2>max_2 then

max_2 := v_num2;

end if;

end loop;

return  obj_test(min_1,max_1,round(total_1/45),min_2,max_2,round(total_2/45));

end;

/

with test_table as

(select '2019-07-28 00:01:00' post_time,

'46,46,45,47,47,47,47,47,46,45,45,45,47,47,46,46,46,46,46,47,47,47,47,47,45,46,45,47,49,49,49,48,48,45,47,47,48,49,49,48,49,48,46,47,46,40,40,41,42,42,41,40,41,41,42,42,42,43,43,43,42,43,42,41,42,42,43,43,42,41,42,42,41,42,41,42,42,42,41,42,41,42,42,42,42,43,42,41,42,41' temp_list

from dual

union all

select '2019-07-28 00:02:00' post_time,

'51,51,51,52,52,52,52,52,51,51,51,51,52,52,53,52,52,50,51,52,51,52,52,52,51,51,50,53,53,53,53,53,53,51,52,51,52,53,53,52,53,53,51,52,50,44,45,45,46,46,46,44,45,45,46,46,46,47,48,47,47,47,46,45,46,46,46,47,47,45,46,45,45,46,46,47,47,47,46,46,45,46,46,46,46,47,47,46,46,45' temp_list

from dual)

select post_time,d.r.min_1,d.r.max_1,d.r.avg_1,d.r.min_2,d.r.max_2,d.r.avg_2

from (

select post_time, calc_test(temp_list) r

from test_table

) d;