create or replace procedure AAA IS
COUNT_ONE NUMBER;
CURSOR MINUS_AAA IS
SELECT A1,
B1
FROM TABLE_B
MINUS
SELECT A1,
B1
FROM TABLE_A;
begin
FOR CUR_AA IN MINUS_AAA LOOP
BEGIN
SELECT COUNT(1)
INTO COUNT_ONE
FROM TABLE_A T
WHERE T.A1 = CUR_AA.A1;
--无则新增
IF COUNT_ONE=0 THEN
INSERT INTO TABLE_A
(A1,
B1
)
VALUES
(CUR_AA.A1,
CUR_AA.B1
);
END IF;
--有则修改
IF COUNT_ONE = 1 THEN
UPDATE TABLE_A
SET A1 = CUR_AA.A1,
B1 = CUR_AA.B1
WHERE A1 = CUR_AA.A1;
END IF;
END;
END LOOP;
COMMIT;
end AAA;
COUNT_ONE NUMBER;
CURSOR MINUS_AAA IS
SELECT A1,
B1
FROM TABLE_B
MINUS
SELECT A1,
B1
FROM TABLE_A;
begin
FOR CUR_AA IN MINUS_AAA LOOP
BEGIN
SELECT COUNT(1)
INTO COUNT_ONE
FROM TABLE_A T
WHERE T.A1 = CUR_AA.A1;
--无则新增
IF COUNT_ONE=0 THEN
INSERT INTO TABLE_A
(A1,
B1
)
VALUES
(CUR_AA.A1,
CUR_AA.B1
);
END IF;
--有则修改
IF COUNT_ONE = 1 THEN
UPDATE TABLE_A
SET A1 = CUR_AA.A1,
B1 = CUR_AA.B1
WHERE A1 = CUR_AA.A1;
END IF;
END;
END LOOP;
COMMIT;
end AAA;