This is an old revision of the document!
-------------------------------------------------------- -- 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;