sql - 根据类型创建表

Question

我有以下代码片段 (SQL3):

Create Type person;
Create Type child AS Table Of Ref person;
Create Type person AS Object
(name varchar(10),
father ref person,
children child);

我想了解第一个和第二个创建类型的人有什么区别。另外，如何根据类型 person 创建一个表 Employee 并向其中插入数据？

Answer 1

what is the difference between the first and the second create type person.

Create Type person;

首先是类型的前向声明。此时，类型还不完整，但它告诉数据库它将存在并且可以在其他 DDL 语句中使用。

此前向声明是必需的，以便可以创建 child 类型，因为它引用 person 类型。

Create Type person AS Object(
  name     varchar(10),
  father   ref person,
  children child
);

person 的第二个声明是该类型的完整声明。这在 child 依赖项被编译之前无法实现，反过来，在数据库知道 person 类型存在之前无法实现，这将创建一个循环依赖链;并且类型的前向声明解决了这个问题。

how can I create a table Employee based on the type person

简单的解决方法是:

CREATE TABLE Employee OF Person
  NESTED TABLE children STORE AS employee_children;

但是，您可能想要指定一个主键，并且 REF 列的范围也是 EMPLOYEE 表。然后你可以这样做:

Create Type person;

Create Type child AS Table Of Ref person;

Create Type person AS Object(
  id       NUMBER(12,0),
  name     varchar(10),
  father   ref person,
  children child
);

CREATE TABLE Employee OF Person(
  father SCOPE IS Employee,
  CONSTRAINT Employee__ID__PK PRIMARY KEY (ID)
) NESTED TABLE children STORE AS employee_children;

ALTER TABLE employee_children ADD SCOPE FOR ( COLUMN_VALUE ) IS Employee;

how can I [...] insert data into it?

只需使用INSERT:

-- Create the father:
INSERT INTO Employee (
  id,
  name,
  father,
  children
) VALUES (
  1,
  'Adam',
  NULL,
  child()
);

-- Create a child:
INSERT INTO Employee (
  id,
  name,
  father,
  children
) VALUES (
  2,
  'Bob',
  ( SELECT REF(e) FROM Employee e WHERE id = 1 ),
  child()
);

-- Add the child to the parent's children:
INSERT INTO TABLE( SELECT children FROM Employee WHERE id = 1 )
VALUES ( ( SELECT REF(e) FROM Employee e WHERE id = 2 ) );

-- Add another child:
INSERT INTO Employee (
  id,
  name,
  father,
  children
) VALUES (
  3,
  'Charles',
  ( SELECT REF(e) FROM Employee e WHERE id = 1 ),
  child()
);

-- Again, add the child to the parent's children:
INSERT INTO TABLE( SELECT children FROM Employee WHERE id = 1 )
VALUES ( ( SELECT REF(e) FROM Employee e WHERE id = 3 ) );

然后，如果您想列出员工及其子女:

SELECT e.id,
       e.name,
       c.COLUMN_VALUE.id AS child_id,
       c.COLUMN_VALUE.name AS child_name
FROM   employee e
       OUTER APPLY TABLE( e.children ) c

输出:

ID | NAME    | CHILD_ID | CHILD_NAME-: | :------ | -------: | :--------- 1 | Adam    |        2 | Bob        1 | Adam    |        3 | Charles    2 | Bob     |     null | null       3 | Charles |     null | null      

db<> fiddle here