Oracle自定义数组与Case使用实例

1.自定义数组使用实例：
Declare
  --create relation between name and age
  Type shabrave_rec Is Record(
     name Varchar2(255),
     age  Number
  );
  TYPE shabrave_rec_ttype IS TABLE OF shabrave_rec INDEX BY PLS_INTEGER;
  shabrave_rec_tbl shabrave_rec_ttype;

k number;

Begin
  k := 1;
  --insert into array
  For rec In (select name,age from usertable) Loop
    shabrave_rec_tbl(k).name := rec.name;
    shabrave_rec_tbl(k).age := rec.age;
    k := (k+1);
  End Loop;

  --iterate data of array
  For i In 1 .. shabrave_rec_tbl.count Loop
    dbms_output.put_line('Username' || i|| ' '||shabrave_rec_tbl(i).name||' '||shabrave_rec_tbl(i).age);
  End Loop;

End;

2.Case使用实例：
select key,
        CASE --如果key为空或者等于0，都取‘Test’值，否则取本身
             WHEN nvl(key,'0') = '0' THEN 
              'Test'
             ELSE
              key
           END key_name
  from KeyTable k
where k.name in  --查询出有重复的记录
       (select name
          from KeyTable
         group by name
        having count(name) > 1);