mirall | Динамически настраиваемые запросы из приложения (Reply)

    Задача: имеется некий запрос с динамически настраиваемым условием where. Параметры передаются с веб-интерфейса, при этом если параметр пуст, т.е. is null, то он не должен включаться в условие where. Дополнительное условие — постраничный вывод данных, но это мы уже проходили.
    Подход я придумала сама, давно уже. А выношу этот вопрос на публику сейчас, потому что сегодня наткнулась на статью приснопамятного Тома Кайта как раз на эту тему. Мой личный подход концептуально не отличается, но кажется более сложным, у Кайта как-то полегче. Но переделывать, наверное, уже не буду — слишком много в приложении на него завязано. Хотя кое-что можно попробовать поковырять с целью повышения производительности.

    Общий подход: построить динамический запрос таким образом, чтобы количество переменных привязки было постоянным. Потом execute immediate — и voilà — всё работает.


create or replace procedure GetEmployeeList (
    o_cur out sys_refcursor
  , i_sEmployeeFirstName in employee.first_name%type
  , i_sEmployeeLastName in employee.last_name%type
  , i_sEmployeeEmail in employee.email%type
  , i_sStatus in record_status.status_name%type
  , i_nStartRecNum in number
  , i_nRecQuantity in number
  , i_sOrderBy in varchar2
  ) as
    sQuery varchar2(32000);
    sWhere varchar2(32000) := null;
    sOrderBy varchar2(100 CHAR);
    sQueryFinish varchar2(100 CHAR);

    recErrorCode error_handling.tOuterError;

    -- Created:  2009-03-18
    -- Return:  output cursor (employeeID, firstName, lastName, email, status,
    --                         created, updated, rowNumber)
    procedure GetStubEmployeeList (
      o_cur out sys_refcursor
    , i_recErrorCode in error_handling.tOuterError
    ) is
    begin
      open o_cur for
        select i_recErrorCode.nReturnCode employeeID
             , null firstName
             , null lastName
             , null email
             , i_recErrorCode.sReturnMessage status
             , null created
             , null updated
             , 0 rowNumber
          from dual;
    end GetStubEmployeeList;

  begin
    sQuery := 'select employeeID, firstName, lastName, email, status, '||CHR(10)||
              '       created, updated, rowNumber '||CHR(10)||
              '   from ( '||CHR(10)||
              '     select /*+ FIRST_ROWS(n) */ esi.*, rownum rn '||CHR(10)||
              '       from ( '||CHR(10)||
              '         select e.employee_id employeeID '||CHR(10)||
              '           , e.first_name  firstName '||CHR(10)||
              '           , e.last_name   lastName '||CHR(10)||
              '           , e.email       email '||CHR(10)||
              '           , s.status_name status '||CHR(10)||
              '           , e.creation_dt created '||CHR(10)||
              '           , e.last_update_dt updated '||CHR(10)||
              '           , count(1) over () rowNumber '||CHR(10)||
              '           , rownum rowsQuantity '||CHR(10)||
              '        from employee e '||CHR(10)||
              '        join employee_status s on s.status_id = e.status '||CHR(10)||
              '        join employee c on c.employee_id = e.creator_id '||CHR(10);

    sQueryFinish := '    ) esi where rownum <= :rec_max '||CHR(10)||
                    '  ) where rn >= :rec_first';

    -- where clause for employee first name
    sWhere := QUERY_MANAGE.AddWhereClause (
        i_sVar => i_sEmployeeFirstName
      , i_sVarName => 'firstName'
      , i_sWhere => sWhere
      );

    -- where clause for employee last name
    sWhere := QUERY_MANAGE.AddWhereClause (
        i_sVar => i_sEmployeeLastName
      , i_sVarName => 'lastName'
      , i_sWhere => sWhere
      );

    -- where clause for employee email
    sWhere := QUERY_MANAGE.AddWhereClause (
        i_sVar => i_sEmployeeEmail
      , i_sVarName => 'email'
      , i_sWhere => sWhere
      );

    -- where clause for status name
    sWhere := QUERY_MANAGE.AddWhereClause (
        i_sVar => i_sStatus
      , i_sVarName => 'status'
      , i_sWhere => sWhere
      );

    -- order by clause
    sOrderBy := query_manage.CreateOrderByClause( i_sOrderBy );

    sQuery := sQuery || sWhere || sOrderBy || sQueryFinish;
    --dbms_output.put_line (sQuery);

    -- return values
    open o_cur for sQuery
      using nvl(i_sEmployeeFirstName, QUERY_MANAGE.S_NOT_DEFINED)
          , nvl(i_sEmployeeLastName, QUERY_MANAGE.S_NOT_DEFINED)
          , nvl(i_sEmployeeEmail, QUERY_MANAGE.S_NOT_DEFINED)
          , nvl(i_sStatus, QUERY_MANAGE.S_NOT_DEFINED)
          , i_nStartRecNum + i_nRecQuantity - 1
          , i_nStartRecNum;

  exception
    when others then
      recErrorCode := error_handling.CatchError (
          i_nErrorCode => sqlcode
        , i_sErrorMessage => sqlerrm
        , i_sInputs => 'i_sEmployeeFirstName='||i_sEmployeeFirstName||CHR(10)||
                       'i_sEmployeeLastName='||i_sEmployeeLastName||CHR(10)||
                       'i_sEmployeeEmail='||i_sEmployeeEmail||CHR(10)||
                       'i_sStatus='||i_sStatus||CHR(10)||
                       'i_nStartRecNum='||i_nStartRecNum||CHR(10)||
                       'i_nRecQuantity='||i_nRecQuantity||CHR(10)||
                       'i_sOrderBy='||i_sOrderBy
        );
      -- return error message
      GetStubEmployeeList (
          o_cur => o_cur
        , i_recErrorCode => recErrorCode
      );
  end GetEmployeeList;

Обработка исключений не является предметом обсуждения в этом посте. Но для ясности — построена на базе советов Стива Фейерштайна и его же пакета обработки исключений.
Процедуры query_manage.AddWhereClause и query_manage.CreateOrderByClause написаны, чтобы избежать многочисленных повторов одного и того же кода. И не раз себя оправдали. Вообще, есть ещё процедура для поддержки группировок, но она более трудоёмка в использовании.


create or replace package QUERY_MANAGE is
  S_NOT_DEFINED varchar2(5 CHAR) := 'NDFD';
  N_NOT_DEFINED number(1) := 1;
  C_TS_NOT_DEFINED varchar2(10 CHAR) := '01-01-2000';
  TS_NOT_DEFINED date := to_date('01-01-2000','DD-MM-YYYY');
  DATE_FORMAT varchar2(21 CHAR) := 'DD-MM-YYYY HH24:MI:SS';

  function AddWhereClause (
    i_sVar in varchar2
  , i_sVarName in varchar2
  , i_sWhere in varchar2
  ) return varchar2;

  function AddWhereClause (
    i_nVar in number
  , i_sVarName in varchar2
  , i_sWhere in varchar2
  ) return varchar2;

  function AddWhereClause (
    i_nVarLower in number
  , i_nVarHigher in number
  , i_sVarName in varchar2
  , i_sWhere in varchar2
  ) return varchar2;

  function AddWhereClause (
    i_tsVarLower in timestamp
  , i_tsVarHigher in timestamp
  , i_sVarName in varchar2
  , i_sWhere in varchar2
  ) return varchar2;

  function CreateOrderByClause (
    i_sOrderBy in varchar2
  , i_sUniqueField in varchar2 default null
  ) return varchar2;

  procedure BindParameter (
    i_nCursorNumber in integer
  , i_sVarName in varchar2
  , i_nVarVal in number
  );

  procedure BindParameter (
    i_nCursorNumber in integer
  , i_sVarName in varchar2
  , i_sVarVal in varchar2
  );
end QUERY_MANAGE;

create or replace package body QUERY_MANAGE is
  function AddWhereClause (
    i_sVar in varchar2
  , i_sVarName in varchar2
  , i_sWhere in varchar2
  ) return varchar2 as
    sValCondition varchar2(200 CHAR);
    sWhere varchar2(1000 CHAR) := i_sWhere;
  begin
    if i_sVar is not null then
      sValCondition := i_sVarName || ' LIKE :i_sVar escape ''\'' ';
    else
      sValCondition := '''' || S_NOT_DEFINED || ''' = :i_sVar ';
    end if;
    select nvl2(sWhere, sWhere || 'and ' || sValCondition, 'where ' || sValCondition)
      into sWhere
      from dual;

    return sWhere;
  end AddWhereClause;

  function AddWhereClause (
    i_nVar in number
  , i_sVarName in varchar2
  , i_sWhere in varchar2
  ) return varchar2 as
    sValCondition varchar2(200 CHAR);
    sWhere varchar2(1000 CHAR) := i_sWhere;
  begin
    if i_nVar is not null then
      sValCondition := i_sVarName || ' = :i_sVar ';
    else
      sValCondition := N_NOT_DEFINED || ' = :i_sVar ';
    end if;
    select nvl2(sWhere, sWhere || 'and ' || sValCondition, 'where ' || sValCondition)
      into sWhere
      from dual;

    return sWhere;
  end AddWhereClause;

  function AddWhereClause (
    i_nVarLower in number
  , i_nVarHigher in number
  , i_sVarName in varchar2
  , i_sWhere in varchar2
  ) return varchar2 as
    sValCondition varchar2(200 CHAR);
    sWhere varchar2(1000 CHAR) := i_sWhere;
  begin
    if i_nVarLower is not null then
      sValCondition := i_sVarName || ' between :i_nVarLower and :i_nVarHigher ';
    else
      sValCondition := N_NOT_DEFINED || ' between :i_nVarLower and :i_nVarHigher ';
    end if;
    select nvl2(sWhere, sWhere || 'and ' || sValCondition, 'where ' || sValCondition)
      into sWhere
      from dual;

    return sWhere;
  end AddWhereClause;

  function AddWhereClause (
    i_tsVarLower in timestamp
  , i_tsVarHigher in timestamp
  , i_sVarName in varchar2
  , i_sWhere in varchar2
  ) return varchar2 as
    sValCondition varchar2(200 CHAR);
    sWhere varchar2(1000 CHAR) := i_sWhere;
  begin
    if i_tsVarLower is not null then
      sValCondition := i_sVarName || ' between :i_tsVarLower and :i_tsVarHigher ';
    else
      sValCondition := 'to_date(''' || C_TS_NOT_DEFINED ||''', '''|| DATE_FORMAT || ''')' || ' between :i_tsVarLower and :i_tsVarHigher ';
    end if;
    select nvl2(sWhere, sWhere || 'and ' || sValCondition, 'where ' || sValCondition)
      into sWhere
      from dual;

    return sWhere;
  end AddWhereClause;

  function CreateOrderByClause (
    i_sOrderBy in varchar2
  , i_sUniqueField in varchar2 default null
  ) return varchar2 as
    sOrderBy varchar2(200 CHAR);
    nValid number(1);
  begin
    -- check if Order by clause contains only string like '1 asc,2 desc,3'
    if i_sOrderBy is not null then
      select 1 into nValid
        from dual
        where regexp_like(lower(i_sOrderBy), '^([0-9a-z] *(asc|desc)?,)*([0-9a-z]*( (asc|desc))?)?$');
    end if;
    -- form order by clause
    select nvl2( i_sOrderBy,
                 'order by ' || i_sOrderBy || nvl2( i_sUniqueField, ', ' || i_sUniqueField, i_sUniqueField ),
                 i_sOrderBy )
      into sOrderBy
      from dual;

    return sOrderBy;
  exception
    when no_data_found then
      error_handling.RaiseError( 'INVALID_ORDER_BY_CLAUSE' );
  end CreateOrderByClause;
end QUERY_MANAGE;

    Я использовала отдельные константы для каждого типа данных и перегрузила процедуры работы с числами и датами, чтобы можно было выбирать между равенством и between. Подход Кайта проще в том смысле, что хардкодится только выражение 1=1, и константы, которые, как ни крути, есть тоже хардкод, но более "хард", чем 1=1, оказываются ненужными.
    Прекрасно в этой связке то, что собрать любую процедуру выборки данных для веб-интерфейса становится делом чисто техническим, почти на уровне copy-paste. Соответственно, быстро и легко. Думать нужно только над основой запроса и не париться каждый раз с условиями where.
    Остаётся открытым только один вопрос: что делать, если нужно выбрать непосредственно пустые значения, т.е. если в запросе должно присутствовать where column1 is null. И вот эту проблему я до сих пор не решила. Правда, в настоящее время проблема скорее теоретическая, потому что пока требования подобного не выдвигалось.

Динамически настраиваемые запросы из приложения

Post a comment in response: