Oracle плюс+

  Выпуск 3
  Базовые объекты

  Здравствуйте. В прошлом выпуске рассылки я рассказал как установить СУБД Oracle,
а также настроить клиента для работы с ним. Этот выпуск рассылки будет посвящен
работе с Oracle при помощи такой утилиты как SQL*Plus, а также созданию при помощи
данной утилиты базовых объектов СУБД Oracle.
  Сначала я хотел бы остановиться на том, что же из себя представляет SQL*Plus
(кстати, то как я пишу название - не моя личная прихоть, подобным образом данная
утилита называется и во всех официальных руководствах Oracle). Думаю некоторые
из Вас начинали работать еще на 80286 (а может даже и на 8086 :), а может и сейчас
работаете /:( ), или имеют некоторый опыт работы под OS Linux, а может Вас по
каким то причинам не устраивает программирование под этими тормозными IDE (Interface
Development Environment - среда разработки); как бы то ни было, в любом из вышеприведенных
случаев Вам приходилось иметь дело с командной строкой.
  Так вот, SQL*Plus - та же командная строка, только под Oracle. Данное приложение
и является тем самым минимумом, который необходим Вам для того чтобы начать свое
знакомство с возможностями Oracle.
  По-моему глубокому заблуждению основными достоинствами(+) SQL*Plus являются:
    1) Минимальные требования к аппаратным и программным ресурсам. Собственно
говоря, кроме него на машине должен быть установлен еще только Net8 (не вдаваясь
сейчас в подробности скажу, что это набор драйверов для связи с Oracle). Минимальная
конфигурация машины при этом (официально заявлено под Oracle9i: ...)
    2) Максимальные функциональные возможности: после соединения с Oracle посредством
SQL*Plus Вы можете делать все что захотите: начиная от простой выборки данных
из таблиц и заканчивая выполнением, каких либо административных функций; главное
при этом, чтобы у пользователя СУБД Oracle под которым Вы законектились хватало
прав на те действия, которые Вы выполняете.
  (!) Если прав недостаточно, то в случае выполнения каких либо административных
функций или операторов определения данных (например CREATE TABLE который мы рассмотрим
ниже) Oracle выдаст Вам об этом соответствующее сообщение. В случае же выполнения
операций языка манипулирования данными (например оператора SELECT который мы
также рассмотрим ниже) Oracle может и не сказать что у Вас недостаточно прав,
а просто сообщит, что объекта, над которым Вы хотите провести операцию не существует
(это на заметку будущим DBA).
    3) Простота настройки: что нам нужно для работы с SQL*Plus, так это:
      а) сконфигурированный Net8 Connection;
      б) имя пользователя и пароль для соединения с базой данных.
  Правда, чтобы быть объективным хочу сказать, что большинству продуктов Oracle
требуется ровно то же самое, только иногда приходится немного поискать где именно
это нужно указывать, в SQL*Plus Вы же задаете данные параметры при входе.
  Четвертым достоинством, равно как и недостатком, является отсутствие какого
то бы ни было интерфейса. Все приходится делать руками (это же командная строка).
Но здесь уже "каждый выбирает для себя..."
  Но теперь о том, как присоединится: из меню "Пуск-..." в группе программ Oracle
выбираем SQL*Plus. После начальной загрузки появляется окошко с тремя полями:
    - имя пользователя;
    - пароль;
    - "псевдоним" соединения.
  "Имя пользователя" и "пароль" - это то что напишет вам на бумажке DBA (DataBase
Administrator - администратор базы данных). Если вы сами DBA и еще не знаете
как создавать пользователей в базе данных, то попробуйте такие вот сочетания
имени/пароля:
    - scott/tiger (прав данного пользователя вполне хватает для учебных целей);
    - system/manager (это пароль и имя пользователя с правами DBA, коннектится
под ним, чтобы создать пару таблиц не советую, по умолчанию данные таблицу будут
создаваться в табличном пространстве SYSTEM - до этого мы еще с Вами дойдем,
а пока запомните, что это нехорошо);
    - есть еще такой пользователь как sys/change_on_install - это БОГ базы данных;
под ним работать не советую вообще либо только в исключительных случаях.
  (!) Не забудьте для двух последних пользователей после инсталляции сменить
пароль (да и в официальной документации тоже так советуют).
  Перейдем теперь к третьему параметру. Собственно говоря это то, что мы указывали
как "псевдоним" соединения, когда в предыдущем выпуске его настраивали. Набираем
его там и нажимаем на "Ok". Должно появиться что-то типа:

    SQL*Plus: Release 8.1.6.0.0 - Production on Пнд Сен 9 11:26:27 2002
    (c) Copyright 1999 Oracle Corporation.  All rights reserved.
    Присоединен к:
    Oracle8i Enterprise Edition Release 8.1.6.0.0 - Production
    With the Partitioning option
    JServer Release 8.1.6.0.0 - Production
    SQL>

  (!) Иногда соединиться с использованием "псевдонима" не удается. В этом случае
попробуете указать GDN (Global Database Name).
  (!) Автор считает необходимым также отметить следующий момент: версии SQL*Plus:
Release 8.1.6.0.0, Oracle8i Enterprise Edition Release 8.1.6.0.0 и JServer Release
8.1.6.0.0 должны совпадать. Если они не совпадают, то это конечно не будет означать
что Ваш сервер тут же перестанет работать, однако это может стать потенциальным
источником проблем.
  Настало время перейти к самому интересному: создание объектов базы данных.
Сегодня мы с Вами попробуем создать таблицу (table), представление (view), получающее
данные из этой таблицы и последовательность (sequence).
  Таблица - основа хранения и обработки данных в любой реляционной(!) базе данных.
Логически она представляет собой упорядоченный двумерный массив, строки которой
содержать экземпляры какой либо сущности, а столбцы - ее атрибуты. Отмечу также,
что физически данные таблицы могут и не располагаться последовательно на диске,
что в общем то снижает производительность при выполнении запросов к базе данных.
  Итак первый пример:
  Пример 1:
    CREATE TABLE dclass (
    classid VARCHAR2(2),
    classname VARCHAR2(255) NOT NULL);

  Набираем пример, нажимаем на Enter и... Oracle должен выдать что нибудь вроде:
"Table created". Этот пример создания таблицы - самый простой.
  Для более тонкой настройки и оптимизации работы корпорация Oracle предусмотрела
указание ряда дополнительных параметров, но в приведенном выше примере СУБД сама
определит их за Вас (это такие параметры как TABLESPACE (определяет табличное
пространство размещения объекта, если не указано - то табличное пространство
по умолчанию для данного пользователя), STORAGE (предложение, определяющее параметры
хранения для данной таблицы базы данных)).
  Итак, таблица создана. Однако, как мы отметили выше, таблица - основа для хранения
данных. Но для того чтобы что то в чем то хранить, нужно это сначала туда поместить.
Для добавления данных в таблицу в языке SQL используется предложение INSERT.
На практике, вышеприведенная таблица использовалась для хранения классов товаров
по ОКП (Общероссийский Классификатор Продукции). Мы будем в ней хранить практически
то же самое - группы товаров, а также их коды (а вообще то каждый может хранить
ту информацию, которую захочет).
  Пример 2:
    INSERT INTO dclass VALUES ('00', 'Прочие товары');
    INSERT INTO dclass VALUES ('00', 'Бумага писчая формата А4');
    INSERT INTO dclass VALUES ('00', 'Клей канцелярский');

  Остальное если хотите придумайте сами, общий принцип я думаю Вы уже поняли.
  Удалить только что созданную нами таблицу можно следующей командой:
    DROP TABLE dclass;

  Перед разработчиками клиентов баз данных (так называемого front-end) очень
часто встает вопрос: "Как ограничить пользователю приложения доступ к данным,
к которым он в силу своего служебного положения или других обстоятельств доступа
иметь не должен?". Очень часто это решается в реализации самого клиента. На уровне
же базы данных такая проблема может быть решена с использованием видов (view).
  Представление (view) - это выборка одной или нескольких строк из одной или
нескольких таблиц. Представление по своей сути представляет собой структуру,
которая ссылается на данные, фактически хранящиеся в таблицах. С точки зрения
проектирования, представление представляет собой хранимый SQL запрос (с логикой
соединения и фильтрации), из которого можно выбирать данные, так если бы это
была просто таблица.
  Кроме вышеуказанной проблемы разграничения доступа представление может быть
использовано также для достижения следующих целей:
    - пользователи не хотят писать сложных запросов с соединением двух или нескольких
таблиц; поэтому можно вложить логику соединения двух таблиц в представление и
разрешить пользователям обращаться к данным так, как если бы это была одна таблица;
    - встречаются таблицы, в которых имена столбцов могут быть непонятны для
некоторых пользователей; можно создать представление, где эти столбцы будут переименованы
и получат осмысленные имена;
    - может возникнуть необходимость обращаться к более простым структурам чем
таблицы; например, таблица может иметь сотни столбцов; в этом случае проще создать
представление, а затем предоставить пользователям доступ к более простому представлению
[1].
  Теперь перейдем к практике. Попробуем создать простейший вид, который бы выбирал
все данные из ранее созданной таблицы:
  Пример 3:
    CREATE VIEW vdclass (
    classid, classname) AS
    SELECT dclass.classid, dclass.classname
    FROM dclass;

  На эту команду Oracle скажет что нибудь типа "View created".
  Данный вид выбирает все данные из таблицы dclass, если же вас к примеру интересуют
только названия классов товаров, необходимо модифицировать предложение SELECT,
убрав из него наименование поля кода класса товара.
  Удалить наш вид можно введя команду:
    DROP VIEW vdclass;

  (!) Если Вы отлаживает скрипты по созданию объектов серверной части базы данных
бывает удобно писать следующим образом:
    DROP TABLE ...
    CREATE TABLE ...

  То есть за удалением сразу же следует создание объекта с новыми параметрами.
Это касается не только таблиц, но и всех остальных объектов базы данных, которые
Вы планируете создать. Однако следует помнить, что зависимые объекты должны создаваться
позже тех объектов, от которых они зависят, а удаляться раньше (например, вид
должен быть создан после той таблицы, на основе которой строится его выборка,
но удален раньше ее).
  (!) Читатель может быть отметил, что в создании вида при построении выборки
автор использовал запись вида: имя_таблицы.имя_столбца. Это так называемая уточненная
форма записи. Она позволяет однозначно идентифицировать таблицу, из которой мы
выбираем столбец, если, например, в выборке участвуют две таблицы с одинаковыми
названиями столбцов (это может быть, например, при построении отношений master-detail
(главный-подчиненный)). В вышеприведенном примере такая форма записи не обязательна,
однако если база данных достаточно сложна, она может потребоваться.
  Существует также так называемая уточненная форма записи имени объекта. Она
позволяет однозначно идентифицировать объект в любой базе данных Oracle (если
мы, например, проектируем распределенную базу данных).
  Последним объектом базы данных, который мы сегодня рассмотрим будет последовательность
(sequence). Очень часто перед разработчиками баз данных встает следующая задача:
сгенерировать уникальную последовательность значений (например, для выработки
значения первичного ключа в таблице). Для решения данной задачи корпорация Oracle
предлагает использовать такой объект базы данных как последовательность (sequence).
Рассмотрим на примере создание объекта последовательности:
  Пример 4:
    CREATE SEQUENCE seq_dclass
    INCREMENT BY 1
    START WITH 1
    MAXVALUE 99
    NOCYCLE;

  На эту команду Oracle должен сказать "Sequence created". Рассмотрим также как
можно использовать последовательность при заполнении таблицы данными:
  Пример 5:
    INSERT INTO dclass VALUES (seq_dclass.nextval, 'Ручка шариковая');

  Метод seq_dclass.nextval генерирует следующее значение последовательности и
подставляет его на место вставляемых данных. Кроме того каждая последовательность
имеет также метод currval, позволяющий получить текущее значение последовательности.
  Удалить созданную последовательность можно командой:
    DROP SEQENCE seq_dclass;

  (!) Нельзя выполнить откат своего обращения к последовательности. Другими словами,
если вставку записи в таблицу Вы выполняете в диалоговом режиме и после выдачи
уникального значения последовательности пользователь все еще может отменить вставку
записи, то нельзя будет снова обратиться к последовательности за тем же числом.
  (!) Если автор не ошибается, то в предложениях SELECT нельзя использовать условие
вида:
    ... any_column = any_seqence.currval ...
  Связано это кажется с тем, что any_seqence.currval не является реальным значением
(как например числа 3, 7 или строка 'qqq'), а лишь ссылкой на это значение.
  В заключении небольшой блок справочной информации и анонс на следующий выпуск.
  Синтаксис создания реляционной(!) таблицы:
    CREATE TABLE таблица (<реляционные_свойства>);

  Где реляционные свойства (читай описание параметров столбца таблицы) можно
записать в следующем виде:
    имя_столбца тип_данных [DEFAULT выражение] [ограничения_столбца]

  Синтаксис создания вида:
    CREATE VIEW представление (<столбцы_представления>) AS
    SELECT <столбцы_данных>
    FROM источники_данных;

  Синтаксис создания последовательности:
    CREATE SEQUENCE название_последовательности
    INCREMENT BY интервал_приращения
    START WITH начальное_значение
    MAXVALUE конечное значение
    [CYCLE] или [NOCYCLE] (циклическая или нециклическая);

  Хочу отметить, что здесь дан лишь очень упрощенный синтаксис создания объектов
базы данных. Описание полного синтаксиса создания таблицы например занимает 10
листов текста (формат 84х106/16).
  В следующем выпуске автор планирует рассказать о системе безопасности, предоставляемой
Oracle а также поднять проблему выполнения административных функций с использованием
Oracle DBA Studio. Кратко также будут рассмотрены возможности применения для
аналогичных целей командной строки.
  Использованные источники:
    1) Грин Джо и др. Oracle 8/8i Server. Энциклопедия пользователя: Пер. с англ./Джо
Грин и др. - К.: Издательство "ДиаСофт", 2000. - 576с. ISBN 966-7393-44-5

  Sincerely yours
    Grayrat
    grayrat@rseu.ru