I've created a VARRAY within a table (below) I would like to query whether or not a Title has a particular theme, eg. Show 'Action' games. I'm not to sure how to go about this...
CREATE OR REPLACE TYPE Theme_Game AS OBJECT
(Theme VARCHAR(20));
/
CREATE OR REPLACE TYPE Theme_Type AS VARRAY(3) OF Theme_Game;
/
CREATE OR REPLACE TYPE Game_Type AS OBJECT
(Title VARCHAR2(50),
GameTheme Theme_Type);
/
CREATE TABLE Game_Table of Game_Type
/
INSERT INTO Game_Table
VALUES('Star Wars' ,(Theme_Type(Theme_Game('Action'), Theme_Game('FPS'))))
/
For multiple themes you could do something like
This could also allow you to do things like get titles with exactly n matches, at least n matches, at most n matches...
You can use a collection and then compare multiple items using the
SUBMULTISET
operator:SQL Fiddle
Oracle 11g R2 Schema Setup:
Query 1:
Results:
However, why are you using the
Theme_Game
object when it only has a singleVARCHAR2
attribute? You can just use aVARRAY(3) OF VARCHAR2(20)
orTABLE OF VARCHAR2(20)
without the intermediate object:SQL Fiddle
Oracle 11g R2 Schema Setup:
Query 1:
Results:
If you want to do it with
VARRAY
s then:SQL Fiddle
Oracle 11g R2 Schema Setup:
Query 1:
Results:
or:
Query 2:
Results:
You need to expose the nested table in the FROM clause using the
table()
function. You can then reference attributes of the collection:Apologies for the clunky solution but I need to go to work now. I may post a more elegant solution later.
This alternative approach won't work with your current type because VARRAY does not support
member of
. But it would work if the collection was a Nested Table.