同步数据package

create or replace package body PKG_Import_ITEM_INSTANCE is
-- Created : 9/16/2011 2:09:59 PM
-- Purpose :

--DBLINK_NAME
V_DBLINK_NAME VARCHAR2(40) := '@JF_8004';
V_SQL VARCHAR2(5000) := '';
V_ID VARCHAR2(40);
V_MSG VARCHAR2(5000) := '';

--记录 CUX_SCADA_JOBLOG 表
procedure RecordJobResult(
p_JobID IN VARCHAR2,
p_StartDateTime IN DATE,
p_EndDateTime IN DATE,
p_ProcessCount IN NUMBER,
p_Result IN VARCHAR2,
p_ErrorMsg IN NVARCHAR2,
V_Username in varchar2) is
v_UsedSeconds NUMBER;
begin
v_UsedSeconds := (p_EndDateTime - p_StartDateTime) * 24 * 60 * 60;

INSERT INTO CUX_SCADA_JOBLOG(SERIAL,jobid,startdatetime,enddatetime,usedtime,processcount,result,errormsg,Username)
VALUES(SYS_GUID(),p_JobID,p_StartDateTime,p_EndDateTime,v_UsedSeconds,p_ProcessCount,p_Result,p_ErrorMsg,V_Username);
commit;
return;
exception
when others then
V_MSG := substrb(sqlerrm,1,4000);
return;

end RecordJobResult;

PROCEDURE ImportINSTANCE is

V_INSTANCE_NUMBER VARCHAR2(30); -- not null
V_ITEM_NUMBER VARCHAR2(40);
V_ITEM_DESCRIPTION VARCHAR2(240);
V_ITEM_CATEGORY VARCHAR2(240);
V_QUANTITY NUMBER; --not null
V_UNIT_OF_MEASURE VARCHAR2(3); --not null
V_SERIAL_NUMBER VARCHAR2(30);
V_LOT_NUMBER VARCHAR2(80);
V_PROJECT_NUMBER VARCHAR2(240);
V_PROJECT_NAME VARCHAR2(240);
V_CUSTOMER_NAME VARCHAR2(360); -- not null
V_CUSTOMER_ADDRESS VARCHAR2(240);
V_BLADE_STALL VARCHAR2(240);
V_INSTALL_ANGLE_INIT_VALUE VARCHAR2(240);
V_INSTALL_ANGLE_ADJUST_VALUE VARCHAR2(240);
V_SWIRL_GENERATOR VARCHAR2(240);
V_P_INSTANCE_NUMBER VARCHAR2(30);
V_P_ITEM_NUMBER VARCHAR2(40);
V_P_ITEM_DESCRIPTION VARCHAR2(240);
V_P_ITEM_CATEGORY VARCHAR2(240);
V_INSTALL_DATE VARCHAR2(20);
V_LAST_UPDATE_DATE VARCHAR2(20); -- not null
V_P_SERIAL_NUMBER VARCHAR2(30);
INSTANCE_Cursor Sys_Refcursor;
Update_Time VARCHAR2(30);
B_INSTANCE_NUMBER VARCHAR2(30);

v_JobID VARCHAR2(40) := 'JOB_ImportINSTANCE';
-- v_StartDateTime DATE;
v_StartDateTime DATE :=sysdate;
I_ProcessCount NUMBER := 0;
v_ErrorID VARCHAR2(4000);
V_Username varchar2(40) := 'admin';

begin

select to_char(sysdate,'yyyy-MM-dd HH24:MI:SS')
into Update_Time from dual;

SELECT NVL(MAX(LAST_UPDATE_DATE), '1990-01-01 12:00:00')
INTO V_LAST_UPDATE_DATE
FROM cux_scada_item_instance_v;

--测试之用
--V_LAST_UPDATE_DATE :='2011-09-20 10:20:54';

V_SQL := '';
V_SQL := V_SQL || ' SELECT
a.INSTANCE_NUMBER,a.ITEM_NUMBER,a.ITEM_DESCRIPTION,a.ITEM_CATEGORY,a.QUANTITY,a.UNIT_OF_MEASURE,a.SERIAL_NUMBER,';
V_SQL := V_SQL || '
a.LOT_NUMBER,a.PROJECT_NUMBER,a.PROJECT_NAME,a.CUSTOMER_NAME,a.CUSTOMER_ADDRESS,a.BLADE_STALL,a.INSTALL_ANGLE_INIT_VALUE,';
V_SQL := V_SQL || '
a.INSTALL_ANGLE_ADJUST_VALUE,a.SWIRL_GENERATOR,a.P_INSTANCE_NUMBER,a.P_ITEM_NUMBER,a.P_ITEM_DESCRIPTION,';
V_SQL := V_SQL || ' a.P_ITEM_CATEGORY,a.INSTALL_DATE,a.LAST_UPDATE_DATE,a.P_SERIAL_NUMBER,';
V_SQL := V_SQL || ' NVL(b.INSTANCE_NUMBER,'' '') AS BINSTANCE_NUMBER';
V_SQL := V_SQL || ' FROM (';
V_SQL := V_SQL || ' SELECT *';
V_SQL := V_SQL || ' from cux_scada_item_instance_v' || V_DBLINK_NAME;
V_SQL := V_SQL || ' WHERE to_char(LAST_UPDATE_DATE,''yyyy-MM-dd HH24:MI:SS'')>'''||V_LAST_UPDATE_DATE ||''') a';
V_SQL := V_SQL || ' LEFT JOIN cux_scada_item_instance_v b';
V_SQL := V_SQL || ' ON a.INSTANCE_NUMBER=b.INSTANCE_NUMBER';

open INSTANCE_Cursor for V_SQL;
loop
fetch INSTANCE_Cursor
into V_INSTANCE_NUMBER, V_ITEM_NUMBER, V_ITEM_DESCRIPTION, V_ITEM_CATEGORY, V_QUANTITY,
V_UNIT_OF_MEASURE, V_SERIAL_NUMBER, V_LOT_NUMBER,V_PROJECT_NUMBER, V_PROJECT_NAME,
V_CUSTOMER_NAME, V_CUSTOMER_ADDRESS, V_BLADE_STALL, V_INSTALL_ANGLE_INIT_VALUE,
V_INSTALL_ANGLE_ADJUST_VALUE, V_SWIRL_GENERATOR, V_P_INSTANCE_NUMBER, V_P_ITEM_NUMBER,
V_P_ITEM_DESCRIPTION, V_P_ITEM_CATEGORY, V_INSTALL_DATE, V_LAST_UPDATE_DATE, V_P_SERIAL_NUMBER,
B_INSTANCE_NUMBER;
exit when INSTANCE_Cursor%notfound;

if(B_INSTANCE_NUMBER != ' ') then
UPDATE cux_scada_item_instance_v
SET ITEM_NUMBER = V_ITEM_NUMBER,
ITEM_DESCRIPTION = V_ITEM_DESCRIPTION,
ITEM_CATEGORY = V_ITEM_CATEGORY,
QUANTITY = V_QUANTITY,
UNIT_OF_MEASURE = V_UNIT_OF_MEASURE,
SERIAL_NUMBER = V_UNIT_OF_MEASURE,
LOT_NUMBER = V_UNIT_OF_MEASURE,
PROJECT_NUMBER = V_UNIT_OF_MEASURE,
PROJECT_NAME = V_PROJECT_NAME,
CUSTOMER_NAME = V_CUSTOMER_NAME,
CUSTOMER_ADDRESS = V_CUSTOMER_ADDRESS,
BLADE_STALL = V_BLADE_STALL,
INSTALL_ANGLE_INIT_VALUE = V_INSTALL_ANGLE_INIT_VALUE,
INSTALL_ANGLE_ADJUST_VALUE = V_INSTALL_ANGLE_ADJUST_VALUE,
SWIRL_GENERATOR = V_SWIRL_GENERATOR,
P_INSTANCE_NUMBER = V_P_INSTANCE_NUMBER,
P_ITEM_NUMBER = V_P_ITEM_NUMBER,
P_ITEM_DESCRIPTION = V_P_ITEM_DESCRIPTION,
P_ITEM_CATEGORY = V_P_ITEM_CATEGORY,
INSTALL_DATE = V_INSTALL_DATE,
LAST_UPDATE_DATE = Update_Time,
P_SERIAL_NUMBER = V_P_SERIAL_NUMBER
WHERE INSTANCE_NUMBER = B_INSTANCE_NUMBER; else
V_ID := SYS_GUID();
INSERT INTO cux_scada_item_instance_v
(ID,
INSTANCE_NUMBER,
ITEM_NUMBER,
ITEM_DESCRIPTION,
ITEM_CATEGORY,
QUANTITY,
UNIT_OF_MEASURE,
SERIAL_NUMBER,
LOT_NUMBER,
PROJECT_NUMBER,
PROJECT_NAME,
CUSTOMER_NAME,
CUSTOMER_ADDRESS,
BLADE_STALL,
INSTALL_ANGLE_INIT_VALUE,
INSTALL_ANGLE_ADJUST_VALUE,
SWIRL_GENERATOR,
P_INSTANCE_NUMBER,
P_ITEM_NUMBER,
P_ITEM_DESCRIPTION,
P_ITEM_CATEGORY,
INSTALL_DATE,
LAST_UPDATE_DATE,
P_SERIAL_NUMBER)
VALUES
(V_ID,
V_INSTANCE_NUMBER,
V_ITEM_NUMBER,
V_ITEM_DESCRIPTION,
V_ITEM_CATEGORY,
V_QUANTITY,
V_UNIT_OF_MEASURE,
V_SERIAL_NUMBER,
V_LOT_NUMBER,
V_PROJECT_NUMBER,
V_PROJECT_NAME,
V_CUSTOMER_NAME,
V_CUSTOMER_ADDRESS,
V_BLADE_STALL,
V_INSTALL_ANGLE_INIT_VALUE,
V_INSTALL_ANGLE_ADJUST_VALUE,
V_SWIRL_GENERATOR,
V_P_INSTANCE_NUMBER,
V_P_ITEM_NUMBER,
V_P_ITEM_DESCRIPTION,
V_P_ITEM_CATEGORY,
V_INSTALL_DATE,
Update_Time,
V_P_SERIAL_NUMBER); end if; I_ProcessCount := I_ProcessCount + 1;
end loop; close INSTANCE_Cursor;
commit;

RecordJobResult(v_JobID,v_StartDateTime,sysdate,I_ProcessCount,'OK','',V_Username);
exception
when others then
v_ErrorID := substrb(sqlerrm,1,4000);
if INSTANCE_Cursor%isopen then
close INSTANCE_Cursor;
end if; rollback;
RecordJobResult(v_JobID,v_StartDateTime,sysdate,I_ProcessCount,'FAIL',v_ErrorID,V_Username);
commit;

end ImportINSTANCE;
end PKG_Import_ITEM_INSTANCE;