CREATE OR REPLACE PROCEDURE PROC_INTERFACE AS
C_COUNT INTEGER;
--建立游标
CURSOR CURS_BANKCARD_IU IS
SELECT *
FROM TB_INF_BANKCARD
WHERE C_OPERATETIME > (SELECT TM.C_TIME FROM HR_TIME TM);
CURSOR CURS_BANKCARD_D IS
SELECT *
FROM TB_INF_BANKCARDBAK
WHERE C_OID NOT IN (SELECT C_OID FROM TB_INF_BANKCARD);
--4。执行同步
BEGIN
FOR CUR IN CURS_BANKCARD_IU LOOP
SELECT COUNT(*)
INTO C_COUNT
FROM TB_INF_BANKCARDBAK
WHERE C_OID = CUR.C_OID;
IF C_COUNT = 0 THEN
--新增信息
INSERT INTO TB_INF_BANKCARDBAK
(C_OID,
C_EMPOID,
C_BANKTYPE,
C_BANKNUM,
C_REMARK,
C_OPERATETIME,
C_STATUS,
C_OPERATESORT)
VALUES
(CUR.C_OID,
CUR.C_EMPOID,
CUR.C_BANKTYPE,
CUR.C_BANKNUM,
CUR.C_TXT,
CUR.C_OPERATETIME,
'1',
1);
COMMIT;
ELSE
--更新信息
DELETE FROM TB_INF_BANKCARDBAK WHERE C_OID = CUR.C_OID;
COMMIT;
INSERT INTO TB_INF_BANKCARDBAK
(C_OID,
C_EMPOID,
C_BANKTYPE,
C_BANKNUM,
C_REMARK,
C_OPERATETIME,
C_STATUS,
C_OPERATESORT)
VALUES
(CUR.C_OID,
CUR.C_EMPOID,
CUR.C_BANKTYPE,
CUR.C_BANKNUM,
CUR.C_TXT,
CUR.C_OPERATETIME,
'1',
2);
COMMIT;
END IF;
END LOOP;
end;
--删除
begin
FOR CUR IN CURS_BANKCARD_D LOOP
UPDATE TB_INF_BANKCARDBAK
SET C_OPERATESORT = 3, C_STATUS = '0'
WHERE C_OID = CUR.C_OID;
COMMIT;
END LOOP;
end;
--更新HR_TIME表
UPDATE HR_TIME SET C_TIME = SYSDATE;
COMMIT;
--UPDATE HR_TIME SET C_TIME = TO_DATE('1900-01-01 00:00:00','YYYY-MM-DD HH24:MI:SS')
END PROC_INTERFACE;
在begin那里报错 请大家分析一下 谢谢
------解决方案--------------------
报错起码得给个错误信息啊。。。