表A,字段有用户ID,日冻结数据日期(每天24点提交数据的),电能值
表B,用户,用户ID,
表C,用户ID,电价
表D,用户ID,日电量,日电费 本月累计电量 当前余额
每天凌晨计算前一天各个用户的总用电量,以及电费,然后插入到表D中
昨天电量=昨天电能值-前天电能值当前余额=昨天余额-昨天电量*电价用oracle的存储过程实现,每天凌晨任务开始,需要把表B中的每个用户都计算一遍
表B,用户,用户ID,
表C,用户ID,电价
表D,用户ID,日电量,日电费 本月累计电量 当前余额
每天凌晨计算前一天各个用户的总用电量,以及电费,然后插入到表D中
昨天电量=昨天电能值-前天电能值当前余额=昨天余额-昨天电量*电价用oracle的存储过程实现,每天凌晨任务开始,需要把表B中的每个用户都计算一遍
SQL> begin
2 dbms_job.submit(:job1,'insert into a values(sysdate);',to_date('12032009','mmddyyyy'),'to_date('12032009','mmddyyyy') + 1');
3 end;
4 /PL/SQL procedure successfully completed
job1
---------
21SQL> begin
2 dbms_job.run(:job1);
3 end;
4 /PL/SQL procedure successfully completedSQL> select to_char(a,'mm/dd/yyyy hh24:mi:ss') from a;TO_CHAR(A,'MM/DD/YYYYHH24:MI:SS'
------------------------------
12/01/2009 14:03:54
12/01/2009 14:04:59SQL> begin
2 dbms_job.remove(:job1); -- 移除job
3 end;
4 /PL/SQL procedure successfully completed
1.表D少了个日期字段,不管是最后计算日期还是什么日期
否则你这里的
当前余额=昨天余额-昨天电量*电价
昨天余额怎么找?
2.是否B存在的userid,C表就一定有?
3.D表中针对新进来的用户最开始应该是空的,因为它是统计的啊,那么这个时候,这个当前余额从哪里找出来的?缺表。
4.你的电量是小数还是整数?电价是小数以元为单位还是整数以厘为单位?楼主先解决了上面几个问题,或者直接贴出表结构,并且带上一定量的测试数据。
我试图帮你写proc,但是才写了两句就发现不对头。不能怪我啊。
r_f NUMBER(12,4);
r_l NUMBER(12,4);
mp_id NUMBER(16);
rs NUMBER(12,4);
bal NUMBER(16,2);
price Number(6,4);
total_price NUMBER(16,2);
accu_spq NUMBER(16,2);--本月累计总电量
accu_rcvbls NUMBER(16,2);--本月累计总电费
cr_lmt Number(12,2);--催电阀值
Cursor cursor is select CUST_NO from PPM_PREPAY_CTRL;CUST_NO varchar(16);
begin
for CUST_NO in cursor LOOP
Begin
select MP_ID into mp_id from C_MP where CONS_ID=CUST_NO;
SELECT R into rf from E_MR_DAY_READ where ID=mp_id and DATA_DATE=to_date(to_char(sysdate-1),'yyyy-mm-dd');
SELECT R into rl from E_MR_DAY_READ where ID=mp_id and DATA_DATE=to_date(to_char(sysdate),'yyyy-mm-dd');
rs:=rl-rf;
select PRICE into price from PPM_MP_SECT_RELA where CONS_NO=CUST_NO;
total_price:=rs*price;
select ACCT_BAL into bal from PPM_DAY_RATA where CONS_NO=CUST_NO;--从用户日电费明细中查出余额
bal:= bal-total_price;
select ACCU_SPQ into accu_spq from PPM_DAY_RATA where CONS_NO=COST_NO;--从用户日电费明细中查出本月累计电量
select ACCU_RCVBLS into accu_rcvbls from PPM_DAY_RATA where CONS_NO=COST_NO;--从用户日电费明细中查出本月累计电费
update PPM_DAY_RATA set BILL_DATE=sysdate,TOTAL_PQ=rs,TOTAL_AMT=total_price,ACCU_SPQ=(accu_spq+rs),ACCU_RCVBLS=bal where CONS_NO=COST_NO;
SELECT CR_LMT into ce_lmt FROM PPM_MP_SECT_RELA WHERE CONS_NO=CUST_NO;
if(bal<ce_lmt and bal<0)
THEN
INSERT INTO PPM_CR_CTRL(CR_ID,MPED_INDEX,CUST_NO,TYPE_CODE,PAY_AMT) values(0,mp_id,CUST_NO,'3',abs(bal));
Insert into PPM_OWE_FLOW values(COST_NO,sysdate,abs(bal));
ELSE IF(bal<ce_lmt)
INSERT INTO PPM_CR_CTRL(CR_ID,MPED_INDEX,CUST_NO,TYPE_CODE) values(0,mp_id,CUST_NO,'3');
END IF;
COMMIT;
END LOOP;
END day_Settlement; create or replace procedure mon_Settlement()as
mp_id NUMBER(16);rs NUMBER(12,4);
r_f NUMBER(12,4);r_l NUMBER(12,4);
price Number(6,4);total_price NUMBER(16,2);
bal NUMBER(16,2);--用户余额
Cursor cursor is select CUST_NO from PPM_PREPAY_CTRL;CUST_NO varchar(16);
begin
for CUST_NO in cursor LOOP
Begin
select MP_ID into mp_id from C_MP where CONS_ID=CUST_NO;--通过用户ID查询出计量点ID
select R into rf from E_MR_DAY_READ where ID=mp_id and DATA_DATE=to_date(select to_char(add_months(last_day(sysdate)+1,-2),'yyyy-MM-dd') firstDay from dual,'YY-MM-DD');
--通过日期和计量表ID从日冻结电能示值表中取出月初和月末的数据
select R into rl from E_MR_DAY_READ where ID=mp_id and DATA_DATE=to_date(select to_char(add_months(last_day(sysdate),-1),'yyyy-MM-dd') LastDay from dual,'YY-MM-DD');
rs=rl-rf;--计算出这个月的用电量
select PRICE into price from PPM_MP_SECT_RELA where CONS_NO=CUST_NO;
total_price=rs*price;
select ACCT_BAL into bal from PPM_ACCT_BAL where CONS_NO=CUST_NO;--查询出用户的余额
bal:=bal-total_price;--计算最新余额
update PPM_ACCT_BAL set ACCT_BAL=bal,CHANGE_DATE=sysdate where CONS_NO=CUST_NO;
if bal<0 then
Insert into PPM_OWE_FLOW values(COST_NO,sysdate,abs(bal));
commit;
end LOOP;
end mon_Settlement;我自己模仿着写的存储过程,不过好像有错,但是找不到
Cursor cursor is select CUST_NO from PPM_PREPAY_CTRL;CUST_NO varchar(16);
应该写成:
Cursor c1 is select CUST_NO from PPM_PREPAY_CTRL;CUST_NO varchar(16);
后面:
for e in c1 loop
--既然用了cursor,那么应该是这样的:
select PRICE into price from PPM_MP_SECT_RELA where CONS_NO=e.CUST_NO ;
下面的你自己改吧