如何通过 Oracle PL/SQL 测试字符串是否在预定义字符串列表中
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/2742174/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
How to test if a string is inside a list of predefined strings via Oracle PL/SQL
提问by drupalspring
I defined a list of strings, which contains different country codes (like USA ,CHINA,HK,JPN,etc.). How can I check, if an input variable is the country code in the list. I use the following code to test,but fails.
我定义了一个字符串列表，其中包含不同的国家/地区代码（例如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;
回答by Jeffrey Kemp
If you can guarantee that the input will not include the delimiter, you can do this:
如果你能保证输入不包含分隔符，你可以这样做：
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;
回答by Roman Vogt
If you only want to check a few countries, you can write the list into the if-Statement:
如果只想勾选几个国家，可以将列表写入if-Statement：
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;
If you want to check the existence of a specific value within a larger number of values, you maybe want to use a table:
如果要检查大量值中是否存在特定值，您可能需要使用表格：
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;
