如何在SQL ORACLE的REF和REF的SET中插入值

Question

我有问题插入值到表的属性是REF和SET的REF of TYPES在SQL

我有sports类型:

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;

当我试图插入:

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

和我不知道如何插入多个值到嵌套表

Answer 1

您的DDL语句没有意义:

• 你从不定义t_set_ref_sport;和
• sport表定义了一个嵌套表，但类型不包含任何集合。

此外，INSERT语句没有意义(忽略它在语法上无效)，因为您没有将任何行INSERT写入sport以供其引用。

如果你有类型:

CREATE TYPE tsport AS OBJECT(
idsport integer,
libelle VARCHAR2(50)
);
CREATE TYPE t_set_ref_sport IS TABLE OF REF TSPORT;
CREATE TYPE tsportifs AS OBJECT (
idsportif          integer, 
nom                varchar2(50), 
prenom             varchar2(50),
sexe               varchar2(50),
age                integer,
sportifs_coneiller REF tsportifs,
sportifs_sport     t_set_ref_sport
);

then the tables:

create table sport of tsport (
primary key(idsport)
);
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;

然后你可以给引用添加范围约束:

ALTER TABLE sportifs ADD SCOPE FOR ( sportifs_coneiller ) IS sportifs;
ALTER TABLE table_sportifs_sport ADD SCOPE FOR ( COLUMN_VALUE ) IS sport;

并插入数据:

INSERT INTO sport (idsport, libelle) VALUES (2, 'Test');
INSERT INTO Sportifs (
idsportif,
nom,
prenom,
sexe,
age,
sportifs_coneiller,
sportifs_sport
) VALUES (
1,
'BOUTAHAR',
'Abderahim',
'M',
30,
NULL,
t_set_ref_sport(
(SELECT REF(s) FROM sport s WHERE idsport = 2)
)
);

小提琴

如果你想要NULL引用，那么只需插入NULL。

如果你想插入一个行引用，那么使用:

( SELECT REF(table_alias)
FROM table_name table_alias
WHERE primary_key_column = something )

如果要在嵌套表中插入引用集合，则使用:

collection_data_type(
(SELECT REF(table_alias) FROM table_name table_alias WHERE primary_key_column = first_thing),
(SELECT REF(table_alias) FROM table_name table_alias WHERE primary_key_column = third_thing),
NULL,
(SELECT REF(table_alias) FROM table_name table_alias WHERE primary_key_column = fourth_thing)
)