Информационное обеспечение
жүктеу/скачать 256,49 Kb.
|
| Числовые типы представлены типом 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'; жүктеу/скачать 256,49 Kb. Достарыңызбен бөлісу:
|