Опубликован: 28.12.2011 | Доступ: свободный | Студентов: 6839 / 683 | Оценка: 3.81 / 3.53 | Длительность: 19:30:00
ISBN: 978-5-9963-0488-2
Лекция 3:

Создание, удаление и изменение структуры таблиц

< Лекция 2 || Лекция 3: 12345 || Лекция 4 >
Аннотация: Рассматриваются главные конструктивные операции с таблицами — основным средством моделирования данных в БД Oracle, — а также сопутствующие логические и технологические особенности этих операций.

Создание, удаление и изменение структуры таблиц

Таблицы представляют собой главный инструмент моделирования данных в БД, как построенных на основе SQL вообще, так и в Oracle в частности.

Предложение CREATE TABLE

Создание таблиц осуществляется предложением CREATE TABLE категории DDL.

Пример:

CREATE TABLE proj 
(
   projno NUMBER   ( 4 )
 , pname  VARCHAR2 ( 14 )
 , bdate  DATE
 , budget NUMBER   ( 10, 2 )
);

Помимо указанных в этом примере предложение CREATE TABLE может включать чрезвычайно много других конструкций, большая часть которых связана с организацией хранения, а не с логикой данных. Полностью они приводятся в документации Oracle по SQL.

Типы данных в столбцах

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

  • Числа:
    • типы NUMBER, NUMBER ( n ), NUMBER ( n, m ) (в частности, NUMBER ( *, m ))
    • типы FLOAT, REAL, NUMERIC, DECIMAL, INTEGER и другие совместимые с ANSI
    • типы BINARY_FLOAT(10.1-), BINARY_DOUBLE(10.1-)
  • Строки текста:
    • типы VARCHAR2 ( n ), CHAR ( n )
    • типы NVARCHAR2 ( n ), NCHAR ( n )(9.0-)
    • типы CLOB(8-), NCLOB(8-)
    • типы STRING, CHARACTER VARYING, NATIONAL CHARACTER VARYING и другие
  • Строки байтов:
    • RAW ( n )
    • BLOB(8-)
    • LONG, LONG RAW
  • Моменты времени:
    • DATE
    • TIMESTAMP(9.2-), TIMESTAMP ( n )(9.2-)
    • TIMESTAMP WITH TIME ZONE(9.2-), TIMESTAMP WITH LOCAL TIME ZONE(9.2-)
  • Интервалы времени:
    • INTERVAL YEAR TO MONTH(9.2-), INTERVAL DAY TO SECOND(9.2-)
  • Прочие типы данных:
    • BFILE(8-)
    • ROWID, UROWID (физический и "логический физический" адреса строк или объектов в таблицах)
    • XMLTYPE(9.2-), ANYDATA(9.2-), URITYPE с подтипами(9.2-); типы для Oracle Spatial(8-), для построения геоинформационных систем; прочие (встроенные объектные)

(8-) начиная с версии 8.

(9.0-) начиная с версии 9.0.

(9.2-) начиная с версии 9.2.

(10.1-) начиная с версии 10.1.

Помимо встроенных типов Oracle с версии 8 позволяет использовать в описании столбцов типы, создаваемые самостоятельно пользователями БД. Это объектные ("структурные") типы.

Типы BLOB, CLOB, NCLOB и BFILE иногда объединяют в единую категорию типов "больших неструктурированных объектов" (объекты LOB). Для работы с ними в SQL иногда требуется прибегать к функциям встроенного пакета DBMS_LOB, хотя с версии 9.2 поводов для этого стало меньше. Их употребление в SQL связано с определенными ограничениями. Например, столбцы этих типов не могут употребляться в формировании ключа таблицы и вообще индексироваться стандартным образом. Часть подобных ограничений употребления обязаны предположительно гигантским объемам значений, а часть — особенному способу хранения, отличному от принятого для "обычных" данных.

В коде СУБД Oracle сохранились попытки ввести в некоторых случаях более разумные встроенные типы, по ряду причин не доведенные до официального предоставления. Примером может служить тип TIME для обозначения времени суток. Разрешить его употребление в своем сеансе связи с СУБД можно следующей командой ( http://citforum.ru/database/oracle/time/):

 ALTER SESSION SET EVENTS '10407 trace name context forever, level 1';

Официально этого типа не существует, а следами его фактического наличия являются возможности указывать в обычных выражениях SQL значение времени суток, например, TIME '12:30:45', и некоторые функции, например, TO_TIME и EXTRACT.

Похожим путем в версии 8.1 можно активировать типы TIMESTAMP и INTERVAL (впоследствии ставшие штатными, в отличие от TIME):

ALTER SESSION SET EVENTS '10406 trace name context forever';
Числовые типы
Тип NUMBER
  • Исторически первым для Oracle числовым типом является NUMBER. Он существует в трех вариантах:
  • NUMBER — для хранения чисел "самого общего вида";
  • NUMBER (n) — для хранения целых с максимальной точностью мантиссы n десятичных позиций;
  • NUMBER (n, m) (в частности, NUMBER (*, m )) — для хранения чисел "с фиксированной десятичной точкой" с максимальной точностью мантиссы n десятичных позиций, из них m после десятичной точки.

Формат хранения во всех случаях одинаков:

  • 1-й байт — знак числа и степень 100 в двоичном виде;
  • остальные байты — двоично-десятичное представление цифр мантиссы, по две десятичные цифры на байт, максимум 38 десятичных цифр.

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

SQL> SELECT DUMP ( 1 ) FROM dual;
DUMP(1)
------------------
Typ=2 Len=2: 193,2
SQL> SELECT DUMP ( 1000 ) FROM dual;
DUMP(1000)
------------------- 
Typ=2 Len=2: 194,11
SQL> SELECT DUMP ( 1000 - 1 ) FROM dual;
DUMP(1000-1)
-----------------------
Typ=2 Len=3: 194,10,100

В выдачах Typ = 2 сообщает числовое внутреннее обозначение для типа NUMBER, Len сообщает занимаемую при хранении длину в байтах, а десятичные значения самих байтов перечисляются следом.

Подтипы NUMBER

Тип NUMBER в Oracle не входит в стандарт ANSI/ISO SQL. Следующие типы включены в диалект SQL Oracle для совместимости со стандартом и с решениями IBM:

Тип SQL Совместимость Соответствующий тип в Oracle
DEC (точность, масштаб) ANSI NUMBER (точность, масштаб)
DEC ANSI NUMBER (38, 0)
DECIMAL (точность, масштаб) IBM NUMBER (точность, масштаб)
DECIMAL IBM NUMBER (38, 0)
DOUBLE PRECISION ANSI NUMBER
FLOAT (двоичная точность, 1 .. 126) ANSI, IBM NUMBER
INT ANSI NUMBER (38, 0)
INTEGER ANSI, IBM NUMBER (38, 0)
NUMERIC (точность, масштаб) ANSI NUMBER (точность, масштаб)
REAL ANSI NUMBER
SMALLINT ANSI, IBM NUMBER (38, 0)

Содержательно эти типы в Oracle ничего не привносят и фактически являются подтипами типа NUMBER.

< Лекция 2 || Лекция 3: 12345 || Лекция 4 >
Ярослав Прозоров
Ярослав Прозоров

В лекции № 7 "Введение в Oracle SQL" в подразделе "Несамостоятельность группировки с обобщениями ROLLUP, CUBE и GROUPING SETS"  представленная таблица сравнения содержит ошибки - окончания запросов пропущены. Видимо, ошибки вызваны некорректным переносом материала лекции.

Володимир Миколайчук
Володимир Миколайчук
Помогите разобраться поетапно с логикой запроса
-------TOOLS
NAME PRICE TYPE
drill 155 A
sawzall 192 N
mitre saw 292 M
router 86 I
RAD 145 M
jigsaw 128 I
screwdriver 77 P
------TOOL_TYPES
TYPE USAGE
A Always
I Often
M Sometimes
N Rarely
P Never

Запрос SQL:
SELECT t.type, SUM(t.price)
FROM tools t
GROUP BY t.type
HAVING SUM(t.price) >= (SELECT AVG(price)
FROM tools
WHERE type IN (SELECT type
FROM tool_types
WHERE usage = 'Often'));

И сколько строк он все таки вернет
Алишер Сапиев
Алишер Сапиев
Казахстан, Алматы
Адиль Нурманов
Адиль Нурманов
Соединенные Штаты, Калифорния