ITPub博客

首页 > 数据库 > PostgreSQL > Oracle vs PostgreSQL Develop(17) - ARRAY

Oracle vs PostgreSQL Develop(17) - ARRAY

原创 PostgreSQL 作者:husthxd 时间:2019-08-23 15:47:35 0 删除 编辑

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
使用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
$$;

对于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

来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/6906/viewspace-2654739/,如需转载,请注明出处,否则将追究法律责任。

下一篇: 后现代主义
请登录后发表评论 登录
全部评论
ITPUB数据库版块资深版主,对Oracle、PostgreSQL有深入研究。现就职于广州云图数据技术有限公司,系统架构师。

注册时间:2007-12-28

  • 博文量
    1374
  • 访问量
    3827850