Oracle存储过程实现同步表数据，若存在更新，不存在插入

  创建存储过程：

CREATE OR REPLACE 
PROCEDURE "TEST_PROC"(NAME IN VARCHAR2,DM IN VARCHAR2) IS
        ZJ VARCHAR2(40);
        JH VARCHAR2(10);
        VERRINFO VARCHAR2(200);
    	USER_COUNT NUMBER;
    BEGIN
        ZJ:= 'YH0' || TRUNC(100000000000+900000000000*DBMS_RANDOM.VALUE);
        
        SELECT XZQH INTO JH FROM ZF_T_SYS_DWDM WHERE DWDM = DM;  

    	INSERT INTO ZF_T_SYS_YH(ID,XM,JH) VALUES(ZJ,NAME,JH);

    	FOR I IN (SELECT * FROM ZF_T_SYS_YH) LOOP

            SELECT COUNT(1) INTO USER_COUNT FROM APP_USER WHERE JYID = I.ID;

            IF USER_COUNT > 0 THEN
    	        UPDATE  APP_USER SET USERNAME = I.XM WHERE JYID=I.ID;	
    	    ELSE
    		INSERT INTO APP_USER(ID,JYID,USERNAME) VALUES(TRUNC(100000000000+900000000000*DBMS_RANDOM.VALUE),I.ID,I.XM);
            END IF;

        END LOOP;
        COMMIT;
    EXCEPTION
      WHEN OTHERS THEN
        BEGIN
          ROLLBACK;
          DBMS_OUTPUT.PUT_LINE(TO_CHAR(SQLCODE));
          VERRINFO := SUBSTR(SQLERRM, 1, 200);
          INSERT INTO ERR(ERRINFO,CREATETIME) VALUES(VERRINFO,SYSDATE);
          COMMIT;
        END;
    END TEST_PROC;

  调用存储过程：

DECLARE
	NAME VARCHAR2(20);
	DM VARCHAR2(40);
BEGIN   
	NAME:='姓名';
	DM:='000000000000';
	TEST_PROC(NAME,DM);    
END;

备注：Oracle的存储过程创建建议采用PL/SQL工具，Navicat Premium创建存储过程容易出错。（同样的过程在PL/SQL上执行成功，在Navicat Premium上执行报错......）