最小值least()函数
情景设定:
从三张表中取出各自的最小时间,比较这三个时间,再取出最小值(其时间可能为null);
--三个表中各自取出其最小值
select min(v.CHECK_START_TIME) into v_check_v from t_third_survey_vehicle v where v.claim_id = v_claim_data.claim_id;
select min(v.CHECK_START_TIME) into v_check_c from t_third_survey_property v where v.claim_id = v_claim_data.claim_id;
select min(v.CHECK_START_TIME) into v_check_p from T_THIRD_SURVEY_PERSON v where v.claim_id = v_claim_data.claim_id;
--****************************************************************************************************
--判断其各种情况,并得出最小值
if v_check_v is not null and v_check_c is not null and v_check_p is not null then
v_min_date:=least(v_check_v,v_check_c,v_check_p);
elsif v_check_v is null and v_check_c is null and v_check_p is null then
v_min_date:=null;
end if;
if v_check_v is null and v_check_c is null then
v_min_date:=v_check_p;
elsif v_check_p is null and v_check_c is null then
v_min_date:=v_check_v;
elsif v_check_p is null and v_check_v is null then
v_min_date:=v_check_c;
end if;
if v_check_v is null then
if v_check_c > v_check_p or v_check_c = v_check_p
then
v_min_date:=v_check_p;
else
v_min_date:=v_check_c;
end if;
elsif v_check_c is null then
if v_check_v > v_check_p or v_check_v = v_check_p
then
v_min_date:=v_check_p;
else
v_min_date:=v_check_v;
end if;
elsif v_check_p is null then
if v_check_v > v_check_c or v_check_v = v_check_c then
v_min_date:=v_check_c;
else
v_min_date:=v_check_v;
end if;
end if;
最大值greasest()函数使用方法道理一样