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