dokuwiki

--------------------------------------------------------
--  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;

/

10 zufällige, nicht doppelte Employees ziehen

CREATE OR REPLACE PROCEDURE PLSQL_TABLE AS 
type type_emp is table of employees%rowtype
  -- index by varchar2(100);
index by binary_integer;

cursor c_emp is select * from employees 
   order by salary desc;

t_emp      type_emp;
t_emp_bulk type_emp;
-- v_index   varchar2(100);
v_index    binary_integer;
v_ind_rand binary_integer;
v_c        number := 0;
BEGIN  
  
  for r_emp in c_emp loop 
    -- t_emp(r_emp.first_name||' '||r_emp.last_name) 
    t_emp(r_emp.employee_id) 
       := r_emp;
  end loop;    
  
  open c_emp;
  fetch c_emp bulk collect into t_emp_bulk;
  close c_emp;

  -- 
  while v_c < 10 loop
    v_ind_rand := trunc(dbms_random.value *
                        t_emp_bulk.count);
    if t_emp_bulk.exists(v_ind_rand) then 
      dbms_output.put_line(v_ind_rand||' '||
                t_emp_bulk(v_ind_rand).first_name);
      v_c := v_c + 1;                
    end if;
  end loop;    

  dbms_output.put_line('2te möglichkeit');
  
  v_index := t_emp.first;
  dbms_output.put_line(t_emp.count);
  /* t_emp.delete(209);
  dbms_output.put_line(t_emp.count);
  while v_index is not null loop
    dbms_output.put_line(v_index||' '||
                        t_emp(v_index).first_name);
    v_index := t_emp.next(v_index);
  end loop;        
  */  
  
  for j in 1..10 loop
    v_ind_rand := trunc(dbms_random.value*t_emp.count);
    v_index := t_emp.first;
    for i in 1..v_ind_rand loop
      v_index := t_emp.next(v_index);
    end loop;
    dbms_output.put_line(v_index||' '||
                        t_emp(v_index).first_name);
    t_emp.delete(v_index);
  end loop;
  
END PLSQL_TABLE;

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;
  */

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;