create or replace procedure EZ_CLTJ(yf_IN CLSYJLB.Yf%Type) is
s_ks CLSYJLB.SYRSSKS%TYPE; --变量科室
i_gls CLSYJLB.Gls%TYPE; --变量本月使用公里数
i_jygl KSSYTJB.DYJY%TYPE; --变量本月结余公里
i_ljjy KSSYTJB.Ljjy%TYPE; --变量累计结余公里
i_syjygl KSSYTJB.DYJY%TYPE; --变量上月结余公里
i_edgl KSYCCSXXB.MYEDSYGL%TYPE; --变量额定使用公里
i_count integer; --计算行数
i_Newcount integer; --计算行数
i_Scount integer; --计算行数
i_syf CLSYJLB.Yf%Type; --变量上月份
s_yf CLSYJLB.Yf%Type; --变量本月份
s_myf CLSYJLB.Yf%Type; --变量最小月份
s_flag Boolean; --标识符
CURSOR c_ks IS
select KSYCCSXXB.KSMC, KSYCCSXXB.MYEDSYGL from KSYCCSXXB;
begin
OPEN c_ks;
LOOP
FETCH c_ks
into s_ks, i_edgl;
exit when c_ks%Notfound;
s_flag := true;
select SUM(CLSYJLB.GLS) --统计当月科室使用公里数
INTO i_gls
from CLSYJLB
where CLSYJLB.SYRSSKS = s_ks
and CLSYJLB.YF = yf_IN;
i_gls := Nvl(i_gls, 0);
i_jygl := i_edgl - i_gls;
if substr(yf_IN, 5, 2) = '01' then
--判断上月份
i_syf := to_char(to_number(substr(yf_IN, 1, 4)) - 1) + '12';
else
i_syf := to_char(to_number(yf_IN) - 1);
end if;
select min(KSSYTJB.Yf)
into s_myf
from KSSYTJB
where KSSYTJB.KSMC = s_ks;
select count(*)
into i_Scount
from KSSYTJB
where KSSYTJB.Ksmc = s_ks
and KSSYTJB.YF = i_syf;
if yf_IN < s_myf or i_Scount <= 0 then
s_flag := false;
end if;
select count(*) into i_count from KSSYTJB where KSSYTJB.Ksmc = s_ks; --判断初始情况以及结余公里
If i_count <= 0 Then
--情况一，完全初始化的结余公里
select KSYCCSXXB.CSJY
into i_ljjy
from KSYCCSXXB
where KSYCCSXXB.Ksmc = s_ks;
i_ljjy := Nvl(i_ljjy, 0) + Nvl(i_jygl, 0);
s_flag := true;
end if;
if i_count = 1 then
--情况二，有一条统计记录
select KSSYTJB.Ljjy, KSSYTJB.Yf
into i_syjygl, s_yf
from KSSYTJB
where KSSYTJB.Ksmc = s_ks;
if s_yf <> yf_IN then
--情况二-1，有一条不是本月的统计记录
select KSSYTJB.LJJY, KSSYTJB.Yf
into i_ljjy, s_myf
from KSSYTJB
where KSSYTJB.Ksmc = s_ks;
if s_myf > yf_IN then
i_ljjy := Nvl(i_ljjy, 0) + Nvl(i_jygl, 0);
s_flag := true;
end if;
else
--情况二-2，有一条是本月的统计记录
select KSYCCSXXB.CSJY
into i_ljjy
from KSYCCSXXB
where KSYCCSXXB.Ksmc = s_ks;
i_ljjy := Nvl(i_ljjy, 0) + Nvl(i_jygl, 0);
s_flag := true;
end if;
end if;
if i_count > 1 and s_flag = true then
--情况三，有多条是本月的统计记录
select KSSYTJB.LJJY
into i_ljjy
from KSSYTJB |
|
资源大小：2.30 KB 
