更新不同数据类型的单个列

uwopmtnx  于 2021-07-29  发布在  Java
关注(0)|答案(1)|浏览(355)

我在oracle数据库中有三个表:content、content\u data、data\u type
内容数据中的数据记录如下:

PK_ID  DATA_TYPE_ID  CONTENT_ID   VALUE
1         1             1           0
2         2             1          100
3         3             1          200

我需要根据内容数据表中的数据类型id将“value”列更新为其他“value”列的总和,并且应该为每个内容id执行此过程。例如:value(数据类型(3))=sum(值(数据类型(1)),value(数据类型(2)))
我创建了如下所示的过程。但是它跑得很慢。有没有更好的办法来处理这样的问题?

CREATE OR REPLACE PROCEDURE UPDATE_DATA
AS
    CURSOR your_cursor is
        SELECT DISTINCT CONTENT_ID FROM CONTENT_DATA;
    item your_cursor%rowtype;
BEGIN
    OPEN your_cursor;
    loop
        FETCH your_cursor INTO item;
        UPDATE CONTENT_DATA SET VALUE = 
             (SELECT  SUM(NVL(VALUE,0)) FROM CONTENT_DATA WHERE DATA_TYPE_ID IN(1,2)
                                        AND CONTENT_ID = item.CONTENT_ID) 
        WHERE CONTENT_ID = item.CONTENT_ID AND DATA_TYPE_ID = 3;  

        UPDATE CONTENT_DATA SET VALUE = (SELECT  SUM(NVL(VALUE,0)) FROM CONTENT_DATA WHERE DATA_TYPE_ID  IN(3,4,5,6,7,8,9)
                                        AND CONTENT_ID = item.CONTENT_ID)
        WHERE CONTENT_ID = item.CONTENT_ID
        AND DATA_TYPE_ID = 10;           

        UPDATE CONTENT_DATA SET VALUE = (SELECT  SUM(NVL(VALUE,0)) FROM CONTENT_DATA WHERE DATA_TYPE_ID IN( 10,11,12,13,14)
                                            AND CONTENT_ID = item.CONTENT_ID)
        WHERE CONTENT_ID = item.CONTENT_ID
        AND DATA_TYPE_ID = 15;

    end loop;
    close your_cursor;
END;
jaql4c8m

jaql4c8m1#

我不认为你需要用光标来做这个。我为您构建了一个测试用例来查看它的行为,然后您只需要在游标中为三条语句更改三个更新,而不需要使用游标
测试用例

SQL> create table my_merge_test ( pk_id number, data_type_id number, content_id number, value number );

SQL> select * from my_merge_test ;

     PK_ID DATA_TYPE_ID CONTENT_ID      VALUE
---------- ------------ ---------- ----------
         1            1          1          0
         2            2          1        100
         3            3          1        200
         4            3          1        120
         5            3          1        120

现在,让我们看看第一次更新时要执行的操作:

UPDATE CONTENT_DATA SET VALUE = 
             (SELECT  SUM(NVL(VALUE,0)) FROM CONTENT_DATA WHERE DATA_TYPE_ID IN(1,2)
                                        AND CONTENT_ID = item.CONTENT_ID) 
        WHERE CONTENT_ID = item.CONTENT_ID AND DATA_TYPE_ID = 3;

对于数据类型为1或2的每个content\u id,当项目相同且数据类型为3时,您希望将值更新为value列的和(如果为null,则为0)。对于其他更新,逻辑是相同的,但具有不同的值
您可以使用with子句在一条语句中执行该操作,而不用使用游标

SQL> update my_merge_test t
  2  set t.value = (
  3    with source as ( select  content_id, SUM(NVL(value,0)) as sum_value from my_merge_test where DATA_TYPE_ID IN(1,2)
                   group by  content_id
        )
  select h.sum_value
  from  source h
  where h.content_id = t.content_id and t.data_type_id = 3
) where t.data_type_id = 3
;  4    5    6    7    8    9   10

3 rows updated.

SQL> select * from my_merge_test ;

     PK_ID DATA_TYPE_ID CONTENT_ID      VALUE
---------- ------------ ---------- ----------
         1            1          1          0
         2            2          1        100
         3            3          1        100
         4            3          1        100
         5            3          1        100

SQL>

第二次更新是

update my_merge_test t
set t.value = (
  with source as ( select  content_id, SUM(NVL(value,0)) as sum_value from my_merge_test where DATA_TYPE_ID IN (3,4,5,6,7,8,9)
               group by  content_id 
    )
  select h.sum_value 
  from  source h
  where h.content_id = t.content_id and t.data_type_id = 10
) where t.data_type_id = 10 
;

第三个是

update my_merge_test t
set t.value = (
  with source as ( select  content_id, SUM(NVL(value,0)) as sum_value from my_merge_test where DATA_TYPE_ID IN ( 10,11,12,13,14)
               group by  content_id 
    )
  select h.sum_value 
  from  source h
  where h.content_id = t.content_id and t.data_type_id = 15
) where t.data_type_id = 15 
;

把你的名字换成表格,如果你需要的话告诉我。

相关问题