Oracle12c存储过程中使用merge自关联时,只有update语句生效了,insert和delete没有生效,请问是什么原因呢?

非著名魔兽解说 发布于 2016/09/29 10:11
阅读 194
收藏 0

Oracle12c存储过程中使用merger自关联时,只有update语句生效了,insert和delete没有生效,请问是什么原因呢?

表结构和数据如下:

--student  
CREATE TABLE TEST_INS_UPD_DEL (  
"ID" NUMBER(10) NOT NULL ,  
"NAME" VARCHAR2(255 BYTE) NULL ,  
"AGE" NUMBER(10) NULL ,  
"SE" VARCHAR2(255 BYTE) NULL  
)  
;  
INSERT INTO TEST_INS_UPD_DEL VALUES ('1201', 'Peter', '23', 'male');  
INSERT INTO TEST_INS_UPD_DEL VALUES ('6695', 'Lu', '12', 'female');  
INSERT INTO TEST_INS_UPD_DEL VALUES ('6720', 'Ka', '22', 'female');  
   
   
   
--teacher  
CREATE TABLE TEST_INS_UPD_DEL_TEACH (  
"ID" NUMBER NULL ,  
"NAME" VARCHAR2(255 BYTE) NULL ,  
"AGE" NUMBER NULL ,  
"SE" VARCHAR2(255 BYTE) NULL  
)  
;  
INSERT INTO TEST_INS_UPD_DEL_TEACH VALUES ('9900', 'John', '47', 'male');



存储过程代码如下:
CREATE OR REPLACE  
PROCEDURE "PRO_TEST_DML_TEACH"  (  
    V_PAGE_NO    IN  VARCHAR2,  
    V_FUN_ID     IN  VARCHAR2,  
    V_JSON_STR   IN  varchar2,  
    V_OUT_RETURN OUT VARCHAR2  
      
                                      
)  
IS 
   --student  
    V_STU_COUNT NUMBER;  
    V_STU_ID NUMBER;  
    V_STU_NAME VARCHAR(100);  
    V_STU_AGE NUMBER;  
    V_STU_SE VARCHAR(100);  
    V_STU_FLAG VARCHAR(100);  
    
    
    
    
  --teacher  
    V_TEACH_COUNT NUMBER;  
    V_TEACH_ID NUMBER;  
    V_TEACH_NAME VARCHAR(100);  
    V_TEACH_AGE NUMBER;  
    V_TEACH_SE VARCHAR(100);  
    V_TEACH_FLAG VARCHAR(100);  
    
    
    
    
    V_OU_MSG VARCHAR(3000);  
    V_JSON_DEST_STR CLOB;             
BEGIN 
       
    
    
    
    
  /*V_JSON_STR's content is:  
   {"stu_total": 5,"teach_total": 2,  
   "teach_rows":[{"ID":8750,"NAME":"Owen","AGE": 55,"SE":"male","FLAG":"insert"},  
   {"ID":9900,"NAME":"King","AGE": 52,"SE":"male","FLAG":"update"}],  
   "stu_rows": [{"ID":1201,"NAME":"Peter","AGE": 23,"SE":"male","FLAG":"delete"},  
   {"ID":1568,"NAME":"Tom","AGE":32,"SE":"male","FLAG":"insert"},  
   {"ID":6695,"NAME":"Lucy","AGE":13,"SE":"female","FLAG":"update"},  
   {"ID":6720,"NAME":"Kate","AGE":15,"SE":"female","FLAG":"update"},  
   {"ID":2336,"NAME":"Jack","AGE": 13,"SE":"male","FLAG":"insert"}]}  
   */ 
         
       
        V_OUT_RETURN := '';     
        IF V_PAGE_NO IS NULL OR V_PAGE_NO !='888' OR   
            V_FUN_ID IS NULL OR V_FUN_ID !='dml' OR   
            V_JSON_STR IS NULL   
             THEN  
            
            V_OUT_RETURN := 'ERROR';  
            RETURN;  
               
       ELSE   
            SELECT regexp_replace(replace(V_JSON_STR,',',','),'\s') INTO V_JSON_DEST_STR from dual;  
            SELECT JSON_VALUE(V_JSON_DEST_STR, '$.stu_total') INTO V_STU_COUNT FROM dual;  
            SELECT JSON_VALUE(V_JSON_DEST_STR, '$.teach_total') INTO V_TEACH_COUNT FROM dual;   
                  
            if V_STU_COUNT>0 then   
                for V_I in 0..(V_STU_COUNT-1)   
                loop  
                execute immediate 'select  JSON_VALUE(''' || V_JSON_DEST_STR || ''', ''$.stu_rows[' || V_I || '].ID''),  
                                   JSON_VALUE(''' || V_JSON_DEST_STR || ''', ''$.stu_rows[' || V_I || '].NAME''),  
                                   JSON_VALUE(''' || V_JSON_DEST_STR || ''', ''$.stu_rows[' || V_I || '].AGE''),  
                                   JSON_VALUE(''' || V_JSON_DEST_STR || ''', ''$.stu_rows[' || V_I || '].SE''),  
                                   JSON_VALUE(''' || V_JSON_DEST_STR || ''', ''$.stu_rows[' || V_I || '].FLAG'')  
                             from  dual' into V_STU_ID,V_STU_NAME,V_STU_AGE,V_STU_SE,V_STU_FLAG;  
    
    
                    MERGE INTO TEST_INS_UPD_DEL b  
                    USING (  
                           SELECT ID,NAME,AGE,SE FROM TEST_INS_UPD_DEL) e            
                          ON (b.ID = e.ID)  
                          WHEN MATCHED THEN  
                    update    
                            set b.NAME = V_STU_NAME,  
                            b.AGE = V_STU_AGE,  
                            b.SE = V_STU_SE  
                            where b.ID = V_STU_ID and V_STU_FLAG = 'update'  
    
                    delete  where (V_STU_FLAG = 'delete')   
    
                    WHEN NOT MATCHED THEN  
                    insert  (b.ID,b.NAME,b.AGE,b.SE)  
                    values(V_STU_ID,V_STU_NAME,V_STU_AGE,V_STU_SE)  
                    WHERE (V_STU_FLAG = 'insert');  
    
                end loop;   
            end if;  
                 
              
            if V_TEACH_COUNT>0 then   
                for V_N in 0..(V_TEACH_COUNT-1)   
                loop  
                execute immediate 'select  JSON_VALUE(''' || V_JSON_DEST_STR || ''', ''$.teach_rows[' || V_N || '].ID''),  
                                   JSON_VALUE(''' || V_JSON_DEST_STR || ''', ''$.teach_rows[' || V_N || '].NAME''),  
                                   JSON_VALUE(''' || V_JSON_DEST_STR || ''', ''$.teach_rows[' || V_N || '].AGE''),  
                                   JSON_VALUE(''' || V_JSON_DEST_STR || ''', ''$.teach_rows[' || V_N || '].SE''),  
                                   JSON_VALUE(''' || V_JSON_DEST_STR || ''', ''$.teach_rows[' || V_N || '].FLAG'')  
                             from  dual' into V_TEACH_ID,V_TEACH_NAME,V_TEACH_AGE,V_TEACH_SE,V_TEACH_FLAG;  
    
    
                        MERGE INTO TEST_INS_UPD_DEL_TEACH b  
                        USING (  
                        SELECT ID,NAME,AGE,SE FROM TEST_INS_UPD_DEL_TEACH) e  
                        ON (b.ID = e.ID)  
                        WHEN MATCHED THEN  
                        update    
                        set b.NAME = V_TEACH_NAME,  
                            b.AGE = V_TEACH_AGE,  
                            b.SE = V_TEACH_SE  
                            where b.ID = V_TEACH_ID and V_TEACH_FLAG = 'update'  
    
                         delete  where (V_TEACH_FLAG = 'delete')  
    
                         WHEN NOT MATCHED THEN  
                        insert  (b.ID,b.NAME,b.AGE,b.SE)  
                        values(V_TEACH_ID,V_TEACH_NAME,V_TEACH_AGE,V_TEACH_SE)  
                        WHERE (V_TEACH_FLAG = 'insert');     
    
                end loop;   
            end if;  
              
            V_OUT_RETURN := 'OK';  
            RETURN;  
       END IF;  
             
        EXCEPTION  
        WHEN OTHERS THEN  
        V_OUT_RETURN := 'ERROR';  
        V_OU_MSG    := 'Reason is:' || SQLERRM;  
        ROLLBACK;  
        INSERT INTO SYS_ERROR_LOG  
          (ROW_ID, PR_NAME, ERROR_DESC, INSDT)  
        VALUES  
          (SYS_ERROR_LOG_SEQ.NEXTVAL, 'PRO_TEST_DML_TEACH', V_OU_MSG, SYSDATE);  
        COMMIT;  
      
END PRO_TEST_DML_TEACH;



执行完存储过程,查询表中的数据,发现只有update的语句生效了,而insert和delete的数据没有生效,
如下所示:

select * from TEST_INS_UPD_DEL;
1201 Peter 23 male
6695 Lucy 13 female
6720 Kate 15 female
 
select * from TEST_INS_UPD_DEL_TEACH;
9900 King 52 male
加载中
0
非著名魔兽解说
非著名魔兽解说

已解决:代码为:

CREATE OR REPLACE 
PROCEDURE            "PRO_TEST_DML_TEACH_MERGE"  (
    V_PAGE_NO    IN  VARCHAR2,--页面Number
    V_FUN_ID     IN    VARCHAR2,--页面Number,对应的功能方法ID
    V_JSON_STR   IN  varchar2,--前端传到后台的JSON字符串
    V_OUT_RETURN OUT VARCHAR2--输出参数
  
                                  
)
IS
   --相当于head表
    V_STU_COUNT NUMBER;
    V_STU_ID NUMBER;
    V_STU_NAME VARCHAR(100);
    V_STU_AGE NUMBER;
    V_STU_SEX VARCHAR(100);
    V_STU_FLAG VARCHAR(100);

--相当于明细表
    V_TEACH_COUNT NUMBER;
    V_TEACH_ID NUMBER;
    V_TEACH_NAME VARCHAR(100);
    V_TEACH_AGE NUMBER;
    V_TEACH_SEX VARCHAR(100);
    V_TEACH_FLAG VARCHAR(100);

    V_OU_MSG VARCHAR(3000);
    V_JSON_DEST_STR CLOB;           
BEGIN
   
 /*输入参数为V_JSON_STR:{"stu_total": 5,"teach_total": 2,
 "teach_rows":[{"ID":8750,"NAME":"Owen","AGE": 55,"SEX":"male","FLAG":"insert"},
 {"ID":9900,"NAME":"King","AGE": 60,"SEX":"male","FLAG":"update"}],
 "stu_rows": 
 [{"ID":1201,"NAME":"Peter","AGE": 23,"SEX":"male","FLAG":"delete"},
 {"ID":1568,"NAME":"Tom","AGE":32,"SEX":"male","FLAG":"insert"},
 {"ID":6695,"NAME":"Lucy","AGE":13,"SEX":"female","FLAG":"update"},
 {"ID":6720,"NAME":"Kate","AGE":15,"SEX":"female","FLAG":"update"},
 {"ID":2336,"NAME":"Jack","AGE": 13,"SEX":"male","FLAG":"insert"}]}
 
     
   
        V_OUT_RETURN := '';   
        IF V_PAGE_NO IS NULL OR V_PAGE_NO !='888' OR 
            V_FUN_ID IS NULL OR V_FUN_ID !='dml' OR 
            V_JSON_STR IS NULL 
             THEN
        
            V_OUT_RETURN := 'ERROR';
            RETURN;
           
       ELSE 
			SELECT regexp_replace(replace(V_JSON_STR,',',','),'\s') INTO V_JSON_DEST_STR from dual;--过滤空白字符和中文逗号
            SELECT JSON_VALUE(V_JSON_DEST_STR, '$.stu_total') INTO V_STU_COUNT FROM dual;--一共有几条学生数据要处理
            SELECT JSON_VALUE(V_JSON_DEST_STR, '$.teach_total') INTO V_TEACH_COUNT FROM dual;--一共有几条老师数据要处理  
              
            if V_STU_COUNT>0 then --有学生数据时执行下面的操作
                for V_I in 0..(V_STU_COUNT-1) 
                loop

                execute immediate 'select  JSON_VALUE(''' || V_JSON_DEST_STR || ''', ''$.stu_rows[' || V_I || '].ID''),
                                   JSON_VALUE(''' || V_JSON_DEST_STR || ''', ''$.stu_rows[' || V_I || '].NAME''),
                                   JSON_VALUE(''' || V_JSON_DEST_STR || ''', ''$.stu_rows[' || V_I || '].AGE''),
                                   JSON_VALUE(''' || V_JSON_DEST_STR || ''', ''$.stu_rows[' || V_I || '].SEX''),
                                   JSON_VALUE(''' || V_JSON_DEST_STR || ''', ''$.stu_rows[' || V_I || '].FLAG'')
                             from  dual' into V_STU_ID,V_STU_NAME,V_STU_AGE,V_STU_SEX,V_STU_FLAG;
				

									
						MERGE INTO TEST_INS_UPD_DEL b  
                        USING (  
                        SELECT V_STU_ID ID,V_STU_NAME NAME,V_STU_AGE AGE,V_STU_SEX SEX FROM dual) E  
                        ON (b.ID = E.ID)  
                        WHEN MATCHED THEN  
                        update    
                        set b.NAME = E.NAME,  
                            b.AGE = E.AGE,  
                            b.SEX = E.SEX  
                            where  V_STU_FLAG = 'update'  
    
                         delete  where (V_STU_FLAG = 'delete')  
                          
                         WHEN NOT MATCHED THEN  
                        insert  (b.ID,b.NAME,b.AGE,b.SEX)  
                        values(E.ID,E.NAME,E.AGE,E.SEX)  
                        WHERE (V_STU_FLAG = 'insert');
										
                    
                end loop; 
            end if;
             
          
            if V_TEACH_COUNT>0 then --有教师数据时执行下面的操作
                for V_N in 0..(V_TEACH_COUNT-1) 
                loop
                execute immediate 'select  JSON_VALUE(''' || V_JSON_DEST_STR || ''', ''$.teach_rows[' || V_N || '].ID''),
                                   JSON_VALUE(''' || V_JSON_DEST_STR || ''', ''$.teach_rows[' || V_N || '].NAME''),
                                   JSON_VALUE(''' || V_JSON_DEST_STR || ''', ''$.teach_rows[' || V_N || '].AGE''),
                                   JSON_VALUE(''' || V_JSON_DEST_STR || ''', ''$.teach_rows[' || V_N || '].SEX''),
                                   JSON_VALUE(''' || V_JSON_DEST_STR || ''', ''$.teach_rows[' || V_N || '].FLAG'')
                             from  dual' into V_TEACH_ID,V_TEACH_NAME,V_TEACH_AGE,V_TEACH_SEX,V_TEACH_FLAG;
									

						MERGE INTO TEST_INS_UPD_DEL_TEACH bb  
                        USING (  
                        SELECT V_TEACH_ID ID,V_TEACH_NAME NAME,V_TEACH_AGE AGE,V_TEACH_SEX SEX FROM dual) ee  
                        ON (bb.ID = ee.ID)  
                        WHEN MATCHED THEN  
                        update    
                        set bb.NAME = ee.NAME,  
                            bb.AGE = ee.AGE,  
                            bb.SEX = ee.SEX  
                            where  V_TEACH_FLAG = 'update'  
    
                         delete  where (V_TEACH_FLAG = 'delete')  
                          
                         WHEN NOT MATCHED THEN  
                        insert  (bb.ID,bb.NAME,bb.AGE,bb.SEX)  
                        values(ee.ID,ee.NAME,ee.AGE,ee.SEX)  
                        WHERE (V_TEACH_FLAG = 'insert');
                 
                end loop; 
            end if;
          
            V_OUT_RETURN := 'OK';
            RETURN;
       END IF;
         
        EXCEPTION
        WHEN OTHERS THEN
        V_OUT_RETURN := 'ERROR';
        V_OU_MSG    := '失败,原因是:' || SQLERRM;
        ROLLBACK;
        INSERT INTO SYS_ERROR_LOG
          (ROW_ID, PR_NAME, ERROR_DESC, INSDT)
        VALUES
				(SYS_ERROR_LOG_SEQ.NEXTVAL, 'PRO_TEST_DML_TEACH_MERGE', V_OU_MSG, SYSDATE);
				
        COMMIT;
  
END PRO_TEST_DML_TEACH_MERGE;



返回顶部
顶部