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

[execute] [@return_status=]

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

[[@<имя параметра>=]<значение>|

[@<имя параметра>=] @<переменная>[output]

[,[@<имя параметра>=]<значение>|

[@<имя параметра>=]@<переменная>[output]...]]

[with recompile]

где номер - это номер процедуры в группе процедур, имеющей свое имя; например, имена процедур: orders;1, orders;2 и т. д. (orders - имя группы);

умолчание - это значение параметра по умолчанию;

output - обозначает, что параметр возвращает значение в вызываемую процедуру;

with recompile (в команде create procedure)- сообщает SQL-серверу не сохранять проект указанной процедуры; новый проект процедуры создается при каждом ее запуске на выполнение;

with recompile (в команде execute) - сообщает SQL-серверу выполнить компиляцию нового проекта процедуры;

return status - показывает либо успешное завершение процедуры, либо причины невыполнения (ошибки); это значение может сохраняться в переменной и затем использоваться операторами Transact-SQL; это значение может находиться в промежутке от 0 до -99 (0 - успешное завершение, остальные значения обозначают ошибки).

Три способа запуска хранимых процедур:

1) <имя процедуры>

2) execute <имя процедуры>

3) exec <имя процедуры>

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

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

[,[<владелец>.]<имя процедуры>]...

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

sp_rename <старое имя>, <новое имя>

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

1) выдать данные по учащимся России, принятых на учебу в 89 и 90 годах по специальности 0401 и отсортировать по годам приема и учебным заведениям:

create procedure zapr1 as select * from poss

where (gp='89' or gр='90') and spec='000401' order by gp, vuz_k

2) выдать фамилии, даты рождения, названия учебных заведений, пол, специальности, категории обучения, месяцы приема, года приема, месяцы окончания и года окончания по выпускникам России 1996 года; данные отсортировать по учебным заведениям и специальностям;

create procedure zapr2 as select fio, data_rogden,

vuz.uch_zavedenie, pol, spec, kat_obuch. kat_obucheniya,

mp, gp, mo, gok

from poss, vuz, kat_obuch

where gok='96' and vuz_k=vuz.cod and

kat_obuch_k=kat_obuch.cod

order by 3,5

3) выдать список студентов, обучаемых в городе Москве, принятых на учебу в 1989 г.; схема выдачи отчета должна включать поля: фамилия, дата рождения, учебное заведение, пол, специальность, категория обучения, месяц окончания, год окончания; данные отсортировать по учебным заведениям и специальностям.

create procedure zapr3 as select fio, data_rogden,

vuz.uch_zavedenie, pol, spec, kat_obuch.kat_obucheniya,

mo, gok from poss, vuz, kat_obuch

where vuz_k in (select cod_vuza from vuz_gorod

where cod_goroda='001’)

and kat_obuch_k between '03' and '04'

and gp='89' and vuz_k=vuz.cod

and kat_obuch_k=kat_obuch.cod

order by 3,5

4) выдать статистические данные по всем учебным заведениям России, где есть специальность 0401.

create procedure zapr4 as select vuz.uch_zavedenie,

count(*) from poss, vuz where spec='000401'

and vuz_k=vuz.cod group by vuz.uch_zavedenie

В хранимых процедурах можно использовать операторы языка программирования Transact-SQL.

1. Условный оператор: if ... else

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

if <логическое выражение> <оператор>

[ else [if <логическое выражение>] <оператор>]

где <логическое выражение> возвращает либо TRUE, либо FALSE и может включать имя поля, константу, комбинацию из имен полей и констант, соединенных арифметическими или поразрядными операциями, и подзапрос, возвращающий единственное значение (обычно используется exists или not exists).

В качестве оператора используется либо предложение языка SQL, либо оператор языка Transact-SQL.

Пример:

if exists(select postalcode from authors where postalcode = ‘94705’)

select «Berkeley author»

1. Составной оператор (блок): begin ... end

Если в качестве оператора нужно использовать несколько SQL-предложений или операторов, то нужно использовать составной оператор.

Синтаксис оператора: begin

< блок предложений>

end

Пример:

begin

update titles set price = price * 2

select title, price from titles where price > $28

end

1. Оператор цикла: while

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

while <логическое выражение> <оператор>

где <оператор> выполняется до тех пор, пока истинно <логическое выражение>. В качестве оператора может использоваться оператор языка Transact-SQL, SQL-предложение или составной оператор.

Пример:

while (select avg(price) from titles) < $30

begin

select title_id, price from titles where price > $20

update titles set price = price * 2

end

1. Оператор break ... continue

Операторы break и continue используются в теле цикла while. Оператор break выполняет немедленный выход из цикла и передает управление первому после цикла (обычно после end) оператору. Оператор continue продолжает выполнять цикл while, пропуская все операторы цикла while, следующие после continue. Очень часто операторы break и continue используются в операторе if ... else.

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

while <логическое выражение>

begin

<оператор 1>

break

<оператор 2>

continue

<оператор 3>

end

Пример:

while (select avg(price) from titles)> $20

begin

update titles set price = price/2

if (select avg(price) from titles) < $40 break

else if (select avg(price) from titles) <$20 continue

select «Average price still over $20»

end

select title_id, price from titles where price > $20

select «Not Too Expensive»

1. Оператор объявления переменных declare

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

declare @<имя переменной> <тип данных>

[, @<имя переменной> <тип данных>] ...

Имена локальных переменных начинаются с @. При объявлении переменной ее значение устанавливается NULL. Присвоение значения переменной осуществляется предложением select:

select @<имя переменной> = {<выражение> | (<select-предложение>)}

[, @<имя переменной> = {<выражение> | (<select-предложение>)} ...]

[<предложение from>] [<предложение where>] [<предложение group by>]

[<предложение having>] [<предложение order by>] [<предложение compute>]

Примеры:

declare @veryhigh money

select @veryhigh = max(price) from titles

Кроме локальных переменных существуют и глобальные переменные, поддерживаемые системой SYBASE. Имена глобальных переменных начинаются с двух знаков «@», например: @@error

1. Оператор перехода: goto

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

<имя метки>:

.

.

.

goto <имя метки>

где <имя метки> задается по обычным правилам задания имени идентификатора.

Пример:

declare @count smallint

select @count = 1

restart:

select «yes»

select @count = @count + 1

if @count <= 4 goto restart

1. Оператор return

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

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

return [<целочисленное выражение>]

Пример:

create procedure find @nm varchar(30) = null

as if @nm is null

begin

select «you must give a user name»

return

end

else

begin

select sysobjects.name from sysobjects, syslogins

where syslogins.name = @nm and sysobjects.uid = syslogins.suid

end

1. Оператор print

Оператор print используется для вывода сообщений пользователя и значений локальных переменных на экран пользователя. Длина сообщения не должна превышать 255 байт.

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

print {<строка символов> | @<имя переменной> | @@<имя переменной>}

[, <список аргументов>]

Примеры:

print «Berkeley author»

print @msg

1. Оператор raiserror

Оператор raiserror показывает на экране пользователя ошибку, определенную пользователем, или сообщение из локальной переменной, а также устанавливает системный флажок, т. е. записывает номер ошибки в глобальную переменную @@error.

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

raiserror <номер ошибки> [{<строка символов> | @<имя локальной переменной>}]

[, <список аргументов>] [<расширенное значение> = <расширенное значение>

[{, < расширенное значение> = <расширенное значение>} ...]]

Пример:

raiserror 99999 «you must give a user name»

1. Оператор waitfor

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

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

waitfor { delay «<время>» | time «<время>» | errorexit | processexit | mirrorexit}

delay сообщает SQL-серверу промежуток времени, в течение которого ему находиться в состоянии ожидания. time сообщает SQL-серверу то же самое время, только в формате данных типа datetime. Для <времени> используется формат «<часы>:<минуты>:<секунды>».

Примеры:

waitfor delay «01:30»

waitfor time «16:23»

errorexit сообщает SQL-серверу ждать до тех пор, пока процесс не закончится аварийно; processexit заставляет ожидать до тех пор, пока процесс не прервется по какой-либо причине; mirrorexit заставляет ожидать до тех пор, пока не прервется чтение или запись на диске дампирования.

1. Оператор комментариев

Синтаксис:

/* <текст комментария> */

Длина текста комментария не ограничена. Если комментарий занимает несколько строчек, то первая строка начинается с «/*», а последующие с «**», хотя каждую строку можно начинать с «/*», а заканчивать «*/».

Пример:

/* пример сложного запроса */

Использование курсоров

Курсор - это символическое имя, связанное с предложением select через декларативное предложение. Курсор состоит из двух частей: множество результатов курсора и позиция (текущий указатель) курсора. Курсоры используются в тех случаях, когда select - предложение возвращает множество (таблицу) записей, а пользователю необходимо манипулировать каждой записью в отдельности. Используя имя курсора можно выполнять операторы delete или update для каждой записи.

Процесс использования курсора включает в себя следующие шаги:

1) объявление курсора;

2) открытие курсора;

3) выборка одной записи с помощью курсора;

4) обновление или удаление записей с помощью курсора;

5) закрытие курсора;

6) освобождение курсора.

Объявление курсора

declare <имя курсора> cursor for <select_предложение>

[for {read only|update [of <список имен колонок>]}]

Открытие курсора

open <имя курсора>

Выборка одной записи с помощью курсора

fetch <имя курсора> [into <выходной список переменных>]

Переменные объявляются в начале процедуры с помощью оператора declare. Имя переменной начинается с @, например: @name, @city, @state.

Результатом работы оператора fetch является код возврата, имеющий одно из трех значений:

0 - fetch выполнен успешно;

1 - ошибочное выполнение fetch;

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

Обновление записей с помощью курсора:

1) - объявление курсора для обновления:

declare <имя курсора> cursor for <select - предложение>

for update [of <список имен колонок>]