Управление таблицами. Изучаем SQL. Введение в PL/SQL.

Выпуск 23

Привет!

Вот и наступила пора очередного выпуска рассылки <СУБД Oracle "с нуля">. Сегодня мы продолжим тему управления таблицами. В частности рассмотрим типы данных и возможности использования секционированных таблиц. В 22-м выпуске я объявил, что SQL станет постоянной рубрикой рассылки. Так оно и будет. Кроме этого, открывается постоянная рубрика по PL/SQL, которую будет вести Каплич Сергей.

Вы всегда можете посмотреть последние новости проекта ORANET, заглянуть в архив рассылки, а также задать вопрос на форуме сайта oranet.ru . Для зарегистрированных пользователей есть возможность скачать несколько книг на русском языке.

Также я говорил, что мы занимаемся переводом официальной документации Oracle, но пока что не можем предоставить переводы для публичного доступа. Как только будут улажены правовые аспекты, все будет выложено на сайте. Сейчас в процессе перевода две книги, это "Концепции Oracle Database 10g" и "Двухдневный курс разработчика Oracle Database 10g XE". Приглашаю желающих присоединиться к проекту.

Если Вы хотите попробовать себя на стезе переводчика, или написать техническую статью по тематике баз данных Oracle – наш проект всегда открыт. Пишите – zlygostev@gmail.com

Хочу от души поздравить всех читателей с наступающим Новым Годом! Следующий выпуск выйдет в 2006 году.

Как всегда, вопросы по предыдущим выпускам рассылки:

1. Для чего нужна пробная схема HR?
2. Как получить доступ к схеме HR?
3. Какие существуют ограничения на использование Oracle Database 10g XE?
4. Из каких элементов состоят таблицы?
5. Как устранить дублирование строк в результатах SQL-запроса?

В этом выпуске:

• Новости
• Управление таблицами
• Изучаем SQL
• Введение в PL/SQL

Новости

Еврокомиссия одобрила поглощение Siebel

В четверг Еврокомиссия одобрила сделку по слиянию Siebel Systems с Oracle. Гигант бизнес-ПО приобретет своего крупного конкурента за $5,85 млрд. Процедура, начатая в сентябре, завершится в начале следующего года и пополнит портфолио Oracle в сфере приложений для корпоративной работы с клиентами.

http://www.cnews.ru/newsline/index.shtml?2005/12/23/193707

Oracle снижает цены для многоядерных чипов

Oracle опять изменила структуру своих лицензий на базы данных, предоставив скидки заказчикам, которые покупают СУБД для серверов с новыми многоядерными процессорами от Advanced Micro Devices, Intel и Sun Microsystems.

http://zdnet.ru/?ID=504848

Аптечная автоматизация

ДЕБЮТ. Корпорация Oracle представила на российском рынке систему Oracle Retail, предназначенную для автоматизации крупных операторов розничной торговли. Она представляет собой единое решение на основе свежеприобретенных разработок Retek (планирование и управление продажами) и ProfitLogic (оптимизация розничной торговли), а также собственной СУБД и ERP-приложения.

http://www.finansmag.ru/23782

Управление таблицами

Типы данных Oracle

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

В Oracle существуют следующие скалярные типы данных:

• CHAR – символьная строка фиксированной длины. Может быть от 1 до 255 символов.
• DATE – позволяет сохранять год, месяц, день, час, минуты и секунды.
• LONG – символьная строка переменной длины размером до 2 Гб.
• LONG RAW – бинарные данные переменной длины размером до 2 Гб.
• NUMBER – число как с фиксированной, так и с плавающей запятой.
• ROWID – идентификатор любой строки не кластерных таблиц, состоящий из тройки значений: блок данных, строка, файл данных.
• VARCHAR2 – символьная строка переменной длины от 1 до 4000 символов. (Существует также синоним этого типа данных - VARCHAR , но Oracle не рекомендует его использовать, поскольку со временем планируется изменить его назначение).

Кроме скалярных имеются композитные (или составные) типы данных. Это:

• VARRAY – Позволяет хранить набор однотипных данных, идентифицируемых индексом. Иными словами, это массив данных.
• REF – ссылки на элементы других таблиц.
• Nested Tables – вложенные таблицы. В элементах этого типа хранятся ссылки на другие таблицы.
• LOB – ссылки на неструктурированные данные большого объема (изображения, видео, музыка и др.).

Параметры хранения таблиц

В предыдущем выпуске мы рассмотрели пример создания таблицы. В SQL-команде было использовано предложение STORAGE, которое определяет параметры хранения данных в таблице. Давайте рассмотрим эти параметры подробнее. Синтаксис STORAGE:

STORAGE
(
[ INITIAL число K или M ]
[ NEXT число K или M ]
[ MINEXTENTS число ]
[ MAXEXTENTS число или MAXEXTENTS UNLIMITED ]
[ PCTINCREASE число ]
[ FREELISTS число ]
[ FREELIST GROUPS число ]
[ OPTIMAL [ число K или M ] или [ NULL ] ]
)

Эти параметры определяют следующее:

• INITIAL число K или M – Этот параметр определяет исходный размер экстентов. Экстенты создаются сразу же при создании объекта схемы (т.е. таблицы). Размер задается в килобайтах (К) или в мегабайтах (М). По умолчанию создается экстенты размером в 5 блоков данных .
• NEXT число K или M – Этим параметром определяется размер экстентов, которые будут созданы позднее (при нехватке свободного места в сегменте). По умолчанию также берется размер 5 блоков данных . Можно указать в килобайтах (К) или в мегабайтах (М).
• MINEXTENTS число – Количество экстентов, которые должны быть созданы сразу. Каждый из этих экстентов будет размером INITIAL. По умолчанию создается один экстент (для сегментов отката – 2 экстента).
• MAXEXTENTS число – Максимальное количество экстентов для объекта схемы данных (включая первый экстент).
• MAXEXTENTS UNLIMITE – Этот параметр определяет возможность создания неограниченного количества экстентов для объекта схемы. Oracle не рекомендует использование этого параметра .
• PCTINCREASE число – Определяет процент увеличения размера экстентов, начиная с третьего. Если Вы зададите его равным 30, то размер создаваемого третьего экстента будет на 30% больше, чем второго. При этом размер будет округляться до целого числа блоков данных. Размер четвертого создаваемого экстента будет на 30% больше третьего и так далее. Если укажите 0, то все вновь создаваемые экстенты будут одного размера. По умолчанию – 50 (для сегментов отката – 0).
• FREELISTS число – Параметр не применим к табличным пространствам. Определяет количество списков свободных блоков в каждой группе списков свободных блоков для таблиц, индексов, секций и кластеров.
• FREELIST GROUPS число – Параметр не применим к табличным пространствам. Определяет количество групп списков свободных блоков .
• OPTIMAL число K или M - Параметр применим только к сегментам отката. Определяет оптимальный размер сегмента . Oracle будет стремиться держать размер сегмента близким к оптимальному .
• OPTIMAL NULL – При указании этого параметра Oracle никогда не будет освобождать неиспользуемые экстенты сегмента отката. Параметр используется по умолчанию.

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

Секционированные таблицы

Средства контроля над физическим расположением табличных данных сильно ограничены. Табличные пространства создаются посредством одного или более файлов данных. Затем экстенты создаются в файлах данных, но Вы не сможете указать, в каком файле данных надо создавать конкретные экстенты. Обычно, это не имеет значения, но может оказаться, что физический ввод-вывод будут несбалансированны.

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

• Секционирование по диапазонам ключей – Данные распределяются по секциям в зависимости от диапазона, в который попадает ключ. Это наиболее распространенный тип секционирования. В качестве ключа чаще всего используется дата. В этом случае данные будут секционированы по диапазонам дат.
• Секционирование по списку значений ключа – Данные распределяются в зависимости от значений ключа, для которого составлен дискретный список.
• Хэш-секционирование – Позволяет легко произвести секционирование на основе ключа, который не имеет определенной закономерности в своем распределении.
• Составное секционирование – Это совместное использование нескольких методов секционирование. Например, при секционировании по диапазонам ключей, можно каждую секцию дополнительно секционировать по списку значений ключа.

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

• Меньшие сегменты данных, меньшие сканирования таблиц – Так как данные могут быть разбиты на меньшие области, например, по месяцам, по годам и т.д., то можно избежать полного сканирования больших таблиц, просканировав только необходимую секцию.
• Меньшие индексы – Есть возможность создавать небольшие индексы отдельно для каждой секции. Тем самым ускоряется выборка данных.
• Более простое резервное копирование и восстановление – Так как резервное копирование секционированных таблиц можно осуществлять, опираясь на секции, то операция резервного копирования может быть разделена, а также запущена в параллельном режиме.

Рассмотрим синтаксис создания секционированной таблицы по диапазону ключей:

CREATE TABLE [схема.] имя_таблицы
(столбец тип_данных)
PARTITION BY RANGE (список_столбцов)
(PARTITION [имя_секции] VALUES LESS THAN значение
TABLESPACE имя_табличного_пространства
[, (PARTITION [имя_секции] VALUES LESS THAN значение
TABLESPACE имя_табличного_пространства])

Диапазон указывается предложением VALUES LESS THAN значение, а все строки со значением ключа меньше заданного будут сохранены в соответствующем табличном пространстве.

Приведу немного измененный пример из документации:

CREATE TABLE sales_range 
(salesman_id  NUMBER(5), 
salesman_name VARCHAR2(30), 
sales_amount  NUMBER(10), 
sales_date    DATE)
PARTITION BY RANGE(sales_date) 
(
PARTITION sales_jan2000 VALUES LESS THAN(TO_DATE('02/01/2000','DD/MM/YYYY'))
   TABLESPACE TS1,
PARTITION sales_feb2000 VALUES LESS THAN(TO_DATE('03/01/2000','DD/MM/YYYY'))
   TABLESPACE TS2,
PARTITION sales_mar2000 VALUES LESS THAN(TO_DATE('04/01/2000','DD/MM/YYYY'))
   TABLESPACE TS3,
PARTITION sales_apr2000 VALUES LESS THAN(TO_DATE('05/01/2000','DD/MM/YYYY'))
   TABLESPACE TS4
);

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

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

Изучаем SQL

Во-первых, разрешите продемонстрировать решение задачи из предыдущего выпуска:

SQL> SELECT country_name FROM countries;

Довольно простой запрос, в котором мы используем проекцию (т.е. выборку определенного столбца из таблицы).

Советы по написанию SQL-команд

1. SQL-команды регистронезависимые. Это означает, что Вы можете писать команды как заглавными, так и строчными буквами. Например, Вы можете написать даже так: sElEcT * fRoM cOuNtRiEs; И это будет с успехом выполнено! Так что пишите так, как удобно, но старайтесь все же не извращаться до такой степени. Я рекомендую ключевые слова писать заглавными буквами, а остальные (названия таблиц, столбцов и др.) строчными.
2. SQL-команды могут занимать несколько строк текста. Завершающим символом является точка с запятой (иногда слэш).
3. Ключевые слова нельзя переносить на новую строку посреди слова, также нельзя их сокращать.
4. Используйте отступы (табуляцию) для наглядности команд.

Арифметические выражения

Вы можете составлять запросы, используя арифметические выражения. В арифметических выражениях могут принимать участие наименования столбцов, константы, переменные и операторы. Используйте следующие операторы: + (сложение), - (вычитание), * (умножение), / (деление).

Арифметические выражения можно применять в любой части SQL -запросов, кроме предложения FROM. Рассмотрим пример:

SQL> SELECT last_name, salary, salary + 100
2 FROM employees;

LAST_NAME                     SALARY SALARY+100
------------------------- ---------- ----------
King                           24000      24100
Kochhar                        17000      17100
De Haan                        17000      17100
Hunold                          9000       9100
Ernst                           6000       6100
 .
 .
 .
Gietz                           8300       8400

107 rows selected.

Обратите внимание, что цифра 2 в примере запроса означает переход на вторую строку, это не часть запроса! SQL*Plus автоматически нумерует строки в длинных запросах.

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

SELECT last_name, salary, salary + 100 * 3
  FROM employees;

Так как скобки не используются, то сначала произойдет умножение 100 * 3, а затем к каждому значению столбца salary будет прибавлено полученное значение 300. Пример с использованием скобок:

SELECT last_name, 12 * (salary + 300)
  FROM employees;

Этим запросом будет выдано годовое жалование каждого сотрудника, завышенное на $300 за каждый месяц.

Значение NULL

Если значение столбца в строке таблицы не было определено, то в нем не находится ничего. Такие недостающие значения определяются словом NULL (произносится как "НАЛ"). Обратите внимание, что NULL – это НЕ ноль и НЕ пробел. NULL – это неопределенное, недоступное, неизвестное и неприменимое значение. Ноль и пробел – это определенные значения.

Столбцы любого типа данных могут содержать NULL. Однако такие ограничения целостности, как NOT NULL и PRIMARY KEY, позволяют избежать появления NULL. Но NULL часто необходим. Например, в таблице employees (сотрудники) только продавцы и торговые представители могут зарабатывать комиссионные. Все остальные сотрудники не имеют комиссионных, а значение NULL в столбце commission_pct отражает этот факт.

Если Вы будете использовать NULL в арифметических выражениях, то в результате будете также получать NULL. Например, salary*NULL=NULL, salary+NULL=NULL. Возможно и такое: salary/NULL=NULL (так как NULL – это не ноль).

Практика. Выведите таблицу, содержащую фамилии сотрудников и полугодовое жалование каждого из сотрудников.

Примечание: Описание схемы HR смотрите в 21-м выпуске рассылки. Ответ на задание будет дан в следующем выпуске.

Введение в PL/SQL

PL/SQL – это язык программирования, представляющий собой процедурное расширение (Procedural Language) языка SQL. PL/SQL является развитым языком программирования, используемым для доступа к базам данных Oracle из различных сред. PL/SQL интегрирован с сервером базы данных, поэтому программы PL/SQL обрабатываются быстро и эффективно.

Возможно, Вас испугало то, что Вам придется изучать новый язык? Но этот язык прост в сравнении с другими языками программирования. В основе его структуры лежат "блоки" с разными разделами, четко идентифицируемыми с помощью ключевых слов. Важнейшая характеристика этого языка – это его тесная интеграция с SQL.

Для того чтобы иметь возможность опробовать на практике дальнейшие примеры, Вам необходимо: иметь установленную СУБД Oracle (например, Oracle Database 10g XE), пробную схему данных HR. Проверьте, что Вы можете подключиться к этой схеме.

Запуск кода PL/SQL

Структуру программы, типы используемых переменных мы рассмотрим позже. Как правило, начинающему интересно создать и выполнить простейшую программу в изучаемом языке программирования. Средств для выполнения кода PL/SQL очень много.

Мы будем использовать SQL*Plus, выполняемый из командной строки (Вы также можете использовать SQL*Plus для Windows или iSQL*Plus). Для запуска SQL*Plus без подключения, введите в командной строке:

Если на экране Вы будете видеть "кракозяблики" (не та кодировка), то выполните до запуска sqlplus команду:

(Аварийный выход из SQL*Plus можно осуществлять клавишами <Ctrl>+<Break>)

После успешного запуска SQL*Plus , можно подключиться к БД:

Если Вы подключаетесь через сеть, тогда так:

Для просмотра результатов Вам требуется предварительно выполнить команду:

Введем самую маленькую программу:

SQL> BEGIN
 2    DBMS_OUTPUT.PUT_LINE('Hello world!');
 3   END;
 4    /

После выполнения на экране увидите:

Hello world!
PL/SQL procedure successfully completed.
SQL>

Для отключения от базы данных используется команда:

Выход из sqlplus осуществляется командой:

Параллельно с изучением PL/SQL нам придется изучать SQL*Plus . Может, в дальнейшем мы рассмотрим другие средства для работы с PL/SQL, но в первую очередь необходимо знание стандартных средств, так как другие программные продукты не всегда могут оказаться с собой или у Вас под рукой, хотя они, как правило, значительно облегчают труд программиста.

В большинстве случаев, Ваши программы сохранены в отдельном файле (сценарий). Для его запуска нужно воспользоваться командой:

или

где имя_сценария - есть полное имя и путь к файлу, если он не находится в текущем каталоге.

Строка комментария для sqlplus начинается с команды REM .

Настроек у sqlplus множество, для их просмотра воспользуйтесь командой:

Часто выходные данные требуется сохранять в файле, для этого используется команда SPOOL (команда SQL*Plus ):

SQL> SPOOL report.txt
SQL> @run_program
…
SQL> SPOOL OFF

Чтобы сильно Вас не утруждать, на этом ставлю точку.

Задания:

1. Сохраните текст первой программы в файле-сценарии и запустите его на выполнение в SQL*Plus.
2. Результаты выполнения первой программы сохраняйте в файле see.log .

Дополнительная литература

Если Вы заинтересовались PL/SQL и хотите изучить его опережающими темпами, то можете ознакомиться со списком литературы, которым я постоянно пользуюсь:

1. Oracle PL/SQL для профессионалов. С. Фейерштейн. Б.Прибыл 3-е издание изд. Питер. 2004 г .
2. Oracle 9i. Программирование на языке PL/SQL (Разработка эффективных приложений с помощью PL/SQL). Скотт Урман. Изд. Лори 2004 г .
3. Oracle 8. Язык программирования PL/SQL. Скотт Урман.
4. Документация Oracle 9i.
5. Introduction to Oracle 9i SQL (Student Guide) - Volume I, Volume II.
6. Introduction to Oracle 9i PL /SQL (Student Guide) - - Volume I, Volume II, Volume III.
7. Справочник по инструкциям и функциям SQL .

Автор рубрики: Каплич Сергей Григорьевич - skaplich1@tut.by

Что дальше?

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

Чем Вы можете помочь рассылке?

1. Пишите мне письма! Я обязательно прочитаю КАЖДОЕ из пришедших писем. И отвечу КАЖДОМУ читателю. Самые интересные письма будут опубликованы. Если Вы не хотите, чтобы Ваше письмо было опубликовано, то укажите об этом в письме. Я рассматриваю вопросы только по вопросам администрирования СУБД Oracle и по SQL. Просьба не присылать мне вопросы по PL/SQL.
2. Если Вы способны перевести одну-две страницы в неделю, то пишите мне письмо о своем желании участия в проекте перевода.
3. Если Вы хотите написать статью для этой рассылки, но не знаете о чем можно написать, то обращайтесь ко мне. Я пришлю список возможных тем. Это позволит Вам лучше понять рассматриваемый вопрос, оказать услугу другим читателям и познакомится с другими читателями (которые наверняка захотят прислать письмо автору по возникшим вопросам).

Я рад, что Вы подключились к этой рассылке, надеюсь на Ваше посильное участие.

Злыгостев А.А. aka Lemon – lemon@oranet.ru или zlygostev@gmail.com