如何查找值是否存在于 VARRAY 中 [英] How to find if a value exists within a VARRAY

问题描述

我在表中创建了一个 VARRAY(如下)我想查询标题是否具有特定主题，例如.显示动作"游戏.我不知道该怎么做...

CREATE OR REPLACE TYPE Theme_Game AS OBJECT(主题 VARCHAR(20));/CREATE OR REPLACE TYPE Theme_Type AS VARRAY(3) OF Theme_Game;/CREATE OR REPLACE TYPE Game_Type AS OBJECT(标题 VARCHAR2(50),GameTheme Theme_Type);/CREATE TABLE Game_Table of Game_Type/插入游戏表VALUES('星球大战' ,(Theme_Type(Theme_Game('Action'), Theme_Game('FPS'))))/

解决方案

您需要使用 table() 函数在 FROM 子句中公开嵌套表.然后您可以引用集合的属性:

SQL>选择 g.title2 来自 game_table g3、表(g.gametheme)gt4 where gt.theme = 'Action';标题--------------------------------------------------星球大战SQL>

<小时><块引用>

如果我需要检索具有多个主题的行，即动作、FPS 怎么办?"

对于笨拙的解决方案深表歉意，但我现在需要去上班.稍后我可能会发布更优雅的解决方案.

SQL>从游戏表中选择 *2/标题--------------------------------------------------游戏主题(主题)--------------------------------------------------------------------------------星球大战THEME_TYPE(THEME_GAME('Action'), THEME_GAME('FPS'))神秘海域3THEME_TYPE(THEME_GAME('Action'), THEME_GAME('Puzzle'))科迪指挥官THEME_TYPE(THEME_GAME('Fun'), THEME_GAME('拼图'))SQL>选择 g.title2 来自 game_table g3、表(g.gametheme)gt4、表(g.gametheme)gt15 where gt.theme = 'Action'6 和 gt1.theme = 'FPS' ;标题--------------------------------------------------星球大战SQL>

<小时>

这种替代方法不适用于您当前的类型，因为 VARRAY 不支持 member of.但如果集合是嵌套表，它会起作用.

 选择 g.title来自 game_table g其中 g.gametheme 的Action"成员和 g.gametheme 的FPS"成员

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

解决方案

You need to expose the nested table in the FROM clause using the table() function. You can then reference attributes of the collection:

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

TITLE
--------------------------------------------------
Star Wars

SQL> 

---

"what if I then needed to retrieve rows with multiple Themes i.e Action, FPS?"

Apologies for the clunky solution but I need to go to work now. I may post a more elegant solution later.

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> 

---

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.

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

这篇关于如何查找值是否存在于 VARRAY 中的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！