Методические указания к выполнению лабораторных и курсовой работ ЙошкарОла 2007
 Скачать 0.8 Mb.
|
|
Содержание отчета 1. Задание; 2. Операторы создания и удаления таблицы; 3. Операторы создания и удаления представления; 4. Операторы объявления и закрытия курсора; 5. Операторы создания и удаления индекса; 6. Операторы манипулирования данными, относящиеся к базовой, представляемой и результирующей таблицам; 7. Исходная базовая таблица; 8. Исходная представляемая таблица; 9. Исходная результирующая таблица курсора; 10. Измененные таблицы (базовая, представляемая и результирующая) и ссылки на соответствующие им операторы изменения таблиц (для каждого акта изменения). 11. Операторы создания и вызова хранимой процедуры, результаты выполнения. 12. Операторы создания триггера и операторы, запускающие триггер, результаты выполнения. 13. Операторы создания и вызова пользовательской функции, результаты выполнения. Типы данных Transact-SQL Символьные типы данных CHAR[(n)] - cтроки фиксированной длины, где n - число символов в строке; VARCHAR[(n)] - строки переменной длины , где n - максимальное число символов в строке; TEXT - строки потенциально неограниченного размера (до 2 Гб текста в строке). В данном случае 1 n 255. Символьные столбцы, допускающие пустые значения (NULL), хранятся как столбцы переменной длины. Примеры определений столбцов и типов данных: name VARCHAR(40) state CHAR(2) description CHAR(50) NULL Двоичные типы данных BINARY(n) - двоичные строки фиксированной длины, где n - число двоичных символов в строке; VARBINARY(n) - двоичные строки переменной длины, где n - макси- мальное число двоичных символов в строке; IMAGE - большие двоичные строки (изображения до 2 Гб в строке). В данном случае 1 n 255. Пример задания двоичного столбца: bin_column BINARY(4) NOT NULL Типыданныхдаты SQL Server поддерживает два типа обозначения даты и времени при хранении: DATETIME и SMALLDATETIME. Последний менее точный и охватывает меньший диапазон дат, но зато позволяет экономить место на диске. SQL Server поддерживает различные форматы ввода даты. По умолчанию он работает с датами в формате xx/yy/zz как с последовательностями месяц/день/год. Точность представления времени при использовании DATETIME - 3 миллисекунды, а при использовании SMALLDATETIME - 1 минута. Пример: Формат ввода: 4/15/99 Значение DATETIME: Apr 15 1999 12:00:00:000 AM Значение SMALLDATETIME: Apr 15 1999 12:00 AM Логический тип данных Server поддерживает логический тип данных BIT для столбцов флагов, имеющих значение 1 или 0. Числовые типы данных Числовые типы данных разбиваются на четыре основные категории: • целые, включающие INT, SMALLINT и TINYINT. • данные с плавающей точкой, включающие FLOAT и REAL. • данные с фиксированной точкой - NUMERIC и DECIMAL • денежные типы данных - MONEY и SMALLMONEY. Целые типы данных
Типы данных с плавающей точкой
Спецификатор типа FLOAT имеет вид FLOAT[(p)], где p - точность. Точные числовые типы данных Эти типы данных вводится описателями DECIMAL[(p,s)] и NUMERIC[(p,s)], где p - точность, s - масштаб. Они являются синонимами и взаимозаменяемы, но только NUMERIC может использоваться в комбинации со столбцами IDENTITY. Точность - это число значащих цифр, масштаб - число цифр после десятичной точки. Пример: NUMERIC(7,2). Если опущен масштаб, то он полагается равным нулю, а если опущена точность, то ее значение по умолчанию определяется в реализации. Денежные типы данных
Создание таблицы Оператор создания таблицы имеет следующий синтаксис: <оператор создания таблицы>::= CREATE TABLE <имя таблицы> (<элемент таблицы>[{,<элемент таблицы>}...]) <элемент таблицы>::=<определение столбца> | <определение ограничения целостности> Каждая таблица БД имеет простое и квалифицированное (уточненное) имена. В качестве квалификатора имени выступает “идентификатор полномочий”. Квалифицированное имя таблицы имеет вид: <идентификатор полномочий>.<простое имя> Определение столбца <определение столбца>::= <имя столбца><тип данных>[<раздел умолчания>] [{<ограничение целостности столбца>}...] <раздел умолчания>::= DEFAULT {<литерал> | USER | NULL} <ограничение целостности столбца>::= NOT NULL[<спецификация уникальности>] | <спецификация ссылок> | CHECK (<условие поиска>) В разделе умолчания указывается значение, которое должно быть помещено в строку, заносимую в данную таблицу, если значение данного столбца явно не указано. Значение по умолчанию может быть: 1) литеральная константа, соответствующая типу столбца; 2) символьная строка, содержащая имя текущего пользователя (USER); 3) неопределенное значение (NULL). Если значение столбца по умолчанию не специфицировано, и в разделе ограничений целостности столбца указано NOT NULL (т.е. наличие неопределенных значений запрещено), то попытка занести в таблицу строку с неспецифицированным значением данного столбца приведет к ошибке. Если ограничение NOT NULL не указано, и раздел умолчаний отсутствует, то неявно порождается раздел умолчаний DEFAULT NULL. Ограничения целостности столбца в принципе сходны с ограничениями целостности таблицы и рассмотрены ниже. Определение ограничений целостности таблицы Синтаксис для определения ограничений целостности таблицы представлен следующими правилами: <определение ограничений целостности таблицы>::= <определение ограничения уникальности> | <определение ограничения по ссылкам> | <определение проверочного ограничения> <определение ограничения уникальности>::=<спецификация уникальности>(<список столбцов>) <спецификация уникальности>::= UNIQUE | PRIMARY KEY <список столбцов>::= <имя столбца>[{,<имя столбца>}..] <определение ограничения по ссылкам>::= FOREIGN KEY (<ссы- лающиеся столбцы>)<спецификация ссылок> <спецификация ссылок>::== REFERENCES <ссылаемая таблица и столбцы> <ссылаемая таблица и столбцы>::=<имя таблицы>[(<список столбцов>)] <определение проверочного ограничения>::= CHECK (<условие поиска>) Действие ограничения уникальности состоит в том, что в таблице не допускается появление двух или более строк, значения столбцов уникальности которых совпадают. Среди ограничений уникальности таблицы не должно быть более одного определения первичного ключа (ограничения уникальности с ключевым словом PRIMARY KEY). Ограничения по ссылкам в данной работе не используются, и по- этому подробно не рассматриваются. Проверочное ограничение специфицирует условие, которому должен удовлетворять в отдельности каждая строка таблицы. Это условие не должно содержать подзапросов, спецификаций агрегатных функций, а также ссылок на внешние переменные или параметров. В него могут входить только имена столбцов данной таблицы и литеральные константы. Примеры создания таблиц с ограничениями: CREATE TABLE employee (emp_id INTEGER CONSRAINT p1 PRIMARY KEY, fname CHAR(20) NOT NULL, minitial CHAR(1) NULL, lname VARCHAR(30) NOT NULL, job_id SMALLINT NOT NULL DEFAULT 1 REFERENCES jobs(job_id) CREATE TABLE inventory (code CHAR(4) NOT NULL CONSTRAINT c1 CHECK(code LIKE”[0-9][0-9][0-9][0-9]”), high INT NOT NULL CHECK (high>0), low INT NOT NULL CHECK (low>0), CONSTRAIN c4 CHECK (hign>=low AND high-low<1000) Изменение таблиц Для изменения таблицы, а именно: для включения новых столбцов и ограничений, а также удаления ограничений, используется оператор ALTER TABLE, имеющий следующий синтаксис: <оператор изменения таблицы>::= ALTER TABLE <имя таблицы> {ADD <элемент таблицы>[{,<элемент таблицы>}...] | DROP CONSTRAINT <имя ограничения>[{,<имя ограничения>}...]} Пример включения нового столбца в таблицу: ALTER TABLE names2 ADD middle_name VARCHAR(20) NULL, fax VARCHAR(15) NULL Создание представлений Механизм представлений является мощным средством языка SQL, позволяющим скрыть реальную структуру БД от некоторых пользователей за счет определения представления БД. Представление реально является некоторым хранимым в БД запросом с именованными столбцами, а для пользователя ничем не отличается от базовой таблицы БД. Представляемая таблица является виртуальной. Обычно вычисление представляемой таблицы производится каждый раз при использовании представления. Оператор определения представления имеет следующий синтаксис: <оператор создания представления>::= CREATE VIEW <имя таблицы>[(список столбцов)] AS <спецификация запроса> [WITH CHECK OPTION] <спецификация запроса>::= SELECT [ALL | DISTINCT] <список вы- борки><табличное выражение> <список столбцов>::=<имя столбца>[{,<имя столбца>}...] Требование WITH CHECK OPTION имеет смысл только в случае определения изменяемой представляемой таблицы, которая определяется спецификацией запроса, содержащей раздел WHERE. При наличии этого требования не допускаются изменения представляемой таблицы, приводящие к появлению в базовых таблицах строк, не видимых в представляемой таблице. Примеры создания представлений: CREATE VIEW ta_limited AS SELECT au_id, title_id, au_ord FROM titleauthor CREATE VIEW cal_publishers AS SELECT * FROM publishers WHERE state=”CA” Операторы, связанные с курсором Курсор - это механизм языка SQL, предназначенный для того, что-бы позволить прикладной программе последовательно, строка за строкой, просмотреть результат связанного с курсором запроса. Курсор можно представить как “буфер” с указателем на текущую строку. Ниже приводится синтаксис операторов, связанных с курсором и их краткая характеристика. <оператор объявления курсора>::= DECLARE <имя курсора> [SCROLL] CURSOR FOR <спецификация курсора> <спецификация курсора>::= SELECT [ALL | DISTINCT] <список выборки> <табличное выражение>[ORDER BY <спецификация сортировки>] Этот оператор не является выполняемым, он только связывает имя курсора со спецификацией курсора. Если задан описатель SCROLL, то курсор является “скроллируемым”, то есть допускает прокрутку результирующей таблицы как вниз, так и вверх на любое число строк. <оператор открытия курсора>::= OPEN <имя курсора> Оператор открытия курсора должен быть первым в серии выполняемых операторов, связанных с данным курсором. Можно считать, что во время выполнения оператора открытия курсора производится построение временной таблицы, содержащей результат запроса, который связан с этим курсором. <оператор чтения>::= FETCH <имя курсора> INTO <список спецификаций целей> <список спецификаций целей>::= <спецификация цели>[{,<спецификация цели>}..] Данный оператор устанавливает курсор на следующую строку таблицы и выбирает значения из этой строки. <оператор позиционного удаления>::= DELETE FROM <имя таблицы> WHERECURRENTOF <имя курсора> Данный оператор удаляет строку таблицы. Изменяемая таблица, указанная в разделе FROM оператора DELETE, должна быть таблицей, указанной в самом внешнем разделе FROM спецификации курсора. <оператор позиционной модификации>::= UPDATE <имя таблицы> SET <предложение установки> [{,<предложение установки>}...] WHERE CURRENT OF <имякурсора> <предложение установки>::= <имя столбца> = {<арифметическое выражение> | NULL} Данный оператор изменяет значение полей строки таблицы, определенной курсором, в соответствии с предложениями установки. <оператор закрытия курсора>::= CLOSE <имя курсора> Примеры работы с курсором: DECLARE mycursor SCROLL CURSOR FOR SELECT au_lname FROM authors OPEN mycursor FETCH FIRST FROM mycursor /* первая строка */ FETCH ABSOLUTE 10 FROM mycursor FETCH NEXT FROM mycursor /* следующая строка */ FETCH RELATIVE 2 FROM mycursor FETCH PRIOR FROM mycursor /* предыдущая строка */ FETCH LAST FROM mycursor /* последняя строка */ CLOSE mycursor Одиночные операторы манипулирования данными Каждый из операторов этой группы является абсолютно независимым от другого оператора. <оператор выборки>::= SELECT [ALL | DISTINCT] <список выборки> [INTO <список спецификаций целей>]<табличное выражение> Результатом выполнения оператора выборки является таблица, состоящая не более чем из одной строки. После выполнения оператора цели содержат соответствующие поля результирующей строки. <оператор поискового удаления>::= DELETE FROM <имя таблицы> [WHERE <условие поиска>] При выполнении оператора последовательно просматриваются все строки таблицы, и те строки, для которых результатом вычисления условия поиска является “истина”, удаляются из таблицы. При отсутствии раздела WHERE удаляются все строки таблицы. Примеры: DELETE authors DELETE titles WHERE type= “business” <оператор поисковой модификации>::= UPDATE <имя таблицы> SET <предложение установки >[{,<предложение установки>}…] [WHERE <условие поиска>] При выполнении оператора просматриваются все строки таблицы, и каждая строка, для которой результатом вычисления условия поиска является “истина”, изменяется в соответствии с разделом SET. Пример: UPDATE publishers SET pub_name= “Joe’s Press” WHERE pub_id= “1234” <оператор включения>::= INSERT INTO <имя таблицы>[(<список столбцов>)] {VALUES (<список значений >) | <подзапрос>} Оператор включения добавляет строку в таблицу. При это строка формируется или из списка значений раздела VALUES, или вычисляется с помощью подзапроса. Список столбцов определяет те столбцы, для которых явно будет указано их значение. Причем i-му столбцу в списке столбцов соответствует i-ое значение из списка значений или i-я строка результата подзапроса. Если список столбцов опущен, то для каждого столбца таблицы должно быть точно указаны (или вычислены) значения, в порядке, в котором они были определены. При вставке символьных данных или поиске значения в конструкции WHERE значение необходимо передавать в одиночных или двойных кавычках. Для вставки в столбец двоичных данных их нужно указывать без кавычек, начиная с 0х и задавая два шестнадцатеричных символа для каждого байта данных. Примеры: INSERT INTO publishers (pub_id, pub_name, cite, state) VALUES (‘1234’, ‘Stendahl Publishing’, ‘Paris’, ‘France’) INSERT INTO binary_example(id, bin_column) VALUES(19,0xa134e2ff) Создание индекса Индекс представляет собой объект, ускоряюший выполнение запросов. Синтаксис оператора создания индекса имеет вид: <оператор создания индекса>::= CREATE [UNIQUE] INDEX <имя индекса> ON <имя таблицы> (<имя столбца> [ASC | DESC] [{,<имя столбца>[ASC | DESC]}..]) Описатель уникальности UNIQUE указывает, что никаким двум строкам в индексируемой базовой таблице не позволяется принимать одно и тоже значение для индексируемого столбца (или комбинации столбцов) в одно и то же время. Описатели ASC и DESC определяют, что столбец должен быть отсортирован в возрастающем или убывающем порядке в пределах индекса. В Transact-SQL описатели ASC и DESC не используются. Удаление объектов базы данных Для удаления объектов базы данных используются соответствующие операторы, синтаксис которых представлен ниже. <оператор удаления таблицы>::= DROP TABLE <имя таблицы> <оператор удаления представления>::=DROP VIEW <имя представления> <оператор удаления представления>::= DROP INDEX <имя индекса> Хранимые процедуры Хранимые процедуры – еще одно средство выполнения операторов Transact-SQL. Мы уже сталкивались с SQL-сценариями и с возможностью передавать команды непосредственно из приложения. Однако хранимые процедуры обладают рядом преимуществ: • хранимые процедуры являются объектами базы данных; они размещаются в файле базы данных и перемещаются вместе с файлом в случае отключения или репликации базы данных; • хранимые процедуры позволяют вам передавать данные процедуре для их обработки и принимать обратно от процедуры как данные, так и сформированный процедурой итоговый код; • хранимые процедуры представляются в оптимизированной форме, что дает возможность ускорить их выполнение. Обычные SQL-сценарии выполнялись независимо — у нас не было никакой возможности передать им какую-либо информацию, а единственная информация, которую они возвращали, отображалась в панелях сетки Grid или в панели сообщений Message Pane окна Query. Хранимые процедуры предоставляют два метода взаимодействия с внешними процессами: через параметры и через возвращаемые значения. Параметры представляют собой специальный тип локальных переменных, объявляемых как часть хранимой процедуры. Вы можете использовать параметры для передачи информации хранимым процедурам (входные параметры) или получения данных обратно из хранимой процедуры (выходные параметры). Возвращаемое значение схоже с результатом выполнения функции и аналогичным образом может быть присвоено локальной переменной. Возвращаемые значения всегда являются целыми числами. Теоретически они могут быть использованы для возврата любого результата, но в соответствии с соглашением они применяются для возврата статуса выполнения хранимой процедуры. Например, хранимая процедура может возвращать 0, если все идет нормально, или -1, если возникла ошибка. Более сложные хранимые процедуры могут возвращать различные значения для указания типа ошибки. Важно не путать параметры и возвращаемые коды с какими-либо результирующими множествами, которые может возвращать хранимая процедура. Хранимая процедура может содержать любое количество операторов SELECT, которые будут возвращать результирующие множества. Для их получения вам не нужно использовать параметр; они будут возвращены в программу приложения независимо. Хранимые процедуры делятся на две группы: системные процедуры, создаваемые SQL Server, и пользовательские процедуры, которые вы создаете самостоятельно. Системные хранимые процедуры хранятся в главной базе данных. Все они начинаются с символов sp_. Поскольку SQL Server всегда ищет хранимые процедуры прежде всего в главной базе данных, если существует системная процедура с таким же именем, ваша хранимая процедура никогда не будет выполнена. Системные процедуры В главной базе данных около сотни системных процедур. Многие из них предоставляют средства для программного выполнения задач администрирования. Например, процедура sp_addlogin позволяет добавлять идентификатор учетной записи, а процедура sp_add_jobschedule дает возможность составлять расписание заданий, таких как резервное копирование базы данных. Другие системные процедуры помогают управлять объектами базы данных. Например, процедура sp_rename дает возможность переименовывать объекты базы данных, а процедура sp_renamedb предоставляет средства для переименования базы данных. Важная группа системных процедур предоставляет информацию о текущем статусе системы: процедура sp_who предоставляет информацию о текущих пользователях и процессах; процедура sp_cursor_list предоставляет список текущих курсоров для данного соединения; процедура sp_helpdb предоставляет список всех текущих баз данных, обслуживаемых сервером, а также сообщает вам физическое местоположение файла данных и журнала транзакций для любой заданной базы данных. Информацию об объектах текущей базы данных можно получить, запустив хранимую процедуру SP_HELP, выполнив оператор SP_HELP <имя объекта> Для объекта-таблицы отображаются: имя собственника таблицы; дата и время ее создания; имена столбцов таблицы и их типы данных; имена, описания и ключи индексов, связанных с таблицей; типы, имена и описания ограничений столбцов и таблицы в целом. Без входных параметров эта процедура возвращает список всех объектов, их собственников и типов объектов. Для получения информации только об ограничениях таблицы можно воспользоваться хранимой процедурой SP_HELPCONSTRAINT, выполнив оператор SP_HELPCONSTRAINT <имя таблицы> Для получения информации только об индексах таблицы можно воспользоваться хранимой процедурой SP_HELPINDEX, выполнив оператор SP_HELPINDEX <имя таблицы> Для того, чтобы получить список и описания объектов класса X, определенных в базе данных, можно выполнить оператор SELECT * FROM sysobjects WHERE type= ‘X’ Возможные значения параметра X: U - таблица, V - представление, С - проверочное ограничение, F - ограничение по ссылкам, K - ограничение уникальности, D - раздел умолчаний. Пользовательские хранимые процедуры Подобно таблицам базы данных, пользовательские хранимые процедуры представляют собой объект, являющийся частью базы данных, в которой он создан и будет перемещаться вместе с ней. Различие заключается в том, что объекты хранимых процедур содержат не данные, а код Transact-SQL. SQL Server создает в каждой базе данных несколько хранимых процедур. Все имена этих хранимых процедур начинаются с dt_. Они используются для управления исходными данными, а также при преобразовании данных. Выполнение хранимой процедуры обычно занимает меньше времени, чем запуск пакета команд из приложения. Дело в том, что SQL Server может выполнять некоторые действия из выполняемой процедуры с опережением, а затем сохранять результаты выполнения этих действий в хранимой процедуре. Использование хранимых процедур Для вызова пользовательских и системных хранимых процедур используется оператор EXECUTE. Если хранимая процедура не требует параметров или не возвращает результат, синтаксис ее будет очень простым: EXECUTE имя_процедуры Ключевое слово EXECUTE можно не указывать, если вызов хранимой процедуры является первым оператором в пакете. Однако, как и в других подобных случаях, лучше лишний раз подстраховаться. Поэтому лучше всегда использовать ключевое слово EXECUTE или его аббревиатуру EXEC при использовании хранимой процедуры. Если хранимая процедура принимает входные параметры, вы можете предоставить их, указав позицию или имя. Чтобы предоставить параметры по позиции, нужно просто указать их после имени хранимой процедуры, отделяя запятыми: |