CREATE OR REPLACE PROCEDURE MYFUNC(FYNY in varchar2) IS CURSOR BJQD_CURSOR IS
SELECT *
FROM KQFW_WYSF_B_BJQD
WHERE BJYT = '住户计量'
AND DQZT = '使用中'; BJQD_ROW KQFW_WYSF_B_BJQD%ROWTYPE; LASTDATE VARCHAR2(36); --上个月
TEMPDATE VARCHAR2(36); --这个月
T_BJMC VARCHAR2(64); --名称
T_BJLB VARCHAR2(64); --类别
T_ZQZ NUMBER(16, 4); --正确值
T_JLSJ VARCHAR2(16); --计量时间
T_BJBL NUMBER(10, 2); --倍率
T_MAXVAL VARCHAR2(16); --最大值 V_ZQZ NUMBER(16, 4); --正确值
V_JLSJ VARCHAR2(16); --计量时间 V_ZZZ NUMBER(16, 4); --最综值
V_SYL NUMBER(10, 4); SDATE VARCHAR2(16);
I_ROWCOUNT INTEGER;begin
SELECT TO_CHAR(ADD_MONTHS(TO_DATE(FYNY, 'YYYY-MM'), -1), 'YYYY-MM')
INTO SDATE
FROM DUAL; --时间
LASTDATE := SDATE || '-25'; --上个月
TEMPDATE := FYNY || '-25'; --这个月
dbms_output.put_line('* * * ' || LASTDATE);
dbms_output.put_line('* * * ' || TEMPDATE);
OPEN BJQD_CURSOR; --打开游标
loop
FETCH BJQD_CURSOR
INTO BJQD_ROW;
EXIT WHEN BJQD_CURSOR%NOTFOUND;
--判断上个月有没有已计算的数据
I_ROWCOUNT := 0;
select count(*)
into I_ROWCOUNT
from KQFW_WYSF_B_BJJLDS
where bjid = BJQD_ROW.bjid
and bjbh = BJQD_ROW.bjbh
and jlsj < to_date(TEMPDATE, 'yyyy-mm-dd')
and dqzt = '已计算';
if I_ROWCOUNT > 0 then
--已计算的时间最大的值
select a.bjmc, a.bjlb, a.zqz, a.jlsj, b.maxval, b.bjbl
into T_BJMC, T_BJLB, T_ZQZ, T_JLSJ, T_MAXVAL, T_BJBL
from KQFW_WYSF_B_BJJLDS a, KQFW_WYSF_B_BJQD b
where a.bjid = b.bjid
and a.bjid = BJQD_ROW.bjid
and a.bjbh = BJQD_ROW.bjbh
and a.jlsj =
(select max(jlsj)
from KQFW_WYSF_B_BJJLDS
where bjid = BJQD_ROW.bjid
and bjbh = BJQD_ROW.bjbh
and dqzt = '已计算'
and jlsj < to_date(TEMPDATE, 'yyyy-mm-dd'));
--就一条数据,直接赋给变量
else
--有效的时间最小的值
select a.bjmc, a.bjlb, a.zqz, a.jlsj, b.maxval, b.bjbl
into T_BJMC, T_BJLB, T_ZQZ, T_JLSJ, T_MAXVAL, T_BJBL
from KQFW_WYSF_B_BJJLDS a, KQFW_WYSF_B_BJQD b
where a.bjid = b.bjid
and a.bjid = BJQD_ROW.bjid
and a.bjbh = BJQD_ROW.bjbh
and a.jlsj =
(select min(jlsj)
from KQFW_WYSF_B_BJJLDS
where bjid = BJQD_ROW.bjid
and bjbh = BJQD_ROW.bjbh
and dqzt = '有效'
and jlsj >= to_date(LASTDATE, 'yyyy-mm-dd'));
--就一条数据,直接赋给变量
end if;
--------------------------------------
--当月有没有计算的数据
select a.zqz, a.jlsj
into V_ZQZ, V_JLSJ
FROM KQFW_WYSF_B_BJJLDS a
where a.bjid = BJQD_ROW.bjid
and a.bjbh = BJQD_ROW.bjbh
and a.jlsj =
(select max(jlsj)
from KQFW_WYSF_B_BJJLDS
where bjid = BJQD_ROW.bjid
and bjbh = BJQD_ROW.bjbh
and dqzt = '有效'
and jlsj < to_date(TEMPDATE, 'yyyy-mm-dd'));
--取出读数和计量时间 (一条数据)
-------------------------------------------------
if T_MAXVAL is not null then
V_SYL := T_MAXVAL + V_ZQZ - T_ZQZ;
V_ZZZ := T_BJBL * V_SYL; --倍率*(最大值+这月值-上月值)
else
V_SYL := V_ZQZ - T_ZQZ;
V_ZZZ := T_BJBL * V_SYL; --倍率*(这月值-上月值)
end if;
dbms_output.put_line('* * * ' || T_JLSJ);
dbms_output.put_line('* * * ' || V_JLSJ);
insert into kqfw_wysf_b_bjzhsysl
(JLBH,
BJID,
BJBH,
BJMC,
BJLB,
BJBL,
SLZ,
SYL,
FYNY,
KSSJ,
JZSJ,
DQZT)
values
(to_char(sysdate, 'yyyymmddhh24miss') || 'XXXH' ||
lpad(TXNFXHY_HJJC.NEXTVAL, 4, '0'),
BJQD_ROW.bjid,
BJQD_ROW.bjbh,
T_BJMC,
T_BJLB,
T_BJBL,
V_SYL,
V_ZZZ,
FYNY,
T_JLSJ,
V_JLSJ,
'已计算');
update kqfw_wysf_b_bjjlds
set dqzt = '已计算'
where bjid = BJQD_ROW.bjid
and bjbh = BJQD_ROW.bjbh
and jlsj >= to_date(t_jlsj, 'yyyy-mm-dd hh24:mi:ss')
and jlsj < = to_date(V_JLSJ, 'yyyy-mm-dd hh24:mi:ss');
commit;
end loop;
close BJQD_CURSOR;
end myfunc;
SELECT *
FROM KQFW_WYSF_B_BJQD
WHERE BJYT = '住户计量'
AND DQZT = '使用中'; BJQD_ROW KQFW_WYSF_B_BJQD%ROWTYPE; LASTDATE VARCHAR2(36); --上个月
TEMPDATE VARCHAR2(36); --这个月
T_BJMC VARCHAR2(64); --名称
T_BJLB VARCHAR2(64); --类别
T_ZQZ NUMBER(16, 4); --正确值
T_JLSJ VARCHAR2(16); --计量时间
T_BJBL NUMBER(10, 2); --倍率
T_MAXVAL VARCHAR2(16); --最大值 V_ZQZ NUMBER(16, 4); --正确值
V_JLSJ VARCHAR2(16); --计量时间 V_ZZZ NUMBER(16, 4); --最综值
V_SYL NUMBER(10, 4); SDATE VARCHAR2(16);
I_ROWCOUNT INTEGER;begin
SELECT TO_CHAR(ADD_MONTHS(TO_DATE(FYNY, 'YYYY-MM'), -1), 'YYYY-MM')
INTO SDATE
FROM DUAL; --时间
LASTDATE := SDATE || '-25'; --上个月
TEMPDATE := FYNY || '-25'; --这个月
dbms_output.put_line('* * * ' || LASTDATE);
dbms_output.put_line('* * * ' || TEMPDATE);
OPEN BJQD_CURSOR; --打开游标
loop
FETCH BJQD_CURSOR
INTO BJQD_ROW;
EXIT WHEN BJQD_CURSOR%NOTFOUND;
--判断上个月有没有已计算的数据
I_ROWCOUNT := 0;
select count(*)
into I_ROWCOUNT
from KQFW_WYSF_B_BJJLDS
where bjid = BJQD_ROW.bjid
and bjbh = BJQD_ROW.bjbh
and jlsj < to_date(TEMPDATE, 'yyyy-mm-dd')
and dqzt = '已计算';
if I_ROWCOUNT > 0 then
--已计算的时间最大的值
select a.bjmc, a.bjlb, a.zqz, a.jlsj, b.maxval, b.bjbl
into T_BJMC, T_BJLB, T_ZQZ, T_JLSJ, T_MAXVAL, T_BJBL
from KQFW_WYSF_B_BJJLDS a, KQFW_WYSF_B_BJQD b
where a.bjid = b.bjid
and a.bjid = BJQD_ROW.bjid
and a.bjbh = BJQD_ROW.bjbh
and a.jlsj =
(select max(jlsj)
from KQFW_WYSF_B_BJJLDS
where bjid = BJQD_ROW.bjid
and bjbh = BJQD_ROW.bjbh
and dqzt = '已计算'
and jlsj < to_date(TEMPDATE, 'yyyy-mm-dd'));
--就一条数据,直接赋给变量
else
--有效的时间最小的值
select a.bjmc, a.bjlb, a.zqz, a.jlsj, b.maxval, b.bjbl
into T_BJMC, T_BJLB, T_ZQZ, T_JLSJ, T_MAXVAL, T_BJBL
from KQFW_WYSF_B_BJJLDS a, KQFW_WYSF_B_BJQD b
where a.bjid = b.bjid
and a.bjid = BJQD_ROW.bjid
and a.bjbh = BJQD_ROW.bjbh
and a.jlsj =
(select min(jlsj)
from KQFW_WYSF_B_BJJLDS
where bjid = BJQD_ROW.bjid
and bjbh = BJQD_ROW.bjbh
and dqzt = '有效'
and jlsj >= to_date(LASTDATE, 'yyyy-mm-dd'));
--就一条数据,直接赋给变量
end if;
--------------------------------------
--当月有没有计算的数据
select a.zqz, a.jlsj
into V_ZQZ, V_JLSJ
FROM KQFW_WYSF_B_BJJLDS a
where a.bjid = BJQD_ROW.bjid
and a.bjbh = BJQD_ROW.bjbh
and a.jlsj =
(select max(jlsj)
from KQFW_WYSF_B_BJJLDS
where bjid = BJQD_ROW.bjid
and bjbh = BJQD_ROW.bjbh
and dqzt = '有效'
and jlsj < to_date(TEMPDATE, 'yyyy-mm-dd'));
--取出读数和计量时间 (一条数据)
-------------------------------------------------
if T_MAXVAL is not null then
V_SYL := T_MAXVAL + V_ZQZ - T_ZQZ;
V_ZZZ := T_BJBL * V_SYL; --倍率*(最大值+这月值-上月值)
else
V_SYL := V_ZQZ - T_ZQZ;
V_ZZZ := T_BJBL * V_SYL; --倍率*(这月值-上月值)
end if;
dbms_output.put_line('* * * ' || T_JLSJ);
dbms_output.put_line('* * * ' || V_JLSJ);
insert into kqfw_wysf_b_bjzhsysl
(JLBH,
BJID,
BJBH,
BJMC,
BJLB,
BJBL,
SLZ,
SYL,
FYNY,
KSSJ,
JZSJ,
DQZT)
values
(to_char(sysdate, 'yyyymmddhh24miss') || 'XXXH' ||
lpad(TXNFXHY_HJJC.NEXTVAL, 4, '0'),
BJQD_ROW.bjid,
BJQD_ROW.bjbh,
T_BJMC,
T_BJLB,
T_BJBL,
V_SYL,
V_ZZZ,
FYNY,
T_JLSJ,
V_JLSJ,
'已计算');
update kqfw_wysf_b_bjjlds
set dqzt = '已计算'
where bjid = BJQD_ROW.bjid
and bjbh = BJQD_ROW.bjbh
and jlsj >= to_date(t_jlsj, 'yyyy-mm-dd hh24:mi:ss')
and jlsj < = to_date(V_JLSJ, 'yyyy-mm-dd hh24:mi:ss');
commit;
end loop;
close BJQD_CURSOR;
end myfunc;
-- Call the procedure
FYNY varchar2;
FYNY:='2008_09';
myfunc(FYNY => :FYNY);
end;是这样调吗?
怎么写?
from KQFW_WYSF_B_BJJLDS
where bjid = BJQD_ROW.bjid
and bjbh = BJQD_ROW.bjbh
and dqzt = '有效'
and jlsj < to_date(TEMPDATE, 'yyyy-mm-dd')),这样写,查出的是一条数据?加个row_number()取出唯一列看看