Oracle：如何在表中只激活一行？

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;
/