Oracle嵌套表操作-优快云博客

本文链接：https://blog.youkuaiyun.com/liyongjie/article/details/7397734

本文介绍如何在Oracle数据库中创建及使用嵌套表，包括定义嵌套表类型、创建嵌套表结构、插入数据到嵌套表以及查询嵌套表中的数据。同时展示了更新嵌套表的具体方法。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

/* 定义数据库嵌套表 */
CREATE TYPE vendor AS OBJECT
(
vendor_id      NUMBER(8),
vendor_code    VARCHAR2(30),
vendor_name    VARCHAR2(100),
vendor_address VARCHAR2(200)
)
;

CREATE TYPE vendorlist AS TABLE OF vendor;

CREATE TABLE nestedtable_test(data_id NUMBER, inventory_item_id NUMBER,
organization_id NUMBER, vendors vendorlist) NESTED TABLE vendors store AS vendors_tab;

/* 向数据库中插入嵌套表数据 */
DECLARE
-- 声明并初始化一个vendorlist类型的嵌套表
v_vendor vendorlist := vendorlist(vendor(to_number(to_char(SYSDATE,
                                                             'HH24MISS')),
                                           to_char(SYSDATE, 'HH24MISS'),
                                           '供应商_' ||
                                           to_char(SYSDATE, 'HH24MISS'),
                                           NULL));
BEGIN
INSERT INTO nestedtable_test
VALUES
    (to_number(to_char(SYSDATE, 'HH24MISS')),
     to_number(to_char(SYSDATE, 'HH24MISS')),
     173,
     v_vendor);
END;

/* 查询有嵌套表的数据库 */
DECLARE
v_data_id         nestedtable_test.data_id%TYPE;
v_item_id         nestedtable_test.inventory_item_id%TYPE;
v_organization_id nestedtable_test.organization_id%TYPE;
v_vendors         nestedtable_test.vendors%TYPE;
CURSOR cur_nested IS
    SELECT data_id, inventory_item_id, organization_id, vendors
      FROM nestedtable_test;
BEGIN
OPEN cur_nested;
LOOP
    FETCH cur_nested
      INTO v_data_id, v_item_id, v_organization_id, v_vendors;
    EXIT WHEN cur_nested%NOTFOUND;
    dbms_output.put_line('data_id = ' || to_char(v_data_id) ||
                         'item_id = ' || to_char(v_item_id));
    FOR key_num IN 1 .. v_vendors.COUNT LOOP
      dbms_output.put_line('vendors(' || to_char(key_num) ||
                           ').vendor_name = ' || v_vendors(key_num)
                           .vendor_name);
    END LOOP;
END LOOP;
CLOSE cur_nested;
END;