CREATE OR REPLACE PROCEDURE "BAS_ID_GENERATOR_RULES" AS
BEGIN
-- routine body goes here, e.g.
-- DBMS_OUTPUT.PUT_LINE('Navicat for Oracle');
CASE
WHEN BAS_ID_GENERATOR_RULE.OBJECT_RRN@MESTEST !=BAS_ID_GENERATOR_RULE.OBJECT_RRN
THEN
INSERT INTO QMSTEST.BAS_ID_GENERATOR_RULE
SELECT OBJECT_RRN, ORG_RRN, IS_ACTIVE, CREATED,CREATED_BY,UPDATED,UPDATED_BY,LOCK_VERSION,NAME,DESCRIPTION,VERSION,STATUS,ACTIVE_TIME,ACTIVE_USER,RULE_TYPE
FROM BAS_ID_GENERATOR_RULE@MESTEST ;
ELSE
UPDATE QMSTEST.BAS_ID_GENERATOR_RULE
SET OBJECT_RRN = (SELECT OBJECT_RRN FROM BAS_ID_GENERATOR_RULE@MESTEST ),
ORG_RRN = (SELECT ORG_RRN FROM BAS_ID_GENERATOR_RULE@MESTEST ),
IS_ACTIVE = (SELECT IS_ACTIVE FROM BAS_ID_GENERATOR_RULE@MESTEST ),
CREATED_TIME = (SELECT CREATED FROM BAS_ID_GENERATOR_RULE@MESTEST ),
CREATED_BY = (SELECT CREATED_BY FROM BAS_ID_GENERATOR_RULE@MESTEST ),
UPDATED_TIME = (SELECT UPDATED FROM BAS_ID_GENERATOR_RULE@MESTEST ),
UPDATED_BY = (SELECT UPDATED_BY FROM BAS_ID_GENERATOR_RULE@MESTEST ),
LOCK_VERSION = (SELECT LOCK_VERSION FROM BAS_ID_GENERATOR_RULE@MESTEST ),
NAME = (SELECT NAME FROM BAS_ID_GENERATOR_RULE@MESTEST ),
DESCRIPTION = (SELECT DESCRIPTION FROM BAS_ID_GENERATOR_RULE@MESTEST ),
VERSION= (SELECT VERSION FROM BAS_ID_GENERATOR_RULE@MESTEST ),
STATUS= (SELECT STATUS FROM BAS_ID_GENERATOR_RULE@MESTEST ),
ACTIVE_TIME= (SELECT ACTIVE_TIME FROM BAS_ID_GENERATOR_RULE@MESTEST ),
ACTIVE_USER_RRN = (SELECT ACTIVE_USER FROM BAS_ID_GENERATOR_RULE@MESTEST ),
RULE_TYPE = (SELECT RULE_TYPE FROM BAS_ID_GENERATOR_RULE@MESTEST );
END CASE;
CASE
WHEN BAS_ID_GENERATOR_RULE_LINE.OBJECT_RRN@MESTEST != BAS_ID_GENERATOR_RULE_LINE.OBJECT_RRN
THEN
INSERT INTO BAS_ID_GENERATOR_RULE_LINE
SELECT OBJECT_RRN, ORG_RRN,IS_ACTIVE,CREATED_TIME,CREATED_BY,UPDATED_TIME,UPDATED_BY,LOCK_VERSION,RULE_RRN,RULE_ID, SEQ_NO,DS_TYPE,FIXED_STRING,DATE_TYPE,SPECIFIC_DATE,CALENDAR,DATE_FORMAT,SEQUENCE_TYPE,SEQUENCE_DIRECTION,
EXCLUDE_TYPE,MIN,MAX,BASE_TYPE,BASE_ON,BASE_TABLE,BASE_COLUMN,VARIABLE_TYPE,PARAMETER,WHERE_CLAUSE,
VARIABLE_DIRECTION,START_POSITION,LENGTH,FORMAT_CODE,CODE_LEN,TABLEE,COLUMNN,SIZEE,STRATEGY,EXCLUDE
FROM BAS_ID_GENERATOR_RULE_LINE @MESTEST;
ELSE
UPDATE QMSTEST.BAS_ID_GENERATOR_RULE_LINE
SET OBJECT_RRN = (SELECT OBJECT_RRN FROM BAS_ID_GENERATOR_RULEE_LINE@MESTEST ),
ORG_RRN = (SELECT ORG_RRN FROM BAS_ID_GENERATOR_RULEE_LINE@MESTEST ),
IS_ACTIVE = (SELECT IS_ACTIVE FROM BAS_ID_GENERATOR_RULEE_LINE@MESTEST ),
CREATED_TIME = (SELECT CREATED FROM BAS_ID_GENERATOR_RULEE_LINE@MESTEST ),
CREATED_BY = (SELECT CREATED_BY FROM BAS_ID_GENERATOR_RULE@MESTEST ),
UPDATED_TIME = (SELECT UPDATED_TIME FROM BAS_ID_GENERATOR_RULEE_LINE@MESTEST ),
UPDATED_BY = (SELECT UPDATED_BY FROM BAS_ID_GENERATOR_RULEE_LINE@MESTEST ),
LOCK_VERSION = (SELECT LOCK_VERSION FROM BAS_ID_GENERATOR_RULEE_LINE@MESTEST ),
RULE_RRN = (SELECT RULE_RRN FROM BAS_ID_GENERATOR_RULEE_LINE@MESTEST ),
RULE_ID = (SELECT RULE_ID FROM BAS_ID_GENERATOR_RULEE_LINE@MESTEST ),
SEQ_NO = (SELECT SEQ_NO FROM BAS_ID_GENERATOR_RULEE_LINE@MESTEST ),
DS_TYPE = (SELECT DS_TYPE FROM BAS_ID_GENERATOR_RULEE_LINE@MESTEST ),
FIXED_STRING = (SELECT FIXED_STRING FROM BAS_ID_GENERATOR_RULEE_LINE@MESTEST ),
DATE_TYPE = (SELECT DATE_TYPE FROM BAS_ID_GENERATOR_RULEE_LINE@MESTEST ),
SPECIFIC_DATE = (SELECT SPECIFIC_DATE FROM BAS_ID_GENERATOR_RULEE_LINE@MESTEST ),
CALENDAR = (SELECT CALENDAR FROM BAS_ID_GENERATOR_RULEE_LINE@MESTEST ),
DATE_FORMAT = (SELECT DATE_FORMAT FROM BAS_ID_GENERATOR_RULEE_LINE@MESTEST ),
SEQUENCE_TYPE = (SELECT SEQUENCE_TYPE FROM BAS_ID_GENERATOR_RULEE_LINE@MESTEST ),
SEQUENCE_DIRECTION = (SELECT SEQUENCE_DIRECTION FROM BAS_ID_GENERATOR_RULEE_LINE@MESTEST ),
EXCLUDE_TYPE = (SELECT EXCLUDE_TYPE FROM BAS_ID_GENERATOR_RULEE_LINE@MESTEST ),
MIN = (SELECT MIN FROM BAS_ID_GENERATOR_RULEE_LINE@MESTEST ),
MAX = (SELECT MAX FROM BAS_ID_GENERATOR_RULEE_LINE@MESTEST ),
BASE_TYPE = (SELECT BASE_TYPE FROM BAS_ID_GENERATOR_RULEE_LINE@MESTEST ),
BASE_ON = (SELECT BASE_ON FROM BAS_ID_GENERATOR_RULEE_LINE@MESTEST ),
BASE_TABLE = (SELECT BASE_TABLE FROM BAS_ID_GENERATOR_RULEE_LINE@MESTEST ),
BASE_COLUMN = (SELECT BASE_COLUMN FROM BAS_ID_GENERATOR_RULEE_LINE@MESTEST ),
VARIABLE_TYPE = (SELECT VARIABLE_TYPE FROM BAS_ID_GENERATOR_RULEE_LINE@MESTEST ),
PARAMETER = (SELECT PARAMETER FROM BAS_ID_GENERATOR_RULEE_LINE@MESTEST ),
WHERE_CLAUSE = (SELECT WHERE_CLAUSE FROM BAS_ID_GENERATOR_RULEE_LINE@MESTEST ),
VARIABLE_DIRECTION = (SELECT VARIABLE_DIRECTION FROM BAS_ID_GENERATOR_RULEE_LINE@MESTEST ),
START_POSITION = (SELECT START_POSITIONN FROM BAS_ID_GENERATOR_RULEE_LINE@MESTEST ),
LENGTH = (SELECT LENGTH FROM BAS_ID_GENERATOR_RULEE_LINE@MESTEST ),
FORMAT_CODE = (SELECT FORMAT_CODE FROM BAS_ID_GENERATOR_RULEE_LINE@MESTEST ),
CODE_LEN = (SELECT CODE_LEN FROM BAS_ID_GENERATOR_RULEE_LINE@MESTEST ),
TABLEE = (SELECT TABLEE FROM BAS_ID_GENERATOR_RULEE_LINE@MESTEST ),
COLUMNN = (SELECT COLUMNN FROM BAS_ID_GENERATOR_RULEE_LINE@MESTEST ),
SIZEE = (SELECT SIZEE FROM BAS_ID_GENERATOR_RULEE_LINE@MESTEST ),
STRATEGY = (SELECT STRATEGY FROM BAS_ID_GENERATOR_RULEE_LINE@MESTEST ),
EXCLUDE = (SELECT EXCLUDE FROM BAS_ID_GENERATOR_RULEE_LINE@MESTEST);
END CASE;
END BAS_ID_GENERATOR_RULES;
首先,存储过程中条件判断一般是用if
然后,根据你这些sql来看 ,"BAS_ID_GENERATOR_RULE.OBJECT_RRN"是一个表里面的某个字段名称,不是函数也不是一个值,因此无法用于判断
最后,我猜你是想判断如果远端数据和本地数据不一样,就把远端数据覆盖到本地,但你这个写法已经错得相当离谱了,
如果数据小,我建议你直接用merge into；
如果数据大,这个应该是要做一个增量同步逻辑的,即先识别出哪些记录有变化,再针对有变化的数据去进行处理,这个需要远端提供增量标识