根据类型创建表 [英] Create a table based on a type

问题描述

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

创建类型的人；创建类型子 AS 引用人表；创建类型 person AS 对象(名称varchar(10)，父亲参考人，儿童儿童)；

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

解决方案

第一个和第二个创建类型的人有什么区别.

创建类型的人；

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

此前向声明是必需的，以便可以在引用 person 类型时创建 child 类型.

创建类型 person AS Object(名称 varchar(10),父亲参考人，孩子 孩子);

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

<块引用>

如何创建基于person类型的Employee表

简单的解决方法是:

CREATE TABLE Employee OF Person嵌套表儿童存储为员工儿童；

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

创建类型的人；创建类型子 AS 引用人表；创建类型 person AS Object(编号 (12,0),名称 varchar(10),父亲参考人，孩子 孩子);CREATE TABLE Employee of Person(父亲 SCOPE 是雇员，约束员工__ID__PK 主键(ID)) 嵌套表儿童存储为员工儿童；ALTER TABLE employee_children 添加范围(COLUMN_VALUE)是员工；

<块引用>

我如何[...] 将数据插入其中?

只需使用INSERT:

-- 创建父亲:插入员工 (ID，名称，父亲，孩子们) 值 (1、'亚当'，空值，孩子());-- 创建一个子项:插入员工 (ID，名称，父亲，孩子们) 值 (2、'鲍勃',( SELECT REF(e) FROM Employee e WHERE id = 1 ),孩子());-- 将孩子添加到父母的孩子:INSERT INTO TABLE(从员工WHERE id = 1中选择孩子)值 ( ( SELECT REF(e) FROM Employee e WHERE id = 2 ) );-- 添加另一个孩子:插入员工 (ID，名称，父亲，孩子们) 值 (3、'查尔斯',( SELECT REF(e) FROM Employee e WHERE id = 1 ),孩子());-- 再次将孩子添加到父母的孩子中:INSERT INTO TABLE(从员工WHERE id = 1中选择孩子)值 ( ( SELECT REF(e) FROM Employee e WHERE id = 3 ) );

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

SELECT e.id,e.name,c.COLUMN_VALUE.id 作为 child_id，c.COLUMN_VALUE.name AS child_name来自员工 e外申请表(e.children)c

输出:

<块引用><前>身份证 |姓名 |CHILD_ID |CHILD_NAME-: |:------ |-------: |:---------1 |亚当 |2 |鲍勃1 |亚当 |3 |查尔斯2 |鲍勃 |空 |空3 |查尔斯 |空 |空

db<>fiddle 这里

I have this following code snippet (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);

I want to understand what is the difference between the first and the second create type person. Also how can I create a table Employee based on the type person and insert data into it?

解决方案

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

Create Type person;

The first is a forward declaration of the type. At this point, the type is not complete but it tells the database that it will exist and can be used in other DDL statements.

This forward declaration is required so that the child type can be created as it references the person type.

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

The second declaration of person is a complete declaration of the type. This cannot be implemented until the child dependency has been compiled which, in turn, could not be implemented until the database knew that a person type exists which would create a circular dependency chain; and the forward declaration of the type gets around this.

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

The simple solution to is:

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

However, you probably want to specify a primary key and that the scope for the REF columns is also the EMPLOYEE table. Then you could do:

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?

Just use an 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 ) );

Then, if you want to list the employees and their children:

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

Outputs:

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

这篇关于根据类型创建表的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！