CREATE OR REPLACE PROCEDURE PROCSAMPLE IS BEGIN
DELETE TABLESAMPLE
WHERE KEY1 NOT IN
(SELECT KEY1 FROM T30070_REGION_SSC_TMP);
UPDATE TABLESAMPLE SET (
NAME1,
UPDATEDATE)
=
(SELECT
NAME1,
SYSDATE
FROM T30070_REGION_SSC_TMP
WHERE TABLESAMPLE.KEY1=T30070_REGION_SSC_TMP.KEY1
AND TABLESAMPLE.NAME1<>T30070_REGION_SSC_TMP.NAME1)
WHERE KEY1 =
(SELECT KEY1
FROM T30070_REGION_SSC_TMP
WHERE TABLESAMPLE.KEY1=T30070_REGION_SSC_TMP.KEY1
AND TABLESAMPLE.NAME1<>T30070_REGION_SSC_TMP.NAME1);
INSERT INTO TABLESAMPLE (
KEY1,
UPDATEDATE)
SELECT
KEY1,
SYSDATE
FROM T30070_REGION_SSC_TMP
WHERE KEY1 NOT IN (SELECT KEY1 FROM TABLESAMPLE);
COMMIT;
EXCEPTION WHEN OTHERS THEN BEGIN
ROLLBACK;
RAISE_APPLICATION_ERROR(SQLCODE,SQLERRM,TRUE);
END;
END PROCSAMPLE;
/