BULK COLLECT

• 复制
  复制成功

postgres=# create table t5(f1 integer,f2 varchar(10));
CREATE TABLE
postgres=# insert into t5 values(1,'tbase1'); 
INSERT 0 1
postgres=# insert into t5 values(2,'tbase2'); 
INSERT 0 1

postgres=# create or replace procedure p_bulk_collect()
AS 
$$
declare  
    TYPE t5list IS TABLE OF t5.f2%TYPE;
    t5s t5list;
BEGIN
    SELECT f2 BULK COLLECT INTO t5s FROM t5;    
    FOR i IN t5s.FIRST .. t5s.LAST
    LOOP        
        raise notice '%',t5s[i];
    END LOOP;  
END
$$language plpgsql;
NOTICE:  type reference t5.f2%TYPE converted to character varying
CREATE PROCEDURE
postgres=# CALL p_bulk_collect();
NOTICE:  tbase1
NOTICE:  tbase2
CALL

• 复制
  复制成功

postgres=# create table tbl_person(id integer, name text, tdd int);
CREATE TABLE
postgres=# insert into tbl_person values(1,'tbase',1);
insert into tbl_person values(2,'pgxz',1);
INSERT 0 1
postgres=# insert into tbl_person values(2,'pgxz',1);
INSERT 0 1
postgres=# insert into tbl_person values(3,'pg',2);
INSERT 0 1

postgres=# create or replace procedure p_bulkcollect_select_into(a_tdd integer) AS 
$$
declare        
   type TAPersonlist is table of tbl_person%rowtype;        
   vpa TAPersonlist;
   rp tbl_person%rowtype;
begin        
   select * bulk collect into vpa from tbl_person where tdd = a_tdd;
   raise notice 'count=%', vpa.count;
   for i in vpa.first..vpa.last loop
       rp = vpa[i];
       raise notice 'loop=%', i;
       raise notice 'vname=%', rp.name;
   end loop;
   raise notice 'vpa.count=%',vpa.count;
end;
$$language plpgsql;
CREATE PROCEDURE
postgres=# CALL p_bulkcollect_select_into(1);
NOTICE:  count=2
NOTICE:  loop=1
NOTICE:  vname=tbase
NOTICE:  loop=2
NOTICE:  vname=pgxz
NOTICE:  vpa.count=2
CALL