oracle学习笔记(嵌套表、可变数组的知识点)

在实际工作中，嵌套表、可变数组用的极少！我劝大家也别用！了解即可！

/*
嵌套表知识点如下：
嵌套表：在一个表中还包含另外一个子表
*/

/*
oracle中的嵌套表、可变数组的知识点
嵌套表、可变数组在实际开发中使用的极少，基
本不会用，谁用嵌套表和可变数组，谁就是吃
饱了撑着

嵌套表是oracle数据库特有的东西

嵌套表和可变数组用的比较少，我反正不太喜欢用这种东西，有
点像鸡肋(食之无味，弃之可惜)，嵌套表和可变数组用起来比
较繁琐，我觉的完全没必要用嵌套表和可变数组，正常人都不会选
择使用嵌套表和可变数组，只有吃饱了撑着的家伙才会选择使用嵌
套表和可变数组

*/

先截个图，看看什么是嵌套表和可变数组，让大家心里有个数！

我就直接po代码了

/*
oracle中的嵌套表、可变数组的知识点
嵌套表、可变数组在实际开发中使用的极少，基
本不会用，谁用嵌套表和可变数组，谁就是吃
饱了撑着

嵌套表是oracle数据库特有的东西

嵌套表和可变数组用的比较少，我反正不太喜欢用这种东西，有
点像鸡肋(食之无味，弃之可惜)，嵌套表和可变数组用起来比
较繁琐，我觉的完全没必要用嵌套表和可变数组，正常人都不会选
择使用嵌套表和可变数组，只有吃饱了撑着的家伙才会选择使用嵌
套表和可变数组

*/

/*
嵌套表知识点如下：
嵌套表：在一个表中还包含另外一个子表
*/

--创建两张主从关系的表,正常的思路如下：
--主表(父表)
--机构/部门表
create table organizations(
	id NUMBER(6) primary key NOT NULL,
	name VARCHAR2(50) NOT NULL
);

--从表(子表)
--人员/员工表
create table persons(
	id NUMBER(10) primary key NOT NULL,
	name VARCHAR2(30) NOT NULL,
	orgId NUMBER(6),
	--添加外键约束(级联外键约束)
	constraint FK_persons_organizations_on_id foreign key (orgId) references organizations(id) ON DELETE CASCADE
	--置空外键约束
	--constraint FK_persons_organizations_on_id foreign key (orgId) references organizations(id) ON DELETE SET NULL
);


/*
oracle中引入了嵌套表的概念
可以直接将员工表的类型作为部门表的字段类型，达到嵌套的功能和目的

定义表中的字段的时候，都需要指定字段的类型，所以嵌套表也同样需要指定类型，这种类
型需要单独定义
*/

--创建类型
/*
类型创建成功之后，还不能直接使用，因为此类型是一个完整的类型，所
以我们要为此类型指定一个名称(给类型取个名字)
*/
--注意：最后的/斜杠不能少，一定要写
CREATE TYPE persons_ty as object(
	id NUMBER(10),
	name VARCHAR2(30)
);/


--给类型取个名字(persons_ty这个类型用persons_nest这个名字来表示),名字可以自己取
--注意：最后的/斜杠不能少，一定要写
CREATE TYPE persons_nest AS TABLE OF persons_ty;/

--以后直接使用persons_nest这个名字表示persons_ty类型，就类似于VARCHAR2表示字符串一样

/*一个部门可以有多个员工*/
--使用嵌套表
create table organizations2(
	id NUMBER(6) primary key NOT NULL,
	name VARCHAR2(50) NOT NULL,
	persons persons_nest --使用嵌套表
) nested TABLE persons store AS persons_nest_tab_temp;

--插入数据
INSERT INTO organizations2(id, name, persons)
VALUES(
1, '人事部',
persons_nest(
persons_ty(100, '令狐冲'),
persons_ty(101, '韦小宝'),
persons_ty(102, '张无忌')
)
);

--插入数据
INSERT INTO organizations2(id, name, persons)
VALUES(
2, '销售部',
persons_nest(
persons_ty(103, '乔峰'),
persons_ty(104, '段誉'),
persons_ty(105, '虚竹')
)
);

--查询嵌套表，可以返回多个人员，以一张内部表的形式出现
SELECT * from organizations2;

--查询嵌套表
--我们分步骤演示，第1步
SELECT persons FROM organizations2;
--第2步
SELECT persons FROM organizations2 WHERE organizations2.id = 1;
--第3步
SELECT * FROM TABLE (SELECT persons FROM organizations2 WHERE organizations2.id = 1);

--查询嵌套表，如下写法是一步到位
SELECT * FROM TABLE (SELECT persons FROM organizations2  WHERE organizations2.id = 2);
SELECT * FROM TABLE (SELECT persons FROM organizations2  WHERE id = 1);

/*
如下会报错，(报错信息：single-row subquery returns more than one row)
单行子查询返回多行
大家自己试一下就知道了
*/
SELECT * FROM TABLE (SELECT persons FROM organizations2);

--
SELECT * from organizations2;

--更新嵌套表
--案例：更新人事部102号员工的名字
UPDATE TABLE (
SELECT persons FROM organizations2 WHERE organizations2.id = 1
)per SET VALUE(per) = persons_ty(102, '郭靖') WHERE per.id = 102;

--案例：更新人事部101号员工的编号和名字
UPDATE TABLE (
SELECT persons FROM organizations2 WHERE organizations2.id = 1
)per SET VALUE(per) = persons_ty(666, '杨过') WHERE per.id = 101;

--更新完嵌套表之后，我们再重新查一下，看下是否更新成功
SELECT * FROM TABLE (SELECT persons FROM organizations2 WHERE organizations2.id = 1);

--
SELECT * FROM TABLE (SELECT persons FROM organizations2 WHERE organizations2.id = 2);


/*

可变数组知识点

可变数组可以理解成嵌套表的升级版，其实和嵌套表差不多，都是差
不多的套路，可变数组实际上就是对内部的嵌套表的内容的长度进行了限制(说
白了，就是对嵌套表中的记录数做了限制，比如限制嵌套表中只能有5条记录)
打个比方：
一个部门可以有多个员工，多个的意思就是一个部门有多少个员工都可以(即嵌套表中的记录数不受限制)
如果换做是可变数组呢？
打个比方：
一个部门只能有10个人(说白了，还是嵌套表的套路，只是对嵌套表中的记录数做了限制，限
制只能有10条记录)

下面直接看案例

*/

--创建类型
--注意：最后的/斜杠不能少，一定要写
CREATE TYPE address AS object(
	id NUMBER(10),
	name VARCHAR2(50),
	note VARCHAR2(300)
);/

--给类型取个名字
--注意：最后的/斜杠不能少，一定要写
--设置数组长度为5，大家可以根据需要，自己定义长度
CREATE TYPE address_List AS varray(5) OF address;/

/*一个部门可以有多个办公地点*/
--使用可变数组
create table organizations3(
	id NUMBER(6) primary key NOT NULL,
	name VARCHAR2(50) NOT NULL,
	addresses address_List --使用可变数组
);

--插入数据
INSERT INTO organizations3(id, name, addresses)
VALUES(
17, '售楼部',
address_List(
address(666, '江西省赣州市于都县', '长征出发地'),
address(888, '江西省赣州市兴国县', '走出了100多位将军，又称为将军县'),
address(999, '江西省赣州市赣县', '在我们于都县隔壁')
)
);

--查询数据
SELECT * FROM organizations3;

/*
插入数据
会报错,exceeded maximum VARRAY limit，超过了数
组的长度，因为我们之前，数组长度定义的是5
*/
INSERT INTO organizations3(id, name, addresses)
VALUES(
26, '经营策划部',
address_List(
address(188, '江西省赣州市会昌县', '会昌桔柚等等'),
address(189, '江西省赣州市上犹县', '上犹绿茶,包米果等等'),
address(190, '江西省赣州市信丰县', '信丰萝卜,赣南脐橙,信丰脐橙,信丰红瓜子等等'),
address(191, '江西省赣州市崇义县', '阳岭茶等等'),
address(192, '江西省赣州市安远县', '香菇鱼饼,安远艾蛋,赣南密瓜,安远粉丝等等'),
address(193, '江西省赣州市定南县', '定南蜜梨等等')
)
);

--插入数据(可以正确插入)
INSERT INTO organizations3(id, name, addresses)
VALUES(
26, '经营策划部',
address_List(
address(188, '江西省赣州市会昌县', '会昌桔柚等等'),
address(189, '江西省赣州市上犹县', '上犹绿茶,包米果等等'),
address(190, '江西省赣州市信丰县', '信丰萝卜,赣南脐橙,信丰脐橙,信丰红瓜子等等'),
address(191, '江西省赣州市崇义县', '阳岭茶等等'),
address(192, '江西省赣州市安远县', '香菇鱼饼,安远艾蛋,赣南密瓜,安远粉丝等等')
--address(193, '江西省赣州市定南县', '定南蜜梨等等')
)
);

--查询数据
SELECT * FROM organizations3;