Поиск в базе данных Oracle 10g c помощью регулярных выражений

В продолжение http://community.terrasoft.ua/blogs/2965 хочу поделиться примером как можно искать в базе данных Oracle с помощью регулярных выражений:

DECLARE
   sqlcommand      VARCHAR2 (3000);
   recordscount    INT;
   regexpression   VARCHAR2 (250);
   recordid        VARCHAR (38);
   columvalue      VARCHAR2 (250);

   CURSOR tbl_cur
   IS
      SELECT "Code"
        FROM "tbl_Service"
       WHERE "ServiceTypeCode" = 'Table';

   CURSOR col_cur (tblname VARCHAR2)
   IS
      SELECT column_name
        FROM ts_tab_columns
       WHERE table_name = tblname AND data_type = 'VARCHAR2' AND DATA_LENGTH > 38;

   TYPE query_curtype IS REF CURSOR;

   dyncur          query_curtype;
BEGIN
   regexpression := '[A-Za-z]';

   FOR tbl_rec IN tbl_cur
   LOOP
      sqlcommand := 'SELECT COUNT(*) FROM "' || tbl_rec."Code" || '"';

      EXECUTE IMMEDIATE sqlcommand
                   INTO recordscount;

      IF recordscount > 0
      THEN
         FOR col_rec IN col_cur (tbl_rec."Code")
         LOOP
            sqlcommand :=
                  'SELECT "ID","'
               || col_rec.column_name
               || '" FROM "'
               || tbl_rec."Code"
               || '" WHERE REGEXP_LIKE("'
               || col_rec.column_name
               || '",'''
               || regexpression
               || ''')';

            OPEN dyncur FOR sqlcommand;

            FETCH dyncur
             INTO recordid, columvalue;

            WHILE (dyncur%FOUND)
            LOOP
               DBMS_OUTPUT.put_line (   tbl_rec."Code"
                                     || ':'
                                     || col_rec.column_name
                                     || ':'
                                     || recordid
                                     || ':'
                                     || columvalue
                                    );

               FETCH dyncur
                INTO recordid, columvalue;
            END LOOP;

            CLOSE dyncur;
         END LOOP;
      END IF;
   END LOOP;
END;

О синтаксисе регулярных выражений можно прочитать здесь
 

Нравится

Поделиться

5 комментариев

Спасибо за ценный материал, Алексей!

Пользуйтесь на здоровье!!!

Алексей,
1. Если используете varchar2, то указывайте в длине, что это символы, так как по умолчанию это байты или используйте nvarchar2. Из-за этого могут быть проблемы с юникодом:

 regexpression   VARCHAR2 (250 CHAR);
   columvalue      VARCHAR2 (250 CHAR);

или

 regexpression   NVARCHAR2 (250);
   columvalue      NVARCHAR2 (250);

2. Что делать если regexpression содержит запрещенные символы? Например, одинарную кавычку?
3. И что же все таки делать если длина строки 38? Правильно было бы исключать ПК.

Приятно когда твой код анализируют и дают ценные замечания.
По п. 1,3 согласен. По п. 2 можно, как вариант, задавать regexpression в виде '['||CHR(39)||CHR(39)||']', но наиболее правильным будет переделать курсор в виде

sqlcommand :=
'SELECT "ID","'
|| col_rec.column_name
|| '" FROM "'
|| tbl_rec."Code"
|| '" WHERE REGEXP_LIKE("'
|| col_rec.column_name
|| '", :regexpression)';

OPEN dyncur FOR sqlcommand USING regexpression;

Алексей, отлично! :smile:

Показать все комментарии