操作数据库中的数据肯定使用SQL
ORACLE文档上提供了一个例子:You can perform DML operations on nested tables only if they are defined as columns of a table. Therefore, when the query_table_expression_clause of an INSERT, DELETE, or UPDATE statement is a table_collection_expression, the collection expression must be a subquery that selects the table's nested table column. The examples that follow are based on this scenario: CREATE TYPE ProjectType AS OBJECT(
pno NUMBER,
pname CHAR(31),
budget NUMBER);
CREATE TYPE ProjectSet AS TABLE OF ProjectType;
CREATE TABLE Dept (dno NUMBER, dname CHAR(31), projs ProjectSet)
NESTED TABLE projs STORE AS
ProjectSetTable ((Primary Key(Nested_Table_Id, pno))
ORGANIZATION
INDEX COMPRESS 1);
INSERT INTO Dept VALUES (1, 'Engineering', ProjectSet()); This example inserts into the 'Engineering' department's 'projs' nested table: INSERT INTO TABLE(SELECT d.projs
FROM Dept d
WHERE d.dno = 1)
VALUES (1, 'Collection Enhancements', 10000);
This example updates the 'Engineering' department's 'projs' nested table: UPDATE TABLE(SELECT d.projs
FROM Dept d
WHERE d.dno = 1) p
SET p.budget = p.budget + 1000;
This example deletes from the 'Engineering' department's 'projs' nested table DELETE TABLE(SELECT d.projs
FROM Dept d
WHERE d.dno = 1) p
WHERE p.budget > 100000;
ORACLE文档上提供了一个例子:You can perform DML operations on nested tables only if they are defined as columns of a table. Therefore, when the query_table_expression_clause of an INSERT, DELETE, or UPDATE statement is a table_collection_expression, the collection expression must be a subquery that selects the table's nested table column. The examples that follow are based on this scenario: CREATE TYPE ProjectType AS OBJECT(
pno NUMBER,
pname CHAR(31),
budget NUMBER);
CREATE TYPE ProjectSet AS TABLE OF ProjectType;
CREATE TABLE Dept (dno NUMBER, dname CHAR(31), projs ProjectSet)
NESTED TABLE projs STORE AS
ProjectSetTable ((Primary Key(Nested_Table_Id, pno))
ORGANIZATION
INDEX COMPRESS 1);
INSERT INTO Dept VALUES (1, 'Engineering', ProjectSet()); This example inserts into the 'Engineering' department's 'projs' nested table: INSERT INTO TABLE(SELECT d.projs
FROM Dept d
WHERE d.dno = 1)
VALUES (1, 'Collection Enhancements', 10000);
This example updates the 'Engineering' department's 'projs' nested table: UPDATE TABLE(SELECT d.projs
FROM Dept d
WHERE d.dno = 1) p
SET p.budget = p.budget + 1000;
This example deletes from the 'Engineering' department's 'projs' nested table DELETE TABLE(SELECT d.projs
FROM Dept d
WHERE d.dno = 1) p
WHERE p.budget > 100000;
UPDATE TABLE(SELECT d.projs
FROM Dept d
WHERE d.dno = 1) p
SET p.budget = p.budget + 1000;
2 pno NUMBER,
3 pname CHAR(31),
4 budget NUMBER);
5 /类型已创建。SQL> CREATE TYPE ProjectSet AS TABLE OF ProjectType;
2 /类型已创建。SQL> CREATE TABLE Dept (dno NUMBER, dname CHAR(31), projs ProjectSet)
2 NESTED TABLE projs STORE AS
3 ProjectSetTable ((Primary Key(Nested_Table_Id, pno))
4 ORGANIZATION
5 INDEX COMPRESS 1); 表已创建。SQL> INSERT INTO Dept VALUES (1, 'Engineering', ProjectSet()); 已创建 1 行。SQL> INSERT INTO TABLE(SELECT d.projs
2 FROM Dept d
3 WHERE d.dno = 1)
4 VALUES (1, 'Collection Enhancements', 10000);已创建 1 行。SQL> INSERT INTO TABLE(SELECT d.projs
2 FROM Dept d
3 WHERE d.dno = 1)
4 VALUES (2, 'xxxxxxx', 20000);已创建 1 行。SQL> commit ;提交完成。SQL> SELECT d.projs
2 FROM Dept d
3 WHERE d.dno = 1;PROJS(PNO, PNAME, BUDGET)
--------------------------------------------------------------------------------
PROJECTSET(PROJECTTYPE(1, 'Collection Enhancements ', 10000), PROJECTTYPE
(2, 'xxxxxxx ', 20000))
上面是两个纪录