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 it fails.
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;
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;
If you only want to check a few countries, you can write the list into the 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;
Another possibility would be to use the MULTISET instruction:
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;
For very large amounts of data, you may not want to use DISTINCT for the MULTISET INTERSECT because this can lead to very long response time.
Alternatively, you may define the list of the country codes as a collection, and check with the operator 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;
/
It is Inside