如何查找 VARRAY 中是否存在值

Question

我在表格中创建了一个VARRAY（下图）我想查询标题是否具有特定主题，例如。显示"动作"游戏。我不确定该怎么做...

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

Answer 1

您需要

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

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

---

"如果我随后需要检索具有多个主题（即动作、FPS）的行怎么办？"

为笨拙的解决方案道歉，但我现在需要去上班。我稍后可能会发布一个更优雅的解决方案。

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> 

---

此替代方法不适用于当前类型，因为 VARRAY 不支持member of 。但是，如果集合是嵌套表，它将起作用。

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

Answer 2

对于多个主题，您可以执行以下操作

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;

这还可以让你做一些事情，比如获得正好有 n 个匹配项的标题，至少 n 个匹配项，最多 n 个匹配项......

Answer 3

可以使用集合，然后使用 SUBMULTISET 运算符比较多个项目：

SQL 小提琴

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

查询 1：

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

结果：

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

但是，当 Theme_Game 对象只有一个VARCHAR2属性时，为什么要使用它？您可以只使用没有中间对象的VARRAY(3) OF VARCHAR2(20)或TABLE OF VARCHAR2(20)：

SQL 小提琴

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

查询 1：

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

结果：

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

如果你想用VARRAY s来做到这一点，那么：

SQL 小提琴

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

查询 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 )
             )

结果：

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

或：

查询 2：

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

结果：

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