Вопрос по plsql, oracle11g, oracle – Как использовать параметры в предложении «где значение в…»?

1

Это работает, когда у меня есть только один код состояния в качестве параметра.

Как я могу заставить код работать, когда у меня есть более одного state_code в parm_list?

Требования:

(1) Я не хочу жестко кодировать коды состояний в моем определении курсора

(2) Я хочу разрешить использование более чем одного кода состояния в моем предложении where

Например: я хочу запустить этот код дляparm_list = ('NY','NJ','NC'). I'm encountering difficulties in reconciling single quotes in parm_list with the single quotes in the 'where state_code in ' query.

set serveroutput on;

DECLARE
parm_list varchar2(40);

cursor get_state_codes(in_state_codes varchar2)
is
select state_name, state_code from states
where state_code in (in_state_codes);

BEGIN
 parm_list := 'NY';
 for get_record in get_state_codes(parm_list) loop
  dbms_output.put_line(get_record.state_name || get_record.state_code);
 end loop;
END;
Вы можете увидеть мой ответ вstackoverflow.com/a/18100419/947356  Я надеюсь, что это поможет вам. Sergey

Ваш Ответ

2   ответа
11

кодирования. Однако проблема с динамическим SQL заключается в том, что вам приходится тщательно анализировать каждую отдельную версию запроса, которая не только потенциально облагает налогом ваш ЦП, но и потенциально может заполнить ваш общий пул множеством неразборчивых операторов SQL, подталкивая Выражения out, которые вы хотели бы кэшировать, вызывая более жесткие разборы и ошибки фрагментации общего пула. Если вы выполняете это один раз в день, это, вероятно, не является серьезной проблемой. Если сотни людей исполняют его тысячи раз в день, это, вероятно, серьезная проблема.

Пример подхода динамического SQL

SQL> ed
Wrote file afiedt.buf

  1  declare
  2    l_deptnos  varchar2(100) := '10,20';
  3    l_rc       sys_refcursor;
  4    l_dept_rec dept%rowtype;
  5  begin
  6    open l_rc for 'select * from dept where deptno in (' || l_deptnos || ')';
  7    loop
  8      fetch l_rc into l_dept_rec;
  9      exit when l_rc%notfound;
 10      dbms_output.put_line( l_dept_rec.dname );
 11    end loop;
 12    close l_rc;
 13* end;
SQL> /
ACCOUNTING
RESEARCH

PL/SQL procedure successfully completed.

Кроме того, вы можете использовать коллекцию. Преимущество этого состоит в том, что вы генерируете один разделяемый курсор, поэтому вам не нужно беспокоиться о сложном разборе или заполнении общего пула. Но это, вероятно, требует немного больше кода. Самый простой способ иметь дело с коллекциями

SQL> ed
Wrote file afiedt.buf

  1  declare
  2    l_deptnos  tbl_deptnos := tbl_deptnos(10,20);
  3  begin
  4    for i in (select *
  5                from dept
  6               where deptno in (select column_value
  7                                  from table(l_deptnos)))
  8    loop
  9      dbms_output.put_line( i.dname );
 10    end loop;
 11* end;
SQL> /
ACCOUNTING
RESEARCH

PL/SQL procedure successfully completed.

Если, с другой стороны, вам действительно нужно начать со списка значений, разделенных запятыми, то вам придется проанализировать эту строку в коллекции, прежде чем вы сможете ее использовать. Существуют различные способы синтаксического анализа строки с разделителями - мой личный фаворит - использовать регулярные выражения в иерархическом запросе, но вы наверняка могли бы написать и процедурный подход

SQL> ed
Wrote file afiedt.buf

  1  declare
  2    l_deptnos     tbl_deptnos;
  3    l_deptno_str  varchar2(100) := '10,20';
  4  begin
  5    select regexp_substr(l_deptno_str, '[^,]+', 1, LEVEL)
  6      bulk collect into l_deptnos
  7      from dual
  8   connect by level <= length(replace (l_deptno_str, ',', NULL));
  9    for i in (select *
 10                from dept
 11               where deptno in (select column_value
 12                                  from table(l_deptnos)))
 13    loop
 14      dbms_output.put_line( i.dname );
 15    end loop;
 16* end;
 17  /
ACCOUNTING
RESEARCH

PL/SQL procedure successfully completed.
7

SELECT uo.object_name
      ,uo.object_type
FROM   user_objects uo
WHERE  instr(',TABLE,VIEW,', ',' || uo.object_type || ',') > 0;

Хотя это выглядит некрасиво, оно работает хорошо и до тех пор, пока не будет использоваться индекс для тестируемого столбца (поскольку это препятствует использованию какого-либо индекса), производительность не сильно пострадает. Если тестируемый столбец является первичным ключом, определенно, это не должно использоваться.

Другой вариант:

SELECT uo.object_name
      ,uo.object_type
FROM   user_objects uo
WHERE  uo.object_type IN
       (SELECT regexp_substr('TABLE,VIEW', '[^,]+', 1, LEVEL)
        FROM   dual
        CONNECT BY regexp_substr('TABLE,VIEW', '[^,]+', 1, LEVEL) IS NOT NULL);

В этом случае список значений должен быть объединен в одну переменную varchar, разделенную запятыми (или что угодно.)

Похожие вопросы