oracle 我已经创建了下面的表stafftable，但当我尝试插入时，它显示错误

dgjrabp2 于 2022-11-28 发布在 Oracle

关注(0)|答案(1)|浏览(169)

CREATE TYPE accounttype AS OBJECT( no varchar2(10), name varchar2(10), balance number(10), dob date, member function age return number );

CREATE TYPE BODY accounttype AS
MEMBER FUNCTION age RETURN NUMBER
AS
BEGIN
RETURN FLOOR(MONTHS_BETWEEN(sysdate,dob)/12);
END age;
END;
/

CREATE TYPE account_branchtype AS OBJECT( account REF accounttype, branch  varchar2(10) );

create type account_branchtabletype as table of account_branchtype;

create type stafftype as object(staff_id varchar2(20),name varchar2(20) ,sal number(20), other_details varchar2(20) , emp8 account_branchtabletype ,dob date , member function getage return number);

create or replace type body stafftype as member function getage return number
as
begin
return(round((sysdate-dob)/365));
end getage;
end;
/

create table stafftable of stafftype nested table emp8 store as relaccount_branch8;

insert into stafftable values(stafftype('S01','Captain','account',20000,'abc','24-apr-1993'));
insert into stafftable values(stafftype('S02','Thor','manager',30000,'pqr','14-jun-1993'));

insert into account_branchtable values('B01','manager','andheri',stafftabletype(stafftype('S01','Captain','account',20000,'abc','24-apr-1993')));
insert into account_branchtable values('B02','asst manager','sion',stafftabletype(stafftype('S02','Thor','manager',30000,'pqr','14-jun-1993')));

尝试将数据插入Stafftable时，错误显示为inconsistent datatypes: expected schema.ACCOUNT_BRANCHTABLETYPE got CHAR。
小提琴= https://dbfiddle.uk/zDdqEJdx。

oracle

来源：https://stackoverflow.com/questions/74487391/i-have-created-the-below-table-stafftable-but-when-im-trying-to-insert-its-sho

1条答案

按热度按时间

dbf7pr2w1#

您没有表account_branchtable（您可能也不需要它）。
您可能需要创建一个REF的集合：

create type account_branchtabletype as table of REF account_branchtype;

然后创建您的人员类型：

create type stafftype as object(
  staff_id      varchar2(20),
  name          varchar2(20),
  sal           number(20),
  other_details varchar2(20),
  emp8          account_branchtabletype,
  dob           date,
  member function getage return number
);

create or replace type body stafftype as member function getage return number
as
begin
return FLOOR(MONTHS_BETWEEN(sysdate,dob)/12);
end getage;
end;
/

然后创建表：

create table stafftable of stafftype (
  staff_id PRIMARY KEY
) nested table emp8 store as relaccount_branch8;

ALTER TABLE relaccount_branch8 ADD SCOPE FOR (COLUMN_VALUE) IS account_branch;

然后您可以插入具有巢状表格值的人员：

insert into stafftable (staff_id, name, sal, other_details, emp8, dob)
values(
  'S01',
  'Captain',
  20000,
  'abc',
  account_branchtabletype(
    (SELECT REF(b) FROM account_branch b WHERE b.account.no = '19DCS001' AND b.branch = 'Manjalpur')
  ),
  DATE '1993-04-24'
);

fiddle

赞(0）回复(0）举报 2022-11-28

我来回答

oracle 我已经创建了下面的表stafftable，但当我尝试插入时，它显示错误

1条答案

相关问题

热门标签

最新问答