"例题描述和简单分析 Oracle数据库中，有 BRANCH 表，存储 DVD 分店信息，部分数据如下所示： BID STREET CITY B001 street1 New York B002 .."

Charname
乾学院 1750 号会员
768 浏览 • 3 年前

对异构数据库进行关联查询

应用计算

例题描述和简单分析

Oracle数据库中，有 BRANCH 表，存储 DVD 分店信息，部分数据如下所示：

BID	STREET	CITY
B001	street1	New York
B002	street2	Houston
B003	street3	LA
B004	street4	Lincoln

MySQL数据库中，有 DVD 表，存储 DVD 的标题及分类信息，部分数据如下所示：

DVDID	CATEGORY	TITLE
D001	science fiction	Transformers IV
D002	science fiction	Transformers II
D003	science fiction	Guardians of the Galaxy
D004	act	The Expendables III
D005	sport	Need for Speed
D006	feature	Grace of Monaco
DVDID	Category	Title

HSQL数据库中，有 DVDCOPY 表，存储 DVD 的多张拷贝，部分数据如下所示：

COPYID	DVDID	BID	STATUS	LASTDATERENTED	LASTDATERETURNED	MEMBERID
C000	D001	B001		7/10/2014	7/13/2014	M001
C001	D004	B001		7/10/2014	7/13/2014	M001
C002	D001	B001		7/10/2014		M001
C003	D005	B001		7/10/2014	7/13/2014	M003
C004	D006	B001		7/10/2014	7/13/2014	M003
C005	D005	B002		7/10/2014	7/13/2014	M003
C006	D002	B002		7/10/2014	7/13/2014	M006
C007	D002	B002		7/10/2014	7/13/2014	M007
C008	D001	B002		7/10/2014	7/13/2014	M008
C009	D004	B002		7/10/2014	7/13/2014	M009
C010	D005	B002		7/10/2014	7/13/2014	M010
C011	D006	B002	Miss	7/10/2014	7/13/2014	M010
C000	D001	B003		7/10/2014	7/13/2014	M001
C001	D004	B003		7/10/2014	7/13/2014	M001
C002	D001	B003	Miss	7/10/2014		M001
C003	D005	B003		7/10/2014	7/13/2014	M003

其中 DVDCOPY 表以 BID 字段和 BRANCH 表关联，以 DVDID 字段和 DVD 表关联。

需求 1：找出城市为 New York 的分店中所有的 DVD 拷贝的标题及分类信息，结果如下：

CITY	COPYID	CATEGORY	TITLE
New York	C000	science fiction	Transformers IV
New York	C001	act	The Expendables III
New York	C002	science fiction	Transformers IV
New York	C003	sport	Need for Speed
New York	C004	feature	Grace of Monaco

需求 2：查询出缺货的 DVD 分店，即现存的 DVD 拷贝不到 4 类的分店，结果如下：

BID	STREET	CITY
B002	street2	Houston
B003	street3	LA
B004	street4	Lincoln

解法及简要说明

在集算器中编写脚本 p1.dfx，如下所示：

需求1：简单SQL

	A
1	=connect("demo").cursor@x("SELECT * FROM DVDCOPY")
2	=connect("mysql").cursor@x("SELECT * FROM DVD")
3	=connect("oracle").cursor@x("SELECT * FROM BRANCH")
4	$select b.CITY as CITY,dc.COPYID as COPYID,d.CATEGORY as CATEGORY,d.TITLE as TITLE from {A1} dc join {A2} d on dc.DVDID=d.DVDID join {A3} b on dc.BID=b.BID where b.CITY='New York'

简要说明：

A1 连接数据源名为 demo 的数据库（HSQL），返回根据 sql 创建的数据库游标，关闭游标时自动关闭数据库连接

A2 连接数据源名为 mysql 的数据库（MySQL），返回根据 sql 创建的数据库游标，关闭游标时自动关闭数据库连接

A3 连接数据源名为 oracle 的数据库（Oracle），返回根据 sql 创建的数据库游标，关闭游标时自动关闭数据库连接

A3 用简单 SQL 关联三表

需求2：SPL

	A
1	=BRANCH=connect("oracle").query@x("SELECT * FROM BRANCH")
2	=DVD=connect("mysql").query@x("SELECT * FROM DVD")
3	=DVDCOPY=connect("demo").query@x("SELECT * FROM DVDCOPY")
4	=DVDCOPY.switch(DVDID,DVD:DVDID;BID,BRANCH:BID)
5	=DVDCOPY.select(STATUS!="Miss" && LASTDATERETURNED!=null)
6	=A5.group(BID)
7	=A6.new(~.BID:BonList,~.(DVDID).icount(CATEGORY):CatCount)
8	=A7.select(CatCount<4)
9	=A8.(BonList)\|(BRANCH\A7.(BonList))

简要说明：

A1-A3：从数据库中检索数据，分别命名为变量 BRANCH、DVD、DVDCOPY。

A4：用 switch 函数，将 DVDCOPY 表中的 DVDID 字段切换成 DVD 表中对应的记录，将 BID 字段切换成 BRANCH 表中对应的记录。

A5：过滤数据：丢失的和未归还的 DVD 拷贝不在计算范围内

A6：A5 按 BID 分组，每组代表一个门店的所有 DVD 拷贝

A7：计算每个门店对应的 DVD 拷贝各有几类。函数 new 可以根据 A6 中的数据生成新的对象 A7，A7 有两个列：BonList 和 CatCount，BonList 直接来自 A6 中组内数据的 BID 列，CatCount 来自于组内数据的 DVDID 列。CatCount 的算法分为三部分：~.(DVDID) 找到每个门店所有的 DVD 拷贝对应的 DVD 记录；icount(CATEGORY) 用来计算 CATEGORY 去重后的个数。

A8：在 A7 中找出 CatCount 小于 4 的门店

A9：运算符“|”表示将两个数据集进行并集计算（可用 union 函数代替），运算符“\”表示差集计算（可用 diff 函数代替）。A8.(BonList)、BRANCH、A7.(BonList) 分别代表：DVDCOPY 表中缺货的门店、所有的门店、DVDCOPY 表中出现过的门店

JAVA 集成这段代码的方法可参考：《Java 如何调用 SPL 脚本》。

问答搜集

https://stackoverflow.com/questions/16991792/java-library-for-cross-database-retrieval

关联查询(2) 异构数据库(4) 简单SQL(1) 分组关联(1)

对异构数据库进行关联查询

例题描述和简单分析

解法及简要说明

需求1：简单SQL

需求2：SPL

目录