创建stafftable表后插入数据报错：数据类型不一致问题求助

错误根源 #

1. 构造函数参数顺序混乱：stafftype的字段定义顺序为staff_id, name, sal, other_details, emp8, dob，但原插入语句完全打乱了参数顺序，导致数值类型字段（如sal）被传入字符串，字符串类型字段被传入数值，同时emp8字段（account_branchtabletype类型）被错误传入字符串值，直接触发类型不匹配错误。
2. 无效表与类型引用：后续插入account_branchtable的语句中，该表未创建，且stafftabletype类型不存在，属于非法语句。

修正后的完整代码 #

1. 保留原有类型定义（无修改） #

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;

2. 修正stafftable插入语句 #

调整参数顺序，为emp8字段传入合法的account_branchtabletype实例（示例传入空表，若有实际数据可构造account_branchtype对象填充）：

insert into stafftable values(
    stafftype('S01','Captain',20000,'account',account_branchtabletype(),'24-apr-1993')
); 

insert into stafftable values(
    stafftype('S02','Thor',30000,'manager',account_branchtabletype(),'14-jun-1993')
);

3. 修正account_branchtable相关逻辑（若需创建该表） #

若需要存储account_branchtype数据，需先创建对应表，并使用合法的REF引用accounttype实例：

-- 先创建存储accounttype的表并插入数据
create table accounttable of accounttype;
insert into accounttable values('A01','Alice',5000,'20-apr-1990');
insert into accounttable values('A02','Bob',8000,'10-jun-1988');

-- 创建account_branchtable表并插入数据
create table account_branchtable of account_branchtype;
insert into account_branchtable values(
    (select ref(a) from accounttable a where a.no='A01'), 'Andheri'
);
insert into account_branchtable values(
    (select ref(a) from accounttable a where a.no='A02'), 'Sion'
);

验证结果 #

执行修正后的代码后，类型不匹配错误将被消除，所有插入语句可正常执行。

内容的提问来源于stack exchange，提问作者SADIQ SONALKAR