6.6 オブジェクト・ビューで使用されるネストした表およびVARRAY
ネストした表およびVARRAYのコレクションはどちらも、ビューの列になる場合があります。これらのコレクションは、基礎となるコレクション列から選択するか、または副問合せを使用して合成できます。CAST-MULTISET演算子によって、これらのコレクションを合成します。
この項では、次の項目について説明します。
6.6.1 オブジェクト・ビューのシングルレベル・コレクション
シングルレベル・コレクションを使用してオブジェクト・ビューを作成できます。
最初に例6-1および例6-2を使用することで、empリレーショナル表に存在する各従業員は例6-4の構造を持ちます。このリレーショナル表を使用すると、部門番号、名前、住所および部門に所属する従業員のコレクションを持つdept_viewを構成できます。
まず、従業員型employee_t用のネストした表型を定義します。次に、部門番号、名前、住所、および従業員のネストした表を持つ部門型を定義します。最後に、オブジェクト・ビューdept_viewを定義します。
CAST-MULTISETブロック内のSELECT副問合せにより、現在の部門に所属する従業員のリストが選択されます。MULTISETキーワードは、これが単一の値とは対照的なリストであることを示します。CAST演算子は、結果セットを適切な型、この場合は、ネストした表型employee_list_tにキャスト(型変換)します。
このビューへの問合せでは、それぞれの部門行に、部門番号、名前、住所オブジェクトおよびその部門に所属する従業員のコレクションが入っている部門リストが取得できます。
例6-4 シングルレベル・コレクションを持つビューの作成
-- Requires Ex. 6-1 and Ex. 6-2
CREATE TABLE emp (
empno NUMBER PRIMARY KEY,
empname VARCHAR2(20),
salary NUMBER,
job VARCHAR2 (20),
deptno NUMBER REFERENCES dept(deptno));
CREATE TYPE employee_list_t AS TABLE OF employee_t; -- nested table
/
CREATE TYPE dept_t AS OBJECT (
deptno NUMBER,
deptname VARCHAR2(20),
address address_t,
emp_list employee_list_t);
/
CREATE VIEW dept_view OF dept_t WITH OBJECT IDENTIFIER (deptno) AS
SELECT d.deptno, d.deptname,
address_t(d.deptstreet,d.deptcity,d.deptstate,d.deptzip) AS deptaddr,
CAST( MULTISET (
SELECT e.empno, e.empname, e.salary, e.job
FROM emp e
WHERE e.deptno = d.deptno)
AS employee_list_t)
AS emp_list
FROM dept d;
insert into dept values(100,'ST','400 Oracle Pkwy','Redwood S','CA',94065);
insert into dept values(200,'Sales','500 Oracle Pkwy','Redwood S','CA',94065);
insert into emp values(1,'John',900,'Developer1',100);
insert into emp values(2,'Robert',1000,'Developer2',100);
insert into emp values(3,'Mary', 1000,'Apps1',200);
insert into emp values(4,'Maria',1500,'Developer3',200);
select * from dept_view where deptno = 100;
DEPTNO DEPTNAME
---------- --------------------
ADDRESS(STREET, CITY, STATE, ZIP)
--------------------------------------------------------------------------------
EMP_LIST(EMPNO, ENAME, SALARY, JOB)
--------------------------------------------------------------------------------
100 ST
ADDRESS_T('400 Oracle Pkwy', 'Redwood S', 'CA', '94065')
EMPLOYEE_LIST_T(EMPLOYEE_T(1, 'John', 900, 'Developer1'), EMPLOYEE_T(2, 'Robert'
, 1000, 'Developer2'))
select emp_list from dept_view where deptno = 100;
EMP_LIST(EMPNO, ENAME, SALARY, JOB)
--------------------------------------------------------------------------------
EMPLOYEE_LIST_T(EMPLOYEE_T(1, 'John', 900, 'Developer1'), EMPLOYEE_T(2, 'Robert'
, 1000, 'Developer2'))
6.6.2 オブジェクト・ビューのマルチレベル・コレクション
オブジェクトを表示および問い合せるために作成できます。
マルチレベル・コレクションもシングルレベル・コレクションも、オブジェクト・ビューで同じ方法で作成し、使用します。異なるのは、マルチレベル・コレクションで、別のレベルのコレクションを作成する必要がある点のみです。
例6-5では、マルチレベル・コレクションが含まれているオブジェクト・ビューを作成します。このビューは、コレクションが何も入っていないフラットなリレーショナル表に基づいています。オブジェクト・ビューを作成する準備作業として、この例で使用するオブジェクト型とコレクション型を作成します。それぞれのリレーショナル表と対応するように、オブジェクト型(emp_tなど)を、型がそれぞれの表列の型と対応する属性を使用して定義します。また、従業員型はプロジェクトのネストした表(属性)を持ち、部門型は従業員のネストした表(属性)を持ちます。後者のネストした表は、マルチレベル・コレクションです。CAST-MULTISET演算子をCREATE VIEW文で使用してコレクションを作成します。
例6-5 マルチレベル・コレクションを持つビューの作成
CREATE TABLE depts
( deptno NUMBER,
deptname VARCHAR2(20));
CREATE TABLE emps
( ename VARCHAR2(20),
salary NUMBER,
deptname VARCHAR2(20));
CREATE TABLE projects
( projname VARCHAR2(20),
mgr VARCHAR2(20));
CREATE TYPE project_t AS OBJECT
( projname VARCHAR2(20),
mgr VARCHAR2(20));
/
CREATE TYPE nt_project_t AS TABLE OF project_t;
/
CREATE TYPE emp_t AS OBJECT
( ename VARCHAR2(20),
salary NUMBER,
deptname VARCHAR2(20),
projects nt_project_t );
/
CREATE TYPE nt_emp_t AS TABLE OF emp_t;
/
CREATE TYPE depts_t AS OBJECT
( deptno NUMBER,
deptname VARCHAR2(20),
emps nt_emp_t );
/
CREATE VIEW v_depts OF depts_t WITH OBJECT IDENTIFIER (deptno) AS
SELECT d.deptno, d.deptname,
CAST(MULTISET(SELECT e.ename, e.salary, e.deptname,
CAST(MULTISET(SELECT p.projname, p.mgr
FROM projects p
WHERE p.mgr = e.ename)
AS nt_project_t)
FROM emps e
WHERE e.deptname = d.deptname)
AS nt_emp_t)
FROM depts d;