Oracle数组使用以及PLSQL操作记录

最新推荐文章于 2024-07-07 08:30:00 发布

武睿傲雪

最新推荐文章于 2024-07-07 08:30:00 发布

阅读量5.6k

点赞数 3

分类专栏： Oracle学习笔记文章标签： PLSQL 数组

Oracle学习笔记专栏收录该内容

4 篇文章 0 订阅

订阅专栏

----------------------------固定长度数组
declare
type intarray is varray(30) of varchar2(100);
var_arr intarray := intarray('a','b','c','d');
begin
for i in 1..var_arr.count
loop
   dbms_output.put_line(var_arr(i));
end loop;
end;
---------------------------未知长度数组
declare
type vararray is table of varchar2(50) index by binary_integer;
var_arr vararray;
begin
   for i in 1..15 loop
       var_arr(i) := i+10;
   end loop;
   dbms_output.put_line('Count: '|| var_arr.count);
   dbms_output.put_line(var_arr(11));
end;

------------------------自定义1
declare
var_phone varchar2(100);
idex   int;
nextIdex int;
iphone varchar2(100);
begin
var_phone := '10000,10001,10002,10003,10004';
dbms_output.put_line(var_phone);
idex := 0;
nextIdex := 1;
while nextIdex > 0 loop
   nextIdex := instr(var_phone, ',', idex+1);
   dbms_output.put_line(idex || ' --- ' || nextIdex);
   if nextIdex > 0 then
   iphone := substr(var_phone, idex+1, nextIdex-idex-1);
   else
   iphone := substr(var_phone, idex+1);
   end if;
   idex := nextIdex;
   dbms_output.put_line(iphone);
end loop;
end;

------------------- 自定义2 使用数组
declare
var_phone varchar2(100) := '1000';--'10000,10001,10002,10003,10004';
idex   int;
nextIdex int;
type charArray is table of varchar2(100) index by binary_integer;
phone_arr charArray;
arrCount int := 1;
begin
dbms_output.put_line(var_phone);
idex := 0;
nextIdex := 1;
while nextIdex > 0 loop
   nextIdex := instr(var_phone, ',', idex+1);
   dbms_output.put_line(idex || ' --- ' || nextIdex);
   if nextIdex > 0 then
      phone_arr(arrCount) := substr(var_phone, idex+1, nextIdex-idex-1);
      arrCount := arrCount + 1;
   else
      phone_arr(arrCount) := substr(var_phone, idex+1);
   end if;
   idex := nextIdex;
   --dbms_output.put_line();
end loop;
for i in 1..phone_arr.count loop
    dbms_output.put_line(phone_arr(i));
end loop;
end;