Create a table based on a type

Question

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?

Answer 1

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