Поиск в базе данных Oracle 10g c помощью регулярных выражений
В продолжение http://community.terrasoft.ua/blogs/2965 хочу поделиться примером как можно искать в базе данных Oracle с помощью регулярных выражений:
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;
О синтаксисе регулярных выражений можно прочитать здесь
Алексей,
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;