oracle collection

----record|%rowtype|%type的使用方法
declare
  name    pmb_amap_mtr_t.name%type;
  all_col pmb_amap_mtr_t%rowtype;
  type serize is record(global_id pmb_amap_mtr_t.global_id%type, name pmb_amap_mtr_t.name%type, addr pmb_amap_mtr_t.addr%type);
  sezz serize;
begin
  select name into name from pmb_amap_mtr_t where rownum = 1;
  dbms_output.put_line(name.getstringval());
  select * into all_col from pmb_amap_mtr_t where rownum = 1;
  dbms_output.put_line(all_col.pcs_poiid);
  select global_id,name,addr into sezz from pmb_amap_mtr_t where rownum=1;
  dbms_output.put_line(sezz.global_id);
  dbms_output.put_line(sezz.name.getstringval());
  dbms_output.put_line(sezz.addr.getstringval());
end;



------------创建object类型
CREATE or replace TYPE mytype is OBJECT ( 
field1 NUMBER, 
field2 VARCHAR2 (50) 
); 

------嵌套表的使用
create or replace type ffx is table of varchar2(100);---定义嵌套表type类型---要删除嵌套表类型，必须先删除与之相关的表
/
create table llax(m number(6,2),nnbc ffx) nested table nnbc store as ffx2 ;
/
insert into llax(m,nnbc) values(1,ffx('a','b','b','d','e','f','g','h'));----"ffx(....)"像这样的这是初始化
insert into llax(m,nnbc) values(2,ffx('a','b','b','d','e','f')); 
insert into llax(m,nnbc) values(3,ffx('a','b','b'));
/
commit;
/
create or replace function collection2str(nnbc ffx) return varchar2 as
vv varchar2(3000);
begin
if (nnbc.exists(1)) then ---第一个元素是否存在? 嵌套表下标是从1开始的
for i in 1..nnbc.count loop
vv:=vv||nnbc(i);
end loop;
end if;
return vv;
end collection2str;
/
select m,collection2str(nnbc) as nnbc from llax;








declare 
cursor cur is select pcs_poiid from pmb_amap_mtr;
rowdata cur%rowtype;
type xxa is table of varchar2(1000) index by binary_integer;---以int为下标索引的索引表
type xxb is table of varchar2(1000) index by varchar2(10);-----以varchar2为下标索引的索引表
type tta is table of poi.name%type ;------嵌套表  下标从"1"开始
type ppa is table of poi%rowtype index by binary_integer;------以int为下标的plsql记录表
type ppb is table of poi%rowtype index by varchar2(10);--------以varchar2为下标的plsql记录表
yya xxa;
yyb xxb;
qqa tta;
kka ppa;
kkb ppb;
begin


yya(-1):='adsfa';
dbms_output.put_line(yya(-1));


yyb('aa'):='jjjj';
dbms_output.put_line(yyb('aa'));




qqa:=tta('','','','');
qqa(2):='qqaqqaqqaqqaqqa';
dbms_output.put_line(qqa(2));




select * into kka(-10) from poi where rownum=1;
dbms_output.put_line(kka(-10).objectid);


select * into kkb('aa') from poi where telephone is not null and telephone <>' '  and rownum=1;
dbms_output.put_line(kkb('aa').telephone);




open cur;
fetch cur into rowdata;
close cur;
dbms_output.put_line(rowdata.pcs_poiid);


end ;
/




--------
create table lhl_tmp_a (m integer,n number(6,2),p varchar2(300));
create table lhl_tmp_b (m integer,n number(6,2),p varchar2(300));
/
insert into lhl_tmp_a select 1,101,'hello' from dual;
insert into lhl_tmp_a select 2,102,'hello2' from dual;
insert into lhl_tmp_a select 3,103,'hello3' from dual;
insert into lhl_tmp_a select 4,104,'hello4' from dual;
/
insert into lhl_tmp_b select 1,101,'hello' from dual;
insert into lhl_tmp_b select 2,102,'hello2' from dual;
insert into lhl_tmp_b select 3,103,'hello3' from dual;
insert into lhl_tmp_b select 4,104,'hello4' from dual;
insert into lhl_tmp_b select 5,104,'hello5' from dual;
insert into lhl_tmp_b select 3,103,'hello3' from dual;
insert into lhl_tmp_b select 4,104,'hello4' from dual;
insert into lhl_tmp_b select 5,104,'hello5' from dual;
/
commit;
/
create or replace type ppx is table of varchar2(4000);
/
create or replace function collection2str(nnbc ppx) return varchar2 as -------nnbc即可以是SYS_REFCURSOR也可以是ppx类型
vv varchar2(3000);
begin
if (nnbc.exists(1)) then ---判断第一个元素是否存在。 嵌套表下标是从1开始的
for i in 1..nnbc.count loop
vv:=vv||'|'||nnbc(i);
end loop;
end if;
return vv;
end collection2str;
/
select collection2str(cast(multiset((select p from lhl_tmp_b b where a.p=b.p)) as ppx))  from lhl_tmp_a a where rownum=1;
/
declare 
--type ppx is table of lhl_tmp_a.m%type;----如果加上这句救护报错
xx ppx;
begin
select set(cast(multiset((select p from lhl_tmp_b b where a.p=b.p)) as ppx)) into xx from lhl_tmp_a a where rownum=1;--"set"的作用是排重
end ;
/