如何在Oracle动态列表中检查IN条件? [英] How do I check for a IN condition against a dynamic list in Oracle?

问题描述

更改标题以适合下面的代码.

changed the title to fit the code below.

我正在尝试从Oracle表中检索可接受值的列表，然后对另一个表执行SELECT，同时将一些字段与该列表进行比较.

I'm trying to retrieve a list of acceptable values from an Oracle table, then performing a SELECT against another while comparing some fields against said list.

我试图使用游标(如下所示)执行此操作，但这失败了.

I was trying to do this with cursors (like below), but this fails.

DECLARE
    TYPE gcur IS REF CURSOR;
    TYPE list_record IS TABLE OF my_table.my_field%TYPE;
    c_GENERIC gcur;
    c_LIST list_record;
BEGIN
    OPEN c_GENERIC FOR
    SELECT my_field FROM my_table
    WHERE some_field = some_value;

    FETCH c_GENERIC BULK COLLECT INTO c_LIST;

    -- try to check against list
    SELECT * FROM some_other_table
    WHERE some_critical_field IN c_LIST;

END

基本上，我想做的是将可接受的值列表缓存到一个变量中，因为稍后我将反复对其进行检查.

Basically, what I'm trying to do is to cache the acceptable values list into a variable, because I will be checking against it repeatedly later.

您如何在Oracle中执行此操作?

How do you perform this in Oracle?

推荐答案

我们可以使用集合来存储适合您目的的值，但是需要将它们声明为SQL类型:

We can use collections to store values to suit your purposes, but they need to be declared as SQL types:

create type list_record is table of varchar2(128)
/

这是因为我们不能在SQL语句中使用PL/SQL类型. las，这意味着我们不能使用%TYPE或%ROWTYPE，因为它们是PL/SQL关键字.

This is because we cannot use PL/SQL types in SQL statements. Alas this means we cannot use %TYPE or %ROWTYPE, because they are PL/SQL keywords.

您的过程将如下所示:

DECLARE
    c_LIST list_record;
BEGIN

    SELECT my_field 
    BULK COLLECT INTO c_LIST 
    FROM my_table
    WHERE some_field = some_value;

    -- try to check against list
    SELECT * FROM some_other_table
    WHERE some_critical_field IN ( select * from table (c_LIST);

END;    

---

"我看到您仍然必须执行 SELECT语句填充列表 用于IN子句."

"I see that you still had to perform a SELECT statement to populate the list for the IN clause."

如果值在表中，则没有其他方法可以将它们放入变量中:)

If the values are in a table there is no other way to get them into a variable :)

我在想 使用可显着提高性能 通过直接的半联接"

"I'm thinking that there's a significant performance gain using this over a direct semi-join"

不一定.如果只使用一次值，那么子查询无疑是更好的方法.但是，由于要在多个离散查询中使用相同的值，因此填充集合是更有效的方法.

Not necessarily. If you're only using the values once then the sub-query is certainly the better approach. But as you want to use the same values in a number of discrete queries then populating a collection is the more efficient approach.

在11g企业版中，我们可以选择使用结果集缓存.这是一种更好的解决方案，但并不适合所有表.

In 11g Enterprise Edition we have the option to use result set caching. This is a much better solution, but one which is not suited for all tables.

这篇关于如何在Oracle动态列表中检查IN条件?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！