Cómo encontrar si existe un valor dentro de un VARRAY

He creado un VARRAY dentro de una tabla (a continuación) Me gustaría consultar si un título tiene o no un tema en particular, ej. Mostrar juegos de 'Acción'. No estoy seguro de cómo hacerlo …

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')))) / 

Necesita exponer la tabla anidada en la cláusula FROM utilizando la function table() . A continuación, puede hacer reference a los attributes de la colección:

 SQL> select g.title 2 from game_table g 3 , table(g.gametheme) gt 4 where gt.theme = 'Action'; TITLE -------------------------------------------------- Star Wars SQL> 

"¿Qué pasa si luego necesito recuperar filas con múltiples Temas, es decir, Acción, FPS?"

Disculpas por la solución torpe pero tengo que ir a trabajar ahora. Posiblemente publique una solución más elegante más adelante.

 SQL> select * from game_table 2 / TITLE -------------------------------------------------- GAMETHEME(THEME) -------------------------------------------------------------------------------- Star Wars THEME_TYPE(THEME_GAME('Action'), THEME_GAME('FPS')) Uncharted 3 THEME_TYPE(THEME_GAME('Action'), THEME_GAME('Puzzle')) Commander Cody THEME_TYPE(THEME_GAME('Fun'), THEME_GAME('Puzzle')) SQL> select g.title 2 from game_table g 3 , table(g.gametheme) gt 4 , table(g.gametheme) gt1 5 where gt.theme = 'Action' 6 and gt1.theme = 'FPS' ; TITLE -------------------------------------------------- Star Wars SQL> 

Este enfoque alternativo no funcionará con su tipo actual porque VARRAY no es compatible con member of . Pero funcionaría si la colección fuera una tabla anidada.

  select g.title from game_table g where 'Action' member of g.gametheme and 'FPS' member of g.gametheme 

Para múltiples temas, podrías hacer algo como

 select g.Title from game_table g, table(g.gameTheme) t where t.Theme in ('FPS','Action') group by g.Title having count(0) = 2; 

Esto también podría permitirle hacer cosas como get títulos con exactamente n partidos, al less n partidos, en la mayoría de n partidos …

Puede usar una colección y luego comparar varios elementos usando el operador SUBMULTISET :

SQL Fiddle

Configuración del esquema Oracle 11g R2 :

 CREATE OR REPLACE TYPE Theme_Game AS OBJECT (Theme VARCHAR(20)); / CREATE OR REPLACE TYPE Theme_Type AS TABLE OF Theme_Game; / CREATE OR REPLACE TYPE Game_Type AS OBJECT( Title VARCHAR2(50), GameTheme Theme_Type ); / CREATE TABLE Game_Table of Game_Type NESTED TABLE GameTheme STORE AS GameTheme_Tab / INSERT INTO Game_Table VALUES('Star Wars' ,(Theme_Type(Theme_Game('Action'), Theme_Game('FPS')))) / 

Consulta 1 :

 SELECT * FROM game_table WHERE Theme_Type(Theme_Game('Action'), Theme_Game('FPS')) SUBMULTISET OF GameTheme 

Resultados :

 | TITLE | GAMETHEME | |-----------|-------------------------------------------------------| | Star Wars | oracle.sql.STRUCT@67e8dc0f,oracle.sql.STRUCT@795b6d4c | 

Sin embargo, ¿por qué está utilizando el object Theme_Game cuando solo tiene un único atributo VARCHAR2 ? Solo puede usar un VARRAY(3) OF VARCHAR2(20) o una TABLE OF VARCHAR2(20) sin el object intermedio:

SQL Fiddle

Configuración del esquema Oracle 11g R2 :

 CREATE OR REPLACE TYPE Varchar20List AS TABLE OF VARCHAR2(20); / CREATE OR REPLACE TYPE Game_Type AS OBJECT( Title VARCHAR2(50), GameTheme Varchar20List ); / CREATE TABLE Game_Table of Game_Type NESTED TABLE GameTheme STORE AS GameTheme_Tab / INSERT INTO Game_Table VALUES('Star Wars' , Varchar20List('Action', 'FPS')) / 

Consulta 1 :

 SELECT * FROM game_table WHERE Varchar20List('Action','FPS') SUBMULTISET OF GameTheme 

Resultados :

 | TITLE | GAMETHEME | |-----------|------------| | Star Wars | Action,FPS | 

Si quieres hacerlo con VARRAY s, entonces:

SQL Fiddle

Configuración del esquema Oracle 11g R2 :

 CREATE OR REPLACE TYPE Varchar20List AS VARRAY(3) OF VARCHAR2(20); / CREATE OR REPLACE TYPE Game_Type AS OBJECT( Title VARCHAR2(50), GameTheme Varchar20List ); / CREATE TABLE Game_Table of Game_Type / INSERT INTO Game_Table VALUES('Star Wars' , Varchar20List('Action', 'FPS')) / 

Consulta 1 :

 SELECT * FROM game_table g WHERE 2 >= ( SELECT COUNT(*) FROM TABLE( g.GameTheme ) a INNER JOIN TABLE( Varchar20List( 'Action', 'FPS' ) ) b ON ( a.COLUMN_VALUE = b.COLUMN_VALUE ) ) 

Resultados :

 | TITLE | GAMETHEME | |-----------|------------| | Star Wars | Action,FPS | 

o:

Consulta 2 :

 SELECT * FROM game_table g WHERE 2 >= ( SELECT COUNT(*) FROM TABLE( g.GameTheme ) a WHERE a.COLUMN_VALUE IN ( 'Action', 'FPS' ) ) 

Resultados :

 | TITLE | GAMETHEME | |-----------|------------| | Star Wars | Action,FPS |