我有两个表，要为它们创建一个Stored procedure。
表1:-R4G_APPDEV.plannedroute_evw表2:-ASSIGNED_ROUTE_MASTER
现在我想要的是将表2中的某个列与表1进行比较，如果列值有任何差异，则更新表2中的列。
以下是两个表格的表格说明。
表1:R4G_APPDEV.plannedroute_evw
Name Null? Type
--------------------------- ----- ---------------
OBJECTID NUMBER
MEASUREDLENGTH NUMBER(38,8)
DEPTHPLACED NUMBER(38,8)
HEIGHT NUMBER(38,8)
WIDTH NUMBER(38,8)
NUMBEROFDUCTS NUMBER(5)
NUMBEROFSUBDUCTS NUMBER(5)
DUCTDIAMETER NUMBER(38,8)
SUBDUCTDIAMETER NUMBER(38,8)
REMARKS NVARCHAR2(250)
NETWORKTYPE NUMBER(10)
NETWORKCATEGORY NVARCHAR2(50)
ROUTENAME NVARCHAR2(200)
FACILITYID NVARCHAR2(41)
BWLEASE NVARCHAR2(50)
ENABLED NUMBER(5)
TRENCHMATERIAL NVARCHAR2(15)
UNITS NVARCHAR2(5)
AGGREGATEROUTENAME NVARCHAR2(200)
CSSROUTENAME NVARCHAR2(200)
COREROUTENAME NVARCHAR2(200)
PHASE NVARCHAR2(20)
PATHID NVARCHAR2(30)
AGGREGATEROUTE NVARCHAR2(50)
OWNER NVARCHAR2(50)
ACTUALLENGTH NUMBER(38,8)
CITYCODE NVARCHAR2(4)
CITYNAME NVARCHAR2(50)
DUCTCOLOUR NVARCHAR2(50)
REGULATORYDUCT NUMBER(5)
ADDITIONALPROTECTIONREQD NVARCHAR2(10)
PROTECTIONTYPE NVARCHAR2(200)
LINK_ID NVARCHAR2(25)
SPAN_ID NVARCHAR2(25)
OPERATOR_ID NVARCHAR2(50)
CONSTRUCTION_METHODOLOGY NVARCHAR2(50)
ROUTEPATH_NEW NVARCHAR2(200)
ROUTETYPE NVARCHAR2(50)
SWITH_ON_CORE NVARCHAR2(50)
FINAL_CORE NVARCHAR2(50)
SWITCH_ON_COLLECTOR NVARCHAR2(50)
FINAL_COLLECTOR NVARCHAR2(50)
COLLECTOR_BW_CLASSIFICATION NVARCHAR2(50)
FROM_CITY NVARCHAR2(50)
TO_CITY NVARCHAR2(50)
PLANNEDDATE TIMESTAMP(6)
USER_NAME NVARCHAR2(50)
CREATED_USER NVARCHAR2(50)
LAST_EDITED_USER NVARCHAR2(50)
LAST_EDITED_DATE TIMESTAMP(6)
CREATED_DATE TIMESTAMP(6)
INTRACITY_LINK_ID NVARCHAR2(20)
SRNO NUMBER(10)
LEASE_ROUTE_NAME NVARCHAR2(200)
R4G_STATE NVARCHAR2(50)
LEASE_ROUTE_LINK_ID NVARCHAR2(25)
LEASE_ROUTE_SPAN_ID NVARCHAR2(25)
SCOPE_CATEGORY NVARCHAR2(25)
INTRACITY_CONSTRUCTION_TYPE NVARCHAR2(25)
FACILITY_SAP_ID NVARCHAR2(25)
NLD_STAGES NVARCHAR2(10)
AG_PARENTID NVARCHAR2(30)
CORE_PARENTID_1 NVARCHAR2(30)
CORE_PARENTID_2 NVARCHAR2(30)
SDCC_OBLIGATION NVARCHAR2(25)
LASTMILE_ID NVARCHAR2(20)
LINK_ID_100G NVARCHAR2(25)
SPAN_ID_100G NVARCHAR2(25)
LINK_ID_NX10G NVARCHAR2(25)
SPAN_ID_NX10G NVARCHAR2(25)
LINK_ID_1X10G NVARCHAR2(25)
SPAN_ID_1X10G NVARCHAR2(25)
LINK_ID_1X1G NVARCHAR2(25)
SPAN_ID_1X1G NVARCHAR2(25)
LINK_STATUS NVARCHAR2(30)
LINK_RFE_DATE TIMESTAMP(6)
NLD_POP_PATH1 NVARCHAR2(25)
NLD_POP_PATH2 NVARCHAR2(25)
EPC_PATH1 NVARCHAR2(25)
EPC_PATH2 NVARCHAR2(25)
LINK_RFE_PLAN TIMESTAMP(6)
LINK_RFE_ACT TIMESTAMP(6)
LINK_RFE_FCST TIMESTAMP(6)
LAUNCH_MRO NVARCHAR2(50)
LAUNCH_FRO NVARCHAR2(50)
GLOBALID CHAR(38)
JIOCENTERNAME NVARCHAR2(255)
JIOCENTERCODE NVARCHAR2(10)
JIOCENTER_RJID NVARCHAR2(10)
LOGICAL_LINK_ID NVARCHAR2(25)
LM_PARENT_ID NVARCHAR2(25)
INTRACITY_PHASE NVARCHAR2(25)
OLD_SPAN_ID NVARCHAR2(21)
LM_CONNECTIVITY_TYPE NVARCHAR2(20)
PARENTEASTAG1SAPID NVARCHAR2(18)
PARENTWESTAG1SAPID NVARCHAR2(18)
POST_DATE TIMESTAMP(6)
SURVEYSPANID_1 NVARCHAR2(21)
SURVEYSPANID_2 NVARCHAR2(21)
BLOWINGSPANID_2ND_DUCT NVARCHAR2(21)
BLOWINGSPANID_3RD_DUCT NVARCHAR2(21)
BLOWING_DUCT_COLOUR NVARCHAR2(15)
OLD_INTRACITY_LINK_ID NVARCHAR2(15)
HOTO_DONE_DATE TIMESTAMP(6)
HOTO_DONE NVARCHAR2(50)
SHAPE SDE.ST_GEOMETRY
SDE_STATE_ID NUMBER
DESC表2:R4G_APPDEV.plannedroute_evw
Name Null? Type
------------------------ -------- --------------
IID NOT NULL VARCHAR2(50)
PROJECT_PHASE VARCHAR2(20)
NETWORK_CATEGORY VARCHAR2(50)
NETWORK_TYPE NUMBER
ROUTE_NAME VARCHAR2(200)
LINK_ID VARCHAR2(25)
SPAN_ID VARCHAR2(25)
SPAN_NAME VARCHAR2(200)
PARENT_LINK_ID VARCHAR2(25)
SCOPE_CATEGORY VARCHAR2(25)
FROM_FACILITY_LAT NUMBER
FROM_FACILITY_LONG NUMBER
TO_FACILITY_LAT NUMBER
TO_FACILITY_LONG NUMBER
FROM_FACILITY VARCHAR2(25)
TO_FACILITY VARCHAR2(25)
LENGTH NUMBER
JIO_STATE_CODE VARCHAR2(4)
JIO_STATE_NAME VARCHAR2(200)
STATE_CODE VARCHAR2(4)
STATE_NAME VARCHAR2(200)
CITY_CODE VARCHAR2(4)
CITY_NAME VARCHAR2(200)
FROM_CITY_NAME VARCHAR2(200)
TO_CITY_NAME VARCHAR2(200)
FROM_CITY_CODE VARCHAR2(4)
TO_CITY_CODE VARCHAR2(4)
NO_OF_CROSSING NUMBER
TYPE_OF_CROSSING VARCHAR2(100)
EXECUTING_MP VARCHAR2(50)
STATUS VARCHAR2(40)
DATE_ROUTE_RELEASED DATE
LAST_EDITED_USER VARCHAR2(100)
LAST_EDITED_DATE NOT NULL DATE
INTRACITY_LINK_ID VARCHAR2(25)
WORK_ORDER_NAME VARCHAR2(50)
WORK_ORDER_STATUS_CODE VARCHAR2(20)
WORKFLOW VARCHAR2(20)
LAST_EDITED_USER_REMARKS VARCHAR2(1000)
OBJECTID NOT NULL NUMBER(38)
REVISION NUMBER
TND_SCOPE_LENGTH NUMBER(20,2)
BLOWING_SCOPE_LENGTH NUMBER(20,2)
SAP_ID VARCHAR2(20)
LASTMILE_ID VARCHAR2(20)
请让我知道如何更新它。
**注**表1的唯一列为OBJECTID，表2中为IID
更新
我开始写下面这样的程序来比较一列。现在我想为每个记录循环它，如果发现任何差异，然后更新它。如何做到
CREATE OR REPLACE PROCEDURE PROC_CHK_PLNDRT_ASSMST AS
VACTUALLEN nvarchar2(50);
VLENGTH nvarchar2(50);
BEGIN
SELECT ACTUALLENGTH into VACTUALLEN from R4G_APPDEV.plannedroute_evw
UNION ALL
select LENGTH into VLENGTH from ASSIGNED_ROUTE_MASTER;
NULL;
END PROC_CHK_PLNDRT_ASSMST;
按照我的理解，您实际上不需要对列与列进行比较，也不需要在不同的情况下进行更新-无条件更新(使"过程"更简单(。这意味着您根本不必使用PL/SQL，因为merge在SQL级别处理它。
表的描述不同，我不知道plannedroute_evw中的哪一列映射到assigned_route_master中的哪列。但是，如果您这样做，您将很容易地完成以下merge语句。
方法如下：
merge into assigned_route_master b
using plannedroute_evw a
on (a.objectid = b.iid)
when matched then update set
b.length = a.actual_length,
b.city_code = a.city_code,
<here you should map all columns between these two tables>
;