РУКОВОДСТВО по работе с языком PL/SQL

РУКОВОДСТВО по работе с языком PL/SQL

Выполнили: с-т Шумейко Г.В.

ефр. Мартынов И.А.

Санкт-Петербург

2007год

ОБЩАЯ ХАРАКТЕРИСТИКА SQL

SQL является фактическим стандартом языковых средств обработки данных для современных СУБД. Практически каждый производитель СУБД определяет собственный вариант языка, учитывающий особенности архитектуры и позиционирование системы на рынке. В настоящее время существуют три стандарта языка SQL, принятых Американским Национальным Институтом Стандартов (ANSI): SQL89, SQL92, SQL3. Каждый последующий стандарт в этой цепочке уточняет и расширяет предыдущий. Следование стандартам не является обязательным, хотя практически все производители систем управления базами данных строго выполняют требования стандарта SQL89 и, в значительной части, — требования SQL92. Операторы SQL можно разбить, на несколько отдельных групп: DML (Data Manipulation Language) — операторы манипуляции данными, DDL (Data Definition Language) — операторы определения данных, группа операторов управления транзакциями и группа операторов предоставления доступа. Операторы DDL предназначены для создания, модификации и удаления объектов базы данных.

Основные объекты Oracle

Oracle поддерживает реляционную модель данных, поэтому естественно, что к числу основных объектов базы данных относятся: таблица, представление и пользователь.

Пользователь (USER) — объект, обладающий возможностью создавать и использовать другие объекты Oracle, а также запрашивать выполнение функций сервера. К числу таких функций относится организация сессии, изменение со- стояния базы данных и др. Следует отметить, что в некоторых других системах управления базами данных, например, IBM DB2, объект базы данных " пользователь" отсутствует. С пользователем Oracle связана схема (SCHEMA), которая является. логическим набором объектов базы данных, таких, как таблицы, последовательности, хранимые программы, принадлежащих этому пользователю. Схема имеет только одного пользователя-владельца, ответственного за создание и удаление этих объектов. При создании пользователем первого объекта неявно создается соответствующая схема. При создании им других объектов они по умолчанию становятся частью этой схемы. Для просмотра объектов схемы текущего пользователя можно использовать представление словаря данных USER_OBJECTS. При массовом выполнении DDL-предложений можно создать несколько объектов и назначить для них привилегии за одну операцию, используя оператор CREATE SCHEMA. Оператор CREATE SCHEMA применяется тогда, когда требуется гарантировать успешное создание всех объектов и назначение привилегий за одну операцию. Если при создании объектов произошла ошибка, происходит возвращение к исходному состоянию. Схема может содержать следующие объекты: кластеры, связи баз данных, триггеры, библиотеки внешних процедур, индексы, пакеты, последовательности, хранимые функции и процедуры, синонимы, таблицы, представления, снимки, объектные таблицы, объектные типы, объектные представления. Объекты схемы могут состоять из других объектов, называемых подобъектами схемы. К ним относятся столбцы таблиц и представлений, секции таблиц, ограничения целостности, триггеры, пакетные процедуры и функции и другие элементы, хранимые в пакетах (курсоры, типы и т. п). К объектам, не принадлежащим схеме, но хранимым в базе данных, относятся каталоги, профили, роли, сегменты отката, табличные области и пользователи.

Таблица (TABLE) - является базовой структурой реляционной модели. Как известно, вся информация в реляционной базе данных хранится в таблицах. Полное имя таблицы в базе данных состоит из имени схемы и собственно имени таблицы. Таблицы состоят из множества поименованных столбцов или атрибутов. Множество допустимых значений атрибута называют доменом значений или просто доменом. Множество допустимых значений столбца также может быть уточнено с помощью статических ограничений целостности. Таблицы могут быть связаны между собой отношениями ссылочной целостности. Таблица может быть пустой или состоять из одной или более строк значений атрибутов. Строки значений атрибутов таблицы называются также кортежами. Для однозначной идентификации строки в таблице служит идентификатор (ROWID) — указатель, имеющий специальный формат. В OracleS появились вложенные таблицы (NESTED GRUP236ES), которые позволяют объявить таблицу как тип значения столбца родительской таблицы. Подробно они рассмотрены в разделе " Объектные расширения в OracleS". Для повышения скорости доступа к данным таблица может быть индексно организована (INDEX-ORGANIZED GRUP236E). Физическое пространство для хранения данных таблицы выделяется частями, называемыми экстентами. Размеры начального и дополнительных экстентов определяются при создании таблицы.

Представление (VIEW) — это поименованная, динамически поддерживаемая сервером выборка из одной или нескольких таблиц. По сути, представление — это производное множество строк, которое является результатом выполнения некоторого запроса к базовым таблицам. В словаре данных хранится только определение представления и, когда в операторе SQL встречается название представления, Oracle обращается к словарю за определением и подставляет его в исходный запрос. Запрос, определяющий выборку, ограничивает видимые пользователем данные. Представления позволяют упростить сложные запросы и сделать более понятными их логику. Используя представления, администратор безопасности ограничивает доступную пользователю часть базы данных только теми данными, которые реально необходимы для выполнения его работы. Представления также можно использовать для поддержки приложений при изменении структуры таблицы. Например, при добавлении нового столбца в таблицу создать представление, его не включающее.

Синоним (SYNONYM) — это альтернативное имя иди псевдоним объекта Oracle, который позволяет пользователям базы данных иметь доступ к данному объекту. Синоним может быть частным и общим. Общий (PUBLIC) синоним позволяет всем пользователям базы данных обращаться к соответствующему объекту по альтернативному имени. Характерным применением общих синонимов является сокрытие информации о схеме, в которой расположен объект. Наличие синонима позволяет обращаться к объекту по имени, которое является абсолютным в масштабе базы данных. Реальная привязка объекта к некоторой схеме при этом скрыта от пользователя или приложения. Для управления эффективностью доступа к данным Oracle поддерживает следующие объекты: индекс, табличная область, кластер и хэш-кластер.

Индекс (INDEX) — это объект базы данных, предназначенный для повышения производительности выборки данных. Индекс создается для столбцов таблицы и обеспечивает более быстрый доступ к данным за счет хранения указателей (ROWID) на месторасположение строк. При обращении к индексированному столбцу сервер по предъявляемому значению находит в индексе указатели на эти строки, а потом непосредственно обращается к ним. Если все требуемые значения столбцов имеются в индексе, обращение к таблице не происходит вовсе. Имеется несколько типов индексов — В*Тгее (двоичное дерево, каждый узел которого содержит указатель на следующий и предыдущий), масочный индекс, индекс с реверсированным ключом, кластерный индекс. Подробнее о них рассказывается в разделе " Методы повышения производительности".

Кластер (CLUSTER) — объект, задающий способ хранения данных нескольких таблиц, содержащих информацию, обычно обрабатываемую совместно, например, значения столбцов таблиц, часто участвующих в эквисоединениях. Строки таких таблиц, имеющие одинаковое значение в кластеризованных столбцах, хранятся в базе данных специальным образом: на логическом уровне — в нормализованном виде, а на физическом — в ненормализованном. Кластеризация столбцов таблиц позволяет уменьшить время выполнения выборки. При использовании хешированных кластеров (HASH CLUSTER) организация таблиц базируется на результатах хэширования их первичных ключей. Для получения данных из такого кластера запрашиваемое значение ключа обрабатывается хэш-функцией, полученное значение определяет, в каком блоке кластера хранятся данные.

Табличная область (TABLESPACE) -— именованная часть базы данных, используемая для распределения памяти для таблиц, индексов и других объектов. В табличную область входит один или несколько файлов. Это предоставляет возможность гибко настроить хранение данных в зависимости от порядка и интенсивности их использования. Например, можно отвести одну табличную область для таблиц, а другую — для индексов. В каждой базе данных есть табличная область SYSTEM, с которой связаны все системные объекты, например таблицы словаря данных. Доступность табличных областей может устанавливаться переводом в автономный или оперативный режим. Для эффективного управления разграничением доступа к данным Oracle поддерживает объект роль.

Роль (ROLE) — именованная совокупность привилегий, которые могут быть предоставлены пользователям или другим ролям. Oracle поддерживает несколько предопределенных ролей. Для систем, в которых количество пользователей и приложений велико, роли могут заметно облегчить разграничение доступа, например, возможно динамически назначать роли для изменения набора привилегий пользователя при работе с различными приложениями. Также роли можно защищать паролем. Специфичными для распределенных систем являются объекты Oracle: снимок и связь базы данных.

Снимок (SNAPSHOT) — локальная копия таблицы уда- ленной базы данных, которая, используется либо для тиражирования (копирования) всей или части таблицы, либо для тиражирования результата запроса данных из нескольких таблиц. Снимки могут быть модифицируемыми или предназначенными только для чтения. Снимки только для чтения возможно периодически обновлять, отражая изменения основной таблицы. Изменения, сделанные в модифицируемом снимке, распространяются на основную таблицу и другие копии. Связь базы данных (DATABASE LINK) — это объект базы данных, который позволяет обратиться к объектам удаленной базы данных. Имя связи базы данных можно рассматривать как ссылку на параметры механизма доступа к удаленной базе данных (имя узла, протокол и т. п.).

Сегмент отката (ROLBACK SEGMENT) — объект базы данных, предназначенный для обеспечения многопользовательской работы. В сегментах отката находятся обновляемые и удаляемые данные в пределах одной транзакции. При отмене изменений старая версия данных всегда доступна, так как находится в сегментах отката. В начале транзакции и в каждой контрольной точке текущие значения данных копируются в сегмент отката. Кроме того, сегменты отката используются при других операциях сервера. Размер и доступность сегментов отката в сильной степени влияют на производительность сервера баз данных и их настройка должна быть выполнена самым тщательным образом. Для программирования алгоритмов обработки данных, реализации механизмов динамической поддержки целостности базы данных Oracle использует следующие объекты: процедуры, функции, пакеты, тела пакетов и триггеры.

Процедура (PROCEDURE) — это поименованный, структурированный набор конструкций языка PL/SQL, предназначенный для решения конкретной задачи. Функция (FUNCTION) — это поименованный, структурированный набор конструкций языка PL/SQL, предназначенный для решения конкретной задачи и возвращающий значение.

Пакет (PACKAGE) — это поименованный, структурированный набор переменных, процедур и функций и других объектов, связанных функциональным замыслом. Пакет состоит из двух самостоятельных частей: заголовка и тела. Заголовок содержит описание переменных, констант, типов, процедур, функций и других конструкций языка PL/SQL. Тело пакета содержит реализацию алгоритмов процедур и функций и хранится отдельно. Например, Oracle предоставляет стандартный пакет UTL_FILE, который содержит процедуры и функции, предназначенные для организации файлового ввода-вывода из программ на языке PL/SQL.

Триггер (TRIGGER) — это хранимая процедура, которая автоматически выполняется тогда, когда происходит связанное с триггером событие. Обычно события связаны с выполнением операторов вставки, модификации и удаления данных. С помощью триггеров можно реализовать правила динамической проверки целостности данных и дополнительного контроля доступа. Библиотеки (LIBRARY) — объекты базы данных, предназначенные для взаимодействия программ PL/SQL с модулями, написанными на других языках программирования. Типы (TYPE) и коллекции типов — новые виды объектов базы данных для OracleS, предназначенные для реализации объектных расширений.

Каталог (DIRECTORY) — объект, предназначенный для организации файлового ввода-вывода и работы с большими двоичными объектами.

Профиль (PROFILE) — объект, ограничивающий использование пользователем системных ресурсов, например процессорного времени или числа операций ввода-вывода.

Структура запроса.

Оператор SELECT используется для выборки атрибутов одной или нескольких таблиц в соответствии с указанным критерием отбора. В запросе можно использовать встроенные функции и, более того, пакетные функции, не изменяющие состояние базы данных (обладающие необходимым " уровнем чистоты" ). Последовательность операций при прохождении оператора SELECT через архитектуру сервера Oracle была приведена в разделе " Архитектура распределенных систем обработки данных". Всякий запрос начинается с ключевого слова SELECT. После ключевого слова SELECT следует список, определяющий перечень выводимых столбцов и, возможно, элементы форматирования. Ключевое слово FROM определяет таблицы, представления или снимки, из которых будут отбираться данные. Ключевое слово WHERE определяет логическое условие отбора данных. Если ключевое слово WHERE опущено, то осуществляется выбор из декартова произведения таблиц, представлений и снимков, указанных в перечне значений после ключевого слова FROM.

Простейшие запросы

Хотя выводить все атрибуты таблицы обычно требуется редко, стандарт SQL поддерживает простой способ запроса на вывод всех атрибутов. Для этого используется специальный символ " *" (звездочка). Проиллюстрируем использование простейших запросов на таблице, созданной и заполненной предложениями:

CREATE TABLE GRUP236 (ID NUMBER, FIO VARCHAR2(30), DOLGNOST VARCHAR2(20), ZVANIE VARCHAR2(20), OKLAD NUMBER);

INSERT INTO GRUP236 VALUES(1, ’Alijakin Denis Urievich’, ’ryadovoi’, ’kursant’, 3000);

INSERT INTO GRUP236 VALUES(2, ’Anshukov Anton Olegovich’, ’ryadovoi’, ’efreitor’, 3100);

INSERT INTO GRUP236 VALUES(3, ’Bagenov Petr Igorevich’, ’ryadovoi’, ’efreitor’, 3000);

Запрос, выполняющий вывод всех данных таблицы GRUP236, задается оператором SELECT * FROM GRUP236:

SQL> SELECT * FROM GRUP236;

FIO ID

-----------------------------------------------------

Alijalin Denis Urievich 1

Anshukov Anton Olegovich 2

Bagenov Petr Igorevich 3

Листинг 3.Запрос, выводящий данные столбцов таблицы GRUP236 в указанном порядке

Дополнительно пользователь может указать в списке вывода выражения, зависящие от значений столбца и заголовок, который будет выведен на соответствующей позиции. Ниже приведен пример запроса, выполняющего выборку значений столбца OKLAD+500 с соответствующим заголовком и значения столбца ZVANIE с присоединенной строкой:

SQL> SELECT OKLAD+500 " OKLAD BEZ NALOGOV", ZVANIE FROM GRUP236;

OKLAD BEZ NALOGOV ZVANIE

------------------------------ --------------------

Kursant

Efreitor

Листинг 4.Запрос, выводящий значения OKLAD+500 и ZVANIE с присоединеннойстрокой и с измененными заголовками

Результатом выполнения операции выборки является мультимножество, то есть, если это не указано явно, устранение повторяющихся строк не производится. Для устранения повторяющихся строк используется ключевое слово DISTINCT. Для устранения дубликатов Oracle неявно производит сортировку и в этом случае время выполнения запроса может оказаться неожиданно большим. Поэтому не рекомендуется использовать эту конструкцию в приложениях, требующих малого времени реакции. Сравните два запроса:

SQL> SELECT ZVANIE FROM GRUP236;

ZVANIE

----------

Kursant

Efreitor

SQL> SELECT DISTINCT ZVANIE FROM GRUP236;

ZVANIE

---------

Kursant

Efreitor

Листинг 5. Запросы без устранения повторяющихся строк и с устранением повторяющихся строк

DECLARE

операторы...

BEGIN

Операторы..

EXCEPTION

опера торы .....

END;

Программа такого вида называется анонимным блоком. В блоке DECLARE описываются переменные, константы и определяемые пользователем типы данных. Первый оператор BEGIN отмечает начало тела основной программы. В тело программы могут быть вложены другие блоки, ограниченные операторными скобками BEGIN и END. В блоке EXCEPTION определяются фрагменты программного кода для обработки исключительных ситуаций в программе. Последний оператор END указывает конец тела программы. В анонимном блоке могут отсутствовать блоки DECLARE и EXCEPTION, но обязательно должен присутствовать блок операторов, ограниченный операторными скобками BEGIN и END. В вырожденном случае там может присутствовать только оператор NULL. В любые части программы на PL/SQL можно включать комментарии. Текст, который начинается с символов " --" и продолжается до конца текущей строки, рассматривается как комментарий. Многострочные комментарии включаются между символами " /*" и " */". Использование комментариев является хорошей практикой составления программ. Также хорошей практикой программирования является придание тексту программ удобочитаемости с помощью выделения синтаксических конструкций языка двумя-тремя отступами или использование для форматирования кода специальных средств, например PL/Formatter производства uest Software.

Перед блоком DECLARE могут присутствовать команды установки переменных окружения для различных инструментальных средств. Переменные, константы и типы В ограниченном операторами DECLARE и BEGIN блоке программы PL/SQL описываются переменные, типы и константы. Любая переменная или константа должна иметь один из допустимых в PL/SQL типов. Константа идентифицируется ключевым словом CONSTANT и отличается от переменной тем, что попытка изменить ее значение приводит к сообщению об ошибке. Присваивание значений переменным осуществляется оператором ": =". Типы данных в PL/SQL практически совпадают с описанными выше типами данных SQL за исключением некоторых несущественных различий (данные могут иметь отличающуюся максимальную длину, различия в реализации, имеется специфические для PL/SQL типы данных PLSJNTEGER, BINARYJNTEGER и т. д.). Наряду со скалярными типами данных в PL/SQL присутствуют составные: тип RECORD, массивы и PL/SQL-таблицы. Составной тип данных RECORD предназначен для хранения и обработки записей. Каждая запись имеет атрибуты, которые могут быть проинициализированы при объявлении. Обращение к атрибутам записи производится с использованием полной нотации, включающей имя переменной и имя атрибута, отделенное точкой.

В переменные типа RECORD удобно выполнять выборку данных с помощью курсоров. PL/SQL-таблицы и способы работы с ними будут рассмотрены далее. Рассмотрим пример простейшей программы, в которой определяются переменные и выполняются действия по вычислению натурального логарифма чисел 2 и 3. Команды установки переменных окружения SET SERVEROUTPUT и SET ECHO определяют режим вывода на терминал пользователя. Процедура DBMS_OUTPUT.PUT_LINE обеспечивает вывод данных на терминал пользователя и позже будет рассмотрена подробно, функция LN вычисляет натуральный логарифм. Символ " /" указывает на завершение текста анонимного блока и является командой к интерпретации и выполнению.

SQL> set serveroutput on;

SQL> set echo on;

SQL> declare

2 Header1 CONSTANT VARCHAR2(20): = ‘Логарифм двух равен ';

3 Header2 CONSTANT VARCHAR2(20): = ‘Логарифм двух равен ';

4 Arg NUMBER: = 2;

BEGIN

6 DBMS_OUTPUT.PUT_LINE(Header1||LN(Arg));

7 Arg: = Arg+1;

8 DBMS_OUTPUT.PUT_LINE(Header2||LN(Arg));

9 end;

10 /

Jan 4 14: 44: 6 2002

Connected by:

Production

With the Objects option

USER

SYSTEM

Листинг 9. Выполнение SQL-оператора в SQL*Plus

Команды и операторы запоминаются в SQL-буфере — очень удобная особенность SQL*Plus. Содержимое SQL- буфера можно повторно выполнить, сохранить на диске, отредактировать в текстовом редакторе. SQL-буфер не очищается до тех пор, пока не начнется ввод очередного предложения SQL или блока PL/SQL. Для просмотра содержимого буфера используется команда LIST.

SQL> LIST

1* SELECT USER FROM dual,

Листинг 10. Просмотр содержимого SQL-буфера

Для программ PL/SQL порядок их ввода несколько иной — каждый блок PL/SQL начинается с зарезервированного слова DECLARE, или, если блок не имеет декларативной части, со слова BEGIN. Ввод любого из этих слов заставляет SQL*Plus очистить SQL-буфер, войти в режим ввода и игнорировать "; " как терминальный символ. Заканчивая ввод блока PL/SQL, можно не вводить одиночную точку. Если вместо точки ввести в очередной строке одиночную наклонную черту (/), то блок PL/SQL сохранится в SQL-буфере и выполнится. Если SQL-буфер содержит оператор SQL или программу PL/SQL, то для их выполнения нужно ввести команду RUN или просто наклонную черту:

SQL> set serveroutput on

SQL> BEGIN

2 DBMSJDUTPUT.PUT_LINE(SYSDATE);

3 END;

4 /

08-04-2002

Enter value for atl: 3

old.1: SELECT COUNT(l) с FROM GRUP236 WHERE ID=& ID

new 1: SELECT COUNT(1) с FROM GRUP236 WHERE ID=’2'

ZVANIE

Efreitor

Листинг 12. Использование переменных подстановки SQL*Plus

Из SQL*Plus можно вызывать хранимые программы с помощью команды EXECUTE, не используя конструкции BEGIN... END, В следующем примере вызывается процедура DBMS OUPUT.PUT LINE:

SQL> set serveroutput on

SQL> EXECUTE DBMS_OUTPUT.PUT_LINE('Hello, word');

Hello, word

Листинг 13. Использование команды EXECUTE

В том случае, если SQL*Plus используется для получения текстовых отчетов, можно применить встроенные команды для форматирования вывода данных. В качестве примера рассмотрим стандартный запрос о сессиях базы данных.

SQL> set line 250

SQL> col sid format a3

SQL> col serf format a5

SQL> col program format a40

SQL> col username format a8

SQL> col osuser format a8

, SQL> col machine format a8

SQL> col command format alO

SQL> set heading on

SQL> SELECT s.sid sid,

S.serial sertt,

" 3 s.username username, s.osuser, s.machine,

Substr(a.name, 1, 13) command

6.FROM v$session s, sys.audit_actions a

7 WHERE a.action=s.command

8 AND s.sid> 5

ORDER BY 1, 2

10./

U1 UD DFORT PLUS80W SELECT

Листинг 14. Форматирование отчетов в SQL*Plus

Здесь мы с помощью команд COLUMN и SET HEADING. установили вывод заголовков столбцов и их ширину. Более полное руководство по использованию SQL*Plus можно получить в разделе документации Oracle SQL*Plus User's Guide and Reference. Следует отметить, что для работы с Oracle существуют более удобные интегрированные средства. В частности, это программные продукты фирмы Quest Software: SQL Navigator, Instance Monitor, TOAD и другие. Ознакомительные версии программ находятся на сайте компании —www.quest.com.

ТИПЫ ДАННЫХ ORACLE

Данный раздел представляет типы данных, поддерживаемые Oracle. Все типы данных, перечисленных в стандарте ANSI SQL92, полностью поддерживаются в OracleS. Типы данных, специфичные для сервера Oracle, отмечены строкой [Только для Oracle].

Строки символов

Тип CHARACTER используется для хранения строк фиксированной длины. Для хранения строк резервируется определенное в параметре длина пространство. При необходимости короткая строка дополняется пробелами.

Синтаксис: CHARACTER[(длина)], СНAR длина)].

Если длина строки не указана явно, она полагается равной 1. Максимальное значение параметра длина — 255 символов.

Пример

strl CHAR(10)

Str2 CHARACTER

Тип VARCHAR используется для хранения строк переменной длины. Для хранения строк резервируется реально необходимое пространство.

Синтаксис: VARCHAR [(длина)], CHAR VARYING [(длина)], CHARACTER VARYING [(длина)].' Если длина строки не указана явно, она полагается равной 1. Максимальное значение параметра длина — 4000 символов.

Пример

varstrl VARCHAR(10)

varstr2 CHARACTER VARYING(5)

Тип VARCHAR2 [Только для Oracle] используется для хранения строк переменной длины. Для хранения строк резервируется реально необходимое пространство. Основная причина введения типа VARCHAR2 состоит в том, что фирма-производитель декларирует неизменность этого типа в более поздних реализациях Oracle, в то время как тип VARCHAR будет соответствовать требованиям стандартов SQL.

Синтаксис: VARCHAR2 (длина). Длина строки должна быть указана явно. Минимальное значение параметра длина — 1 символ, максимальное значение параметра длина — 4000 символов.

Пример

.. strlora VARCHAR2(10)

Между этими типами есть различие — данные типа CHAR имеют фиксированную длину и могут содержать до 255 символов, a VARCHAR2 имеет переменную длину и может содержать до 4000 символов. По умолчанию в SQL все строковые литералы имеют тип CHAR. Значение любого столбца типа CHAR(255) занимает 255 байт дискового пространства и также 255 байтов в листьевом наборе индекса. Эти различия следует учитывать при проектировании структуры таблиц, выделении места для хранения данных и сравнениях строк в SQL-выражениях. Если две строки имеют разную длину, то при сравнении более короткая строка дополняется пробелами до длины второй строки. В том случае, если ограничение в 4000 символов существенно и есть необходимость хранить более длинные тексты именно в VARCHAR2, можно создать таблицу, каждая запись которой будет содержать одну строку текста и для ускорения доступа поместить ее в однотабличный кластер. Для хранения символьных данных с использованием национальных алфавитов предназначены типы NCHAR и NVARCHAR2.

Тип LONG [Только для Oracle] используется для хранения больших строк переменной длины. Для хранения строк резервируется реально необходимое пространство. Использование переменных типа LONG имеет следующие ограничения:

— таблица не может содержать более одного столбца с типом данных LONG;

— для столбцов типа LONG не разрешено построение индекса;

— столбцы типа LONG не могут включаться в ограничения целостности (кроме ограничения NULL или NOT NULL);

— их нельзя использовать в выражениях вида INSERT INTO... SELECT... FROM;

— столбцы этого типа нельзя использовать в конструкциях GROUP BY, CONNECT BY.

Фирма-производитель считает тип LONG устаревшим и не рекомендует его использовать. Тип данных LONG может не поддерживаться в более поздних версиях Oracle.

Синтаксис: LONG [(длина)]. Если длина строки не указана явно, она полагается равной 2 мегабайтам. Максимальное значение параметра длина — 2 гигабайта символов.

Пример

longstr LONG(10000)

morelongstr LONG

Числовые типы

Тип INTEGER используется для представления целых чисел в диапазоне от -231 до 231.

Синтаксис: INTEGER, INT.

Пример

varintl INTEGER

varint2 I-NT

Тип NUMBER[Только для Oracle] используется для представления чисел с заданной точностью.

Синтаксис: NUMBER [(точность [, масштаб])]. Если значение параметра точность не указано явно, оно полагается равным 38. Значение параметра масштаб по умолчанию предполагается равным 0. Значение параметра точность может изменяться от 1 до 38, значение параметра масштаб может изменяться от -84 до 127. Использование отрицательных значений масштаба означает сдвиг десятичной точки в сторону старших разрядов. Например, определение NUMBER (7, -3) означает округление до тысяч.

Пример

varcounter NUMBER

В таблице 2 приведены представления числа 123456.789 с различной точностью и масштабом.

Таблица 2. Представления числа 123456.789 с различной точностью и масштабом

Определение Представление

при выполнении действий

NUMBER 123456

NUMBER(7, 1) 123456.7

NUMBER(5, 2) ошибка в данных

NUMBER(8, 3) 1234456.789

NUMBER(7, -2) 1234500

Для совместимости с другими СУБД Oracle поддерживает типы данных DECIMAL, DOUBLE_PRECISION, NUMERIC, DEC и REAL. Все типы числовых данных реально хранятся в одном и том же внутреннем формате Oracle.

Типы DECIMAL И NUMERICполностью эквивалентны типу NUMBER.

Синтаксис: DECIMAL [(точность [, масштаб])], DEC [(точность [, масштаб])], NUMERIC [(точность [, масштаб])].

Пример

vardecl DEC

vardec2 DEC(5)

vardecS D'ECIMAL (8, 3)

varnum NUMERIC

Тип ROWID — специальный тип данных, который служит для представления указателей на запись в таблице. При создании строки в таблице ей сразу присваивается ROWID, который остается неизменным до ее удаления или реорганизации данных. Использование ROWID — самый быстрый способ доступа к строке в таблице.

SQL > SELECT ROWID FROM GRUP236 WHERE ROWNUM=1;

ROWID

AAADFlAADAAAGnPABX

Листинг 15. Вычисление ROWID

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

SQL> CREATE TABLE GRUP236 (ID NUMBER, FIO VARCHAR2(30), DOLGNOST VARCHAR2(20), ZVANIE VARCHAR2(20), OKLAD NUMBER);

GRUP236e created.

SQL> INSERT INTO GRUP236 VALUES(1, ’Alijakin Denis Urievich’, ’ryadovoi’, ’kursant’, 3000);

Row created.

SQL> INSERT INTO GRUP236 VALUES(2, ’Anshukov Anton Olegovich’, ’ryadovoi’, ’efreitor’, 3100);

Row created.

SQL> INSERT INTO GRUP236 VALUES(3, ’Bagenov Petr Igorevich’, ’ryadovoi’, ’efreitor’, 3000);

Row created.

SQL> INSERT INTO GRUP236 VALUES(1, ’Alijakin Denis Urievich’, ’ryadovoi’, ’kursant’, 3000);

Row created.

SQL> DELETE FROM GRUP236

WHERE ROWID NOT IN (SELECT MIN(ROWID)

FROM GRUP236

GROUP BY ID)

5 /

Row deleted.

Листинг 16. Пример удаления записей-дубликатов

Битовые строк

Тип RAW[Только для Oracle] используется для хранения двоичных строк переменной длины. Отличие типа RAW от типов CHAR, VARCHAR2 состоит в том, что для типов символьных строк Oracle производит автоматическое преобразование данных при их передаче между клиентом и сервером. Утилиты Import и Export также производят автоматическое преобразование символьных строк при логической разгрузке и загрузке баз данных в соответствии с настройками средств поддержки национальных языков. Oracle выдает данные типа RAW в шестнадцатеричном виде.

Синтаксис: RAW [(длина)]. Параметр длина измеряется в байтах. Максимальное значение параметра длина — 2000 байт.

Пример

bitarrayl RAW(10)

Тип LONG RAW [Только для Oracle] используется для хранения больших битовых строк переменной длины.

Синтаксис: LONG RAW [(длина)]. Параметр длина измеряется в байтах. Если длина строки не указана явно, онаполагается равной 2 мегабайтам. Максимальное значение параметра длина — 2 гигабайта символов. Для переменных типаLONG RAW невозможно построение индекса.

Пример

verylongstrl LONG RAW(1000000)

Дата и время

Тип DATE [Только для Oracle] используется для хранения даты и времени. Допускаются даты с 1 января 4712 г. дон.э. до 31 декабря 4712 г. н.э. Для формирования значения типа DATE в SQL и PL/SQL обычно используется встроенная функция ТО_ОАТЕ(произвольная_строка_даты', 'формат_даты *). При определении даты без уточнения времени по умолчанию принимается время полуночи. Функция SYSDATE возвращает текущее значение даты и времени. Значение функции определяется средствами операционной системы компьютера, на котором работает сервер Oracle.

Синтаксис: DATE.

Пример

birthday DATE

Наличие специального типа для хранения даты и времени позволяет поддерживать специальную арифметику дат и времен. Добавление к переменной типа DATE целого числа интерпретируется Oracle как определение более поздней даты, а вычитание выполняется как определение более ранней. Рассмотрим несколько примеров:

SQL > SELECT SYSDATE FROM dual;

SYSDATE

13-07-2007

SQL> SELECT SYSDATE+10 " sysd+10" FROM dual;

SYSD+10

23-07-2007

SQL> SELECT SYSDATE-10 " sysd-10" FROM dual;

SYSD-10

03-07-2007

Листинг 17. Запросы, иллюстрирующие арифметику дат Oracle

Также возможно использование юлианской даты. Юлианская дата — это число дней, прошедших с 1 января 4712 г. до нашей эры. Реализация юлианской даты в Oracle не имеет компоненты времени. Для использования юлианской даты в функциях TO_CHAR и TO_DATE применяется маска формата " J".

SQL> SELECT TO_CHAR(TO_DATE('13-07-2007',

2 'DD-MM-YYYY'), 'J') JDATE FROM dual;

JDATE

-------

Листинг 18. Использование юлианской даты

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

LOB-объекты

Тип BLOB [Только для Oracle] используется для хранения двоичных данных размером до 4 гигабайт. Для работы с большими двоичными объектами используется стандартный пакет DBMS_LOB, о котором рассказывается в разделе " PL/SQL — процедурное расширение языка SQL".

Синтаксис: BLOB.

Пример

doc BLOB

Тип CLOB [Только для Oracle] используется для хранения символьных данных переменной длины размером до 4 гигабайт, использующих однобайтовую кодировку.

Синтаксис: CLOB.

Пример

cdoc CLOB

Тип NCLOB [Только для Oracle] используется для хранения символьных данных размером до 4 гигабайт, использующих одно- или многобайтовую кодировку.

Синтаксис: NCLOB.

Пример

ncdoc NCLOB

Тип BFILE [Только для Oracle] используется для хранения указателей на двоичные данные, находящиеся во внешних по отношению к СУБД файлах. Сами файлы хранятся в файловой системе.

Синтаксис: BFILE.

Пример

file doc BFILE

Procedure created.

Листинг 30. Протокол создания процедуры, выполняющей выборку записей из таблицы GRUP236

12 3 4 5 Следующая ⇒