User Tools

Site Tools


kurs:migration_von_nested_table_zu_m-n

Differences

This shows you the differences between two versions of the page.

Link to this comparison view

kurs:migration_von_nested_table_zu_m-n [2015/04/16 09:33] (current)
mh created
Line 1: Line 1:
 +<​code>​ 
 +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;​ 
 +</​code>​
kurs/migration_von_nested_table_zu_m-n.txt · Last modified: 2015/04/16 09:33 by mh