oracle 自定义数组测试,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);