CREATE OR REPLACE PROCEDURE pro_csdn IS
v_x number(20,0);
v_IDCARD (20,0);
BEGIN
coursor aaaa is select IDCARD from money_info where total_flag='1';
open aaaa;
fetct aaaa into v_IDCARD ;
while aaaa%found
loop
--得到x的值
select X into v_x from table where IDCARD =v_IDCARD;
UPDATE money_info
SET total_money=total_money+(TO_NUMBER(TO_char(sysdate,'yyyymm'))-TO_NUMBER(TO_char(ADD_DATE,'yyyymm')))*100
WHERE total_flag='1'
AND MONTHS_BETWEEN(sysdate,ADD_DATE)>0 and IDCARD = v_x;
commit;
fetct aaaa into v_IDCARD;
end loop;
END pro_csdn;
现改的,自己研究吧!
v_x number(20,0);
v_IDCARD (20,0);
BEGIN
coursor aaaa is select IDCARD from money_info where total_flag='1';
open aaaa;
fetct aaaa into v_IDCARD ;
while aaaa%found
loop
--得到x的值
select X into v_x from table where IDCARD =v_IDCARD;
UPDATE money_info
SET total_money=total_money+(TO_NUMBER(TO_char(sysdate,'yyyymm'))-TO_NUMBER(TO_char(ADD_DATE,'yyyymm')))*100
WHERE total_flag='1'
AND MONTHS_BETWEEN(sysdate,ADD_DATE)>0 and IDCARD = v_x;
commit;
fetct aaaa into v_IDCARD;
end loop;
END pro_csdn;
现改的,自己研究吧!
/*声明变量*/
money money_subsidy_standard.money_subsidy_standard%type;
vidcard test_money.idcard%TYPE; --定义一个变量,用于存放idcard;
CURSOR res IS SELECT IDCARD FROM TEST_MONEY WHERE TOTAL_FLAG = '1'; --定义一个游标,用来得到total_flag=1时的idcard结果集;
begin
select MONEY_SUBSIDY_STANDARD into money from MONEY_SUBSIDY_STANDARD where valide_TIME = (select max(valide_TIME) from MONEY_SUBSIDY_STANDARD);
--打开游标;
OPEN res;
--循环对每一条特定记录进行处理;
LOOP
FETCH res INTO vidcard; --取游标中的一个idcard
EXIT WHEN res%notfound; --当游标中记录为空时结束循环;
--对特定idcard的记录进行更新
UPDATE test_money
SET total_money=total_money+(TO_NUMBER(TO_char(sysdate,'yyyyMM'))-TO_NUMBER(TO_char(ADD_DATE,'yyyyMM')))*money, add_date = sysdate
WHERE IDCARD = vidcard;
END LOOP; --结束循环
CLOSE res;--关闭游标
end add_money; --过程结束
create procedure pro
as
begin
update mondy_info set TOTAL_MONEY=(select TOTAL_MONEY+month_between(sysdate,add_date)*x from others_table a where a.id=IDCARD) where month_between(sysdate,add_date)>0 and total_flag='1';
--注明,others_table是x值的表,id是others_table与TOTAL_MONEY表IDCARD字段关联
end;
/
最好用游标。
CREATE OR REPLACE PROCEDURE protest IScursor c_moneyinfo is select * from money_info where total_flag='1' order by idcard;
v_x number(8,2);
begin
for v_moneyinfo in c_moneyinfo loop
if MONTHS_BETWEEN(sysdate,v_moneyinfo.ADD_DATE)>0 then
begin
select X into v_x from table where IDCARD = v_moneyinfo.IDCARD;
UPDATE money_info SET
total_money = total_money+(TO_NUMBER(TO_char(sysdate,'mm'))-TO_NUMBER(TO_char(ADD_DATE,'mm')))*v_x,
add_date = sysdate
WHERE id_card = v_moneyinfo.IDCARD;
commit;
end;
end if;
end loop;
end test;