Как создать файл sql
Перейти к содержимому

Как создать файл sql

  • автор:

Технология SQL-файл, препроцессор для T-SQL, “бок-о-бок” файлы и др

Завершив в недавнем прошлом очередную доработку своей легковесной технологии SQL-файл, применяемой для эффективной трансляции файлового SQL-кода в базу данных, автор данной статьи решил в очередной раз представить (в этой заметке теперь, на популярном ресурсе) свои реализованные, хотя бы отчасти, идеи касательно программирования MSSQL, а так же некоторые соображения относительно применения SQL вообще. Автор полагает, что несмотря на форму предлагаемой им частной реализации SQL-файл (для MSSQL), лежащая в основе подхода концепция имеет определённую силу и смысл.

Введение

Язык SQL (его основа), а также средства поддержки SQL придумывались давно. Классические серверы реляционных БД, как правило, не допускают прямого размещения файлов исходного кода запросов на сервере; например, в специальных директориях БД, подобно тому, как, к примеру, на веб-сервере можно выложить JS, HTML, CSS и т. п. (В реляционной СУБД всё весьма консервативно, присутствует множество серьёзных ограничений.) Вместо этого клиент SQL-сервера имеет доступ к механизму манипулирования объектами БД и сервера (путём отправки пакетов инструкций SQL). С помощью специальной (как правило графической) консоли БД создаётся подобие редактирования исходников сохраняемых в БД объектов. Собираясь же по определённым причинам переходить (всецело) на файловую базу для хранения скриптов Вы можете обнаружить недостаточную поддержку работы с подобными файлами-скриптами со стороны клиентских инструментов разработки SQL. Представленная же здесь (в статье) легковесная технология SQL-файл ориентирована как раз на файловую базу для размещения исходников, в директориях и файлах SQL, для SQL-объектов БД: таблицы (скрипты их создания), индексы, ограничения (constraint), хранимые процедуры (ХП), функции и т. п.

Описывая в общих чертах и отдельных деталях конкретное исполнение идей, статья, в то же время, содержит элементы некоторого вымысла (фантастики), нацеленные на обретение теоретического видения касательно применения различных диалектов языка SQL, в плане предлагаемого общего знаменателя (для доступа к SQL-запросам), отражённого здесь путём привнесения соответствующих терминов: Заголовок SQL-запроса (формат, подлежащий унификации), Компилируемый временный запрос к SQL-серверу (допускает определённую унификацию) и прочее. Непривычный взгляд на взаимодействие с SQL-сервером позволяет, однако, не быть обязанным к общению с т. н. большой ORM (Object-Relational Mapping), иметь свободу интенсивно задействовать программирование внутри базы данных (функции, вспомогательные ХП и прочее), при этом логически разделяя запрос к веб-серверу на универсальные части-составляющие: (1) Составляющая SQL-сервера (условно открытый SQL-заголовок + реализация); (2) Составляющая веб-сервера (декларация API + имплементация); (3) Клиентская составляющая (в основном определения для использования запроса). Наличие разнородных сред и языков программирования для исполнения одного, казалось бы простого, запроса клиента создаёт препятствие для гладкой его реализации. Сложности же, вызываемые двойственной природой реализуемого запроса (SQL-сервер + веб-сервер) навряд ли подлежат существенному устранению, учитывая даже применение, так скажем, идеальной (максимальной) ORM.

В последующих текстах присутствует определённое количество специфических наименований, а так же сверхкоротких фрагментов из соответствующих скриптов и конфигурационных файлов (т. н. инишник-и, батч-и и т. п.). Можно особо не заострять на них внимание; они тут изобильно приводятся в основном с той целью, чтобы показать, что поведение т. н. SQL-трансляции (файлы => объекты SQL) подлежит конфигурированию, настраивается и доступно для управления. Так же, не следует ассоциировать применение файлового коммандер-а, процессора CMD и т. п. с какой бы то ни было отсталостью. Автономный проект (компиляция) SQL-файл, по ощущениям автора имеет, в каком-то смысле, схожие черты с автономными проектами в универсальной кроссплатформенной среде разработки VS-Code (конфигурирование, задачи, запуски и т. п.). Файловый же коммандер (здесь это, как правило, Far Manager) вкупе со сценариями обработки (здесь это в основном CMD) хорошо подходят для реализации автономного, независимого от привязок к фирменным IDE, компилируемого проекта.

Касательно же недостатков интеграции SQL-файл с Far Manager, стоит отметить отсутствие какой-либо функции автодополнения при редактировании SQL, в отличие, например, от задействования технологии IntelliSense в Visual Studio. В SQL-файл подобное редактирование (с “равнением” на коллекцию имён из базы данных в данном случае) доступно через т. н. IDE-стартер SSMS.cmd, что, однако, также имеет свои ограничения, выражающиеся, в первую очередь, в “замораживании” всего окружения (переменные среды) на моменте старта IDE (и не только это).

Минимальная формулировка назначения и замысла SQL-файл:

Когда-то, презентуя впервые свою методику взаимодействия с СУБД SQL Server, автор данной статьи писал (в прошлом) примерно следующее:

Добрый день, уважаемые разработчики SQL!

Позвольте представить Вашему вниманию самодельную легковесную технологию, именуемую как SQL-файл (или же SQL в файлах), — для MSSQL и T-SQL. Данная методика успешно применялась в течение достаточно долгого периода для программирования БД расчёта квартирной платы (город Воронеж). Технология базируются на известной утилите SQLCMD и командном процессоре CMD. В качестве IDE (командный пульт SQL + оперативный редактор) эффективно применяется Far Manager 3, со вспомогательными простейшим плагином и макросами. (Так же, возможно задействование других редакторов SQL, помимо встроенного в FAR.)

Идея состоит в том, чтобы поддерживать исходный код и/или вспомогательные скрипты в виде SQL-файлов в директориях, транслируя их в базу данных, по отдельности либо группами. Используя утилиту $SQLTRANS и соответствующие шаблоны, можно настроить трансляцию (генерацию) большого количества объектов БД, работающую, так сказать, на раз-два-три. За один приём, например, возможно обновить активную составляющую программы в базе (процедуры, функции, представления, …), или, скажем, создать табличную структуру (и наполнить её некоторым количеством необходимых данных). При умелом обращении подсистема (активные объекты) может вполне свободно корректироваться даже на работающей программе/службе.

Обзорное минимальное описание SQL-файл доступно на страницах Handicraft-CODE (англ. язык): https://handicraft.remelias.ru/sdk/sql_file.html (Handicraft-CODE :: Handicraft-SDK :: SQL-file technology); https://handicraft.remelias.ru/sdk/sql_tools.html (Handicraft-CODE :: Handicraft-SDK :: CMD-utilities :: SQL-tools).

А так же, см. скриншот-ы: https://handicraft.remelias.ru/sdk/sql/screenshots_1.html; https://handicraft.remelias.ru/sdk/sql/screenshots_2.html; https://handicraft.remelias.ru/sdk/sql/screenshots_3.html.

Вместе с командными утилитами и шаблонами предлагается возможное (опционально) использование так называемого Усиленного Transact-SQL, с препроцессором (на базе переменных среды), представленного множеством соответствующих импорт-определений и широким набором хелпер-объектов прилагаемой библиотеки SQLAUX (полезные программатик-и).

: : : : : : : : : :

Спасибо за внимание!

I. Предыстория (что не так с языком Transact-SQL, расширения SQLCMD и др.)

Автор данной статьи в течение долгого времени имел дело с СУБД MS SQL Server. Когда-то в прошлом к автору пришло осознание того, что режим исполнения инструкций (Control Flow) в языке Transact-SQL, который MSSQL предлагает по умолчанию (режим), не является надёжным. Без использования специальных блоков-обёрток для перехвата исключения — необходимо везде-везде проверять состояние последней ошибки @@ERROR . Исходя из желания использовать эффективный (полуавтоматический, с исключениями) контроль ошибок, а так же и по другим (тоже веским) причинам, в течение длительного времени вырабатывалась и применялась интересная технология SQL-файл, основанная на расширениях SQLCMD. SQL-код хранится в файлах, организованных специальным образом в виде SQL-проекта (в дереве вложенных файловых папок). Для трансляции файлов в базу данных применяется легковесный обработчик — препроцессор-конкатенатор скриптов: SqlCatPP (EXE) в связке с вызывающей его обёрткой $SqlTrans (CMD). Так же, подразумевается использование вспомогательной библиотеки элементарных хелпер-объектов и констант (вида $(<ПеременнаяСреды>) ) SQLAUX (в приложение к основному транслятору/препроцессору). В качестве редактора SQL в настоящее время, помимо встроенного редактора Far Manager, а так же одного из GUI-редакторов для простого текста (см. в загрузках), доступно так же использование SQL Server Management Studio (+Environment), в немного ограниченном, однако, виде (см. устройство SSMS.cmd в загрузках).

II. Технология SQL-файл на страницах https://handicraft.remelias.ru/

Вот ссылки на страницы автора статьи (минимальное описание + картинки и примеры):

Ещё картинки (вспомогательное применение SQL-файл, бок-о-бок файлы SQL/C# и др.):

Утилита SQL*Plus. Создание и выполнение сценариев.Часть 2

Утилита SQL*Plus позволяет запоминать команды, операторы SQL и блоки PL/SQL в командных файлах (или сценариях ). В дальнейшем эти командные файлы, — текстовые файлы базовой операционной системы — можно выполнять, загружать и редактировать.

Создавать командные файлы можно вне среды SQL*Plus с помощью любого текстового редактора либо в среде SQL*Plus с помощью представленных выше команд редактирования или вызова внешнего редактора. Рассмотрим средства SQL*Plus — сохранение буфера SQL в файле — команду SAVE , и вызов внешнего редактора для редактирования буфера SQL — команду EDIT .

Команда SAVE позволяет сохранить в файле содержимое SQL-буфера и имеет следующий синтаксис:

<команда SAVE> ::= SAV [ E ] <имя файла>[ . <расширение>] [<режим записи>] <режим записи> ::= CRE [ ATE ] / REP [ LACE ] / APP [ END ]

Команда SAVE по умолчанию (или в режиме записи CREATE ) создает файл с указанным именем и записывает в него содержимое буфера. В режиме REPLACE содержимое существующего файла заменяется содержимым буфера или файл создается. В режиме APPEND содержимое буфера дописывается в конец указанного файла.

Если расширение не указано, предполагается стандартное расширение ( SQL или заданное командой SET SUFFIX ). Если имя файла совпадает с режимом записи, расширение указывать обязательно.

Команда SAVE добавляет в командный файл строку, содержащую символ косой черты ( / ).

Команда EDIT позволяет вызвать текстовый редактор базовой операционной системы для редактирования указанного файла или содержимого SQL-буфера. Она имеет следующий синтаксис:

<команда EDIT> ::= ED [ IT ] [<имя файла>[ . <раширение>]]

Если расширение не указано, предполагается стандартное расширение ( SQL или заданное командой SET SUFFIX ). Файл для редактирования ищется в текущем рабочем каталоге. Если в нем такой файл не найден, он создается. При вызове без параметров содержимое буфера помещается в файл afiedt.buf в текущем рабочем каталоге, а затем этот файл загружается в текстовый редактор. Это стандартное имя можно переопределить с помощью команды SET EDITFILE . Если команда вызвана без параметров, а SQL-буфер пустой, выдается следующее сообщение об ошибке:

Если редактировался SQL-буфер, после завершения работы редактора содержимое соответствующего файла автоматически загружается в буфер. При этом последний символ последней строки (если она не пустая) усекается.

Имя вызываемого текстового редактора содержится в пользовательской переменной SQL*Plus _EDITOR . Значение этой переменной можно задать с помощью команды DEFINE . Если ее значение не задано, используется стандартный редактор операционной системы (Notepad в Windows; задаваемый переменной среды EDITOR или ed в UNIX).

В любой момент в ходе работы с SQL*Plus можно загрузить содержимое любого текстового файла в буфер SQL. Для этого используется команда GET со следующим синтаксисом:

<команда GET> ::= GET <имя файла>[ . <расширение>] [<режим выдачи>] <режим выдачи> ::= LIS [ T ] / NOL [ IST ]

Если расширение не указано, предполагается стандартное расширение ( SQL или заданное командой SET SUFFIX ).

Загружаемый файл должен содержать один оператор SQL или блок PL/SQL. SQL-оператор не должен завершаться точкой с запятой ( ; ). Обычно загружаются командные файлы, созданные с помощью команды SAVE — они автоматически удовлетворяют данным условиям.

Учтите, что команды собственно SQL*Plus в SQL-буфер не попадают , и если они окажутся в файле, загруженном с помощью команды PUT , то при выполнении содержимого буфера будут выданы сообщения об ошибках — команды SQL*Plus не являются операторами SQL ! Также ошибка выдается, если файл содержит несколько операторов SQL или PL/SQL-блоков.

По умолчанию и в режиме LIST содержимое загруженного файла выдается на экран. Подавить выдачу содержимого командного файла позволяет режим NOLIST .

Хотя команда GET и позволяет загрузить содержимое файла в SQL-буфер, откуда оно в дальнейшем может быть выполнено с помощью команды / , этот способ не является универсальным, так как накладывает существенные ограничения на содержимое командного файла (см. выше). Для загрузки и выполнения командных файлов, содержащих любое количество команд SQL*Plus, SQL-операторов и PL/SQL блоков, используется команда START и ее сокращенные варианты, @ и @@ .

Команда START имеет следующий синтаксис:

Файл, передаваемый команде START по имени (или по ссылке на Web-сайт в Oracle9i на платформе Windows), может содержать любые команды, которые можно вводить в диалоговом режиме. Если расширение в имени файла не указано, предполагается стандартное расширение (SQL или заданное командой SET SUFFIX ).

Указанный по короткому имени файл будет сначала искаться в текущем каталоге, затем — в каталогах, входящих в стандартный путь поиска (обычно задается переменной среды SQLPATH ).

При вызове командного файла можно передавать параметры в виде аргументов командной строки. Утилита SQL*Plus подставляет значение аргументов командной строки вместо позиционных параметров командного файла ( &1 , &2 и т.д.). Первый аргумент подставляется вместо параметра &1 , второй — вместо &2 , и так далее.

Рассмотрим простой пример:

Утилита SQL*Plus поддерживает две сокращенные формы команды START : @ и @@ . Команда @ функционально аналогична команде START :

Примечание Команда @ удаляет завершающий символ SQLTERMINATOR (по умолчанию — точка с запятой; см. SET SQLTERMINATOR ) из последней команды. Если этот символ необходим, надо его удвоить.

Команда @@ работает так же, как и @ , но дополнительно ищет командный файл в том же каталоге, что и командный файл, в котором она вызвана. Поэтому данная команда используется для вложенных вызовов командных файлов.

Рассмотрим типичное использование команды @@ в файле ex_all.sql :

При его запуске с помощью команды @ из того же каталога будут выполнены все перечисленные в нем командные файлы, а результаты их работы будут выданы на экран:

Если в ходе выполнения командного файла происходит ошибка Oracle, может потребоваться вернуть соответствующий код возврата базовой операционной системе. Это позволяет сделать команда WHENEVER SQLERROR со следующим синтаксисом:

<команда WHENEVER SQLERROR> ::= WHENEVER SQLERROR <реакция на ошибку> <реакция на ошибку> ::= <выход>
/ COMMIT
/ ROLLBACK
/ CONTINUE <действие перед продолжением> <выход> ::= EXIT [<код возврата>][<завершение транзакции>] <код возврата> ::= SUCCESS / FAILURE / WARNING
/ <целое число> / <переменная> / : <связываемая переменная> <завершение транзакции> ::= COMMIT / ROLLBACK <действие перед продолжением> ::= COMMIT / ROLLBACK / NONE

В ответ на ошибку в сценарии можно, тем самым, выйти из SQL*Plus и вернуть необходимый код возврата операционной системе, зафиксировать или откатить выполненные изменения, либо проигнорировать ошибку и продолжить выполнение сценария (с фиксацией или откатом изменений при необходимости).

В командных файлах можно вводить и использовать комментарии трех видов:

  • однострочные комментарии, задаваемые командой SQL*Plus REMARK ;
  • одно- или многострочные комментарии SQL /* . */ ;
  • однострочные комментарии ANSI/ISO — .

Команда REMARK имеет следующий простой синтаксис:

<команда REMARK> ::= REM [ ARK ] [<любые символы до конца строки>]

Команда REMARK должна быть первой командой в строке. Задаваемый ею комментарий продолжается до конца строки.

Комментарии /* . */ можно вводить в виде отдельных строк в командном файле, в строке оператора SQL или PL/SQL-блока. После пары символов начала комментария ( /* ) обязательно должен идти пробел, иначе косая рассматривается как команда выполнения содержимого SQL-буфера. Такие комментарии не могут быть вложенными. Комментарий, введенный в командной строке SQL*Plus, не попадает в буфер SQL.

Комментарии в виде — . можно вводить в конце строки с часть оператора SQL или PL/SQL-блока (такой комментарий продолжается до конца строки). Таким комментарием нельзя завершать команду SQL*Plus — он должен быть первой командой в строке.

При размещении комментариев в командных файлах необходимо придерживаться ряда простых правил, связанных с особенностями работы утилиты SQL*Plus:

  1. Не размещайте комментарии среди первых нескольких ключевых слов оператора SQL.
  2. Не размещайте комментарии после символа-завершителя команды SQL*Plus (точки, точки с запятой или косой).
  3. Не размещайте символы-завершители команды в конце строки комментария или после комментария в SQL-операторе или PL/SQL-блоке.
  4. Не используйте в комментариях метасимвол & , — утилита SQL*Plus потребует ввести значение параметра, проинтерпретировав слово после символа & в качестве имени параметра.

Рассмотрим пример командного файла, использующего все виды комментариев:

Вот результат его выполнения:

Утилита SQL*Plus поддерживает многочисленные установки (см. раздел «Настройка среды SQL*Plus»), которые имеет смысл запоминать между сеансами. Для этого используется команда STORE :

Эта команда записывает значения переменных среды SQL*Plus в командный файл базовой операционной системы:

Вот примерное содержимое полученного файла f:\env.sql , созданного в SQL*Plus 8.1.6:

Листинг 1. Типичные установки среды SQL*Plus.

Режим записи указывает, будет ли файл просто создан ( CREATE , используется по умолчанию), переписан, если существует ( REPLACE ), или же значения установок среды SQL*Plus будут добавлены в конец существующего файла ( APPEND ). Полученный командный файл может быть выполнен командой START или ее сокращенными формами ( @ , @@ ).

Утилита SQL*Plus позволяет сбросить результаты выполнения команд в файл и распечатать их на стандартном принтере. Такой сброс называют спулингом . Для этого используется команда SPOOL (управляющая спулингом) со следующим синтаксисом:

<команда SPOOL> ::= SPO [ OL ] [<файл или команда>] <файл или команда> ::= <имя файла>[ . <расширение>] / OFF / OUT

Команда SPOOL выдает результаты выполнения команд SQL*Plus в указанный файл и, возможно, на стандартный принтер, независимо от их отображения на экране. При вызове без параметров команда выдает состояние спулинга. Если не указано расширение имени файла, используется стандартное расширение (обычно, LST или LIS ).

Команда OFF прекращает спулинг. Команда OUT прекращает спулинг и посылает файл на стандартный принтер базовой операционной системы.

Чтобы сбрасываемые в файл результаты не выдавались на экран, необходимо выполнить команду SET TERMOUT OFF .

В листинге 2 представлен пример сценария SQL*Plus, использующего команду SPOOL для выдачи исходного текста хранимой программной единицы в файл с соответствующим именем. Этот файл, в свою очередь, является сценарием, пригодным для повторного создания хранимой программной единицы. Такой прием, — генерация командных файлов в результате выполнения командных файлов — часто используется опытными администраторами баз данных при работе с SQL*Plus.

Вызывать данный сценарий можно, например, так:

Примечание На платформе Windows при использовании оконной версии SQL*Plus ( sqlplusw.exe ) файл, указанный в команде SPOOL , по умолчанию (если не задан полный путь) создается в каталоге %ORACLE_HOME%\bin . Вряд ли это подходящее место для таких файлов.

Мы еще вернемся к сценарию getcode.sql в следующих разделах, посвященных параметрам и настройке среды SQL*Plus.

Утилита SQL*Plus позволяет создавать командные файлы, запрашивающие у пользователя параметры и подставляющие их значение в команды по ходу выполнения. Для этого используются т.н. пользовательские переменные . Такие переменные определяются в командном файле, в частности, с помощью команды DEFINE . На пользовательские переменные можно ссылаться в командах, предваряя их имя символами & или && . Конструкцию & <имя переменной> называют подставляемой переменной .

Команда DEFINE позволяет определить пользовательскую переменную строкового типа и задать ей значение типа CHAR , либо получить значение определенной или всех пользовательских переменных. Эта команда имеет следующий синтаксис:

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

При вызове без значения, команда DEFINE выдает значение переменной. Команда DEFINE без параметров выдает значение всех пользовательских переменных SQL*Plus, включая системные. Рассмотрим пример:

Как видите, утилита SQL*Plus автоматически определяет ряд системных пользовательских переменных.

Для удаления как явно определенной пользовательской переменной, так и параметра командной строки сценария (см. описание команды START выше) используется команда UNDEFINE . После применения этой команды к переменной значение переменной теряется, и она становится неопределенной. Команда UNDEFINE имеет следующий простой синтаксис:

Продолжая предыдущий пример:

Подставляемую переменную можно использовать в любом месте команды SQL*Plus или SQL-оператора, кроме первого слова в командной строке (по первому слову утилита SQL*Plus определяет тип команды). Когда SQL*Plus встречает в командной строке подставляемую переменную, она подставляет значение этой переменной (запрашивая его, если переменная не определена). Используются подставляемые переменные для получения более гибких, интерактивных сценариев SQL*Plus. Рассмотрим простой пример:

Как видите, если в команде встречается не определенная явно ранее подставляемая переменная, SQL*Plus запрашивает ее значение. Затем на экран выдается вид команды до и после подстановки всех значений (это можно отключить с помощью команды SET VERIFY OFF ), и команда выполняется. В нашем примере мы подставили в команду конкретную функцию агрегирования, имя столбца, по которому выполняется агрегирование, и имя таблицы.

Обратите внимание, что если необходимо вставить значение подставляемой переменной перед не пробельным символом, необходимо указать точку ( . ) после имени переменной.

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

SQL*Plus читает данные с клавиатуры, даже если входной и выходной потоки терминала перенаправлены в файлы. Если же сценарий запущен в пакетном режиме, данные читаются из соответствующего файла.

Если введенное значение совпадает с подставляемой переменной (начинается с & ), то (по крайней мере, в версии 8.1.6) выдается сообщение об ошибке:

Если использовать одну и ту же подставляемую переменную с символом & в одной команде несколько раз, значение будет запрашиваться каждый раз заново:

Чтобы значение переменной запрашивалось только один раз, используется подстановка с двумя амперсантами ( && ):

Подстановка переменных выполняется и для позиционных параметров, переданных при вызове сценария. На эти параметры можно ссылаться как на &1 , &2 и т.д. Если значение для них в командной строке не передано, SQL*Plus запрашивает значения при вызове сценария. Подстановка позиционных параметров выполняется только при вызове сценария командами START (или ее сокращенными формами @ , @@ ).

Подставляемые переменные нельзя использовать в командах редактирования буфера SQL ( APPEND , CHANGE , DEL , INPUT ) и в других командах, где эта подстановка «не имеет смысла», в частности, в комментариях. Команды редактирования буфера считают символ & обычным и используют его буквально (см. пример выше).

В табл. 13 представлены системные установки, влияющие на подстановку пользовательских переменных.

Таблица 13. Системные установки, влияющие на подстановку переменных.

Установка Описание
SET DEFINE Задает символ подстановки (вместо стандартного — & ) и позволяет включать и отключать подстановку.
SET ESCAPE Задает символ маскировки, позволяющий маскировать символ подстановки. Стандартным символом маскировки является обратная косая ( \ ).
SET VERIFY Позволяет включать и отключать выдачу каждой строки команды до и после подстановки пользовательских переменных.
SET CONCAT Задает символ, отделяющий имя подставляемой переменной от следующей непосредственно за ним строки. По умолчанию используется точка ( . ).

Стандартный механизм запроса значений пользовательских переменных дает ограниченные средства взаимодействия с пользователем — ввод значений в ответ на стандартные приглашения. Утилита SQL*Plus позволяет управлять выдачей сообщений и запросом значений переменных.

Для выдачи на экран произвольного текста используется команда PROMPT со следующим синтаксисом:

<команда PROMPT> ::= PRO [ MPT ] [<текст>]

Она выдает указанный текст или пустую строку (при вызове без параметров). Если необходимо выдать несколько строк, для каждой строки выполняется отдельная команда PROMPT .

Считать строку и запомнить ее в указанной пользовательской переменной определенного типа (выдавая, при необходимости, приглашение) позволяет команда ACCEPT со следующим синтаксисом:

<команда ACCEPT> ::= ACC [ EPT ] <имя переменной> [<тип переменной>]
[ FOR [ MAT ] <формат>] [ DEF [ AULT ] <стандартное значение>]
[<приглашение>] [ HIDE ] <тип переменной> ::= NUM [ BER ] / CHAR / DATE <приглашение> ::= PROMPT <текст> / NOPR [ OMPT ]

Если указанная в команде ACCEPT пользовательская переменная не существует, SQL*Plus создает ее. Опции команды ACCEPT описаны в табл. 14.

Таблица 14. Опции команды ACCEPT.

Опция Назначение
NUMBER Задает переменной тип NUMBER . Если введенное значение не приводится к этому типу, команда ACCEPT выдает сообщение об ошибке и запрашивает значение снова.
CHAR Задает переменной тип CHAR . Длина строки-значения не должна превышать 240 байтов.
DATE Задает переменной тип DATE . Если введенное значение не преобразуется в тип DATE с учетом текущего формата даты в сеансе ( NLS_DATE_FORMAT ), команда ACCEPT выдает сообщение об ошибке и запрашивает значение снова.
FORMAT Явно задает формат, которому должно соответствовать введенное значение. Формат задается так же, как для команды COLUMN . Если значение не соответствует формату, команда ACCEPT выдает сообщение об ошибке и запрашивает значение снова.
DEFAULT Задает стандартное значение, если оно не будет введено. Стандартное значение должно соответствовать стандартному или указанному формату.
PROMPT Выдает на экран указанный текст перед ожиданием ввода значения переменной.
NOPROMPT Выдает перевод строки и ждет ввода значения, не выдавая приглашений.
HIDE Подавляет выдачу вводимого значения на экран по ходу ввода.

Рассмотрим простой пример совместного использования команд PROMPT и ACCEPT . Пусть имеется командный файл splus1.sql со следующим содержимым:

Вот что происходит при его выполнении:

Команда PAUSE позволяет дождаться подтверждения того, что пользователь прочитал сообщение на экране, выданное командой PROMPT . Для подтверждения необходимо нажать клавишу Enter, после чего выполнение сценария или сеанса SQL*Plus продолжится.

Команда PAUSE имеет следующий синтаксис:

<команда PAUSE> ::= PAU [ SE ] [<текст>]

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

Рассмотрим простой пример. Изменим файл splus1.sql следующим образом:

Вот что будет выдано при его выполнении:

Для получения результатов пришлось нажать клавишу Enter после вывода соответствующего приглашения.

Связываемые переменные — это создаваемые в SQL*Plus переменные, на которые можно ссылаться (как на хост-переменные) в блоках PL/SQL. Таким переменным можно, например, присваивать значения в блоках PL/SQL или использовать их значения во включенных в блоки SQL-операторах. Значения связываемых переменных можно затем выдавать в SQL*Plus.

Для создания связываемой переменной используется команда VARIABLE со следующим синтаксисом:

<команда VARIABLE> ::= VAR [ IABLE ] [<имя переменной> [<тип данных>]] <тип данных> ::= NUMBER
/ CHAR [ ( <количество> [<единица измерения>] ) ]
/ NCHAR [ ( <количество> ) ]
/ VARCHAR2 ( <количество> [<единица измерения>] )
/ NVARCHAR2 ( <количество> ) / CLOB
/ NCLOB
/ REFCURSOR <единица измерения> ::= CHAR / BYTE

При вызове без параметров команда VARIABLE выдает список всех переменных, созданных в сеансе. Если указать только имя переменной, выдается информация только об этой переменной.

Связываемые переменные можно использовать как параметры хранимых процедур или непосредственно, в анонимных PL/SQL-блоках. Их нельзя использовать в команде COPY или присвоить им значение в SQL-операторах, не входящих в PL/SQL-блоки. Вместо связанной переменной, не получившей явно значения, при необходимости подставляется значение NULL .

Рассмотрим простой пример использования связываемых переменных:

Мы использовали связываемую переменную для передачи значения из одного оператора SQL в другой, так и не выдав его на экран. Чтобы значения используемых в команде связываемых переменных выдавались автоматически, необходимо выполнить команду SET AUTOPRINT ON .

Типы данных для связываемых переменных аналогичны соответствующим типам данных PL/SQL. Переменные типа REFCURSOR позволяют работать с курсорными переменными PL/SQL. Утилита SQL*Plus обрабатывает связываемые переменные такого типа особым образом. Курсор, соответствующий курсорной переменной, открывается явно, а закрывается после выдачи значения или при завершении сеанса. При выдаче значения на экран выдается результирующее множество соответствующего запроса. Рассмотрим пример:

Результаты, выдаваемые при показе значений связываемой переменной типа REFCURSOR можно форматировать так же, как и результаты выполнения SQL-оператора SELECT . Значение такой переменной выдается только один раз, — затем результирующее множество надо выбирать повторно.

Для явной выдачи на экран значения связываемой переменной используется команда PRINT .

Команда PRINT имеет следующий синтаксис:

<команда PRINT> ::= PRI [ NT ]

Эта команда выдает текущее значение перечисленных связываемых переменных. При вызове без параметров выдаются значения всех связываемых переменных.

Продолжая предыдущий пример:

Утилита SQL*Plus позволяет автоматически получать отчет о способе выполнения оператора, выбранном оптимизатором SQL, а также статистическую информацию о выполнении. Этот отчет выдается после успешного выполнения операторов SELECT , INSERT , UPDATE и DELETE . Такой отчет полезен для контроля и настройки производительности этих операторов.

Для управления данным отчетом используется команда SET AUTOTRACE . Эта команда имеет пять опций:

OFF Отчет не выдается. Это стандартный режим работы SQL*Plus.
ON EXPLAIN Выдается только выбранный оптимизатором план выполнения оператора.
ON STATISTICS Выдается только статистическая информация о выполнении оператора.
ON Выдается план выполнения запроса и статистическая информация о выполнении оператора.
TRACEONLY Выдается отчет о статистике, но не выдаются результаты выполнения оператора. Используется для трассировки запросов, возвращающих большие объемы данных.

Для использования этой возможности SQL*Plus необходимо создать в схеме пользователя таблицу PLAN_TABLE и получить роль PLUSTRACE (предоставить ее может только DBA). Выполним следующие действия:

Как видите, по умолчанию эта возможность не поддерживается. Создадим таблицу с помощью сценария $ORACLE_HOME/rdbs/admin/utlxplan.sql :

Затем создадим роль PLUSTRACE, дадим ей необходимые привилегии, а затем предоставим ее роли DBA (с помощью сценария $ORACLE_HOME/sqlplus/admin/plustrce.sql ):

Теперь предоставим роль PLUSTRACE пользователю, который будет использовать трассировку:

Проверяем, что трассировочный отчет теперь выдается:

Подробнее использование возможностей трассировки в SQL*Plus рассмотрено в отдельном модуле, посвященном настройке производительности.

Среда SQL*Plus — очень гибкая и имеет широкие возможности настройки по требованиям пользователя. Эти настройки выполняются, в основном, с помощью команды SET . Мы неоднократно использовали команду SET в примерах данного модуля. В следующем разделе представлено ее формальное описание.

Команда SET позволяет установить системную переменную, изменяющую свойства среды SQL*Plus для текущего сеанса. Она имеет следующий синтаксис:

<команда SET> ::= SET <системная переменная> <значение>

Имена, описания и возможные значения основных системных переменных для команды SET представлены в табл. 15.

Таблица 15. Основные системные переменные SQL*Plus

Переменная Значения Назначение
APPI [ NFO ] ON / OFF / <текст> Устанавливает автоматическую регистрацию командных файлов с помощью пакета DBMS_APPLICATION_INFO . Это позволяет контролировать производительность и использование ресурсов каждым командным файлом. При отключенной регистрации файлов в качестве имени модуля используется строка «SQL*Plus» или заданный в этой переменной текст. По умолчанию регистрация отключена.
ARRAY [ SIZE ] <размер пакета> Устанавливает размер пакета строк, извлекаемых утилитой SQL*Plus из базы данных за один прием. Диапазон допустимых значений — от 1 до 5000. По умолчанию извлекается по 15 строк.
AUTO [ COMMIT ] ON / OFF / IMM [ EDIATE ] / <к-во операторов> Управляет фиксацией изменений в базе данных. По умолчанию ( OFF ) изменения необходимо фиксировать явно. Изменения могут фиксироваться немедленно после успешного выполнения оператора или блока ( ON , IMM ), или после успешного выполнения указанного количества операторов или блоков PL/SQL. Значение должно быть в диапазоне от 0 до 2000000000.
AUTOP [ RINT ] ON / OFF Устанавливает автоматическую выдачу значений использованных в команде связываемых переменных.
AUTORECOVERY ON / OFF При установке значения ON команда RECOVER будет автоматически использовать стандартные имена файлов архивных журналов повторного выполнения. По умолчанию, имена файлов при восстановлении придется вводить вручную, в ответ на запросы SQL*Plus.
AUTOT [ RACE ] ON / OFF / TRACE [ ONLY ] [ EXP [ LAIN ]] [ STAT [ ISTICS ]] Управляет выдачей отчета о выполнении успешного оператора SELECT , INSERT , UPDATE или DELETE . В отчет может включаться план выполнения и статистическая информация о выполненных действиях.
BLO [ CKTERMINATOR ] . / <символ> Задает не алфавитно-цифровой символ, используемый для завершения блока PL/SQL. По умолчанию используется точка.
CMDS [ EP ] ; / ON / OFF / <символ> Задает не алфавитно-цифровой символ, используемый для разделения нескольких команд SQL*Plus в одной строке. ON / OFF управляет возможностью обработки нескольких команд в строке. Значение ON означает, кроме того, установку стандартного разделителя — точки с запятой.
COLSEP <текст> Задает текст, который будет выдаваться между выбранными столбцами. Если текст содержит пробелы или символы пунктуации, его необходимо брать в апострофы. По умолчанию используется один пробел.
COM [ PATIBILITY ] V7 / V8 / NATIVE Задает версию Oracle, к которой подключена утилита SQL*Plus. Значение NATIVE , принятое по умолчанию, указывает, что версия определяется базой данных.
CON [ CAT ] . / ON / OFF / <символ> Задает символ, который позволяет отделить имя подставляемой переменной от остальной части строки. Стандартный символ — точка.
COPYC [ OMMIT ] <к-во пакетов> Задает количество пакетов (размер задается системной переменной ARRAYSIZE ), после копирования которых команда COPY фиксирует изменения в базе данных. Значение должно быть в диапазоне от 0 до 5000. При использовании стандартного значения, 0, фиксация выполняется только по завершении копирования.
DEF [ INE ] & / ON / OFF / <символ> Задает символ-префикс подставляемой переменной. При указании значения ON используется стандартный префикс — & .
DESC [ RIBE ] [ DEPTH 1 / ALL / <глубина>]
[ LINENUM ON / OFF ]
[ INDENT ON / OFF ]
Задает количество уровней рекурсивного описания объекта. Допустимы значения от 1 до 50. ALL означает максимальное количество уровней, 50. Можно также указывать номер строки и отступы для имени столбца или атрибута, когда объект содержит несколько компонентов объектных типов.
ECHO ON / OFF Управляет выдачей на экран каждой команды в командном файле, выполняемом с помощью START .
EDITF [ ILE ] <имя файла>[ . <расширение>] Устанавливает стандартное имя файла для команды EDIT .
EMB [ EDDED ] ON / OFF Задает выдачу каждого нового отчета с новой страницы (стандартное значение, OFF ) или как продолжение на текущей странице ( ON ).
ESC [ APE ] \ / ON / OFF / <символ> Задает маскирующий символ. Значение ON задает стандартный маскирующий символ, обратную косую.
FEED [ BACK ] ON / OFF / <к-во строк> Управляет выдачей количества строк, возвращенных запросом. Информация выдается, если возвращено не менее указанного количества строк. Значение ON эквивалентно 1 .
FLU [ SH ] ON / OFF Управляет буферизацией результатов. При стандартном значении ON результаты выдаются клиенту немедленно.
HEA [ DING ] ON / OFF Управляет выдачей заголовков столбцов в результатах.
HEADS [ EP ] / / ON / OFF / <символ> Задает символ перевода строки для команд, задающих колонтитулы. Можно задавать любой символ, кроме алфавитно-цифровых и пробела. По умолчанию используется вертикальная черта.
LIN [ ESIZE ] <длина строки> Задает количество символов в выдаваемой строке. Если выдаваемая строка длиннее, остаток переносится на новую строку. Это значение также используется для выравнивания колонтитулов и заголовков отчетов.
LOGSOURCE [<путь>] Задает местонахождение архивных файлов журнала повторного выполнения для восстановления. Если значение не указано, будет использоваться путь, указанный в файле параметров инициализации.
LONG <ширина столбца> Задает максимальную ширину (в байтах) для выдачи значений типа LONG , CLOB и NCLOB (а также для копирования значений типа LONG ). Значение не может превосходить 2 Гбайта.
NEWP [ AGE ] NONE / <к-во строк> Задает количество пустых строк, выдаваемых в начале страницы отчета, перед верхним колонтитулом. Стандартное значение — 1 строка. Если указано значение NONE , пустые строки не выдаются. Если же указано значение 0, при печати пропускается страница, а при выдаче на экран он очищается.
NULL <текст> Задает текст, представляющий пустое значение в результатах выполнения оператора SELECT .
NUMF [ ORMAT ] <формат> Задает стандартный формат выдачи чисел (о форматах см. в описании команды COLUMN )
NUM [ WIDTH ] <ширина> Задает стандартную ширину числовых столбцов.
PAGES [ IZE ] <к-во строк> Задает количество строк на странице. Если установить значение 0, подавляется выдача заголовков, колонтитулов и начальных пустых строк.
PAU [ SE ] ON / OFF / <текст> Позволяет управлять прокруткой при показе отчетов. При установке значения ON , утилита SQL*Plus приостанавливает работу перед выводом каждой страницы отчета, ожидая нажатия клавиши Enter. Можно также задать текст, который будет при этом выводиться.
RECSEP WR [ APPED ] / EA [ CH ] / OFF Устанавливает выдачу разделителей между записями (после перенесенных на несколько строк, после всех или никогда).
RECSEPCHAR <символ> Задает символ, из которого состоит разделитель записей. По умолчанию используется пробел. Разделитель записей представляет собой строку символов RECSEPCHAR , повторенных LINESIZE раз.
SERVEROUT [ PUT ] ON / OFF
[ SIZE <к-во строк>]
[ FOR [ MAT ] WRA [ PPED ] / WOR [ D_WRAPPED ] / TRU [ NCATED ]]
Управляет показом результатов хранимых процедур или анонимных блоков PL/SQL (выданных с помощью пакета DBMS_OUTPUT ), размером буфера и переносом строк. Размер буфера должен быть в пределах от 2000 (стандартное значение) до 1000000.
SHOW [ MODE ] ON / OFF Управляет выдачей значений системных переменных при изменении. Если задано значение ON , при изменении выдается старое и новое значение.
SQLBL [ ANKLINES ] ON / OFF Разрешает или запрещает использование пустых строк в сценариях и командах SQL.
SQLC [ ASE ] MIX [ ED ] / LO [ WER ] / UP [ PER ] Управляет изменением регистра символов в команде перед выполнением. По умолчанию ( MIXED ) регистр символов остается неизменным. Иначе все символы, включая литералы в кавычках, переводятся в соответствующий регистр. Содержимое буфера SQL при этом не меняется.
SQLCO [ NTINUE ] <текст> Задает текст, выдаваемый утилитой SQL*Plus в качестве приглашения для продолжения ввода команды. По умолчанию используется символ >.
SQLN [ UMBER ] ON / OFF Управляет выдачей приглашения для ввода второй и последующих строк команды или блока PL/SQL. Если указано значение ON , в качестве приглашения выдается номер строки, если OFF — значение системной переменной SQLPROMPT .
SQLPRE [ FIX ] <символ> Задает префиксный символ SQL*Plus (по умолчанию — # ). Если по ходу ввода команды или блока PL/SQL ввести в отдельной строке команду, начинающуюся префиксным символом, эта команда немедленно выполнится. Префиксный символ должен быть не алфавитно-цифровым.
SQLP [ ROMPT ] <текст> Задает приглашение командной строки SQL*Plus (по умолчанию — » SQL> «).
SQLT [ ERMINATOR ] <символ> / ON / OFF Задает символ, завершающий и посылающий на выполнение команду SQL. По умолчанию используется точка с запятой. Если задать значение OFF , то для завершения придется вводить пустую строку или BLOCKTERMINATOR , если установлена системная переменная SQLBLANKLINES .
SUF [ FIX ] <текст> Задает стандартное расширение командного файла. По умолчанию используется sql .
TAB ON / OFF Управляет использованием символа табуляции для форматирования результатов запросов в SQL*Plus.
TERM [ OUT ] ON / OFF Управляет выдачей результатов команд на экран. Если задать значение OFF , результат выполнения команд (кроме интерактивных) не будет выдаваться на экран, но будет записываться в файл, указанный в команде SPOOL .
TI [ ME ] ON / OFF Управляет выдачей текущего времени. При установке значения ON в приглашении выдается текущее время.
TIMI [ NG ] ON / OFF Управляет выдачей статистики о времени. При установке значения ON после выполнения команды или командного файла выдается время выполнения с точностью до сотых долей секунды.
TRIM [ OUT ] ON / OFF Управляет усечением хвостовых пробелов в выдаваемых строках. При установке значения ON (принято по умолчанию), хвостовые пробелы усекаются. Эта переменная учитывается только при выводе на экран.
TRIMS [ POOL ] ON / OFF Управляет усечением хвостовых пробелов в строках, сбрасываемых в файл или на принтер, аналогично TRIMOUT .
UND [ ERLINE ] ON / OFF / <символ> Задает символ, используемый для подчеркивания заголовков столбцов. По умолчанию используется символ дефиса. Значение OFF отключает подчеркивание заголовков.
VER [ IFY ] ON / OFF Управляет выдачей текста SQL-операторов и PL/SQL-блоков до и после замены подставляемых переменных значениями.
WRA [ P ] ON / OFF Управляет усечением слишком длинных строк результатов. По умолчанию, остатки строк переносятся на следующую строку. При задании значения OFF — усекаются.

Рассмотрим пример задания некоторых системных переменных SQL*Plus:

Команда SHOW позволяет получить значение системных переменных SQL*Plus, а также ряд информации о среде, базе данных, к которой подключена утилита, и об ошибках в последней выполненной команде. Эта команда имеет следующий синтаксис:

<команда SHOW> ::= SHO [ W ] <опция> <опция> ::= <имя системной переменной>
/ ALL
/ BTI [ TLE ]
/ ERR [ ORS ] [[<объект>] [<схема> . ]<имя объекта>]
/ LNO
/ PARAMETERS [<часть параметра>]
/ PNO
/ REL [ EASE ]
/ REPF [ OOTER ]
/ REPH [ EADER ]
/ SGA
/ SPOO [ L ]
/ SQLCODE
/ TTI [ TLE ]
/ USER <объект> ::= FUNCTION / PROCEDURE
/ PACKAGE
/ PACKAGE BODY
/ TRIGGER
/ VIEW
/ TYPE
/ TYPE BODY
/ DIMENSION
/ JAVA CLASS

Назначение опций представлено в табл. 16.

Таблица 16. Опции команды SHOW.

Опция Назначение
ALL Выдает значения всех опций SHOW , кроме ERRORS и SGA , и всех системных переменных.
BTI [ TLE ] Выдает текущее определение BTITLE (нижнего колонтитула).
ERR [ ORS ] Выдает ошибки компиляции хранимой программной единицы. При компиляции выдается сообщение, что ошибки есть. Для получения детальной информации об ошибках необходимо выполнить команду SHOW ERRORS . При выполнении этой команды без аргументов, выдается информация об ошибках при компиляции последней созданной или измененной программной единицы. Если же указать тип и имя программной единицы, будут выданы ошибки, полученные при ее компиляции.

Выдается номер строки/столбца (в столбце LINE/COL ), в которых обнаружена ошибка, и текст сообщения об ошибке (в столбце ERROR). Форматом выдачи можно управлять с помощью команды COLUMN .

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

Для использования этой опции необходима объектная привилегия SELECT на представление V_$PARAMETER .

PNO Выдает номер текущей страницы результатов.
REL [ EASE ] Выдает номер версии сервера Oracle, к которой подключена утилита SQL*Plus.
REPF [ OOTER ] Выдает текущее определение REPFOOTER .
REPH [ EADER ] Выдает текущее определение REPHEADER .
SGA Выдает информацию об области SGA экземпляра, к которому подключена утилита SQL*Plus.

Для использования опции SGA необходима объектная привилегия SELECT на представление V_$SGA .

SPOO [ L ] Выдает информацию о том, направляются ли результаты в файл или на принтер.
SQLCODE Выдает код завершения последнего оператора SQL.
TTI [ TLE ] Выдает текущее определение TTITLE (верхнего колонтитула).
USER Выдает имя текущего пользователя.

Рассмотрим пример использования ряда опций команды SHOW :

Есть еще несколько полезных команд, не относящихся ни к одной из рассмотренных выше категорий. Вот некоторые из них.

Эта команда выполняет один оператор PL/SQL. Чаще всего, это вызов хранимой процедуры или функции. Команда, по сути, неявно создает анонимный блок, в котором выполняется оператор. Рассмотрим пример:

Команда PASSWORD позволяет изменить пароль пользователя, не отображая его на экране. Эта команда имеет следующий простой синтаксис:

<команда PASSWORD> ::= PASSW [ ORD ] [<имя пользователя>]

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

Ряд команд утилиты SQL*Plus предназначен для АБД. По сути, SQL*Plus — полнофункциональное средство администрирования. Доступные в SQL*Plus команды администрирования рассматриваются в данном разделе.

Начиная с Oracle8, утилиту SQL*Plus можно использовать для запуска и остановки базы данных (ранее для этих целей использовались утилиты svrmgrl и SQL*DBA ). Запуск базы данных состоит из трех шагов:

Экземпляр — это совокупность фоновых процессов и областей памяти, необходимых для доступа к базе данных Oracle.

При монтировании база данных связывается с запущенным ранее экземпляром.

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

Запуск базы данных в SQL*Plus выполняется командой STARTUP , имеющей следующий синтаксис:

<команда STARTUP> ::= STARTUP [ FORCE ] [ RESTRICT ] [ PFILE= <имя файла параметров>] <действие запуска> <действие запуска> ::= MOUNT [<имя базы данных>]
/ OPEN [<опция открытия>][<имя базы данных>]
/ NOMOUNT <опция открытия> ::= READ ONLY
/ READ WRITE [ RECOVER ]
/ RECOVER

Все варианты команды STARTUP , так или иначе, запускают экземпляр (выделяется память и запускаются фоновые процессы). Опции команды STARTUP представлены в табл. 17.

Таблица 17. Опции команды STARTUP.

Опция Назначение
FORCE Останавливает (с опцией ABORT ) и потом перезапускает экземпляр. Это единственная опция команды STARTUP , которую можно применять к работающему экземпляру. Используется при отладке и в экстраординарных ситуациях.
RESTRICT Позволяет подключаться к базе данных после запуска только пользователям, обладающим системной привилегией RESTRICTED SESSION . Это ограничение в дальнейшем можно снять с помощью команды ALTER SYSTEM .
PFILE Задает нестандартный файл параметров инициализации. Если эта опция не указана, используется стандартный файл (в ОС UNIX это обычно $ORACLE_HOME/admin/dbs/init$ORACLE_SID.ora , а в Windows — %ORACLE_HOME%\database\init%ORACLE_SID%.ora ).
MOUNT Монтирует указанную (стандартную локальную — значение параметра инициализации DB_NAME ) базу данных, но не открывает ее.
OPEN Монтирует и открывает указанную базу данных.
NOMOUNT Экземпляр запускается, но база данных не монтируется.
RECOVER Требует выполнить восстановление носителей, если необходимо, перед запуском экземпляра. Применение этой опции аналогично выполнению команды RECOVER DATABASE с последующим обычным запуском. Так можно выполнять только полное восстановление.

Эту команду может выполнять только пользователь, подключившийся как SYSOPER или SYSDBA к выделенному серверному процессу . По умолчанию используется опция OPEN . Команда STARTUP OPEN RECOVER монтирует и открывает базу данных, даже если полное восстановление закончилось неудачно.

Команда SHUTDOWN в SQL*Plus останавливает текущий экземпляр Oracle, к которому подключен пользователь, и может при этом закрыть и демонтировать базу данных. Эту команду можно применять только для серверов версии 8 и выше.

Команда SHUTDOWN имеет следующий синтаксис:

<команда SHUTDOWN> ::= SHUTDOWN <режим остановки> <режим остановки> ::= ABORT / IMMEDIATE / NORMAL / TRANSACTIONAL [ LOCAL ]

Назначение опций команды SHUTDOWN описано в табл. 18:

Таблица 18. Опции команды SHUTDOWN.

Опция Назначение
ABORT Выполняет немедленную остановку базы данных, не дожидаясь завершения транзакций и отключения пользователей. Эта опция используется при аварийном завершении одного из фоновых процессов или при невозможности нормальной остановки. При перезапуске потребуется восстановление.
IMMEDIATE Не ждет завершения транзакций (автоматически их откатывает) и отключения пользователей. Новые подключения запрещаются. База данных закрывается и демонтируется, затем останавливается экземпляр. При перезапуске восстановление не потребуется.
NORMAL Ждет отключения всех пользователей от базы данных (новые подключения запрещены). База данных закрывается и демонтируется, затем останавливается экземпляр. При перезапуске восстановление не потребуется.

Эта опция используется по умолчанию.

TRANSACTIONAL [ LOCAL ] Ждет завершения активных транзакций. При попытке начать новую транзакцию происходит отключение сеанса. После завершения всех активных транзакций все сеансы автоматически отключаются. Затем остановка идет как при вводе опции IMMEDIATE .

Режим LOCAL задает такой режим остановки только для локальных транзакций. Сервер не ждет завершения удаленных транзакций.

Эту команду может выполнять только пользователь, подключившийся как SYSOPER или SYSDBA к выделенному серверному процессу . По умолчанию используется опция NORMAL .

Каждая база данных Oracle имеет набор из двух или более файлов журнала повторного выполнения, который обобщенно называют просто журналом повторного выполнения . В этот журнал записываются изменения данных, которые используются при восстановлении базы данных после сбоев. Для защиты от сбоя самого журнала сервер Oracle поддерживает его мультиплексирование (поддержку нескольких копий на разных дисках в виде группы ) и архивирование .

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

Для управления режимом архивирования журналов повторного выполнения утилита SQL*Plus предлагает команду ARCHIVE LOG , которая имеет следующий синтаксис:

<команда ARCHIVE LOG > ::= ARCHIVE LOG <команда или журнал> [ TO <место назначения>] <команда или журнал> ::= LIST / STOP / START / NEXT / ALL / <номер журнала>

Эта команда позволяет начать или остановить автоматическое архивирование оперативных файлов журнала повторного выполнения, явно заархивировать указанные по номерам файлы или выдать информацию о них. Опции команды ARCHIVE LOG описаны в табл. 19.

Таблица 19. Опции команды ARCHIVE LOG.

Опция Назначение
LIST Выдает информацию о режиме архивирования, каталоге, в котором создаются файлы архива, номерах текущей, последней заархивированной и требующей архивирования группы журналов. Вид выдаваемой информации представлен в примере ниже.
STOP Останавливает автоматическое архивирование. Если экземпляр по- прежнему работает в режиме ARCHIVELOG и все группы журналов повторного выполнения заполнены, работа базы данных приостанавливается, пока файл журнала повторного выполнения не будет заархивирован (например, командами ARCHIVE LOG NEXT или ARCHIVE LOG ALL ).
START Включает автоматическое архивирование. Запускает фоновый процесс ARCH , выполняющий автоматическое архивирование при необходимости. Если запускается процесс ARCH и в команде указано имя файла, этот файл становится новым стандартным местом назначения для архива. Процесс ARCH запускается автоматически при запуске экземпляра, если параметр инициализации LOG_ARCHIVE_START имеет значение TRUE .
NEXT Явно архивирует следующую заполненную, но еще не заархивированную оперативную группу файлов журнала повторного выполнения.
ALL Явно архивирует все заполненные, но еще не заархивированные оперативные группы файлов журнала повторного выполнения.
номер журнала Вызывает архивирование любой еще доступной оперативной группы файлов журнала повторного выполнения с указанным номером последовательности. Если такая группа не найдена, выдается сообщение об ошибке. Эта опция позволяет повторно выполнить архивирование группы.

Если место назначения для архива не указано явно в командной строке, используется параметр инициализации LOG_ARCHIVE_DEST ( стандартное место назначения ). Если новое место назначения указано с опцией START , оно становится стандартным. В остальных случаях туда записываются только соответствующие архивы, инициированные данной командой.

Команду ARCHIVE LOG может выполнять только пользователь, подключившийся как SYSOPER или SYSDBA . Она применяется только к текущему экземпляру. Для управления другими экземплярами и кластером в целом используется SQL-оператор ALTER SYSTEM .

Если все оперативные группы файлов журнала повторного выполнения заполнены и не доступны для повторного использования (т.е. сервер работает в режиме ARCHIVELOG , и они не скопированы) работа базы данных приостанавливается. Явное архивирование решает эту проблему.

Рассмотрим простой пример использования команды ARCHIVE LOG для просмотра информации о текущем состоянии архивирования журналов повторного выполнения:

Если база данных работала в режиме ARCHIVELOG , после сбоя носителя ее можно полностью или частично восстановить. Для этого в SQL*Plus предлагается команда RECOVER , имеющая следующий, весьма объемный, синтаксис:

<команда RECOVER> ::= RECOVER <режим восстановления> [<степень параллелизма>] <режим восстановления> ::= <общее восстановление> / <управляемое восстановление> / END BACKUP <общее восстановление> ::= [ AUTOMATIC ] [ FROM <местонахождение>] <команда восстановления> <команда восстановления> ::= <вид восстановления> [ TEST ALLOW <целое число> CORRUPTION ]
/ CONTINUE [ DEFAULT ]
/ CANCEL <вид восстановления> ::= <полное восстановление>
/ <частичное восстановление>
/ LOGFILE <имя файла> <полное восстановление> ::= [ STANDBY ] DATABASE < <уровень восстановления >><уровень восстановления> UNTIL <точка восстановления>
/ USING BACKUP CONTROLFILE <точка восстановления> ::= CANCEL / TIME <дата> / CHANGE <целое число> <частичное восстановление> ::= <табличные пространства или файлы данных>
/ STANDBY <табличные пространства или файлы данных>
UNTIL [ CONSISTENT ] [ WITH ] CONTROLFILE <табличные пространства или файлы данных> ::= TABLESPACE <табличное пространство> < , <табличное пространство>>
/ DATAFILE <файл данных> < , <файл данных>><управляемое восстановление> ::= MANAGED STANDBY DATABASE <опция управляемого восстановления> <опция управляемого восстановления> ::= NODELAY
/ [ TIMEOUT ] <целое число>
/ CANCEL [ IMMEDIATE ] [ NOWAIT ]
/ DISCONNECT [ FROM SESSION ] [ FINISH [ NOWAIT ]] <степень параллелизма> ::= PARALLEL [<целое число>] / NOPARALLEL

Опции команды RECOVER кратко описаны в табл. 20.

Таблица 20. Опции команды RECOVER.

Опция Назначение
AUTOMATIC Автоматически генерирует имя архивного файла журнала повторного выполнения, необходимого для продолжения операции восстановления. Для этого используются значения параметров конфигурации LOG_ARCHIVE_DEST и LOG_ARCHIVE_FORMAT (или соответствующие стандартные значения). Если файл с таким именем не найден, SQL*Plus запрашивает имя файла, выводя автоматически сгенерированное в качестве подсказки. Имя запрашивается также, если не указана ни опция AUTOMATIC , ни опция LOGFILE . Если заранее известно, что архивирование выполнялось в файл с нестандартным именем, имеет смысл сразу указать опцию LOGFILE .
FROM <местонахождение> Задает местонахождение архивных файлов журнала повторного выполнения. По умолчанию используется значение параметра инициализации LOG_ARCHIVE_DEST . Можно также задать местонахождение архивных файлов с помощью команды SQL*Plus SET LOGSOURCE.
LOGFILE Продолжает восстановление носителей, применяя указанный файл журнала повторного выполнения. При восстановлении в интерактивном режиме ( AUTORECOVERY OFF ), запрашивает новое имя файла, если указанный файл журнала не найден.
TEST ALLOW <целое число> CORRUPTION В случае повреждения файла журнала указывает, при скольких поврежденных блоках еще можно продолжать восстановление. В ходе обычного восстановления это значение не должно быть более 1.
CONTINUE Продолжает восстановление нескольких экземпляров после прерывания для отключения восстановления одного из них.
CONTINUE DEFAULT Продолжает восстановление, используя автоматически сгенерированное имя архивного файла журнала повторного выполнения, если оно не указано явно. Аналогично опции AUTOMATIC , но не запрашивает альтернативное имя файла, если файл не найден.
CANCEL Прерывает восстановление, ведущееся до CANCEL (см. опцию UNTIL CANCEL ).
STANDBY DATABASE Восстанавливает резервную базу данных, используя управляющий файл и архивные файлы журнала повторного выполнения основной базы данных. Резервная база данных должна быть смонтирована, но не открыта.
DATABASE Восстанавливает всю базу данных в целом.
UNTIL CANCEL Задает неполное восстановление, до прерывания администратором. Необходимо указать или подтвердить автоматически сгенерированные имена файлов журналов повторного выполнения. Восстановление завершится, если указать CANCEL вместо очередного имени файла.
UNTIL TIME Задает неполное восстановление до момента времени. Момент времени указывается в одиночных кавычках по формату ‘YYYY-MM-DD:HH24:MI:SS’ .
UNTIL CHANGE Задает неполное восстановление до указанного по номеру изменения ( SCN ), не включая его.
USING BACKUP CONTROLFILE Указывает, что вместо текущего управляющего файла должна использоваться его резервная копия.
TABLESPACE Восстанавливает указанные табличные пространства текущей базы данных (до 16).
DATAFILE Восстанавливает любое количество указанных файлов данных.
STANDBY TABLESPACE Реконструирует потерянное или поврежденное табличное пространство на резервной базе данных, используя архивные файлы журнала повторного выполнения и управляющий файл основной базы данных.
STANDBY DATAFILE Реконструирует потерянный или поврежденный файл данных на резервной базе данных, используя архивные файлы журнала повторного выполнения и управляющий файл основной базы данных.
UNTIL CONSISTENT WITH CONTROLFILE Указывает, что восстановление старого табличного пространства или файла данных использует текущий управляющий файл резервной базы данных.
MANAGED STANDBY DATABASE Задает режим устойчивого восстановления резервной базы данных. В этом режиме предполагается, что резервная база данных является активным компонентом. В таком режиме можно восстанавливать только носители.
NODELAY Немедленно применяет отложенный архивный журнал к резервной базе данных, независимо от установки параметра DELAY в параметре инициализации LOG_ARCHIVE_DEST_n в основной базе данных.
TIMEOUT Задает период ожидания (в минутах) для операции устойчивого восстановления. Если за это время не станет доступным архивный журнал повторного выполнения, процесс восстановления завершается с ошибкой. Если эта конструкция не указана, резервная база данных остается в состоянии ожидания восстановления, пока не будет повторно выполнена команда RECOVER с конструкцией CANCEL или пока не произойдет остановка или сбой экземпляра.
CANCEL При управляемом восстановлении конструкция CANCEL прерывает восстановление резервной базы данных после применения текущего архивного файла повторного выполнения. Приглашение SQL*Plus снова появится после остановки процесса восстановления.
CANCEL IMMEDIATE Прерывает управляемое восстановление резервной базы данных после применения текущего архивного файла повторного выполнения или после прочтения следующего, в зависимости от того, какое событие произойдет раньше. Приглашение SQL*Plus снова появится после остановки процесса восстановления. Команду RECOVER CANCEL IMMEDIATE нельзя выполнять из того же сеанса, из которого была выполнена команда RECOVER MANAGED STANDBY DATABASE .
CANCEL NOWAIT Прерывает управляемое восстановление резервной базы данных после прочтения следующего файла журнала повторного выполнения и выдает приглашение SQL*Plus.
DISCONNECT FROM SESSION Указывает, что в ходе управляемого восстановления архивные файлы повторного выполнения должны применяться отдельным фоновым процессом, не блокирующим текущий сеанс.
FINISH Немедленно восстанавливает резервные файлы текущего журнала повторного выполнения резервной базы данных. Используется при сбое основной базы данных.
NOWAIT Возвращает управление немедленно, не дожидаясь завершения процесса восстановления.

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

Чтобы можно было выполнить восстановление носителей для всей базы данных в целом (для всех табличных пространств), база данных должна быть смонтирована, но закрыта, а все требующие восстановления табличные пространства должны быть включены (online). Для восстановления же отдельного табличного пространства база данных должна быть смонтирована и открыта, а само табличное пространство отключено. Для восстановления файла данных, база данных может оставаться открытой и смонтированной, а поврежденные файлы — отключены (если только они не входят в табличное пространство SYSTEM ).

Рассмотрим простой пример остановки, запуска и восстановления носителя из SQL*Plus:

Для освоения всех возможностей утилиты SQL*Plus необходим практический опыт ее использования. Упражнения позволят вам его получить.

Напишите командный файл SQL*Plus, выдающий все записи указанной таблицы в файл с именем <имя таблицы>.unl в виде строк, поля которых разделены символом вертикальной черты ( / ). Заголовки столбцов не выдавать.

Например, для таблицы dept содержимое файла должно иметь следующий вид:

Файл такого вида подходит для загрузки в базы данных Informix :).

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

Напишите командный файл SQL*Plus, выдающий на экран данные таблицы emp, предваряя денежные суммы знаком доллара и заменяя неизвестные значения прочерками. Снабдите создаваемый отчет заголовком и колонтитулами. Для каждого отдела выдайте значение средней заработной платы. В конце отчета выдайте сумму начисленных сотрудникам комиссионных.

Напишите командный файл SQL*Plus, выдающий по указанному имени представления текст оператора для его создания ( CREATE VIEW ) в файл с именем <имя>.sql . См. представление USER_VIEWS в словаре данных, описывающее представления данного пользователя. Длина каждой строки в файле не должна превышать 80 символов, чтобы файл было удобно читать.

Напишите сценарий SQL*Plus (без применения PL/SQL), увеличивающий вдвое зарплату всем сотрудникам отдела, средняя зарплата в котором ниже, чем средняя зарплата по всей организации (см. таблицы emp и dept ). Затем сценарий должен выдать (в указанный при вызове файл) отчет о сотрудниках с указанием средней зарплаты по отделам. Снабдите столбцы отчета заголовками на русском языке.

Пример создания и работы с “ *. mdf ” файлом локальной базы данных Microsoft SQL Server

В данной теме рассматривается подключение (создание) файла базы данных к локальному екземпляру Microsoft SQL Server с помощью поставщика данных .NET Framework для SQL Server .

Содержание

  • Условие задачи
  • Выполнение
    • 1. Запуск MS Visual Studio .
    • 2. Активировать окно Server Explorer .
    • 3. Команда “ Add Connection… ”.
    • 4. Окно “ Add Connection ”.
    • 5. Создание таблицы Student .
    • 6. Создание таблицы Session .
    • 7. Редактирование структуры таблиц.
    • 8. Установление связей между таблицами.
    • 9. Внесение данных в таблицы.

    Поиск на других ресурсах:

    Условие задачи

    Создать базу данных с именем “ Education ”. В базе данных создать две таблицы, которые связаны между собой по некоторому полю.

    Структура первой таблицы « Student ».

    09_02_00_002_table01_r

    Структура второй таблицы “Session”.

    09_02_00_002_table02_r

    Таблицы должны быть связаны между собой по полю Num_book .

    Выполнение

    1. Загрузить MS Visual Studio .
    2. Активировать окно Server Explorer .

    Файл базы данных с расширением “ *.mdf ” относится к серверу реляционных баз данных Microsoft SQL Server . Файл содержит непосредственно базу данных.

    При создании “ *.mdf ” файла базы данных также создается файл с расширением “ *.ldf ”, который содержит журнал транзакций.

    Перед созданием базы данных, нужно активировать утилиту Server Explorer . Для этого, в MS Visual Studio нужно вызвать (рисунок 1)

    Visual Studio команда Server Explorer

    Рис. 1. Вызов Server Explorer

    3. Команда “ Add Connection… ”.

    Для вызова окна создания базы данных в формате mdf нужно вызвать команду Add Connection . Команда Add Connection вызывается двумя способами. Первый способ – это вызов контекстного меню (клик правой кнопкой мыши) на элементе ” Data Connection… ” (рисунок 2).

    Visual Studio команда Add Connection

    Рис. 2. Вызов команды Add Connection из контекстного меню

    Второй способ – это вызов команды “ Connect to Database… ” из меню Tools главного меню Microsoft Visual Studio . Также эту команду можно вызвать из Server Explorer кликом на соответствующей кнопке (рисунок 3).

    Visual Studio команда “Connect to Database. ”

    Рис. 3. Команда “ Connect to Database… ”

    4. Окно “ Add Connection ”.

    В результате выполнения предыдущей команды откроется окно “ Add Connection ” (рисунок 4). В этом окне пользователь имеет возможность:

    • выбрать источник данных ( Data source );
    • создать новый или выбрать уже существующий файл базы данных ( Database file name );
    • если нужно, задать пароль входа в базу данных;
    • проверить соединение с базой данных (кнопка Test Connection );
    • настроить другие параметры вызовом кнопки « Advanced» .

    SQL Server соединение база данных

    Рис. 4. Окно “ Add Connection ”

    В качестве источника данных Microsoft Visual Studio предлагает базу данных Microsoft Access . Для создания “ *.mdf ” файла базы данных MS SQL Server нужно изменить источник данных выбором кнопки “ Change… ”.

    В результате откроется второе окно “ Change Data Source ” (рисунок 5). В этом окне выбирается источник данных и провайдер.

    Система MS Visual Studio предлагает следующие виды источников данных:

    • база данных Microsoft Access , которая содержится в файле формата “ *.mdb ”;
    • база данных, которая поддерживает доступ с помощью драйвера ODBC ;
    • база данных типа Microsoft SQL Server , в том числе и локальный сервер SQLEXPRESS ;
    • база данных “ Microsoft SQL Server Compact 3.5 ”, которая размещается в файлах с расширением “ *.sdf ”;
    • база данных “ Microsoft SQL Server Database File ”, которая содержится в файлах формата “ *.mdf ”;
    • база данных Oracle .

    Для создания “ *.mdf ” файла базы данных Microsoft SQL Server нужно выбрать источник данных “ Microsoft SQL Server Database File ” как зображено на рисунке 5.

    09_02_00_002_05_

    Рис. 5. Окно “ Change Data Source ”

    После изменения источника данных в окне Add Connection в поле “ Database file name (new or existing): ” нужно ввести имя создаваемой базы данных. Если нужно выбрать “ *.mdf ” файл уже существующей базы данных, то для этого предназначена кнопка “ Browse… ”.

    В нашем случае нужно ввести название базы данных “ Education ”, как изображено на рисунке 6.

    09_02_00_002_06_

    Рис. 6. Создание базы данных Education

    После подтверждения на “ OK ”, система выведет окно, как изображено на рисунке 7. Предлагается системная папка по умолчанию:

    Если нужно установить другую папку, для этого используется кнопка “ Browse… ” из окна “ Add Connection ”.

    09_02_00_002_07_

    Рис. 7. Предложение создать файл “ Education.mdf ”

    После подтверждения, база данных Education.mdf будет создана (рисунок 8).

    09_02_00_002_08_

    Рис. 8. Новосозданная база данных “ Education.mdf ”

    5. Создание таблицы Student .

    На данный момент база данных Education абсолютно пустая и не содержит никаких объектов (таблиц, сохраненных процедур, представлений и т.д.).

    Чтобы создать таблицу, нужно вызвать контекстное меню (клик правой кнопкой мышки) и выбрать команду “ Add New Table ” (рисунок 9).

    09_02_00_002_09_

    Рис. 9. Команда добавления новой таблицы

    Существует и другой вариант добавления таблицы базы данных с помощью команд меню Data (рисунок 10):

    09_02_00_002_10_

    Рис. 10. Альтернативный вариант добавления новой таблицы

    В результате откроется окно добавления таблицы, которое содержит три столбца (рисунок 11). В первом столбце “ Column Name ” нужно ввести название соответствующего поля таблицы базы данных. Во втором столбце “ Data Type ” нужно ввести тип данных этого поля. В третьем столбце “ Allow Nulls ” указывается опция о возможности отсутствия данных в поле.

    09_02_00_002_11_

    Рис. 11. Окно создания новой таблицы

    С помощью редактора таблиц нужно сформировать таблицу Student как изображено на рисунке 12. Имя таблицы нужно задать при ее закрытии.

    В редакторе таблиц можно задавать свойства полей в окне Column Properties . Для того, чтобы задать длину строки ( nvchar ) в символах, в окне Column Properties есть свойство Length . По умолчанию значения этого свойства равно 10.

    09_02_00_002_12_

    Рис. 12. Таблица Student

    Следующим шагом нужно задать ключевое поле. Это осуществляется вызовом команды “ Set Primary Key ” из контекстного меню поля Num_book (рисунок 13). С помощью ключевого поля будут установлены связи между таблицами. В нашем случае ключевым полем есть номер зачетной книжки.

    09_02_00_002_13_

    Рис. 13. Задание ключевого поля

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

    Рис. 14. Таблица Student после окончательного формирования

    Теперь можно закрыть таблицу. В окне сохранения таблицы нужно задать ее имя – « Student» (рисунок 15).

    Рис. 15. Ввод имени таблицы Student

    6. Создание таблицы Session .

    По образцу создания таблицы Student создается таблица Session .

    На рисунке 16 изображен вид таблицы Session после окончательного формирования. Первичный ключ ( Primary Key ) устанавливается в поле Num_book . Имя таблицы задается Session .

    Рис. 16. Таблица Session

    После выполненных действий, в окне Server Explorer будут отображаться две таблицы Student и Session .

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

    7. Редактирование структуры таблиц.

    Бывают случаи, когда нужно изменить структуру таблицы базы данных.

    Для того, чтобы вносить изменения в таблицы базы данных в MS Visual Studio , сначала нужно снять опцию “ Prevent Saving changes that require table re-creation ” как показано на рисунке 17. Иначе, MS Visual Studio будет блокировать внесения изменений в ранее созданную таблицу. Окно Options , показанное на рисунке 17 вызывается из меню Tools в такой последовательности:

    Рис. 17. Опция “ Prevent Saving changes that require table re-creation ”

    После настройки можно изменять структуру таблицы. Для этого используется команда “Open Table Definition ” (рисунок 18) из контекстного меню, которая вызывается для выбранной таблицы (правый клик мышкой).

    Рис. 18. Вызов команды “ Open Table Definition ”

    Также эта команда размещается в меню Data :

    Предварительно таблицу нужно выделить.

    8. Установление связей между таблицами.

    В соответствии с условием задачи, таблицы связаны между собою по полю Num_book.

    Чтобы создать связь между таблицами, сначала нужно (рисунок 19):

    • выделить объект Database Diagram ;
    • выбрать команду Add New Diagram из контекстного меню (или из меню Data );
    • подтвердить создание нового объекта-диаграммы (рисунок 20).

    Рис. 19. Вызов команды добавления новой диаграммы

    Рис. 20. Сообщение о создании объекта-диаграммы

    В результате откроется окно добавления новой диаграммы Add Table (рисунок 21). В этом окне нужно выбрать последовательно две таблицы Session и Student и нажать кнопку Add.

    Рис. 21. Окно добавления таблиц к диаграмме

    В результате будет создан новый объект с двумя таблицами Student и Session (рис. 22).

    Рис. 22. Таблицы Student и Session после добавления их к диаграмме

    Чтобы начать устанавливать отношение между таблицами, надо сделать клик на поле Num_book таблицы Student , а потом (не отпуская кнопку мышки) перетянуть его на поле Num_book таблицы Session .

    В результате последовательно откроются два окна: Tables and Columns (рис. 23) и Foreign Key Relationship (рис. 24), в которых нужно оставить все как есть и подтвердить свой выбор на OK .

    В окне Tables and Columns задается название отношения ( FK_Session_Student ) и названия родительской ( Student ) и дочерней таблиц.

    Рис. 23. Окно Tables and Columns

    Рис. 24. Окно настройки свойств отношения

    После выполненных действий будет установлено отношение между таблицами (рисунок 25).

    Рис. 25. Отношение между таблицами Student и Session

    Сохранение диаграммы осуществляется точно также как и сохранение таблицы. Имя диаграммы нужно выбрать на свое усмотрение (например Diagram1 ).

    После задания имени диаграммы откроется окно Save , в котором нужно подтвердить свой выбор (рисунок 26).

    Рис. 26. Подтверждение сохранения изменений в таблицах

    9. Внесение данных в таблицы.

    Система Microsoft Visual Studio разрешает непосредственно вносить данные в таблицы базы данных.

    В нашем случае, при установлении связи (рис. 22) первичной ( Primary Key Table ) выбрана таблица Student . Поэтому, сначала нужно вносить данные в ячейки именно этой таблицы. Если попробовать сначала внести данные в таблицу Session , то система заблокирует такой ввод с выводом соответствующего сообщения.

    Чтобы вызвать режим ввода данных в таблицу Student , нужно вызвать команду Show Table Data из контекстного меню (клик правой кнопкой мышки) или с меню Data (рис. 27).

    Рис. 27. Команда Show Table Data

    Откроется окно, в котором нужно ввести входные данные (рис. 28).

    Рис. 28. Ввод данных в таблице Student

    После внесения данных в таблицу Student нужно внести данные в таблицу Session .

    При внесении данных в поле Num_book таблицы Session нужно вводить точно такие же значения, которые введены в поле Num_book таблицы Student (поскольку эти поля связаны между собой).

    Например, если в поле Num_book таблицы Student введены значения “1134”, “1135”, “1221” (см. рис. 28), то следует вводить именно эти значения в поле Num_book таблицы Session . Если попробовать ввести другое значение, система выдаст приблизительно следующее окно (рис. 29).

    Рис. 29. Сообщение об ошибке ввода данных связанных таблиц Student и Session

    Таблица Session с введенными данными изображена на рисунке 30.

    Рис. 30. Таблица Session с введенными данными

    Итак, база данных создана. Ввод и обработку данных в таблицах можно реализовать программным путем.

    Transact-SQL — изменение базы данных и таблиц

    Язык Transact-SQL поддерживает изменение структуры следующих объектов базы данных:

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

    Изменение базы данных

    Для изменения физической структуры базы данных используется инструкция ALTER DATABASE. Язык Transact-SQL позволяет выполнять следующие действия по изменению свойств базы данных:

    добавлять и удалять один или несколько файлов базы данных;

    добавлять и удалять один или несколько файлов журнала;

    добавлять и удалять файловые группы;

    изменять свойства файлов или файловых групп;

    устанавливать параметры базы данных;

    изменять имя базы данных с помощью хранимой процедуры sp_rename.

    Эти разные типы модификаций базы данных рассматриваются далее.

    Добавление и удаление файлов базы данных, файлов журналов и файловых групп

    Добавление или удаление файлов базы данных осуществляется посредством инструкции ALTER DATABASE. Операция добавления нового или удаления существующего файла указывается предложением ADD FILE и REMOVE FILE соответственно. Кроме этого, новый файл можно определить в существующую файловую группу посредством параметра TO FILEGROUP.

    В примере ниже показано добавление нового файла базы данных в базу данных SampleDb:

    В этом примере инструкция ALTER DATABASE добавляет новый файл с логическим именем sampledb_dat1. Здесь же указан начальный размер файла 10 Мбайт и автоувеличение по 5 Мбайт до максимального размера 100 Мбайт. Файлы журналов добавляются так же, как и файлы баз данных. Единственным отличием является то, что вместо предложения ADD FILE используется предложение ADD LOG FILE.

    Удаления файлов (как файлов базы данных, так и файлов журнала) из базы данных осуществляется посредством предложения REMOVE FILE. Удаляемый файл должен быть пустым.

    Новая файловая группа создается посредством предложения CREATE FILEGROUP, а существующая удаляется с помощью предложения DELETE FILEGROUP. Как и удаляемый файл, удаляемая файловая группа также должна быть пустой.

    Изменение свойств файлов и файловых групп

    С помощью предложения MODIFY FILE можно выполнять следующие действия по изменению свойств файла:

    изменять логическое имя файла, используя параметр NEWNAME;

    увеличивать значение свойства SIZE;

    изменять значение свойств FILENAME, MAXSIZE и FILEGROWTH;

    отмечать файл как OFFLINE.

    Подобным образом с помощью предложения MODIFY FILEGROUP можно выполнять следующие действия по изменению свойств файловой группы:

    изменять логическое имя файловой группы, используя параметр NAME;

    помечать файловую группу, как файловую группу по умолчанию, используя для этого параметр DEFAULT;

    помечать файловую группу как позволяющую осуществлять доступ только для чтения или для чтения и записи, используя для этого параметр read_only или read_write соответственно.

    Установка опций базы данных

    Для установки различных опций базы данных используется предложение SET инструкции ALTER DATABASE. Некоторым опциям можно присвоить только значения ON или OFF, но для большинства из них предоставляется выбор из списка возможных значений. Каждый параметр базы данных имеет значение по умолчанию, которое устанавливается в базе данных model. Поэтому значения определенных опций по умолчанию можно модифицировать, изменив соответствующим образом базу данных model.

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

    Опции состояния управляют следующими возможностями:

    доступом пользователей к базе данным (это опции single_user, restricted_user и multi_user);

    статусом базы данных (это опции online, offline и emergency);

    режимом чтения и записи (опции read_only и read_write).

    Опции автоматических операций управляют, среди прочего, остановом базы данных (опция auto_close) и способом создания статистики индексов (опции auto_create_statistics и auto_update_statistics).

    Опции SQL управляют соответствием базы данных и ее объектов стандарту ANSI. Значения всех операторов SQL можно узнать посредством функции DATABASEPROPERTY, а редактировать — с помощью инструкции ALTER DATABASE.

    Опции восстановления full, bulk-logged и simple управляют процессом восстановления базы данных.

    Хранение данных типа FILESTREAM

    При описании типов данных T-SQL мы рассмотрели данные типа FILESTREAM и причины, по которым их используют. В этом разделе мы рассмотрим, как данные типа FILESTREAM можно сохранять в базе данных. Чтобы данные FILESTREAM можно было сохранять в базе данных, система должна быть должным образом инициирована. В следующем подразделе объясняется, как инициировать операционную систему и экземпляр базы данных для хранения данных типа FILESTREAM.

    Инициирование хранилища FILESTREAM

    Хранилище данных типа FILESTREAM требуется инициировать на двух уровнях:

    для операционной системы Windows;

    для конкретного экземпляра сервера базы данных.

    Инициирование хранилища данных типа FILESTREAM на уровне системы осуществляется с помощью диспетчера конфигурации SQL Server Configuration Manager. Чтобы запустить диспетчер конфигурации, выполните следующую последовательность команд по умолчанию Пуск —> Все программы —> Microsoft SQL Server 2012 —> Configuration Tools . В открывшемся окне Sql Server Configuration Manager щелкните правой кнопкой пункт SQL Server Services (Службы SQL Server) и в появившемся контекстном меню выберите команду Open. В правой панели щелкните правой кнопкой экземпляр, для которого требуется разрешить хранилище FILESTREAM, и в контекстном меню выберите команду Properties. В открывшемся диалоговом окне SQL Server Properties выберите вкладку FILESTREAM:

    Диалоговое окно SQL Server Properties, вкладка FILESTREAM

    Чтобы иметь возможность только читать данные типа FILESTREAM, установите флажок Enable FILESTREAM for Transact-SQL access (Разрешить FILESTREAM при доступе через Transact-SQL). Чтобы кроме чтения можно было также записывать данные, установите дополнительно флажок Enable FILESTREAM for file I/O streaming access (Разрешить использование FILESTREAM при доступе файлового ввода/вывода). Введите имя общей папки Windows в одноименное поле. Общая папка Windows используется для чтения и записи данных FILESTREAM, используя интерфейс API Win32. Если для возвращения пути для FILESTREAM BLOB использовать имя, то это будет имя общей папки Windows.

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

    Чтобы разрешить хранилище FILESTREAM, необходимо быть администратором Windows локальной системы и обладать правами администратора (sysadmin). Чтобы изменения вступили в силу, необходимо перезапустить экземпляр сервера базы данных.

    Следующим шагом будет разрешить хранилище FILESTREAM для конкретного экземпляра. Мы рассмотрим, как выполнить эту задачу с помощью среды SQL Server Management Studio. (Для этого можно также воспользоваться хранимой системной процедурой sp_configure с параметром FILESTREAM ACCESS LEVEL.) Щелкните правой кнопкой требуемый экземпляр в обозревателе объектов и в появившемся контекстном меню выберите пункт Properties, в левой панели открывшегося диалогового окна Server Properties выберите пункт Advanced (Дополнительно):

    Диалоговое окно Server Properties с уровнем доступа FILESTREAM, установленным в Full Access Enabled

    После этого в правой панели из выпадающего списка выберите FILESTREAM Access Level (Уровень доступа FILESTREAM) одну из следующих опций:

    Disabled

    Отключено — хранилище FILESTREAM не разрешено.

    Transact-SQL Access Enabled

    Включен доступ с помощью Transact-SQL — к данным FILESTREAM можно обращаться посредством инструкций T-SQL.

    Full Access Enabled

    Включен полный доступ — к данным FILESTREAM можно обращаться как посредством инструкций T-SQL, так и через интерфейс API Win32.

    Добавление файла в файловую группу

    Разрешив хранилище FILESTREAM для требуемого экземпляра, можно сначала создать файловую группу для данных FILESTREAM (посредством инструкции ALTER DATABASE), а затем добавить файл в эту файловую группу, как это показано в примере ниже. (Конечно же, эту задачу также можно было бы выполнить с помощью инструкции CREATE DATABASE.)

    Первая инструкция ALTER DATABASE в примере добавляет в базу данных SampleDb новую файловую группу Employee_FSGroup. Параметр CONTAINS FILESTREAM этой инструкции указывает системе, что данная файловая группа будет содержать только данные FILESTREAM. Вторая инструкция ALTER DATABASE добавляет в созданную файловую группу новый файл.

    Теперь можно создавать таблицы, содержащие столбцы с типом данных FILESTREAM. Создание такой таблицы показано в примере ниже:

    В этом примере таблица EmployeeInfo содержит столбец FilestreamData, тип данных которого должен быть VARBINARY(MAX). Определение такого столбца включает атрибут FILESTREAM, указывающий, что данные столбца сохраняются в файловой группе FILESTREAM. Для всех таблиц, в которых хранятся данные типа FILESTREAM, требуется наличие свойств UNIQUE ROWGUIDCOL. Поэтому таблица EmployeeInfo содержит столбец Id, определенный с использованием этих двух атрибутов.

    Данные в столбце типа FILESTREAM вставляются посредством стандартной инструкции INSERT. А для считывания данных используется стандартная инструкция SELECT.

    Автономные базы данных

    Одна из значительных проблем с базами данных SQL Server состоит в том, что они трудно поддаются экспортированию и импортированию. Как рассматривалось ранее, базы данных можно присоединять и отсоединять, но при этом утрачиваются важные части и свойства присоединенных баз данных. (Основной проблемой в таких случаях является безопасность базы данных, в общем, и учетные записи, в частности, в которых после перемещения обычно отсутствует часть информации или содержится неправильная информация.)

    Разработчики Microsoft планируют решить эти проблемы посредством использования автономных баз данных (contained databases). Автономная база данных содержит все параметры и данные, необходимые для определения базы данных, и изолирована от экземпляра Database Engine, на котором она установлена. Иными словами, база данных данного типа не имеет конфигурационных зависимостей от экземпляра и ее можно с легкостью перемещать с одного экземпляра SQL Server на другой.

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

    полностью автономные базы данных;

    частично автономные базы данных;

    неавтономные базы данных.

    Полностью автономными являются такие базы данных, объекты которых не могут перемещаться через границы приложения. (Граница приложения определяет область видимости приложения. Например, пользовательские функции находятся в границах приложения, в то время как функции, связанные с экземплярами сервера, находятся вне границ приложения.)

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

    В SQL Server 2012 поддерживаются частично автономные базы данных. В будущих версиях SQL Server также будет поддерживаться полная автономность. Базы данных предшествующих версий SQL Server являются неавтономными.

    Рассмотрим, как создать частично автономную базу данных в SQL Server 2012. Если существующая база данных SampleDb является неавтономной (созданная, например, посредством инструкции CREATE DATABASE), с помощью инструкции ALTER DATABASE ее можно преобразовать в частично автономную, как это показано в примере ниже:

    Инструкция ALTER DATABASE изменяет состояние автономности базы данных SampleDb с неавтономного на частично автономное. Это означает, что теперь система базы данных позволяет создавать как автономные, так неавтономные объекты для базы данных SampleDb. Все другие инструкции в примере являются вспомогательными для инструкции ALTER DATABASE.

    Функция sp_configure является системной процедурой, с помощью которой можно, среди прочего, изменить дополнительные параметры конфигурации, такие как ‘contained database authentication’. Чтобы изменить дополнительные параметры конфигурации, сначала нужно присвоить параметру ‘show advanced options’ значение 1, а потом переконфигурировать систему (инструкция RECONFIGURE). В конце кода этому параметру опять присваивается его значение по умолчанию — 0.

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

    Изменение таблиц

    Для модифицирования схемы таблицы применяется инструкция ALTER TABLE. Язык Transact-SQL позволяет осуществлять следующие виды изменений таблиц:

    добавлять и удалять столбцы;

    изменять свойства столбцов;

    добавлять и удалять ограничения для обеспечения целостности;

    разрешать или отключать ограничения;

    переименовывать таблицы и другие объекты базы данных.

    Эти типы изменений рассматриваются в последующих далее разделах.

    Добавление и удаление столбцов

    Чтобы добавить новый столбец в существующую таблицу, в инструкции ALTER TABLE используется предложение ADD. В одной инструкции ALTER TABLE можно добавить только один столбец. Применение предложения ADD показано в примере ниже:

    В этом примере инструкция ALTER TABLE добавляет в таблицу Employee столбец PhoneNumber. Компонент Database Engine заполняет новый столбец значениями NULL или IDENTITY или указанными значениями по умолчанию. По этой причине новый столбец должен или поддерживать значения NULL, или для него должно быть указано значение по умолчанию.

    Новый столбец нельзя вставить в таблицу в какой-либо конкретной позиции. Столбец, добавляемый предложением ADD, всегда вставляется в конец таблицы.

    Столбцы из таблицы удаляются посредством предложения DROP COLUMN. Применение этого предложения показано в примере ниже:

    В этом коде инструкция ALTER TABLE удаляет в таблице Employee столбец PhoneNumber, который был добавлен в эту таблицу предложением ADD ранее.

    Изменение свойств столбцов

    Для изменения свойств существующего столбца применяется предложение ALTER COLUMN инструкции ALTER TABLE. Изменению поддаются следующие свойства столбца:

    поддержка значения NULL.

    Применение предложения ALTER COLUMN показано в примере ниже:

    Инструкция ALTER TABLE в этом примере изменяет начальные свойства (nchar(40), значения NULL разрешены) столбца Location таблицы Department на новые (nchar(25), значения NULL не разрешены).

    Добавление и удаления ограничений для обеспечения целостности (ключей и проверок)

    Для добавления в таблицу новых ограничений для обеспечения целостности используется параметр ADD CONSTRAINT инструкции ALTER TABLE. В примере ниже показано использование параметра ADD CONSTRAINT для добавления проверочного ограничения и определения первичного ключа таблицы:

    В этом примере сначала инструкцией CREATE TABLE создается таблица Sales, содержащая два столбца с типом данных DATE: OrderDate и ShipDate. Далее, инструкция ALTER TABLE определяет ограничение для обеспечения целостности order_check, которое сравнивает значения обоих этих столбцов и выводит сообщение об ошибке, если дата отправки ShipDate более ранняя, чем дата заказа OrderDate. Далее инструкция ALTER TABLE используется для определения первичного ключа таблицы в столбце Id.

    Ограничения для обеспечения целостности можно удалить посредством предложения DROP CONSTRAINT инструкции ALTER TABLE, как это показано в примере ниже:

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

    Разрешение и запрещение ограничений

    Как упоминалось ранее, ограничение для обеспечения целостности всегда имеет имя, которое может быть объявленным или явно посредством опции CONSTRAINT, или неявно посредством системы. Имена всех ограничений таблицы (объявленных как явно, так и неявно) можно просмотреть с помощью системной процедуры sp_helpconstraint.

    В последующих операциях вставки или обновлений значений в соответствующий столбец ограничение по умолчанию обеспечивается принудительно. Кроме этого, при объявлении ограничения все существующие значения соответствующего столбца проверяются на удовлетворение условий ограничения. Начальная проверка не выполняется, если ограничение создается с параметром WITH NOCHECK. В таком случае ограничение будет проверяться только при последующих операциях вставки и обновлений значений соответствующего столбца. (Оба параметра — WITH CHECK и WITH NOCHECK — можно применять только с ограничениями проверки целостности CHECK и проверки внешнего ключа FOREIGN KEY.)

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

    Все ограничения таблицы Sales отключаются посредством ключевого слова ALL. Применять опцию NOCHECK не рекомендуется, поскольку любые подавленные нарушения условий ограничения могут вызвать ошибки при будущих обновлениях.

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

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

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

    Удаление объектов баз данных

    Все инструкции Transact-SQL для удаления объектов базы данных имеют следующий общий вид:

    Для каждой инструкции CREATE object для создания объекта имеется соответствующая инструкция DROP object для удаления. Инструкция для удаления одной или нескольких баз данных имеет следующий вид:

    Эта инструкция безвозвратно удаляет базу данных из системы баз данных. Для удаления одной или нескольких таблиц применяется следующая инструкция:

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

    Кроме объектов DATABASE и TABLE, в параметре objects инструкции DROP можно указывать, среди прочих, следующие объекты:

    Добавить комментарий

    Ваш адрес email не будет опубликован. Обязательные поля помечены *