Классификация имен таблиц, начинающаяся с и заканчивающаяся на в Oracle DB

Позвольте мне объяснить на простых примерах, в основном у нас есть сложный дизайн соглашений об именах таблиц, по крайней мере, в схеме стадии есть 4 шаблона.

Всего СЛОЕВ 4

1. ЭТАП СЛОЙ
2. БАЗОВЫЙ СЛОЙ ХРАНИЛИЩА ДАННЫХ
3. СЛОЙ ЭДО
4. МАРТ СЛОЙ

Все приведенные ниже шаблоны относятся только к сценическому слою.

ЭТАПНЫЙ СЛОЙ — "DS_00", "DSC_00", "DSP_00", "DT"

В этой схеме при поиске комбинации шаблонов поиска мне нужно учитывать таблицы, начинающиеся с «DS_00», «DSC_00», «DSP_00», «DT» (приведенные ниже шаблоны) и заканчивающиеся (T, W, W01, W02, W03,W1,W2,W3), поэтому таблица должна быть классифицирована как STANADARD_NAMING_CONV, иначе NON_STANDARD_NAMING_CONV

Т - технический W - рабочие столы

Узор 1

DS_00_to_99 (00_to_99 — это число, определяющее, из какой системы загружается таблица) DS_SYSTEM_NO_TABLENAME_ENDING СИСТЕМНЫЙ_НОМЕР - 00_to_99 TABLENAME - полное имя таблицы ENDING - не во всех случаях таблица классифицируется как (T,W,W01,W02,W03,W1,W2,W3) поэтому диапазон чисел, как указано, от 00 до 99, также одинаков и для других шаблонов 1.

Узор 2

DSC_00_to_99 (00_to_99 — это число, определяющее, из какой системы загружается таблица из исходного кода) DSC_SYSTEM_NO_TABLENAME_ENDING поэтому диапазон чисел, как указано, от 00 до 99, также одинаков и для других шаблонов 2.

Узор 3

DSP_00_to_99 (00_to_99 — это число, определяющее, из какой системы загружается таблица из исходного кода) DS_SYSTEM_NO_TABLENAME_ENDING поэтому диапазон чисел, как указано, от 00 до 99, также одинаков и для других шаблонов 3.

Узор 4

DT_ABC_FHSJ DS_TABLENAME_ENDING поэтому диапазон чисел не применим для этого шаблона

УРОВЕНЬ БАЗОВОГО ХРАНИЛИЩА ДАННЫХ — «DS»

В этой схеме при поиске комбинации шаблонов поиска мне нужно учитывать таблицы, начинающиеся с «DB» и заканчивающиеся (D, F, L, T, W, W01, W02, W03, W1, W2, W3), поэтому таблица должен быть классифицирован как STANADARD_NAMING_CONV, иначе NON_STANDARD_NAMING_CONV

СЛОЙ EDW - 'DE'

В этой схеме при поиске комбинации шаблонов поиска мне нужно учитывать таблицы, начинающиеся с «DB» и заканчивающиеся (D, F, L, T, W, W01, W02, W03, W1, W2, W3), поэтому таблица должны быть классифицированы как STANADARD_NAMING_CONV, иначе NON_STANDARD_NAMING_CONV, и в этом слое есть некоторые другие таблицы, которые начинаются с TBD_, должны быть классифицированы как TO_BE_DROPPED, а TMP_ должны быть классифицированы как TEMPORARY_TABLE

МАРТ СЛОЙ - 'DM'

В этой схеме при поиске комбинации шаблонов поиска мне нужно учитывать таблицы, начинающиеся с «DB» и заканчивающиеся на (D, F, L, T, W, W01, W02, W03, W1, W2, W3, A, AD, AM,AQ,AY), поэтому таблица должна быть классифицирована как STANADARD_NAMING_CONV, иначе NON_STANDARD_NAMING_CONV, и в этом слое есть некоторые другие таблицы, которые начинаются с TBD_, должны быть классифицированы как TO_BE_DROPPED, а TMP_ должны быть классифицированы как TEMPORARY_TABLE.

D - Таблицы размеров
F - Таблицы фактов
L - Таблицы поиска
T - Технические таблицы
W - Рабочие столы
A - сводные таблицы фактов
AD - сводка фактов ежедневно
AM - Ежемесячный сводка фактов
AQ - Ежеквартальная сводка фактов
AY - совокупный факт ежегодно

Запрос 1 — СТАРЫЙ запрос

SELECT owner,
         object_name,
         beginning,
         ending,
         --count(*),
         CASE
         WHEN ( beginning, ending ) IN (
                ( 'DS', 'T' ),
                ( 'DS', 'W' ),
                ( 'DS', 'W01' ),
                ( 'DS', 'W02' ),
                ( 'DS', 'W03' ),
                ( 'DS', 'W1' ),
                ( 'DS', 'W2' ),
                ( 'DS', 'W3' ),
                ( 'DB', 'D' ),
                ( 'DB', 'F' ),
                ( 'DB', 'L' ),
                ( 'DB', 'T' ),
                ( 'DB', 'W' ),
                ( 'DB', 'W01' ),
                ( 'DB', 'W02' ),
                ( 'DB', 'W03' ),
                ( 'DB', 'W1' ),
                ( 'DB', 'W2' ),
                ( 'DB', 'W3' ),
                ( 'DE', 'D' ),
                ( 'DE', 'F' ),
                ( 'DE', 'L' ),
                ( 'DE', 'T' ),
                ( 'DE', 'W' ),
                ( 'DE', 'W01' ),
                ( 'DE', 'W02' ),
                ( 'DE', 'W03' ),
                ( 'DE', 'W1' ),
                ( 'DE', 'W2' ),
                ( 'DE', 'W3' ),
                ( 'DA', 'D' ),
                ( 'DA', 'F' ),
                ( 'DA', 'L' ),
                ( 'DA', 'T' ),
                ( 'DA', 'W' ),
                ( 'DA', 'W01' ),
                ( 'DA', 'W02' ),
                ( 'DA', 'W03' ),
                ( 'DA', 'W1' ),
                ( 'DA', 'W2' ),
                ( 'DA', 'W3' ),
                ( 'DA', 'A' ),
                ( 'DA', 'AD' ),
                ( 'DA', 'AM' ),
                ( 'DA', 'AQ' ),
                ( 'DA', 'AY' )
              )
         THEN 'STANDARD_NAMING_CONVENTION'
         WHEN object_name LIKE 'TBD%'
         THEN 'TO_BE_DROPPED'
         WHEN object_name LIKE 'TMP%'
         THEN 'TEMPORARY_TABLE'
         ELSE 'NON_STANDARD_NAMING_CONVENTION'
         END AS table_classification
  FROM   (
    SELECT owner,       
           object_name,
           CASE first_separator
           WHEN 0
           THEN NULL
           ELSE SUBSTR( object_name, 1, first_separator - 1 )
           END AS beginning,
           CASE last_separator
           WHEN 0
           THEN NULL
           ELSE SUBSTR( object_name, last_separator + 1 )
           END AS ending
    FROM   (
      SELECT owner,       
             object_name,
             INSTR( object_name, '_', 1 )  AS first_separator,
             INSTR( object_name, '_', -1 ) AS last_separator
      FROM   dba_objects
    WHERE  owner in ('DI_STAGE','DI_BDS','DI_EDW','DI_MART')   
      and    object_type='TABLE'
      group by owner,object_name
      order by owner desc 
    )
  );

Запрос 2 – окончательный запрос – выполнено

SELECT OWNER, 
       OBJECT_NAME, 
       REGEXP_REPLACE(OBJECT_NAME, '^([A-Z0-9$]{1,})_([A-Z0-9$]{1,})_.*','\1_\2')AS  BEGINNING, 
       REGEXP_SUBSTR(OBJECT_NAME, '[^_]*$') AS ENDING, 
      CASE 
        WHEN REGEXP_LIKE(OBJECT_NAME, '^D(S[CP]?|T)_.+_(T|(W0?[123]?))$')
       THEN 
         'STANDARD_NAMING_CONVENTION' 
         WHEN REGEXP_LIKE(OBJECT_NAME, '^DB_.+_(D|F|T|W|W1|W2|W3)$')
           THEN 
         'STANDARD_NAMING_CONVENTION' 
         WHEN REGEXP_LIKE(OBJECT_NAME, '^DE_.+_(D|F|L|T|W|W01|W02|W03|W1|W2|W3)$')
      THEN 
         'STANDARD_NAMING_CONVENTION'
         WHEN OBJECT_NAME LIKE 'TBD%' 
        THEN 
            'TO_BE_DROPPED' 
         WHEN OBJECT_NAME LIKE 'TMP%' 
        THEN 'TEMPORARY_TABLE' 
         WHEN REGEXP_LIKE(OBJECT_NAME, '^DM_.+_(D|F|L|T|W|W01|W02|W03|W1|W2|W3|A|AD|AM|AQ|AY)$') 
       THEN 
         'STANDARD_NAMING_CONVENTION' 
         ELSE 'NON_STANDARD_NAMING_CONVENTION' 
       END 
       AS TABLE_CLASSIFICATION 
FROM   DBA_OBJECTS 
WHERE  OWNER IN ('DI_STAGE', 'DI_BDS', 'DI_EDW', 'DI_MART') 
       AND OBJECT_TYPE = 'TABLE' 
GROUP  BY OWNER, 
          OBJECT_NAME 
ORDER  BY OWNER DESC, 
          OBJECT_NAME; 

ОЖИДАЕМЫЕ РЕЗУЛЬТАТЫ - Соответствует

SNO OWNER  OBJECT_NAME       BEGINNING ENDING  TABLE_CLASSIFICATION 
01  DI_BDS DB_PROD_DGGAA_D   DB_PROD   D       STANDARD_NAMING_CONVENTION
02  DI_BDS DB_CUST_DHHA_F    DB_CUST   F       STANDARD_NAMING_CONVENTION
03  DI_BDS DB_DHSHJA_HHSGS_T DB_DHSHJA T       STANDARD_NAMING_CONVENTION
04  DI_BDS DB_DHS_DHHA_W     DB_DHS    W       STANDARD_NAMING_CONVENTION
05  DI_BDS DB_GSG_DHHA_W01   DB_GSG    W01     STANDARD_NAMING_CONVENTION
06  DI_BDS DB_GFS_FHSH_W02   DB_GFS    W02     STANDARD_NAMING_CONVENTION
07  DI_BDS DB_FGS_FHS_W03    DB_FGS    W03     STANDARD_NAMING_CONVENTION
08  DI_BDS DB_DJJ_GSA_W1     DB_DJJ    W1      STANDARD_NAMING_CONVENTION
09  DI_BDS DB_DKS_SJ_W2      DB_DKS    W2      STANDARD_NAMING_CONVENTION
10  DI_BDS DB_DJA_DT_W3      DB_DJA    W3      STANDARD_NAMING_CONVENTION
11  DI_BDS DB_DHH_DG         DB_DHH    DG      NON_STANDARD_NAMING_CONV
12  DI_BDS DB_DNS_DRS_123    DB_DNS    123     NON_STANDARD_NAMING_CONV
13  DI_BDS DB_FHD_DRS_1      DB_FHD    1       NON_STANDARD_NAMING_CONV
14  DI_BDS DB_OKS_DRS_0      DB_OKS    0       NON_STANDARD_NAMING_CONV
15  DI_BDS DB_SKG_DRS_90     DB_SKG    90      NON_STANDARD_NAMING_CONV

Наконец-то сработало