如何测试字符串是否在预定义字符串列表中？

Question

我定义了一个字符串列表，其中包含不同的国家/地区代码（例如 USA,CHINA,HK,JPN 等）。

如何检查输入变量是否是列表中的国家/地区代码？

我使用以下代码进行测试，但失败。

declare
 country_list  CONSTANT VARCHAR2(200) := USA,CHINA,HK,JPN;
 input VARCHAR2(200);
begin
 input  := 'JPN';
 IF input   IN  (country_list)
         DBMS_OUTPUT.PUT_LINE('It is Inside');
    else       
         DBMS_OUTPUT.PUT_LINE('It is not  Inside');
 END IF;
end;

Answer 1

如果你能保证输入的内容不包含分隔符，你可以这样做：

country_list := 'USA,CHINA,HK,JPN';

input := 'JPN'; -- will be found
IF INSTR(',' || country_list || ','
        ,',' || input || ',') > 0 THEN
   --found
ELSE
   --not found
END IF;

input := 'HINA'; --will not be found
IF INSTR(',' || country_list || ','
        ,',' || input || ',') > 0 THEN
   --found
ELSE
   --not found
END IF;

Answer 2

如果您只想检查几个国家，您可以将列表写入 if 语句：

set serveroutput on;

declare 
    input varchar2(10) := 'JPN';
begin
    if input in ('USA', 'CHINA', 'HK' , 'JPN') then
        DBMS_OUTPUT.PUT_LINE('It is Inside');
    else
        DBMS_OUTPUT.PUT_LINE('It is not Inside');
    end if;
end;

如果您想检查大量值中是否存在特定值，您可能需要使用表格：

set serveroutput on;

declare 
    type country_list is table of varchar2(10);
    countries country_list := null;
    input varchar2(10) := 'JPN';
    inside boolean := false;

begin
    countries := country_list('USA', 'CHINA', 'HK' , 'JPN');
    for i in countries.first .. countries.last loop
        if input = countries(i) then
            inside := true;
            exit;
        end if;
    end loop;

    if inside then
        DBMS_OUTPUT.PUT_LINE('It is Inside');
    else
        DBMS_OUTPUT.PUT_LINE('It is not Inside');
    end if;

end;

另一种可能性是使用 MULTISET 指令：

set serveroutput on;

declare 
    type table_varchar IS TABLE OF VARCHAR2(5);
    countries table_varchar := null;
    country_input table_varchar;
    countries_intersect table_varchar;

begin
    country_input := table_varchar('JPN');
    -- Simulate a query which delivers a lot of countries
    select c.country bulk collect into countries from (
        select 'USA' as country from dual union all
        select 'CHINA' as country from dual union all
        select 'HK' as country from dual union all
        select 'JPN' as country from dual
    ) c;
    countries_intersect := country_input MULTISET INTERSECT DISTINCT countries;
    if countries_intersect.count >= 1 then
        DBMS_OUTPUT.PUT_LINE('It is Inside');
    else
        DBMS_OUTPUT.PUT_LINE('It is not Inside');
    end if;

end;

对于非常大量的数据，您可能不希望对 MULTISET INTERSECT 使用 DISTINCT，因为这可能会导致响应时间很长。

Answer 3

或者，您可以将国家/地区代码列表定义为集合，并使用运算符 MEMBER OF 进行检查：

declare
    type tokenList is table of varchar2 (8);  
    countrylist constant tokenList := tokenList ('USA','CHINA','HK','JPN');
    input varchar2 (8) := 'JPN';
begin
    if (input member of countrylist) then
         dbms_output.put_line ('It is Inside');
    else       
         dbms_output.put_line ('It is not  Inside');
    end if;
end;
/

在里面

---