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

曾经终败给现在 2022-05-28 03:10 783阅读 0赞

创建存储过程：

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上执行报错......）