如下：
--场景三--多条记录，确认使用其中一条，并确认/更新正确，更新所有关联表使用选择的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)>4order by customername ;
存储过程如下：
CREATE OR REPLACE PROCEDURE UPDATE_CUSTOMERID_AllTAble( V_TRUECUSTOMERID IN NUMBER, V_DELCUSTOMERID IN NUMBER) ISBEGIN /*****先更新数据为需要的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如下：
declareBEGIN 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_20161227AC_OPERATOR_SIGNNATUREAC_ROLEFUNCITEMAPPROVAL_ADDENDA_APPLYARCHIVE_RECEIVE_APPLYARCHIVE_RETURN_APPLYASSETS_VISIT_REPORTAT_LOGIN_POLICYBASE_DICTIONARYBASE_DICTIONARY_LOGCFG_REGISTERBIZCOMMISSION_REPORT_SCORECOMMON_PMSCORECOMM_LOGCOMM_TAGS_IDEACOMPENSATORY_APPLYCRM_BANKACCOUNTANALYSISCRM_BANKACCOUNTANALYSIS_PROCRM_BANKRETURNEDMONEYCRM_BANKRETURNEDMONEY_PROCRM_BLACK_WHITE_CUSTOMCRM_CORP_BEHISCHANGE_EVENTCRM_CORP_BEHISCHANGE_EVENT_PROCRM_ENERGYCONSUMPTIONCRM_ENERGYCONSUMPTION_PROCRM_ENTCREDITEXNDCRM_ENTCREDITEXND_PROCRM_FINAN_FIRSTCAL_VALUECRM_INDUSTRYMARKETCRM_INDUSTRYMARKETR_PROCRM_MANAGERANALCRM_MANAGERANAL_PROCRM_PARTNERFRAMECRM_PARTNERFRAME_PROCRM_PERSON_BORROWER_PROCRM_PRODUCTMADECRM_PRODUCTMADE_PROCRM_PRODUCTTECHSTATUSCRM_PRODUCTTECHSTATUS_PROCRM_PURCHASE_SALESCRM_PURCHASE_SALES_PROCRM_REPORTCRM_STOCKCHANNELCRM_STOCKCHANNEL_PROCRM_VISIT_ENTDEPOSIT_MARGIN_APPLYFEE_PROJ_BUSSFINAN_LOAN_TRANSFERGUARANTEE_LIABILITY_RELEASEHM_ATTACHMENTHM_CONTENTHM_INSTANCETASKWORKITEMINFO926HM_PEOPLEASSIGNMENT_OWNERSHM_TASKCOMMENTHM_TODOTASKLEGAL_EXINFOLEGAL_PACT_BANKGUARLEGAL_PACT_DOCLEGAL_PACT_PERFLEGAL_PACT_PETTYLOANLETTER_ARREARAGE_RECORDLETTER_GUARANTEE_APPROVALLOSS_COFIRM_APPLYMONITOR_CHECK_FINANMONITOR_CHECK_PERFMONITOR_CHECK_PETTYOM_EMPGROUPOM_EMPORG_TMPOM_GROUPPETTYLOAN_LOANPETTYLOAN_LOAN_NOTICEPETTYLOAN_LOAN_RETURNPETTYLOAN_RETURN_FLOWPROJECT_OPERATORPROJ_ALTER_RECODEPROJ_ALTER_TEMPPROJ_BANK_INFOPROJ_ENDMANAGE_APPLYPROJ_EXTENSIONPROJ_FINAN_PROPERTYPROJ_FINAN_RISKANALYSISPROJ_LOAN_PASSONAPPLYPROJ_MONITOR_INFOPROJ_ONLINE_INFOPROJ_PERFPROJ_PETTYLOANPROJ_POINTS_RESULTPROJ_RISKEVAL_REPORT_PROPROJ_RISKTRIAL_REPORT_PROPROJ_SCORE_RESULTPROJ_SQUAREPROJ_USECREDIT_APPLYPROJ_WORKMANUSCRIPTRECOVER_DEBT_APPLYRECOVER_DEBT_DETAILRETURN_RATE_APPLYREVIEWREPORT_TEMPLATEREVIEW_REPORTREVIEW_REPORT_PRORISKLOAN_APPLYRISKLOAN_PLAN_APPLYRISKLOAN_PLAN_EXTENDRISK_CATEGOROES_APPLYRISK_CONTROL_SCOREITEMRISK_POINT_DETAILSECHANDHOUSE_GUARANTEE_APPRSECHOUSE_GUARANTEE_CODESECHOUSE_GUARANTEE_PROJ_INFOSECONDHAND_HOUSE_MID_INFOSIS_LOGSYS_LOGTEMP_VALUETT_V_BITTABLETT_V_TEMPTABLEVERSION_PROJ_BANK_INFOVERSION_PROJ_REPORT_EXTENDSWF_AGENCYLIMITWF_APPLYTABLEWF_EVENTTYPESWF_HANDOVERINFOWF_HANDOVERPROCESSITEMWF_PROCESSINSTANCEEVENTINFOWF_PROCESSREADER
所有有customerID的表：
-----------------------------------customerid--删除的四张主表CRM_CORP_BASEINFOCRM_CORP_BASEINFO_PROCRM_CUSTOMER_BASEINFOCRM_CUSTOMER_BASEINFO_PRO---可更新的表CRM_CORPREPR_PROCRM_CORP_EXDESCINFO_PROCRM_CUSTOMER_EXINFO_PROCRM_FINANCIAL_TYPECRM_GUARANTEESANDLIABILICRM_GUARANTEESANDLIABILI_PROCRM_INFO_TAXESCRM_INFO_TAXES_PROCRM_LEGAL_AND_ARBITRATIONCRM_LEGAL_AND_ARBITRATION_PROCRM_PERSON_EXINFO_PROCRM_PERSON_INFOCRM_PERSON_INFO_PROCRM_PROFIT_STRUCTURECRM_PROFIT_STRUCTURE_PROLEGAL_SENDCONFIRMOPERATOR_INDEX_PROJ_INFOPROJ_BASEINFOPROJ_GJJ_BASEINFOREPORT_ANALYSISREPORT_INFOREPROT_CRM_RELVERSION_PROJ_BASEINFO--不可更新的表CRM_CORPREPRCRM_CORP_EXDESCINFO-----------------------------------compserialidCRM_ACCOUNTBALANCEINFOCRM_ACCOUNTBALANCEINFO_PROcrm_assoentinfoCRM_ASSOENTINFO_PROCRM_BANKCLOSEINFOCRM_BANKCLOSEINFO_PROCRM_COUNTERGUARANTEECRM_COUNTERGUARANTEE_PROCRM_CUSTOMERCRM_CUSTOMER_PROCRM_ENTCREDITSTATUSCRM_ENTCREDITSTATUS_PROCRM_ENTPAYTAXESCRM_ENTPAYTAXES_PROCRM_FAMILY_MEMBERCRM_FAMILY_MEMBER_PROCRM_FINANCIALCRM_FINANCIAL_PROCRM_FINANOTHERCRM_FINANOTHER_PROCRM_FINATARGANALCRM_FINATARGANAL_PROCRM_LOANRECODECRM_LOANRECODE_PROCRM_MANAGERINFOCRM_MANAGERINFO_PROCRM_MANAGERINFO_RECORDCRM_MANAGERSTAFFCRM_MANAGERSTAFF_PROCRM_PRODUCTSTRUCTURECRM_PRODUCTSTRUCTURE_PROCRM_REPORTJSONCRM_REPORTJSON_PROCRM_REVECORPCRM_REVECORP_PROCRM_REVEPERSONCRM_REVEPERSON_PROCRM_REVE_CUSTOMER_INFOVERSION_CRM_REVECORP_PROVERSION_CRM_REVEPERSON_PROVERSION_PRO_COUNTERGUARANTEE
end；