[课业] 26 | 数据库基础 | 对象-关系SQL

知识

概述

1. 回顾例子：家属属性通过dependent属性来描述下属两个属性，每个雇员都对应多个家属属性
   
   dependents这样的称作为对象属性，本章主要研究对象属性

对象类型

定义新的数据类型（对象类型）

1. 对象类型的创建和删除
   创建对象：create type typename as object(attrname datatype, …);
   删除对象：drop type typename;
2. 这些数据类型一经定义，便以持久形式保存在数据库系统中，用户可以像使用系统内置的数据类型一样使用这些复杂的数据类型，以此来扩充系统的数据类型

对象类型的使用方法

使用方法一共有三种

1. 使用所创建的对象类型来创建新的类型
   用于定义新类型中的属性，这就实现了类型的嵌套定义
2. 使用创建的对象类型来创建新的表
   用于定义表中的属性

前两种方法就像使用普通的数据类型一样，可以用来定义表或对象类型中的属性

3. 使用对象数据类型来直接创建一张表
   create table table name of typename
   ((constraint-define));
   这样建起来的是一个空表，往表里添加的行就是该类型的对象
   示例
   

对象值的创建、查询与更新

1. 对象值的创建
   两个重要函数：
   对象构造函数：typename (argument, …)
   返回对象取值的函数：value(…)
2. 对象值的查询
3. 对象值的更新
   可以修改整个对象值
   – 表中一个属性的值域是对象类型时，可以用对象值直接对该属性进行赋值
   – 如果一张表是基于对象类型创建的，那么可以用该对象的对象值直接修改整个元组
   可以修改对象中成员属性的值

对象的引用类型

对象的引用类型REF <object-type>
– 指某个元组对象的指针类型
– 可用于实现对象类型之间的嵌套引用
在使用含有REF类型的对象类型来创建关系表时，必须使用scope for子句来限制REF属性的取值范围

1. 定义类型之间的引用关系
   定义方法：先定义一个对象类型X，再定义一个对象类型Y，在类型Y中含有一个对类型X的引用属性（REF属性），从而构成了类型X与类型Y之间的引用关系
   其中，X被称为基本对象类型（被持有引用的类型），Y被称为引用类型
2. 创建含有引用类型的关系表
   基本方法
   – 先使用基本对象类型创建相应的基本关系表
   – 再使用含有ref属性的引用类型创建对应的关系表
   创建示例见下
   scope for子句用来限制ref属性的取值范围，他保证了所有的非空引用在创建时能够指向正确的对象
   即基本过程为
   – 首先，创建三个基本对象类型
   – 其次，创建一个引用类型，包含以上三个基本对象类型的ref属性
   – 然后，直接在三个基本对象类型本身上建立三张表
   – 最后，再创建引用类型的表（注意scope for子句）
3. 引用关系查询
   根据元组之间的ref引用关系进行查询
4. 函数与谓词
   – 两个函数：
   返回对象（元组）的引用指针：REF(…)
   返回引用指针所指向的对象的值：DEREF(…)
   – 两个谓词
   is dangling：有关REF属性的判断谓词，用于判断所引用的元组对象是否存在，如果所引用的元组对象不存在，该谓词返回TRUE；否则返回FALSE；该谓词主要用于检查错误的对象引用指针（被引用的表中的行被删除时，连接到这些行上的ref就会变成挂起的ref，is dangling谓词被用来检查这种异常）
   is null：发现空的ref
   空的ref于被挂起的ref不同，因为被挂起的可以有一个不正确的值
   – 对象引用指针的使用规则：
   一个dangling的ref是非空而无用的；如果o.ordcust是空的或者是dangling的，那么o.ordcust.cname是空的
   示例皆见下
5. 类型的循环嵌套定义
   对象类型不能嵌套定义（即一个对象类型不能嵌套地包含一个与其类型相同的成分），但是ref关系可以实现嵌套引用
   示例见下
6. ref定义的其他约束
   – 两张表之间的相互ref关系的定义
   首先需要定义两个具有相互ref关系的对象类型
   （步骤为：先部分创建第一个对象类型，即只给出类型名，不给出类型的详细定义；再详细定义第二个对象类型，包含对第一个对象的引用；最后详细定义第一个对象类型）
   定义好两个相互ref相关的对象类型之后，再用创建好的对象类型创建关系表
   – 两个具有相互ref关系的表、类型的删除
   在删除类型之前需要先删除表（drop type之前先drop table）
   在删除类型时需要采用强制删除的方式(DROP type typename force)
   – ref属性数据的加载
   方法一：先不管ref属性的赋值（先置为null），再使用update操作修改ref属性上的取值
   方法二：使用带子查询的插入操作
   示例皆见下

集合类型

概述

集合类型允许我们在一行的某一列中放入多个值（值的集合）

表类型(Nested Table 嵌套表)

1. 使用table type来定义表中的属性，实现多值属性的功能
   示例见下
2. 在一条建表命令中可以定义多个嵌套表，每个table-type属性都需要一个对应的嵌套表
3. 对象属性内如果有表类型的成员属性，在给这个对象属性直接建表时，表类型的那个属性就对应成为了嵌套表
4. 嵌套表的访问
   示例见下
5. Oracle数据库中没有提供嵌套表的相等比较运算，可以使用in操作符来实现某些需要通过嵌套表进行的查询功能
   示例见下
6. Oracle数据库中提供了单个对象的相等比较功能
   示例见下
7. Oracle不支持直接对嵌套表的统计查询功能，下述是错误的
   count(e.dependents)
8. 表与其自身的嵌套表的连接查询
   示例见下
9. 如果希望同时列出那些没有dependents的职工信息，可以用outer join来实现这样的查询
10. 嵌套游标(Nested Cursor)：可以使查询结果更加明了
    示例见下
11. 注意：table()不能用于提供最外层select语句的from子句的表

数组类型（包含所有相同类型的数据项，每个数据项有自己的元素属性）

1. 数组类型的定义
   示例
   create type extensions_t as varray(4) of int
   在这个定义中：typename: extensions_t; Element type: int; Maximum length: 4
2. varray是变长数组，似varchar
3. varray类型元素有特定次序
4. 用array type定义表中属性
   示例见下
5. 嵌套表与varray的比较

	嵌套表	varray
成员排列次序	无序	有序
成员最大数目	无限制	确定值
成员存储组织	单独的存储表，一个嵌套表类型的成员变量，指示着这个单独的存储表	直接存储在表中（直接作为成员变量）
访问模式的比较
可以对嵌套表属性进行insert操作，或通过update操作修改其成员的取值
但对于varray属性，则不能执行上述的插入和修改操作，只能通过update属性修改整个varray取值

过程SQL，UDF（用户定义函数），方法

1. 过程SQL示例
   
   上方定义了两个局部变量，下方是一个简单的过程SQL
2. 在SQL中创建函数示例
   
   实现了从1到n的数字加起来赋值给total
3. 创建对象类型示例
   
   再考虑成员函数的实现
   
   

其他内容

暂略

例题

e.g. 1. 创建一个‘姓名’类型（包括名、姓、中间名）

create type name_t as object (
	lname varchar(30),
	fname varchar(30),
	mi varchar(1) 
);

e.g. 2. 创建一个文档类型

create type document as object(
	name varchar(50),
	author varchar(30),
	date Date
);

e.g. 3. 使用对象类型来创建新的数据类型或表

create type person_t as object(
	id int,
	name name_t, 
	age int
);

create table teachers(
	id int, 
	name name_t,
	room int
);

e.g. 4. 使用对象数据类型直接创建一张表

create table people of person_t(
	primary key (id)
);

e.g. 5.

name_t('Einstein', 'Albert', 'E');

e.g. 6. 使用对象类型name_t来定义teachers表中的属性tname，当我们使用插入操作在表teachers中插入新元组时，会自动生成类型为name_t的对象值

insert into teachers 
	values(1234, name_t('Einstein', 'Albert', 'E'), 120);

e.g. 7. 查询位于123号房间的教师编号

select t.tid
from teachers t
where t.room = 123;

e.g. 8. 查询位于123号房间的教师编号和姓名(last name & first name)

select t.tid, t.tlname, t.fname
from teachers t
where t.room = 123;

e.g. 9. 修改元组中的对象属性值

update people p
set p.pname = name_t('Russell', 'Han', null)
where ssno = 123;

e.g. 10. 修改某个对象属性值的成员属性

update people p
set p.pname.mi = 'c'
where ssno  = 123;

e.g. 11. 修改整个元组

update people p
set p = person_t(...)
where ssno = 123;

解释：修改了people表中的ssno为123的元组的值，设置成了新的值
其中people是用对象类型person_t直接创建的表

e.g. 12. 先定义基本对象类型：customer_t, agent_t, product_t，再定义引用类型order_t

create type customer_t as object(
	cid char(4),
	cname varchar(13),
	city varchar(20),
	discount real
);

create type agent_t as object(
	aid char(3),
	aname varchar(13),
	city varchar(20),
	percent smallint
);
 
create type product_t as object(
 	pid char(3),
 	pname varchar(13),
 	city varchar(20),
 	quantity integer,
 	price double precision
);
 
create order_t as object(
	ordno int, 
	month char(3),
	cid char(4),
	aid char(3),
	pid char(3),
	quantity int,
	dollars double precision,
	ordcust ref customer_t,
	ordagent ref agent_t,
	ordered ref product_t
);
//新增三个ref属性，指向三个不同的元组对象(row object)

e.g. 13. 创建含有引用类型的关系表
思路：先使用前面定义的基本对象类型创建基本关系表，再创建含有引用类型的关系表

create table customers of customer_t(primary key(cid));
create table agents of agent_t(primary key(aid));
create table products of product_t(primary key(pid));

create table orders of order_t(
	primary key(ordno),
	scope for (ordcust) is customers,
	scope for (ordagents) is agents,
	scope for (ordered) is products
//意为：三个ref属性的取值范围分别是
);

e.g. 14. 查询价格大于200美元的订单的ordno和顾客名

select o.ordno, o.ordcust.cname
from orders o
where o.dollars > 200

e.g. 15. 查询所有顾客的姓名和代理商的姓名

selete distinct o.ordcust.cname, o.ordagent.aname
from orders o

e.g. 16. 找出所有至少被2个顾客订购的产品的pid值

select distinct o1.pid
from orders o1, orders o2
where o1.pid = o2.pid and o1.ordcust < o2.ordcust;
//避免显式地使用cid

e.g. 17. Retrieve all customer names where the customer does not place an order through agent a05

select c.cname
from customers c
where not exists (
	select *
	from orders o
	where o.aid='a05' and o.ordcust=ref(c);
//最后一个“指针等于指针”
//ref(c)中的c，相当于是遍历customers表时的行，也就是一个基本类型对象，返回对象的指针，没毛病
);

e.g. 18. 取得所有通过NYC的代理商发订单的顾客的cid值

select c.cid
from customers c
where not exists(
	select *
	from agents a
	where a.city='NYC' and not exists(
		select *
		from orders o
		where o.ordcust = ref(c) and o.ordagent = ref(a)
	);
);

e.g. 19. 检索所有警官及其搭档信息

create type police_officer_t as object(
pol_person person_t,
badge_number integer,
partner ref police_officer_t
);

create table police_officer of police_officer_t(
primary key(badge_number),
scope for (partner) is police_officer
);

select value(p), deref(p.partner)
from police_officer p
//from子句里的东西是要被遍历的，选择列表中每一刻的p都是一个对象，取对象的值，也没毛病

e.g. 20. 获得orders中挂起的ref的顾客的cid值

select o.cid
from orders o
where o.ordcust is dangling

等价于

select o.cid
from orders o
where o.ordcust <>(
  select ref(c)
  from customers c
  where o.cid = c.cid;
);

e.g. 21. 检索其搭档年龄超过60岁的所有警官的姓

create type police_officer_t as object(
pol_person person_t,
badge_number integer,
partner ref police_officer_t
);

create table police_officer of police_officer_t(
primary key(badge_number),
scope for (partner) is police_officer
);

select police_officer.pol_person.lastname
from police_officer
where police_officer.partner.pol_person.age > 60;

e.g. 22. 使用方法一对ordcust，ordagent，ordproduct三个属性进行加载

update orders o
set ordcust = (select ref(c) from customers c where c.cid = o.cid),
	ordagent = (select ref(a) from agents a where a.aid = o.oid),
ordproduct.= (select ref(p) from products p where p.pid = o.pid)
//相当于根据已经有的（但是ref属性还没有加载好的）订单数据项的数据自行从customers、agents、products表中进行配对
//因为每个order对象的引用都不是随便就能引用上的，应用的顾客或代理商或产品要和他的属性cid, aid, pid对应上

e.g. 23. 使用带有子查询的插入操作

insert into police_officer
	select value(p), 100, ref(po)
	from people p, police_officer po
	where p.ssno = 123 and po.badge_number = 9;
//与上面的例题相比，这个就是随便找到个人就能插入的

e.g. 24. 创建一个新的表类型（table type）

//定义一个新的表类型
create type dependents_t as table of person_t；
//再使用dependents_t定义表employees中的属性并构成一个嵌套表
//其中dependents为嵌套表类型
create table employee(
	eid int, 
	eperson person_t,
	dependents dependents_t,
	primary key(Eid)
);
nested table dependents stored as dependents;
//执行这个建表命令将在数据库中创建两个关系表：employee（存放职工记录）、dependents（存放所有职工的家属信息，被称为嵌套表）

e.g. 25. 检索雇员101的所有家属的嵌套表

select dependents 
from employees
where eid=101;

e.g. 26. 检索有6个以上家属的雇员的eid

select e.eid
from employees e
where 6 <(
	select count(*)
	from table(e.dependents);
//此处需要强制类型转换，将嵌套表转化成一张表
);

e.g. 27. 列出带有社会保障号为345的家属的雇员的eid

select e.eid
from employees e
where 345 IN(
	select d.ssno
	from table(e.dependents) as d;
);

e.g. 28. 检索带有由name_t(‘Lukes’, ‘David’, E’)为名的家属的雇员的eid

select e.eid
from employees e
where name_t(...) IN (
	select d.pname
	from table(e.dependents) as d;
);

e.g. 29. 显示所有雇员的标识和家属的ssno值（雇员和家属表的乘积可以被用于消除家属表的嵌套）

select e.eid, d.ssno
from employees e, table(e.dependents) as d

注意：无家属的雇员不会被显示在该表中
为了看到无家属的雇员行（带有空ssno），可以在家属的边上加上一个符号(+)，形式如下

select e.eid, d.ssno
from employees e, table(e.dependents) (+) d;

(+)号来自于oracle的外部链接，在这两种情况下，(+)都标志着空值将被填入另外一个表的保留行的那一侧

e.g. 30. 嵌套游标的使用和作用

//普通的查询工作
select e.eid, d.ssno as dep_ssno
from employees e, table(e.dependents) as d
where d.age <16;
//使用嵌套游标的操作
select e.eid
cursor(
	select d.ssno as dep_ssno
	from table(e.dependents) as d 
	where d.age < 16
) as dep_tab
from employees e;
//可以实现对嵌套表属性的统计功能
//select设定一个在employees表上的循环，cursor设定在被检索到的每个employee行的dependents嵌套表上的第二季循环雇员表此刻的显示形式：外层循环发现eid 101， 内层循环为该雇员行发现两个ssno，然后，外层在发现下一个eid，内层再...
select eid
cursor(
	select count(*)
	from table(e.dependents)
)
from employees e;
//也可以使用下面两种方式来实现上述统计查询功能
select eid, (select count(*) from table(e.dependents))
from employees e;
//or
select eid, count(*)
from employees e, table(e.dependents)
group by eid;

e.g. 31. 用Array Type定义表中属性

create type extensions_t as varray(4) of int;
create table phonebook(
	phperson person_t,
	extensions extensions_t
);

可以使用table函数使varray属性转化成一张嵌套表