Oracle vs PostgreSQL Develop (17)-ARRAY

PostgreSQL can replace collection type in Oracle with ARRAY, including associative array/Varrays (Variable-Size Arrays) / Nested Tables

Oracle

Take a simple example:

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: = item1; end loop;-- SELECT * INTO emp_tab (100) FROM employee WHERE id = 100th Endscape /

It is easier to use 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

Use ARRAY

Drop 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$$

The array of Associative array indexed by string,PG cannot be replaced.

DECLARE-Associative array indexed by string: TYPE population IS TABLE OF NUMBER-Associative array type INDEX BY VARCHAR2 (64);-- indexed by string...

references

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