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