那个人来了 2019-08-20 16:35 采纳率: 0%
浏览 1278

orcl如何复制一条记录,并修改其中两个字段,id设有主键,求解

在存储过程就是要复制一条记录保存在本表,新记录要修改两个字段
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;

  • 写回答

1条回答

  • ashuaige 2019-08-20 17:07
    关注

    建一个临时表。
    create table tmp as SELECT * FROM PD_PROJECT WHERE ROW_ID =RID;
    update tmp
    set col1='',col2=''
    where row_id=RID;
    commit;
    delete from PD_PROJECT WHERE ROW_ID =RID;
    commit;
    insert into PD_PROJECT select * from tmp;
    commit;

    评论

报告相同问题?

悬赏问题

  • ¥15 matlab图像高斯低通滤波
  • ¥15 针对曲面部件的制孔路径规划,大家有什么思路吗
  • ¥15 钢筋实图交点识别,机器视觉代码
  • ¥15 如何在Linux系统中,但是在window系统上idea里面可以正常运行?(相关搜索:jar包)
  • ¥50 400g qsfp 光模块iphy方案
  • ¥15 两块ADC0804用proteus仿真时,出现异常
  • ¥15 关于风控系统,如何去选择
  • ¥15 这款软件是什么?需要能满足我的需求
  • ¥15 SpringSecurityOauth2登陆前后request不一致
  • ¥15 禅道二次开发编辑版本,上传不了发行包