taais/script/sql/postgresql/update_task_and_subtask.sql

CREATE OR REPLACE PROCEDURE update_task_and_subtask(
    IN p_biz_id INT,
    IN p_bizType VARCHAR
)
LANGUAGE plpgsql
AS $$
BEGIN
    IF p_bizType = 'dataProcess' THEN
        -- 检查 subtask 状态 --失败场景暂时不更新上层任务
        -- PERFORM 1 FROM algorithm_biz_process WHERE subtaskId = p_biz_id AND status = '3';
        -- IF FOUND THEN
            -- 如果有一个状态是3(失败)，更新 algorithm_subtask 表为3(失败)
        --    UPDATE algorithm_subtask
        --    SET status = 3
        --    WHERE id = p_biz_id;
        -- ELSE
            -- 如果algorithm_biz_process所有状态都是2(成功)，更新 algorithm_subtask 表为2(成功)
            PERFORM 1 FROM algorithm_biz_process WHERE status <> '2' AND sub_task_id = ( SELECT sub_task_id FROM algorithm_biz_process WHERE id = p_biz_id) ;
            IF NOT FOUND THEN
                UPDATE algorithm_subtask
                SET status = '2'
                WHERE id = ( SELECT sub_task_id FROM algorithm_biz_process WHERE id = p_biz_id);
            END IF;
            -- 如果algorithm_subtask表所有状态都是2(成功)，更新 algorithm_task 表为2(成功)
            PERFORM 1 FROM algorithm_subtask WHERE status <> '2' AND task_id = (select task_id FROM algorithm_subtask WHERE id = (SELECT sub_task_id FROM algorithm_biz_process WHERE id = p_biz_id));
            IF NOT FOUND THEN
                UPDATE algorithm_task
                SET status = '2'
                WHERE id =  (SELECT task_id FROM algorithm_subtask WHERE id = (SELECT sub_task_id FROM algorithm_biz_process WHERE id = p_biz_id));
            END IF;

        --END IF;
    ELSIF p_bizType = 'dataBizProcess' THEN
        -- 如果algorithm_data_process所有状态都是2(成功)，更新 algorithm_subtask 表为2(成功)
        PERFORM 1 FROM algorithm_data_process WHERE status <> '2' AND sub_task_id = ( SELECT sub_task_id FROM algorithm_biz_process WHERE id = p_biz_id);
        IF NOT FOUND THEN
            UPDATE algorithm_subtask
            SET status = '2'
            WHERE id = ( SELECT sub_task_id FROM algorithm_biz_process WHERE id = p_biz_id);
        END IF;
        -- 如果algorithm_subtask表所有状态都是2(成功)，更新 algorithm_task 表为2(成功)
        PERFORM 1 FROM algorithm_subtask WHERE status <> '2' AND task_id = (SELECT task_id FROM algorithm_subtask WHERE id = (SELECT sub_task_id FROM algorithm_data_process WHERE id = p_biz_id));
        IF NOT FOUND THEN
            UPDATE algorithm_task
            SET status = '2'
            WHERE id =  (SELECT task_id FROM algorithm_subtask WHERE id = (SELECT sub_task_id FROM algorithm_data_process WHERE id = p_biz_id));
        END IF;
    END IF;
END;
$$;