甲骨文 : How to have only one row active in a table?

Question

我有一个 Oracle 表，其字段名为 活跃 .
该字段具有唯一约束，因此只有一行将被标记为事件。

有什么办法在数据库添加新行或更新旧行时仅保留一行事件的图层？

例子

表的当前状态

ID        Active
----------------
1         yes

添加了一个新的事件行:

表的新状态

ID        Active
----------------
1         No
2         Yes

第 1 行更新为 Active = Yes

ID        Active
----------------
1         Yes
2         No

当然，当插入新行时，我无法使用触发器更新表。

有没有人知道如何做到这一点？

Answer 1

您需要使用有效值作为输入参数调用此过程

CREATE OR REPLACE PROCEDURE t416493.set_tab
    (
     p_id IN tab.id%TYPE
    ,p_active_flag IN tab.active%TYPE
    ,p_dml_type    IN VARCHAR2
    )

    IS 

    CURSOR check_flag_exists
    IS 
    SELECT id 
    FROM tab
    WHERE active = p_active_flag;

    v_id tab.id%TYPE;
    v_active_flag VARCHAR2(3);

    BEGIN

        OPEN check_flag_exists;
          FETCH check_flag_exists INTO v_id;
            IF check_flag_exists%FOUND THEN 

              IF  p_active_flag ='Yes' THEN
                  v_active_flag :='No';
              ELSE
                 v_active_flag :='Yes';
              END IF;

                UPDATE tab
                 SET  active = v_active_flag
                WHERE id =v_id; 

            END IF; 
         CLOSE check_flag_exists;  

         IF p_dml_type ='INSERT' THEN 
          INSERT INTO tab 
           (id
           ,active
           )
           VALUES
           (p_id
           ,p_active_flag
           );
         ELSIF p_dml_type ='UPDATE' THEN
           UPDATE tab
           SET active =p_active_flag
           WHERE id =v_id; 
         END IF;  

    END set_tab;

您需要像这样调用您的 proc，如下所示:

开始

 set_tab
       (
         p_id =>2
        ,p_active_flag =>'Yes'
        ,p_dml_type   =>'INSERT'
        );

end;
/