Create table oracle типы данных
Number Functions (Math Functions)
Dropping Partitions
Значения по умолчанию
При создании таблицы можно указать, какое значение будет принимать колонка по умолчанию:
В этом примере создается таблица cars, в которой помимо модели и страны-производителя хранится еще и количество колес, которое имеет автомобиль. И поле wheel_count по-умолчанию будет принимать значение, равное 4.
Что значит по-умолчанию? Это значит, что если при вставке данных в эту таблицу не указать значение для колонки wheel_count , то оно будет равно числу 4.
Representing Date and Time Data
Use the DATE datatype to store point-in-time values (dates and times) in a table. The DATE datatype stores the century, year, month, day, hours, minutes, and seconds.
Use the TIMESTAMP datatype to store precise values, down to fractional seconds. For example, an application that must decide which of two events occurred first might use TIMESTAMP. An application that needs to specify the time for a job to execute might use DATE.
Date Format
For input and output of dates, the standard Oracle default date format is DD-MON-RR. For example:
To change this default date format on an instance-wide basis, use the NLS_DATE_FORMAT parameter. To change the format during a session, use the ALTER SESSION statement. To enter dates that are not in the current default date format, use the TO_DATE function with a format mask. For example:
TO_DATE ('November 13, 1992', 'MONTH DD, YYYY')
Be careful using a date format like DD-MON-YY. The YY indicates the year in the current century. For example, 31-DEC-92 is December 31, 2092, not 1992 as you might expect. If you want to indicate years in any century other than the current one, use a different format mask, such as the default RR.
Time Format
Time is stored in 24-hour format, HH24:MI:SS. By default, the time in a date field is 12:00:00 A.M. (midnight) if no time portion is entered. In a time-only entry, the date portion defaults to the first day of the current month. To enter the time portion of a date, use the TO_DATE function with a format mask indicating the time portion, as in:
INSERT INTO Birthdays_tab (bname, bday) VALUES
('ANNIE',TO_DATE('13-NOV-92 10:56 A.M.','DD-MON-YY HH:MI A.M.'));
Once you have designed the table and decided about datatypes use the following SQL command to create a table.
For example, the following statement creates a table named Emp.
CREATE TABLE Emp (
Empno NUMBER(5),
Ename VARCHAR2(15),
Hiredate DATE,
Sal NUMBER(7,2)
);
To insert rows in the table you can use SQL INSERT command.
For example the following statement creates a row in the above table.
SQL> insert into emp values (101,’Sami’,3400);
To insert rows continuously in SQL Plus you can give the following command.
SQL> insert into emp values (&empno,’&name’,&sal);
These &Empno, &name and &sal are known as substitution variables. That is SQLPlus will prompt you for these values and then rewrites the statement with supplied values.
To see the rows you have inserted give the following command.
SQL> Select * from emp;
To see the structure of the table i.e. column names and their datatypes and widths. Give the following command.
При объявлении переменной или константы необходимо задать ее тип данных (PL/SQL, за некоторыми исключениями, относится к языкам со статической типизацией — см. ниже врезку). В PL/SQL определен широкий набор скалярных и составных типов данных; кроме того, вы можете создавать пользовательские типы данных. Многие типы данных PL/SQL (например, BOOLEAN и NATURAL ) не поддерживаются столбцами баз данных, но в коде PL/SQL эти типы весьма полезны.
Практически все заранее определенные типы данных определяются в пакете PL/SQL STANDARD . Например, определение типа данных BOOLEAN и двух числовых типов данных может выглядеть так:
PL/SQL поддерживает распространенный «джентльменский набор» типов данных, а так-же ряд других типов. В этом разделе приводится краткий обзор различных предопределенных типов данных.
ЧТО ТАКОЕ «СТАТИЧЕСКАЯ ТИПИЗАЦИЯ »?
Статической (или сильной) типизацией называется проверка типов во время компиляции (а не на стадии выполнения программы). К числу языков программирования, использующих сильную типизацию, относятся PL/SQL, Ada, C и Pascal. Языки с динамической типизацией (такие, как JavaScript, Perl или Ruby) выполняют большинство проверок типов во время выполнения. Статическая типизация позволяет выявлять ошибки во время компиляции, что повышает надежность программ. Преимуществом статической типизации является и ускорение выполнения программ. Оптимизирующий компилятор, который знает точные типы данных, может подбирать более эффективные ассемблерные решения и генерировать высокооптимизированный машинный код. Динамическая типизация тоже обладает своими преимуществами: например, метаклассы и интроспекция проще реализуются на базе динамической типизации.
GROUP BY Queries, SUB Queries
UNION, INTERSECT, MINUS Operators and Sorting Query Result
Oracle DECODE function
Oracle INSTR() function with examples
Oracle TO_CHAR() function
Oracle Join Queries
Создание таблицы с несколькими полями
В таблице может много столбцов. Например, можно создать таблицу с тремя, пятью или даже 100 колонками. В версиях oracle с 8i по 11g максимальное количество колонок в одной таблице достигает 1000.
Для того, чтобы создать таблицу с несколькими колонками, нужно перечислить все колонки через запятую.
Например, создадим таблицу cars , в которой будем хранить марку автомобиля и страну-производитель:
Эта таблица может содержать, например, такие данные:
Следует обратить внимание на последние 2 строки в таблице cars - они не полные. Первая из них содержит данные только в колонке model , вторая - не содержит данных ни в одной из колонок. Эта таблица может даже состоять из миллиона строк, подобных последней - и каждая строка не будет содержать в себе абсолютно никаких данных.
Типы данных ROWID и UROWID
Oracle поддерживает два типа данных ROWID и UROWID , предназначенных для предоставления адреса строки в таблице. Тип ROWID представляет уникальный физический адрес строки в таблице, а тип UROWID — логическую позицию строки в индексной таблице (Index-Organized Table, IOT). Тип ROWID также является псевдостолбцом SQL, который может включаться в инструкции SQL.
Working with Dates
Oracle DML (INSERT, UPDATE, DELETE. )
Oracle Views
Differing Constraints Check
Dropping Constraints
Oracle DDL (CREATE, ALTER, DROP. )
COMMIT, ROLLBACK,SAVEPOINT
Понятие NULL. Not-null колонки
Ячейки в таблицах могут быть пустыми, т.е. не содержать значения. Для обозначения отсутствия значения в ячейке используется ключевое слово NULL . Null могут содержать ячейки с любым типом данных.
Рассмотрим таблицу cars из предыдущего примера. В каждой из трех ее колонок может храниться Null(даже в колонке wheel_count , если указать значение Null явно при вставке).
Но представляют ли информационную ценность строки в таблице, где абсолютно нет значений? Конечно нет. Если рассматривать таблицу cars как источник информации об автомобилях, то нам хотелось бы получать хоть какую-то полезную информацию. Наиболее важной здесь будет колонка model
- без нее информация о стране-производителе и количестве колес будет бесполезной.
Для того, чтобы запретить Null-значения в колонке при создании таблицы, к описанию колонки добавляется not null :
Теперь БД гарантирует, что колонка model не будет пустой, по крайней мере до тех пор, пока флаг not null включен для этой колонки.
Также можно указать, что колонка wheel_count тоже не должна содержать Null :
Oracle Synonyms
Altering Partition Tables
Дата, время и интервалы
До появления версии Oracle9i представление дат в Oracle ограничивалось типом DATE , в котором хранится дата и время (с округлением до ближайшей секунды). В Oracle9i были введены два новых типа данных, INTERVAL и TIMESTAMP , значительно расширившие возможности разработчиков в отношении операций с датами и временем в PL/ SQL, а также вычисления и хранения временных интервалов. Их использование продемонстрировано в функции, вычисляющей возраст человека в интервальном формате с точностью до месяца:
DEFAULT Values
View Info about Constraints
Merging Partitions
Символьные типы данных
PL/SQL поддерживает строки фиксированной и переменной длины, состоящие как из традиционных символов, так и из символов Юникода. К строкам первого типа относятся строки CHAR и NCHAR , а к строкам второго вида — VARCHAR2 и NVARCHAR2 . Объявление строки переменной длины, которая может содержать до 2000 символов, выглядит так:
Для очень длинных символьных строк в PL/SQL предусмотрены типы данных CLOB (Character Large Object) и NCLOB (NLS Character Large Object). По соображениям совместимости PL/SQL также поддерживает тип данных LONG. Эти типы данных позволяют сохранять и обрабатывать очень большие объемы данных; так, в Oracle11g тип LOB способен хранить до 128 терабайт информации.
Использование типа LONG ограничивается множеством правил. Мы не рекомендуем применять его в Oracle8 и последующих версиях.
Создание таблицы
После выполнения данной sql-команды в базе данных будет создана таблица под названием hello . Эта таблица будет содержать всего одну колонку под названием text_to_hello . В этой колонке мы можем хранить только строковые значения(т.е. любой текст, который можно ввести с клавиатуры) длинной до 100 байт.
Обратите внимание на размер допустимого текста в колонке text_to_hello . 100 байт - это не одно и то же, что и 100 символов! Для того, чтобы сказать базе данных Oracle, что длина строки может быть 100 символов, нужно было определить столбец следующим образом:
Объектные типы данных в Oracle
Помимо сравнительно простых встроенных типов данных — как перешедших из стандартов SQL , так и собственных, — в Oracle имеется возможность использовать составные. Это конструируемые типы объектов, рассчитанные на хранение в БД данных, имеющих внутреннюю структуру. Эта структура известна СУБД , и СУБД позволяет с ней работать. Объектные типы позволяют хранить и обрабатывать средствами СУБД "сложно устроенные данные" более продвинутым образом, нежели это позволяет техника "больших неструктурированных объектов" типов LOB . Ввиду наличия вполне определенного типа (даже если это тип коллекции ), единичное объектное значение можно полагать за скаляр, хотя оно и не будет атомарным.
Хранение в столбцах таблицы значений в виде объектов, в смысле объектного подхода (ОП в программировании и моделировании), фирма Oracle впервые обеспечила в рамках так называемой "объектно-реляционной модели" начиная с версии Oracle 8. Некоторые существенные пробелы первой реализации (например, отсутствие наследования типов ) были устранены в версии 9. Примеры ниже не выходят за рамки возможностей версии 9.2, позже которой, впрочем, никаких существенных нововведений по объектной части не наблюдалось. Объектные возможности Oracle в общем следуют определениям SQL :1999, однако делают это непунктуально.
Программируемые типы данных и объекты в БД
Простой пример
Ниже приводится простой пример использования программируемых (объектных) типов.
Вначале требуется создать "тип", как разновидности хранимых элементов БД. Пример создания типа объекта (в SQL*Plus):
Здесь типу ADDRESS_TYPE приписаны два "свойства" (по объектной терминологии): ZIP и LOCATION . В реальной жизни для представления адреса в типе наверняка будет указано большее количество свойств, однако в ознакомительном примере их более пространный перечень излишен и не добавит понимания техники.
Определение типа напоминает определение таблицы, однако в отличие от таблицы (а также стандарта SQL и от реляционного подхода) тип объекта в Oracle не имеет права содержать ограничений целостности (которые в таком случае можно было бы назвать "ограничениями целостности типа"). Если необходимо их указать, сделать это придется только по месту употребления типа, то есть в описании таблицы.
В соответствии с традициями объектного подхода (уместно вспомнить, что "объектной теории", в отличие от реляционной, не создано) Oracle разрешает использовать тип для создания "буквальных значений" и собственно объектов. Далее приводится сначала несколько примеров первого, а затем второго.
"Буквальные значения" фактически позволяют работать со значениями, обладающими известной СУБД структурой и однозначно определяются набором значений элементов своей структуры.
Примеры использования типа ADDRESS_TYPE для определения столбца в обычной таблице:
Столбцы ADDR и HOME можно с некоторой вольностью назвать "объектными атрибутами". Они не позволяют хранить объектные значения в виде самостоятельной сущности и ссылаться на них ссылками. Локализовать такие значения можно только по обычным правилам поиска данных в таблице.
В выражениях явно указанные объектные значения формулируются с помощью конструктора. В отличие от других объектных систем, например, от Java, в Oracle конструктор умолчательно имеет список параметров, соответствующих свойствам типа. Примеры применения в операциях добавления данных:
Oracle допускает определенные синтаксические вольности в записи выражения над объектными данными. Здесь и далее используются частные случаи возможных формулировок.
Пример применения в запросе о сотрудниках, "работающих по месту жительства", за исключением конкретно указанного адреса:
Пример показывает легкость формулирования сравнения составных величин, каковыми являются адреса. Сравнение осуществляется поэлементно, путем сравнением всех свойств по очереди. Увы, но простота формулировки не дает права программисту расслабляться и забывать об особых случаях сравнения с данными типа CHAR и с NULL . Так, присутствие NULL в буквальных объектных значениях запутывает проблему сравнения еще больше, чем для случая простых типов. Сравните:
То есть получается, что x = x не дает TRUE , но притом x IS NOT NULL дает TRUE ( x имеет значение).
В выражениях можно обращаться к буквальному объектному значению как к целому, а можно и к его отдельным свойствам. Во втором случае, как правило, требуется прибегать к псевдониму:
Таблицы объектов
Созданный в БД тип можно употребить и для создания "таблиц объектов":
Хотя для этой категории хранимых элементов используется термин "таблица", такая таблица всегда содержит ровно один столбец, и именно объектного типа.
Запись занесения "строк" в такую таблицу может быть, в частности, такой:
SELECT a.*, UPPER ( location ) FROM addresses1 a;
Объекты в таких таблицах хранятся как самостоятельные сущности, у которых имеется автоматически порождаемый СУБД внутренний уникальный идентификатор object ID, в соответствии с классическим объектным подходом позволяющий ссылаться на конкретные объекты из других таблиц или из программы. Сравнение элементов-"строк" в таблице объектов друг с другом происходит уже не по значениям свойств, как в случае объектного столбца в обычной таблице, а по значению object ID. Перейти на сравнение значений свойств позволяет функция VALUE, например:
Сделан запрос об отделах, расположенных по адресам из таблицы ADDRESS1 .
Не исключено, что создатели функции VALUE обсуждали другое ее название — LITERAL_VALUE . По крайней мере, оно точнее описывает совершаемое действие: создание значения со структурой из объекта. Буквальные значения сравниваются друг с другом по значениям их свойств, а объекты — по значениям object ID.
Данные в реляционных базах данных хранятся в таблицах. Таблицы - это ключевой объект, с которыми придется работать в SQL.
Таблицы в БД совсем не отличаются от тех таблиц, с которыми все уже знакомы со школы - они состоят из колонок и строк.
Каждая колонка в таблице имеет своё имя и свой тип, т.е. тип данных, которые будут в ней содержаться. Помимо типа данных для колонки можно указать максимальный размер данных, которые могут содержаться в этой таблице.
Например, мы можем указать, что для колонки возраст тип данных - это целое число, и это число должно состоять максимум из 3-х цифр. Т.о. максимальное число, которое может содержаться в этой колонке = 999. А с помощью дополнительных конструкций можно задать и правила проверки корректности для значения в колонке,- например, мы можем указать, что для колонки возраст в таблице минимальное значение = 18.
Двоичные данные
Oracle поддерживает несколько разновидностей двоичных данных — неструктурированных данных, не интерпретируемых и не обрабатываемых Oracle. К их числу относятся типы RAW , BLOB и BFILE . Тип BFILE используется для хранения неструктурированных двоичных данных в файлах операционной системы вне базы данных. Тип RAW имеет переменную длину, а при операциях с ним Oracle не выполняет преобразование символов при передаче данных. В остальном он аналогичен символьному типу VARCHAR2 .
Тип данных LONG RAW поддерживается для обеспечения обратной совместимости, но в PL/SQL поддержка данных LONG RAW ограничена. В базе данных Oracle столбец LONG RAW занимает до 2 Гбайт, но PL/SQL позволяет работать только с первыми 32 760 байтами LONG RAW . Если, например, вы попытаетесь выполнить выборку в переменную PL/SQL из столбца LONG RAW , превышающего лимит в 32 760 байт, произойдет ошибка:
Для работы с данными LONG RAW , превышающими лимит PL/SQL, потребуется программа OCI; кстати, это веская причина для перевода старого кода с LONG RAW на данные BLOB , не имеющие такого ограничения.
Miscellaneous Functions
Data Control Language (GRANT, REVOKE)
Oracle Sequences
Пользовательские типы данных
Из встроенных типов данных Oracle и пользовательских типов можно строить типы данных произвольной сложности, которые с большой точностью отражают структуру и поведение данных в конкретных системах. В базе данных Oracle они получили еще название абстрактных типов данных.
Аннотация: Рассматриваются главные конструктивные операции с таблицами — основным средством моделирования данных в БД Oracle, — а также сопутствующие логические и технологические особенности этих операций.
Character Functions
Типы данных для поддержки интернет-технологий
В Oracle9i появилась встроенная поддержка ряда технологий и типов данных, связанных с Интернетом, в частности XML (eXtensible Markup Language) и URI (Universal Resource Identifier). В Oracle имеются специализированные типы для работы с данными XML и URI, а также специальный класс идентификаторов URI ( DBUri-REF ), который используется для доступа к базе данных. Кроме того, в Oracle появился новый набор типов данных, обеспечивающий хранение внешних и внутренних URI и обращение к ним из базы данных.
Тип XMLType предназначен для запроса и сохранения данных в формате XML. Для работы с XML используются такие функции, как SYS_XMLGEN из пакета DBMS_XMLGEN . Поддержка XPath и встроенные команды языка SQL позволяют выполнять поиск данных в документах XML.
CUBE, ROLLUP Functions
Комментарии к таблице, колонкам
Для создаваемых таблиц и их колонок можно указывать комментарии. Это значитально облегчит понимание того, для чего и как они используются.
Например, укажем комментарии для таблицы cars и ее колонок:
Для того, чтобы удалить комментарий, нужно просто задать в качестве его значения пустую строку:
Representing Character Data
Use the character datatypes to store alphanumeric data:
CHAR and NCHAR datatypes store fixed-length character strings.
VARCHAR2 and NVARCHAR2 datatypes store variable-length character strings. (The VARCHAR datatype is synonymous with the VARCHAR2 datatype.)
NCHAR and NVARCHAR2 datatypes store Unicode character data only.
CLOB and NCLOB datatypes store single-byte and multibyte character strings of up to four gigabytes.
The LONG datatype stores variable-length character strings containing up to two gigabytes, but with many restrictions.
This datatype is provided for backward compatibility with existing applications; in general, new applications should use CLOB and NCLOB datatypes to store large amounts of character data, and BLOB and BFILE to store large amounts of binary data.
When deciding which datatype to use for a column that will store alphanumeric data in a table, consider the following points of distinction:
To store data more efficiently, use the VARCHAR2 datatype. The CHAR datatype blank-pads and stores trailing blanks up to a fixed column length for all column values, while the VARCHAR2 datatype does not add any extra blanks.
For example if you define empname as char(20) then if you store names like “Sami” then name will occupy 20 bytes( 4 bytes for characters “Sami” and 16 blank spaces)
And if you define empname as varchar2(20) then if you store names like “Sami” then oracle will take 4 bytes only.
Use the CHAR datatype when you require ANSI compatibility in comparison semantics (when trailing blanks are not important in string comparisons). Use the VARCHAR2 when trailing blanks are important in string comparisons.
The CHAR and VARCHAR2 datatypes are and will always be fully supported. At this time, the VARCHAR datatype automatically corresponds to the VARCHAR2 datatype and is reserved for future use.
Coalescing Partitions
Создание, удаление и изменение структуры таблиц
Таблицы представляют собой главный инструмент моделирования данных в БД , как построенных на основе SQL вообще, так и в Oracle в частности.
Предложение CREATE TABLE
Создание таблиц осуществляется предложением CREATE TABLE категории DDL.
Помимо указанных в этом примере предложение 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-)
- 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-)
(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 связано с определенными ограничениями. Например, столбцы этих типов не могут употребляться в формировании ключа таблицы и вообще индексироваться стандартным образом. Часть подобных ограничений употребления обязаны предположительно гигантским объемам значений, а часть — особенному способу хранения, отличному от принятого для "обычных" данных.
Официально этого типа не существует, а следами его фактического наличия являются возможности указывать в обычных выражениях SQL значение времени суток, например, TIME '12:30:45' , и некоторые функции, например, TO_TIME и EXTRACT .
Похожим путем в версии 8.1 можно активировать типы TIMESTAMP и INTERVAL (впоследствии ставшие штатными, в отличие от TIME ):
Числовые типы
Тип NUMBER
- Исторически первым для Oracle числовым типом является NUMBER . Он существует в трех вариантах:
- NUMBER — для хранения чисел "самого общего вида";
- NUMBER (n) — для хранения целых с максимальной точностью мантиссы n десятичных позиций;
- NUMBER (n, m) (в частности, NUMBER (*, m ) ) — для хранения чисел "с фиксированной десятичной точкой" с максимальной точностью мантиссы n десятичных позиций, из них m после десятичной точки.
Формат хранения во всех случаях одинаков:
- 1-й байт — знак числа и степень 100 в двоичном виде;
- остальные байты — двоично-десятичное представление цифр мантиссы, по две десятичные цифры на байт, максимум 38 десятичных цифр.
Таким образом, все варианты типа NUMBER преобразуются к соответствующей им форме при помещении в базу, а при выборке из базы интерпретируются в соответствии с типом столбца. Проверить фактический вид хранения позволяет служебная функция DUMP :
В выдачах Typ = 2 сообщает числовое внутреннее обозначение для типа NUMBER, Len сообщает занимаемую при хранении длину в байтах, а десятичные значения самих байтов перечисляются следом.
Подтипы NUMBER
Тип NUMBER в Oracle не входит в стандарт ANSI/ISO SQL. Следующие типы включены в диалект SQL Oracle для совместимости со стандартом и с решениями IBM:
Содержательно эти типы в Oracle ничего не привносят и фактически являются подтипами типа NUMBER .
Аннотация: Рассматривается самостоятельное создание и использование объектных данных в Oracle. Приводятся примеры встроенных объектных данных, полезных прикладному программисту.
Datatypes
A datatype associates a fixed set of properties with the values that can be used in a column of a table or in an argument of a procedure or function. These properties cause Oracle to treat values of one datatype differently from values of another datatype. For example, Oracle can add values of NUMBER datatype, but not values of RAW datatype.
Oracle supplies the following built-in datatypes:
Character datatypes
- CHAR
- NCHAR
- VARCHAR2 and VARCHAR
- NVARCHAR2
- CLOB
- NCLOB
- LONG
Numeric datatypes
Time and date datatypes:
- DATE
- INTERVAL DAY TO SECOND
- INTERVAL YEAR TO MONTH
- TIMESTAMP
- TIMESTAMP WITH TIME ZONE
- TIMESTAMP WITH LOCAL TIME ZONE
Binary datatypes
Another datatype, ROWID, is used for values in the ROWID pseudocolumn, which represents the unique address of each row in a table.
The following table summarizes the information about each Oracle built-in datatype.
Column Length and Default
CHAR (size [BYTE | CHAR])
Fixed-length character data of length size bytes or characters.
Fixed for every row in the table (with trailing blanks); maximum size is 2000 bytes per row, default size is 1 byte per row. Consider the character set (single-byte or multibyte) before setting size.
VARCHAR2 (size [BYTE | CHAR])
Variable-length character data, with maximum length size bytes or characters.
Variable for each row, up to 4000 bytes per row. Consider the character set (single-byte or multibyte) before setting size. A maximum size must be specified.
Fixed-length Unicode character data of length size characters.
Fixed for every row in the table (with trailing blanks). Column size is the number of characters. (The number of bytes is 2 times this number for the AL16UTF16 encoding and 3 times this number for the UTF8 encoding.) The upper limit is 2000 bytes per row. Default is 1 character.
Variable-length Unicode character data of length size characters. A maximum size must be specified.
Variable for each row. Column size is the number of characters. (The number of bytes may be up to 2 times this number for a the AL16UTF16 encoding and 3 times this number for the UTF8 encoding.) The upper limit is 4000 bytes per row. Default is 1 character.
Single-byte character data
Up to 232 - 1 bytes, or 4 gigabytes.
Unicode national character set (NCHAR) data.
Up to 232 - 1 bytes, or 4 gigabytes.
Variable-length character data.
Variable for each row in the table, up to 232 - 1 bytes, or 2 gigabytes, per row. Provided for backward compatibility.
Variable-length numeric data. Maximum precision p and/or scale s is 38.
Variable for each row. The maximum space required for a given column is 21 bytes per row.
Fixed-length date and time data, ranging from Jan. 1, 4712 B.C.E. to Dec. 31, 4712 C.E.
Fixed at 7 bytes for each row in the table. Default format is a string (such as DD-MON-RR) specified by the NLS_DATE_FORMAT parameter.
INTERVAL YEAR (precision) TO MONTH
A period of time, represented as years and months. The precision value specifies the number of digits in the YEAR field of the date. The precision can be from 0 to 9, and defaults to 2 for years.
Fixed at 5 bytes.
INTERVAL DAY (precision) TO SECOND (precision)
A period of time, represented as days, hours, minutes, and seconds. The precision values specify the number of digits in the DAY and the fractional SECOND fields of the date. The precision can be from 0 to 9, and defaults to 2 for days and 6 for seconds.
Fixed at 11 bytes.
A value representing a date and time, including fractional seconds. (The exact resolution depends on the operating system clock.)
The precision value specifies the number of digits in the fractional second part of the SECOND date field. The precision can be from 0 to 9, and defaults to 6
Varies from 7 to 11 bytes, depending on the precision. The default is determined by the NLS_TIMESTAMP_FORMAT initialization parameter.
TIMESTAMP (precision) WITH TIME ZONE
A value representing a date and time, plus an associated time zone setting. The time zone can be an offset from UTC, such as '-5:0', or a region name, such as 'US/Pacific'.
Fixed at 13 bytes. The default is determined by the NLS_TIMESTAMP_TZ_FORMAT initialization parameter.
TIMESTAMP (precision) WITH LOCAL TIME ZONE
Similar to TIMESTAMP WITH TIME ZONE, except that the data is normalized to the database time zone when stored, and adjusted to match the client's time zone when retrieved.
Varies from 7 to 11 bytes, depending on the precision. The default is determined by the NLS_TIMESTAMP_FORMAT initialization parameter.
Unstructured binary data
Up to 232 - 1 bytes, or 4 gigabytes.
Binary data stored in an external file
Up to 232 - 1 bytes, or 4 gigabytes.
Variable-length raw binary data
Variable for each row in the table, up to 2000 bytes per row. A maximum size must be specified. Provided for backward compatibility.
Variable-length raw binary data
Variable for each row in the table, up to 231 - 1 bytes, or 2 gigabytes, per row. Provided for backward compatibility.
Binary data representing row addresses
Fixed at 10 bytes (extended ROWID) or 6 bytes (restricted ROWID) for each row in the table.
Aggregate Functions
Designing Tables
Consider the following guidelines when designing your tables:
- Use descriptive names for tables, columns, indexes, and clusters.
- Table Names, Columns Names can contain maximum of 30 characters and they should start with an alphabet.
- Be consistent in abbreviations and in the use of singular and plural forms of table names and columns.
- Select the appropriate datatype for each column.
- Arrange columns that can contain NULL Values in the last, to save storage space.
Before creating a table, you should also determine whether to use integrity constraints. Integrity constraints can be defined on the columns of a table to enforce the business rules of your database automatically.
Before creating a Table you also have to decide what type of data each column can contain. This is known as datatype. Lets Discuss what datatypes are available in Oracle.
Oracle Objects and Object Types
A table is the data structure that holds data in a relational database. A table is composed of rows and columns.
A table in Oracle Ver. 7.3 can have maximum 255 Columns and in Oracle Ver. 8 and above a table can have maximum 1000 columns. Number of rows in a table is unlimited in all the versions.
A table can represent a single entity that you want to track within your system. This type of a table could represent a list of the employees within your organization, or the orders placed for your company's products.
A table can also represent a relationship between two entities. This type of a table could portray the association between employees and their job skills, or the relationship of products to orders. Within the tables, foreign keys are used to represent relationships.
Although some well designed tables could represent both an entity and describe the relationship between that entity and another entity, most tables should represent either an entity or a relationship.
The following sessions explain how to create, alter, and drop tables. Some simple guidelines to follow when managing tables in your database are included.
Disabling and Enabling
Тип данных REF CURSOR
Тип данных REF CURSOR позволяет объявлять курсорные переменные, которые могут использоваться со статическими и динамическими SQL-командами для улучшения гибкости программного кода. Тип REF CURSOR существует в двух формах: сильной и слабой. PL/SQL относится к категории языков со статической типизацией, а слабый тип REF CURSOR является одной из немногочисленных конструкций с динамической типизацией.
В следующем примере объявления сильной формы REF CURSOR курсорная переменная связывается с конкретной структурой записи с помощью атрибута %ROWTYPE :
Далее следуют два слабых объявления REF CURSOR , в которых переменная не связывается ни с какой конкретной структурой. Во втором объявлении (строка 4) представлен тип SYS_REFCURSOR — заранее определенный слабый тип REF CURSOR .
Splitting Partitions
Типы данных «Any»
Большая часть программного кода предназначается для решения узкоспециализированных задач. Однако иногда нам приходится писать более универсальные программы. Именно для таких ситуаций предназначены типы данных Any .
Эта группа типов данных, появившаяся в Oracle9i, заметно отличается от любых других типов данных Oracle. Типы данных Any позволяют динамически инкапсулировать описания типов, экземпляры данных и наборы экземпляров данных любого другого типа SQL. С помощью этих объектных типов (и определенных для них методов) можно, к примеру, определить тип данных, хранимых во вложенной таблице, не обращаясь к объявлению типа этой таблицы!
В группу типов данных Any входят AnyType , AnyData и AnyDataSet .
Числовые типы данных
В PL/SQL поддерживаются как вещественные, так и целочисленные типы данных. Тип NUMBER давно был основным типом для работы с числовыми данными; он может применяться для работы с целыми и вещественными данными как с фиксированной, так и с плавающей запятой. Пример типичного объявления NUMBER :
Из-за своей внутренней десятичной природы тип NUMBER особенно удобен при работе с денежными суммами. В отличие от двоичного представления, ему не присущи ошибки округления. Например, если сохранить в нем значение 0.95, позднее вы прочитаете именно эту величину, а не приближенную (скажем, 0.949999968).
До выхода Oracle10g тип NUMBER был единственным числовым типом данных PL/SQL, напрямую соответствовавшим типу столбцов базы данных. В этом нетрудно убедиться, изучив содержимое пакета STANDARD . Данная особенность стала одной из причин, по которым тип NUMBER так широко применялся в программах PL/SQL.
В Oracle10g появились два двоичных типа с плавающей запятой: BINARY_FLOAT и BINARY_DOUBLE . Как и NUMBER , эти двоичные типы данных поддерживаются и в PL/SQL, и в базах данных. Однако в отличие от NUMBER , эти типы хранят значение в двоичном виде, а следовательно, при работе с ними могут возникнуть погрешности округления. Типы BINARY_FLOAT и BINARY_DOUBLE поддерживают специальные значения NaN (Not a Number, «не является числом»), положительную и отрицательную бесконечность. В некоторых типах приложений эти типы обеспечивают огромный выигрыш в быстродействии, так как вычисления с этими двоичными типами по возможности выполняются на аппаратном уровне.
В Oracle11g появились еще две разновидности вещественных типов. Типы SIMPLE_FLOAT и SIMPLE_DOUBLE являются аналогами BINARY_FLOAT и BINARY_DOUBLE , но они не поддерживают NULL и не инициируют исключение в случае переполнения.
PL/SQL поддерживает несколько числовых типов и подтипов, не имеющих прямого соответствия среди типов баз данных, но все равно полезных. Особого внимания заслуживают PLS_INTEGER и SIMPLE_INTEGER . Операции с целочисленным типом PLS_INTEGER реализуются на аппаратном уровне. В частности, счетчики циклов FOR реализуются в формате PLS_INTEGER . Тип SIMPLE_INTEGER , появившийся в Oracle11g, обладает тем же диапазоном значений, что и PLS_INTEGER , но не поддерживает NULL и не инициирует исключение в случае переполнения. SIMPLE_INTEGER , как и SIMPLE_FLOAT с SIMPLE_DOUBLE , работает невероятно быстро — особенно с откомпилированным кодом.
Representing Numeric Data
Use the NUMBER datatype to store real numbers in a fixed-point or floating-point format. Numbers using this datatype are guaranteed to be portable among different Oracle platforms, and offer up to 38 decimal digits of precision. You can store positive and negative numbers of magnitude 1 x 10-130 through 9.99 x10125, as well as zero, in a NUMBER column.
You can specify that a column contains a floating-point number, for example:
Or, you can specify a precision (total number of digits) and scale (number of digits to right of decimal point):
price NUMBER (8, 2)
Although not required, specifying precision and scale helps to identify bad input values. If a precision is not specified, the column stores values as given. The following table shows examples of how data different scale factors affect storage.
Input Data Specified As Stored As 4,751,132.79 NUMBER 4751132.79 4,751,132.79 NUMBER (9) 4751133 4,751,132.79 NUMBER (9,2) 4751132.79 4,751,132.79 NUMBER (9,1) 4751132.7 4,751,132.79 NUMBER (6) (not accepted, exceeds precision) 4,751,132.79 NUMBER (7, -2) 4,751100 Date and Time Functions
Логические данные
PL/SQL поддерживает тип данных BOOLEAN . Переменные этого типа могут принимать одно из трех значений ( TRUE, FALSE или NULL ).
Логические переменные помогают писать понятный, легко читаемый код даже в тех случаях, когда он содержит очень сложные логические выражения. Пример объявления логической переменной с присваиванием ей значения по умолчанию:
Indexes and Clusters
Читайте также: