Topic: the bound tables in a multiple user mode
to Create archive tables of the same structure that the table of tasks ("task") and the table, binding the task and employees ("task-employee"). To write the function moving records from the main tables ("task" and "task-employee") to appropriate archive tables and working in a multiple user mode. As parameter function receives date which specifies that the tasks completed before this date should be transferred to archive. Function returns 0 if 1 task has been transferred to archive tables at least; function returns 1 if it has not been transferred any task and returns 2 if at performance there was an error.
Not archive tables of the same structure as well as archive: create table tasks_arch (task_id number not null; task_name varchar2 (200) not null; start_date date; stop_date date; description varchar2 (200); manager_id number); create table workflow_arch (task_id number not null; employee_id number not null);
declare p_start_date date: = to_date (' 10/11/2019 ', ' dd.mm.yyyy '); lr_tasks tasks%rowtype; begin for rec_all_tasks in (select rowid, task_id from tasks t where t.start_date <(p_start_date) +1 ) loop begin SavePoint sp_before_tasks; select * into lr_tasks from tasks t where t.rowid = rec_all_tasks.rowid for update nowait; begin insert into tasks_arch (task_id; task_name; start_date; stop_date; description; manager_id) select task_id; task_name; start_date; stop_date; description; manager_id from tasks t where t.task_id = lr_tasks.task_id; insert into workflow_arch (task_id, employee_id) select task_id, employee_id from workflow w where w.task_id = lr_tasks.task_id; delete from workflow w where w.task_id = lr_tasks.task_id; delete from tasks t where t.rowid = rec_all_tasks.rowid; exception when no_data_found then rollback to sp_before_tasks; continue; end; exception when others then rollback to sp_before_tasks; end; end loop; commit; end;