Oracle、对象表&嵌套表

发布于 2024-12-28 06:22:27 字数 986 浏览 1 评论 0原文

假设我有 2 个对象 MY_OBJ、MY_NESTED_TABLE_OBJ

CREATE OR REPLACE TYPE MY_NESTED_TABLE_OBJ IS TABLE OF VARCHAR2(100);

CREATE OR REPLACE TYPE MY_OBJ AS OBJECT (
    simple_atribute NUMBER(6),
    table_attribute MY_NESTED_TABLE_OBJ,
    MEMBER PROCEDURE doStuff(text VARCHAR2)
) NOT FINAL INSTANTIABLE;

MY_OBJ 的表

CREATE TABLE TBL_MY_OBJ OF MY_OBJ
(  CONSTRAINT PK_simple_atribute PRIMARY KEY(simple_atribute))
NESTED TABLE table_attribute STORE AS attribute_nst;

如何将 VARCHAR2(100) 插入属于 table_attribute 的嵌套表？罪名是什么？？

进行简单的插入，例如： INSERT INTO attribute_nst VALUES ('some text'); 给出错误

无法引用嵌套表列的存储表

我想要的是从在PROCEDURE doStuff(text VARCHAR2)中插入，我已经尝试过：

INSERT INTO SELF.attribute_nst VALUES (text);
INSERT INTO attribute_nst VALUES (text);
INSERT INTO table_attribute VALUES (text);

...和其他组合，但什么也没有，所以请帮忙！

原文

Say I have 2 objects MY_OBJ, MY_NESTED_TABLE_OBJ

CREATE OR REPLACE TYPE MY_NESTED_TABLE_OBJ IS TABLE OF VARCHAR2(100);

CREATE OR REPLACE TYPE MY_OBJ AS OBJECT (
    simple_atribute NUMBER(6),
    table_attribute MY_NESTED_TABLE_OBJ,
    MEMBER PROCEDURE doStuff(text VARCHAR2)
) NOT FINAL INSTANTIABLE;

MY_OBJ's table

CREATE TABLE TBL_MY_OBJ OF MY_OBJ
(  CONSTRAINT PK_simple_atribute PRIMARY KEY(simple_atribute))
NESTED TABLE table_attribute STORE AS attribute_nst;

How do I insert a VARCHAR2(100) into the nested table belonging to table_attribute?? What is the sintax??

Doing a simple insert like: INSERT INTO attribute_nst VALUES ('some text'); gives the error

cannot reference nested table column's storage table

What i want is to do insert from within PROCEDURE doStuff(text VARCHAR2), i've tried:

INSERT INTO SELF.attribute_nst VALUES (text);
INSERT INTO attribute_nst VALUES (text);
INSERT INTO table_attribute VALUES (text);

...and other combination and no nothing, so please help!

分享到QQ

分享到微博

如果你对这篇内容有疑问，欢迎到本站社区发帖提问参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

发布评论

需要登录才能够评论，你可以免费注册一个本站的账号。

清晨说晚安 2025-01-04 06:22:27

SQL> CREATE OR REPLACE TYPE BODY MY_OBJ AS
  2    member procedure doStuff(text varchar2) is
  3    begin
  4      table_attribute.extend(1);
  5      table_attribute(table_attribute.count) := text;
  6    end;
  7  end;
  8  /

Type body created.

SQL> declare
  2     l_my_obj My_Obj := My_Obj(1,MY_NESTED_TABLE_OBJ());
  3  begin
  4    l_my_obj.doStuff('abc');
  5    l_my_obj.doStuff('def');
  6
  7    insert into tbl_my_obj values (l_my_obj);
  8  end;
  9  /

PL/SQL procedure successfully completed.

SQL> select * from tbl_my_obj;

SIMPLE_ATRIBUTE
---------------
TABLE_ATTRIBUTE
------------------------------------------------------------
              1
MY_NESTED_TABLE_OBJ('abc', 'def')

SQL> CREATE OR REPLACE TYPE BODY MY_OBJ AS
  2    member procedure doStuff(text varchar2) is
  3    begin
  4      table_attribute.extend(1);
  5      table_attribute(table_attribute.count) := text;
  6    end;
  7  end;
  8  /

Type body created.

SQL> declare
  2     l_my_obj My_Obj := My_Obj(1,MY_NESTED_TABLE_OBJ());
  3  begin
  4    l_my_obj.doStuff('abc');
  5    l_my_obj.doStuff('def');
  6
  7    insert into tbl_my_obj values (l_my_obj);
  8  end;
  9  /

PL/SQL procedure successfully completed.

SQL> select * from tbl_my_obj;

SIMPLE_ATRIBUTE
---------------
TABLE_ATTRIBUTE
------------------------------------------------------------
              1
MY_NESTED_TABLE_OBJ('abc', 'def')

回复收藏 0 原文