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

mingjie

发布于 2023-10-13 10:26:13

1170

发布于 2023-10-13 10:26:13

问题

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

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

实例

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);

报错

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

解决

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

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

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后的结果：

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

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;

本文参与腾讯云自媒体分享计划，分享自作者个人站点/博客。

原始发表：2023-06-14，如有侵权请联系 cloudcommunity@tencent.com 删除

oracle