Что такое код fbsql_list_tables

Содержание

Как поместить результат sql-запроса в List<>?

28.02.2020, 16:36

Как передать результат sql-запроса в List<>?
SELECT * FROM Напишите пожалуйста как это реализовать. Если можно на примере.

Как отобразить результат SQL запроса в dataGridView
ла ла ла ла лла аллала . SqlDataAdapter dataAdapter = new.

Поместить результат запроса в созданные поля таблицы
Здравствуйте!)) Я не знаю есть ли такие темы я не нашла) У меня есть Datatable в ней созданы 6.

Поместить данные результата SQL запроса в переменные
Здравствуйте! У меня есть запрос который выбирает некие значения и возвращает их в одной строке.

Михаил Смирнов

руководитель проектного офиса, руководитель проектов

среда, 12 января 2011 г. — www.msmirnov.ru

Стандарты и правила оформления кода T-SQL

Последнее время меня несколько раз спрашивали относительно стандартов и правил оформления кода на T-SQL , поэтому я решил выложить их в открытый доступ.

Для загрузки Стандарты и правила доступны по следующий ссылке на моем сайте: http://www.msmirnov.ru/public/TSQL_Coding_Standards.doc

Либо с ними можно ознакомиться прямо здесь.

Цель документа

  • предоставить общие правила, позволяющие сохранить единый стиль написания кода, облегчив тем самым его понимание всеми участниками команды;
  • ввести базовые правила написания программ, что позволит повысить предсказуемость выполнения программ, а также избежать ошибок при написании программ новыми участниками команды, не знакомыми с внутренними стандартами разработки.

Стили именования

  • Pascal case – первая буква каждого слова в имени идентификатора начинается с верхнего регистра.
  • Camel case – первая буква первого слова в идентификаторе в нижнем регистре, все первые буквы последующих слов – в верхнем.
  • UpperCase – стиль используется только для сокращений, все буквы в имени идентификатора в верхнем регистре.
  • Hungarian notation – перед именем идентификатора пишется его тип в сокращенной форме.

Правила именования объектов базы данных

  • Имена баз данных должны быть оформлены в стиле Pascal , нести осмысленное и логическое назначение и отвечать основному назначению баз данных.
  • Имена таблиц должны быть оформлено в стиле Pascal , нести осмысленное и логическое назначение и отвечать основному назначению таблиц. Не допускается использование пробелов или специальных символов в имени таблиц баз данных, а также символов национального алфавита.
  • Имена полей таблиц должно быть оформлено в стиле Pascal , нести осмысленное и логическое назначение и отвечать основному назначению полей. Не допускается использование пробелов или специальных символов в имени поля таблицы, а также символов национального алфавита.
    Первичные ключи в таблицах должны именоваться как ID.

CREATE CLUSTERED INDEX [idx_TrackDate] ON [dbo] . [EventTracking] ( [TrackDate] )

CREATE INDEX [ndx_HitGUID] ON [dbo] . [EventTracking] ( [HitGUID] ) ON [SECONDARY]

CREATE INDEX [ndx_SessionID_EventTypeID_TrackDate_VisitorID_Others] ON [dbo] . [EventTracking] ( [SessionID] , [EventTypeID] , [TrackDate] , [VisitorID] , [Cost] , [Profit] )

CREATE INDEX [ndx_TrackDate_SessionID] ON [dbo] . [EventTracking] ( [TrackDate] , [SessionID] )

  • Ограничения (constraints) должны быть поименованы следующим образом:

Checks: «Ck» + table name(Pascal case)> + column name (Pascal case)>

  • Имена представлений должны быть оформлены в стиле Pascal , нести осмысленное и логическое назначение и отвечать основному его назначению, или же должно содержать в своем имени имя таблицы или имена таблиц, из которых происходит выборка данных.
    Не допускается использование пробелов или специальных символов в имени представления, а также символов национального алфавита. Обязательно наличие префикса «v» в имени представления.
  • Триггеры должны быть проименованы согласно следующим правилам:
    Префикс «Tr» + TableName> + TriggerType>.

    Если на одной таблице существует более одного триггера определенного типа, то в конце имени триггера добавляется порядковый номер такого триггера.

  • Полное имя объекта с использованием linked_server (четырехкомпонентные имена) формируется следующим образом:
    LINKED_SERVER_NAME.Catalog.schema.ObjectName –
  • [project name, task name, service name] prefix> — наименование проекта (применительно к c# solution) или процесса обработки данных, задачи, сервиса, в работе которой используется данная хранимая процедура или функция. Если это наименование подвергается сокращению, то даже сокращенное наименование должно быть понятным всем разработчикам, а не только разработчику данной процедуры, функции.

    В качестве примера, если это вспомогательная процедура для поддержки логики работы всех остальных, то используется префикс [support], если используется в работе приложения MyApplication – то [myApplication] и т.д.

  • operation name> — название операции, которая применяется к object name> . Если под операцией понимается какое-либо изменение конкретной таблицы, тогда допустимы следующие имена operation name>.
    • Get – возврат только одной строки;
    • Select – возврат 1 и более строк или 1 и более наборов данных;
    • Insert – Вставка в одну или более таблиц. Недопустимо возвращать из такой процедуры набор данных. Return code должен содержать признак 0 или 1 как признак удачного или неудачного выполнения;
    • Update – Обновление данных в одной или более таблиц. Недопустимо возвращать из такой процедуры набор данных. Return code должен содержать признак 0 или 1 как признак удачного или неудачного выполнения;
    • Delete – Удаление данных из одной или более таблиц. Недопустимо возвращать из такой процедуры набор данных. Return code должен содержать признак 0 или 1 как признак удачного или неудачного выполнения;
    • другой> — если операция не попала ни в одну из перечисленных;
      Если под операцией понимается название какого-либо подпроцесса, логической операции, относящейся к группе таблиц – в этом случае необходимо использовать логическое наименование этого подпроцесса, операции – здесь допустимо как существительное (имя операции, например, backup), так и повелительное наклонение глагола (load, upload, remove и пр.).

— если в системе есть процесс MyProcess , которая делает backup каких-либо данных, то возможно такое наименование хранимой процедуры:
[dbo].[ myProcess_MyDataBackup];

— если тот же самый процесс имеет процедуры для добавления новой записи в справочник Dictionary , то возможно следующее наименование:
[dbo].[ myProcess_DictionaryInsert];

— если в пределах упомянутого процесса MyProcess мы хотим логически выделить другой подпроцесс, скажем, работу с какими-либо настройками, то возможно следующее наименование процедуры для операции, например, вставки данных в таблицу SomeTable :

  • В тех случаях, когда возникает необходимость разнести код и логику хранимой процедуры (а эта необходимость возникает, как правило, в случаях разрастания кода), следует разбить исходную хранимую процедуру на несколько, придерживаясь следующего правила именования вновь создаваемых процедур:

    Имя вновь созданной хранимой процедуры формируется как

Оформление t-sql кода при написании user defined functions, stored procedures, triggers и прочих вспомогательных скриптов.

  • Любой код в теле ветвлений, циклов, блоков кода должен быть выделен отступом размером один tab . Размер Tab Size принимается равным 4 пробела. Условие выражений if, while отделяем от данных инструкций символом tab таким образом, чтобы сам t-sql код внутри блоков был выравнен по началу условия ветвления, цикла,

begin
if condition >
begin
condition body

.
>

while while_condition >
begin

while loop body

declare @variable int
set @variable = 1

if @variable > 1
begin
.
end

  • В запросах и подзапросах алиасы таблиц формируются путем сокращения имен участвующих в запросе таблиц, например, Users -> U и т.д.
  • Все комментарии в коде необходимо оформлять, используя «—» + 1 tab symbol перед началом текста самого комментария. Если комментарий идет перед началом блока, сам текст комментария должен начинаться с той же позиции, что и t-sql код блока – т.е. символ начала комментарии «—» должен отставать от позиции начала блока на один tab size, для примера:

— начало комментария
— .
if condition >

begin
condition body >
end

— .
if condition >
begin
condition body
.
>
while while_condition >
begin

while loop body

>
end — while while_condition>

end — if condition>

  • Ко всем встроенным инструкциям t-sql языка программирования в коде хранимых процедур, триггеров, пользовательских функций должен применяться стиль оформления lower-case . Это относится как к select, insert, update, delete, create и прочим инструкциям, так и к выховам extended stored procedures и встроенным функциям ( substring(), getdate() и пр.).

  • При создании хранимой процедуры после ее имени с новой строки после объявления ее параметров должна быть указана в комментариях следующая информация:
    — дата создания,
    — кем была создана процедура
    — ее назначение.
    После каждого какого-либо существенного изменения эти изменения должны быть также зафиксированы в истории работы над кодом хранимой процедуры.
    Комментарий о произведенных изменениях оформляется аналогично оформлению комментария о назначении процедуры (Все комментарии оформляются согласно стилю оформления комментариев) .

ALTER PROCEDURE [dbo] . [support_LostDBRecordsProcessor]
@Dir nvarchar ( 100 ) = ‘c:\logs\LostDBRecords’ ,

@FileExtension nvarchar ( 100 ) = ‘*.txt’ ,

@DateFormat nvarchar ( 10 ) = ‘dmy’

— 2010.02.07 created by smirnov a
— lost db records processor implementation

— This procedure must scan ..\Logs\LostDBRecords directory and execute
— the bulk insert operation for current
— database and after that create .cab archives or create a .log file for a

— corresponding file in case of an error
— 2010.04.06 modified by smirnov a

— sql server version analisys was added. If @version variable equals 2005, we must
— remove the «nooutput» parameter from xp_cmdShell
AS
begin …

ALTER PROCEDURE [dbo] . [support_SearchDDLChanges]
@TableName nvarchar ( 100 ) = null

— 2010.01.01 created by author>
— Данная процедура предназначена для выполнения операции сравнения

— DDL описаний таблиц из указанной базы данных по сравнению с эталонной

— 2010.03.01 modified by changes_author >

— bugfix 0001 — скрипт неправильно работал

— 2010.02.01 modified by changes_author >

— Добавлена функциональность по автоматическому обновлению структуры
— указанной базы данных
— .

— date > modified by author >

— bugfix bug number > or additional description

  • Параметры хранимой процедуры, функции должны быть именованы в стиле Pascal и нести осмысленный логический смысл. Объявление параметра должно начинаться с новой строки с отступом 1 tab size . Если смысл параметра непонятен, после объявления ее типа, значения по умолчанию (если есть) должен быть комментарий о назначении данного параметра. Если комментарий не убирается в пределах окна редактирования по ширине, его необходимо сделать многострочным, применяя следующий стиль оформления:

@FullDatabasePathLogs nvarchar ( 100 ) = ‘dbo’ ,

— Полный путь к базе данных (linked_server.catalog.owner),

— откуда будем брать нормализованные логи для обработки

@FullDatabasePathReports nvarchar ( 100 ) = ‘MyServer.MyDB.dbo’
— Полный путь к базе данных (linked_server.catalog.owner), — на котором будут храниться отчеты — MyServer.MyDB.dbo

  • К выражениям select необходимо применять следующий стиль оформления: имя каждого выражения (операции над column), поля должно быть на отдельной строке; каждое следующее выражение, поле должно начинаться с начальной позиции предыдущего; первое выражение, поле должно быть отделено от select statement отступом 1 tab size , как в примере ниже:

select count (*),
table_01 . column_01 ,

expression ( table_01 . column_02 , operand_01 , operand_02 , ..),

table_02 . column_03
from table_01

join table_01 on table_01.column_01 = table_02.column_01
and boolean expression >
join table_nn on table_nn . column_03 = table_02 . column_03

where table_01.column_03 not in (. )
and where boolean expression 01 >
or where boolean expression 02 >
group by table_01 . column_01,
expression ( table_01 . column_02 , operand_01 , operand_02 , ..),
table_02 . column_03
having count (*) > 10
order by table_01 . column_01
select count (*),
table_01 . column_01 ,
expression ( table_01 . column_02 , operand_01 , operand_02 , ..),
table_02 . column_03
from table_01,
table_02,
table_nn .

  • В выражении insert into список полей в выражении insert указывается одной строкой, если список не убирается в одну строку, переносится так, чтобы список был виден в пределах ширины страницы.

    Into statement должен начинаться с той же позиции, что и список полей из select_list из выражения select .

insert into destination_table > ( field_01 , field_02 , field_03 , field_04 . field_nn )
select count (*),
table_01 . column_01 ,
expression ( table_01 . column_02 , operand_01 , operand_02 , ..),
table_02 . column_03 ,
.

.
select count (*),
table_01 . column_01 ,
expression ( table_01 . column_02 , operand_01 , operand_02 , ..),
table_02 . column_03 ,
.
into destination_table >
.

  • Для операций update, delete применяются те же правила, что и при оформлении select, insert выражений, т.е. операции присваивания над полями таблицы должны быть отделены от set на 1 tab size , каждая операция set в update -выражении должна начинаться с новой строки и начинаться с той же позиции, что и предыдущая. Операнды в операции присваивания должны быть отделены одним пробелом или tab от символа «=» (смотри рекомендации по отступам).

update SourceTable >
set Column_01 > = value01 > ,
Column_02 > = value02 > ,
Column_nn > = valueNN >
where condition_01 >
and condition_02 >

delete from SourceTable >
where condition_01 >
and condition_02 >

  • Выражение create table оформляется таким образом, чтобы описание каждого поля начиналось с новой строки. Желательно форматировать список колонок таким образом, чтобы типы данных находились один над другим, т.е. применяем колоночный стиль оформления.

    Пример:

  • При объявлении курсора select statement должно начинаться с новой строки с той позиции, с которой начинается описание типа переменной курсора. К оформлению select statement применяются все вышеописанные правила.
  • При объявлении в t-sql коде переменных желательно придерживаться табличного стиля оформления, т.е. типы данных переменных должны начинаться с одной определенной позиции, имена переменных аналогично, приблизительно как показано ниже:

Общие рекомендации.

Переменные.
Все переменные необходимо объявлять в начале блока, в котором они используются: метода, цикла, ветвления и пр.

Временные таблицы малого объема сохранять в памяти (вместо # — @), кроме тех случаев, когда это накладывает неприемлемые ограничения.
Т.е. вместо create table #temp ( ID int )
использовать declare @temp table ( ID int )

Требования, обязательные к применению.

  • Необходимо создавать связи для всех таблиц в базе данных.
  • Все таблицы должны быть отражены на соответствующих диаграммах.
  • Поля с допустимым значением NULL допускается использовать только в тех случаях, когда значение NULL несет логическое значение и не может быть заменено другим.
  • При добавлении новых полей допустимость значения NULL определяется в соответствии с предыдущим правилом.
  • Необходимо использовать типы минимально необходимого размера.
  • Для операции select необходимо указывать только те поля, которые требуются в данном запросе.
  • Именование таблиц статусов: Имя статуса(с большой буквы)+Statuses.
  • Идентификаторы записей во всех таблицах должны называться ID .
  • Идентификаторы в статических таблицах (справочниках) не должны быть помечены как identity .
  • Ссылки на идентификаторы других таблиц именуются: Имя таблицы в единственном числе+ID .

  • Для всех запросов, где участвуют таблицы, которые не подвергаются изменению, в запросах select необходимо использовать (nolock) для каждой такой таблицы.

select Keyword , KP . ID
from Keywords ( nolock ),
KeywordParams KP ( nolock )
where Keywords . ID = KP . KeywordID

Исследуем базы данных с помощью T-SQL

Как dba и консультант по оптимизации производительности SQL Server в Ambient Consulting, я часто сталкиваюсь с необходимостью анализа узких мест производительности на экземплярах SQL Server, которые вижу первый раз в жизни. Это может быть сложной задачей. Как правило, у большинства компаний нет документации по их базам данных. А если есть, то она устарела, или же её поиск занимает несколько дней.

В этой статье я поделюсь базовым набором скриптов, раскапывающим информацию о метаданных с помощью системных функций, хранимых процедур, таблиц, dmv. Вместе они раскрывают все секреты баз данных на нужном экземпляре – их размер, расположение файлов, их дизайн, включая столбцы, типы данных, значения по умолчанию, ключи и индексы.

Если вы когда-нибудь пытались получить часть этой информации, с помощью GUI, я думаю вы будете приятно удивлены количеством той информации, которая, с помощью этих скриптов, получается мнгновенно.

Как и с любыми скриптами, сначала проверьте их в тестовом окружении, прежде чем запускать в продакшене. Я бы рекомендовал вам погонять их на тестовых базах MS, таких как AdventureWorks или pubs.

Ну, хватит слов, давайте я покажу скрипты!

Изучаем сервера

Начнём с запросов, предоставляющих информацию о ваших серверах.

Базовая информация

Во-первых, несколько простых @@Функций, которые предоставят нам базовую информацию.

Как долго ваш SQL Server работает после последнего перезапуска? Помните, что системная база данных tempdb пересоздаётся при каждом перезапуске SQL Server. Вот один из методов определения времени последнего перезапуска сервера.

Связанные сервера

Связанные сервера – это соединения, позволяющие SQL Server’у обращаться к другим серверам с данными. Распределённые запросы могут быть запущенны на разных связанных серверах. Полезно знать – является ли ваш сервер баз данных изолированным от других, или он связан с другими серверами.

Список всех баз данных

Во-первых, получим список всех баз данных на сервере. Помните, что на любом сервере есть четыре или пять системных баз данных (master, model, msdb, tempdb и distribution, если вы пользуетесь репликацией). Вы, вероятно, захотите исключить эти базы в следующих запросах. Очень просто увидеть список баз данных в SSMS, но, эти запросы будут нашими «строительными блоками» для более сложных запросов.

Есть несколько путей для получения списка всех БД на T-SQL и ниже вы увидите некоторые из них. Каждый метод возвращает похожий результат, но с некоторыми отличиями.

Последний бэкап?

Стоп! Прежде чем двигаться дальше, каждый хороший dba должен узнать есть ли у него свежий бэкап.

Будет лучше, если вы сразу узнаете путь к файлу с последним бэкапом.

Активные пользовательские соединения

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

Примечание переводчика: это будет работать только в SQL Server 2012 и выше, в предыдущих редакциях, в dmv sys.dm_exec_sessions отсутствовал столбец database_id. Чтобы узнать в каких БД в данный момент работают пользователи, можно воспользоваться sp_who.

Изучаем базы данных

Давайте заглянем поглубже и посмотрим, как мы можем собрать информацию об объектах во всех ваших БД, используя различные представления каталога и dmv. Большинство из запросов, представленных в этом разделе, смотрят «внутрь» только одной БД, поэтому не забывайте выбирать нужную БД в SSMS или с помощью команды use database. Также помните, что вы всегда можете посмотреть в контексте какой БД будет выполнен запрос, с помощью select db_name().

Системная таблица sys.objects одна из ключевых для сбора информации об объектах, составляющих вашу модель данных.

Ниже представлен список типов объектов, информацию о которых мы можем получить (смотрите документацию на sys.objects в MSDN)

Другие представления каталога, такие как sys.tables и sys.views, обращаются к sys.objects и предоставляют информацию о конкретном типе объектов. С этими представлениями, плюс функцией OBJECTPROPERTY, мы можем получить огромное количество информации по каждому из объектов, составляющих нашу схему БД.

Расположение файлов баз данных

Физическое расположение выбранной БД, включая основной файл данных (mdf), и файл журнала транзакций (ldf), могут быть получены с помощью этих запросов.

Таблицы

Конечно, Object Explorer в SSMS показывает полный список таблиц в выбранной БД, но часть информации с помощью GUI получить сложнее, чем с помощью скриптов. Стандарт ANSI предполагает обращение к представлениям INFORMATION_SCHEMA, но они не предоставят информацию об объектах, которые не являются частью стандарта (такие как триггеры, extended procedures и т.д.), поэтому лучше использовать представления каталога SQL Server.

Количество записей в таблице

Если вы ничего не знаете о таблице, то все таблицы одинаково важны. Чем больше вы узнаёте о таблицах, тем больше вы их разделяете на условно более важные и условно менее важные. В целом, таблицы с огромным количеством записей чаще оказывают серьёзное влияние на производительность.

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

Довольно тяжело собрать вручную эту информацию обо всех таблицах. Опять же, если мы будем писать SELECT COUNT(*) FROM TABLENAME для каждой таблицы, нам придётся очень много печатать.

Намного удобнее использовать T-SQL для генерирования скрипта. Скрипт, приведённый ниже, сгенерирует набор инструкций T-SQL для получения количества строк в каждой таблице текущей базы данных. Просто выполните его, скопируйте результат в новое окно и запустите.

Примечание переводчика: у меня запрос не работал, добавил схему к имени таблицы.

sp_msForEachTable

Sp_msforeachtable – это недокументированная функция, которая «проходит» по всем таблицам в БД и выполняет запрос, подставляя вместо ‘?’ имя текущей таблицы. Так же существует похожая функция sp_msforeachdb, работающая на уровне баз данных.

Известно несколько проблем с этой недокументированной функцией, например, использование спецсимволов в именах объектов. Т.е. если имя таблицы или базы данных содержит знак ‘-‘, хранимая процедура, листинг которой ниже, завершится с ошибкой.

Самый быстрый способ получения количества записей – кластерный индекс

Все предыдущие метода использовали COUNT(*), который медленно отрабатывает, если в таблице больше чем 500K записей.

Самый быстрый способ получения количества записей в таблице – получать количество записей в кластерном индексе или куче. Помните, что хоть этот метод и самый быстрый, MS говорит, что информация о количестве записей индекса и реальное количество строк в таблице может не совпадать, из-за того, что на обновление информации требуется хоть и небольшое, но время. В большинстве же случаев, эти значения или одинаковы, или очень-очень близки и вскоре станут одинаковыми.

Поиск куч (таблиц без кластерных индексов)

Работа с кучами – это как работа с плоским файлом, вместо базы данных. Если вы хотите гарантированно получать полное сканирование таблицы при выполнении любого запроса, используйте кучи. Обычно я рекомендую добавлять primary key ко всем таблицам-кучам.

Разбираемся с активностью в таблице

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

Помните, что эта информация из dmv, очищается при каждом перезапуске SQL Server. Чем дольше сервер работает, тем более надёжна статистика. Я чувствую себя намного более уверенно со статистикой, собранной за 30 дней, чем со статистикой, собранной за неделю.

Намного более продвинутая версия этого запроса представлена курсором, собирающим информацию по всем таблицам всех баз данных на сервере. Вообще, я не фанат курсоров из-за их невысокой производительности, но перемещение по разным базам данных – это отличное применение для них.

Примечание переводчика: курсор не отработает, если у вас в списке есть базы данных с состоянием, отличным от ONLINE.

Представления

Представления – это, условно говоря, запросы, хранящиеся в БД. Вы можете думать о них, как о виртуальных таблицах. Данные не хранятся в представлениях, но в наших запросах мы ссылаемся на них точно так же, как и на таблицы.

В SQL Server, в некоторых случаях, мы можем обновлять данные с использованием представления. Чтобы получить представление «только для чтения», можно использовать SELECT DISTINCT при его создании. Данные «через» представление можно менять только в том случае, если каждой строке представления соответствует только одна строка в «базовой» таблице. Любое представление, не отвечающее этому критерию, т.е. построенное на нескольких таблицах, или с использованием группировок, агрегатных функций и вычислений, будет доступно только для чтения.

Синонимы

Несколько раз в моей карьере я сталкивался с ситуацией, когда не мог понять к какой же таблице обращается запрос. Представьте простой запрос SELECT * FROM Client. Я ищу таблицу под именем Client, но я не могу найти её. Хорошо, думаю я, должно быть это представление, ищу представление с именем Client и всё равно не могу найти. Может быть я ошибся базой данных? В итоге выясняется, что Client – это синоним для покупателей и таблица, на самом деле, называется Customer. Отдел маркетинга хотел обращаться к этой таблице как к Client и из-за этого был создан синоним. К счастью, использование синонимов – это редкость, но разбирательства могут вызвать определённые затруднения, если вы к ним не готовы.

Хранимые процедуры

Хранимые процедуры – это группа скриптов, которые компилируются в единственный план выполнения. Мы можем использовать представления каталога, чтобы определить какие ХП созданы, какие действия они выполняют и над какими таблицами.

Добавив простое условие в WHERE мы можем получить информацию только о тех хранимых процедурах, которые, например, выполняют операции INSERT.

Немного модифицировав условие в WHERE, мы можем собрать информацию о ХП, производящих обновление, удаление или же обращающихся к определённым таблицам.

Функции

Функции хранятся в SQL Server, принимают какие-либо параметры и выполняют определённые действия, либо вычисления, после чего возвращают результат.

Триггеры

Триггер – это что-то вроде хранимой процедуры, которая выполняется в ответ на определённые действия с той таблицей, которой этот триггер принадлежит. Например, мы можем создать INSERT, UPDATE и DELETE триггеры.

CHECK-ограничения

CHECK-ограничения – это неплохое средство для реализации бизнес-логики в базе данных. Например, некоторые поля должны быть положительными, или отрицательными, или дата в одном столбце должна быть больше даты в другом.

Углубляемся в модель данных

Ранее, мы использовали скрипты, которые дали нам представление о «верхнем уровне» объектов, составляющих нашу базу данных. Иногда нам нужно получить больше данных о таблице, включая столбцы, их типы данных, какие значения по умолчанию заданы, какие ключи и индексы существуют (или должны существовать) и т.д.

Запросы, представленные в этом разделе, предоставляют средства почти что реверс-инжиниринга существующей модели данных.

Столбцы

Следующий скрипт описывает таблицы и столбцы из всей базы данных. Результат этого запроса, можно скопировать в Excel, где можно настроить фильтры и сортировку и хорошо разобраться с типами данных, использующимися в БД. Так же, обратите внимание на столбцы с одинаковыми именами, но разными типами данных.

Значения по умолчанию

Значение по умолчанию – это значение, которое будет сохранено, если никакого значения для столбца не будет задано при вставке. Зачастую, для столбцов хранящих дату ставят get_date(). Также, значения по умолчанию используются для аудита – вставляется system_user для определения учётной записи пользователя, совершившего определённое действие.

Вычисляемые столбцы

Вычисляемые столбцы – это столбцы, значения в которых вычисляются на основании, как правило, значений в других столбцах таблицы.

Столбцы identity

Столбцы IDENTITY автоматически заполняются системой уникальными значениями. Обычно используются для хранения порядкового номера записи в таблице.

Ключи и индексы

Как я писал ранее, наличие первичного ключа и соответствующего индекса у таблицы – это одна из best practice. Ещё одна best practice заключается в том, что внешние ключи так же должны иметь индекс, построенный по столбцам, входящим во внешний ключ. Индексы, построенные «по внешним ключам» отлично подходят для соединения таблиц. Эти индексы так же хорошо сказываются на производительности при удалении записей.

Какие индексы у нас есть?

Скрипт для поиска всех индексов во всех таблицах текущей БД.

Каких индексов не хватает?

На основании ранее исполнявшихся запросов, SQL Server предоставляет информацию об отсутствующих индексах в БД, создание которых может увеличить производительность.

Не добавляйте эти индексы вслепую. Я бы подумал о каждом из предложенных индексов. Использование включенных столбцов, например, может аукнуться серьёзным увеличением объёмов.

Внешние ключи

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

Пропущенные индексы по внешним ключам

Как я уже говорил, желательно иметь индекс, построенный по столбцам, входящим во внешний ключ. Это значительно ускоряет соединения таблиц, которые, обычно, всё равно соединяются по внешнему ключу. Эти индексы так же значительно ускоряют операции удаления. Если такого индекса нет, SQL Server будет производить table scan связанной таблицы, при каждом удалении записи из «первой» таблицы.

Зависимости

Это зависит… Я уверен, вы слышали это выражение раньше. Я рассмотрю три разных метода для «реверс-инжиниринга» зависимостей в БД. Первый метода – использовать хранимую процедуру sp_msdependecies. Второй – системные таблицы, связанные со внешними ключами. Третий метод – использовать CTE.

sp_msdependencies

Sp_msdependencies – это недокументированная хранимая процедура, которая может быть очень полезна для разбора сложных взаимозависимостей таблиц.

Если мы выведем все зависимости, используя sp_msdependencies, мы получим четыре столбца: Type, ObjName, Owner(Schema), Sequence.

Обратите внимание на номер последовательности (Sequence) – он начинается с 1 и последовательно увеличивается. Sequence – это «порядковый номер» зависимости.

Я несколько раз использовал этот метод, когда мне нужно было выполнить архивирование или удаление на очень большой БД. Если вы знаете зависимости таблицы, значит у вас есть «дорожная карта» — в каком порядке вам нужно архивировать или удалять данные. Начните с таблицы с самым большим значение в столбце Sequence и двигайтесь от него в обратном порядке – от большего к меньшему. Таблицы с одинаковым значением Sequence могут быть удалены одновременно. Этот метод не нарушает ни одного из ограничений внешних ключей и позволяет перенести/удалить записи без временного удаления и перестроения ограничений (constraints).

В SSMS, если вы нажмёте правой кнопкой мыши на имя таблицы, вы сможете выбрать «View Dependencies» и «Объекты, которые зависят от TABLENAME»:

Мы также можем получить эту информацию следующим способом:

Если в SSMS, в окне просмотра зависимостей, выбрать «Объекты которые зависят от TABLENAME», а затем раскрыть все уровни, мы увидим следующее:

Ту же самую информацию вернёт sp_msdependencies.

Так же, в SSMS, мы можем увидеть от каких объектов зависит выбранная таблица.

Следующий запрос, с использованием msdependencies, вернёт ту же самую информацию.

Если вы хотите получить список зависимостей таблиц, вы можете использовать временную таблицу, чтобы отфильтровать зависимости по типу.

Запросы к системным представлениям каталога

Второй метод «реверс-инжиниринга» зависимостей в вашей БД – это запросы к системным представлениям каталога, связанным со внешними ключами.

Использование CTE

Третий метод, для получения иерархии зависимостей – использование рекурсивного CTE.

Заключение

Таким образом, за час или два, можно получить неплохое представление о внутренностях любой базы данных, используя методы «реверс-инжиниринга», описанные выше.

Примечание переводчика: все запросы в тексте (за исключением одного, в тексте он отмечен) будут работать на SQL Server 2005 SP3 и в более поздних редакциях. Текст достаточно объёмный, я старался как мог его вычитать и найти свои ошибки (стилистические, синтаксические, смысловые и прочие), но, наверняка что-то не заметил, напишите мне в личку, пожалуйста, если что-то будет резать глаз.

FORALL и BULK COLLECT — ускоряем запросы в PL/SQL

В Oracle были введены новые средства, значительно повышающие производительность запросов в PL/SQL , — команда FORALL и секция BULK COLLECT . Они объединяются общим термином конструкций массовой обработки ( bulk processing ). Для чего нужны эти конструкции, спросите вы? Как известно, язык PL/SQL тесно интегрирован с SQL -ядром базы данных Oracle. Он является основным языком программирования для Oracle, несмотря на то что теперь в базе данных также может использоваться язык Java. Однако эта интеграция не означает, что выполнение кода SQL из программы PL/SQL не сопряжено с затратами.

В ходе обработки блока программного кода ядро PL/SQL выполняет процедурные команды самостоятельно, а команды SQL передает ядру SQL. Уровень SQL выполняет команды и при необходимости возвращает результаты ядру PL/SQL. Передача управления между ядрами PL/SQL и SQL (рис. 1) называется переключением контекста. Каждое переключение контекста приводит к дополнительным затратам ресурсов. Необходимость в переключении контекста, приводящим к снижению производительности, встречается во многих ситуациях. PL/SQL и SQL тесно интегрированы на синтаксическом уровне, но во внутренней реализации они связаны отнюдь не так тесно, как кажется на первый взгляд.

Рис. 1. Переключения контекста между PL/SQL и SQL

Однако конструкции FORALL и BULK COLLECT позволяют оптимизировать взаимодействие этих двух компонентов; фактически вы приказываете ядру PL/SQL объединить множество переключений контекста в одно переключение, тем самым повышая производительность своего приложения.

Рассмотрим команду FORALL , изображенную на рис. 2. Вместо того чтобы перебирать обновляемые записи в курсорном цикле FOR или в цикле со счетчиком, мы используем заголовок FORALL для определения общего количества итераций. На стадии выполнения ядро PL/SQL «расширяет» команду UPDATE в набор команд для выполнения всех итераций, а затем передает их ядру SQL за одно переключение контекста. Иначе говоря, выполняются те же команды SQL , но за одно обращение к ядру SQL .

Рис. 2. Одно переключение контекста при использовании FORALL

Сокращение количества переключений контекста приводит к значительному ускорению выполнения в PL/SQL команд SQL , выполняющих выборку нескольких строк данных. Давайте поближе познакомимся с конструкциями BULK COLLECT и FORALL .

Ускорение выборки с использованием BULK COLLECT

Синтаксис BULK COLLECT позволяет за одно обращение к базе данных извлечь из явного или неявного курсора несколько строк данных. Выборка данных с помощью запроса с секцией BULK COLLECT сокращает количество переключений контекста между PL/SQL и SQL , благодаря чему работа выполняется быстрее и с меньшими затратами. Рассмотрим следующий фрагмент кода. Нам нужно прочитать сотни строк данных об автомобилях, загрязняющих окружающую среду. Эти данные помещаются в набор коллекций, что значительно упрощает их дальнейший анализ и обработку:

Этот код выполняет поставленную задачу, но далеко не самым эффективным образом. Если, к примеру, таблица transportation содержит 2000 строк, PL/SQL придется выполнить 2000 операций выборки данных из глобальной системной области.

Секция BULK COLLECT способна значительно ускорить выборку. Включив ее в курсор (явный или неявный), вы указываете ядру SQL на необходимость перед возвратом управления PL/SQL связать выходные данные из множества строк с заданными коллекциями. Синтаксис этой секции таков:

Здесь имя_коллекции определяет коллекцию, в которую должны быть помещены выходные данные курсора.

Некоторые правила и ограничения, связанные с использованием секции BULK COLLECT :

  • До выхода Oracle9i секция BULK COLLECT могла использоваться только в статических командах SQL. Последующие версии поддерживают ее применение и в динамическом SQL .
  • Ключевые слова BULK COLLECT могут использоваться только в секциях SELECT INTO, FETCH INTO и RETURNING INTO .
  • Ядро SQL автоматически инициализирует и расширяет коллекции, заданные в секции BULK COLLECT . Заполнение начинается с индекса 1, далее элементы вставляются последовательно (без пропусков), с заменой определенных ранее элементов.
  • Команда select. bulk collect не выдает исключение no_data_found , если при выборке не получено ни одной строки. Наличие данных проверяется по содержимому коллекции.
  • Если запрос не вернул ни одной строки, метод COUNT коллекции возвращает 0.

Рассмотрим эти правила на примерах. Новая версия предыдущего примера, усовершенствованная с помощью секции BULK COLLECT :

Из программы удаляется код инициализации и расширения коллекций.

Для решения задачи не обязательно использовать неявные курсоры — этот пример также можно переписать с использованием явного курсора:

Я также могу упростить свою работу, выполнив выборку в коллекцию записей:

В OraclelOg и последующих версиях компилятор PL/SQL автоматически оптимизирует курсорный цикл FOR , чтобы его производительность была сравнима с производительностью BULK COLLECT . Вам не нужно заниматься явным преобразованием этого кода — если только в теле цикла не выполняются (прямо или косвенно) команды DML . База данных не оптимизирует команды DML в FORALL , поэтому вы должны явно преобразовать курсорный цикл FOR для использования BULK COLLECT . После этого коллекции, заполненные с BULK COLLECT , используются для управления командой FORALL .

Ограничение на количество возвращаемых строк

Для секций BULK COLLECT Oracle поддерживает условие LIMIT , ограничивающее количество строк, выбираемых из базы данных. Его синтаксис таков:

Здесь строки — это литерал, переменная или выражение, возвращающее значение типа NUMBER (иначе инициируется исключение VALUE_ERROR ).

Ограничение очень полезно при использовании BULK COLLECT , потому что оно помогает управлять объемом памяти, используемым программой при обработке данных. Допустим, вам потребовалось запросить и обработать 10 000 строк данных. Вы можете использовать BULK COLLECT для выборки всех записей и заполнения объемистой коллекции, однако такой подход приводит к значительным затратам памяти в PGA . Его выполнение из разных схем Oracle приведет к снижению производительности приложения из-за необходимости выгрузки PGA .

В следующем блоке секция LIMIT используется в команде FETCH , вызываемой в простом цикле:

Обратите внимание: цикл прерывается по проверке значения allrows_cur%NOTFOUND в конце цикла. При выборке данных по одной строке этот код обычно размещается сразу же за командой FETCH . С конструкцией BULK COLLECT так поступать не стоит, потому что при достижении последнего набора строк курсор будет исчерпан (а %NOTFOUND вернет TRUE ), но при этом в коллекции останутся элементы, которые необходимо обработать. Проверьте либо атрибут %NOTFOUND в конце цикла, либо содержимое коллекции непосредственно после выборки:

Недостаток второго решения заключается в выполнении дополнительной выборки, не возвращающей строк (по сравнению с проверкой %NOTFOUND в конце тела цикла).

В Oracle Database 12c появилась возможность использования секции FIRST ROWS для ограничения количества строк, возвращаемых выборкой с BULK COLLECT . В следующем программном блоке используется команда FETCH с секцией LIMIT , которая выполняется в простом цикле. Этот код читает только первые 50 строк, определяемых командой SELECT :

Выборка нескольких столбцов

Как показывают приведенные примеры, секция BULK COLLECT позволяет выбрать из курсора данные нескольких столбцов и поместить их в разные коллекции. Несомненно, было бы элегантнее извлечь набор столбцов в одну коллекцию записей. Такая возможность появилась в Oracle9i Release 2 .

Допустим, нам нужно извлечь из таблицы transportation информацию обо всех машинах с расходом топлива менее галлона на 20 миль. Задача решается с минимальным объемом кода:

До Oracle9i Release 2 при выполнении этого кода было бы выдано исключение ( PLS-00597 ). При извлечении данных из курсора в коллекцию записей также можно использовать секцию LIMIT , ограничивающую количество выбираемых строк.

Использование RETURNING с BULK COLLECT

Вы уже знаете, как использовать секцию BULK COLLECT с явными и неявными курсорами. Ее также можно включить в команду FORALL , чтобы воспользоваться секцией RETURNING .

Секция RETURNING позволяет получить информацию из команды DML . Таким образом, благодаря RETURNING вы обходитесь без дополнительных запросов к базе данных для определения результата только что завершенной операции DML .

Допустим, конгресс принял закон, по которому оклад самых высокооплачиваемых сотрудников компании не должен превышать оклад низкооплачиваемых сотрудников более чем в 50 раз. Общее число сотрудников составляет 250 000 человек. Поскольку руководство компании не намерено уменьшать доходы генерального директора, решено повысить оклады сотрудников, зарабатывающих менее 1/50 части его дохода в 145 миллионов долларов, и урезать оклады всего остального высшего руководства — должен же кто-то компенсировать расходы.

Чтобы пересчитать и обновить такое количество данных, придется изрядно потрудиться. Команда FORALL ускорит работу, однако после выполнения всех расчетов и внесения изменений нам нужно будет вывести отчет со старыми и новыми окладами. В этом нам пригодится секция RETURNING .

Начнем с функции, которая возвращает величину оклада генерального директора:

В главном блоке программы обновления мы объявим ряд локальных переменных и запрос для определения низкооплачиваемых и высокооплачиваемых сотрудников, чья зарплата будет обновлена:

В начале исполняемого раздела определяемые запросом данные помещаются в две коллекции командой FETCH , содержащей секцию BULK COLLECT :

Затем команда FORALL выполняет последовательный перебор элементов коллекции names :

Команда DECODE либо увеличивает оклад сотрудника на 80%, либо уменьшает его. В конце используется секция RETURNING , с помощью которой мы помещаем новые значения в коллекцию new_salaries .

Вот и все. Благодаря секции RETURNING нам не придется выполнять еще один запрос, выбирающий из базы данных новые оклады сотрудников, и можно сразу приступать к формированию отчета:

А вот как выглядит отчет, сгенерированный сценарием onlyfair.sql :

Значения столбцов и выражения, возвращаемые секцией RETURNING команды FORALL , добавляются в коллекции после ранее записанных туда значений. Если же секция RETURNING используется в обычном цикле FOR , предыдущие значения заменяются данными, возвращенными последней командой DML .

Быстрое выполнение операций DML и команда FORALL

BULK COLLECT ускоряет выполнение запросов на выборку. FORALL делает то же самое для операций вставки, обновления, удаления и слияния ( FORALL с командой MERGE поддерживается только в Oracle11g ). FORALL приказывает ядру PL/SQL выполнить массовую привязку всех элементов одной или нескольких коллекций перед отправкой команд ядру SQL.

С учетом основополагающей роли SQL в приложениях на базе Oracle и влияния команд DML на общую производительность команда FORALL , вероятно, является самым важным средством оптимизации в языке PL/SQL .

Итак, если вы еще не используете FORALL , у меня есть для вас и плохие, и хорошие новости. Плохие новости: кодовая база вашего приложения годами не совершенствовалась для использования важнейших нововведений Oracle. Хорошие новости: когда вы начнете использовать FORALL , ваши пользователи будут радоваться очень приятному (и относительно легко достижимому) приросту производительности.

Ниже объясняются основные особенности и нюансы использования FORALL с множеством примеров.

Синтаксис оператора FORALL

Хотя команда FORALL выполняет итеративную обработку (то есть перебирает все строки коллекции), она не является циклом FOR , а потому не имеет ни команды LOOP , ни команды END LOOP . Ее синтаксис выглядит так:

Здесь индекс — целочисленная переменная, неявно объявляемая Oracle; нижняя граница — начальное значение индекса (строка или элемент коллекции); верхняя граница — конечное значение индекса (строка или элемент коллекции); Kомaнда_sql — команда SQL, выполняемая для каждого элемента коллекции; коллекция — коллекция PL/SQL, используемая для выборки индексов в массиве, упоминаемом в командe sql. Конструкции INDICES OF и VALUES OF поддерживаются начиная с OraclelOg . Необязательная секция SAVE EXCEPTIONS указывает FORALL на необходимость обработки всех строк данных с сохранением всех возникающих исключений.

При использовании FORALL необходимо соблюдать следующие правила:

  • Тело команды FORALL должно представлять собой одну команду DML — INSERT, UPDATE, DELETE или MERGE (в Oraclellg и выше).
  • Команда DML должна содержать ссылки на элементы коллекции, индексируемые в команде FORALL. Область видимости переменной индекс ограничивается командой FORALL ; ссылаться на нее за пределами цикла нельзя. Помните, что нижняя граница и верхняя граница не обязаны задавать все множество элементов коллекции.
  • Переменная цикла не должна объявляться явно. Ядро PL/SQL объявляет ее автоматически с типом PLS_INTEGER .
  • Нижняя граница и верхняя граница должны задавать допустимый диапазон смежных индексов для коллекции, используемой в команде SQL . Для разреженных коллекций выдается сообщение об ошибке (ORA-22160).

Соответствующий пример приведен в файле missing_element.sql. Начиная с версии Oracle Database 10g допускается использование синтаксиса INDICES OF и VALUES OF для работы с разреженными коллекциями (содержащими неопределенные элементы между FIRST и LAST ). Эти конструкции рассматриваются позднее в этом моем блоге.

  • До Oracle Database llg ссылки на поля в коллекциях записей в командах DML были запрещены. Вместо этого можно было ссылаться только на строку коллекции в целом, независимо от того, были ли ее поля коллекциями скалярных значений или коллекциями более сложных объектов. Например, следующий код:

в версиях, предшествующих Oracle Database 11g , приводил к выдаче ошибки компилятора (PLS-00436).

Чтобы использовать FORALL в этом случае, приходилось загружать идентификаторы работников и фамилии в две разные коллекции. К счастью, в Oracle Database 11g это ограничение было снято.

  • Индекс коллекции, используемый в команде DML , не мог быть выражением. Например, в следующем сценарии:

компилятор выдавал сообщение об ошибке (PLS-00430).

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

Несколько примеров использования FORALL :

  • Изменение количества страниц для всех книг, коды ISBN которых присутствуют в коллекции isbns_in :

В этом примере изменения сводятся к замене FOR на FORALL , а также удалению ключевых слов LOOP и END LOOP . В этой команде FORALL ядру SQL передаются все записи, определяемые в двух коллекциях. Изменения в поведении представлены на рис. 2.

  • Следующий пример показывает, что команда DML может содержать ссылки на несколько коллекций. В данном случае используются три коллекции: denial , patient_ name и illnesses . При этом индексируются только первые две коллекции, то есть при вызове INSERT передаются отдельные элементы коллекции. В третий столбец health_coverage для каждой вставляемой записи включается коллекция:
  • Использование секции RETURNING в команде FORALL для получения информации о каждой отдельной операции DELETE . Обратите внимание на необходимость использования BULK COLLECT INTO в секции RETURNING :
  • Использование индексов, определяемых в одной коллекции, для определения строк данных коллекции, которые должны использоваться динамической командой INSERT :

Атрибуты курсоров для FORALL

Вы можете использовать атрибуты курсоров после выполнения команды FORALL для получения информации об операции DML , выполняемой в FORALL . Oracle также предоставляет дополнительный атрибут %BULK_ROWCOUNT для получения более детализированной информации о результатах массовой команды DML .

В табл. 1 описаны значения, возвращаемые этими атрибутами для FORALL .

таблица 1. Неявные атрибуты курсоров для команд FORALL

Составной атрибут %BULK_ROWCOUNT , созданный специально для FORALL , обладает семантикой ассоциативного массива или коллекции. База данных помещает в N-й элемент коллекции количество строк, обработанных при N-м выполнении INSERT, UPDATE, DELETE или MERGE команды FORALL . Если операция не затронула ни одной строки, N-я строка содержит нуль.

Пример использования %bulk_rowcount (а также общего атрибута %rowcount ):

Несколько замечаний по поводу работы этого атрибута:

Команды FORALL и %BULK_ROWCOUNT используют одинаковые значения индексов или номера строк коллекций. Например, если коллекция, переданная FORALL , содержит данные в строках с 10 по 200, то псевдоколлекция %BULK_ROWCOUNT также будет содержать определенные и заполненные строки с 10 по 200. Все остальные строки будут неопределенными. Если INSERT влияет только на одну строку (например, при указании списка VALUES ), значение строки в %bulk_rowcount будет равно 1. При этом для команд insert. select значение %bulk_rowcount может быть больше 1.

Значение строки в псевдомассиве %BULK_ROWCOUNT для операций удаления, обновления и вставки с выборкой может быть любым натуральным числом (0 или положительным); эти команды могут изменять более одной строки в зависимости от их условий WHERE .

Поведение ROLLBACK для FORALL

Команда FORALL позволяет передать ядру SQL несколько команд SQL. Это означает, что переключение контекста всего одно — но каждая команда выполняется ядром SQL отдельно от других.

Что случится, если в одной из этих команд SQL произойдет ошибка?

  1. Команда DML , инициировавшая исключение, откатывается от неявной точки сохранения, созданной ядром PL/SQL перед выполнением команды. Изменения во всех строках, модифицированных этой командой, отменяются.
  2. Все предшествующие операции DML в этой команде forall , уже завершенные без ошибок, не отменяются.
  3. Если вы не приняли специальных мер (добавив секцию save exceptions в forall — см. далее), выполнение forall останавливается, и остальные команды вообще не выполняются.

Продолжение после исключений и секция SAVE EXCEPTIONS

Добавляя в заголовок FORALL секцию SAVE EXCEPTIONS , вы приказываете Oracle продолжить обработку даже при возникновении ошибки. База данных «сохраняет исключение» (или несколько исключений, если ошибок было несколько). При завершении команды DML инициируется исключение ORA-24381. Далее в разделе исключений можно обратиться к псевдоколлекции SQL%BULK_EXCEPTIONS для получения информации об ошибке.

Пример с пояснениями:

Если выполнить этот код в режиме SERVEROUTPUT , будут получены следующие результаты:

Другими словами, база данных обнаружила два исключения при обработке команд DML для коллекции. Она не прервала выполнение при первом исключении, а продолжила работу и зарегистрировала второе исключение.

Функциональность обработки ошибок в этом коде описана в следующей таблице.

Управление FORALL для непоследовательных массивов

До выхода Oracle Database 10g коллекция, используемая в команде FORALL , должна была быть плотной или последовательно заполненной. При наличии пропусков между первым и последним значениями в диапазоне из заголовка FORALL , как в следующем коде, выдавалась ошибка:

Сообщение об ошибке выглядит так:

Кроме того, в массиве нельзя пропустить те строки, которые не должны обрабатываться командой FORALL . Эти ограничения часто приводят к написанию дополнительного кода сжатия коллекций, чтобы те удовлетворяли ограничениям FORALL . Чтобы избавить разработчиков от этой раздражающей необходимости, в Oracle Database 10g язык PL/SQL был дополнен конструкциями INDICES OF и VALUES OF для задания части массива, которая должна обрабатываться FORALL .

Начнем с различий между этими секциями, а затем я приведу пару примеров, демонстрирующих их применение.

INDICES OF — эта секция используется в том случае, если у вас имеется коллекция (назовем ее индексным массивом), строки которой определяют, какие строки основного массива (из команды DML FORALL) должны обрабатываться. Иначе говоря, если элемент в позиции N не определен в индексном массиве, то команда FORALL должна проигнорировать элемент в позиции N основного массива.

VALUES OF — секция используется в том случае, если у вас имеется коллекция целых чисел (также называемая индексным массивом), содержимое которой (значения элементов) определяет обрабатываемые позиции основного массива.

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

Допустим, я хочу обновить оклады некоторых работников значением 10 000. В настоящее время таких значений в таблице нет:

Я пишу следующую программу:

Логика программы кратко описана в следующей таблице.

После выполнения кода я выдаю запрос, который показывает, что в таблице были обновлены всего две строки — строка работника с идентификатором 7654 была пропущена, потому что в позиции 100 таблицы индексов элемент не определен:

С конструкцией INDICES OF (строка 20) содержимое индексного массива игнорируется. Важны лишь позиции или номера строк, определенных в коллекции.

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

В этом примере, как и в предыдущем, оклад некоторых работников обновляется значением 10 000 — на этот раз с секцией VALUES OF . В данный момент строк с таким значением в таблице нет:

Программа выглядит так:

Логика программы кратко описана в следующей таблице.

После выполнения кода я снова выдаю запрос, который показывает, что в таблице были обновлены всего две строки — строка работника с идентификатором 7799 была пропущена, потому что «коллекция значений» не содержит элемента, значение которого равно 13067:

Базовые команды SQL часть 1.

Все инструкции SQL начинаются с ключевых слов, таких как SELECT, SHOW, USE, INSERT, DELETE, CREATE. Так же все инструкции обязаны заканчиваться точкой с запятой (;). Именовать инструкции заглавными буквами — это признак хорошего тона при работе с SQL, MYSQL и другими базами данных. В то же время, операторы и инструкции SQL нечувствительны к регистру. Это означает, что SHOW и show имеют одинаковый смысл в операторах SQL. Однако нужно помнить, что регистр имеет значение в названиях таблиц. Поэтому, если вы работаете с MySQL, вам нужно указывать названия таблиц так, как они заданы в базе данных.

Рассмотрим некоторые базовые команды(инструкции) SQL.

Инструкция USE

Для того, чтобы начать взаимодействие с какой-либо базой данных, необходимо подключиться к ней. Для этого существует команда USE . Посмотрим пример:

Oracle SQL List Tables in Database or Schema

Do you need to list all tables in Oracle? There isn’t an SQL list tables command, but you can learn how to do show all tables in this article.

Oracle SQL List Tables

If you’ve forgotten the name of a specific table, or forgotten how to spell a table (was it plural or singular? One word or two with an underscore?), then you can use these queries to show all tables in Oracle SQL.

Oracle has several different built-in views that you can query to find the data you need. You can query any of these views to list all tables in Oracle.

You might not have the privileges to view each of these views, so if one query doesn’t work, try another one.

List Tables Using DBA_TABLES

There isn’t an Oracle show tables command specifically, but the first method is using the dba_tables view. This view shows all of the tables in the database.

To be able to see this data, you need to have database administrator privileges. Otherwise, you’ll get an error message saying the table or view does not exist.

List Tables Using ALL_TABLES

If you can’t use the first method to show all tables, try querying the all_tables view to select all tables in SQL.

This view shows all of the tables in the database that are accessible to the user, whether or not they are the owner. It’s the SQL list tables method that is available to most users.

To see this data, you don’t need any special privileges (other than the ability to select from tables).

List Tables Using USER_TABLES

The final place you can check for a list of all tables in Oracle is the user_tables view. This views shows all of the tables that are owned by the current user or in the current schema.

You don’t need to specify the owner column because it’s always the same value.

To see this data, you don’t need any special privileges (other than the ability to select from tables).

List Tables and Views

If you’re not sure if the object you’re querying is a table or a view, you can also query the dba_views, all_views, or user_views objects.

These queries perform a UNION ALL to show you a list of all tables and views in the Oracle database.

List tables in all databases in SQL Server instance

Table of Contents:

Query below lists all tables from all databases on SQL Server instance

Query

Columns

  • database_name — name of the database within schema resides
  • schema_name — name of the schema
  • table_name — name of the table
  • One row represents one table in database
  • Scope of rows: all tables from all schemas and all databases on SQL Server instance
  • Ordered by database name, schema name, table name

Sample results

You could also get this

Get this interactive HTML data dictionary in minutes with Dataedo.

Informix SQL — список всех полей и таблиц

Informix iSQL имеет команду » info tables; «, которая отображает все таблицы.
Синтаксис для просмотра полей и их соответствующих типов данных » info columns for table; «

Есть ли аналогичная команда, которая показывает table.field для всех таблиц и всех полей?

Использование предпочтительной записи JOIN:

или старомодное обозначение join-in-where-clause:

Предполагая, что вы используете достаточно недавнюю версию IDS, вы можете заказать по столбцам, не указанным в списке выбора. Если вы получаете жалобы, добавьте столбцы упорядочения в список выбора.

Критерий соединения очевиден; tabtpe = ‘T’ перечисляет только таблицы, а не представления, синонимы и другие подобные элементы, перечисленные в systables; tabid >= 100 отображает только таблицы, созданные явно в базе данных, а не в системном каталоге.

APPS-ORACLE.RU

Вложенные таблицы (Nested Tables)

Вложенные таблицы

Вложенную таблицу можно рассматривать как одномерный массив, в котором индексами служат значения целочисленного типа. Вложенная таблица может иметь пустые элементы, которые появляются после их удаления встроенной процедурой DELETE. Количество элементов может динамически увеличиваться.

Максимальное число строк вложенной таблицы составляет 2 гигабайта.

Наиболее важным отличием между вложенными и индексированными таблицами является то, что вложенные таблицы являются постоянной формой коллекции.

Свойства вложенных таблиц

  • Могут использоваться как в SQL (тип столбца в таблице), так и в PL\SQL коде
  • Содержат однородные данные, т.е. все строки имеют одинаковую структуру данных
  • Требуется инициализация, при использовании в PL\SQL
  • Порядок элементов не зафиксирован
  • Используется память PGA (для всех 3 типов коллекций)
  • При попытке чтения элемента с несуществующим индексом -> исключение NO_DATA_FOUND

Использование вложенных таблиц в SQL

Создаём вложенную таблицу с символьным типом данных

Создаём таблицу CHAIRS, в которой присутствует колонка CHAIR_NAME использующая вложенную таблицу

Вставляем данные в таблицу

Просматриваем данные в таблице

Отобразим все результаты используя функцию TABLE

Используя оператор THE отобразим данные только из вложенной таблицы

Использование вложенных таблиц в PL\SQL

Пример pl\sql кода с использование вложенных таблиц:

  • инициализация, используя конструктор
  • добавление элементов через метод EXTEND
  • использование методов FIRST и LAST

Как поместить результат sql-запроса в List<>?

28.02.2020, 16:36

Как передать результат sql-запроса в List<>?
SELECT * FROM Напишите пожалуйста как это реализовать. Если можно на примере.

Как отобразить результат SQL запроса в dataGridView
ла ла ла ла лла аллала . SqlDataAdapter dataAdapter = new.

Поместить результат запроса в созданные поля таблицы
Здравствуйте!)) Я не знаю есть ли такие темы я не нашла) У меня есть Datatable в ней созданы 6.

Поместить данные результата SQL запроса в переменные
Здравствуйте! У меня есть запрос который выбирает некие значения и возвращает их в одной строке.

Илон Маск рекомендует:  Что такое код asp hcdostaticcompression
Понравилась статья? Поделиться с друзьями:
Кодинг, CSS и SQL