UPDATE sch_group
   SET id_patient = 11954
 WHERE id_schedule IN (SELECT sg.id_schedule
                         FROM sch_group sg, epis_info ei, episode e, visit v
                        WHERE sg.id_schedule = ei.id_schedule
                          AND ei.id_episode = e.id_episode
                          AND e.id_visit = v.id_visit
                          AND sg.id_patient != v.id_patient
                          AND v.id_patient = 11954);
													commit;
													
													
													DECLARE

    CURSOR c_apps_to_delete IS

        SELECT id_schedule

          FROM schedule

         WHERE flg_status = 'D';

 

    CURSOR c_ind(i_constraint_name VARCHAR2) IS

        SELECT 'CREATE INDEX ' || alc.constraint_name || 'I ON ' || alc.table_name || '(' || acc.column_name || ')' code

          FROM all_constraints alc, all_cons_columns acc

         WHERE alc.r_constraint_name = i_constraint_name

           AND alc.constraint_name = acc.constraint_name

           AND alc.constraint_name NOT IN (SELECT alc.constraint_name

                                             FROM all_ind_columns aic, all_constraints alc, all_cons_columns acc

                                            WHERE aic.table_name = alc.table_name

                                              AND aic.column_name = acc.column_name

                                              AND acc.constraint_name = alc.constraint_name

                                              AND aic.table_name = acc.table_name

                                              AND alc.constraint_type = 'R'

                                              AND alc.r_constraint_name = i_constraint_name);

 

    TYPE t_apps IS TABLE OF c_apps_to_delete%ROWTYPE;

    r_apps t_apps;

 

    l_constraint_name all_constraints.constraint_name%TYPE;

BEGIN

    SELECT constraint_name

      INTO l_constraint_name

      FROM all_constraints

     WHERE table_name = 'SCHEDULE'

       AND constraint_type = 'P';

 

    FOR idx IN c_ind(l_constraint_name)

    LOOP

        BEGIN

            EXECUTE IMMEDIATE idx.code;

        EXCEPTION

            WHEN OTHERS THEN

                NULL;

        END;

    END LOOP;

 

    OPEN c_apps_to_delete;

 

    LOOP

        FETCH c_apps_to_delete BULK COLLECT

            INTO r_apps LIMIT 10000;

    

        FOR i IN 1 .. r_apps.COUNT

        LOOP

            BEGIN

                DELETE FROM sch_prof_outp

                 WHERE id_schedule_outp IN (SELECT id_schedule

                                              FROM schedule_outp

                                             WHERE id_schedule = r_apps(i).id_schedule);

                DELETE FROM sch_resource

                 WHERE id_schedule = r_apps(i).id_schedule;

                DELETE FROM schedule_outp

                 WHERE id_schedule = r_apps(i).id_schedule;

                DELETE FROM schedule_exam

                 WHERE id_schedule = r_apps(i).id_schedule;

                DELETE FROM schedule_analysis

                 WHERE id_schedule = r_apps(i).id_schedule;

                DELETE FROM schedule

                 WHERE id_schedule = r_apps(i).id_schedule;

            

            EXCEPTION

                WHEN OTHERS THEN

                    NULL;

            END;

        

        END LOOP;

    

        COMMIT;

    

        EXIT WHEN c_apps_to_delete%NOTFOUND;

    END LOOP;

END;

/