dokuwiki

PROCEDURE teilnehmer_index_by_varchar is
type teilnehmer_type IS RECORD
     (name        varchar2(25),
      geschlecht  varchar2(25));

TYPE teilnehmer_table_type IS TABLE OF varchar2(25)
      INDEX BY varchar2(25);

tn teilnehmer_table_type;

v_i varchar2(25);

BEGIN
tn('Mark') := 'M';
tn('Karin') := 'W';
tn('Albert') := 'M';
tn('Gökhan') := 'M';
tn('Natalya') := 'W';

v_i := tn.first;
while v_i is not null loop
  dbms_output.put_line(v_i||' '||tn(v_i));
  v_i := tn.next(v_i);
end loop;

END; -- Procedure

PROCEDURE SALARY_DIFF_VC
   IS
cursor bonus_salary is
  SELECT * FROM bonus
    order by salary desc;

TYPE bonus_table_type IS TABLE OF bonus%rowtype
      INDEX BY varchar2(100);

bonus_table bonus_table_type;

bonus_salary_rec bonus%rowtype; 
  
v_index      varchar2(100);

BEGIN

open bonus_salary;
   loop
     fetch bonus_salary into bonus_salary_rec;
     exit when bonus_salary%notfound;
     bonus_table(bonus_salary_rec.first_name||' '||bonus_salary_rec.last_name) 
        := bonus_salary_rec;
 
   end loop;
close bonus_salary;

v_index := bonus_table.first;   
for j in 1..bonus_table.count loop
  dbms_output.put_line(j||' '||
                       bonus_table(v_index).first_name ||' '||
                       bonus_table(v_index).last_name  ||' '||
                       bonus_table(v_index).salary);
  v_index := bonus_table.next(v_index);
end loop;   

END; -- Procedure

CREATE OR REPLACE PROCEDURE DEMO_HASH AS 

type person_record is record (
   r_name varchar2(100),
   r_height integer);
   
type simple_person_table_type is table of person_record
  index by binary_integer;

type person_table_type is table of persons%rowtype
  index by varchar2(20);

type person_table_i_type is table of persons%rowtype
  index by binary_integer;

cursor c_persons is (select * from persons);

tn simple_person_table_type;

v_per person_table_type;
v_i_per person_table_i_type;

v_i binary_integer;
v_vc varchar2(100);
BEGIN  

tn(10).r_name := 'Mark';
tn(10).r_height := 175;

tn(2000).r_name := 'Michael';
tn(2000).r_height := 183;

v_i := tn.first;
while v_i is not null loop 
  dbms_output.put_line(v_i || ' ' ||tn(v_i).r_name);
  v_i := tn.next(v_i);
end loop;

v_per('Michael').per_name := 'Michael';
v_per('Michael').per_birthday := to_date('1981-04-25', 'yyyy-mm-dd');

for r in (select * from persons) loop 
  v_per(r.per_name) := r;
end loop;  

open c_persons;
fetch c_persons bulk collect into v_i_per;
close c_persons;

dbms_output.put_line('bulk collect result');
for i in 1 .. v_i_per.last loop 
dbms_output.put_line(v_i_per(i).per_name||' '||
                     v_i_per(i).per_birthday                       
                    );
end loop;                    
dbms_output.put_line('***');



v_vc := v_per.first;
while v_vc is not null loop 
  dbms_output.put_line(v_vc || ' ' ||v_per(v_vc).per_name||' '||
                       v_per(v_vc).per_birthday                       
                       );
                       
  if v_per(v_vc).per_education is not null then                      
    dbms_output.put_line(
    v_per(v_vc).per_education(v_per(v_vc).per_education.last)
    );
  end if;
  v_vc := v_per.next(v_vc);
end loop;


END DEMO_HASH;