为什么我输出来的时间是:##### 06-9月 -08 而不是2008-9-6 1:04:01
类型也是DATE型的T_JLSJ,好像是格式的问题,但是我又不知道怎么写。。我把过程贴出来。。请大家帮我看看CREATE OR REPLACE PROCEDURE MYFUNC(FYNY 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 DATE; --计量时间
T_BJBL NUMBER(10, 2); --倍率
T_MAXVAL VARCHAR2(16); --最大值 V_ZQZ NUMBER(16, 4); --正确值
V_JLSJ DATE; --计量时间 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; --打开游标
FETCH BJQD_CURSOR INTO BJQD_ROW;
loop
dbms_output.put_line('* * * ' ||BJQD_ROW.bjid||'--'||BJQD_ROW.bjbh);
--判断上个月有没有已计算的数据
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 = '已计算';
dbms_output.put_line('~~~~~~' ||I_ROWCOUNT);
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 = '1189'--BJQD_ROW.bjid
and a.bjbh = '3285'--BJQD_ROW.bjbh
and a.jlsj =
(select min(jlsj)
from KQFW_WYSF_B_BJJLDS
where bjid = '1189'--BJQD_ROW.bjid
and bjbh = '3285'--BJQD_ROW.bjbh
and dqzt = '有效'
and jlsj >= to_date(LASTDATE, 'yyyy-mm-dd'));
dbms_output.put_line('##### ' || T_JLSJ);
--就一条数据,直接赋给变量
end if;
--------------------------------------
--当月有没有计算的数据
select zqz, jlsj
into V_ZQZ, V_JLSJ
FROM KQFW_WYSF_B_BJJLDS a
where bjid = '1189'
and bjbh = '3285'
and jlsj =
(select max(jlsj)
from KQFW_WYSF_B_BJJLDS
where bjid = '1189'
and bjbh = '3285'
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; --倍率*(最大值+这月值-上月值)
dbms_output.put_line('* * * ' ||V_SYL||'--'||V_ZZZ);
else
V_SYL := V_ZQZ - T_ZQZ;
V_ZZZ := T_BJBL * V_SYL; --倍率*(这月值-上月值)
dbms_output.put_line('* * * ' ||V_SYL||'--'||V_ZZZ);
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'),
'1189',
'3285',
T_BJMC,
T_BJLB,
T_BJBL,
V_SYL,
V_ZZZ,
FYNY,
T_JLSJ,
V_JLSJ,
'已计算');
--更新读数的状态
update kqfw_wysf_b_bjjlds
set dqzt = '已计算'
where bjid = '1189'
and bjbh = '3285'
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;
EXIT WHEN BJQD_CURSOR%NOTFOUND;
FETCH BJQD_CURSOR INTO BJQD_ROW;
end loop;
close BJQD_CURSOR;
end myfunc;
最后的出来的数据:::
* * * 2008-08-25
* * * 2008-09-25
* * * Siw_2357--12863
~~~~~~0
##### 06-9月 -08
* * * 100001--100001
##### 06-9月 -08
##### 08-9月 -08:##### 06-9月 -08 而不是2008-9-6 1:04:01???????????????
类型也是DATE型的T_JLSJ,好像是格式的问题,但是我又不知道怎么写。。我把过程贴出来。。请大家帮我看看CREATE OR REPLACE PROCEDURE MYFUNC(FYNY 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 DATE; --计量时间
T_BJBL NUMBER(10, 2); --倍率
T_MAXVAL VARCHAR2(16); --最大值 V_ZQZ NUMBER(16, 4); --正确值
V_JLSJ DATE; --计量时间 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; --打开游标
FETCH BJQD_CURSOR INTO BJQD_ROW;
loop
dbms_output.put_line('* * * ' ||BJQD_ROW.bjid||'--'||BJQD_ROW.bjbh);
--判断上个月有没有已计算的数据
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 = '已计算';
dbms_output.put_line('~~~~~~' ||I_ROWCOUNT);
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 = '1189'--BJQD_ROW.bjid
and a.bjbh = '3285'--BJQD_ROW.bjbh
and a.jlsj =
(select min(jlsj)
from KQFW_WYSF_B_BJJLDS
where bjid = '1189'--BJQD_ROW.bjid
and bjbh = '3285'--BJQD_ROW.bjbh
and dqzt = '有效'
and jlsj >= to_date(LASTDATE, 'yyyy-mm-dd'));
dbms_output.put_line('##### ' || T_JLSJ);
--就一条数据,直接赋给变量
end if;
--------------------------------------
--当月有没有计算的数据
select zqz, jlsj
into V_ZQZ, V_JLSJ
FROM KQFW_WYSF_B_BJJLDS a
where bjid = '1189'
and bjbh = '3285'
and jlsj =
(select max(jlsj)
from KQFW_WYSF_B_BJJLDS
where bjid = '1189'
and bjbh = '3285'
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; --倍率*(最大值+这月值-上月值)
dbms_output.put_line('* * * ' ||V_SYL||'--'||V_ZZZ);
else
V_SYL := V_ZQZ - T_ZQZ;
V_ZZZ := T_BJBL * V_SYL; --倍率*(这月值-上月值)
dbms_output.put_line('* * * ' ||V_SYL||'--'||V_ZZZ);
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'),
'1189',
'3285',
T_BJMC,
T_BJLB,
T_BJBL,
V_SYL,
V_ZZZ,
FYNY,
T_JLSJ,
V_JLSJ,
'已计算');
--更新读数的状态
update kqfw_wysf_b_bjjlds
set dqzt = '已计算'
where bjid = '1189'
and bjbh = '3285'
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;
EXIT WHEN BJQD_CURSOR%NOTFOUND;
FETCH BJQD_CURSOR INTO BJQD_ROW;
end loop;
close BJQD_CURSOR;
end myfunc;
最后的出来的数据:::
* * * 2008-08-25
* * * 2008-09-25
* * * Siw_2357--12863
~~~~~~0
##### 06-9月 -08
* * * 100001--100001
##### 06-9月 -08
##### 08-9月 -08:##### 06-9月 -08 而不是2008-9-6 1:04:01???????????????
to_char(日有变量, 'yyyy-mm-dd hh24:mi:ss);
报错 因为变量 T_JLSJ 本来就是DATE型的
需要
set serveroutput on