Oracle12c,现有存储过程需要修改一些代码,使用 "merge" 去做 "insert","update","delete",请问怎么样修改,谢谢!

非著名魔兽解说 发布于 2016/09/28 09:35
阅读 429
收藏 0

@IdleMan 你好,想跟你请教个问题:

Oracle12c,现有存储过程需要修改一些代码,使用 "merge" 去做 "insert","update","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;  
  
                    if V_STU_FLAG = 'insert' then --Here need to modify,use "merge" to do insert  
                        insert into TEST_INS_UPD_DEL(ID,NAME,AGE,SE)values(V_STU_ID,V_STU_NAME,V_STU_AGE,V_STU_SE);  
                    end if;  
    
                    if V_STU_FLAG = 'delete' then --Here need to modify,use "merge" to do delete  
                        delete from TEST_INS_UPD_DEL where ID = V_STU_ID;  
                    end if;  
    
                    if V_STU_FLAG = 'update' then --Here need to modify,use "merge" to do update  
                        update TEST_INS_UPD_DEL   
                        set NAME = V_STU_NAME,  
                            AGE = V_STU_AGE,  
                            SE = V_STU_SE  
                            where ID = V_STU_ID;  
                    end if;  
                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;  
  
                    if V_TEACH_FLAG = 'insert' then --Here need to modify,use "merge" to do insert  
                        insert into TEST_INS_UPD_DEL_TEACH(ID,NAME,AGE,SE)values(V_TEACH_ID,V_TEACH_NAME,V_TEACH_AGE,V_TEACH_SE);  
                    end if;  
    
                    if V_TEACH_FLAG = 'delete' then --Here need to modify,use "merge" to do delete  
                        delete from TEST_INS_UPD_DEL_TEACH where ID = V_TEACH_ID;  
                    end if;  
    
                    if V_TEACH_FLAG = 'update' then --Here need to modify,use "merge" to do update  
                        update TEST_INS_UPD_DEL_TEACH   
                        set NAME = V_TEACH_NAME,  
                            AGE = V_TEACH_AGE,  
                            SE = V_TEACH_SE  
                            where ID = V_TEACH_ID;  
                    end if;  
                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;



请问如何修改?

加载中
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;



0
c
catlover

merge的用法是根据两个表之间的数据对比情况来执行后续的操作的,你这里是使用loop从一条数据中(姑且看做只有一条数据的表)取出V_STU_FLAG字段来进行判断下一步的操作,与表TEST_INS_UPD_DEL根本没有进行关联;

就算是使用唯一可以关联的字段V_STU_ID,根据你的逻辑update和delete操作都是通过该字段进行匹配的,无法进行区分;

所以跟你领导讲下,这里没法进行merge优化的。

0
IdleMan
IdleMan

只能把insert/update或insert/delete 合并为merge into,不同同时三者。

现在的写法 性能要高于merge into,因为是人为判断insert/update/delete操作,合并后需要数据库判断insert/update。

merge into适合:

数据库中存在则更新,不存在则insert;

数据库中存在则删除,不存在则insert;

0
非著名魔兽解说
非著名魔兽解说

引用来自“catlover”的评论

merge的用法是根据两个表之间的数据对比情况来执行后续的操作的,你这里是使用loop从一条数据中(姑且看做只有一条数据的表)取出V_STU_FLAG字段来进行判断下一步的操作,与表TEST_INS_UPD_DEL根本没有进行关联;

就算是使用唯一可以关联的字段V_STU_ID,根据你的逻辑update和delete操作都是通过该字段进行匹配的,无法进行区分;

所以跟你领导讲下,这里没法进行merge优化的。

我现在把代码改为这样了,执行完存储过程,查询表中的数据,发现只有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

请指教,谢谢!

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;



0
非著名魔兽解说
非著名魔兽解说

引用来自“IdleMan”的评论

只能把insert/update或insert/delete 合并为merge into,不同同时三者。

现在的写法 性能要高于merge into,因为是人为判断insert/update/delete操作,合并后需要数据库判断insert/update。

merge into适合:

数据库中存在则更新,不存在则insert;

数据库中存在则删除,不存在则insert;

我现在把代码改为这样了,执行完存储过程,查询表中的数据,发现只有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

请指教,谢谢!

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;

0
c
catlover
你先把v_teach_count>0这段改成我这样试试吧,临时表记得新建下,id字段属性设为unique
execute immediate 'truncate table teach_temp';    
              
            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; 
                              
                execute immediate 'insert into teach_temp values (V_TEACH_ID, V_TEACH_NAME, V_TEACH_AGE, V_TEACH_SE, V_TEACH_FLAG)'
               
                        MERGE INTO (select t1.*, t2.teach_flag from TEST_INS_UPD_DEL_TEACH t1, teach_temp t2 where t1.id = t2.id and t2.teach_flag in ('update', 'delete')) b  
                        USING (  
                        SELECT * from teach_temp t3 where t3.teach_flag in ('update', 'insert')) 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    
    
                        WHEN NOT MATCHED By Target THEN  
                        insert (id, name, age, se)
                        values(V_TEACH_ID,V_TEACH_NAME,V_TEACH_AGE,V_TEACH_SE)
                        
                        WHEN NOT MATCHED By Source THEN delete;     
    
                end loop;   
            end if;


返回顶部
顶部