Дополнительные возможности обновления данных

Начиная с версии 3.0 вводится дополнительная команда update or insert. Синтаксически она близка к команде insert.

update or insert into <object> [(<col_ list>)]

values (<val_list>)

[matching ([(<col_list>)]

 

<col_list> ::= <col>[,<col_list>]

<val_list> ::= <val>[,<val_list>]

 

<col> - задает имя столбца

<val> - задает значение, присваиваемое столбцу

 

Оператор update or insert либо обновляет одну или более существующих записей, либо вставляет одну или более новых записей.

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

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

4.4. Уда­ле­ние дан­ных. Ко­ман­да Delete

Ко­ман­да delete уда­ля­ет из таб­ли­цы или обзора (view) од­ну или не­сколь­ко строк в за­ви­си­мо­сти от за­да­вае­мых ус­ло­вий уда­ле­ния.

delete from <table> {[ where <search_condition>];

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

При­мер 4.4.1.

Удалим строки из таблицы по значениям первичного ключа.

delete from treader where unikey in (84,85,86);

Дополнительные возможности команд изменения данных

Помимо внесения изменений в командах insert, update, update or insert, delete предусмотрена также возможность возврата значений столбцов. Последнее актуально, когда эти значения заранее не известны, например, вычисляются в результате работы триггеров.

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

[returning <ret_list> [into <var_list>]]

 

<ret_list> ::= <ret_val> [[as] <ret_alias>] [, <ret_list>]

<ret_val> [[as] <ret_alias>] – задает возвращаемое значение и, при необходимости, его алиасное имя.

<var_list> ::= [:]<varnane>[,<var_list>]

 <varnane> – задает переменную, в которую заносится полученное значение.

Применение опции returning допускается только в случае, если соответствующая операция воздействует не более чем на одну строку таблицы.

При­мер 4.5.1.

Если триггер вставки для таблицы TA, содержащей поля KEYTA, field1, field2, field3, формирует значение поля KEYTA, являющегося первичным ключом в таблице TA, то в процедуре, содержащей переменную wKEYTA допустима команда

insert into TA(field1, field2, field3)

returning KEYTA into wKEYTA;

 

Работа с DML в Oracle

Синтаксис команд DML в Oracle в целом идентичен синтаксису InterBase.

В то же время имеется ряд отличий.

Рассмотрим некоторые наиболее существенные.

Команда select

1. Oracle не поддерживает конструкции skip, first rows, однако Oracle имеет встроенную переменную rownum, которая содержит порядковый номер выбираемой записи

При­мер 4.6.1.

Таким образом, запрос примера 4.1.5. в FireBird

 

select skip 3 first 2 a. Author, a. BookKey, b. booknm

from tbook_author a, tbook b

where a.BOOKKEY=b.unikey

 

 

в Oracle должен принять вид

 

select a. Author, a. BookKey, b. booknm

from tbook_author a, tbook b

where a.BOOKKEY=b.unikey

and rownum>3 and rownum<=5

2. Перечень и сигнатура встроенных функций отличаются. Кроме того, Oracle содержит ряд аналитических функций, отсутствующих в FireBird.

3. Oracle не имеет конструкции join в явном виде. Для реализации внешних соединений используется суффикс (+) в конструкции where при описании связей между источниками данных. Так пример 4.1.4. для Oracle нужно переписать в следующем виде

Таким образом, запрос примера 4 в FireBird

При­мер 4.6.2.

select a. Author, a. BookKey, b. booknm

from tbook_author a left join tbook on a.BOOKKEY=b.unikey

 

в Oracle должен принять вид

 

select a. Author, a. BookKey, b. booknm

from tbook_author a, tbook

where a.BOOKKEY(+) = b.unikey

 

4. Конструкции case в FireBird и Oracle имеют отличия.

В FireBird простой оператор case имеет вид

case <test-expr>

<when_exp>

[else <defaultresult>]

end

 

<when_exp> ::= when <expr> then <result> [<when_exp>]

Поисковый case

case

 <when_cond>

 [else <defaultresult>]

end

 

<when_cond> ::= when <condition> then <result> <when_cond>

 

В Oracle

case

 <when_cond>

 [else <defaultresult>]

end

 

<when_cond> ::= when <condition> then <result> <when_cond>

 

То есть, если конструкция поискового case в FireBird аналогична переключателю switch в C, то конструкция поискового case в FireBird и case в Oracle аналогична группе конструкций if-then-else.

В C поисковый case и case Oracle имел бы вид

if(<condition1>) result=<result1>;

else if(<condition1>) result=<result2>;

…

else result=< defaultresult>;

 

5. Источники данных в FireBird и Oracle имеют ряд отличий.

Объекты Oracle распределены по схемам, что позволяет держать несколько объектов с одинаковыми названиями, но разного назначения, создавая собственные пространства имен. Соответственно обеспечивается изоляция приложений. В одной и той же базе в зависимости от способа присоединения одно и то же приложение может работать с разными данными. FireBird (InterBase) такую возможность не поддерживает.

6. В качестве источника данных FireBird (InterBase) допускает использование процедуры выбора. Oracle не имеют объектов типа процедур выбора.

7. Oracle поддерживает команду truncate, обеспечивающую быструю очистку таблиц и кластеров (в определенном смысле групп таблиц).

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

Встроенные функции

Функции, как в любом языке программирования могут получать несколько (в том числе и 0) параметров и возвращают результат специфицированного типа. В отличие от ранних версий InterBase список функций, включенных в состав FireBird достаточно велик.

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

По характеру аргументов функции подразделяются на агрегатные и скалярные.

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

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

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

 

К агрегатным относятся следующие функции:

count (*|[all] <val> | distinct <val>) – количество строк;

sum ([all]<val>|distinct <val>) – сумма значений;

avg ([all] <val> | distinct <val>) – среднее значение;

max ([all] <val> | distinct <val>) – максимальное значение;

min ([all] <val> | distinct <val>) – минимальное значение.

 

К математическим функциям относятся следующие:

abs(<x>); atan(<x>);

acos(<x>); asin(<x>); ceil(<x>) или ceiling(<x>); cos(<x>); cosh(<x>); cot(<x>) (ctg x); exp(<x>); floor(<x>); ln(<x>); log(<x>); mod(<x>,<y>); pi(); power(<x>,<y>); rand(); round(<x>,<m>); sign(<x>); sin(<x>); sinh(<x>); tan(<x>); tanh(<x>); trunc(<x>,<m>)

Здесь <x>, <y> - числовые выражения; <m> - целые.

К строковым относятся следующие функции:

ascii_char (<code>) , ascii_val(<ch>); bit_length (<str>); char_length (<str>)

 или character_length (<str>); hash (<str>); left (<str>, <num>); lower (<str>); lpad(<str>, <endlen>[, <padstr>]); octet_length(<str>); overlay (<string> placing <replacement> from <pos> [for <length>]); position (<find> in <text>); replace(<str>, <find>, <repl>); reverse(<str>);

right (<str>, <num>); substring (<str> from <startpos> [for <length>]); trim ([[{both | leading | trailing | ''}] [<strwhat>] from] <strfrom>); upper(<str>)

<code> - целое в диапазоне 0 – 255;

 <ch> - отдельный символ или строка (в строке используется первый символ).

<str>, <string>, <replacement>, <padstr>, <find>, <text>, <repl>, <startpos>, <strwhat>, <strfrom> - строка символов.

<endlen>, <pos>, <length>, <num> - целые.

К функциям для работы с датой и временем относятся следующие:

dateadd (<par_list>)

(<par_list> ::= <nm> <unit> to <datetime>

<unit> ={year | month | week | day | weekday | yearday | hour | minute | second | millisecond}

<nm> – числовой параметр, <datetime> – параметр типов date, time или timestamp.

Возвращает результат в формате третьего параметра.

 

datediff (<par_list>)

<par_list> ::= <unit> from <datetime1> to <datetime2>

<unit> ={year | month | week | day | weekday | yearday | hour | minute | second | millisecond}

<datetime1>, <datetime2> – параметр типов date, time или timestamp

Возвращает результат в числовом формате.

 

extract (<unit> from <datetime>)

<unit>:: ={year | month | week | day | weekday | yearday | hour | minute | second | millisecond}

<datetime> – параметр типов date, time или timestamp

Возвращает результат в числовом формате.

 

К функциям преобразования типов относится функция cast.

cast(<val> as <datatype>)

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

 

К функциям для работы с UUID относятся char_to_uuid, gen_uuid, uuid_to_char

 

К функциям для работы с генераторами

(последовательностями) относится функция GEN_ID.

 

gen_id(<gen_name>, <step>)

 

<gen_name> – имя генератора (последовательности),

<step> – приращение генератора (обычно 1)

Возвращает накопленное значение (0, 1, 2 …), в результате обращения накопленное значение увеличивается на величину приращения (<step>).

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

 

К условным функциям относятся следующие:

 

decode(<testexpr>, <expr1>, <result1> [, <expr2>, <result2> ...] [, <defaultresult>])

 

decode эквивалентна конструкции case

 

case <testexpr>

 when <expr1> then <result1>

[when <expr2> then <result2>

…

else <defaultresult>]

end

Синтаксис конструкции case см. ниже.

 

iif(<condition>, <res_true>, <res_false>)

Возвращает <res_true>, если условие (<condition>) выполнено и <res_false> в противном случае.

Аналог оператора C

((condition) ? res_true : res_false;

 

maxvalue(<expr1> [, <exprn> ...])

Возвращает максимальное значение из <expr1>… <exprn>

 

minvalue(<expr1> [,<exprn> ...])

Возвращает минимальное значение из <expr1>… <exprn>

 

nullif(<expr1>, <exprn>)

Функция возвращает значение первого аргумента, если он неравен второму. В случае равенства аргументов возвращается NULL.

Чаше встречается обратная задача: заменить NULL на некоторое значение. Последнее реализуется конструкцией

iif(<expr1> is NULL, <expr1>, <expr2>)

Отметим, что в Oracle для этого предусмотрена функция nvl c синтаксисом

nvl(<expr1>, <expr2>)

Функция возвращает значение <expr1>, если оно отлично от NULL, иначе <expr2>.

 

К функциям побитовых операций относятся следующие:

 

bin_and (<number> [, <number> ...])

Функция bin_and возвращает результат побитовой операции and аргументов.

bin_not (<number>)

Функция bin_not возвращает результат побитовой операции not аргумента.

bin_or (<number> [,<number> ...])

Функция bin_or возвращает результат побитовой операции or аргументов.

bin_shl (<number>, <shift>)

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

bin_shr (<number>, <shift>)

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

bin_xor (<number> [,<number> ...])

Функция bin_xor возвращает результат побитовой операции xor аргументов

 

Гла­ва 5. Опи­са­ние дан­ных на ос­но­ве SQL

5.1. Ор­га­ни­за­ция дан­ных в InterBase. Ти­пы дан­ных

Дан­ные в ре­ля­ци­он­ных ба­зах дан­ных, в ча­ст­но­сти в InterBase, хра­нят­ся в пло­ских таб­ли­цах. В ка­ж­дом столб­це таб­ли­цы хра­нят­ся дан­ные од­но­го ти­па. При этом нуж­но пом­нить, что дан­ные, по ко­то­рым осу­ще­ст­в­ля­ет­ся по­иск, долж­ны быть про­сто­го (не­струк­ту­ри­ро­ван­но­го) ти­па, для ко­то­ро­го оп­ре­де­ле­ны опе­ра­ции срав­не­ния. Дан­ные дру­гих ти­пов до­пус­ка­ют­ся, ес­ли мож­но так ска­зать, "в по­ряд­ке ис­клю­че­ния" и для ра­бо­ты с ни­ми ис­поль­зу­ют­ся сред­ст­ва, вы­хо­дя­щие за пре­де­лы стан­дар­та SQL.

При­ве­дем пе­ре­чень используе­мых в InterBase ти­пов дан­ных.

smallint – сло­во, ко­рот­кое це­лое (2-бай­то­вое) со зна­ком.

integer – двой­ное сло­во, длин­ное це­лое (4-бай­то­вое).

bigint – (8-бай­то­вое) целое, доступен только в 3-м диалекте.

boolean - булевский однобайтовый, допустимые значения true, false, null; может непосредственно использоваться в логических выражениях (поддерживается, начиная с версии 3.0).

float – чис­ла с пла­ваю­щей точ­кой оди­нар­ной точ­но­сти (4 бай­та) – 7 зна­ча­щих цифр.

double precision – чис­ла с пла­ваю­щей точ­кой двой­ной точ­но­сти (8 бай­тов) – 15 зна­ча­щих цифр.

decimal (раз­мер, точ­ность) / (раз­мер, точ­ность). Раз­мер пе­ре­мен­ной (от 1 до 15) ука­зы­ва­ет га­ран­ти­ро­ван­ную точ­ность пе­ре­мен­ной, то есть чис­ло зна­ча­щих цифр. Точ­ность (от 1 до 15) за­да­ет чис­ло цифр по­сле за­пя­той (долж­но быть мень­ше или рав­но раз­ме­ру). На­при­мер, decimal (10,3) со­дер­жит чис­ла в фор­ма­те: ppppppp.sss

Ти­пы дан­ных decimal и numeric име­ют смысл толь­ко для внеш­не­го пред­став­ле­ния дан­ных. В ба­зе они ре­аль­но хра­нят­ся в од­ном из ос­нов­ных чи­сло­вых фор­ма­тов (smallint, integer, float или double precision). Конкретный формат выбирается исходя из размера, указанного в decimal (numeric).

date (8 байт) c 1.01.100 до 29.02.32768, вклю­ча­ет так­же дан­ные о вре­ме­ни в диалекте 1; в диалекте 3 – только дата.

time – время (только в диалекте 3)

timestamp – дата и время (только в диалекте 3) – аналог date в диалекте 1.

char (n) / character (n) n сим­во­лов (от 1 до 32 767 байт) – стро­ка фик­си­ро­ван­ной дли­ны; мак­си­маль­ная дли­на – 32К. 

character varying(n) / varchar(n) / char varying(n) n сим­во­лов (от 1 до 32 767 байт) – стро­ка пе­ре­мен­ной дли­ны; мак­си­маль­ная дли­на – 32К.

nchar(n) / national character(n) / national char(n) n сим­во­лов (от 1 до 32 767 байт) – стро­ка фик­си­ро­ван­ной дли­ны, ис­поль­зую­щая ко­до­вый на­бор ISO8859_1.

nchar varying(n) / national character varying(n) / national char varying(n) n сим­во­лов (от 1 до 32 767 байт) – стро­ка пе­ре­мен­ной дли­ны, ис­поль­зую­щая ко­до­вый на­бор ISO8859_1.

unicode – символьный формат; 1 символ занимает от 1 до 4 байт.

blob [sub_type {int | subtype_name}] [segment size int] [character set charname] / blob [(seglen [, subtype])].

sub_type содержит код типа хранимых в blob данных.

segment size – раз­мер бло­ка, че­рез ко­то­рый осу­ще­ст­в­ля­ет­ся чте­ние-за­пись дан­ных blob в при­ло­же­ни­ях, ис­поль­зую­щих embedded SQL.

Все пе­ре­чис­лен­ные ти­пы дан­ных, кро­ме blob, мо­гут быть ор­га­ни­зо­ва­ны в мас­си­вы. Мас­си­вы мо­гут со­дер­жать от 1 до 16 из­ме­ре­ний. При не­об­хо­ди­мо­сти раз­ме­ры мас­си­ва по ка­ж­до­му из из­ме­ре­ний ука­зы­ва­ют­ся в квад­рат­ных скоб­ках. На­при­мер, VesMes[12], Abc[10,4,5].

Гра­ни­цы по из­ме­ре­нию мо­гут быть ука­за­ны яв­но, на­при­мер VesT[5:8]. В этом слу­чае мас­сив бу­дет со­сто­ять из че­ты­рех эле­мен­тов: VesT[5], VesT[6], VesT[7], VesT[8].

За­ме­тим, что мас­сив не мо­жет быть эле­мен­том мас­си­ва. Ну­ме­ра­ция эле­мен­тов мас­си­ва на­чи­на­ет­ся с 1, ес­ли гра­ни­цы не бы­ли за­да­ны яв­но.

5.2. До­ме­ны

Столб­цы в раз­лич­ных таб­ли­цах ба­зы дан­ных мо­гут со­дер­жать од­но­тип­ные дан­ные. Кро­ме то­го, ви­ды кон­тро­ля этих дан­ных так­же мо­гут быть оди­на­ко­вы­ми. В этих ус­ло­ви­ях опи­са­ния дан­ных и ме­то­дов их кон­тро­ля це­ле­со­об­раз­но вы­пол­нить один раз, то­гда при опи­са­нии таб­лиц дос­та­точ­но ука­зы­вать толь­ко имя со­от­вет­ст­вую­ще­го опи­са­ния дан­ных. Для этих це­лей и слу­жат опи­са­ния до­ме­нов. (На­пом­ним, что под до­ме­на­ми от­но­ше­ния R, где R ÌA₁ ´ A₂ ´… ´A_n , по­ни­ма­ют­ся мно­же­ст­ва A_i i Î[1, n].)

Пе­ред тем как соз­да­вать столб­цы, ко­то­рые ссы­ла­ют­ся на до­ме­ны, не­об­хо­ди­мо за­дать опи­са­ния до­ме­нов. Для этих це­лей су­ще­ст­ву­ет ко­ман­да create domain. В ре­зуль­та­те ее вы­пол­не­ния соз­да­ет­ся шаб­лон, на ко­то­рый мож­но ссы­лать­ся в ко­ман­дах соз­да­ния и мо­ди­фи­ка­ции таб­лиц (create table и alter table).

Наи­бо­лее по­лез­но ис­поль­зо­ва­ние до­ме­нов, ко­гда боль­шое чис­ло таб­лиц со­дер­жит иден­тич­ные ти­пы дан­ных.

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

По­сколь­ку опи­са­ния до­ме­нов не свя­за­ны ни с ка­ки­ми таб­ли­ца­ми, то ог­ра­ни­че­ния ло­ги­че­ской це­ло­ст­но­сти таб­лиц, ес­те­ст­вен­но, не мо­гут фи­гу­ри­ро­вать в опи­са­нии до­ме­нов.

Рас­смот­рим син­так­сис опи­са­ния до­ме­нов.

Соз­да­ние до­ме­нов

create domain стро­ит опи­са­ние столб­ца, ко­то­рое ис­поль­зу­ет­ся как шаб­лон при опи­са­нии столб­цов таб­лиц в ко­ман­дах create table или alter table. Опи­са­ние до­ме­на со­дер­жит сле­дую­щий на­бор ха­рак­те­ри­стик: тип дан­ных и не­обя­за­тель­ные зна­че­ние по умол­ча­нию, за­прет зна­че­ний null, кон­троль­ное ог­ра­ни­че­ние (check), по­рядо­к срав­не­ния (понятие >, =, < для строк).

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

create domain <domain> [as] <datatype>

[default {<literal> | null | user | <con_var> }] [not null] [check (<dom_search_condition>)] [collate <collation>];

 

<domain> - уни­каль­ное имя соз­да­вае­мо­го до­ме­на

<datatype> ::= {

{ smallint | integer | bigint | float | double precision | boolean} [<array_dim>]

| {decimal | numeric } [(<precision> [, <scale>])] [<array_dim>]

| date [<array_dim>]

| {char | character | character varying | varchar | unicode}

[(1...32767)] [<array_dim>] [character set <charname>]

| {nchar | national character | national char} [varying] [(1...32767)] [<array_dim>]

| blob [sub_type {<num_int> | <subtype_name>}] [segment size <seg_size>]

[character set <charname>]

| blob [(<seglen> [,<subtype>])]

}

 

<precision> - количество десятичных знаков, занимаемое числом

<scale>> - количество десятичных знаков в дробной части числа

<array_dim> ::= [x:y [, x:y ...]] Здесь внешние квад­рат­ные скоб­ки яв­ля­ют­ся ча­стью син­так­си­са опи­са­ния мас­си­вов, а не эле­мен­том ме­та­язы­ка. Массивы, в общем случае, не являются стандартным типом SQL и здесь не рассматриваются. Доступ к ним возможен только внутри программных объектов (процедуры, функции, триггеры, блоки кода).

<charname> - задает набор символов по умолчанию

sub_type {<num_int> | <subtype_name>} – задает номер или мнемонику подтипа blob

segment size <seg_size> - задает размер сегмента blob (blob при хранении разбивается на сегменты), размер не более 65535 байт.

default - Ука­зы­ва­ет зна­че­ние, ко­то­рое бу­дет при­свое­но столб­цу, ес­ли не бы­ло пря­мо­го при­свое­ния. Зна­че­ния по умол­ча­нию мо­гут быть пе­ре­оп­ре­де­ле­ны при опи­са­нии столб­цов таблиц.

<literal> - константа соответствующего типа

<con_ var> ::= {current_connection | current_transaction | current_role | current_date | current_time | current_timestamp | 'now' | 'today' | 'yesterday' | 'tomorrow' | current_user | user}

not null - Ука­зы­ва­ет, что вво­ди­мые в стол­бец дан­ные не мо­гут при­ни­мать зна­че­ние null

check (<dom_search_cond>) - До­бав­ля­ет check -ог­ра­ни­че­ние в до­мен. До­мен может иметь толь­ко од­но check - ограничение

 

<dom_search_condition> ::= {
value <operator> <val> | value [not] between <val> and <val>
| value [not] like <val> [<val>] | value [not] in (<list_val><val> [, <val> ...])

| <val> [not] similar to <val> [escape <val>]

| <val> <operator> {all | some | any} (<select_list>)

| [not] exists (<select_expr>)

| [not] singular (<select_expr>)
| value is [not] null | value [not] containing <val>
| value[not] starting [with] <val> | (<dom_search_condition>)
| not <dom_search_condition>
| <dom_search_condition> or <dom_search_condition>
| <dom_search_condition> and <dom_search_condition>
}

value - За­ме­ни­тель име­ни столб­ца, основанного на до­ме­не

<operator> ::= {= | < | > | <= | >= | !< | !> | <> | !=}

 <list_val>> ::= <val> [, <list_val>]

<val> - константное выражение

 

collate <collation> - Оп­ре­де­ля­ет по­рядок срав­не­ния для до­ме­на.

 <select_list>, <select_expr> - задают выборку данных из базы.

Кон­ст­рук­ция collate не­при­ме­ни­ма для blob столб­цов.

 

При­мер 5.2.1.

create domain dmonth as smallint check (value between 1 and 12);

За­ме­ча­ние. Тип дан­ных не мо­жет быть пе­ре­оп­ре­де­лен при ис­поль­зо­ва­нии до­ме­на в опи­са­нии таб­лиц.

Из­ме­не­ние до­ме­нов

Из­ме­не­ние до­ме­нов осу­ще­ст­в­ля­ет­ся ко­ман­дой alter domain. С по­мо­щью ко­ман­ды alter domain мож­но из­ме­нить лю­бые ха­рак­те­ри­сти­ки до­ме­на, кро­ме ти­па дан­ных (вклю­чая раз­ме­ры мас­си­ва) и ус­та­но­вок not null. Сде­лан­ные из­ме­не­ния воз­дей­ст­ву­ют на по­ля всех таб­лиц, где ис­поль­зо­вал­ся из­ме­нен­ный до­мен.

alter domain <name> {[ set default {<literal> | null | user}]|[drop default] | [add [constraint] check ( <dom_search_condition>)] | [drop constraint]};

Уда­ле­ние до­ме­нов

Уда­ле­ние до­ме­нов осу­ще­ст­в­ля­ет­ся ко­ман­дой drop domain. С по­мо­щью этой ко­ман­ды мож­но уда­лить опи­са­ние до­ме­на.

Ес­ли до­мен ис­поль­зу­ет­ся в ка­ких-ли­бо таб­ли­цах, то уда­лить его нель­зя (со­от­вет­ст­вую­щая ко­ман­да за­вер­шит­ся ава­рий­но).

 

Пример 5.2.2.

drop domain dmonth;

5.3. Таб­ли­цы

Пе­ред тем как пе­рей­ти к соз­да­нию таб­лиц, не­об­хо­ди­мо вы­пол­нить про­ек­ти­ро­ва­ние ба­зы дан­ных и нор­ма­ли­за­цию таб­лиц.

Да­лее сле­ду­ет оп­ре­де­лить, ка­кие таб­ли­цы и с ка­ки­ми столб­ца­ми (по­ля­ми) под­ле­жат соз­да­нию.

Соз­дать, ес­ли это не бы­ло сде­ла­но ра­нее, са­му ба­зу и вы­пол­нить ко­ман­ду со­еди­не­ния с ба­зой (см. create database … и connect).

Соз­дать ком­плект не­об­хо­ди­мых до­ме­нов и толь­ко по­сле это­го мож­но пе­рей­ти к фи­зи­че­ско­му вво­ду опи­са­ний таб­лиц.

Соз­да­ние таб­ли­цы, точ­нее, ее опи­са­ния и «пус­то­граф­ки» осу­ще­ст­в­ля­ет­ся ко­ман­дой create table.

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

Соз­да­ние таб­лиц. Ко­ман­да CREATE TABLE

create table опи­сы­ва­ет соз­да­вае­мую таб­ли­цу, ее столб­цы, ог­ра­ни­че­ния ло­ги­че­ской це­ло­ст­но­сти в су­ще­ст­вую­щей ба­зе.

create [global temporary] table <table_name> [external [file] '<filespec>'] (<list_def>]) [on commit {delete | preserve} rows];

<table_name> - Имя таб­ли­цы. Имя долж­но быть уни­каль­ным внут­ри ба­зы в спи­ске имен таб­лиц и про­це­дур

<list_def>::={<col_def> | <tconstraint>}[,<list_def>]

<col_def> ::= <col> {datatype | {computed [by] | generated always as} <expr>) | <domain>} [default {<literal> | null | user}] [not null] [<col_constraint>] [collate <collation>]

<col> - Имя столб­ца. Имя долж­но быть уни­каль­ным внут­ри таб­ли­цы в спи­ске имен столб­цов

<datatype> - Тип дан­ных для столб­ца (см. create domain).

<expr> = лю­бое кор­рект­ное SQL вы­ра­же­ние, воз­вра­щаю­щее от­дель­ную ве­ли­чи­ну (blob и мас­сив не до­пус­ка­ют­ся).

computed [by] (<expr>) или generated always as (<expr>) - описывает вычисляемый столбец. Кон­ст­рук­ция <expr> за­да­ет по­ря­док вы­чис­ле­ния. <expr> мо­жет быть лю­бым до­пус­ти­мым в SQL вы­ра­же­ни­ем, воз­вра­щаю­щим един­ст­вен­ное зна­че­ние (мас­си­вы и blob не­до­пус­ти­мы). Все столб­цы, уча­ст­вую­щие в вы­чис­ле­нии, долж­ны су­ще­ст­во­вать на мо­мент вво­да вы­чис­ляе­мо­го столб­ца

 

<domain> - Имя до­ме­на, на ко­то­рый опи­ра­ет­ся опи­са­ние столб­ца

default, collate <collation> - имеют тот же смысл, что и при описании домена.

<col_constraint> ::= [constraint <constraint_name>] <constraint_def>

<constraint_name> - имя ограничения

<constraint_def> ::= {unique [<using_index>]| primary key [<using_index>]| check (<dom_search_cond>) | references <other_table> [(other_col)] [<using_index>][on delete { no action | cascade | set default | set null}] [on update {no action | cascade | set default | set null}]}

unique – задает уникальный индекс

primary key - задает первичный ключ

check – задает способ контроля

<tconstraint> ::= constraint <constraint_name> <tconstraint_def>

<tconstraint_def> = {{ primary key | unique } (<list_col>) [<using_index>] | foreign key (<list_col>) references <other_table> [<using_index>] | check (<search_condition>)}

<list_col> ::= <col> [,(<list_col>]

<search_condition> - см. select.

<constraint_name> - имя ограничения

<using_index> ::= using [asc[ending] | desc[ending]] index <indexname>

 

Необязательное предложение external [file] указывает, что таблица хранится вне базы данных во внешнем текстовом файле в жестком формате без разделителей. Для таких таблиц возможны только операции вставки и выборки. Их можно использовать для хранения данных типа журнала, независимого от основной базы данных.

Необязательное предложение global temporary указывает, что таблица не предназначена для постоянного хранения данных. Таблица предназначена для хранения промежуточных результатов. Время хранения задается предложением on commit {delete | preserve} rows, определяющим момент очистки таблицы. В режиме delete таблица «живет» одну транзакцию. Такая конструкция оправдана, прежде всего, когда в пределах процедуры вычисляются некоторые данные, обрабатываются и, наконец, записываются в постоянные таблицы. Режим preserve используется, когда в рамках одной транзакции данные формируются, а используются, возможно многократно, другими транзакциями. Время «жизни» такой таблицы определяется соединением. В обоих случаях это означает, что одновременно может существовать несколько локальных экземпляров таблиц, используемых различными приложениями. Режим по умолчанию - on commit delete rows, если нужен другой режим, его следует указывать явно.

Предложение <col> <datatype>необходимо для задания всех хранимых данных, именно на их основе определяется объем и структура требуемой памяти.

Конструкция computed [by] или generated always as указывает, что описываемое поле не хранится в базе, а задается лишь алгоритм для его вычисления. Если в операторе SQL, работающем с таблицей данное поле не используется, то не включаются и средства для его вычисления, таким образом, наличие вычисляемых полей не замедляет доступ к данным.

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

Ограничения на отдельный столбец типа primary, unique, foreign key указывают, что данное поле служит первичным, уникальным, внешним ключом.

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

Ограничения на таблицу типа primary, unique, foreign key позволяют указать набор полей, образующих первичный, уникальный, внешний ключ

<using_index> - задает необходимость формирования индекса и задает его режимы: возрастание (по умолчанию) или убывание.

Ограничения на таблицу типа check, позволяет задать условие проверки, зависящее от нескольких столбцов.

При­мер 5.3.1.

Соз­да­ет­ся таб­ли­ца с пер­вич­ным, 2 внеш­ними клю­ча­ми (на уровне поля и на уровне таблицы) и 2 вычисляемыми полями.

create table tbook_author1 (
author integer not null constraint fk_author references tauthor(author),
bookkey integer not null,
cauthor computed by ((select a.auname from tauthor a
    where a.author=tbook_author1.author)),
cbooknm computed by ((select a.booknm from tbook a
   where a.unikey=tbook_author1.bookkey)),
constraint pk_ba primary key (author,bookkey),
constraint fk_book foreign key (bookkey) references tbook(unikey));

Мо­ди­фи­ка­ция таб­лиц. Ко­ман­да ALTER TABLE

Ко­ман­да alter table пред­на­зна­че­на для из­ме­не­ния струк­ту­ры уже су­ще­ст­вую­щих таб­лиц.

Она по­зво­ля­ет:

• до­ба­вить в таб­ли­цу но­вый стол­бец;

• уда­лить из таб­ли­цы су­ще­ст­вую­щий стол­бец;

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

• уда­лить из таб­ли­цы ог­ра­ни­че­ние на стол­бец или таб­ли­цу.

В од­ной ко­ман­де alter table мож­но за­дать лю­бое чис­ло из­ме­не­ний.

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

alter table <table_name> <list_oper>

<list_oper> ::= <operation> [,<list_oper>];

<operation> ::= {add {<col_def> | <tconstraint>} | drop {<col>  | constraint  <constraint>}

Конструкции <col_def>, <tconstraint>, <col>, <constraint> в команде alter table полностью соответствуют аналогичным эле­мен­там опи­са­ния столб­цов или ог­ра­ни­че­ний при соз­да­нии таб­лиц (create table). В ре­зуль­та­те ее вы­пол­не­ния соз­да­ет­ся или удаляется со­от­вет­ст­вую­щий эле­мент опи­са­ния таб­ли­цы.

Уда­ле­ние таб­лиц. Ко­ман­да DROP TABLE

Ко­ман­да dpop table пред­на­зна­че­на для уда­ле­ния таб­лиц.

drop table <table_name>;

< table_name> – имя уда­ляе­мой таб­ли­цы.

В ре­зуль­та­те вы­пол­не­ния ко­ман­ды drop table уда­ля­ет­ся со­дер­жи­мое таб­ли­цы, ее опи­са­ние, все свя­зан­ные с таб­ли­цей ин­дек­сы и триг­ге­ры.

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

5.4. Ин­дек­сы

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

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

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

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