扫二维码与项目经理沟通
我们在微信上24小时期待你的声音
解答本文疑问/技术咨询/运营咨询/技术建议/互联网交流
PostgreSQL可用ARRAY来替代Oracle中的collection type,包括associative array/Varrays (Variable-Size Arrays)/Nested Tables
10年积累的成都网站制作、做网站经验,可以快速应对客户对网站的新想法和需求。提供各种问题对应的解决方案。让选择我们的客户得到更好、更有力的网络服务。我虽然不认识你,你也不认识我。但先网站设计后付款的网站建设流程,更有南安免费网站建设让你可以放心的选择与我们合作。
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
我们在微信上24小时期待你的声音
解答本文疑问/技术咨询/运营咨询/技术建议/互联网交流