Oracle嵌套表作为字段类型如何insert？（ORA-22913: must specify table name for nested table column or attribute）

问题

使用嵌套表作为表字段类型报错：

代码语言：javascript复制

ORA-22913: must specify table name for nested table column or attribute

实例

代码语言：javascript复制

CREATE TYPE people_typ AS OBJECT (
   last_name      VARCHAR2(25),
   department_id  NUMBER(4),
   salary         NUMBER(8,2));
/
CREATE TYPE people_tab_typ AS TABLE OF people_typ;
/
CREATE TABLE hr_info (
   department_id   NUMBER(4),
   location_id     NUMBER(4),
   manager_id      NUMBER(6),
   people          people_tab_typ);

报错

代码语言：javascript复制

ERROR at line 1:
ORA-22913: must specify table name for nested table column or attribute

解决

嵌套表必须指定存储位置：

代码语言：javascript复制

CREATE TABLE hr_info (
   department_id   NUMBER(4),
   location_id     NUMBER(4),
   manager_id      NUMBER(6),
   people          people_tab_typ)
   NESTED TABLE people STORE AS people_stor_tab;

字段中的嵌套表如何增删改查？

insert

代码语言：javascript复制

INSERT INTO hr_info VALUES (280, 1800, 999, people_tab_typ());

INSERT INTO TABLE(SELECT h.people FROM hr_info h
   WHERE h.department_id = 280)
   VALUES ('Smith', 280, 1750);
INSERT INTO TABLE(SELECT h.people FROM hr_info h
   WHERE h.department_id = 280)
   VALUES ('Gao', 500, 2000);

INSERT INTO hr_info VALUES (300, 1800, 999, people_tab_typ());

INSERT INTO TABLE(SELECT h.people FROM hr_info h
   WHERE h.department_id = 300)
   VALUES ('John', 999, 1234);

insert后的结果：

代码语言：javascript复制

SYS@orcl11g>select PEOPLE from hr_info;
PEOPLE_TAB_TYP(PEOPLE_TYP('Smith', 280, 1750), PEOPLE_TYP('Gao', 500, 2000))
PEOPLE_TAB_TYP(PEOPLE_TYP('John', 999, 1234))

update/delete

代码语言：javascript复制

UPDATE TABLE(SELECT h.people FROM hr_info h
   WHERE h.department_id = 280) p
   SET p.salary = p.salary   100;

DELETE TABLE(SELECT h.people FROM hr_info h
   WHERE h.department_id = 280) p
   WHERE p.salary > 1700;

oracle 存储 insert nested table

0 人点赞