Oracle SQL中如何向REF及SET OF REF类型属性插入值？

问题背景 #

已创建以下对象类型与表：

对象类型定义 #

create type tsportifs as object (
idsportif integer, 
nom varchar2(50), 
prenom varchar2(50),
sexe varchar2(50),
age integer
);
/

create type tsport as object (idsport integer, libelle VARCHAR2(50));
/

alter type tsportifs add attribute sportifs_sport t_set_ref_sport cascade;
alter type tsportifs add attribute sportifs_coneiller ref tsportifs cascade;

对象表定义 #

create table sportifs of tsportifs (
    primary key(idsportif),
    CONSTRAINT chk_sexe CHECK (sexe IN ('F', 'M'))
)nested table sportifs_sport store as table_sportifs_sport;

create table sport of tsport (primary key(idsport))nested table sport_sportifs store as table_sport_sportifs;

执行插入语句时触发ORA-00907: missing right parenthesis错误：

INSERT INTO Sportifs VALUES(1,'BOUTAHAR','Abderahim','M',30,REF(tsportifs(NULL)),REF(tsport(2)));

错误提示：

SQL> INSERT INTO Sportifs VALUES(1,'BOUTAHAR','Abderahim','M',30,REF(tsportifs(NULL)),REF(tsport(2)));
INSERT INTO Sportifs VALUES(1,'BOUTAHAR','Abderahim','M',30,REF(tsportifs(NULL)),REF(tsport(2)))
                                                                         *
ERROR at line 1:
ORA-00907: missing right parenthesis

同时存在嵌套表sportifs_sport多值插入的疑问。

错误原因与修正 #

1. REF插入逻辑错误 #

• REF()函数仅能引用对象表中已存在的行，不能直接将对象构造器（如tsportifs(NULL)）作为参数。若需空REF，直接写NULL即可；若要引用已有记录，需通过SELECT REF(表别名) FROM 表 WHERE 条件获取合法REF。
• 参数顺序错误：tsportifs最终属性顺序为idsportif, nom, prenom, sexe, age, sportifs_sport, sportifs_coneiller，原插入语句将单个REF类型的sportifs_coneiller放在了嵌套表类型sportifs_sport的位置，导致结构不匹配。

修正后的插入示例 #

插入空嵌套表与空教练REF

INSERT INTO Sportifs 
VALUES(1,'BOUTAHAR','Abderahim','M',30, t_set_ref_sport(), NULL);

t_set_ref_sport()用于构造空的嵌套表集合，NULL表示未指定关联教练。

引用已有sport记录的REF到嵌套表

先插入sport表基础数据：

INSERT INTO sport VALUES(2, 'Football');

再插入sportifs并关联该sport的REF：

INSERT INTO Sportifs 
VALUES(
    1,'BOUTAHAR','Abderahim','M',30,
    t_set_ref_sport((SELECT REF(sp) FROM sport sp WHERE sp.idsport=2)),
    NULL
);

嵌套表多值插入方法 #

方法1：插入主表时直接构造多REF集合 #

先插入多个sport记录：

INSERT INTO sport VALUES(2, 'Football');
INSERT INTO sport VALUES(3, 'Basketball');

插入sportifs时直接构造包含多个REF的嵌套表：

INSERT INTO Sportifs 
VALUES(
    2,'DUPONT','Marie','F',28,
    t_set_ref_sport(
        (SELECT REF(sp) FROM sport sp WHERE sp.idsport=2),
        (SELECT REF(sp) FROM sport sp WHERE sp.idsport=3)
    ),
    (SELECT REF(s) FROM sportifs s WHERE s.idsportif=1) -- 关联id=1的教练
);

方法2：主表插入后，单独向嵌套表追加数据 #

先插入主表记录（嵌套表为空）：

INSERT INTO Sportifs 
VALUES(3,'LEROY','Paul','M',35, t_set_ref_sport(), NULL);

通过TABLE()函数定位嵌套表，批量插入多个REF：

INSERT INTO TABLE(SELECT s.sportifs_sport FROM sportifs s WHERE s.idsportif=3)
SELECT REF(sp) FROM sport sp WHERE sp.idsport IN (2,3);

内容的提问来源于stack exchange，提问作者azou kr