Типы данных Oracle sql

Обновлено: 06.07.2024

Когда я впервые начал работать с SQL Server, поскольку первоначально работал исключительно с базами данных Oracle, я обнаружил, что постоянно думаю о типах данных Oracle и вынужден искать эквиваленты в SQL Server. Теперь, когда я в первую очередь являюсь администратором базы данных SQL Server, я обнаружил, что все наоборот. В этом совете будут сравниваться различные типы данных, предлагаемые обеими системами баз данных, поэтому те из нас, кто работает с обеими технологиями или планирует переход с одной технологии на другую, получат руководство, которое они могут использовать в качестве краткого справочника.

Решение

Для этого сравнения мы сосредоточимся на SQL Server 2012 и Oracle 11g Release 2. Полное описание всех типов данных для каждой технологии можно найти по следующим ссылкам:

Для этого сравнения мы разделим типы данных на следующие категории (как указано в ссылке SQL Server выше):

Точное число
Приблизительное числовое значение
Дата и время
символьные строки (юникодные и не юникодные)
Двоичные строки
Другие типы данных

Точные числовые типы данных в SQL Server и Oracle

Когда дело доходит до точных чисел, наиболее часто используемым типом данных, вероятно, будет целочисленный тип данных. С SQL Server у нас есть следующие параметры в зависимости от требуемого размера. TINYINT, SMALLINT, INT и BIGINT. Эквивалентом этих типов данных в Oracle будет тип данных NUMBER, указывающий точность на основе требуемого размера и масштаба 0. Указание точности и масштаба должно звонить в колокол администраторам баз данных SQL Server, когда речь идет о десятичных и числовых типах данных, которые мы используем в SQL. Сервер. DECIMAL[(p[,s])] и NUMERIC[(p[,s])] в SQL Server могут быть представлены ЧИСЛО(p,s) в Oracle. Единственное различие между этими двумя технологиями заключается в том, что в SQL Server масштаб должен быть между 0 и точностью, т. е. 0

Комментарии к этой статье

Спасибо, эта статья мне помогла!

Можно ли использовать стили даты и времени (101, 102 и т. д.) в Oracle Sql Developer? Или эти коды стиля являются эксклюзивными для продуктов Microsoft?

Спасибо! Эта диаграмма именно то, что мне было нужно!

При этом - я думаю, что это опечатка - не должно ли varchar в sqlserver быть эквивалентом varchar2 в oracle (в отличие от archar2, который в настоящее время указан выше).

Ниже приведен список типов данных, доступных в Oracle/PLSQL, который включает в себя символьные, числовые типы данных, дату/время, большие объекты и идентификаторы строк.

Типы данных символов

Следующие являются символьными типами данных в Oracle/PLSQL:

Максимальный размер 4000 байт.

Максимальный размер 32 КБ в PLSQL.

Максимальный размер 4000 байт.

Максимальный размер 32 КБ в PLSQL.

Максимальный размер 4000 байт.

Максимальный размер 32 КБ в PLSQL.

Числовые типы данных

Следующие являются числовыми типами данных в Oracle/PLSQL:

Где p — точность, а s — масштаб.

Например, число (7,2) — это число, в котором 5 цифр до десятичной точки и 2 цифры после запятой.

Где p — точность, а s — масштаб.

Например, numeric(7,2) — это число, состоящее из 5 цифр перед запятой и 2 цифр после запятой.

Где p — точность, а s — масштаб.

Например, dec(3,1) – это число, в котором 2 цифры перед запятой и 1 цифра после запятой.

Где p — точность, а s — масштаб.

Например, decimal(3,1) – это число, в котором 2 цифры перед запятой и 1 цифра после запятой.

Типы данных даты/времени

Следующие типы данных даты/времени в Oracle/PLSQL:

Включает год, месяц, день, час, минуту и секунду.

Например:
метка времени(6)

Включает год, месяц, день, час, минуту и секунду; со значением смещения часового пояса.

Например:
метка времени(5) с часовым поясом

Например:
метка времени(4) с местным часовым поясом

Период времени в годах и месяцах.

Например:
интервал от года(4) до месяца

точность дня должна быть числом от 0 до 9 (по умолчанию 2)

точность долей секунды должна быть числом от 0 до 9 (по умолчанию 6)

точность дня должна быть числом от 0 до 9 (по умолчанию 2)

точность долей секунды должна быть числом от 0 до 9 (по умолчанию 6)

точность дня должна быть числом от 0 до 9 (по умолчанию 2)

точность долей секунды должна быть числом от 0 до 9 (по умолчанию 6)

Период времени в днях, часах, минутах и секундах.

Например:
интервал от дня(2) до секунды(6)

Типы данных больших объектов (LOB)

Следующие типы данных LOB в Oracle/PLSQL:

< /tr>

Синтаксис типа данных	Oracle 9i	Oracle 10g	Oracle 11g	Пояснение
bfile	Максимальный размер файла 4 ГБ.	Максимальный размер файла 2 32 -1 байт.	Максимальный размер файла 2 64 -1 байт.	Локаторы файлов, указывающие на двоичный файл в файловой системе сервера (вне базы данных).
blob	Хранить до 4 ГБ двоичных данных.	Хранить до (4 гигабайт -1) * (значение параметра CHUNK в LOB-хранилище).	Хранить до (4 гигабайта -1) * (значение параметра CHUNK LOB-хранилища).	Хранит неструктурированные двоичные большие объекты.
clob	Хранить до 4 ГБ символьных данных.	Хранить до (4 гигабайта -1) * (значение параметра CHUNK LOB-хранилища) символьных данных.	Хранить до (4 гигабайта -1) * (значение параметра CHUNK LOB-хранилища) символьных данных.	Хранить в памяти le-байтовые и многобайтовые символьные данные.
nclob	Хранить до 4 ГБ символьных текстовых данных.	Сохранить до (4 гигабайта -1) * (значение параметра CHUNK хранения больших объектов) текстовых данных символов.	Хранить до (4 гигабайта -1) * (значение параметра CHUNK большого объекта хранилище) текстовых данных символов.	Хранит данные Unicode.

Типы данных Rowid

Следующие типы данных Rowid в Oracle/PLSQL:

Идентификатор строки имеет следующий формат: BBBBBBB.RRRR.FFFFF

Где BBBBBBB — блок в файле базы данных;
RRRR — строка в блоке;
FFFFF — это файл базы данных.

Идентификатор строки имеет следующий формат: BBBBBBB.RRRR.FFFFF

Где BBBBBBB — блок в файле базы данных;
RRRR — строка в блоке;
FFFFF — это файл базы данных.

Идентификатор строки имеет следующий формат: BBBBBBB.RRRR.FFFFF

Где BBBBBBB — блок в файле базы данных;
RRRR — строка в блоке;
FFFFF — это файл базы данных.

Строка в формате Base 64, представляющая логический адрес строки индексно-организованной таблицы.

Необязательный параметр size — это размер столбца типа UROWID.

Максимальный размер по умолчанию – 4 000 байт.

от 8 ТБ до 128 ТБ
(4 гигабайта - 1) * (размер блока базы данных)

8 ТБ в Oracle 9i/10g

от 8 ТБ до 128 ТБ
(4 гигабайта - 1) * (размер блока базы данных)

8 ТБ в Oracle 9i/10g

от 8 ТБ до 128 ТБ
(4 гигабайта - 1) * (размер блока базы данных)

8 ТБ в Oracle 9i/10g

В 11gR1 ограничение составляет 2G/4G в зависимости от набора символов БД.

64 КБ в Oracle 9i/10g

Устарело. Вместо этого используйте двоичное хранилище XML. Заполните XML из CLOB, BLOB или VARCHAR2.

Примечания и примеры

VARCHAR2:
Сохранение символьных данных как Varchar2 сэкономит место:

Oracle9i и более поздние версии позволяют определять столбцы Varchar2 как количество байтов VARCHAR2(50 BYTE) или количество символов VARCHAR2(50 CHAR) , последнее полезно, если база данных когда-либо преобразуется для запуска двухбайтового символа. набор (например, японский), вам не придется редактировать размеры столбцов. Мера по умолчанию, обычно BYTE , устанавливается с помощью nls_length_semantics.

Если вы создадите столбец как Varchar2 (50), но сохраните только 10 байтов, Oracle сохранит только 10 байтов на диск. Это не означает, что вы должны просто создавать столбцы Varchar2 (4000) «на всякий случай, если потребуется место», это действительно плохая идея, которая снизит производительность и удобство обслуживания вашего приложения.

CHAR:
Со временем, когда столбцы varchar2 обновляются, они иногда создают связанные строки, столбцы CHAR имеют фиксированную ширину, на них это не влияет, поэтому для поддержания производительности требуется меньше усилий DBA.

PL/SQL
При извлечении данных для столбца NUMBER рассмотрите возможность использования типа данных PL/SQL: PLS_INTEGER для повышения производительности.

LONG
Используйте BLOB вместо LONG

INTEGER
Этот тип данных ANSI будет принят Oracle — на самом деле это синоним NUMBER(38)

Тип данных FLOAT
Этот тип данных ANSI будет принят Oracle — очень похож на NUMBER, он хранит нулевые, положительные и отрицательные числа с плавающей запятой

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

ЧИСЛО с фиксированной точкой
ЧИСЛО(p,s)
точность p = длина число в цифрах
масштаб s = разряды после запятой или (для отрицательных значений шкалы) значащие разряды перед запятой.

Целое ЧИСЛО
ЧИСЛО(p)
Это число с фиксированной точкой с точностью p и масштабом 0. Эквивалентно ЧИСЛУ(p, 0)

ЧИСЛО С ПЛАВАЮЩЕЙ ЗАПЯТОЙ
ЧИСЛО
число с плавающей запятой с десятичной точностью 38

ТОЧНОСТЬ и МАСШТАБ

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

PRECISION можно использовать для ограничения входных значений.

Если столбец определен как CREATE TABLE демо ( empid number(4) ); то попытка вставить 5-значный номер 10045 будет неудачной. Если вам нужно ограничить значения определенным диапазоном чисел (например, а не круглым числом), добавьте ограничение столбца.

Обратите внимание, что единицы измерения для PRECISION различны, если тип данных FLOAT:

Для типов данных NUMBER: точность p = количество цифр
Для типов данных FLOAT: точность p = двоичная точность (умножить на 0,30103 конвертировать)

Таким образом, FLOAT = FLOAT (126) = 126 x 0,30103 = приблизительно 37,9 разряда точности.

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

Если таблица создается без какой-либо демонстрации Precision/Scale CREATE TABLE ( empid number ); тогда столбец будет хранить значения как заданные. Если масштаб не указан, он равен нулю.

Параметры функции

Oracle не допускает определенной точности для параметров функции, только тип переменной. Это означает, что параметр, определенный как целое число или число (38), может принимать дробные значения. если вы хотите, чтобы процедуре/функции передавалось целое число, используйте pls_integer вместо integer .

В качестве альтернативы переназначение переменной внутри процедуры заставит Oracle неявно преобразовать тип данных и точность для соответствия переменной, thie требует немного другого определения, например число (37) .

Выберите COLUMN_NAME, DATA_TYPE, DATA_LENGTH, DATA_PRECISION, DATA_SCALE
From cols Where table_name = 'Your_Table';

Распределение памяти с помощью Varchar2

Если максимальный размер меньше 4000 байт, PL/SQL выделяет во время компиляции достаточно памяти для максимального размера.
Если максимальный размер составляет 4000 байт или больше, PL/SQL выделяет во время выполнения достаточно памяти для фактического значения.
(До Oracle 11g Release 2 значение переключения составляло 2000 байт вместо 4000)

Логические данные

Вы можете хранить логические значения в виде Oracle CHAR, а не NUMBER.

Создайте TABLE my_demo (счетный код NUMBER, проверка postableYN CHAR (postableYN в (0,1)));
-- Стандартные логические значения: False=0 и True=1
Вставить в my_demo values(525, '1');
Вставьте в my_demo значения (526, '0');

Выберите код аккаунта, расшифруйте (postableYN, 1, 'Истина', 0, 'Ложь') ИЗ my_demo;
< p>-- или по-французски:
Выберите код счета, расшифруйте(postableYN,1,'Vrai',0,'Faux') FROM my_demo;

Старым приемом экономии места было сохранение ' Y' и 'N', это требует не больше (но и не меньше), чем сохранение 0 и 1, но избавляет вас от усилий по написанию оператора декодирования для его отображения. Конечно, если/когда приложение должно поддерживать другие языки, вам все равно нужно будет добавить декодирование.

Сравнение с другими СУБД

< td>XML < td>INTEGER

int10	int6	int1	char(n)	blob
Oracle 11	ЧИСЛО(10)	ЧИСЛО(6)	ЧИСЛО( 1)	VARCHAR2(n)	BLOB	XMLType
MS SQL Server 2008	NUMERIC(10)	NUMERIC(6)	TINYINT	VARCHAR(max)	IMAGE	XML
MS SQL Server 2005	ЧИСЛОВОЕ(10)	ЧИСЛО(6)	TINYINT	VARCHAR(n)	IMAGE	XML
Система Sybase 10	ЧИСЛОВОЕ(10)	ЧИСЛО(6)	ЧИСЛО(1)	VARCHAR(n)	ИЗОБРАЖЕНИЕ
MS Access (Jet)	Long Int или Double	Single	Byte	ТЕКСТ(n)	LONGBINARY
TERADATA	INTEGER	DECIMAL(6)	DECIMAL(1)	VARCHAR(n)	VARBYTE(20480)
DB2	DECIMAL(6)	DECIMAL(1)	VARCHAR(n)	VARCHAR(255)
RDB	INTEGER	DECIMAL( 6)	DECIMAL(1)	VARCHAR(n)	LONG VARCHAR
INFORMIX	INTEGER	DECIMAL(6)	DECIMAL(1)	VARCHAR(n)	BYTE
RedBrick	integer	int	int	char(n)	char(1024)
INGRES	INTEGER	INTEGER	INTEGER	VARCHAR(n)	VARCHAR(1500)

Также учитывайте максимальную длину имени таблицы (или имени столбца) и максимальный размер оператора SQL — эти ограничения значительно различаются между продуктами и версиями.

Типы данных в PL/SQL используются для определения того, как данные будут храниться, обрабатываться и обрабатываться Oracle во время хранения и обработки данных. Типы данных связаны с определенным форматом хранения и ограничениями диапазона. В Oracle каждому значению или константе присваивается тип данных.

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

Ниже представлена диаграмма различных типов данных Oracle PL/SQL:

В этом учебном пособии по типам данных PL/SQL вы узнаете-

Тип данных PL/SQL CHARACTER

Этот тип данных в основном хранит буквенно-цифровые символы в строковом формате.

Литеральные значения всегда должны заключаться в одинарные кавычки при назначении их типу данных CHARACTER.

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

Тип данных CHAR (фиксированный размер строки)
VARCHAR2 Тип данных (переменный размер строки)
Тип данных VARCHAR
NCHAR (собственный фиксированный размер строки)
NVARCHAR2 (собственный размер строки переменной)
ДЛИННЫЙ И ДЛИННЫЙ СЫРОЙ

Oracle дополнил бы переменную пробелами, если бы переменная не занимала весь объявленный для нее размер, поэтому Oracle выделит память для объявленного размера, даже если переменная не заняла ее полностью.
Ограничение размера для этого типа данных: 1–2000 байт.
Тип данных CHAR больше подходит для использования там, где будет обрабатываться фиксированный размер данных.

Объяснение синтаксиса:

В первом операторе объявления объявлена переменная «grade» типа данных CHAR с максимальным размером 1 байт (значение по умолчанию).
Второй оператор объявления объявил переменную «manager» типа данных CHAR с максимальным размером 10 и присвоил 6-байтовое значение «guru99». В этом случае Oracle выделит память размером 10 байт, а не 6 байт.

Ограничение размера для этого типа данных: 1–4000 байт для размера столбца таблицы и 1–32767 байт для переменных.
Размер определяется для каждой переменной во время объявления переменной.
Однако Oracle выделит память только после того, как переменная будет определена, т. е. Oracle будет учитывать только фактическую длину строки, которая хранится в переменной, для выделения памяти, а не размер, указанный для переменной в объявлении. часть.
Всегда рекомендуется использовать тип данных VARCHAR2 вместо типа данных CHAR, чтобы оптимизировать использование памяти.

Объяснение синтаксиса:

Вышеприведенный оператор объявления объявил переменную «менеджер» типа данных VARCHAR2 с максимальным размером 10 и присвоил значение «guru99», которое имеет 6 байтов. В этом случае Oracle выделит память размером всего 6 байт.

Всегда рекомендуется использовать VARCHAR2 вместо VARCHAR, чтобы избежать изменений в поведении.

Объяснение синтаксиса:

Вышеприведенный оператор объявления объявил переменную «менеджер» типа данных VARCHAR с максимальным размером 10 и присвоил значение «guru99», которое имеет 6 байтов. В этом случае Oracle выделит память размером всего 6 байт. (Аналогично VARCHAR2)

Этот набор символов можно определить для сеанса с помощью NLS_PARAMETERS.
Набор символов может быть UTF16 или UTF8.
Ограничение размера: от 1 до 2000 байт.

Объяснение синтаксиса:

Приведенный выше оператор объявления объявляет переменную «собственного» типа данных NCHAR с максимальным размером 10.
Длина этой переменной зависит от (количества длин) на байт, как определено в наборе символов.

Этот набор символов можно определить для сеанса с помощью NLS_PARAMETERS.
Набор символов может быть UTF16 или UTF8.
Ограничение размера: от 1 до 4000 байт.

Объяснение синтаксиса:

Приведенный выше оператор объявления объявляет переменную Native_var типа данных NVARCHAR2 с максимальным размером 10.

Этот тип данных используется для хранения большого текста или необработанных данных размером до 2 ГБ.

В основном они используются в словаре данных.
Тип данных LONG используется для хранения данных набора символов, тогда как LONG RAW используется для хранения данных в двоичном формате.
Тип данных LONG RAW принимает мультимедийные объекты, изображения и т. д., тогда как LONG работает только с данными, которые можно сохранить с помощью набора символов.

Объяснение синтаксиса:

Приведенный выше оператор объявления объявляет переменную «Large_text» типа данных LONG и «Large_raw» типа данных LONG RAW.

Тип данных PL/SQL NUMBER

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

Объяснение синтаксиса:

В приведенном выше примере первое объявление объявляет, что переменная A имеет числовой тип данных с общей точностью 8 и числом десятичных цифр 2.
Второе объявление объявляет, что переменная "B" имеет числовой тип данных с общей точностью 8 и без десятичных цифр.
Третье объявление является наиболее общим, объявляет, что переменная «C» имеет числовой тип данных без ограничений по точности или десятичным разрядам. Он может содержать до 38 цифр.

Тип данных PL/SQL BOOLEAN

Этот тип данных хранит логические значения. Логический тип данных Oracle представляет либо TRUE, либо FALSE и в основном используется в условных операторах. Значения не нужно заключать в кавычки при назначении для этого типа данных.

Объяснение синтаксиса:

В приведенном выше примере переменная «Var1» объявлена как тип данных BOOLEAN. Вывод кода будет либо истинным, либо ложным в зависимости от заданного условия.

Тип данных PL/SQL DATE

Этот тип данных хранит значения в формате даты, например дату, месяц и год. Всякий раз, когда переменная определяется с типом данных DATE вместе с датой, она может содержать информацию о времени, и по умолчанию информация о времени устанавливается на 12:00:00, если она не указана. Значения должны быть заключены в кавычки при назначении для этого типа данных.

Стандартный формат времени Oracle для ввода и вывода — «ДД-МОН-ГГ», и он снова устанавливается в NLS_PARAMETERS (NLS_DATE_FORMAT) на уровне сеанса.

Объяснение синтаксиса:

В приведенном выше примере переменная newyear объявлена как тип данных DATE, и ей присвоено значение даты 1 января 2015 года.
Второе объявление объявляет переменную current_date как тип данных DATE и присваивает ей значение с текущей системной датой.
Обе эти переменные содержат информацию о времени.

Тип данных PL/SQL LOB

Этот тип данных в основном используется для хранения и управления большими блоками неструктурированных данных, таких как изображения, мультимедийные файлы и т. д. Oracle предпочитает LOB вместо типа данных LONG, поскольку он более гибкий, чем тип данных LONG. Ниже приведены основные преимущества типа LOB по сравнению с типом данных LONG.

Количество столбцов в таблице с типом данных LONG ограничено 1, тогда как в таблице с типом данных LOB нет ограничений на количество столбцов.
Инструмент интерфейса данных принимает тип данных таблицы LOB во время репликации данных, но пропускает столбец LONG таблицы. Эти столбцы LONG необходимо реплицировать вручную.
Размер столбца LONG – 2 ГБ, тогда как LOB может хранить до 128 ТБ.
Oracle постоянно совершенствует тип данных LOB в каждом из своих выпусков в соответствии с современными требованиями, в то время как тип данных LONG является постоянным и редко обновляется.

Поэтому всегда полезно использовать тип данных LOB вместо типа данных LONG. Ниже приведены различные типы данных LOB. Они могут хранить до 128 терабайт.

Этот тип данных хранит данные больших объектов в формате двоичного файла до максимального размера 128 ТБ. Это не хранит данные на основе деталей набора символов, поэтому он может хранить неструктурированные данные, такие как мультимедийные объекты, изображения и т. д.

Объяснение синтаксиса:

Тип данных CLOB хранит данные LOB в наборе символов, тогда как NCLOB хранит данные в собственном наборе символов. Поскольку эти типы данных используют хранилище на основе набора символов, они не могут хранить такие данные, как мультимедиа, изображения и т. д., которые нельзя поместить в строку символов. Максимальный размер этих типов данных – 128 ТБ.

Объяснение синтаксиса:

В приведенном выше примере переменная Charac_data объявлена как тип данных CLOB.

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

Обзор

Мы рассмотрели различные простые типы данных, доступные в PL/SQL, а также их синтаксис. Мы узнаем о сложных типах данных в следующих темах.

Читайте также: