create or replace procedure UP_MODIFYORGCODE (oldcode nvarchar2 default null,newcode nvarchar2 default null,newparentcode nvarchar2 default null) as oldsupcode nvarchar2(1000); begin insert into syncdata(id,tablename,infoid,opertype,timespan) select (select max(id)+1 from syncdata)+rownum,'UT_ORGCLASS',pkid,'Update',sysdate from UT_ORGCLASS where orgcode like ''||to_char(oldcode)||'%'; update UT_ORGCLASS set orgcode=concat(newcode,substr(orgcode,length(oldcode)+1,length(orgcode)-length(oldcode))) where orgcode like ''||to_char(oldcode)||'%';insert into syncdata(id,tablename,infoid,opertype,timespan) select (select max(id)+1 from syncdata)+rownum,'UT_ORGCOMPANY',pkid,'Update',sysdate from UT_ORGCOMPANY where orgcode like ''||to_char(oldcode)||'%'; update UT_ORGCOMPANY set orgcode=concat(newcode,substr(orgcode,length(oldcode)+1,length(orgcode)-length(oldcode))) where orgcode like ''||to_char(oldcode)||'%';insert into syncdata(id,tablename,infoid,opertype,timespan) select (select max(id)+1 from syncdata)+rownum,'UT_ORGREWARDSPUNISH',pkid,'Update',sysdate from UT_ORGREWARDSPUNISH where orgcode like ''||to_char(oldcode)||'%'; update UT_ORGREWARDSPUNISH set orgcode=concat(newcode,substr(orgcode,length(oldcode)+1,length(orgcode)-length(oldcode))) where orgcode like ''||to_char(oldcode)||'%';insert into syncdata(id,tablename,infoid,opertype,timespan) select (select max(id)+1 from syncdata)+rownum,'UT_ORGTAB_WORKPLAN',pkid,'Update',sysdate from UT_ORGTAB_WORKPLAN where orgcode like ''||to_char(oldcode)||'%'; update UT_ORGTAB_WORKPLAN set orgcode=concat(newcode,substr(orgcode,length(oldcode)+1,length(orgcode)-length(oldcode))) where orgcode like ''||to_char(oldcode)||'%';insert into syncdata(id,tablename,infoid,opertype,timespan) select (select max(id)+1 from syncdata)+rownum,'UT_ORGTAB_WORKSUMUP',pkid,'Update',sysdate from UT_ORGTAB_WORKSUMUP where orgcode like ''||to_char(oldcode)||'%'; update UT_ORGTAB_WORKSUMUP set orgcode=concat(newcode,substr(orgcode,length(oldcode)+1,length(orgcode)-length(oldcode))) where orgcode like ''||to_char(oldcode)||'%';insert into syncdata(id,tablename,infoid,opertype,timespan) select (select max(id)+1 from syncdata)+rownum,'UT_ORGTAB_DLIVING',pkid,'Update',sysdate from UT_ORGTAB_DLIVING where orgcode like ''||to_char(oldcode)||'%'; update UT_ORGTAB_DLIVING set orgcode=concat(newcode,substr(orgcode,length(oldcode)+1,length(orgcode)-length(oldcode))) where orgcode like ''||to_char(oldcode)||'%';insert into syncdata(id,tablename,infoid,opertype,timespan) select (select max(id)+1 from syncdata)+rownum,'UT_ORGTAB_DCOMMENTS',pkid,'Update',sysdate from UT_ORGTAB_DCOMMENTS where orgcode like ''||to_char(oldcode)||'%'; update UT_ORGTAB_DCOMMENTS set orgcode=concat(newcode,substr(orgcode,length(oldcode)+1,length(orgcode)-length(oldcode))) where orgcode like ''||to_char(oldcode)||'%';insert into syncdata(id,tablename,infoid,opertype,timespan) select (select max(id)+1 from syncdata)+rownum,'UT_ORGTAB_OTHERWORK',pkid,'Update',sysdate from UT_ORGTAB_OTHERWORK where orgcode like ''||to_char(oldcode)||'%'; update UT_ORGTAB_OTHERWORK set orgcode=concat(newcode,substr(orgcode,length(oldcode)+1,length(orgcode)-length(oldcode))) where orgcode like ''||to_char(oldcode)||'%';insert into syncdata(id,tablename,infoid,opertype,timespan) select (select max(id)+1 from syncdata)+rownum,'UT_ORGTAB_PARTYAFFICHE',pkid,'Update',sysdate from UT_ORGTAB_PARTYAFFICHE where orgcode like ''||to_char(oldcode)||'%'; update UT_ORGTAB_PARTYAFFICHE set orgcode=concat(newcode,substr(orgcode,length(oldcode)+1,length(orgcode)-length(oldcode))) where orgcode like ''||to_char(oldcode)||'%';insert into syncdata(id,tablename,infoid,opertype,timespan) select (select max(id)+1 from syncdata)+rownum,'UT_ORGTAB_PARTYHONOUR',pkid,'Update',sysdate from UT_ORGTAB_PARTYHONOUR where orgcode like ''||to_char(oldcode)||'%'; update UT_ORGTAB_PARTYHONOUR set orgcode=concat(newcode,substr(orgcode,length(oldcode)+1,length(orgcode)-length(oldcode))) where orgcode like ''||to_char(oldcode)||'%';insert into syncdata(id,tablename,infoid,opertype,timespan) select (select max(id)+1 from syncdata)+rownum,'UT_ORGTAB_PARTYEXPENSE',pkid,'Update',sysdate from UT_ORGTAB_PARTYEXPENSE where orgcode like ''||to_char(oldcode)||'%'; update UT_ORGTAB_PARTYEXPENSE set orgcode=concat(newcode,substr(orgcode,length(oldcode)+1,length(orgcode)-length(oldcode))) where orgcode like ''||to_char(oldcode)||'%';insert into syncdata(id,tablename,infoid,opertype,timespan) select (select max(id)+1 from syncdata)+rownum,'UT_ORGTAB_SENDPAPER',pkid,'Update',sysdate from UT_ORGTAB_SENDPAPER where orgcode like ''||to_char(oldcode)||'%'; update UT_ORGTAB_SENDPAPER set orgcode=concat(newcode,substr(orgcode,length(oldcode)+1,length(orgcode)-length(oldcode))) where orgcode like ''||to_char(oldcode)||'%';
insert into syncdata(id,tablename,infoid,opertype,timespan) select (select max(id)+1 from syncdata)+rownum,'UT_MEMBER',pkid,'Update',sysdate from UT_MEMBER where BELONGDZB like ''||to_char(oldcode)||'%'; update UT_MEMBER set BELONGDZB=concat(newcode,substr(BELONGDZB,length(oldcode)+1,length(BELONGDZB)-length(oldcode))) where BELONGDZB like ''||to_char(oldcode)||'%';insert into syncdata(id,tablename,infoid,opertype,timespan) select (select max(id)+1 from syncdata)+rownum,'UT_MEMBER_GROWINFO',pkid,'Update',sysdate from UT_MEMBER_GROWINFO where BELONGDZB like ''||to_char(oldcode)||'%'; update UT_MEMBER_GROWINFO set BELONGDZB=concat(newcode,substr(BELONGDZB,length(oldcode)+1,length(BELONGDZB)-length(oldcode))) where BELONGDZB like ''||to_char(oldcode)||'%';insert into syncdata(id,tablename,infoid,opertype,timespan) select (select max(id)+1 from syncdata)+rownum,'UT_SEC_USER',pkid,'Update',sysdate from UT_SEC_USER where RELATINGPARTY like ''||to_char(oldcode)||'%'; update UT_SEC_USER set RELATINGPARTY=concat(newcode,substr(RELATINGPARTY,length(oldcode)+1,length(RELATINGPARTY)-length(oldcode))) where RELATINGPARTY like ''||to_char(oldcode)||'%';insert into syncdata(id,tablename,infoid,opertype,timespan) select (select max(id)+1 from syncdata)+rownum,'UT_ORG',pkid,'Update',sysdate from UT_ORG where SUPORGCODE like ''||to_char(oldcode)||'%'; update UT_ORG set SUPORGCODE=concat(newcode,substr(SUPORGCODE,length(oldcode)+1,length(SUPORGCODE)-length(oldcode))) where SUPORGCODE like ''||to_char(oldcode)||'%';insert into syncdata(id,tablename,infoid,opertype,timespan) select (select max(id)+1 from syncdata)+rownum,'UT_ORG',pkid,'Update',sysdate from UT_ORG where ORGCODE like ''||to_char(oldcode)||'%'; update UT_ORG set ORGCODE=concat(newcode,substr(ORGCODE,length(oldcode)+1,length(ORGCODE)-length(oldcode))) where ORGCODE like ''||to_char(oldcode)||'%';insert into syncdata(id,tablename,infoid,opertype,timespan) select (select max(id)+1 from syncdata)+rownum,'UT_PARTY_AFFLUXMEMBER',pkid,'Update',sysdate from UT_PARTY_AFFLUXMEMBER where AffluxPartyCode like ''||to_char(oldcode)||'%'; update UT_PARTY_AFFLUXMEMBER set AffluxPartyCode=concat(newcode,substr(AffluxPartyCode,length(oldcode)+1,length(AffluxPartyCode)-length(oldcode))) where AffluxPartyCode like ''||to_char(oldcode)||'%';insert into syncdata(id,tablename,infoid,opertype,timespan) select (select max(id)+1 from syncdata)+rownum,'UT_MEMBERHEADSHI',pkid,'Update',sysdate from UT_MEMBERHEADSHI where HOLDPARTYORG like ''||to_char(oldcode)||'%'; update UT_MEMBERHEADSHI set HOLDPARTYORG=concat(newcode,substr(HOLDPARTYORG,length(oldcode)+1,length(HOLDPARTYORG)-length(oldcode))) where HOLDPARTYORG like ''||to_char(oldcode)||'%';insert into syncdata(id,tablename,infoid,opertype,timespan) select (select max(id)+1 from syncdata)+rownum,'UT_MEMBERORGRELA',pkid,'Update',sysdate from UT_MEMBERORGRELA where PARTYBRANCH like ''||to_char(oldcode)||'%'; update UT_MEMBERORGRELA set PARTYBRANCH=concat(newcode,substr(PARTYBRANCH,length(oldcode)+1,length(PARTYBRANCH)-length(oldcode))),ENTERTYPE='188',LEAVETYPE='288',TAGETPARTYBRANCH=oldcode,ENTERDATE=sysdate,LEAVEDATE=sysdate where (select status from ut_member where ut_member.pkid=UT_MEMBERORGRELA.mpkid) in (4,5) and PARTYBRANCH like ''||to_char(oldcode)||'%';--insert into syncdata(id,tablename,infoid,opertype,timespan) --select (select max(id)+1 from syncdata)+rownum,'UT_MEMBERORGRELA',pkid,'Update',sysdate from UT_MEMBERORGRELA where TAGETPARTYBRANCH like ''||to_char(oldcode)||'%'; --update UT_MEMBERORGRELA set TAGETPARTYBRANCH=concat(newcode,substr(TAGETPARTYBRANCH,length(oldcode)+1,length(TAGETPARTYBRANCH)-length(oldcode))) where TAGETPARTYBRANCH like ''||to_char(oldcode)||'%';insert into syncdata(id,tablename,infoid,opertype,timespan) select (select max(id)+1 from syncdata)+rownum,'UT_MEMBERCLAN',pkid,'Update',sysdate from UT_MEMBERCLAN where BEGINPARTYBRANCH like ''||to_char(oldcode)||'%'; update UT_MEMBERCLAN set BEGINPARTYBRANCH=concat(newcode,substr(BEGINPARTYBRANCH,length(oldcode)+1,length(BEGINPARTYBRANCH)-length(oldcode))) where BEGINPARTYBRANCH like ''||to_char(oldcode)||'%';insert into syncdata(id,tablename,infoid,opertype,timespan) select (select max(id)+1 from syncdata)+rownum,'UT_MEMBERCLAN',pkid,'Update',sysdate from UT_MEMBERCLAN where BEGINPARTYBRANCH like ''||to_char(oldcode)||'%'; update UT_MEMBERCLAN set BEGINPARTYBRANCH=concat(newcode,substr(BEGINPARTYBRANCH,length(oldcode)+1,length(BEGINPARTYBRANCH)-length(oldcode))) where BEGINPARTYBRANCH like ''||to_char(oldcode)||'%';--报告期详细数据表 update UT_RPT_DURATIONDETAIL set ORGCODE=concat(newcode,substr(ORGCODE,length(oldcode)+1,length(ORGCODE)-length(oldcode))) where ORGCODE like ''||to_char(oldcode)||'%'; --报告期主表 update UT_RPT_DURATION set ORGCODE=concat(newcode,substr(ORGCODE,length(oldcode)+1,length(ORGCODE)-length(oldcode))) where ORGCODE like ''||to_char(oldcode)||'%'; --UT_RPT_REPORTBASIC --update UT_RPT_REPORTBASIC set ORGCODE=concat(newcode,substr(ORGCODE,length(oldcode)+1,length(ORGCODE)-length(oldcode))) where ORGCODE like ''||to_char(oldcode)||'%'; --update UT_RPT_REPORTBASIC set REPORTORGCODE=concat(newcode,substr(REPORTORGCODE,length(oldcode)+1,length(REPORTORGCODE)-length(oldcode))) where REPORTORGCODE like ''||to_char(oldcode)||'%'; --UT_RPT_STATICBACK update UT_RPT_STATICBACK set ORGCODE=concat(newcode,substr(ORGCODE,length(oldcode)+1,length(ORGCODE)-length(oldcode))) where ORGCODE like ''||to_char(oldcode)||'%'; update UT_RPT_STATICBACK set SUPORGCODE=concat(newcode,substr(SUPORGCODE,length(oldcode)+1,length(SUPORGCODE)-length(oldcode))) where SUPORGCODE like ''||to_char(oldcode)||'%'; --UT_RTP_REPORTCOVER报表封面 update UT_RTP_REPORTCOVER set ORGCODE=concat(newcode,substr(ORGCODE,length(oldcode)+1,length(ORGCODE)-length(oldcode))) where ORGCODE like ''||to_char(oldcode)||'%'; --UT_RPT_AUTODETAIL update UT_RPT_AUTODETAIL set ORGCODE=concat(newcode,substr(ORGCODE,length(oldcode)+1,length(ORGCODE)-length(oldcode))) where ORGCODE like ''||to_char(oldcode)||'%'; --UT_RPT_AOTUSTATIC update UT_RPT_AOTUSTATIC set ORGCODE=concat(newcode,substr(ORGCODE,length(oldcode)+1,length(ORGCODE)-length(oldcode))) where ORGCODE like ''||to_char(oldcode)||'%'; --UT_RPT_REPORT update UT_RPT_REPORT set ORGCODE=concat(newcode,substr(ORGCODE,length(oldcode)+1,length(ORGCODE)-length(oldcode))) where ORGCODE like ''||to_char(oldcode)||'%'; update UT_RPT_REPORT set REPORTORGCODE=concat(newcode,substr(REPORTORGCODE,length(oldcode)+1,length(REPORTORGCODE)-length(oldcode))) where REPORTORGCODE like ''||to_char(oldcode)||'%';insert into syncdata(id,tablename,infoid,opertype,timespan) select (select max(id)+1 from syncdata)+rownum,'UT_ORG',pkid,'Update',sysdate from UT_ORG where ORGCODE=newcode; --declare oldsupcode nvarchar2; select SUPORGCODE into oldsupcode from UT_ORG where ORGCODE=newcode; if (oldsupcode = newparentcode) then update UT_ORG set SUPORGCODE=newparentcode where ORGCODE=newcode;else update UT_ORG set SUPORGCODE=newparentcode,sortorder=(select max(sortorder)+1 from UT_ORG where SUPORGCODE=newparentcode) where ORGCODE=newcode; end if;commit; exception when others then rollback;end UP_MODIFYORGCODE;
begin up_modifyorgcode('001.001.032.005.001.999','001.001.032.005.001.001.999','001.001.032.005.001.001'); end;
exec up_modifyorgcode('001.001.032.005.001.999','001.001.032.005.001.001.999','001.001.032.005.001.001')
exec up_modifyorgcode('001.001.032.005.001.999','001.001.032.005.001.001.999','001.001.032.005.001.001');
end;
提示
出现符号"up_modifyorgcode"在需要下列之一时: :=.(@%;
符号":="被替换为"up_modifyorgcode"后继续。
放到存储过程中,记得不需要加 exec
直接 up_modifyorgcode('001.001.032.005.001.999','001.001.032.005.001.001.999','001.001.032.005.001.001');
就可以了
(oldcode nvarchar2 default null,newcode nvarchar2 default null,newparentcode nvarchar2 default null)
as
oldsupcode nvarchar2(1000);
begin
insert into syncdata(id,tablename,infoid,opertype,timespan)
select (select max(id)+1 from syncdata)+rownum,'UT_ORGCLASS',pkid,'Update',sysdate from UT_ORGCLASS where orgcode like ''||to_char(oldcode)||'%';
update UT_ORGCLASS set orgcode=concat(newcode,substr(orgcode,length(oldcode)+1,length(orgcode)-length(oldcode))) where orgcode like ''||to_char(oldcode)||'%';insert into syncdata(id,tablename,infoid,opertype,timespan)
select (select max(id)+1 from syncdata)+rownum,'UT_ORGCOMPANY',pkid,'Update',sysdate from UT_ORGCOMPANY where orgcode like ''||to_char(oldcode)||'%';
update UT_ORGCOMPANY set orgcode=concat(newcode,substr(orgcode,length(oldcode)+1,length(orgcode)-length(oldcode))) where orgcode like ''||to_char(oldcode)||'%';insert into syncdata(id,tablename,infoid,opertype,timespan)
select (select max(id)+1 from syncdata)+rownum,'UT_ORGREWARDSPUNISH',pkid,'Update',sysdate from UT_ORGREWARDSPUNISH where orgcode like ''||to_char(oldcode)||'%';
update UT_ORGREWARDSPUNISH set orgcode=concat(newcode,substr(orgcode,length(oldcode)+1,length(orgcode)-length(oldcode))) where orgcode like ''||to_char(oldcode)||'%';insert into syncdata(id,tablename,infoid,opertype,timespan)
select (select max(id)+1 from syncdata)+rownum,'UT_ORGTAB_WORKPLAN',pkid,'Update',sysdate from UT_ORGTAB_WORKPLAN where orgcode like ''||to_char(oldcode)||'%';
update UT_ORGTAB_WORKPLAN set orgcode=concat(newcode,substr(orgcode,length(oldcode)+1,length(orgcode)-length(oldcode))) where orgcode like ''||to_char(oldcode)||'%';insert into syncdata(id,tablename,infoid,opertype,timespan)
select (select max(id)+1 from syncdata)+rownum,'UT_ORGTAB_WORKSUMUP',pkid,'Update',sysdate from UT_ORGTAB_WORKSUMUP where orgcode like ''||to_char(oldcode)||'%';
update UT_ORGTAB_WORKSUMUP set orgcode=concat(newcode,substr(orgcode,length(oldcode)+1,length(orgcode)-length(oldcode))) where orgcode like ''||to_char(oldcode)||'%';insert into syncdata(id,tablename,infoid,opertype,timespan)
select (select max(id)+1 from syncdata)+rownum,'UT_ORGTAB_DLIVING',pkid,'Update',sysdate from UT_ORGTAB_DLIVING where orgcode like ''||to_char(oldcode)||'%';
update UT_ORGTAB_DLIVING set orgcode=concat(newcode,substr(orgcode,length(oldcode)+1,length(orgcode)-length(oldcode))) where orgcode like ''||to_char(oldcode)||'%';insert into syncdata(id,tablename,infoid,opertype,timespan)
select (select max(id)+1 from syncdata)+rownum,'UT_ORGTAB_DCOMMENTS',pkid,'Update',sysdate from UT_ORGTAB_DCOMMENTS where orgcode like ''||to_char(oldcode)||'%';
update UT_ORGTAB_DCOMMENTS set orgcode=concat(newcode,substr(orgcode,length(oldcode)+1,length(orgcode)-length(oldcode))) where orgcode like ''||to_char(oldcode)||'%';insert into syncdata(id,tablename,infoid,opertype,timespan)
select (select max(id)+1 from syncdata)+rownum,'UT_ORGTAB_OTHERWORK',pkid,'Update',sysdate from UT_ORGTAB_OTHERWORK where orgcode like ''||to_char(oldcode)||'%';
update UT_ORGTAB_OTHERWORK set orgcode=concat(newcode,substr(orgcode,length(oldcode)+1,length(orgcode)-length(oldcode))) where orgcode like ''||to_char(oldcode)||'%';insert into syncdata(id,tablename,infoid,opertype,timespan)
select (select max(id)+1 from syncdata)+rownum,'UT_ORGTAB_PARTYAFFICHE',pkid,'Update',sysdate from UT_ORGTAB_PARTYAFFICHE where orgcode like ''||to_char(oldcode)||'%';
update UT_ORGTAB_PARTYAFFICHE set orgcode=concat(newcode,substr(orgcode,length(oldcode)+1,length(orgcode)-length(oldcode))) where orgcode like ''||to_char(oldcode)||'%';insert into syncdata(id,tablename,infoid,opertype,timespan)
select (select max(id)+1 from syncdata)+rownum,'UT_ORGTAB_PARTYHONOUR',pkid,'Update',sysdate from UT_ORGTAB_PARTYHONOUR where orgcode like ''||to_char(oldcode)||'%';
update UT_ORGTAB_PARTYHONOUR set orgcode=concat(newcode,substr(orgcode,length(oldcode)+1,length(orgcode)-length(oldcode))) where orgcode like ''||to_char(oldcode)||'%';insert into syncdata(id,tablename,infoid,opertype,timespan)
select (select max(id)+1 from syncdata)+rownum,'UT_ORGTAB_PARTYEXPENSE',pkid,'Update',sysdate from UT_ORGTAB_PARTYEXPENSE where orgcode like ''||to_char(oldcode)||'%';
update UT_ORGTAB_PARTYEXPENSE set orgcode=concat(newcode,substr(orgcode,length(oldcode)+1,length(orgcode)-length(oldcode))) where orgcode like ''||to_char(oldcode)||'%';insert into syncdata(id,tablename,infoid,opertype,timespan)
select (select max(id)+1 from syncdata)+rownum,'UT_ORGTAB_SENDPAPER',pkid,'Update',sysdate from UT_ORGTAB_SENDPAPER where orgcode like ''||to_char(oldcode)||'%';
update UT_ORGTAB_SENDPAPER set orgcode=concat(newcode,substr(orgcode,length(oldcode)+1,length(orgcode)-length(oldcode))) where orgcode like ''||to_char(oldcode)||'%';
select (select max(id)+1 from syncdata)+rownum,'UT_MEMBER',pkid,'Update',sysdate from UT_MEMBER where BELONGDZB like ''||to_char(oldcode)||'%';
update UT_MEMBER set BELONGDZB=concat(newcode,substr(BELONGDZB,length(oldcode)+1,length(BELONGDZB)-length(oldcode))) where BELONGDZB like ''||to_char(oldcode)||'%';insert into syncdata(id,tablename,infoid,opertype,timespan)
select (select max(id)+1 from syncdata)+rownum,'UT_MEMBER_GROWINFO',pkid,'Update',sysdate from UT_MEMBER_GROWINFO where BELONGDZB like ''||to_char(oldcode)||'%';
update UT_MEMBER_GROWINFO set BELONGDZB=concat(newcode,substr(BELONGDZB,length(oldcode)+1,length(BELONGDZB)-length(oldcode))) where BELONGDZB like ''||to_char(oldcode)||'%';insert into syncdata(id,tablename,infoid,opertype,timespan)
select (select max(id)+1 from syncdata)+rownum,'UT_SEC_USER',pkid,'Update',sysdate from UT_SEC_USER where RELATINGPARTY like ''||to_char(oldcode)||'%';
update UT_SEC_USER set RELATINGPARTY=concat(newcode,substr(RELATINGPARTY,length(oldcode)+1,length(RELATINGPARTY)-length(oldcode))) where RELATINGPARTY like ''||to_char(oldcode)||'%';insert into syncdata(id,tablename,infoid,opertype,timespan)
select (select max(id)+1 from syncdata)+rownum,'UT_ORG',pkid,'Update',sysdate from UT_ORG where SUPORGCODE like ''||to_char(oldcode)||'%';
update UT_ORG set SUPORGCODE=concat(newcode,substr(SUPORGCODE,length(oldcode)+1,length(SUPORGCODE)-length(oldcode))) where SUPORGCODE like ''||to_char(oldcode)||'%';insert into syncdata(id,tablename,infoid,opertype,timespan)
select (select max(id)+1 from syncdata)+rownum,'UT_ORG',pkid,'Update',sysdate from UT_ORG where ORGCODE like ''||to_char(oldcode)||'%';
update UT_ORG set ORGCODE=concat(newcode,substr(ORGCODE,length(oldcode)+1,length(ORGCODE)-length(oldcode))) where ORGCODE like ''||to_char(oldcode)||'%';insert into syncdata(id,tablename,infoid,opertype,timespan)
select (select max(id)+1 from syncdata)+rownum,'UT_PARTY_AFFLUXMEMBER',pkid,'Update',sysdate from UT_PARTY_AFFLUXMEMBER where AffluxPartyCode like ''||to_char(oldcode)||'%';
update UT_PARTY_AFFLUXMEMBER set AffluxPartyCode=concat(newcode,substr(AffluxPartyCode,length(oldcode)+1,length(AffluxPartyCode)-length(oldcode))) where AffluxPartyCode like ''||to_char(oldcode)||'%';insert into syncdata(id,tablename,infoid,opertype,timespan)
select (select max(id)+1 from syncdata)+rownum,'UT_MEMBERHEADSHI',pkid,'Update',sysdate from UT_MEMBERHEADSHI where HOLDPARTYORG like ''||to_char(oldcode)||'%';
update UT_MEMBERHEADSHI set HOLDPARTYORG=concat(newcode,substr(HOLDPARTYORG,length(oldcode)+1,length(HOLDPARTYORG)-length(oldcode))) where HOLDPARTYORG like ''||to_char(oldcode)||'%';insert into syncdata(id,tablename,infoid,opertype,timespan)
select (select max(id)+1 from syncdata)+rownum,'UT_MEMBERORGRELA',pkid,'Update',sysdate from UT_MEMBERORGRELA where PARTYBRANCH like ''||to_char(oldcode)||'%';
update UT_MEMBERORGRELA set PARTYBRANCH=concat(newcode,substr(PARTYBRANCH,length(oldcode)+1,length(PARTYBRANCH)-length(oldcode))),ENTERTYPE='188',LEAVETYPE='288',TAGETPARTYBRANCH=oldcode,ENTERDATE=sysdate,LEAVEDATE=sysdate where (select status from ut_member where ut_member.pkid=UT_MEMBERORGRELA.mpkid) in (4,5) and PARTYBRANCH like ''||to_char(oldcode)||'%';--insert into syncdata(id,tablename,infoid,opertype,timespan)
--select (select max(id)+1 from syncdata)+rownum,'UT_MEMBERORGRELA',pkid,'Update',sysdate from UT_MEMBERORGRELA where TAGETPARTYBRANCH like ''||to_char(oldcode)||'%';
--update UT_MEMBERORGRELA set TAGETPARTYBRANCH=concat(newcode,substr(TAGETPARTYBRANCH,length(oldcode)+1,length(TAGETPARTYBRANCH)-length(oldcode))) where TAGETPARTYBRANCH like ''||to_char(oldcode)||'%';insert into syncdata(id,tablename,infoid,opertype,timespan)
select (select max(id)+1 from syncdata)+rownum,'UT_MEMBERCLAN',pkid,'Update',sysdate from UT_MEMBERCLAN where BEGINPARTYBRANCH like ''||to_char(oldcode)||'%';
update UT_MEMBERCLAN set BEGINPARTYBRANCH=concat(newcode,substr(BEGINPARTYBRANCH,length(oldcode)+1,length(BEGINPARTYBRANCH)-length(oldcode))) where BEGINPARTYBRANCH like ''||to_char(oldcode)||'%';insert into syncdata(id,tablename,infoid,opertype,timespan)
select (select max(id)+1 from syncdata)+rownum,'UT_MEMBERCLAN',pkid,'Update',sysdate from UT_MEMBERCLAN where BEGINPARTYBRANCH like ''||to_char(oldcode)||'%';
update UT_MEMBERCLAN set BEGINPARTYBRANCH=concat(newcode,substr(BEGINPARTYBRANCH,length(oldcode)+1,length(BEGINPARTYBRANCH)-length(oldcode))) where BEGINPARTYBRANCH like ''||to_char(oldcode)||'%';--报告期详细数据表
update UT_RPT_DURATIONDETAIL set ORGCODE=concat(newcode,substr(ORGCODE,length(oldcode)+1,length(ORGCODE)-length(oldcode))) where ORGCODE like ''||to_char(oldcode)||'%';
--报告期主表
update UT_RPT_DURATION set ORGCODE=concat(newcode,substr(ORGCODE,length(oldcode)+1,length(ORGCODE)-length(oldcode))) where ORGCODE like ''||to_char(oldcode)||'%';
--UT_RPT_REPORTBASIC
--update UT_RPT_REPORTBASIC set ORGCODE=concat(newcode,substr(ORGCODE,length(oldcode)+1,length(ORGCODE)-length(oldcode))) where ORGCODE like ''||to_char(oldcode)||'%';
--update UT_RPT_REPORTBASIC set REPORTORGCODE=concat(newcode,substr(REPORTORGCODE,length(oldcode)+1,length(REPORTORGCODE)-length(oldcode))) where REPORTORGCODE like ''||to_char(oldcode)||'%';
--UT_RPT_STATICBACK
update UT_RPT_STATICBACK set ORGCODE=concat(newcode,substr(ORGCODE,length(oldcode)+1,length(ORGCODE)-length(oldcode))) where ORGCODE like ''||to_char(oldcode)||'%';
update UT_RPT_STATICBACK set SUPORGCODE=concat(newcode,substr(SUPORGCODE,length(oldcode)+1,length(SUPORGCODE)-length(oldcode))) where SUPORGCODE like ''||to_char(oldcode)||'%';
--UT_RTP_REPORTCOVER报表封面
update UT_RTP_REPORTCOVER set ORGCODE=concat(newcode,substr(ORGCODE,length(oldcode)+1,length(ORGCODE)-length(oldcode))) where ORGCODE like ''||to_char(oldcode)||'%';
--UT_RPT_AUTODETAIL
update UT_RPT_AUTODETAIL set ORGCODE=concat(newcode,substr(ORGCODE,length(oldcode)+1,length(ORGCODE)-length(oldcode))) where ORGCODE like ''||to_char(oldcode)||'%';
--UT_RPT_AOTUSTATIC
update UT_RPT_AOTUSTATIC set ORGCODE=concat(newcode,substr(ORGCODE,length(oldcode)+1,length(ORGCODE)-length(oldcode))) where ORGCODE like ''||to_char(oldcode)||'%';
--UT_RPT_REPORT
update UT_RPT_REPORT set ORGCODE=concat(newcode,substr(ORGCODE,length(oldcode)+1,length(ORGCODE)-length(oldcode))) where ORGCODE like ''||to_char(oldcode)||'%';
update UT_RPT_REPORT set REPORTORGCODE=concat(newcode,substr(REPORTORGCODE,length(oldcode)+1,length(REPORTORGCODE)-length(oldcode))) where REPORTORGCODE like ''||to_char(oldcode)||'%';insert into syncdata(id,tablename,infoid,opertype,timespan)
select (select max(id)+1 from syncdata)+rownum,'UT_ORG',pkid,'Update',sysdate from UT_ORG where ORGCODE=newcode;
--declare oldsupcode nvarchar2;
select SUPORGCODE into oldsupcode from UT_ORG where ORGCODE=newcode;
if (oldsupcode = newparentcode)
then
update UT_ORG set SUPORGCODE=newparentcode where ORGCODE=newcode;else update UT_ORG set SUPORGCODE=newparentcode,sortorder=(select max(sortorder)+1 from UT_ORG where SUPORGCODE=newparentcode) where ORGCODE=newcode;
end if;commit;
exception
when others then
rollback;end UP_MODIFYORGCODE;
up_modifyorgcode('001.001.032.005.001.999','001.001.032.005.001.001.999','001.001.032.005.001.001');
end;
直接这样执行肯定不行啊,都没调用存储过程,在test window下测试,调用存储过程执行
exec 过程名(参数);
end;