This shows you the differences between two versions of the page.
Both sides previous revision Previous revision Next revision | Previous revision | ||
kurs:plsql_tables [2014/11/24 13:53] mh |
kurs:plsql_tables [2015/04/09 15:38] mh |
||
---|---|---|---|
Line 113: | Line 113: | ||
</code> | </code> | ||
+ | === add & delete education === | ||
+ | <code> | ||
+ | CREATE OR REPLACE PROCEDURE ADD_EDUCATION | ||
+ | ( | ||
+ | P_PER_NAME IN VARCHAR2 | ||
+ | , P_NEW_EDUCATION IN VARCHAR2 | ||
+ | ) AS | ||
+ | v_edu education := education(); | ||
+ | v_index number; | ||
+ | v_add_edu education := education(P_new_EDUCATION); | ||
+ | BEGIN | ||
+ | select coalesce(per_education, education()) into v_edu | ||
+ | from persons where per_name = p_per_name; | ||
+ | | ||
+ | /* | ||
+ | v_edu.extend; | ||
+ | v_edu(v_edu.last) := p_new_education; | ||
+ | */ | ||
+ | | ||
+ | v_edu := v_edu multiset union all v_add_edu; | ||
+ | | ||
+ | update persons set per_education = v_edu | ||
+ | where per_name = p_per_name; | ||
+ | | ||
+ | | ||
+ | END ADD_EDUCATION; | ||
+ | |||
+ | /*v_edu.extend; | ||
+ | v_edu(v_edu.last) := 'test1'; | ||
+ | | ||
+ | v_edu.extend; | ||
+ | v_edu(v_edu.last) := 'test2'; | ||
+ | | ||
+ | v_edu.extend; | ||
+ | v_edu(v_edu.last) := 'test3'; | ||
+ | | ||
+ | | ||
+ | dbms_output.put_line(v_edu.first||' '|| v_edu.last); | ||
+ | | ||
+ | for i in v_edu.first .. v_edu.last loop | ||
+ | dbms_output.put_line(i||' '|| v_edu(i)); | ||
+ | end loop; | ||
+ | | ||
+ | v_index := v_edu.first; | ||
+ | loop | ||
+ | dbms_output.put_line(v_index||' '|| v_edu(v_index)); | ||
+ | v_index := v_edu.next(v_index); | ||
+ | exit when v_index is null; | ||
+ | end loop; | ||
+ | */ | ||
+ | </code> | ||
+ | |||
+ | <code> | ||
+ | CREATE OR REPLACE PROCEDURE DELETE_EDUCATION | ||
+ | ( | ||
+ | P_PER_NAME IN VARCHAR2 | ||
+ | , P_DEL_EDUCATION IN VARCHAR2 | ||
+ | ) AS | ||
+ | v_edu education := education(); | ||
+ | v_index number; | ||
+ | v_del_edu education := education(P_DEL_EDUCATION); | ||
+ | BEGIN | ||
+ | select coalesce(per_education, education()) into v_edu | ||
+ | from persons where per_name = p_per_name; | ||
+ | | ||
+ | /* v_index := v_edu.first; | ||
+ | loop | ||
+ | dbms_output.put_line(v_index||' '|| v_edu(v_index)); | ||
+ | if v_edu(v_index) = P_DEL_EDUCATION then | ||
+ | v_edu.delete(v_index); | ||
+ | end if; | ||
+ | v_index := v_edu.next(v_index); | ||
+ | exit when v_index is null; | ||
+ | end loop; | ||
+ | */ | ||
+ | v_edu := v_edu multiset except v_del_edu; | ||
+ | | ||
+ | update persons set per_education = v_edu | ||
+ | where per_name = p_per_name; | ||
+ | | ||
+ | END DELETE_EDUCATION; | ||
+ | </code> | ||