集合赋值

最新推荐文章于 2023-06-21 14:58:53 发布

小妙女

最新推荐文章于 2023-06-21 14:58:53 发布

阅读量365

点赞数

CC 4.0 BY-SA版权

分类专栏：日记

本文链接：https://blog.youkuaiyun.com/weixin_36408281/article/details/53086745

日记专栏收录该内容

22 篇文章

订阅专栏

本文介绍PL/SQL中嵌套表的操作方法，包括赋值、去除重复值、合并表、求交集及差集等实用技巧，并通过具体实例进行演示。

/集合赋值/

/：= 赋值/
declare
type v_varray_type is varray(100) of int;
v_t v_varray_type;
v_t2 v_varray_type;

begin
v_t := v_varray_type(1,2,3,4,3,2,1);
dbms_output.put_line(‘v_t的原数据’);
for i in 1..v_t.count loop
dbms_output.put_line(v_t(i));
end loop;
v_t := v_t2;–赋空值
if v_t is null then
dbms_output.put_line(‘是空的’);
end if;
end;

/————————————-嵌套表———————————————/
/set 去除嵌套表中的重复值/

declare
type v_varray_type is table of int;
v_t v_varray_type;
v_t2 v_varray_type;

begin
v_t := v_varray_type(1,2,3,4,3,2,1);
dbms_output.put_line(‘v_t的原数据’);
for i in 1..v_t.count loop
dbms_output.put_line(v_t(i));
end loop;
v_t := set(v_t);
dbms_output.put_line(‘v_t的新数据’);
for i in 1..v_t.count loop
dbms_output.put_line(v_t(i));
end loop;
end;

/multiset union 合并嵌套表，包含重复值 /
declare
type v_varray_type is table of int;
v_t v_varray_type := v_varray_type(‘1’,’2’,’3’);
v_t2 v_varray_type := v_varray_type(‘2’,’3’,’4’);
v_result v_varray_type;
begin
v_result := v_t multiset union v_t2;
for i in 1 ..v_result.count loop
dbms_output.put_line(v_result (i));
end loop;
end;

/multiset union distinct 合并嵌套表，去除重复值 /
declare
type v_varray_type is table of int;
v_t v_varray_type := v_varray_type(‘1’,’2’,’3’);
v_t2 v_varray_type := v_varray_type(‘2’,’3’,’4’);
v_result v_varray_type;
begin
v_result :=set(v_t multiset union v_t2);
for i in 1 ..v_result.count loop
dbms_output.put_line(v_result (i));
end loop;
end;

declare
type v_varray_type is table of int;
v_t v_varray_type := v_varray_type(‘1’,’2’,’3’);
v_t2 v_varray_type := v_varray_type(‘2’,’3’,’4’);
v_result v_varray_type;
begin
v_result :=v_t multiset union distinct v_t2;
v_result := set (v_result);
for i in 1 ..v_result.count loop
dbms_output.put_line(v_result (i));
end loop;
end;

/multiset intercect 交集 /
declare
type v_varray_type is table of int;
v_t v_varray_type := v_varray_type(‘1’,’2’,’3’);
v_t2 v_varray_type := v_varray_type(‘2’,’3’,’4’);
v_result v_varray_type;
begin
v_result :=v_t multiset intersect v_t2;
v_result := set (v_result);
for i in 1 ..v_result.count loop
dbms_output.put_line(v_result (i));
end loop;
end;

/multiset except 差集 /
declare
type v_varray_type is table of int;
v_t v_varray_type := v_varray_type(‘1’,’2’,’3’);
v_t2 v_varray_type := v_varray_type(‘2’,’3’,’4’);
v_result v_varray_type;
begin
v_result :=v_t multiset except v_t2;
v_result := set (v_result);
for i in 1 ..v_result.count loop
dbms_output.put_line(v_result (i));
end loop;
end;
/————————————-嵌套表———————————————/