我需要一些帮助来创建将使用引用的选择语句。
我设法很好地插入了值,但是当我尝试使用 where 语句提取值时,输出要么是数据类型错误,要么会输出两个表以及它们都包含的数据。
这只是一个例子:
Create or replace table1_Type as object {
id integer,
dateStart date,
etc varchar2(20));
}
/
create table table1 of table1_type;
Create or replace table2_type as object
id integer,
items varchar2(30),
datePurchased varchar2(20),
table1_Ref REF table1_type);
/
create table table2 of table2_type;
所以我尝试了
Select * from table2 a, table1 b where table1.id = table2.table1_ref
Select * from table2 a, table1 b where table1.id = deref(b.table1_ref)
这是行不通的。
我对此很陌生,如果我没有正确解释的话,我很抱歉。我想做的是选择由 table1/id 购买的商品,因此输出应该只显示
某个id购买的商品。
Create or replace type table1_Type as object (
id integer,
dateStart date,
etc varchar2(20));
-- TYPE TABLE1_TYPE compiled
create table table1 of table1_type;
-- table TABLE1 created.
Create or replace type table2_type as object(
id integer,
items varchar2(30),
datePurchased varchar2(20),
table1_Ref REF table1_type);
-- TYPE TABLE2_TYPE compiled
create table table2 of table2_type;
--table TABLE2 created.
INSERT INTO table1 VALUES(table1_Type(1, SYSDATE, 'etc1...'));
INSERT INTO table1 VALUES(table1_Type(2, SYSDATE, 'etc2...'));
SELECT REF(t)
FROM table1 t
WHERE id = 1;
-- [TST.TABLE1_TYPE]
DECLARE
l_table_1_id_1 REF table1_Type;
l_table_1_id_2 REF table1_Type;
BEGIN
SELECT REF(t)
INTO l_table_1_id_1
FROM table1 t
WHERE id = 1;
SELECT REF(t)
INTO l_table_1_id_2
FROM table1 t
WHERE id = 2;
INSERT INTO table2 VALUES (21, 'item21', SYSDATE, l_table_1_id_1);
INSERT INTO table2 VALUES (22, 'item22', SYSDATE, l_table_1_id_2);
END;
-- anonymous block completed
SELECT COUNT(*) FROM table1;
-- 2
SELECT COUNT(*) FROM table2;
-- 2
SELECT * FROM table1;
/*
1 2013-06-16 03:51:50 etc1...
2 2013-06-16 03:52:05 etc2...
*/
SELECT * FROM table2;
/*
21 item21 2013-06-16 04:06:26 [TST.TABLE1_TYPE]
22 item22 2013-06-16 04:06:26 [TST.TABLE1_TYPE]
*/
SELECT *
FROM table1 t1
JOIN table2 t2
ON REF(t1) = t2.table1_Ref;
/*
1 2013-06-16 03:51:50 etc1... 21 item21 2013-06-16 04:06:26 [TST.TABLE1_TYPE]
2 2013-06-16 03:52:05 etc2... 22 item22 2013-06-16 04:06:26 [TST.TABLE1_TYPE]
*/
本文内容由网友自发贡献,版权归原作者所有,本站不承担相应法律责任。如您发现有涉嫌抄袭侵权的内容,请联系:hwhale#tublm.com(使用前将#替换为@)