Числовые типы представлены типом NUMBER, который позволяет опре- делить три различных типа данных:
NUMBER;
NUMBER(p);
NUMBER(p, s).
В первом случае определяются действительные числа, диапазон которых от 1,0·10-130 до 1,0·101261, мантисса содержит 38 знаков. Во втором случае счи- тается, что определяется диапазон целых чисел, где p – количество цифр в чис- ле (от 1 до 38). В третьем случае описываются числа с фиксированной точкой; p – общее количество цифр (от 1 до 38), s – масштаб (от 84 до 127) – определя- ет количество цифр после запятой. Если s > 0, то число округляется до указан- ного числа знаков справа от десятичной точки, если s < 0, то число округляется до указанного числа знаков слева от десятичной точки.
Следует отметить, что язык SQL поддерживает типы данных стандарта ANSI. Если такой тип данных (INTEGER, SMALLINT, DECIMAL, FLOAT, REAL и т. д.) встречается при определении типа столбца таблицы, то имя типа сохраняется, но сами данные хранятся в виде, определяемом одним из типов базы данных Oracle.
Тип данных дата/время. Тип DATE представляет собой специально орга- низованный тип, который хранит столетие, год, месяц, день, часы, минуты, се- кунды в одном поле. Для выборки текущей даты используется функция SYSDATE, например:
SELECT SYSDATE “Текущее время” FROM dual;
Чтобы увидеть другие составляющие поля или изменить формат вывода, применяется встроенная функция TO_CHAR и шаблон, например:
SELECT TO_CHAR(SYSDATE,'DD.MM.YY HH24:MI:SS') "Время и дата"
FROM dual;
Над переменными типа DATE можно выполнить арифметическое дей- ствие – вычитание. Результат операции определяет количество дней между этими двумя датами. К дате можно прибавить или отнять от нее числовую кон- станту, рассматриваемую как количество дней или часть дня.
В прил. 1 (табл. П.1.1–П.1.3) приведены основные встроенные функции Oracle для работы с символьными, числовыми полями и полями типа дата/время. Двоичные типы данных используются для хранения двоичных неструкту- рированных данных (звуковые файлы, файлы изображений и т. д.), обработка ко- торых не поддерживается Oracle. К ним относятся типы RAW(длина) и LONGRAW. Максимальный размер строки типа RAW 2000 байт, минималь-
ный – 1 байт. Длина переменных типа LONGRAW может достигать 2 ГБ.
К большим объектам (LOB-объектам) относятся CLOB, BLOB и BFILE. Они предназначены для хранения неструктурированных данных большого
объема – до 4 ГБ. Тип CLOB хранит данные символьного типа, типы BLOB и BFILE используются для хранения двоичных данных. Столбцы типа LOB со- держат не сами данные, а указатели на их местоположение.
Создание таблиц. Создание таблицы выполняется с помощью DDL-инструкции CREATE TABLE, упрощенный синтаксис которой имеет сле- дующий вид:
CREATE TABLE имя_таблицы
{({ имя_столбца тип_данных [DEFAULT значение] [ограничения_столбца] | ограничение_таблицы}
[,{ имя_столбца тип_данных [DEFAULT значение] [ограничения_столбца] | ограничение_таблицы}]...) | AS подзапрос};
Таблица может быть создана либо стандартным образом через описание ее компонент, либо в результате выполнения некоторого подзапроса. Подзапрос – это обычный запрос на выборку информации, реализуемый оператором SELECT. При создании таблицы задаваемые имена таблиц и имена столбцов должны удовлетворять правилам, предписываемым идентификаторам. При этом имена, присваиваемые таблицам, должны быть уникальными в схеме пользователя, а имена столбцов должны быть уникальными в рамках одной таблицы. Для каждого столбца указываются тип данных и, если необходимо, значение, вставляемое в столбец по умолчанию (DEFAULT значение). Важным элементом при создании таблиц является задание ограничений целостности данных, которые позволяют отслеживать правильность модификации имею- щихся данных или вставляемых в таблицу новых данных. Ограничения целост- ности данных делятся на два типа: ограничения столбца и ограничения табли- цы. Ограничения столбца позволяют определить условия, которым должны удовлетворять значения соответствующего столбца; ограничения целостности данных, накладываемые на таблицу, позволяют проверить правильность всех добавляемых или модифицируемых строк таблицы. Ограничение может быть именованным или безымянным. Удобнее использовать именованные ограниче- ния, поскольку при выдаче информации, связанной с возникшим нарушением одного из ограничений, выдается и имя этого ограничения, что очень удобно для исправления ошибок в дальнейшем. Задание ограничений на столбец или таблицу осуществляется по следующему синтаксису:
[CONSTRAINT имя_ограничения] тип_ограничения
Существуют следующие типы ограничений, накладываемых на столбец:
Первичный ключ (PRIMARY KEY) – это ограничение требует, чтобы вводимые в столбец значения были уникальными и отличными от пустых (NULL), поскольку они будут использоваться в качестве первичного ключа, од-
нозначно идентифицирующего запись; первичный ключ определяется для таб- лицы только единожды и поддерживает ее целостность.
Уникальность (UNIQUE) – это ограничение требует, чтобы вводимые в столбец значения в рамках одной таблицы были уникальными.
Обязательное наличие данных (NOT NULL) – это ограничение требует обязательного присутствия в столбце некоторого значения, т. е. не допускаются значения NULL при заполнении таблицы. Обязательное наличие данных в столбце определяется разработчиком на стадии проектирования таблицы и за- висит только от его смысловой нагрузки в данной таблице.
Условие на значение CHECK(выражение) – это ограничение позволяет подвергнуть определенной проверке вставляемое в столбец значение; если условия, наложенные на вставляемые значения, не выполняются, то значения в столбец не помещаются.
Рассмотрим пример, в котором для поля номера сотрудника sno создается ограничение с именем s1, которое позволяет указывать в качестве номера толь- ко числа от 101 до 199:
CREATE TABLE staff (sno INTEGER not null,
age INTEGER CHECK (age >=21),
CONSTRAINT s1 CHECK (sno BETWEEN 101 AND 199));
Внешний ключ таблицы (FOREIGN KEY) позволяет установить взаи- мосвязь значений указанного столбца со значениями столбца другой таблицы при помощи ключевого слова REFERENCES. Таблица, на чей столбец ссылает- ся другая таблица, называется главной или родительской, а таблица, ссылаю- щаяся на нее, – подчиненной или дочерней. Взаимосвязь обеспечивается ис- пользованием следующей конструкции:
REFERENCES имя_таблицы-родителя[(имя_столбца)] [ON DELETE CASCADE | ON DELETE SET NULL]
При внесении значения в столбец создаваемой таблицы система будет ав- томатически проверять наличие аналогичного значения в указанном столбце таблицы-родителя. При этом для обеспечения однозначности устанавливаемой взаимосвязи все значения, находящиеся в столбце, на которые производится ссылка, должны иметь ограничение UNIQUE или PRIMARY KEY. Если в каче- стве имени столбца родительской таблицы используется первичный ключ, то имя столбца можно не указывать. Конструкция ON DELETE CASCADE указы- вает, что при удалении строк в главной таблице автоматически осуществляется удаление соответствующих строк и в подчиненной таблице; ON DELETE SET NULL указывает, что при удалении родительских строк на месте их значений в дочерней таблице будет установлено значение NULL.
Ограничения на таблицу во многом напоминают ограничения столбца, но при этом задействуют, как правило, несколько столбцов. Также ограничение
столбца помещается в конец описания определения данного столбца после типа данных. Ограничение таблицы помещается в конец описания таблицы после последнего имени столбца, но перед заключительной круглой скобкой. Напри- мер, можно задать ограничение PRIMARY KEY на уровне таблицы, указав спи- сок имен столбцов, тем самым определив составной первичный ключ.
Можно определить составной внешний ключ для таблицы. В случае со- ставного внешнего ключа перечень столбцов в подчиненной таблице и пере- чень столбцов в главной таблице должны совпадать по количеству, типу дан- ных и порядку следования. Например:
FOREIGN KEY (список_имен_столбцов) REFERENCES имя_таблицы(список_имен_столбцов) [ON DELETE CASCADE]
Если ограничение CHECK затрагивает значения нескольких столбцов, увязывая их в некоторое достаточно сложное условие, то такое ограничение также удобно определить как ограничение на таблицу [2].
Рассмотрим код создания таблицы заказов. На рис. 1 приведена реляционная модель БД, включающая родительские таблицы customers (продавцы), salesreps (покупатели) и product (продукция), а также дочернюю таблицу orders (заказы).
Таблица customers
Таблица salesreps Таблица products
Рис. 1. Реляционная модель БД
Инструкция CREATE TABLE создаст таблицу orders при условии, что родительские таблицы, с которыми она связана к моменту ее создания, уже су- ществуют. Если таблиц, на которые ссылается данная таблица, нет, то связи с ней могут быть созданы позже при помощи инструкции ALTER TABLE.
CREATE TABLE orders (ord_n INTEGER NOT NULL,
ord_date DATE NOT NULL,
cust_n VARCHAR2(5) NOT NULL, sale_n VARCHAR2(5) NOT NULL, prod_n VARCHAR2(10) NOT NULL, qty INTEGER,
amount NUMBER (6,2), PRIMARY KEY (ord_n),
CONSTRAINT placed_by FOREIGN KEY (cust_n) REFERENCES customers, CONSTRAINT taken_by FOREIGN KEY (sale_n) REFERENCES salesreps, CONSTRAINT is_for FOREIGN KEY (prod_n) REFERENCES products);
Для изменения структуры таблицы используется оператор ALTER TABLE, с помощью которого можно осуществить добавление столбцов, огра- ничений столбцов или таблицы (предложение ADD), изменение определения столбцов (предложение MODIFY), удаление столбцов, ограничений столбца или таблицы (предложение DROP), переименование столбцов (предложение RENAME). Рассмотрим несколько примеров.
Добавить новый столбец в таблицу orders:
ALTER TABLE orders ADD new_column VARCHAR2(10);
Изменить новый столбец в таблице orders, увеличив его размер до 30 байт: ALTER TABLE orders MODIFY new_column VARCHAR2(30);
Изменять размер пустых столбцов можно как в большую, так и в мень- шую сторону, однако при наличии в таблицах уже введенных строк изменять размер поля можно только в сторону увеличения.
Удалить столбец из таблицы orders можно следующим образом: ALTER TABLE orders DROP COLUMN old_column;
Чтобы переименовать столбец в таблице orders, используется запись: ALTER TABLE orders RENAME COLUMN old_column TO new_column;
Удаление таблицы можно выполнить с помощью инструкции: DROP TABLE имя_таблицы [CASCADE CONSTRAINTS];
При наличии конструкции CASCADE CONSTRAINTS вместе с удалени- ем таблицы уничтожаются ограничения внешнего ключа в других таблицах.
Вставка строк в таблицу осуществляется с помощью DML-инструкции INSERT, которая имеет следующий синтаксис:
INSERT INTO имя_таблицы [(список_столбцов)]
{VALUES (значение1 [, значение2] ...) | подзапрос};
Если список столбцов не указывается, то список значений в предложении VALUES должен содержать значения для всех столбцов таблицы, причем поря- док их следования должен однозначно соответствовать порядку их следования в строке. Кроме этого, должно быть соответствие между типами данных столб- цов и значениями, передаваемыми в предложении VALUES, в частности, сим- вольные литералы и литералы типа дата/время заключаются в одинарные ка- вычки. Если формат вводимых данных отличается от текущего в сессии, то при вводе используют встроенную функцию TO_DATE для приведения данных к нужному виду. Например, TO_DATE ('12/12/2012', 'DD.MM.YYYY').
Также инструкция INSERT INTO может использоваться с подзапросом, что позволяет перенести строки из некоторой существующей таблицы в созда- ваемую таблицу:
INSERT INTO имя_таблицы [(список_столбцов)] SELECT запрос;
Кроме того, следует помнить, что INSERT INTO, как любая DML – ин- струкция в SQL*Plus выполняется только в оперативной памяти текущего сеан- са, т. е. в БД не отразятся все изменения, пока не будет явного подтверждения выполнения транзакции командой COMMIT.
Удаление строк из таблицы осуществляется с помощью оператора DELETE, который имеет следующий синтаксис:
DELETE [FROM] имя_таблицы [WHERE условие];
При отсутствии ключевого слова WHERE из таблицы удаляются все строки, но сама таблица остается.
Создание индексов. Индекс – это средство, обеспечивающее быстрый до- ступ к строкам таблицы на основе значений одного или нескольких столбцов. СУБД пользуется индексом так же, как читатели пользуются предметным ука- зателем книги. В индексе хранятся значения данных и указатели на строки, где эти данные встречаются. Данные в индексе располагаются в отсортированном по убыванию или возрастанию порядке, чтобы СУБД могла быстро найти тре- буемое значение. Затем по указателю СУБД может быстро локализовать стро- ку, содержащую искомое значение.
Наличие или отсутствие индекса совершенно незаметно для пользовате- ля, обращающегося к таблице.
В Oracle можно создать, изменить или удалить индекс для одного или не- скольких столбцов таблицы, используя следующий синтаксис:
CREATE [OR REPLACE] [UNIQUE | BITMAP] INDEX [схема.] имя_индекса ON [схема.] имя_таблицы [псевдоним] (столбец | выражение_для_столбца [ASC | DESC][, …]);
где UNIQUE означает, что значения столбцов, на которые ссылается индекс, должны быть уникальными;
BITMAP – изменение структуры индекса со сбалансированного дерева на структуру растровой карты.
Создание псевдонимов. Псевдоним – это назначаемое пользователем имя, которое заменяет полное имя некоторой таблицы с целью его упрощения.
В Oracle для создания псевдонимов используется инструкция CREATE SYNONYM.
CREATE [PUBLIC] SYNONYM имя_синонима
FOR [схема.] имя_таблицы[@связь_БД]
После создания псевдонима его можно использовать в запросах SQL как обычное имя таблицы. Применение псевдонимов смысл запроса не изменяет, так как и в этом случае необходимо иметь разрешение на доступ к таблицам других пользователей. Тем не менее псевдонимы упрощают инструкции SQL, и последние приобретают такой вид, как если бы вы обращались к своим соб- ственным таблицам. Псевдоним можно удалить посредством инструкции DROP SYNONYM.
Создание последовательности. Использование последовательностей по- лезно для автоматической генерации уникальных первичных ключей для дан- ных, а также для координирования ключей между различными строками или таблицами.
Без генератора последовательностей порядковые номера можно создавать лишь программным способом.
Общий синтаксис создания:
CREATE SEQUENCE [schema].sequence_name [INCREMENT BY increment_num]
[START WITH start_num]
[MAXVALUE maximum_num | NOMAXVALUE] [MINVALUE minimum_num | NOMINVALUE] [CYCLE | NOCYCLE]
[CACHE cache_num | NOCACHE] [ORDER | NOORDER];
где sequence_name – имя последовательности;
increment_num – шаг последовательности, по умолчанию это 1; абсолютное значение этого параметра должно быть меньше, чем разница между конечным и начальным значениями;
start_num – целочисленное значение, с которого начинается отсчет, по умолчанию это 1;
maximum_num – максимальное значение последовательности; значение maximum_num должно быть больше или равно значению start_num, и больше, чем значение minimum_num;
NOMAXVALUE – устанавливает максимальное значение равным 1027 для возрастающей последовательности или –1 для убывающей, используется по умолчанию;
minimum_num – минимальное значение последовательности; должно быть меньше либо равно start_num и меньше, чем maximum_num;
NOMINVALUE – определяет минимальное значение, равное 1 для воз- растающей последовательности, и –1026 для убывающей, используется по умолчанию;
CYCLE – подразумевает, что последовательность начинает генерировать значения по кругу при достижении максимального или минимального значения. При обращении к последовательности, когда она достигла максимального значе- ния, следующее сгенерированное значение будет минимальным значением по- следовательности. В ситуации с убывающей последовательностью при достиже- нии минимального значения следующее сгенерированное будет максимальным;
NOCYCLE – указывает прекратить генерацию значений при достижении максимума или минимума последовательности, используется по умолчанию;
cache_num – количество значений, сохраняемых в памяти, по умолчанию это 20. Минимальное количество кэшированных значений – 2, максимальное значение высчитывается по формуле CEIL (maximum_num -minimum_num)
/ABS(increment_num);
NOCACHE – отключает кэширование. Это не позволит базе данных вы- делить некоторое количество значений заблаговременно, что даст возможность избежать пробелов в последовательности, но увеличит затраты системных ре- сурсов. Пробелы могут возникнуть при остановке базы данных, когда кэширо- ванные значения теряются. Если опции CACHE и NOCACHE не указываются, то по умолчанию кэшируется 20 значений;
ORDER – обеспечивает генерацию значений в порядке запросов, как пра- вило, используется в среде Real Application Clusters;
NOORDER – не дает гарантий генерации значений в порядке запросов, используется по умолчанию.
Для работы с последовательностями генерируемых значений, применяе- мых в качестве уникальных ключей, используются псевдостолбцы:
имя_последовательности.CURRVAL – возвращает текущее значение из указанной последовательности генерируемых значений;
имя_последовательности.NEXTVAL – возвращает следующее значение из указанной последовательности генерируемых значений.
Значения CURRVAL и NEXTVAL используются в следующих местах:
в списке SELECT предложения SELECT;
стах:
в фразе VALUES предложения INSERT;
в фразе SET предложения UPDATE.
Нельзя использовать значения CURRVAL и NEXTVAL в следующих ме-
в подзапросе;
в предложении SELECT с оператором DISTINCT;
в предложении SELECT с фразой GROUP BY или ORDER BY;
в предложении SELECT, объединенном с другим предложением
SELECT оператором множеств UNION;
в фразе WHERE предложения SELECT;
в умалчиваемом (DEFAULT) значении столбца в предложении CREATE TABLE или ALTER TABLE;
в условии ограничения CHECK.
Работа со словарем данных. Словарь данных – это набор служебных таб- лиц Oracle, который создается при генерации базы данных, он обновляется и обслуживается сервером. Как правило, в словаре содержится следующая ин- формация: имена пользователей сервера Oracle; уровни привилегий пользова- телей; имена объектов базы данных; табличные ограничения целостности; учетные данные; параметры размещения объектов в физической памяти.
Получить информацию об объектах базы данных можно с помощью од- ного из следующих представлений:
DICT[IONARY];
USER_TABLES;
USER_OBJECTS;
USER_CONSTRAINTS;
USER_CONS_COLUMNS.
Кроме этого, существует аналогичный набор представлений с префикса- ми ALL_ и DBA_.
Вывод списка всех представлений словаря данных, доступных пользова- телю, осуществляется командой
SELECT *FROM DICTIONARY;
Вывод структуры представления, например, USER_OBJECTS, в SQL*Plus выполняется командой
DESCRIBE user_objects
Вывод имен всех таблиц пользователя возможен командой
SELECT object_name FROM user_objects WHERE object_type ='TABLE’;
Просмотр определений и имен всех ограничений осуществляется из таб- лицы USER_CONSTRAINTS, например, проверка ограничений для таблицы EMP выглядит следующим образом:
SELECT constraint_name, constraint_type, search_condition, r_constraint_name
FROM user_constraints WHERE table_name = 'EMPLOYEES';
Особенно полезен для ограничений, использующих системные имена, просмотр столбцов, на которые наложены ограничения, с помощью представ- ления USER_CONS_COLUMNS:
SELECT constraint_name, column_name FROM user_cons_columns WHERE table_name = 'EMPLOYEES';
Достарыңызбен бөлісу: |