Oracle проверить наличие поля в таблице
Доброго всем дня.
Мне понадобилась ежедневно просматривать наличие базы а таблицах. Но, не во всех подряд,а в тех которые мне понадобятся. И этот список будет хранится в определённой таблице данных.
Сделать список таблиц которые мне стоит проверять для меня не составит труда написать, но я долго ломал себе голову и не пойму как в этот список подставлять запрос проверки данных.
Только начал учить PL/SQL. Это цикл какой-то дожен быть? Подскажите если не сложно!
Если ваша задача узнать какое количество строк, то можно попробовать воспользоваться вьюхой all_tables (поле num_rows). Но если хотите чтоб она работала, статистика по таблице должна быть актуальной!
я так поняла что динамический sql это именно, то что мне надо. Хотелось бы услышать советы человека который очень серьёзно занимается sql-ом. Нашла парочку примеров, но они какие-то невзрачные и не понятные, а хочется по быстрее разобраться. Например есть у нас 2 таблички а и Б и во всех есть поле time_info которое кроет в себе дату. Своим запросом хочу узнавать была ли какае-то запись за сегодняшний день. Если нет, то я вписую в табличку result , дату и число таблицы в которой отсутствует таблица.
Как сделать такую схему с помощью динамического запроса?
вот такой пример можно сделать как-то вот так. ( если что-то работать не будет, вы не судите строго потому что очень спешила и делала на быструю руку)
Table a has data for today
Table b has no data for today
Ну я думаю как сделать вставку в таблицу result вы сами поймёте как сделать.
так, а вот это по хоже именно, то что мне нужно. попробую конечно сама разобраться, но если не получится, то спрошу совету у вас снова. но, заранее спасибо
Теперь нам стоит чтоб дать упругости, нужно сделать так чтоб передавать как параметр делать проверку на ещё одно значение по другим табличкам ( примерно будет так в таблице С валяется список таблиц который предоставили вы мне, а в таблице D, список множества полей из этих табличек , таблицы связаны по id (c.id = d.id)).
Как сделать так чтоб процедура принимала внешние параметры? Или всё это можно осуществить как-то изнутри?
Пишу запрос типа
ALTER TABLE table_name ADD some_column INT
Так вот, хотелось бы перед этим проверить существует ли some_column в данной таблице или нет.
СУБД Oracle.
Заранее спасибо.
Если что сильно не пинайте за такие вопросы.
Здравствуйте, Аноним, Вы писали:
А>Так вот, хотелось бы перед этим проверить существует ли some_column в данной таблице или нет.
А>СУБД Oracle.
Здравствуйте, Lloyd, Вы писали:
L>Здравствуйте, Аноним, Вы писали:
А>>Так вот, хотелось бы перед этим проверить существует ли some_column в данной таблице или нет.
А>>СУБД Oracle.
L>The INFORMATION_SCHEMA COLUMNS Table
А как будет выглядеть запрос с проверкой? Не очень понимаю просто
Надо проверить и если не существует то добавить, как это будет выглядеть?
А>А как будет выглядеть запрос с проверкой? Не очень понимаю просто
А>Надо проверить и если не существует то добавить, как это будет выглядеть?
Я в оракле — ни бум-бум. Для MSSQL-я это бы выглядело так:
Попробуй что-нибудь похожее.
А>>А как будет выглядеть запрос с проверкой? Не очень понимаю просто
А>>Надо проверить и если не существует то добавить, как это будет выглядеть?
L>Я в оракле — ни бум-бум. Для MSSQL-я это бы выглядело так:
L>
L>Попробуй что-нибудь похожее.
Понял, спасибо, просто не знал что существуют такие запросы. Поробую применить это.
Спасибо большое.
P.s. Может конкретно для Oracle кто и подскажет.
Здравствуйте, <Аноним>, Вы писали:
А>Понял, спасибо, просто не знал что существуют такие запросы. Поробую применить это.
А>Спасибо большое.
А>P.s. Может конкретно для Oracle кто и подскажет.
Здравствуйте, LuciferArh, Вы писали:
А>>P.s. Может конкретно для Oracle кто и подскажет.
LA>Здесь.
Я так понимаю, у анонима проблема не с ALTER-ом, а с IF-ами.
Здравствуйте, Lloyd, Вы писали:
L>Здравствуйте, LuciferArh, Вы писали:
А>>>P.s. Может конкретно для Oracle кто и подскажет.
LA>>Здесь.
L>Я так понимаю, у анонима проблема не с ALTER-ом, а с IF-ами.
Да именно с ними, вы правы
А>>А как будет выглядеть запрос с проверкой? Не очень понимаю просто
А>>Надо проверить и если не существует то добавить, как это будет выглядеть?
L>Я в оракле — ни бум-бум. Для MSSQL-я это бы выглядело так:
L>
L>Попробуй что-нибудь похожее.
Что то для Oracle такое не прокатывает
Здравствуйте, Аноним, Вы писали:
L>>Попробуй что-нибудь похожее.
А>Что то для Oracle такое не прокатывает
Конечно не прокатывает, TSQL != PLSQL.
Здравствуйте, <Аноним>, Вы писали:
А>Да именно с ними, вы правы
Ну, тогда как-то вот так:
Здравствуйте, Lloyd, Вы писали:
L>Здравствуйте, Аноним, Вы писали:
L>>>Попробуй что-нибудь похожее.
А>>Что то для Oracle такое не прокатывает
L>Конечно не прокатывает, TSQL != PLSQL.
Ну я пока не очень разбираюсь в этом . Щас попробую то что предложили выше.
Здравствуйте, LuciferArh, Вы писали:
LA>Здравствуйте, , Вы писали:
А>>Да именно с ними, вы правы
LA>Ну, тогда как-то вот так:
LA>
Здравствуйте, LuciferArh, Вы писали:
LA>Здравствуйте, , Вы писали:
А>>Да именно с ними, вы правы
LA>Ну, тогда как-то вот так:
LA>
Запрос отработал, но колонка не добавилась , хотя еще пока не существует там.
Здравствуйте, <Аноним>, Вы писали:
А>Запрос отработал, но колонка не добавилась , хотя еще пока не существует там.
Ну, у меня просто под рукой оракла нет. Но это я напутал с условием if v_exist > 0 then . Если колонка не существует, то и предыдущий селект ничего не вернет. Соответственно, условие в IF не выполнится. Писал на коленке по памяти, сорри.
Здравствуйте, LuciferArh, Вы писали:
LA>Здравствуйте, , Вы писали:
А>>Запрос отработал, но колонка не добавилась , хотя еще пока не существует там.
LA>Ну, у меня просто под рукой оракла нет. Но это я напутал с условием if v_exist > 0 then . Если колонка не существует, то и предыдущий селект ничего не вернет. Соответственно, условие в IF не выполнится. Писал на коленке по памяти, сорри.
Что то я подзапутался, так что там надо в условие поставить? Предыдущий селект ноль вернет?
Это выражение возвращает истину, когда по запросу найдена одна или более строк, соответствующих условию, и ложь, когда не найдено ни одной строки.
Обычно предикат EXISTS применяется в случаях, когда необходимо найти значения, соответствующие основному условию, заданному в секции WHERE, и дополнительному условию, заключённому в подзапрос, являющийся аргументом предиката.
Для NOT EXISTS всё наоборот. Выражение
возвращает истину, когда по запросу не найдено ни одной строки, и ложь, когда найдена хотя бы одна строка.
Если вы хотите выполнить запросы к базе данных из этого урока на MS SQL Server, но эта СУБД не установлена на вашем компьютере, то ее можно установить, пользуясь инструкцией по этой ссылке .
Запросы с предикатом EXISTS и дополнительными условиями
Если дополнительно к предикату EXISTS в запросе применить хотя бы одно дополнительное условие, например, заданное с помощью агрегатных функций, то такие запросы могут служить уже для простого анализа данных. Продемонстрируем это на следующем примере.
Пример 5. Определить ID пользователей, которым выдана хотя бы одна книга Пастернака, и которым при этом выдано более 2 книг. Пишем следующий запрос, в котором первое условие задаётся предикатом EXISTS со вложенным запросом, а второе условие с оператором HAVING всегда должно следовать после вложенного запроса:
SELECT Customer_ID FROM Bookinuse AS pas_user WHERE EXISTS ( SELECT Customer_ID FROM Bookinuse WHERE Author='Пастернак' AND Customer_ID=pas_user.Customer_ID) GROUP BY Customer_ID HAVING COUNT (Title) > 2
Результат выполнения запроса:
Как видно из таблицы BOOKINUSE, книга Пастернака выдана также пользователю с ID 18, но ему выдана всего одна книга и он не попадает в выборку. Если применить к подобному запросу ещё раз функцию COUNT, но уже для подсчёта выбранных строк (потренируйтесь в этом самостоятельно), то можно получить сведения о том, сколько пользователей, читающих книги Пастернака, при этом читают также книги других авторов. Это уже из сферы анализа данных.
Предикат EXISTS в соединениях более двух таблиц
Сейчас мы увидим более предметно, почему использовать EXISTS предпочительнее в тех случаях, когда в результирующую таблицу попадут столбцы лишь из одной таблицы.
Работаем с базой данных "Недвижимость". Скрипт для создания этой базы данных, её таблиц и заполения таблиц данными - в файле по этой ссылке.
Таблица Deal содержит данные о сделках. Для наших заданий в этой таблице будет важен столбец Type с данными о типе сделки - продажа или аренда. Таблица Object содержит данные об объектах. В этой таблице нам понадобятся значения столбцов Rooms (число комнат) и LogBalc, содержащего данные о наличии лоджии или балкона в булевом формате: 1 (да) или 0 (нет). Таблицы Client, Manager и Owner содержат данные соответственно о клиентах, менеджерах фирмы и собственниках объектов недвижимости. В этих таблицах FName и LName соответственно имя и фамилия.
Пример 7. Определить клиентов, купивших или взявших в аренду объекты, у которых нет лоджии или балкона. Пишем следующий запрос, в котором предикатом EXISTS задано обращение к результату соединения двух таблиц:
SELECT cl.* FROM Client cl WHERE EXISTS ( SELECT 1 FROM Deal de JOIN Object ob ON ob.Obj_ID=de.Object_ID WHERE de.Client_ID=cl.Client_ID AND ob.LogBalc=0)
Так как из таблицы Client столбцы выбираются при помощи оператора "звёздочка", то будут выведены все столбцы этой таблицы, в которой будет столько строк, сколько насчитывается клиентов, соответствующих условию, заданному предикатом EXISTS. Из таблиц, к соединению которых обращается вложенный запрос, нам не требуется выводить ни одного столбца. Поэтому для экономии машинного времени извлекается лишь один столбец. Для этого после слова SELECT прописана единица. Этот же приём применён и в запросах в следующих примерах.
Написать запрос SQL с предикатом EXISTS самостоятельно, а затем посмотреть решение
Пример 3. Определить автора (авторов), книги которого выданы пользователю с ID 120, а также с ID 18.
Различия предикатов EXISTS и IN
При первом взгляде на запросы с предикатом EXISTS может возникнуть впечатление, что он идентичен предикату IN. Это не так. Хотя они очень похожи. Предикат IN ведет поиск значений из диапазона, заданного в его аргументе, и если такие значения есть, то выбираются все строки, соответствующие этому диапазону. Результат же действия предиката EXISTS представляет собой ответ "да" или "нет" на вопрос о том, есть ли вообще какие-либо значения, соответствующие указанным в аргументе. Кроме того, перед предикатом IN указывается имя столбца, по которому следует искать строки, соответствующие значениям в диапазоне. Разберём пример, показывающий отличие предиката EXISTS от предиката IN, и задачу, решаемую с помощью предиката IN.
Пример 4. Определить ID пользователей, которым выданы книги авторов, книги которых выданы пользователю с ID 31. Запрос будет следующим:
SELECT Customer_ID FROM Bookinuse WHERE Author IN ( SELECT Author FROM Bookinuse WHERE Customer_ID=31)
Результатом выполнения запроса будет следующая таблица:
User_ID |
120 |
65 |
205 |
Внутренний запрос (после IN) выбирает авторов: Чехов; Ильф и Петров. Внешний запрос выбирает всех пользователей, которым выданы книги этих авторов. Видим, что, в отличие от предиката EXISTS, предикат IN предваряется именем столбца, в данном случае - Author.
Наиболее простые запросы с предикатом SQL EXISTS
В примерах работаем с базой данных библиотеки и ее таблицами "Книга в пользовании" (BOOKINUSE) и "Пользователь" (USER). Пока нам потребуется лишь таблица "Книга в пользовании" (BOOKINUSE).
Скрипт для создания базы данных библиотеки, её таблиц и заполения таблиц данными - в файле по этой ссылке .
Author | Title | Pubyear | Inv_No | Customer_ID |
Толстой | Война и мир | 2005 | 28 | 65 |
Чехов | Вишневый сад | 2000 | 17 | 31 |
Чехов | Избранные рассказы | 2011 | 19 | 120 |
Чехов | Вишневый сад | 1991 | 5 | 65 |
Ильф и Петров | Двенадцать стульев | 1985 | 3 | 31 |
Маяковский | Поэмы | 1983 | 2 | 120 |
Пастернак | Доктор Живаго | 2006 | 69 | 120 |
Толстой | Воскресенье | 2006 | 77 | 47 |
Толстой | Анна Каренина | 1989 | 7 | 205 |
Пушкин | Капитанская дочка | 2004 | 25 | 47 |
Гоголь | Пьесы | 2007 | 81 | 47 |
Чехов | Избранные рассказы | 1987 | 4 | 205 |
Пушкин | Сочинения, т.1 | 1984 | 6 | 47 |
Пастернак | Избранное | 2000 | 137 | 18 |
Пушкин | Сочинения, т.2 | 1984 | 8 | 205 |
NULL | Наука и жизнь 9 2018 | 2019 | 127 | 18 |
Чехов | Ранние рассказы | 2001 | 171 | 31 |
Пример 1. Определить ID пользователей, которым выданы книги Толстого, которым также выданы книги Чехова. Во внешнем запросе отбираются данные о пользователях, которым выданы книги Толстого, а предикат EXISTS задаёт дополнительное условие, которое проверяется в во внутреннем запросе - пользователи, которым выданы книги Чехова. Дополнительным условием во внутреннем запросе является совпадение идентификаторов пользователей из внешнего и внутреннего запросов: Customer_ID=tols_user.Customer_id. Запрос будет следующим:
SELECT Customer_ID FROM Bookinuse AS tols_user WHERE Author='Толстой' AND EXISTS ( SELECT Customer_ID FROM Bookinuse WHERE Author='Чехов' AND Customer_ID=tols_user.Customer_id)
Этот запрос вернёт следующий результат:
Customer_ID |
65 |
205 |
Далее - пример использования NOT EXISTS в запросе, решающем похожую задачу.
Пример 2. Определить ID пользователей, которым выданы книги Чехова, и которым при этом не выданы книги Ильфа и Петрова. Конструкция запроса аналогична конструкции из предыдущего примера с той разницей, что дополнительное условие задаётся предикатом NOT EXISTS. Запрос будет следующим:
SELECT Customer_ID FROM Bookinuse AS cheh_user WHERE Author='Чехов' AND NOT EXISTS ( SELECT Customer_ID FROM Bookinuse WHERE Author='Ильф и Петров' AND Customer_ID=cheh_user.Customer_id)
Этот запрос вернёт следующий результат:
User_ID |
120 |
65 |
205 |
Написать запрос SQL с предикатом EXISTS самостоятельно, а затем посмотреть решение
Пример 3. Определить менеджеров, которые провели сделки с объектами с числом комнат больше 2.
Запросы с предикатом EXISTS к двум таблицам
Запросы с предикатом EXISTS могут извлекать данные из более чем одной таблицы. Многие задачи можно с тем же результатом решить с помощью оператора JOIN, но в ряде случаев использование EXISTS позволяет составить менее громоздкий запрос. Использовать EXISTS предпочительнее в тех случаях, когда в результирующую таблицу попадут столбцы лишь из одной таблицы.
В следующем примере из той же базы данных помимо таблицы BOOKINUSE потребуется также таблица "Пользователь" (CUSTOMER).
Customer_ID | Surname |
18 | Зотов |
31 | Перов |
47 | Васин |
65 | Тихонов |
120 | Краснов |
205 | Климов |
Пример 6. Определить авторов, книги которых выданы пользователю по фамилии Краснов. Пишем следующий запрос, в котором предикатом EXISTS задано единственное условие:
SELECT DISTINCT Author FROM Bookinuse bk WHERE EXISTS ( SELECT * FROM Customer cs WHERE cs.Customer_ID=bk.Customer_ID AND Surname='Краснов')
Результатом выполнения запроса будет следующая таблица:
Author |
Чехов |
Маяковский |
Пастернак |
Как и в случае использования оператора JOIN, в случаях более одной таблицы следует использовать псевдонимы таблиц для проверки соответствия значений ключей, соединяющих таблицы. В нашем примере псевдонимы таблиц - bk и us, а ключ, соединяющий таблицы - User_ID.
Примеры запросов к базе данных "Библиотека" есть также в уроках по операторам GROUP BY, IN и функциям CONCAT, COALESCE.
Продолжаем писать вместе запросы SQL с предикатом EXISTS
Пример 9. Определить собственников объектов, которые были взяты в аренду. Пишем следующий запрос, в котором предикатом EXISTS также задано обращение к результату соединения двух таблиц:
SELECT ow.* FROM Owner ow WHERE EXISTS ( SELECT 1 FROM Object ob JOIN Deal de ON de.Object_ID=ob.Obj_ID WHERE ow.Owner_ID=ob.Owner_ID AND de.Type='rent')
Как и в предыдущем примере, из таблицы, к которой обращён внешний запрос, будут выведены все поля.
Пример 10. Определить число собственников, с объектами которых провёл менеджер Савельев. Пишем запрос, в котором внешний запрос обращается к соединению трёх таблиц, а предикатом EXISTS задано обращение лишь к одной таблице:
SELECT COUNT (*) FROM Object ob JOIN Deal de ON de.Object_ID=ob.Obj_ID JOIN Owner ow ON ob.Owner_ID=ow.Owner_ID WHERE EXISTS ( SELECT 1 FROM Manager ma WHERE de.Manager_ID=ma.Manager_ID AND ma.LName='Савельев')
Все запросы проверены на существующей базе данных. Успешного использования!
Примеры запросов к базе данных "Недвижимость" есть также в уроках по операторам GROUP BY и IN.
вчера я писал некоторые задания, и меня поразило, что я действительно не знаю правильный и принятый способ проверки, существует ли строка в таблице, когда я использую pl / sql.
для примера давайте используем таблицу
Очевидно, Что Я не могу (если нет какого-то секретного метода) что-то вроде:
таким образом, мой стандартный способ решения был:
однако я не знаю, принят ли это способ делая это, или если есть лучший способ проверить, я бы действительно apprieciate, если бы кто-то мог поделиться своей мудростью со мной :)
Я бы не стал вводить обычный код в блок исключений. Просто проверьте, существуют ли какие-либо строки, соответствующие вашему условию, и исходите оттуда:
код IMO с автономным выбором, используемым для проверки наличия строки в таблице, не использует надлежащее преимущество базы данных. В вашем примере у вас есть жестко закодированное значение ID, но это не так, как приложения работают в" реальном мире " (по крайней мере, не в мой мир-ваш может быть другим : -). В типичном приложении вы собираетесь использовать курсор для поиска данных , поэтому предположим, что у вас есть приложение, которое смотрит на данные счета-фактуры и должно знать, существует ли клиент. Основной корпус приложение может быть что-то вроде
один из способов сделать это было бы положить в какой-то синглтон выбрать, как в
но ИМО это относительно медленно и подвержено ошибкам. IMO лучший способ (tm) сделать это-включить его в основной курсор:
этот код рассчитывает на человека.ID объявляется в качестве первичного ключа на PERSON (или по крайней мере, как не NULL); логика заключается в том, что если таблица PERSON внешне присоединена к запросу, а PERSON_ID появляется как NULL, это означает, что строка не была найдена лично для данного CUSTOMER_ID, потому что PERSON.ID должен иметь значение (т. е., по крайней мере, не NULL).
поделиться и наслаждаться.
много способов, чтобы кожа Этот кот. Я помещаю простую функцию в пакет каждой таблицы.
делает ваши чеки очень чистые.
этот оператор дает 0, если нет строк, 1, Если у вас есть хотя бы одна строка в этой таблице. Это намного быстрее, чем выполнение select count(*). Оптимизатор "видит", что для ответа на вопрос необходимо получить только одну строку.
Не найдя на хабре статьи, объединяющей в удобном для чтения виде информацию о методах доступа к данным, используемых СУБД Oracle, я решил совершить «пробу пера» и написать эту статью.
Общая информация
Не углубляясь в детали, можно утверждать что Oracle хранит данные в таблицах, вместе с которыми могут существовать особые структуры данных – индексы, призванные ускорить запросы к таблицам. При выполнении запросов Oracle по-разному обращается к таблицам и индексам – способы доступа к данным в различных ситуациях и являются предметом этой статьи.
Для примеров мы будем использовать следующую таблицу и данные в ней:
Для анализа плана выполнения запроса будем пользоваться следующими средствами:
После создания индекса и использования его в примерах и перед созданием следующего индекса, он должен быть удален. Это можно сделать с помощью следующего запроса:
TABLE FULL SCAN
Данный метод доступа, как следует из названия, подразумевает перебор всех строк таблицы с исключением тех, которые не удовлетворяют предикату where (если таковой есть). Применяется он либо в случае, когда условия предиката отсутствуют в индексе, либо когда индекса нет в принципе. Примеры:
TABLE ACCESS BY ROWID, он же ROWID
- Мы указали идентификатор строки в предикате where;
- ROWID запрошенной записи был найден в индексе;
INDEX FULL SCAN
Данный метод доступа просматривает все листовые блоки индекса для поиска соответствий условиям предиката. Для того чтобы Oracle мог применить этот метод доступа, хотя бы одно из полей ключа должно иметь ограничение NOT NULL, т.к. только в этом случае соответствующая строка таблицы попадет в индекс. Этот метод обычно быстрее чем TABLE FULL SCAN, но медленнее, чем INDEX RANGE SCAN (см. ниже).
INDEX FAST FULL SCAN
Этот метод доступа применяется, когда выполнены все требования для INDEX FULL SCAN, а также все данные, выбираемые запросом, содержатся в индексе и таким образом доступ к самой таблице не требуется. В отличие от INDEX FULL SCAN этот метод может читать блоки индекса в несколько параллельных потоков и таким образом порядок возвращаемых значений не регламентирован. Oracle также не может использовать этот метод для bitmap-индексов.
INDEX RANGE SCAN
INDEX UNIQUE SCAN
Данный метод доступа применяется когда в силу ограничений UNIQUE/PRIMARY KEY, а также условия предиката, запрос должен вернуть ноль или одно значение.
Пример:
INDEX SKIP SCAN
Этот метод доступа используется в случае, если в предикате where не используется первый столбец индекса.
Для примера использования этого метода доступа нам потребуется другая таблица (обратите внимание, что количество строк, данные и т.д. будут зависеть от того, что есть в используемой схеме, и поэтому данный пример может не воспроизвестись сразу):
DISCLAIMER
Утверждения о том, что при определенных условиях cost-based-optimizer (CBO) выберет тот или иной метод доступа, могут быть не совсем справедливыми в отдельных случаях, так как логика определения оптимального метода оптимизатором очень сложна.
Читайте также: