Nested table usage detailed (PLSQL)

Nested tables

A nested table is a structure that is similar to an indexed table, can be used to hold multiple data, and can also hold data of a composite type

A nested table refers to a data table definition that joins the definition of other internal tables, introduced in Oracle 8, which can be accessed using SQL or dynamically extensible.

CREATE table specify nested table storage space name

Create Table Table name (

Field Name Type

......

Nested table field name nested tables type

) NESTED table nested table field name store as storage space name;

Defining Departmental Tables

DROP TABLE Department

Create Table Department (

Did number,

Deptname varchar (+) NOT NULL,

Projects project_nested,

Constraint Pk_did primary KEY (DID)

) NESTED TABLE projects STORE as projects_nested_table;

To create a new object type

Create type name as OBJECT (

Column name data type,

Column name data type,

......

Column name data type

);

/

Example: Creating an object that represents a project type

Create or replace type Kingsql_type as Object (

ProjectID number,

Projectname varchar (50),

Projectfunds number,

Pubdate Date

);

/

CREATE OR REPLACE TYPE kingsql_type as OBJECT(

ProjectID number,

ProjectName VARCHAR (50),

Projectfunds number,

pubdate DATE

) ;

Defining nested Table Types--project_nested

CREATE OR REPLACE TYPE kingsql_nested as TABLE of Kingsql_type not NULL;

CREATE TABLE department_01 (

Did number,

Deptname VARCHAR () not NULL,

Qt_column kingsql_nested

) NESTED TABLE qt_column STORE as Kingsql_nested_t1(

ProjectID not NULL,

ProjectName not NULL,

Projectfunds not NULL,

pubdate not NULL));

Nested tables Insert Data

INSERT into department_01 values (

1,--first column

' Hehe1 ',--second column

Kingsql_nested (Kingsql_type (1, ' hehe1 ', 1,sysdate), Kingsql_type (one, ' Hehe11 ', 11,sysdate)); Third column first row/third column second row

Inserting data

Declare

V1 kingsql_nested:=kingsql_nested (Kingsql_type (2, ' haha2 ', 2,sysdate));

Begin

INSERT INTO department_01

VALUES (1, ' Hehe1 ', kingsql_nested (Kingsql_type (1, ' hehe1 ', 1,sysdate), Kingsql_type (one, ' Hehe11 ', 11,sysdate)));

INSERT into department_01 values (2, ' haha2 ', v1);

End

/

Querying nested table Data

SELECT * from the (select Department_01.qt_column from department_01 where did=1);

ProjectID PROJECTNAME projectfunds PUBDATE

---------- -------------------------------------------------- ------------ -------------------

1 hehe1 1 2018-05-21 14:35:32

Hehe11 11 2018-05-21 14:35:32

SELECT * from the (select Department_01.qt_column from department_01 where did=2);

ProjectID PROJECTNAME projectfunds PUBDATE

---------- -------------------------------------------------- ------------ -------------------

2 HAHA2 2 2018-05-21 14:35:32

SELECT * from the (select Department_01.qt_column from department_01 where did=1) where projectid=1;

ProjectID PROJECTNAME projectfunds PUBDATE

---------- -------------------------------------------------- ------------ -------------------

1 hehe1 1 2018-05-21 14:35:32

Inserting nested table data directly

INSERT into the (select Department_01.qt_column from department_01 where did=1) VALUES (111, ' hehe111 ', 111,sysdate);

SELECT * from the (select Department_01.qt_column from department_01 where did=1);

Loop Insert 100 rows

Declare
x number:=3;
Begin
For x in 3..103 loop
INSERT into the (select Department_01.qt_column from department_01 where did=2) values (x, ' hehe ', 111,sysdate);
End Loop;
Commit
End

Updating nested table data directly

Update the (select Department_01.qt_column from department_01 where did=1) set projectid=1111 where projectid=1;

Commit

SELECT * from the (select Department_01.qt_column from department_01 where did=1)

Delete nested table data directly

Delete the (select Department_01.qt_column from department_01 where did=1) where projectid=1111;

Commit

SELECT * from the (select Department_01.qt_column from department_01 where did=1);

DROP TABLE department_01 PURGE; Remove from Recycle Bin

Nested table usage detailed (PLSQL)