SQL集合嵌套表varray

DECLARE
TYPE list_of_name_t IS TABLE OF person.first_name%TYPE INDEX BY PLS_INTEGER;
happyfamily list_of_name_t;
l_row PLS_INTEGER;
BEGIN
happyfamily (200002) :='Eli';
happyfamily (-1507) :='Steven';
happyfamily (-90): 'Chris';
happyfamily (88) :='Veva';

l_row := happyfamily.FIRST;
WHILE (l_row IS NOT NULL)
LOOP
DBMS_OUTPUT.PUT_LINE(happyfamily (l_row));
l_row := happyfamily.NEXT(l_row);
END LOOP;
END;

----
CREATE TYPE list_of_names_t IS TABLE OF VARCHAR2(100)
/

DECLARE
happyfamily list_of_names_t := list_of_names_t ();
children list_of_names_t := list_of_names_t ();
parents list_of_names_t ;=list_of_namess_t();
BEGIN
happyfamily.EXTEND(3);
happyfamily (1) :='Eli';
happyfamily (2) :='Steven';
happyfamily (3) :='Chris';

children.EXTEND;
children (1) :='Chris';
children.EXTEND;
children (2) :='Eli';

parents :=happyfamily MULTISET EXCEPT children;

FOR l_row IN parents.FIRST .. parents.LAST
LOOP
DBMS_OUTPUT.PUT_LINE(parents (l_row));
END LOOP;
END;

--------

CREATE TYPE first_names_t IS VARRAY (2) OF VARCHAR2 (100);
/
CREATE TYPE child_names_t IS VARRAY (1) OF VARCHAR2 (100);
/
CREATE TABLE family (
surname VARCHAR2(1000)
,parent_names first_names_t
,children_names child_names_t
);


DECLARE
parents first_names_t := first_names_t ();
children child_names_t := child_names_t ();
BEGIN
parents.EXTEND (2);
parents (1) :='Samuel';
parents (2) :='Charina';

children.EXTEND;
children (1) :='Feather';

INSERT INTO family ( surname, parents_names,children_names )
VALUES('Assurty',parents,children);
END;

SELECT * FROM family;
评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

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

抵扣说明:

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

余额充值