develop oracle,Oracle vs PostgreSQL Develop（17） - ARRAY

PostgreSQL可用ARRAY来替代Oracle中的collection type,包括associative array/Varrays (Variable-Size Arrays)/Nested Tables

Oracle

简单举个例子:drop table if exists employee;

create table employee(id int,name varchar(30),department varchar(30),salary float);

insert into employee(id,name,department,salary) select rownum,substrb(object_name,1,30),substrb(object_name,1,30),1000 from dba_objects;

DECLARE

TYPE EmpTabTyp IS TABLE OF employee%ROWTYPE

INDEX BY PLS_INTEGER;

emp_tab EmpTabTyp;

i int := 0;

BEGIN

/* Retrieve employee record. */

for c1 in (select * from employee) loop

emp_tab(i).id := c1.id;

emp_tab(i).name := c1.name;

emp_tab(i).department := c1.department;

emp_tab(i).salary := c1.salary;

i := i+1;

end loop;

-- SELECT * INTO emp_tab(100) FROM employee WHERE id = 100;

END;

/

更简单的做法是使用bulk collection

DECLARE

TYPE EmpTabTyp IS TABLE OF employee%ROWTYPE

INDEX BY PLS_INTEGER;

emp_tab EmpTabTyp;

i int := 0;

BEGIN

/* Retrieve employee record. */

select id,name,department,salary bulk collect into emp_tab from employee;

END;

/

PostgreSQL

使用ARRAYdrop type record_of_employee;

CREATE TYPE record_of_employee AS (id int,name varchar(30),department varchar(30),salary float);

do

$$

declare

employees record_of_employee[];

begin

select array_agg(employee) into employees from employee limit 1;

raise notice 'id is %',employees[1].id;

raise notice 'name is %',employees[1].name;

end

$$;

对于Associative array indexed by string,PG的数组则替代不了.

DECLARE

-- Associative array indexed by string:

TYPE population IS TABLE OF NUMBER -- Associative array type

INDEX BY VARCHAR2(64); -- indexed by string

...

参考资料

PL/SQL Collections and Records

Oracle PL/SQL Collections: Varrays, Nested & Index by Tables

Collections in Oracle PL/SQL

Working with Collections

Take a Dip into PostgreSQL Arrays