20190807更新数据相关笔记

如下：

--场景三
--多条记录，确认使用其中一条，并确认/更新正确，更新所有关联表使用选择的customerid，删除企业基本信息无效）
--（打开所有表，看哪些表用到了customerid)

select * from    crm_customer_baseinfo where customername in (
    select customername from crm_customer_baseinfo group by  customername  having count(1)>1  
)  
and length(customername)>4
order by customername ;

存储过程如下：

CREATE OR REPLACE PROCEDURE UPDATE_CUSTOMERID_AllTAble
(
  V_TRUECUSTOMERID   IN NUMBER,
  V_DELCUSTOMERID    IN NUMBER
) IS

BEGIN
  /*****先更新数据为需要的customerid的值******/
  --customerid
  update CRM_CORPREPR_PRO c   set   c.customerid=V_TRUECUSTOMERID where c.customerid=V_DELCUSTOMERID;
  update CRM_CORP_EXDESCINFO_PRO c set c.customerid=V_TRUECUSTOMERID where c.customerid=V_DELCUSTOMERID;
  update CRM_CUSTOMER_EXINFO_PRO c set c.customerid=V_TRUECUSTOMERID where c.customerid=V_DELCUSTOMERID;
  update CRM_FINANCIAL_TYPE c set c.customerid=V_TRUECUSTOMERID where c.customerid=V_DELCUSTOMERID;
  update CRM_GUARANTEESANDLIABILI c set c.customerid=V_TRUECUSTOMERID where c.customerid=V_DELCUSTOMERID;
  update CRM_GUARANTEESANDLIABILI_PRO c set c.customerid=V_TRUECUSTOMERID where c.customerid=V_DELCUSTOMERID;
  update CRM_INFO_TAXES c set c.customerid=V_TRUECUSTOMERID where c.customerid=V_DELCUSTOMERID;
  update CRM_INFO_TAXES_PRO c set c.customerid=V_TRUECUSTOMERID where c.customerid=V_DELCUSTOMERID;
  update CRM_LEGAL_AND_ARBITRATION c set c.customerid=V_TRUECUSTOMERID where c.customerid=V_DELCUSTOMERID;
  update CRM_LEGAL_AND_ARBITRATION_PRO c set c.customerid=V_TRUECUSTOMERID where c.customerid=V_DELCUSTOMERID;
  update CRM_PERSON_EXINFO_PRO c set c.customerid=V_TRUECUSTOMERID where c.customerid=V_DELCUSTOMERID;
  update CRM_PERSON_INFO c set c.customerid=V_TRUECUSTOMERID where c.customerid=V_DELCUSTOMERID;
  update CRM_PERSON_INFO_PRO c set c.customerid=V_TRUECUSTOMERID where c.customerid=V_DELCUSTOMERID;
  update CRM_PROFIT_STRUCTURE c set c.customerid=V_TRUECUSTOMERID where c.customerid=V_DELCUSTOMERID;
  update CRM_PROFIT_STRUCTURE_PRO c set c.customerid=V_TRUECUSTOMERID where c.customerid=V_DELCUSTOMERID;
  update LEGAL_SENDCONFIRM c set c.customerid=V_TRUECUSTOMERID where c.customerid=V_DELCUSTOMERID;
  update OPERATOR_INDEX_PROJ_INFO c set c.customerid=V_TRUECUSTOMERID where c.customerid=V_DELCUSTOMERID;
  update PROJ_BASEINFO c set c.customerid=V_TRUECUSTOMERID where c.customerid=V_DELCUSTOMERID;
  update PROJ_GJJ_BASEINFO c set c.customerid=V_TRUECUSTOMERID where c.customerid=V_DELCUSTOMERID;
  update REPORT_ANALYSISREPORT_INFO c set c.customerid=V_TRUECUSTOMERID where c.customerid=V_DELCUSTOMERID;
  update REPROT_CRM_REL c set c.customerid=V_TRUECUSTOMERID where c.customerid=V_DELCUSTOMERID;
  update VERSION_PROJ_BASEINFO c set c.customerid=V_TRUECUSTOMERID where c.customerid=V_DELCUSTOMERID;
  
  --compserialid
  update CRM_ACCOUNTBALANCEINFO c  set c.compserialid=V_TRUECUSTOMERID where c.compserialid=V_DELCUSTOMERID;  
  update CRM_ACCOUNTBALANCEINFO_PRO c  set c.compserialid=V_TRUECUSTOMERID where c.compserialid=V_DELCUSTOMERID;  
  update crm_assoentinfo c  set c.compserialid=V_TRUECUSTOMERID where c.compserialid=V_DELCUSTOMERID;  
  update CRM_ASSOENTINFO_PRO c  set c.compserialid=V_TRUECUSTOMERID where c.compserialid=V_DELCUSTOMERID;  
  update CRM_BANKCLOSEINFO c  set c.compserialid=V_TRUECUSTOMERID where c.compserialid=V_DELCUSTOMERID;  
  update CRM_BANKCLOSEINFO_PRO c  set c.compserialid=V_TRUECUSTOMERID where c.compserialid=V_DELCUSTOMERID;  
  update CRM_COUNTERGUARANTEE c  set c.compserialid=V_TRUECUSTOMERID where c.compserialid=V_DELCUSTOMERID;  
  update CRM_COUNTERGUARANTEE_PRO c  set c.compserialid=V_TRUECUSTOMERID where c.compserialid=V_DELCUSTOMERID;  
  update CRM_CUSTOMER c  set c.compserialid=V_TRUECUSTOMERID where c.compserialid=V_DELCUSTOMERID;  
  update CRM_CUSTOMER_PRO c  set c.compserialid=V_TRUECUSTOMERID where c.compserialid=V_DELCUSTOMERID;  
  update CRM_ENTCREDITSTATUS c  set c.compserialid=V_TRUECUSTOMERID where c.compserialid=V_DELCUSTOMERID;  
  update CRM_ENTCREDITSTATUS_PRO c  set c.compserialid=V_TRUECUSTOMERID where c.compserialid=V_DELCUSTOMERID;  
  update CRM_ENTPAYTAXES c  set c.compserialid=V_TRUECUSTOMERID where c.compserialid=V_DELCUSTOMERID;  
  update CRM_ENTPAYTAXES_PRO c  set c.compserialid=V_TRUECUSTOMERID where c.compserialid=V_DELCUSTOMERID;  
  update CRM_FAMILY_MEMBER c  set c.compserialid=V_TRUECUSTOMERID where c.compserialid=V_DELCUSTOMERID;  
  update CRM_FAMILY_MEMBER_PRO c  set c.compserialid=V_TRUECUSTOMERID where c.compserialid=V_DELCUSTOMERID;  
  update CRM_FINANCIAL c  set c.compserialid=V_TRUECUSTOMERID where c.compserialid=V_DELCUSTOMERID;  
  update CRM_FINANCIAL_PRO c  set c.compserialid=V_TRUECUSTOMERID where c.compserialid=V_DELCUSTOMERID;  
  update CRM_FINANOTHER c  set c.compserialid=V_TRUECUSTOMERID where c.compserialid=V_DELCUSTOMERID;  
  update CRM_FINANOTHER_PRO c  set c.compserialid=V_TRUECUSTOMERID where c.compserialid=V_DELCUSTOMERID;  
  update CRM_FINATARGANAL c  set c.compserialid=V_TRUECUSTOMERID where c.compserialid=V_DELCUSTOMERID;  
  update CRM_FINATARGANAL_PRO c  set c.compserialid=V_TRUECUSTOMERID where c.compserialid=V_DELCUSTOMERID;  
  update CRM_LOANRECODE c  set c.compserialid=V_TRUECUSTOMERID where c.compserialid=V_DELCUSTOMERID;  
  update CRM_LOANRECODE_PRO c  set c.compserialid=V_TRUECUSTOMERID where c.compserialid=V_DELCUSTOMERID;  
  update CRM_MANAGERINFO c  set c.compserialid=V_TRUECUSTOMERID where c.compserialid=V_DELCUSTOMERID;  
  update CRM_MANAGERINFO_PRO c  set c.compserialid=V_TRUECUSTOMERID where c.compserialid=V_DELCUSTOMERID;  
  update CRM_MANAGERINFO_RECORD c  set c.compserialid=V_TRUECUSTOMERID where c.compserialid=V_DELCUSTOMERID;  
  update CRM_MANAGERSTAFF c  set c.compserialid=V_TRUECUSTOMERID where c.compserialid=V_DELCUSTOMERID;  
  update CRM_MANAGERSTAFF_PRO c  set c.compserialid=V_TRUECUSTOMERID where c.compserialid=V_DELCUSTOMERID;  
  update CRM_PRODUCTSTRUCTURE c  set c.compserialid=V_TRUECUSTOMERID where c.compserialid=V_DELCUSTOMERID;  
  update CRM_PRODUCTSTRUCTURE_PRO c  set c.compserialid=V_TRUECUSTOMERID where c.compserialid=V_DELCUSTOMERID;  
  update CRM_REPORTJSON c  set c.compserialid=V_TRUECUSTOMERID where c.compserialid=V_DELCUSTOMERID;  
  update CRM_REPORTJSON_PRO c  set c.compserialid=V_TRUECUSTOMERID where c.compserialid=V_DELCUSTOMERID;  
  update CRM_REVECORP c  set c.compserialid=V_TRUECUSTOMERID where c.compserialid=V_DELCUSTOMERID;  
  update CRM_REVECORP_PRO c  set c.compserialid=V_TRUECUSTOMERID where c.compserialid=V_DELCUSTOMERID;  
  update CRM_REVEPERSON c  set c.compserialid=V_TRUECUSTOMERID where c.compserialid=V_DELCUSTOMERID;  
  update CRM_REVEPERSON_PRO c  set c.compserialid=V_TRUECUSTOMERID where c.compserialid=V_DELCUSTOMERID;  
  update CRM_REVE_CUSTOMER_INFO c  set c.compserialid=V_TRUECUSTOMERID where c.compserialid=V_DELCUSTOMERID;  
  update VERSION_CRM_REVECORP_PRO c  set c.compserialid=V_TRUECUSTOMERID where c.compserialid=V_DELCUSTOMERID;  
  update VERSION_CRM_REVEPERSON_PRO c  set c.compserialid=V_TRUECUSTOMERID where c.compserialid=V_DELCUSTOMERID;  
  update VERSION_PRO_COUNTERGUARANTEE c  set c.compserialid=V_TRUECUSTOMERID where c.compserialid=V_DELCUSTOMERID;  
    
    /*删除要删除的数据*/
    delete crm_customer_baseinfo where customerid in 
    (
      V_DELCUSTOMERID
    );

    delete crm_customer_baseinfo_pro where customerid in 
    (
      V_DELCUSTOMERID
    );

    delete crm_corp_baseinfo where customerid in 
    (
      V_DELCUSTOMERID
    );

    delete crm_corp_baseinfo_pro where customerid in 
    (
      V_DELCUSTOMERID
    );

    COMMIT;
END UPDATE_CUSTOMERID_AllTAble;

调用SQL如下：

declare
BEGIN 
  UPDATE_CUSTOMERID_AllTAble (55529,55472 );
  UPDATE_CUSTOMERID_AllTAble (59645,54580 );
  UPDATE_CUSTOMERID_AllTAble (56099,54838 );
  UPDATE_CUSTOMERID_AllTAble (56579,55211 );
  UPDATE_CUSTOMERID_AllTAble (55794,55826 );
  UPDATE_CUSTOMERID_AllTAble (55949,54844 );
  UPDATE_CUSTOMERID_AllTAble (58803,58826 );
  UPDATE_CUSTOMERID_AllTAble (56249,55018 );
  UPDATE_CUSTOMERID_AllTAble (58768,58913 );
  UPDATE_CUSTOMERID_AllTAble (57241,54934 );
  UPDATE_CUSTOMERID_AllTAble (54662,57283 );
  UPDATE_CUSTOMERID_AllTAble (55572,54802 );
  UPDATE_CUSTOMERID_AllTAble (55571,54811 );
  UPDATE_CUSTOMERID_AllTAble (56311,55371 );
  UPDATE_CUSTOMERID_AllTAble (56753,55390 );
END;

所有无数据表：

AC_MENU_20161227
AC_OPERATOR_SIGNNATURE
AC_ROLEFUNCITEM
APPROVAL_ADDENDA_APPLY
ARCHIVE_RECEIVE_APPLY
ARCHIVE_RETURN_APPLY
ASSETS_VISIT_REPORT
AT_LOGIN_POLICY
BASE_DICTIONARY
BASE_DICTIONARY_LOG
CFG_REGISTERBIZ
COMMISSION_REPORT_SCORE
COMMON_PMSCORE
COMM_LOG
COMM_TAGS_IDEA
COMPENSATORY_APPLY
CRM_BANKACCOUNTANALYSIS
CRM_BANKACCOUNTANALYSIS_PRO
CRM_BANKRETURNEDMONEY
CRM_BANKRETURNEDMONEY_PRO
CRM_BLACK_WHITE_CUSTOM
CRM_CORP_BEHISCHANGE_EVENT
CRM_CORP_BEHISCHANGE_EVENT_PRO
CRM_ENERGYCONSUMPTION
CRM_ENERGYCONSUMPTION_PRO
CRM_ENTCREDITEXND
CRM_ENTCREDITEXND_PRO
CRM_FINAN_FIRSTCAL_VALUE
CRM_INDUSTRYMARKET
CRM_INDUSTRYMARKETR_PRO
CRM_MANAGERANAL
CRM_MANAGERANAL_PRO
CRM_PARTNERFRAME
CRM_PARTNERFRAME_PRO
CRM_PERSON_BORROWER_PRO
CRM_PRODUCTMADE
CRM_PRODUCTMADE_PRO
CRM_PRODUCTTECHSTATUS
CRM_PRODUCTTECHSTATUS_PRO
CRM_PURCHASE_SALES
CRM_PURCHASE_SALES_PRO
CRM_REPORT
CRM_STOCKCHANNEL
CRM_STOCKCHANNEL_PRO
CRM_VISIT_ENT
DEPOSIT_MARGIN_APPLY
FEE_PROJ_BUSS
FINAN_LOAN_TRANSFER
GUARANTEE_LIABILITY_RELEASE
HM_ATTACHMENT
HM_CONTENT
HM_INSTANCETASKWORKITEMINFO926
HM_PEOPLEASSIGNMENT_OWNERS
HM_TASKCOMMENT
HM_TODOTASK
LEGAL_EXINFO
LEGAL_PACT_BANKGUAR
LEGAL_PACT_DOC
LEGAL_PACT_PERF
LEGAL_PACT_PETTYLOAN
LETTER_ARREARAGE_RECORD
LETTER_GUARANTEE_APPROVAL
LOSS_COFIRM_APPLY
MONITOR_CHECK_FINAN
MONITOR_CHECK_PERF
MONITOR_CHECK_PETTY
OM_EMPGROUP
OM_EMPORG_TMP
OM_GROUP
PETTYLOAN_LOAN
PETTYLOAN_LOAN_NOTICE
PETTYLOAN_LOAN_RETURN
PETTYLOAN_RETURN_FLOW
PROJECT_OPERATOR
PROJ_ALTER_RECODE
PROJ_ALTER_TEMP
PROJ_BANK_INFO
PROJ_ENDMANAGE_APPLY
PROJ_EXTENSION
PROJ_FINAN_PROPERTY
PROJ_FINAN_RISKANALYSIS
PROJ_LOAN_PASSONAPPLY
PROJ_MONITOR_INFO
PROJ_ONLINE_INFO
PROJ_PERF
PROJ_PETTYLOAN
PROJ_POINTS_RESULT
PROJ_RISKEVAL_REPORT_PRO
PROJ_RISKTRIAL_REPORT_PRO
PROJ_SCORE_RESULT
PROJ_SQUARE
PROJ_USECREDIT_APPLY
PROJ_WORKMANUSCRIPT
RECOVER_DEBT_APPLY
RECOVER_DEBT_DETAIL
RETURN_RATE_APPLY
REVIEWREPORT_TEMPLATE
REVIEW_REPORT
REVIEW_REPORT_PRO
RISKLOAN_APPLY
RISKLOAN_PLAN_APPLY
RISKLOAN_PLAN_EXTEND
RISK_CATEGOROES_APPLY
RISK_CONTROL_SCOREITEM
RISK_POINT_DETAIL
SECHANDHOUSE_GUARANTEE_APPR
SECHOUSE_GUARANTEE_CODE
SECHOUSE_GUARANTEE_PROJ_INFO
SECONDHAND_HOUSE_MID_INFO
SIS_LOG
SYS_LOG
TEMP_VALUE
TT_V_BITTABLE
TT_V_TEMPTABLE
VERSION_PROJ_BANK_INFO
VERSION_PROJ_REPORT_EXTENDS
WF_AGENCYLIMIT
WF_APPLYTABLE
WF_EVENTTYPES
WF_HANDOVERINFO
WF_HANDOVERPROCESSITEM
WF_PROCESSINSTANCEEVENTINFO
WF_PROCESSREADER

所有有customerID的表：

---------------------------------
--customerid
--删除的四张主表
CRM_CORP_BASEINFO
CRM_CORP_BASEINFO_PRO
CRM_CUSTOMER_BASEINFO
CRM_CUSTOMER_BASEINFO_PRO
---可更新的表
CRM_CORPREPR_PRO
CRM_CORP_EXDESCINFO_PRO
CRM_CUSTOMER_EXINFO_PRO
CRM_FINANCIAL_TYPE
CRM_GUARANTEESANDLIABILI
CRM_GUARANTEESANDLIABILI_PRO
CRM_INFO_TAXES
CRM_INFO_TAXES_PRO
CRM_LEGAL_AND_ARBITRATION
CRM_LEGAL_AND_ARBITRATION_PRO
CRM_PERSON_EXINFO_PRO
CRM_PERSON_INFO
CRM_PERSON_INFO_PRO
CRM_PROFIT_STRUCTURE
CRM_PROFIT_STRUCTURE_PRO
LEGAL_SENDCONFIRM
OPERATOR_INDEX_PROJ_INFO
PROJ_BASEINFO
PROJ_GJJ_BASEINFO
REPORT_ANALYSISREPORT_INFO
REPROT_CRM_REL
VERSION_PROJ_BASEINFO


--不可更新的表
CRM_CORPREPR
CRM_CORP_EXDESCINFO


---------------------------------
--compserialid
CRM_ACCOUNTBALANCEINFO
CRM_ACCOUNTBALANCEINFO_PRO
crm_assoentinfo
CRM_ASSOENTINFO_PRO
CRM_BANKCLOSEINFO
CRM_BANKCLOSEINFO_PRO
CRM_COUNTERGUARANTEE
CRM_COUNTERGUARANTEE_PRO
CRM_CUSTOMER
CRM_CUSTOMER_PRO
CRM_ENTCREDITSTATUS
CRM_ENTCREDITSTATUS_PRO
CRM_ENTPAYTAXES
CRM_ENTPAYTAXES_PRO
CRM_FAMILY_MEMBER
CRM_FAMILY_MEMBER_PRO
CRM_FINANCIAL
CRM_FINANCIAL_PRO
CRM_FINANOTHER
CRM_FINANOTHER_PRO
CRM_FINATARGANAL
CRM_FINATARGANAL_PRO
CRM_LOANRECODE
CRM_LOANRECODE_PRO
CRM_MANAGERINFO
CRM_MANAGERINFO_PRO
CRM_MANAGERINFO_RECORD
CRM_MANAGERSTAFF
CRM_MANAGERSTAFF_PRO
CRM_PRODUCTSTRUCTURE
CRM_PRODUCTSTRUCTURE_PRO
CRM_REPORTJSON
CRM_REPORTJSON_PRO
CRM_REVECORP
CRM_REVECORP_PRO
CRM_REVEPERSON
CRM_REVEPERSON_PRO
CRM_REVE_CUSTOMER_INFO
VERSION_CRM_REVECORP_PRO
VERSION_CRM_REVEPERSON_PRO
VERSION_PRO_COUNTERGUARANTEE

 end；