dokuwiki

CREATE OR REPLACE PROCEDURE MIGRATE_EDUCATION is 
  v_edu_id number;
BEGIN
  for r in (select per_id, 
                   coalesce(per_education, education()) per_education
                   from persons) loop
    for e in (select column_value edu from table(r.per_education)) loop
       begin 
       select edu_id into v_edu_id from educations
          where edu_name = e.edu;
       exception when no_data_found then       
         insert into educations (
           edu_id, 
           edu_name
         ) values (
           wifi_seq.nextval,
           e.edu
         ) returning edu_id into v_edu_id;
         -- v_edu_id :=  wifi_seq.currval;
       end;
       insert into person_x_education (
         exp_id,
         exp_per_id,
         exp_edu_id
       ) values (
         wifi_seq.nextval,
         r.per_id,
         v_edu_id
       );
       dbms_output.put_line(e.edu||' '||v_edu_id);
    end loop;
  end loop;
       
  
  
END MIGRATE_EDUCATION;