Добро пожаловать в форум, Guest  >>   Войти | Регистрация | Поиск | Правила | В избранное | Подписаться
Все форумы / Oracle Новый топик    Ответить
 Прошу подсказать оптимальное решение  [new]
Титов Артем
Member

Откуда: МО
Сообщений: 297
Есть клиентское приложение, написанное на Delphi.

Данные в компонент cxDBTreeList выводятся посредством динамического запроса (процедура А) фактически из любой таблицы БД.
Колонки компонента, соответствующие полям выводимого датасета и связанные с другими таблицами через FK-ключи, динамически преобразуются в LookupComboBox-поля и содержат ID, выводят на экран уже конкретные данные посредством отдельной серверной процедуры (процедура В).
Таблица на 750 записей выводится на локальной машине за 6 сек.

Мне показалось, что это место слабое и его можно оптимизировать путем изменения процедуры А (процедура С и вместо процедуры В процедура D) и добавления в него полей, содержащих нужные значения связанных таблиц помимо ID этих значений. А поля с ID на клиенте можно просто скрыть в Трилисте и от использования процедуры В на клиенте можно будет отказаться.
В итоге запрос отрабатывал в PL/SQL Developer'e 24 секунды (клиента под него пока не адаптировал, смысла не вижу)

Подскажите плз варианты, что я сделал неправильно

Процедура А: Получение данных
PROCEDURE PRC_GET_TABLE_DATA(IN_TABLE_ID          IN NUMBER,
                               IN_LINKED_FIELD_NAME IN VARCHAR2,
                               IN_LINKED_FIELD_VAL  IN NUMBER,
                               OUT_TABLE_COMMENT    OUT VARCHAR2,
                               IN_ADD_FILTER_STR IN VARCHAR2,
                               RESULT            OUT SYS_REFCURSOR) AS
    TBL_NAME                  AVSDOK_ACCESSTO_DATA_TBL.DATA_TABLE_NAME%TYPE;
    SQL_TXT                   VARCHAR2(4000) := '';
    FROM_TEXT                 VARCHAR2(5000) := '';
    TBL_TYPE                  NUMBER := 0;
    IMG_IDX                   NUMBER := 0;
    PRIV_LEVEL                NUMBER := 0;
    IS_CFG_CHECK              NUMBER := 0;
    IS_SYSTEM_CHECK           NUMBER := 0;
    ORDER_FIELD_NAME          VARCHAR2(50) := 'ID';
    CHECK_FIELD_NAME_FOR_NULL VARCHAR2(100) := '';
  BEGIN
    SELECT AADT.DATA_TABLE_NAME,
           (SELECT ATC.COMMENTS
              FROM ALL_TAB_COMMENTS ATC
             WHERE ATC.TABLE_NAME = AADT.DATA_TABLE_NAME) AS COMMENT_TXT,
           AADT.PRIV_TO_VIEW_ID,
           AADT.TABLE_TYPE_ID,
           FNC_GET_IMAGE_INDEX(IN_TABLE_ID, 1) AS IMAGE_INDEX
      INTO TBL_NAME, OUT_TABLE_COMMENT, PRIV_LEVEL, TBL_TYPE, IMG_IDX
      FROM AVSDOK_ACCESSTO_DATA_TBL AADT
     WHERE AADT.ID = IN_TABLE_ID;
    FROM_TEXT := '';
    FOR REC IN (SELECT UTC.COLUMN_NAME
                  FROM USER_TAB_COLUMNS UTC
                 WHERE UTC.TABLE_NAME = TBL_NAME
                 ORDER BY UTC.TABLE_NAME, UTC.COLUMN_ID) LOOP
      IF REC.COLUMN_NAME NOT IN
         ('BDATA_NOTSH', 'CDATA_NOTSH', 'CDATA2_NOTSH') THEN
        FROM_TEXT := FROM_TEXT || REC.COLUMN_NAME || ', ';
      ELSE
        FROM_TEXT := FROM_TEXT || '(SELECT ''' || case REC.COLUMN_NAME
                       when 'BDATA_NOTSH' then
                        '<BLOB-поле>'
                       when 'CDATA_NOTSH' then
                        '<CLOB-поле>'
                       when 'CDATA2_NOTSH' then
                        '<CLOB-поле>'
                     end || '''' || ' FROM DUAL) AS ' || REC.COLUMN_NAME || ', ';
      
      END IF;
      IF (REC.COLUMN_NAME = 'CFG_ID') AND
         (FNC_GET_ACTIVE_CFGID_CONTEXT <> -1) THEN
        IS_CFG_CHECK := 1;
      END IF;
      IF REC.COLUMN_NAME = 'SYSTEM_FLAG' THEN
        IS_SYSTEM_CHECK := 1;
      END IF;
      IF REC.COLUMN_NAME = 'FNAME' THEN
        ORDER_FIELD_NAME          := 'FNAME';
        CHECK_FIELD_NAME_FOR_NULL := ' AND NOT FNAME IS NULL ';
      END IF;
      IF REC.COLUMN_NAME = 'DATA_NAME' THEN
        CHECK_FIELD_NAME_FOR_NULL := ' AND NOT DATA_NAME IS NULL ';
      END IF;
      IF REC.COLUMN_NAME = 'DATA_NAME_NS' THEN
        CHECK_FIELD_NAME_FOR_NULL := ' AND NOT DATA_NAME_NS IS NULL ';
      END IF;
      IF INSTR(REC.COLUMN_NAME, '_ORF') > 0 THEN
        ORDER_FIELD_NAME := REC.COLUMN_NAME;
      END IF;
      IF REC.COLUMN_NAME = 'PRIORITY' THEN
        ORDER_FIELD_NAME := REC.COLUMN_NAME;
      END IF;
      IF REC.COLUMN_NAME = 'REPORT_TYPE_ID' THEN
        ORDER_FIELD_NAME := REC.COLUMN_NAME;
      END IF;
      IF REC.COLUMN_NAME = 'TABLE_TYPE_ID' THEN
        ORDER_FIELD_NAME := REC.COLUMN_NAME || ', ID';
      END IF;
    
    END LOOP;
  
    CASE IMG_IDX
      WHEN -10 THEN
        FROM_TEXT := FROM_TEXT ||
                     ' DECODE(REPORT_TYPE_ID_NOTSH,1,64,2,11,3,12,4,55) AS IMAGE_INDEX';
      WHEN -20 THEN
        FROM_TEXT := FROM_TEXT ||
                     ' DECODE(CLOSED_FLAG,0,DECODE(DOGSTATE_ID,1,7,11,57,2,58,59),1,DECODE(DOGSTATE_ID,2,60,61)) AS IMAGE_INDEX';
      WHEN -30 THEN
        FROM_TEXT := FROM_TEXT ||
                     ' DECODE(SEX_NOTSH, 0, 63, 1, 5,2,62) AS IMAGE_INDEX';
      WHEN -40 THEN
        FROM_TEXT := FROM_TEXT ||
                     ' DECODE(GROUP_FLAG_NOTSH, 1, 67, 0, 11) AS IMAGE_INDEX';
      WHEN -50 THEN
        FROM_TEXT := FROM_TEXT ||
                     ' DECODE(REPORT_TYPE_ID, 1, 23, 2, 65, 3, 66) AS IMAGE_INDEX';
      WHEN -60 THEN
        FROM_TEXT := FROM_TEXT ||
                     ' DECODE(MOT_ID_NOTSH, 1, 72, 2, 71) AS IMAGE_INDEX';
      ELSE
        FROM_TEXT := FROM_TEXT || TO_CHAR(IMG_IDX) || ' AS IMAGE_INDEX';
    END CASE;
  
    IF IS_CFG_CHECK = 0 THEN
      IF NOT IN_LINKED_FIELD_NAME IS NULL THEN
        SQL_TXT := 'SELECT ' || FROM_TEXT || ' FROM ' || TBL_NAME ||
                   ' V WHERE ' || IN_LINKED_FIELD_NAME || '=' ||
                   IN_LINKED_FIELD_VAL || CHECK_FIELD_NAME_FOR_NULL ||
                   ' ORDER BY ' || ORDER_FIELD_NAME;
      ELSE
        IF NOT IN_ADD_FILTER_STR IS NULL THEN
          SQL_TXT := 'SELECT ' || FROM_TEXT || ' FROM ' || TBL_NAME ||
                     ' V WHERE ' || IN_ADD_FILTER_STR ||
                     CHECK_FIELD_NAME_FOR_NULL || ' ORDER BY ' ||
                     ORDER_FIELD_NAME;
        ELSE
          IF NOT CHECK_FIELD_NAME_FOR_NULL IS NULL THEN
            SQL_TXT := 'SELECT ' || FROM_TEXT || ' FROM ' || TBL_NAME ||
                       ' V WHERE 1=1 ' || CHECK_FIELD_NAME_FOR_NULL ||
                       ' ORDER BY ' || ORDER_FIELD_NAME;
          ELSE
            SQL_TXT := 'SELECT ' || FROM_TEXT || ' FROM ' || TBL_NAME ||
                       ' V ORDER BY ' || ORDER_FIELD_NAME;
          END IF;
        END IF;
      END IF;
    END IF;
    IF IS_CFG_CHECK = 1 AND IS_SYSTEM_CHECK = 0 THEN
      IF NOT IN_LINKED_FIELD_NAME IS NULL THEN
        SQL_TXT := 'SELECT ' || FROM_TEXT || ' FROM ' || TBL_NAME ||
                   ' V WHERE CFG_ID=' || FNC_GET_ACTIVE_CFGID_CONTEXT ||
                   ' AND ' || IN_LINKED_FIELD_NAME || '=' ||
                   IN_LINKED_FIELD_VAL || CHECK_FIELD_NAME_FOR_NULL ||
                   ' ORDER BY ' || ORDER_FIELD_NAME;
      ELSE
        IF NOT IN_ADD_FILTER_STR IS NULL THEN
          SQL_TXT := 'SELECT ' || FROM_TEXT || ' FROM ' || TBL_NAME ||
                     ' V WHERE CFG_ID=' || FNC_GET_ACTIVE_CFGID_CONTEXT ||
                     ' AND ' || IN_ADD_FILTER_STR ||
                     CHECK_FIELD_NAME_FOR_NULL || ' ORDER BY ' ||
                     ORDER_FIELD_NAME;
        ELSE
          SQL_TXT := 'SELECT ' || FROM_TEXT || ' FROM ' || TBL_NAME ||
                     ' V WHERE CFG_ID=' || FNC_GET_ACTIVE_CFGID_CONTEXT ||
                     CHECK_FIELD_NAME_FOR_NULL || ' ORDER BY ' ||
                     ORDER_FIELD_NAME;
        END IF;
      END IF;
    END IF;
    IF IS_CFG_CHECK = 1 AND IS_SYSTEM_CHECK = 1 THEN
      IF NOT IN_LINKED_FIELD_NAME IS NULL THEN
        SQL_TXT := 'SELECT ' || FROM_TEXT || ' FROM ' || TBL_NAME ||
                   ' V WHERE ' || IN_LINKED_FIELD_NAME || '=' ||
                   IN_LINKED_FIELD_VAL || CHECK_FIELD_NAME_FOR_NULL ||
                   ' AND CFG_ID=' || FNC_GET_ACTIVE_CFGID_CONTEXT ||
                   ' OR SYSTEM_FLAG = 1 ORDER BY ' || ORDER_FIELD_NAME;
      ELSE
        IF NOT IN_ADD_FILTER_STR IS NULL THEN
          SQL_TXT := 'SELECT ' || FROM_TEXT || ' FROM ' || TBL_NAME ||
                     ' V WHERE ' || IN_ADD_FILTER_STR ||
                     CHECK_FIELD_NAME_FOR_NULL || ' AND (' || 'CFG_ID=' ||
                     FNC_GET_ACTIVE_CFGID_CONTEXT ||
                     ' OR SYSTEM_FLAG = 1) ORDER BY ' || ORDER_FIELD_NAME;
        ELSE
          SQL_TXT := 'SELECT ' || FROM_TEXT || ' FROM ' || TBL_NAME ||
                     ' V WHERE CFG_ID=' || FNC_GET_ACTIVE_CFGID_CONTEXT ||
                     CHECK_FIELD_NAME_FOR_NULL ||
                     ' OR SYSTEM_FLAG = 1 ORDER BY ' || ORDER_FIELD_NAME;
        END IF;
      END IF;
    END IF;
    --    TXT_OUT:= SQL_TXT;
    OPEN RESULT FOR SQL_TXT;
  END PRC_GET_TABLE_DATA;


Процедура В: Получение lookup-данных с клиентского приложения
PROCEDURE PRC_GET_LOOKUP_DATA(IN_TABLE_ID   IN NUMBER,
                                IN_FIELD_NAME IN VARCHAR2,
                                TXT_OUT       OUT VARCHAR2,
                                RESULT        OUT SYS_REFCURSOR) AS
    IN_TBL_NAME   Avsdok_Accessto_Data_Tbl.Data_Table_Name%TYPE;
    OUT_TBL_NAME  USER_CONS_COLUMNS.table_name%TYPE;
    IN_CONSTR     USER_CONS_COLUMNS.constraint_name%TYPE;
    OUT_CONSTR    USER_CONSTRAINTS.r_constraint_name%TYPE;
    UK_NAME       USER_CONSTRAINTS.constraint_name%TYPE;
    NAME_SQL      VARCHAR2(3000) := '';
    TEMP_CFG_DATA VARCHAR2(500) := '';
    SQL_TXT       VARCHAR2(2000) := '';
    COL_CNT       NUMBER := 0;
    CHECK_FOR_CFG NUMBER := 0;
  BEGIN
    IF FNC_GET_ACTIVE_CFGID_CONTEXT = -1 THEN
      TEMP_CFG_DATA := ' IN (SELECT ACT.ID FROM AVSDOK_CONFIG_TBL ACT)';
    ELSE
      TEMP_CFG_DATA := '=' || TO_CHAR(FNC_GET_ACTIVE_CFGID_CONTEXT);
    END IF;
  
    SELECT TRIM(AADT.DATA_TABLE_NAME)
      INTO IN_TBL_NAME
      FROM AVSDOK_ACCESSTO_DATA_TBL AADT
     WHERE AADT.ID = IN_TABLE_ID;
  
    SELECT UCC.CONSTRAINT_NAME
      INTO IN_CONSTR
      FROM USER_CONS_COLUMNS UCC
     WHERE UCC.TABLE_NAME = IN_TBL_NAME
       AND UCC.COLUMN_NAME = IN_FIELD_NAME
       AND upper(SUBSTR(UCC.CONSTRAINT_NAME, 1, 2)) = 'FK';
  
    SELECT UC.R_CONSTRAINT_NAME
      INTO OUT_CONSTR
      FROM USER_CONSTRAINTS UC
     WHERE UC.CONSTRAINT_NAME = IN_CONSTR;
  
    SELECT UCC.TABLE_NAME
      INTO OUT_TBL_NAME
      FROM USER_CONS_COLUMNS UCC
     WHERE UCC.CONSTRAINT_NAME = OUT_CONSTR;
  
    SELECT COUNT(UTC.TABLE_NAME)
      INTO CHECK_FOR_CFG
      FROM USER_TAB_COLUMNS UTC
     WHERE UTC.TABLE_NAME = OUT_TBL_NAME
       AND UTC.COLUMN_NAME = 'CFG_ID';
  
    SELECT UC.CONSTRAINT_NAME
      INTO UK_NAME
      FROM USER_CONSTRAINTS UC
     WHERE UC.CONSTRAINT_TYPE = 'U'
       AND UC.TABLE_NAME = OUT_TBL_NAME;
  
    FOR REC IN (SELECT UCC.COLUMN_NAME
                  FROM USER_CONS_COLUMNS UCC
                 WHERE UCC.CONSTRAINT_NAME = UK_NAME
                   AND UCC.TABLE_NAME = OUT_TBL_NAME
                   AND UCC.COLUMN_NAME <> 'CFG_ID'
                   AND UCC.column_name <> 'REPORT_TYPE_ID'
                 ORDER BY UCC.POSITION) LOOP
      COL_CNT := COL_CNT + 1;
      IF (COL_CNT <= 3) AND (REC.COLUMN_NAME <> 'ID') AND
         (REC.COLUMN_NAME <> 'COST') THEN
        IF INSTR(REC.COLUMN_NAME, '_DATE') = 0 THEN
          NAME_SQL := NAME_SQL || REC.COLUMN_NAME || '||'' ''|| ';
        ELSE
          NAME_SQL := RTRIM(NAME_SQL, '||'' ''||');
          NAME_SQL := NAME_SQL || '||'' от ''||' || REC.COLUMN_NAME;
        END IF;
      END IF;
      IF ((IN_TABLE_ID = 6) OR (IN_TABLE_ID = 13) OR (IN_TABLE_ID = 18)) AND
         REC.COLUMN_NAME = 'COURCE_ID' THEN
        NAME_SQL := ' (SELECT A.DATA_NAME_NS FROM AVSDOK_COURCES_TBL A WHERE A.ID=' ||
                    REC.COLUMN_NAME || ')';
      END IF;
    END LOOP;
  
    NAME_SQL := RTRIM(NAME_SQL, '||'' ''||');
    NAME_SQL := REPLACE(NAME_SQL, '  ', ' ');
    IF NAME_SQL = 'DATA_TABLE_NAME' THEN
      SQL_TXT := 'SELECT ID, PAC_SERVICE_PROC.FNC_GET_TABLE_DESC(ID) AS NAME FROM ' ||
                 OUT_TBL_NAME || ' ORDER BY NAME';
    ELSIF NAME_SQL = 'AVTO_L_ID' THEN
      SQL_TXT := 'SELECT ID, (SELECT PAC_WORK_WITH_AVTO.FNC_GET_AVTO_DATA(E.ID) FROM AVSDOK_AVTO_TBL E WHERE E.ID=AVTO_L_ID) AS NAME FROM ' ||
                 OUT_TBL_NAME;
    ELSE
      IF CHECK_FOR_CFG = 0 THEN
        SQL_TXT := 'SELECT ID, ' || NAME_SQL || ' AS NAME FROM ' ||
                   OUT_TBL_NAME;
      ELSE
        IF FNC_GET_ACTIVE_CFGID_CONTEXT = -1 THEN
          NAME_SQL := NAME_SQL ||
                      '||'' <''|| (SELECT CCC.SHORTNAME FROM AVSDOK_CONFIG_TBL CCC WHERE CCC.ID=CFG_ID)||''>'' ';
        END IF;
        SQL_TXT := 'SELECT ID, ' || NAME_SQL || ' AS NAME FROM ' ||
                   OUT_TBL_NAME || ' WHERE CFG_ID ' || TEMP_CFG_DATA;
      END IF;
    END IF;
    TXT_OUT := SQL_TXT;
    OPEN RESULT FOR SQL_TXT;
  END PRC_GET_LOOKUP_DATA;


Процедура C: Получение данных по-новому
PROCEDURE PRC_GET_TABLE_DATA_NEW(IN_TABLE_ID          IN NUMBER,
                                   IN_LINKED_FIELD_NAME IN VARCHAR2,
                                   IN_LINKED_FIELD_VAL  IN NUMBER,
                                   OUT_TABLE_COMMENT    OUT VARCHAR2,
                                   TXT_OUT2             OUT VARCHAR2,
                                   IN_ADD_FILTER_STR    IN VARCHAR2,
                                   RESULT               OUT SYS_REFCURSOR) AS
    TBL_NAME                  AVSDOK_ACCESSTO_DATA_TBL.DATA_TABLE_NAME%TYPE;
    SQL_TXT                   VARCHAR2(4000) := '';
    FROM_TEXT                 VARCHAR2(5000) := '';
    TBL_TYPE                  NUMBER := 0;
    IMG_IDX                   NUMBER := 0;
    PRIV_LEVEL                NUMBER := 0;
    IS_CFG_CHECK              NUMBER := 0;
    IS_SYSTEM_CHECK           NUMBER := 0;
    ORDER_FIELD_NAME          VARCHAR2(50) := 'ID';
    CHECK_FIELD_NAME_FOR_NULL VARCHAR2(100) := '';
  BEGIN
    SELECT AADT.DATA_TABLE_NAME,
           FNC_GET_TABLE_DESC(IN_TABLE_ID) AS COMMENT_TXT,
           AADT.PRIV_TO_VIEW_ID,
           AADT.TABLE_TYPE_ID,
           FNC_GET_IMAGE_INDEX(IN_TABLE_ID, 1) AS IMAGE_INDEX
      INTO TBL_NAME, OUT_TABLE_COMMENT, PRIV_LEVEL, TBL_TYPE, IMG_IDX
      FROM AVSDOK_ACCESSTO_DATA_TBL AADT
     WHERE AADT.ID = IN_TABLE_ID;
  
    FROM_TEXT := '';
  
    FOR REC IN (SELECT UTC.COLUMN_NAME
                  FROM USER_TAB_COLUMNS UTC
                 WHERE UTC.TABLE_NAME = TBL_NAME
                 ORDER BY UTC.TABLE_NAME, UTC.COLUMN_ID) LOOP
      IF REC.COLUMN_NAME NOT IN
         ('BDATA_NOTSH', 'CDATA_NOTSH', 'CDATA2_NOTSH') THEN
        IF INSTR(REC.COLUMN_NAME, '_ID') > 0 AND
           REC.COLUMN_NAME NOT IN ('CREATOR_U_ID',
                                   'EDITOR_U_ID',
                                   'U_CREATOR_U_ID',
                                   'U_EDITOR_U_ID',
                                   'CFG_ID') THEN
          FROM_TEXT := FROM_TEXT || REC.COLUMN_NAME || ', 
                       PAC_SERVICE_PROC.FNC_GET_LOOKUP_DATA_NEW(' ||    ------ Получение lookup-данных из связанных таблиц
                       IN_TABLE_ID || ',' || '''' || REC.COLUMN_NAME || '''' || ',' || 'V.' ||
                       REC.COLUMN_NAME || ') AS ' || REC.COLUMN_NAME ||
                       '_LK_DATA, ';
        ELSE
          FROM_TEXT := FROM_TEXT || REC.COLUMN_NAME || ', ';
        END IF;
      ELSE
        FROM_TEXT := FROM_TEXT || '(SELECT ''' || case REC.COLUMN_NAME
                       when 'BDATA_NOTSH' then
                        '<BLOB-поле>'
                       when 'CDATA_NOTSH' then
                        '<CLOB-поле>'
                       when 'CDATA2_NOTSH' then
                        '<CLOB-поле>'
                     end || '''' || ' FROM DUAL) AS ' || REC.COLUMN_NAME || ', ';
      END IF;
    
      IF (REC.COLUMN_NAME = 'CFG_ID') AND
         (FNC_GET_ACTIVE_CFGID_CONTEXT <> -1) THEN
        IS_CFG_CHECK := 1;
      END IF;
    
      IF REC.COLUMN_NAME = 'SYSTEM_FLAG' THEN
        IS_SYSTEM_CHECK := 1;
      END IF;
    
      IF REC.COLUMN_NAME = 'FNAME' THEN
        ORDER_FIELD_NAME          := 'FNAME';
        CHECK_FIELD_NAME_FOR_NULL := ' AND NOT FNAME IS NULL ';
      END IF;
    
      IF REC.COLUMN_NAME = 'DATA_NAME' THEN
        CHECK_FIELD_NAME_FOR_NULL := ' AND NOT DATA_NAME IS NULL ';
      END IF;
    
      IF REC.COLUMN_NAME = 'DATA_NAME_NS' THEN
        CHECK_FIELD_NAME_FOR_NULL := ' AND NOT DATA_NAME_NS IS NULL ';
      END IF;
    
      IF INSTR(REC.COLUMN_NAME, '_ORF') > 0 THEN
        ORDER_FIELD_NAME := REC.COLUMN_NAME;
      END IF;
    
      IF REC.COLUMN_NAME = 'PRIORITY' THEN
        ORDER_FIELD_NAME := REC.COLUMN_NAME;
      END IF;
    
      IF REC.COLUMN_NAME = 'REPORT_TYPE_ID' THEN
        ORDER_FIELD_NAME := REC.COLUMN_NAME;
      END IF;
    
      IF REC.COLUMN_NAME = 'TABLE_TYPE_ID' THEN
        ORDER_FIELD_NAME := REC.COLUMN_NAME || ', ID';
      END IF;
    
    END LOOP;
  
    CASE IMG_IDX
      WHEN -10 THEN
        FROM_TEXT := FROM_TEXT ||
                     ' DECODE(REPORT_TYPE_ID_NOTSH,1,64,2,11,3,12,4,55) AS IMAGE_INDEX';
      WHEN -20 THEN
        FROM_TEXT := FROM_TEXT ||
                     ' DECODE(CLOSED_FLAG,0,DECODE(DOGSTATE_ID,1,7,11,57,2,58,59),1,DECODE(DOGSTATE_ID,2,60,61)) AS IMAGE_INDEX';
      WHEN -30 THEN
        FROM_TEXT := FROM_TEXT ||
                     ' DECODE(SEX_NOTSH, 0, 63, 1, 5,2,62) AS IMAGE_INDEX';
      WHEN -40 THEN
        FROM_TEXT := FROM_TEXT ||
                     ' DECODE(GROUP_FLAG_NOTSH, 1, 67, 0, 11) AS IMAGE_INDEX';
      WHEN -50 THEN
        FROM_TEXT := FROM_TEXT ||
                     ' DECODE(REPORT_TYPE_ID, 1, 23, 2, 65, 3, 66) AS IMAGE_INDEX';
      WHEN -60 THEN
        FROM_TEXT := FROM_TEXT ||
                     ' DECODE(MOT_ID_NOTSH, 1, 72, 2, 71) AS IMAGE_INDEX';
      ELSE
        FROM_TEXT := FROM_TEXT || TO_CHAR(IMG_IDX) || ' AS IMAGE_INDEX';
    END CASE;
  
    IF IS_CFG_CHECK = 0 THEN
      IF NOT IN_LINKED_FIELD_NAME IS NULL THEN
        SQL_TXT := 'SELECT ' || FROM_TEXT || ' FROM ' || TBL_NAME ||
                   ' V WHERE ' || IN_LINKED_FIELD_NAME || '=' ||
                   IN_LINKED_FIELD_VAL || CHECK_FIELD_NAME_FOR_NULL ||
                   ' ORDER BY ' || ORDER_FIELD_NAME;
      ELSE
        IF NOT IN_ADD_FILTER_STR IS NULL THEN
          SQL_TXT := 'SELECT ' || FROM_TEXT || ' FROM ' || TBL_NAME ||
                     ' V WHERE ' || IN_ADD_FILTER_STR ||
                     CHECK_FIELD_NAME_FOR_NULL || ' ORDER BY ' ||
                     ORDER_FIELD_NAME;
        ELSE
          IF NOT CHECK_FIELD_NAME_FOR_NULL IS NULL THEN
            SQL_TXT := 'SELECT ' || FROM_TEXT || ' FROM ' || TBL_NAME ||
                       ' V WHERE 1=1 ' || CHECK_FIELD_NAME_FOR_NULL ||
                       ' ORDER BY ' || ORDER_FIELD_NAME;
          ELSE
            SQL_TXT := 'SELECT ' || FROM_TEXT || ' FROM ' || TBL_NAME ||
                       ' V ORDER BY ' || ORDER_FIELD_NAME;
          END IF;
        END IF;
      END IF;
    END IF;
    IF IS_CFG_CHECK = 1 AND IS_SYSTEM_CHECK = 0 THEN
      IF NOT IN_LINKED_FIELD_NAME IS NULL THEN
        SQL_TXT := 'SELECT ' || FROM_TEXT || ' FROM ' || TBL_NAME ||
                   ' V WHERE CFG_ID=' || FNC_GET_ACTIVE_CFGID_CONTEXT ||
                   ' AND ' || IN_LINKED_FIELD_NAME || '=' ||
                   IN_LINKED_FIELD_VAL || CHECK_FIELD_NAME_FOR_NULL ||
                   ' ORDER BY ' || ORDER_FIELD_NAME;
      ELSE
        IF NOT IN_ADD_FILTER_STR IS NULL THEN
          SQL_TXT := 'SELECT ' || FROM_TEXT || ' FROM ' || TBL_NAME ||
                     ' V WHERE CFG_ID=' || FNC_GET_ACTIVE_CFGID_CONTEXT ||
                     ' AND ' || IN_ADD_FILTER_STR ||
                     CHECK_FIELD_NAME_FOR_NULL || ' ORDER BY ' ||
                     ORDER_FIELD_NAME;
        ELSE
          SQL_TXT := 'SELECT ' || FROM_TEXT || ' FROM ' || TBL_NAME ||
                     ' V WHERE CFG_ID=' || FNC_GET_ACTIVE_CFGID_CONTEXT ||
                     CHECK_FIELD_NAME_FOR_NULL || ' ORDER BY ' ||
                     ORDER_FIELD_NAME;
        END IF;
      END IF;
    END IF;
    IF IS_CFG_CHECK = 1 AND IS_SYSTEM_CHECK = 1 THEN
      IF NOT IN_LINKED_FIELD_NAME IS NULL THEN
        SQL_TXT := 'SELECT ' || FROM_TEXT || ' FROM ' || TBL_NAME ||
                   ' V WHERE ' || IN_LINKED_FIELD_NAME || '=' ||
                   IN_LINKED_FIELD_VAL || CHECK_FIELD_NAME_FOR_NULL ||
                   ' AND CFG_ID=' || FNC_GET_ACTIVE_CFGID_CONTEXT ||
                   ' OR SYSTEM_FLAG = 1 ORDER BY ' || ORDER_FIELD_NAME;
      ELSE
        IF NOT IN_ADD_FILTER_STR IS NULL THEN
          SQL_TXT := 'SELECT ' || FROM_TEXT || ' FROM ' || TBL_NAME ||
                     ' V WHERE ' || IN_ADD_FILTER_STR ||
                     CHECK_FIELD_NAME_FOR_NULL || ' AND (' || 'CFG_ID=' ||
                     FNC_GET_ACTIVE_CFGID_CONTEXT ||
                     ' OR SYSTEM_FLAG = 1) ORDER BY ' || ORDER_FIELD_NAME;
        ELSE
          SQL_TXT := 'SELECT ' || FROM_TEXT || ' FROM ' || TBL_NAME ||
                     ' V WHERE CFG_ID=' || FNC_GET_ACTIVE_CFGID_CONTEXT ||
                     CHECK_FIELD_NAME_FOR_NULL ||
                     ' OR SYSTEM_FLAG = 1 ORDER BY ' || ORDER_FIELD_NAME;
        END IF;
      END IF;
    END IF;
    TXT_OUT2 := SQL_TXT;
    OPEN RESULT FOR SQL_TXT;
  END PRC_GET_TABLE_DATA_NEW;


Процедура D: Получение lookup-данных для процедуры C
FUNCTION FNC_GET_LOOKUP_DATA_NEW(IN_TABLE_ID   IN NUMBER,
                                   IN_FIELD_NAME IN VARCHAR2,
                                   IN_ID_VAL     IN NUMBER) RETURN VARCHAR2 IS
    OUT_TBL_NAME USER_CONS_COLUMNS.table_name%TYPE;
    UK_NAME      USER_CONSTRAINTS.constraint_name%TYPE;
    NAME_SQL     VARCHAR2(3000) := '';
    SQL_TXT      VARCHAR2(2000) := '';
    COL_CNT      NUMBER := 0;
    RES          VARCHAR2(1000) := NULL;
  BEGIN
  
    SELECT UCC.TABLE_NAME
      INTO OUT_TBL_NAME
      FROM USER_CONS_COLUMNS UCC
     WHERE UCC.CONSTRAINT_NAME = (SELECT UC.R_CONSTRAINT_NAME
      FROM USER_CONSTRAINTS UC
     WHERE UC.CONSTRAINT_NAME =
           (SELECT UCC.CONSTRAINT_NAME
              FROM USER_CONS_COLUMNS UCC
             WHERE UCC.TABLE_NAME =
                   (SELECT TRIM(AADT.DATA_TABLE_NAME)
                      FROM AVSDOK_ACCESSTO_DATA_TBL AADT
                     WHERE AADT.ID = IN_TABLE_ID)
               AND UCC.COLUMN_NAME = IN_FIELD_NAME
               AND upper(SUBSTR(UCC.CONSTRAINT_NAME, 1, 2)) = 'FK'));
  
    SELECT UC.CONSTRAINT_NAME
      INTO UK_NAME
      FROM USER_CONSTRAINTS UC
     WHERE UC.CONSTRAINT_TYPE = 'U'
       AND UC.TABLE_NAME = OUT_TBL_NAME;
  
    FOR REC IN (SELECT UCC.COLUMN_NAME
                  FROM USER_CONS_COLUMNS UCC
                 WHERE UCC.CONSTRAINT_NAME = UK_NAME
                   AND UCC.TABLE_NAME = OUT_TBL_NAME
                 ORDER BY UCC.POSITION) LOOP
      COL_CNT := COL_CNT + 1;
      IF (COL_CNT <= 3) AND (REC.COLUMN_NAME <> 'ID') AND
         (REC.COLUMN_NAME <> 'COST') THEN
        IF INSTR(REC.COLUMN_NAME, '_DATE') = 0 THEN
          NAME_SQL := NAME_SQL || REC.COLUMN_NAME || '||'' ''|| ';
        ELSE
          NAME_SQL := RTRIM(NAME_SQL, '||'' ''||');
          NAME_SQL := NAME_SQL || '||'' от ''||' || REC.COLUMN_NAME;
        END IF;
      END IF;
      IF ((IN_TABLE_ID = 6) OR (IN_TABLE_ID = 13) OR (IN_TABLE_ID = 18)) AND
         REC.COLUMN_NAME = 'COURCE_ID' THEN
        NAME_SQL := ' (SELECT A.DATA_NAME_NS FROM AVSDOK_COURCES_TBL A WHERE A.ID=' ||
                    REC.COLUMN_NAME || ')';
      END IF;
    END LOOP;
  
    NAME_SQL := RTRIM(NAME_SQL, '||'' ''||');
    NAME_SQL := REPLACE(NAME_SQL, '  ', ' ');
    IF NAME_SQL = 'AVTO_L_ID' THEN
      SQL_TXT := 'SELECT (SELECT PAC_WORK_WITH_AVTO.FNC_GET_AVTO_DATA(E.ID) FROM AVSDOK_AVTO_TBL E WHERE E.ID=AVTO_L_ID) AS NAME FROM ' ||
                 OUT_TBL_NAME || ' WHERE ID=:a';
    ELSE
      SQL_TXT := 'SELECT ' || NAME_SQL || ' AS NAME FROM ' || OUT_TBL_NAME ||
                 ' WHERE ID=:a';
    END IF;
    EXECUTE IMMEDIATE SQL_TXT
      INTO RES
      using IN_ID_VAL;
  
    RETURN RES;
  END FNC_GET_LOOKUP_DATA_NEW;
11 янв 12, 20:12    [11888413]     Ответить | Цитировать Сообщить модератору
 Re: Прошу подсказать оптимальное решение  [new]
andreymx
Member

Откуда: Запорожье
Сообщений: 54379
скажу сразу - мне всё не нравится :)
слишком это всё академически и безжизненно - все данные из словаря
(конечно, вряд ли я мог полностью оценить весь код)




вот такой кусок не нравится конкретно - если я ошибаюсь, поправь

Титов Артем
Процедура C: Получение данных по-новому
PROCEDURE PRC_GET_TABLE_DATA_NEW(IN_TABLE_ID          IN NUMBER,
....
          FROM_TEXT := FROM_TEXT || REC.COLUMN_NAME || ', 
                       PAC_SERVICE_PROC.FNC_GET_LOOKUP_DATA_NEW(' ||    ------ Получение lookup-данных из связанных таблиц


Процедура D: Получение lookup-данных для процедуры C
FUNCTION FNC_GET_LOOKUP_DATA_NEW(IN_TABLE_ID   IN NUMBER,
...
  BEGIN
  
    SELECT UCC.TABLE_NAME
      INTO OUT_TBL_NAME
      FROM USER_CONS_COLUMNS UCC

получается - для каждой строки основного запроса из процедуры C ты вызываешь функцию D (возможно, несколько раз) - с чтением словарей, динамикой и прочими вычислениями?

Конечно, это займёт время.... и очень немалое

Если че напутал - не взыщи... простыни у тебя большие
11 янв 12, 22:52    [11888998]     Ответить | Цитировать Сообщить модератору
 Re: Прошу подсказать оптимальное решение  [new]
andreymx
Member

Откуда: Запорожье
Сообщений: 54379
Титов Артем,

как вариант - пусть функция FNC_GET_LOOKUP_DATA_NEW возвращает sql-строку, которую ты вставишь в основной запрос. Тоже будет жопа, но не такая ресурсоемкая
11 янв 12, 23:25    [11889176]     Ответить | Цитировать Сообщить модератору
 Re: Прошу подсказать оптимальное решение  [new]
Титов Артем
Member

Откуда: МО
Сообщений: 297
andreymx
Титов Артем,

как вариант - пусть функция FNC_GET_LOOKUP_DATA_NEW возвращает sql-строку, которую ты вставишь в основной запрос. Тоже будет жопа, но не такая ресурсоемкая


Отлично помогло, спасибо. Меньше секунды в PL/SQL Developer'e. Завтра на клиент прикручу
11 янв 12, 23:42    [11889259]     Ответить | Цитировать Сообщить модератору
 Re: Прошу подсказать оптимальное решение  [new]
andreymx
Member

Откуда: Запорожье
Сообщений: 54379
мне наш одмин постоянно напоминает
и в столовой, и в курилке, и на улице:
ПОМЕНЬШЕ UDF в запросах!
11 янв 12, 23:46    [11889273]     Ответить | Цитировать Сообщить модератору
 Re: Прошу подсказать оптимальное решение  [new]
alex-ls
Member

Откуда: Иркутская обл - Пенза - Москва
Сообщений: 6910
     IF REC.COLUMN_NAME = 'FNAME' THEN
        ORDER_FIELD_NAME          := 'FNAME';
        CHECK_FIELD_NAME_FOR_NULL := ' AND NOT FNAME IS NULL ';
      END IF;
      IF REC.COLUMN_NAME = 'DATA_NAME' THEN
        CHECK_FIELD_NAME_FOR_NULL := ' AND NOT DATA_NAME IS NULL ';
      END IF;
      IF REC.COLUMN_NAME = 'DATA_NAME_NS' THEN
        CHECK_FIELD_NAME_FOR_NULL := ' AND NOT DATA_NAME_NS IS NULL ';
      END IF;

не пишут так код :) и таких мест ООООЧЕНЬ МНОГО, поэтому и простыня выходит, читать почти невозможно

приведу примерно, как надо такие вещи писать, чтобы код был меньше по размеру и понятнее 6 строк вместо 10 (40%)

DECLARE
  lREC_COLUMN_NAME VARCHAR2(100);
  lORDER_FIELD_NAME VARCHAR2(100);
  lCHECK_FIELD_NAME_FOR_NULL VARCHAR2(100);
BEGIN
  IF lREC_COLUMN_NAME = 'FNAME' THEN
    lORDER_FIELD_NAME := lREC_COLUMN_NAME;
    lCHECK_FIELD_NAME_FOR_NULL := ' AND NOT ' || lREC_COLUMN_NAME || ' IS NULL ';
  ELSIF lREC_COLUMN_NAME IN ('DATA_NAME', 'DATA_NAME_NS') THEN
    lCHECK_FIELD_NAME_FOR_NULL := ' AND NOT ' || lREC_COLUMN_NAME || ' IS NULL ';
  END IF;
END;

специально добавил блок, чтобы он автономно работал... в логику не вдумывался сильно
12 янв 12, 06:08    [11889607]     Ответить | Цитировать Сообщить модератору
Все форумы / Oracle Ответить