oracle集合

本文介绍了在PL/SQL中如何使用三种不同的集合类型:关联数组(Associative Arrays)、嵌套表(Nested Tables)和变长数组(VARRAY)。通过具体示例展示了每种集合类型的声明、填充和遍历过程,帮助读者理解不同场景下集合类型的应用。

摘要生成于 C知道 ,由 DeepSeek-R1 满血版支持, 前往体验 >

[color=brown][size=x-large]1.Using an associative array[/size][/color]
      1  DECLARE
2 TYPE list_of_names_t IS TABLE OF person.first_name%TYPE
3 INDEX BY PLS_INTEGER;
4 happyfamily list_of_names_t;
5 l_row PLS_INTEGER;
6 BEGIN
7 happyfamily (2020202020) := 'Eli';
8 happyfamily (-15070) := 'Steven';
9 happyfamily (-90900) := 'Chris';
10 happyfamily (88) := 'Veva';
11
12 l_row := happyfamily.FIRST;
13
14 WHILE (l_row IS NOT NULL)
15 LOOP
16 DBMS_OUTPUT.put_line (happyfamily (l_row));
17 l_row := happyfamily.NEXT (l_row);
18 END LOOP;
19* END;

[color=red] Chris
Steven
Veva
Eli[/color]
--------------------------------------------------------------------------------------------------------------------------------------------
[color=brown][size=x-large]2.Using a nested table[/size][/color]
    REM Section A
SQL> CREATE TYPE list_of_names_t IS TABLE OF VARCHAR2 (100);
2 /
Type created.

REM Section B
SQL>
1 DECLARE
2 happyfamily list_of_names_t := list_of_names_t ( );
3 children list_of_names_t := list_of_names_t ( );
4 parents list_of_names_t := list_of_names_t ( );
5 BEGIN
6 happyfamily.EXTEND (4);
7 happyfamily (1) := 'Eli';
8 happyfamily (2) := 'Steven';
9 happyfamily (3) := 'Chris';
10 happyfamily (4) := 'Veva';
11
12 children.EXTEND;
13 children (1) := 'Chris';
14 children.EXTEND;
15 children (2) := 'Eli';
16
17 parents := happyfamily MULTISET EXCEPT children;
18
19 FOR l_row IN parents.FIRST .. parents.LAST
20 LOOP
21 DBMS_OUTPUT.put_line (parents (l_row));
22 END LOOP;
23* END;

[color=red] Steven
Veva[/color]
--------------------------------------------------------------------------------------------------------------------------------------------
[color=brown][size=x-large]3.Using a VARRAY[/size][/color]
    REM Section A
SQL> CREATE TYPE first_names_t IS VARRAY (2) OF VARCHAR2 (100);
2 /
Type created.

SQL> CREATE TYPE child_names_t IS VARRAY (1) OF VARCHAR2 (100);
2 /
Type created.

REM Section B
SQL> CREATE TABLE family (
2 surname VARCHAR2(1000)
3 , parent_names first_names_t
4 , children_names child_names_t
5 );

Table created.

REM Section C
SQL>
1 DECLARE
2 parents first_names_t := first_names_t ( );
3 children child_names_t := child_names_t ( );
4 BEGIN
5 parents.EXTEND (2);
6 parents (1) := 'Samuel';
7 parents (2) := 'Charina';
8 --
9 children.EXTEND;
10 children (1) := 'Feather';
11
12 --
13 INSERT INTO family
14 (surname, parent_names, children_names
15 )
16 VALUES ('Assurty', parents, children
17 );
18 END;
SQL> /

PL/SQL procedure successfully completed.

SQL> SELECT * FROM family
2 /

[color=red] SURNAME
PARENT_NAMES
CHILDREN_NAMES
--------------------------------------------
Assurty
FIRST_NAMES_T('Samuel', 'Charina')
CHILD_NAMES_T('Feather')[/color]
评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值