我必须从Varray检索特定数据,例如
CREATE OR REPLACE TYPE class_t IS VARRAY(9) OF VARCHAR(5);/
create table exprement(name VARCHAR2(20), seat class_t);
insert into exprement(name,seat) values('JPZ',class_t('AC1','EC'));
insert into exprement(name,seat) values('ABC',class_t('GEN','EC'));
insert into exprement(name,seat) values('DFG',class_t('AC1','EC'));
我想检索VARRAY包含AC1的名称我试过select * from exprement where seat='AC1';
您可以使用:
SELECT name
FROM experiment e
WHERE EXISTS (SELECT 1 FROM TABLE(e.seat) WHERE COLUMN_VALUE = 'AC1');
或
SELECT name
FROM experiment e
CROSS APPLY TABLE (e.seat) s
WHERE s.COLUMN_VALUE = 'AC1';
顺便说一句,如果将seat
定义为嵌套表(而不是VARRAY
),则可以使用MEMBER OF
运算符:
SELECT *
FROM experiment
WHERE 'AC1' MEMBER OF seat;
但这对VARRAY
或关联数组不起作用。
db<gt;小提琴这里