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