Как найти значение в oracle

Quote:

I’ve tried using this statement below
to find an appropriate column based on
what I think it should be named but it
returned no results.*

SELECT * from dba_objects WHERE
object_name like '%DTN%'

A column isn’t an object. If you mean that you expect the column name to be like ‘%DTN%’, the query you want is:

SELECT owner, table_name, column_name FROM all_tab_columns WHERE column_name LIKE '%DTN%';

But if the ‘DTN’ string is just a guess on your part, that probably won’t help.

By the way, how certain are you that ‘1/22/2008P09RR8’ is a value selected directly from a single column? If you don’t know at all where it is coming from, it could be a concatenation of several columns, or the result of some function, or a value sitting in a nested table object. So you might be on a wild goose chase trying to check every column for that value. Can you not start with whatever client application is displaying this value and try to figure out what query it is using to obtain it?

Anyway, diciu’s answer gives one method of generating SQL queries to check every column of every table for the value. You can also do similar stuff entirely in one SQL session using a PL/SQL block and dynamic SQL. Here’s some hastily-written code for that:

    SET SERVEROUTPUT ON SIZE 100000

    DECLARE
      match_count INTEGER;
    BEGIN
      FOR t IN (SELECT owner, table_name, column_name
                  FROM all_tab_columns
                  WHERE owner <> 'SYS' and data_type LIKE '%CHAR%') LOOP

        EXECUTE IMMEDIATE
          'SELECT COUNT(*) FROM ' || t.owner || '.' || t.table_name ||
          ' WHERE '||t.column_name||' = :1'
          INTO match_count
          USING '1/22/2008P09RR8';

        IF match_count > 0 THEN
          dbms_output.put_line( t.table_name ||' '||t.column_name||' '||match_count );
        END IF;

      END LOOP;

    END;
    /

There are some ways you could make it more efficient too.

In this case, given the value you are looking for, you can clearly eliminate any column that is of NUMBER or DATE type, which would reduce the number of queries. Maybe even restrict it to columns where type is like ‘%CHAR%’.

Instead of one query per column, you could build one query per table like this:

SELECT * FROM table1
  WHERE column1 = 'value'
     OR column2 = 'value'
     OR column3 = 'value'
     ...
     ;

Однажды передо мной встала задача определить, в какую таблицу БД попадают определённые данные с веб-формы. Вызвано это, например, может быть тем, что у нас есть доступ к БД, но нет исходников приложения. А таблицы кто-то называл в стиле TABLE_1, TABLE_2, OT_1 и так далее.
К счастью, БД была Oracle, поэтому можно использовать тёмные стороны PL/SQL.

Думаю, смысл действа понятен, поэтому сразу к коду:

create or replace procedure whereIsValue(valueParam varchar2)
AS
  TYPE VALCUR IS REF CURSOR;
  cursor tabl is select table_name from user_tables;
  cursor col (tablename varchar2) is select column_name from user_tab_columns where table_name like tableName;
  valueCursor VALCUR;
  tableName varchar2(50);
  columnName varchar2(50);
  columnValue varchar2(500);
  qq number(3);
begin
  open tabl;
  LOOP
    fetch tabl into tableName;
    EXIT WHEN tabl%NOTFOUND;
    OPEN col(tableName);
    LOOP     
      fetch col into columnName;
      EXIT WHEN col%NOTFOUND;
        OPEN valueCursor for 'select ' || columnName || ' from ' || tableName;
        LOOP
          BEGIN
            fetch valueCursor into columnValue;        
            EXIT WHEN valueCursor%NOTFOUND;
            if (columnValue like valueParam) then
              dbms_output.put_line(tableName);
              exit;
            end if;
          EXCEPTION
            WHEN OTHERS then
              qq := 4;
          END;
        END LOOP;
        CLOSE valueCursor;
    END LOOP;
    CLOSE col;
  END LOOP;

end;

Вкратце о том, что же происходит:

• проходим по всем таблицам схемы;
• для каждой таблицы проходим по всем её колонкам;
• для каждой колонки забираем список значений и сравниваем с тем, что передано в параметре процедуры;
• если совпало, то печатаем имя таблицы в DBMS_OUTPUT;

Буду рад комментариям, как можно было сделать иначе, не изобретая велосипедов.

Главная
> oracle, SQL > Поиск значения по всем таблицам

В этом учебном пособии вы узнаете, как использовать Oracle оператор LIKE (для выполнения сравнения с шаблоном) с синтаксисом, примерами и практическими упражнениями.

Описание

Oracle условие LIKE позволяет использовать подстановочные символы, которые будут использоваться в операторе WHERE в запросах SELECT, INSERT, UPDATE или DELETE. Это позволяет выполнять сопоставление с pattern (шаблоном).

Синтаксис

Синтаксис LIKE в Oracle/PLSQL:

expression LIKE pattern [ ESCAPE ‘escape_character’ ]

Параметры или аргументы

expression

Символьное выражение, такие как поле или столбец.

pattern

Символьное выражение, которое содержит сопоставление с pattern. pattern, которые вы можете выбрать:

подстановочный символ	пояснение
%	Соответствует любой строке любой длины (в том числе нулевой длины)
_	Соответствует одному символу

escape_character
Необязательный. Позволяет проверять наличие литералов подстановочных символов, таких как % или _.

Примечание

Смотрите также Oracle REGEXP_LIKE.

Пример использования % (символ процент)

Первый пример Oracle оператора LIKE, который мы рассмотрим, предполагает использование подстановочного символа %.

Рассмотрим, как % работает в Oracle операторе LIKE. Мы хотим найти всех customers, чьи last_name начинается с ‘Ар’.

Кроме того, можно использовать несколько символов % в пределах одной строки.

Например:

В этом примере Oracle оператора LIKE, мы ищем для всех customers, чьи last_name содержит символы ‘er’.

Пример использования _ (символ подчеркивание)

Далее, давайте рассмотрим, как подстановочный символ _ (символ подчеркивания) работает в Oracle операторе LIKE. Помните, что _ ищет только один символ.

Например:

В этом примере Oracle LIKE вернет всех suppliers, чье supplier_name имеет длину 5 символов, где первые два символа является ‘Sm’, а последние два символа это ‘th’. Например, он может вернуть suppliers, чьи supplier_name являются ‘Smith’, ‘Smyth’, ‘Smath’ или ‘Smeth’ и т.д.

Вот еще один пример:

Ища номер счета, вы можете обнаружить, что у вас есть только 5 из 6 цифр. В приведенном выше примере, будет возвращено потенциально 10 последних записей (где отсутствующее значение может быть от 0 до 9). Например, запрос может вернуть suppliers, чьи account_number являются:

923140, 923141, 923142, 923143, 923144, 923145, 923146, 923147, 923148, 923149

Пример использования оператора NOT

Далее, давайте рассмотрим то, как вы будете использовать Oracle оператор NOT с подстановочными символами.

Давайте использовать % с оператором NOT. Кроме того, можно использовать Oracle оператор LIKE для поиска suppliers (поставщиков), имена которых не начинаются на ‘W’.

Например:

Размещая оператор NOT перед LIKE, вы можете получить всех suppliers, чьи supplier_name не начинаются на ‘W’.

Пример использования ESCAPE

Важно понять, каким образом действует escape_character при совпадении с шаблоном. Эти примеры относятся конкретно к пропуску символов в Oracle.

Допустим, вы хотите найти % или _ (символ процента или подчеркивания) в операторе LIKE. Вы можете сделать это с помощью ESCAPE символов.

Обратите внимание, что вы можете определить escape_character (экранирующий символ), как один символ (длина 1).

Например:

Этот пример LIKE идентифицирует символ ! как экранирующий символ. Этот запрос вернет всех suppliers, чье supplier_name имеют значение ‘Water%’.

Этот пример Oracle оператора LIKE возвращает всех suppliers, имена которых начинаются с H и заканчиваются на %. Например, это может вернуть значение, такое как ‘Hello%’.

Вы также можете использовать escape символ с символом _ в условии LIKE.

Например:

Этот пример LIKE возвращает всех suppliers, имена которых начинаются с ‘H’ и заканчиваются на ‘_’. Например, это может вернуть значение, такое как ‘Hello_’.

Часто задаваемые вопросы

Вопрос: Как объединить Oracle функцию UPPER с Oracle оператором LIKE? Я пытаюсь запросить все записи текстового поля, которое содержат слово «test». Проблема заключается в том, что поле может иметь следующие значения: TEST, Test или test.

Ответ: Для того чтобы ответить на этот вопрос, давайте рассмотрим пример.

Давайте предположим, что у нас есть таблица suppliers с полем под названием supplier_name, которое содержит значения TEST, Test или test.

Если мы хотим найти все записи, содержащие слово «test», независимо от того в каком виде они сохранены как TEST, Test или test, то мы могли бы выполнить одно из следующих предложений SELECT:

ИЛИ

Это предложение SELECT, использует комбинацию функции Oracle UPPER и оператора LIKE, чтобы вернуть все записи, где поле supplier_name содержит слово «test», независимо от того, в каком значении оно было сохранено как TEST, Test или test.

Практическое упражнение № 1:

На основании таблицы employees содержащей следующие данные, найти все записи, у которых employee_name заканчивается буквой ‘h’.

Решение для практического упражнения № 1:

Следующий оператор SELECT использует Oracle оператор LIKE, чтобы возвратить записи, у которых employee_name заканчивается буквой ‘h’.

Этот запрос вернет следующий результирующий набор:

EMPLOYEE_NUMBER	EMPLOYEE_NAME	SALARY
1001	John Smith	62000
1004	Jack Horvath	42000

Практическое упражнение № 2:

На основании таблицы employees содержащей следующие данные, найти все записи, в которых employee_name содержит букву ‘s’.

Решение для практического упражнения № 2:

Следующий оператор SELECT использует Oracle оператор LIKE, чтобы возвратить записи, у которых поле employee_name содержит букву ‘s’.

Это вернет следующий результирующий набор:

EMPLOYEE_NUMBER	EMPLOYEE_NAME	SALARY
1002	Jane Anderson	57500
1003	Brad Everest	71000

Практическое упражнение № 3:

На основании таблицы employees содержащей следующие данные, найти все записи, в которых supplier_id состоит из 4 цифр и начинается с ‘500’.

Решение для практического упражнения № 3:

Следующий оператор SELECT использует Oracle оператор LIKE, чтобы возвратить записи, у которых supplier_id состоит из 4 цифр и начинается с ‘500’.

Это вернет следующий результирующий набор:

SUPPLIER_ID	SUPPLIER_NAME	CITY
5008	Microsoft	New York
5009	IBM	Chicago