在存储过程就是要复制一条记录保存在本表,新记录要修改两个字段
INSERT INTO PD_PROJECT SELECT * FROM PD_PROJECT WHERE ROW_ID =RID;
用这个有主键约束不行
create or replace function FN_PD_FINA_BFBK(ROWIDS in varchar2)
return varchar2 is
RES varchar2(200);
STRRLT varchar2(4000);
MT MYTABLE := MYTABLE();
RID varchar2(32);
/* V_FINA_STATUS varchar2(30);*/
/* V_FINA_MONEY_SUM NUMBER(16,2);*/
V_FUND_IN_SUM NUMBER(16,2);
V_FINA_W_MONEY_SUM NUMBER(16,2);
begin
STRRLT := '';
MT := FN_PD_SPLIT(ROWIDS, ',');
for I in MT.FIRST .. MT.LAST loop
if MT.EXISTS(I) then
RID := MT(I);
select FUND_IN_SUM-FINA_MONEY_SUM into V_FINA_W_MONEY_SUM from PD_PROJECT where ROW_ID = RID;
--select FINA_W_MONEY_SUM into V_FINA_W_MONEY_SUM from PD_PROJECT where ROW_ID = RID;
select FUND_IN_SUM into V_FUND_IN_SUM from PD_PROJECT where ROW_ID = RID;
IF V_FINA_W_MONEY_SUM > 0 THEN
/* update PD_PROJECT_FINA_APPLY A
set A.FINA_MONEY=(SELECT FUND_IN_SUM FROM PD_PROJECT WHERE ROW_ID = RID);*/
update PD_PROJECT T
set T.FINA_STATUS = '02',
/* T.FINA_MONEY_SUM=FUND_IN_SUM,*/
T.FINA_W_MONEY_SUM=V_FINA_W_MONEY_SUM
where T.ROW_ID = RID;
create table tmp as SELECT * FROM PD_PROJECT WHERE ROW_ID =RID;
update tmp
set FUND_IN_SUM='1',FINA_MONEY_SUM='2';
commit;
where row_id=RID
delete from PD_PROJECT WHERE ROW_ID =RID;
commit;
insert into PD_PROJECT select * from tmp;
commit;
END IF ;
IF V_FINA_W_MONEY_SUM = 0 THEN
update PD_PROJECT T
set T.FINA_STATUS = '01'
where T.ROW_ID = RID;
END IF ;
IF V_FINA_W_MONEY_SUM = V_FUND_IN_SUM THEN
update PD_PROJECT T
set T.FINA_STATUS = ''
where T.ROW_ID = RID;
END IF ;
end if;
end loop;
return RES;
end FN_PD_FINA_BFBK;