数据库的数据由几十万条,要用存储过程来写,但是老是写的不对。请大家按照我的意思帮我重新写一个。。
是个关于表的存储过程create or replace procedure myfunc(fyny in out String) is --放进去年月lastdate varchar2(36);--上个月
tempdate varchar2(36);--这个月
i_rowcount integer;
begin
select to_char(Add_months(to_date(fyny,'yyyy-mm'), -1),'yyyy-mm') into sdate from dual;--时间
lastdate := sdate|| '-'25;--上个月的25号
tempdate := fyny || '-'25;--这个月的25号
cursor bjqd_cursor is select * from kqfw_wysf_b_bjqd where bjyt='住户计量' and dqzt='使用中';
--DBMS_OUTPUT.put_line('ins当月记录='||SQL%rowcount||'条');
bjqd_row bjqd_cursor%rowtype;
open bjqd_cursor;
t_bjmc varchar2(64);--名称变量
t_bjlb varchar2(64);--类别变量
t_zqz number(16,4);--正确值变量
loop
fetch bjqd_cursor into bjqd_row;
exit when bjqd_cursor%notfound;
dbms_output.put_line('* * * ' ||bjqd_row);
-- t_bjid := bjid;
-- t_bjbh := bjbh;
--判断上个月有没有已计算的数据(开始)
i_rowcount:=0;
select count(*) into i_rowcount 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 < to_date(tempdate, 'yyyy-mm-dd') and a.dqzt = '已计算';
if i_rowcount>0 then
cursor cur_1 is select tt.* from(select rownum rown,dd.* from (select a.bjid tbjid,a.bjbh tbjbh,a.bjmc tbjmc,a.bjlb tbjlb,a.zqz tzqz,a.jlsj tjlsj,b.maxval tmaxval,b.bjbl tbjbl from KQFW_WYSF_B_BJJLDS a, KQFW_WYSF_B_BJQD b where a.bjqd_row.bjid = b.bjqd_row.bjid and a.bjid=bjqd_row.bjid and a.bjbh=bjqd_row.bjbh and a.jlsj<to_date(tempdate,'yyyy-mm-dd') and a.dqzt='已计算' order by a.jlsj desc)dd)tt where rown = 1;
--在这里取出select a.bjid tbjid,a.bjbh tbjbh,a.bjmc tbjmc,a.bjlb tbjlb,a.zqz tzqz,a.jlsj tjlsj,b.maxval tmaxval,b.bjbl tbjbl 这些东西(一条数据)
else
cursor cur_1 is select tt.* from(select rownum rown,dd.* from (select a.bjid tbjid,a.bjbh tbjbh,a.bjmc tbjmc,a.bjlb tbjlb,a.zqz tzqz,a.jlsj tjlsj,b.maxval tmaxval,b.bjbl tbjbl from KQFW_WYSF_B_BJJLDS a, KQFW_WYSF_B_BJQD b where a.bjqd_row.bjid = b.bjqd_row.bjid and a.bjid=bjqd_row.bjid and a.bjbh=bjqd_row.bjbh and a.jlsj>=to_date(lastdate, 'yyyy-mm-dd') and a.dqzt = '有效' order by a.jlsj) dd) tt where rown = 1;
--在这里取出select a.bjid tbjid,a.bjbh tbjbh,a.bjmc tbjmc,a.bjlb tbjlb,a.zqz tzqz,a.jlsj tjlsj,b.maxval tmaxval,b.bjbl tbjbl 这些东西(一条数据) end if;
--当月有没有计算的数据 (结束)
cursor cur_2 is select tt.* from(select rownum rown,dd.* from (select a.zqz tzqz a.jlsj tjlsj from KQFW_WYSF_B_BJJLDS a, KQFW_WYSF_B_BJQD b where a.bjqd_row.bjid = b.bjqd_row.bjid and a.bjid=bjqd_row.bjid and a.bjbh=bjqd_row.bjbh and a.jlsj<to_date(lastdate,'yyyy-mm-dd') and a.dqzt='有效' order by a.jlsj desc)dd )tt where rown = 1;
--取出读数和计量时间 (一条数据)
--在这里取出a.zqz tzqz,a.jlsj tjlsj这些东西 判断没有发生翻表(tmaxval) 获取这段时间内的结束的值(zqz)-开始值
发生翻表 获取这段时间内的最大值+结束的值-开始值 end loop;
end myfunc;高手帮我重新整理一下
是个关于表的存储过程create or replace procedure myfunc(fyny in out String) is --放进去年月lastdate varchar2(36);--上个月
tempdate varchar2(36);--这个月
i_rowcount integer;
begin
select to_char(Add_months(to_date(fyny,'yyyy-mm'), -1),'yyyy-mm') into sdate from dual;--时间
lastdate := sdate|| '-'25;--上个月的25号
tempdate := fyny || '-'25;--这个月的25号
cursor bjqd_cursor is select * from kqfw_wysf_b_bjqd where bjyt='住户计量' and dqzt='使用中';
--DBMS_OUTPUT.put_line('ins当月记录='||SQL%rowcount||'条');
bjqd_row bjqd_cursor%rowtype;
open bjqd_cursor;
t_bjmc varchar2(64);--名称变量
t_bjlb varchar2(64);--类别变量
t_zqz number(16,4);--正确值变量
loop
fetch bjqd_cursor into bjqd_row;
exit when bjqd_cursor%notfound;
dbms_output.put_line('* * * ' ||bjqd_row);
-- t_bjid := bjid;
-- t_bjbh := bjbh;
--判断上个月有没有已计算的数据(开始)
i_rowcount:=0;
select count(*) into i_rowcount 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 < to_date(tempdate, 'yyyy-mm-dd') and a.dqzt = '已计算';
if i_rowcount>0 then
cursor cur_1 is select tt.* from(select rownum rown,dd.* from (select a.bjid tbjid,a.bjbh tbjbh,a.bjmc tbjmc,a.bjlb tbjlb,a.zqz tzqz,a.jlsj tjlsj,b.maxval tmaxval,b.bjbl tbjbl from KQFW_WYSF_B_BJJLDS a, KQFW_WYSF_B_BJQD b where a.bjqd_row.bjid = b.bjqd_row.bjid and a.bjid=bjqd_row.bjid and a.bjbh=bjqd_row.bjbh and a.jlsj<to_date(tempdate,'yyyy-mm-dd') and a.dqzt='已计算' order by a.jlsj desc)dd)tt where rown = 1;
--在这里取出select a.bjid tbjid,a.bjbh tbjbh,a.bjmc tbjmc,a.bjlb tbjlb,a.zqz tzqz,a.jlsj tjlsj,b.maxval tmaxval,b.bjbl tbjbl 这些东西(一条数据)
else
cursor cur_1 is select tt.* from(select rownum rown,dd.* from (select a.bjid tbjid,a.bjbh tbjbh,a.bjmc tbjmc,a.bjlb tbjlb,a.zqz tzqz,a.jlsj tjlsj,b.maxval tmaxval,b.bjbl tbjbl from KQFW_WYSF_B_BJJLDS a, KQFW_WYSF_B_BJQD b where a.bjqd_row.bjid = b.bjqd_row.bjid and a.bjid=bjqd_row.bjid and a.bjbh=bjqd_row.bjbh and a.jlsj>=to_date(lastdate, 'yyyy-mm-dd') and a.dqzt = '有效' order by a.jlsj) dd) tt where rown = 1;
--在这里取出select a.bjid tbjid,a.bjbh tbjbh,a.bjmc tbjmc,a.bjlb tbjlb,a.zqz tzqz,a.jlsj tjlsj,b.maxval tmaxval,b.bjbl tbjbl 这些东西(一条数据) end if;
--当月有没有计算的数据 (结束)
cursor cur_2 is select tt.* from(select rownum rown,dd.* from (select a.zqz tzqz a.jlsj tjlsj from KQFW_WYSF_B_BJJLDS a, KQFW_WYSF_B_BJQD b where a.bjqd_row.bjid = b.bjqd_row.bjid and a.bjid=bjqd_row.bjid and a.bjbh=bjqd_row.bjbh and a.jlsj<to_date(lastdate,'yyyy-mm-dd') and a.dqzt='有效' order by a.jlsj desc)dd )tt where rown = 1;
--取出读数和计量时间 (一条数据)
--在这里取出a.zqz tzqz,a.jlsj tjlsj这些东西 判断没有发生翻表(tmaxval) 获取这段时间内的结束的值(zqz)-开始值
发生翻表 获取这段时间内的最大值+结束的值-开始值 end loop;
end myfunc;高手帮我重新整理一下
解决方案 »
- 求一个给定一个日期和完工期限的天数,推算出具体完工日期的函数,要求排除所有节假日。
- ORACLE动态视图?
- Oracle中的游标怎么用?这里怎么报错了????
- pl/sql中如何看oracle进程占用多少系统的内存?
- 大家好,数据迁移问题
- oms 怎么安装啊
- 大家(特别是高手)都来谈谈自己的ORACLE历程吧,交流经验,相互促进,共同提高!(我散100)
- 关于 SQL 查询.......sos.....sos.....sos
- 100分求一pl/sql语句!!!!!!!!!!!!!!!!!!!!
- 迷惹:select ...start with ... connect by,这个sql语句如何用,它表示什么意思
- 关于一个sql查询问题....... 大家给看看............
- oracle stream无法传递
CREATE OR REPLACE PROCEDURE MYFUNC(FYNY IN OUT VARCHAR2) IS
--放进去年月 LASTDATE VARCHAR2(36); --上个月
TEMPDATE VARCHAR2(36); --这个月
I_ROWCOUNT INTEGER;
TYPE REFCURSOR IS REF CURSOR;
CUR_1 REFCURSOR;
CUR_2 REFCURSOR;
BEGIN
SELECT TO_CHAR(ADD_MONTHS(TO_DATE(FYNY, 'yyyy-mm'), -1), 'yyyy-mm')
INTO SDATE
FROM DUAL; --时间
LASTDATE := SDATE || '-' 25; --上个月的25号
TEMPDATE := FYNY || '-' 25; --这个月的25号 CURSOR BJQD_CURSOR IS
SELECT *
FROM KQFW_WYSF_B_BJQD
WHERE BJYT = '住户计量'
AND DQZT = '使用中';
--DBMS_OUTPUT.put_line('ins当月记录='||SQL%rowcount||'条');
BJQD_ROW BJQD_CURSOR%ROWTYPE;
OPEN BJQD_CURSOR; T_BJMC VARCHAR2(64); --名称变量
T_BJLB VARCHAR2(64); --类别变量
T_ZQZ NUMBER(16, 4); --正确值变量 LOOP
FETCH BJQD_CURSOR
INTO BJQD_ROW;
EXIT WHEN BJQD_CURSOR%NOTFOUND;
DBMS_OUTPUT.PUT_LINE('* * * ' || BJQD_ROW);
-- t_bjid := bjid;
-- t_bjbh := bjbh;
--判断上个月有没有已计算的数据(开始)
I_ROWCOUNT := 0;
SELECT COUNT(*)
INTO I_ROWCOUNT
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 < TO_DATE(TEMPDATE, 'yyyy-mm-dd')
AND A.DQZT = '已计算';
IF I_ROWCOUNT > 0 THEN
OPEN CUR_1 FOR
SELECT TT.*
FROM (SELECT ROWNUM ROWN, DD.*
FROM (SELECT A.BJID TBJID,
A.BJBH TBJBH,
A.BJMC TBJMC,
A.BJLB TBJLB,
A.ZQZ TZQZ,
A.JLSJ TJLSJ,
B.MAXVAL TMAXVAL,
B.BJBL TBJBL
FROM KQFW_WYSF_B_BJJLDS A, KQFW_WYSF_B_BJQD B
WHERE A.BJQD_ROW.BJID = B.BJQD_ROW.BJID
AND A.BJID = BJQD_ROW.BJID
AND A.BJBH = BJQD_ROW.BJBH
AND A.JLSJ < TO_DATE(TEMPDATE, 'yyyy-mm-dd')
AND A.DQZT = '已计算'
ORDER BY A.JLSJ DESC) DD) TT
WHERE ROWN = 1;
--在这里取出select a.bjid tbjid,a.bjbh tbjbh,a.bjmc tbjmc,a.bjlb tbjlb,a.zqz tzqz,a.jlsj tjlsj,b.maxval tmaxval,b.bjbl tbjbl 这些东西(一条数据)
CLOSE CUR_1;
ELSE
OPEN CUR_1 FOR
SELECT TT.*
FROM (SELECT ROWNUM ROWN, DD.*
FROM (SELECT A.BJID TBJID,
A.BJBH TBJBH,
A.BJMC TBJMC,
A.BJLB TBJLB,
A.ZQZ TZQZ,
A.JLSJ TJLSJ,
B.MAXVAL TMAXVAL,
B.BJBL TBJBL
FROM KQFW_WYSF_B_BJJLDS A, KQFW_WYSF_B_BJQD B
WHERE A.BJQD_ROW.BJID = B.BJQD_ROW.BJID
AND A.BJID = BJQD_ROW.BJID
AND A.BJBH = BJQD_ROW.BJBH
AND A.JLSJ >= TO_DATE(LASTDATE, 'yyyy-mm-dd')
AND A.DQZT = '有效'
ORDER BY A.JLSJ) DD) TT
WHERE ROWN = 1;
--在这里取出select a.bjid tbjid,a.bjbh tbjbh,a.bjmc tbjmc,a.bjlb tbjlb,a.zqz tzqz,a.jlsj tjlsj,b.maxval tmaxval,b.bjbl tbjbl 这些东西(一条数据)
CLOSE CUR_1;
END IF;
--当月有没有计算的数据 (结束)
OPEN CUR_2 FOR
SELECT TT.*
FROM (SELECT ROWNUM ROWN, DD.*
FROM (SELECT A.ZQZ TZQZ A.JLSJ TJLSJ
FROM KQFW_WYSF_B_BJJLDS A, KQFW_WYSF_B_BJQD B
WHERE A.BJQD_ROW.BJID = B.BJQD_ROW.BJID
AND A.BJID = BJQD_ROW.BJID
AND A.BJBH = BJQD_ROW.BJBH
AND A.JLSJ < TO_DATE(LASTDATE, 'yyyy-mm-dd')
AND A.DQZT = '有效'
ORDER BY A.JLSJ DESC) DD) TT
WHERE ROWN = 1;
--取出读数和计量时间 (一条数据)
--在这里取出a.zqz tzqz,a.jlsj tjlsj这些东西
-- 判断没有发生翻表(tmaxval) 获取这段时间内的结束的值(zqz)-开始值
-- 发生翻表 获取这段时间内的最大值+结束的值-开始值
CLOSE CUR_2;
END LOOP;
CLOSE BJQD_CURSOR;
END MYFUNC;
--DBMS_OUTPUT.put_line('ins当月记录='||SQL%rowcount||'条');
bjqd_row bjqd_cursor%rowtype;
open bjqd_cursor;
t_bjmc varchar2(64);--名称变量
t_bjlb varchar2(64);--类别变量
t_zqz number(16,4);--正确值变量 上述这部分要放在begin前面定义
对呀,楼主估计是刚把其他数据库的脚本改了下,在oracle上通不过编译的。
tempdate varchar2(36);--这个月
t_bjmc varchar2(64);--名称
t_bjlb varchar2(64);--类别
t_zqz number(16,4);--正确值
t_jlsj varchar(16);--计量时间
t_bjbl number(10,2);--倍率
t_maxval varchar(16);--最大值v_zqz number(16,4);--正确值
v_jlsj varchar(16);--计量时间v_zzz number(16,4);--最综值
v_syl number(10,4);
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;--这个月
cursor bjqd_cursor is select * from kqfw_wysf_b_bjqd where bjyt='住户计量' and dqzt='使用中';
--DBMS_OUTPUT.put_line('ins当月记录='||SQL%rowcount||'条');
bjqd_row bjqd_cursor%rowtype;
open bjqd_cursor;
loop
fetch bjqd_cursor into bjqd_row;
exit when bjqd_cursor%notfound;
dbms_output.put_line('* * * ' ||bjqd_row);
--判断上个月有没有已计算的数据
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
begin
--已计算的时间最大的值
select a.bjmc into bjmc,a.bjlb into bjlb,a.zqz into zqz,a.jlsj into jlsj,b.maxval into maxval,b.bjbl into 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'));
--就一条数据,直接赋给变量
t_bjmc:=bjmc;--表的名称
t_bjlb:=bjlb;--表的类别
t_zqz:=zqz; --读数
t_jlsj:=jlsj; --时间
t_bjbl:=bjbl; --倍率
t_maxval:=maxval; --最大值
else
--有效的时间最小的值
select a.bjmc into bjmc,a.bjlb into bjlb,a.zqz into zqz,a.jlsj into jlsj,b.maxval into maxval,b.bjbl into 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'));
--就一条数据,直接赋给变量
t_bjmc:=bjmc;
t_bjlb:=bjlb;
t_zqz:=zqz;
t_jlsj:=jlsj;
t_bjbl:=bjbl;
t_maxval:=maxval;
end;
end if;
--------------------------------------
--当月有没有计算的数据
select a.zqz into zqz,a.jlsj into 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'));
--取出读数和计量时间 (一条数据)
v_zqz:=tzqz;
v_jlsj:=tjlsj;
if t_maxval is not null then
begin
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;
end if;
dbms_output.put_line('* * * ' ||t_jlsj);
insert into kqfw_wysf_b_bjzhsysl (JLBH,BJID,BJBH,BJMC,BJLB,BJBL,RYBH,RYXM,SLZ,SYL,FYNY,KSSJ,JZSJ.DQZT) value( ,bjqd_row.bjid,bjqd_row.bjbh,t_bjmc,t_bjlb,t_bjbl,,,v_syl,v_zzz,fyny,t_jlsj,v_jlsj,'已计算') ;
commit;
end loop;
end myfunc;
红色部分还要修改,。insert into values 字段比值多。我用空格给你代替了。自己修改吧
create or replace procedure myfunc(fyny in out String) is lastdate varchar2(36); --ÉϸöÔÂ
tempdate varchar2(36); --Õâ¸öÔÂ
t_bjmc varchar2(64); --Ãû³Æ
t_bjlb varchar2(64); --Àà±ð
t_zqz number(16, 4); --ÕýÈ·Öµ
t_jlsj varchar(16); --¼ÆÁ¿Ê±¼ä
t_bjbl number(10, 2); --±¶ÂÊ
t_maxval varchar(16); --×î´óÖµ v_zqz number(16, 4); --ÕýÈ·Öµ
v_jlsj varchar(16); --¼ÆÁ¿Ê±¼ä v_zzz number(16, 4); --×î×ÛÖµ
v_syl number(10, 4);
i_rowcount integer;
cursor bjqd_cursor is
select *
from kqfw_wysf_b_bjqd
where bjyt = 'ס»§¼ÆÁ¿'
and dqzt = 'ʹÓÃÖÐ';
--DBMS_OUTPUT.put_line('insµ±Ô¼Ç¼='||SQL%rowcount||'Ìõ');
bjqd_row bjqd_cursor%rowtype;
begin
select to_char(Add_months(to_date(fyny, 'yyyy-mm'), -1), 'yyyy-mm')
into sdate
from dual; --ʱ¼ä
lastdate := sdate || '- 25'; --ÉϸöÔÂ
tempdate := fyny || '- 25'; --Õâ¸öÔ open bjqd_cursor;
loop
fetch bjqd_cursor
into bjqd_row;
exit when bjqd_cursor%notfound;
dbms_output.put_line('* * * ' || bjqd_row);
--ÅжÏÉϸöÔÂÓÐûÓÐÒѼÆËãµÄÊý¾Ý
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
begin
--ÒѼÆËãµÄʱ¼ä×î´óµÄÖµ
select a.bjmc, a.bjlb, a.zqz, a.jlsj, b.maxval, b.bjbl
into bjmc, bjlb, zqz, jlsj, maxval, b.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'));
--¾ÍÒ»ÌõÊý¾Ý£¬Ö±½Ó¸³¸ø±äÁ¿
t_bjmc := bjmc; --±íµÄÃû³Æ
t_bjlb := bjlb; --±íµÄÀà±ð
t_zqz := zqz; --¶ÁÊý
t_jlsj := jlsj; --ʱ¼ä
t_bjbl := bjbl; --±¶ÂÊ
t_maxval := maxval; --×î´óÖµ
end;
else
--ÓÐЧµÄʱ¼ä×îСµÄÖµ
begin
select a.bjmc, a.bjlb, a.zqz, a.jlsj, b.maxval, b.bjbl
into bjmc, bjlb, zqz, jlsj, maxval, b.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'));
--¾ÍÒ»ÌõÊý¾Ý£¬Ö±½Ó¸³¸ø±äÁ¿
t_bjmc := bjmc;
t_bjlb := bjlb;
t_zqz := zqz;
t_jlsj := jlsj;
t_bjbl := bjbl;
t_maxval := maxval;
end;
end if;
--------------------------------------
--µ±ÔÂÓÐûÓмÆËãµÄÊý¾Ý
select a.zqz, a.jlsj
into zqz, 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'));
--È¡³ö¶ÁÊýºÍ¼ÆÁ¿Ê±¼ä £¨Ò»ÌõÊý¾Ý£©
v_zqz := tzqz;
v_jlsj := tjlsj;
if t_maxval is not null then
begin
v_syl := t_maxval + v_zqz - t_zqz;
v_zzz := t_bjbl * v_syl; --±¶ÂÊ*£¨×î´óÖµ+ÕâÔÂÖµ-ÉÏÔÂÖµ£©
end;
else
begin
v_syl := v_zqz - t_zqz;
v_zzz := t_bjbl * v_syl; --±¶ÂÊ*£¨ÕâÔÂÖµ-ÉÏÔÂÖµ£©
end;
end if;
dbms_output.put_line('* * * ' || t_jlsj);
insert into kqfw_wysf_b_bjzhsysl
(JLBH,
BJID,
BJBH,
BJMC,
BJLB,
BJBL,
RYBH,
RYXM,
SLZ,
SYL,
FYNY,
KSSJ,
JZSJ.DQZT)
values
('',
bjqd_row.bjid,
bjqd_row.bjbh,
t_bjmc,
t_bjlb,
t_bjbl,
'',
v_syl,
v_zzz,
fyny,
t_jlsj,
v_jlsj,
' ÒѼÆËã');
commit;
end loop;
end myfunc;
CREATE OR REPLACE PROCEDURE MYFUNC(FYNY IN OUT STRING) IS LASTDATE VARCHAR2(36); --上个月
TEMPDATE VARCHAR2(36); --这个月
T_BJMC VARCHAR2(64); --名称
T_BJLB VARCHAR2(64); --类别
T_ZQZ NUMBER(16, 4); --正确值
T_JLSJ VARCHAR(16); --计量时间
T_BJBL NUMBER(10, 2); --倍率
T_MAXVAL VARCHAR(16); --最大值 V_ZQZ NUMBER(16, 4); --正确值
V_JLSJ VARCHAR(16); --计量时间 V_ZZZ NUMBER(16, 4); --最综值
V_SYL NUMBER(10, 4);
I_ROWCOUNT INTEGER;
BJQD_ROW KQFW_WYSF_B_BJQD%ROWTYPE;
BEGIN
SELECT TO_CHAR(ADD_MONTHS(TO_DATE(FYNY, 'YYYY-MM'), -1), 'YYYY-MM')
INTO SDATE
FROM DUAL; --时间
LASTDATE := SDATE || '-' 25; --上个月
TEMPDATE := FYNY || '-' 25; --这个月 CURSOR BJQD_CURSOR IS
SELECT *
FROM KQFW_WYSF_B_BJQD
WHERE BJYT = '住户计量'
AND DQZT = '使用中';
--DBMS_OUTPUT.PUT_LINE('INS当月记录='||SQL%ROWCOUNT||'条');
OPEN BJQD_CURSOR;
LOOP
FETCH BJQD_CURSOR
INTO BJQD_ROW;
EXIT WHEN BJQD_CURSOR%NOTFOUND;
DBMS_OUTPUT.PUT_LINE('* * * ' || BJQD_ROW);
--判断上个月有没有已计算的数据
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
BEGIN
--已计算的时间最大的值
SELECT A.BJMC, A.BJLB, A.ZQZ, A.JLSJ, B.MAXVAL, B.BJBL
INTO BJMC, BJLB, ZQZ, JLSJ, MAXVAL, 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'));
--就一条数据,直接赋给变量
T_BJMC := BJMC; --表的名称
T_BJLB := BJLB; --表的类别
T_ZQZ := ZQZ; --读数
T_JLSJ := JLSJ; --时间
T_BJBL := BJBL; --倍率
T_MAXVAL := MAXVAL; --最大值
ELSE
--有效的时间最小的值
SELECT A.BJMC, A.BJLB, A.ZQZ, A.JLSJ, B.MAXVAL, B.BJBL
INTO BJMC, BJLB, ZQZ, JLSJ, MAXVAL, 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'));
--就一条数据,直接赋给变量
T_BJMC := BJMC;
T_BJLB := BJLB;
T_ZQZ := ZQZ;
T_JLSJ := JLSJ;
T_BJBL := BJBL;
T_MAXVAL := MAXVAL;
END;
END IF;
--------------------------------------
--当月有没有计算的数据
SELECT A.ZQZ, A.JLSJ
INTO ZQZ, 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')); --取出读数和计量时间 (一条数据)
V_ZQZ := TZQZ;
V_JLSJ := TJLSJ;
IF T_MAXVAL IS NOT NULL THEN
BEGIN
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;
END IF;
DBMS_OUTPUT.PUT_LINE('* * * ' || T_JLSJ);
INSERT INTO KQFW_WYSF_B_BJZHSYSL
(JLBH,
BJID,
BJBH,
BJMC,
BJLB,
BJBL,
RYBH,
RYXM,
SLZ,
SYL,
FYNY,
KSSJ,
JZSJ.DQZT)
VALUES
('',
BJQD_ROW.BJID,
BJQD_ROW.BJBH,
T_BJMC,
T_BJLB,
T_BJBL,
'',
'',
V_ZZZ,
V_SYL,
FYNY,
T_JLSJ,
'已计算');
COMMIT;
END LOOP;
END MYFUNC;