Регистрация Забыли пароль?
Логин
Пароль
Запомнить меня
Вход через ВКонтакте
Главная > Статьи > Oracle выборка из строки (varchar2)

Oracle выборка из строки (varchar2)

Если вам на вход подают строку со списком ID через разделитель, и вы должны использовать ету строку как фильтр то у вас есть несколько способов сделать ето

1. Динамический SQL


declare

  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;

Минусы - динамический SQL нужно использовать толко там где без него никак не обойтись, очень большие накладные расходы идут на динамический SQL, также уменьшаеться читабельность и нельзя виявить ошибку во время компиляции

2. Проверять вхожление ID в строку


declare

  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;

Минусы - не используються индексы в таблице. В данном случае будет проходить полное сканирование индекса (INDEX FAST FULL SCAN) так как все данные необходимые для запроса уже есть в индексе в в противном случае будет TABLE ACCESS FULL

3. Развернуть строку в набор данных


declare

    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;

Плюсы - используються индексы по полю

Минусы - ограничение на длину строки (SQL 4000, PL/SQL 32768)

4. Развернуть набор строк в набор данных.


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;

В случае если у вас больше чем одна строка для фильтрации, вы можете использовать последний вриант. Алгоритм можно легко модифицировать под ваш набор данных (таблицу pl/sql, массив...)

Вы можете добавить коментарий на етой странице или на странице форума тут.Постов 5.

Последний комментарий

13.07.2016
Thanks for coiguibrtnnt. It's helped me understand the issues. http://gpqxnfbo.com [url=http://hlvjfauiywv.com]hlvjfauiywv[/url] [link=http://vtuexb.com]vtuexb[/link]
11.07.2016
Ah yes, nicely put, eveoyrne.

Имя*E-mail
Код*