This shows you the differences between two versions of the page.
Next revision | Previous revision | ||
kurs:plsql_tables [2014/04/08 13:17] mh created |
kurs:plsql_tables [2015/04/09 15:38] (current) mh |
||
---|---|---|---|
Line 1: | Line 1: | ||
+ | == PLSQL Table übergeben == | ||
+ | |||
+ | <code> | ||
+ | -------------------------------------------------------- | ||
+ | -- DDL for Package PCK_EMPLOYEES | ||
+ | -------------------------------------------------------- | ||
+ | |||
+ | CREATE OR REPLACE PACKAGE "HR"."PCK_EMPLOYEES" AS | ||
+ | |||
+ | cursor c_emp_dep(cp_department_name varchar2) is | ||
+ | select employee_id, | ||
+ | first_name, | ||
+ | last_name, | ||
+ | salary, | ||
+ | department_name | ||
+ | from employees e join departments d | ||
+ | on e.department_id = d.department_id | ||
+ | where department_name = cp_department_name; | ||
+ | | ||
+ | type type_emp_dep is table of c_emp_dep%rowtype | ||
+ | index by binary_integer; | ||
+ | |||
+ | procedure get_emp_per_dep_name( | ||
+ | p_department_name in departments.department_name%type, | ||
+ | t_emp_dep out type_emp_dep); | ||
+ | |||
+ | procedure print_emp_dep(t_emp_dep in type_emp_dep); | ||
+ | /* | ||
+ | procedure get_emp_per_dep_name (p_department_name in | ||
+ | type_emp_dep out von einem zu definierenden table type | ||
+ | |||
+ | procedure print_emp_dep(type_emp_dep in) | ||
+ | ) | ||
+ | |||
+ | |||
+ | |||
+ | */ | ||
+ | |||
+ | procedure next_emp; | ||
+ | |||
+ | END PCK_EMPLOYEES; | ||
+ | |||
+ | / | ||
+ | </code> | ||
+ | |||
+ | |||
+ | |||
10 zufällige, nicht doppelte Employees ziehen | 10 zufällige, nicht doppelte Employees ziehen | ||
Line 65: | Line 112: | ||
END PLSQL_TABLE; | END PLSQL_TABLE; | ||
</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> | ||
+ |