SYBASE (Задания по бд), страница 8

convert (<тип данных>, <выражение> [, <стиль>])

Пример:

select title, convert(char(5), total_sales) from titles

Правила преобразования:

1) преобразование данных типов character в данные типов money, date/time, всех типов numeric и approx_numeric осуществляется только в том случае, если все символы в строке допустимы в новом типе;

2) при преобразовании целых типов данных в тип character необходимо указывать соответствующую длину строки символов;

1. функцию convert можно использовать при преобразовании типов money, date/time, всех типов numeric, integer, binary и image.

Функции inttohex и hextoint служат для преобразования целых чисел в шестнадцатиричные и наоборот.

Операция Join (соединения таблиц).

Для выполнения операции соединения нескольких таблиц в предложении SELECT необходимо соблюдать три условия:

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

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

3) В предложении where указываются все связи таблиц, где каждая таблица соединяется с другой по полям, определенным на одинаковых доменах, с помощью операций сравнения: =, >, >=, <, <=, !=, !>, !<.

Примеры использования операции join приведены в разделе “Хранимые процедуры”.

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

1) *= - включаются все записи из первой названной таблицы;

1. =* - включаются все записи из второй названной таблицы.

При этом, если указывается операция “*=”, то в отчет включаются все записи из первой таблицы, а в колонках, принадлежащих второй таблице, в записях, не удовлетворяющих условию соединения, ставятся null значения. Аналогично, если указывается операция “=*”, то в отчет включаются все записи из второй таблицы, а в колонках, принадлежащих первой таблице, в записях, не удовлетворяющих условию соединения, ставятся null значения.

Пример:

select au_fname, au_lname, pub_name from authors, publishers

where authors.city *= publishers.city

Подзапросы

Подзапрос - это select - предложение, вложенное в другое select-, insert-, update- или delete-предложение или в другой подзапрос.

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

1) where <выражение> [not] in (<подзапрос>)

2) where <выражение> <операция сравнения> [any|all] (<подзапрос>)

1. where [not] exists (<подзапрос>)

Пример подзапроса приведен в разделе “Хранимые процедуры”.

Подмножества

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

Представления используются для:

1. предоставления пользователю только интересующих его данных;

2. упрощения манипулирования данными (представления определяются сложными операциями выборки, проекции и соединения);

3. предоставления различным пользователям видеть одни и те же данные по-разному;

4. обеспечения механизма секретности данных;

5. поддержки логической независимости данных.

Механизм секретности данных осуществляется командами grant и revoke (рассматриваются в разделе “Администрирование”), примененным к представлениям. При этом пользователи получают доступ к различным подмножествам данных:

1. доступ к подмножеству записей базовой таблицы;

2. доступ к подмножеству колонок базовой таблицы;

3. доступ к подмножеству записей и колонок базовой таблицы;

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

5. доступ к статистическим итогам данных в базовой таблице;

6. доступ к подмножеству другого представления или некоторой комбинации представлений и базовых таблиц.

В основе команды создания представления лежит оператор select. Синтаксис команды:

create view [[<имя БД>.] <владелец>.] <имя представления>

[(<имя колонки> [, <имя колонки>]…)]

as <select-предложение>

[with check option]

Пример:

create view pub_view (publisher, city, state)

as select pub_name, city, state from publishers

Существует несколько ограничений на использование select-предложения в создании представления:

1. нельзя использовать структуры order by и compute;

2. нельзя использовать слово into;

3. нельзя ссылаться на временную таблицу.

Структура with check option используется в тех случаях, когда данное представление будет использоваться командами insert и update. При этом будут контролироваться вводимые и обновляемые записи на соответствие select-предложению в представлении.

Существуют ограничения применения операторов изменения данных (update, insert и delete рассматриваются в следующем разделе) к представлениям:

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

2. нельзя применять эти операторы к представлениям, использующим агрегатные функции и структуру group by;

3. нельзя применять эти операторы к представлениям, использующим distinct;

4. нельзя использовать insert для представлений, построенных на таблицах с колонками not null, когда в эти колонки попадают null значения;

5. нельзя использовать delete для представлений, построенных на нескольких таблицах;

6. нельзя использовать insert для представлений, построенных на нескольких таблицах с опцией with check option;

7. нельзя применять insert и update в представлении, построенном на нескольких таблицах с опцией distinct;

8. нельзя использовать update для колонки identity.

Удаление представлений осуществляется командой drop view:

drop view [[<имя БД>.] <владелец>.] <имя представления>

[, [[<имя БД>.] <владелец>.] <имя представления>]…

Пример:

drop view pub_view

4. ОБНОВЛЕНИЕ ДАННЫХ

Понятие транзакции

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

Транзакции обеспечивают:

1. целостность данных при работе операторов манипулирования данными (insert, update и delete);

2. восстановление данных в случае сбоев.

По умолчанию, каждая из команд insert, update и delete рассматривается как одна транзакция.

Каждая определяемая пользователем транзакция, состоящая из операторов языка SQL, начинается командой begin transaction (начать транзакцию), а заканчивается либо командой commit transaction (выполнить транзакцию), либо командой rollback transaction (отменить транзакцию).

Понятие пакета

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

Оператор INSERT рассматривался выше при описании загрузки таблиц БД.

Пример:

insert poss values

(534305, 'ЖУРАВЛЕВ АВЕРЬЯН АЛЕКСЕЕВИЧ', '000073', 'M', '0', '001001', '05', 0129000, '08', '90', '03','95')

Оператор DELETE служит для удаления записей из таблицы по заданному условию поиска.

delete [from][[<имя БД>.]<владелец>.]{<имя таблицы>|<имя представления>}

[from][[<имя БД>.]<владелец>.]{<имя таблицы>|<имя представления>}

[,[[<имя БД>.<владелец>.]<имя таблицы>|<имя представления>]]...]

[where <условие поиска>]

Пример:

delete poss where nomer = 534305

удаление из таблицы poss записи с данными о Журавлеве Аверьяне Алексеевиче (см. предыдущий пример).

Оператор TRUNCATE позволяет быстро удалить все записи из таблицы.

truncate table [[<имя БД>.] <владелец>.] <имя таблицы>

Пример: truncate table poss

Оператор UPDATE служит для внесения изменений в записи таблиц БД.

update [[<имя БД>.]<владелец>.] {<имя таблицы>|<имя представления>}

set [[[<имя БД>.]<владелец>.]{<имя таблицы>.|<имя представления>.}]

<имя кол.1>={<выр.1> | null | (<предложение select>)}

[,<имя кол.2>={<выр.2> | null | (<предложение select>)}]...

[from [[<имя БД>.] <владелец>.]{<имя таблицы>|<имя представления>}

[,[[<имя БД>.]<владелец>.]{<имя таблицы>|<имя представления>}]]...

[where <условие поиска>]

Пример: Студент Иванов Иван Петрович был переведен из МЭИ в МГУ:

update poss set vuz_k=2066426

where fio='Иванов Иван Петрович' and vuz_k=2066414

где 2066414 - код МЭИ,

2066426 - код МГУ.

5. ХРАНИМЫЕ ПРОЦЕДУРЫ

Общие понятия

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

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

1) иметь параметры;

2) вызывать другие процедуры;

1. возвращать статус-значение в вызывающую процедуру или пакет: успех или неудача (и причина неудачи);

2. возвращать значения параметров в вызывающую процедуру или пакет;

5) быть запущены или удалены с SQL-сервера.

Хранимые процедуры отличаются от просто SQL-предложений и от пакетов SQL - предложений тем, что они являются объектами БД и перед созданием они проходят прекомпиляцию. Создаются хранимые процедуры с помощью команды create procedure, а запускаются на выполнение командой execute.

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

1. если в пакете используется команда create procedure, то нельзя использовать никаких других команд в этом пакете;

2. в команде create procedure можно использовать любое количество SQL-предложений, за исключением команды use и команд create:

• create view;

• create default;

• create rule;

• create trigger;

• create procedure;

1. другие объекты БД могут быть созданы в процедуре;

2. нельзя в теле одной процедуры создать объект БД, уничтожить его, а затем создать новый объект БД с этим же именем;

3. SQL-сервер создает объекты, определенные в теле процедуры, во время выполнения этой процедуры, а не во время ее компиляции;

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

5. вы можете ссылаться на временные таблицы в теле процедуры;

6. временные таблицы, созданные процедурой, существуют в БД только во время выполнения этой процедуры;

7. максимальное количество параметров в хранимой процедуре - 255;

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

Синтаксис команды создания хранимой процедуры:

create procedure [<владелец>.] <имя процедуры> [; <номер>]

[[(] @ <имя параметра> <тип данных> [=<умолчание>][output]

[,@<имя параметра> <тип данных>[=<умолчание>][output]]...[)]]

[with recompile] as <SQL - предложения>

Синтаксис команды запуска хранимой процедуры: