SteelProgramming | +38 098 888 58 89 Alexanders.berezovich Skypesteelprogram@gmail.com E-mail |
Oracle select from string (varchar2)If you receive string with list of ID separated in some way as input, and you should use this string as filer, you have few ways do this. 1. Dynamic SQLdeclare ids varchar2(100) := '1,2,3,4,5'; l_cnt number; begin execute immediate 'select count(*) from account where account_id in (' || ids || ')' into l_cnt; dbms_output.put_line(l_cnt); end; Minuses - dynamic SQL should use when you can't do your task without dynamic sql, too large performance impact in bad side. Dynamic SQL is way to the Dark side. 2. Check is ID in stringdeclare ids varchar2(100) := '1,2,3,4,5'; l_cnt number; begin select count(*) into l_cnt from trader_account where instr(ids || ',', account_id || ',') != 0; dbms_output.put_line(l_cnt); end; Minuses - not used index by table. In his situation will be fast index scan (INDEX FAST FULL SCAN) because all data needed for this select already in index, so Oracle will use index instead of table, but in another select where not all data will be in index, Oracle will use full table scan TABLE ACCESS FULL 3. Transform string into datasourcedeclare ids varchar2(100) := '1,2,3,4,5'; l_cnt number; begin select count(*) into l_cnt from account where account_id in (select TO_NUMBER(trim(SUBSTR(t1.str, t1.curr_pos + 1, DECODE(t1.next_pos, 0, LENGTH(t1.str) + 2, t1.next_pos) - t1.curr_pos - 1))) from (select ids str, DECODE(level, 1, 0, INSTR(ids, ',', 1, level - 1) ) as curr_pos, INSTR(ids, ',', 1, level) as next_pos from dual connect by level <= LENGTH(ids) - LENGTH( replace(ids, ',', '') ) + 1) t1); dbms_output.put_line(l_cnt); end; Pluses - index will be used Minuses - max length of string (SQL 4000, PL/SQL 32768) 4. Transform array of strings into datasource.declare ids1 varchar2(100) := '1,2,3,4,5'; ids2 varchar2(100) := '7,8,9,10,11,12'; l_cnt number; begin with tmp_str as (select 1 as id, ids1 as ids from dual union select 2 as id, ids2 as ids from dual), tmp_str2 as (select t.*, length(ids) - LENGTH(replace(ids, ',', '')) + 1 as cnt from tmp_str t), tmp_dual as (select level as lv from dual connect by level <= (select max(cnt) as total_cnt from tmp_str2)), tmp_str3 as (select * from (select id, lv, ids, cnt, row_number() over(partition by id order by lv) as rn from tmp_dual, tmp_str2) t1 where rn <= cnt) select count(*) into l_cnt from account where account_id in (select TO_NUMBER(trim(SUBSTR(t1.str, t1.curr_pos + 1, DECODE(t1.next_pos, 0, LENGTH(t1.str) + 2, t1.next_pos) - t1.curr_pos - 1))) from (select ids str, DECODE(lv, 1, 0, INSTR(ids, ',', 1, lv - 1)) as curr_pos, INSTR(ids, ',', 1, lv) as next_pos from tmp_str3) t1); dbms_output.put_line(l_cnt); end; When you have more than one string for filtering you can use last way. Algorithm can be easy modified for your datasource (pl/sql table, array...) You can add comment on current page, or on forum page there. Last comment | |
powered by Steel Programming |