我有问题插入值到表的属性是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
和我不知道如何插入多个值到嵌套表
您的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)
)