Инструменты для работы с данными и демонстрационный пример

Инструмент для общения с базой данных

Фирма Oracle предоставляет два основных инструмента для общения с БД в диалоге посредством SQL: SQL*Plus и SQL Developer. Дальнейшие примеры в тексте, как правило, предполагаются для исполнения в SQL*Plus, однако с разной степенью корректировки исполняемы и в SQL Developer.

SQL*Plus — программа из обычного комплекта ПО Oracle для диалогового общения с БД путем ввода пользователем (или, возможно, из сценарного файла-"скрипта") текстов на SQL и PL/SQL и предъявления на экране компьютера результата, полученного от СУБД:

Запуск SQL*Plus может осуществляться:

Пример запуска из командной строки:

SQL*Plus обрабатывает тексты на трех языках: SQL, PL/SQL и собственном. Во всех случаях регистр набора не имеет значения. Точнее, можно полагать, что, приняв на входе команду на любом из трех языков, "система" (выполняет ли эту работу SQL*Plus или СУБД, в данном случае неважно) повышает регистр всем буквам, кроме закавыченных символами "'" и """, а потом уж производит обработку. Следующие две команды SQL содержательно равносильны:

SELECT * FROM emp;
select *   FRoM Emp ;

Исключение из правила автоматического повышения регистра перед обработкой команды касается значения пароля в версиях, начиная с 11. С этой версии выдача следующих двух команд SQL приведет к установке разных значений пароля пользователю SCOTT:

ALTER USER scott IDENTIFIED BY tiger;
ALTER USER scott IDENTIFIED BY Tiger;

Символ ";" используется в качестве признака окончания ввода команды SQL (они могут быть многострочными), но в некоторых случаях для этого используется / в первой позиции новой строки. Символ "-" используется как перенос продолжения набираемой команды SQL*Plus на новую строку (если эта команда чересчур длинна).

Собственные команды SQL*Plus служат для настроек работы этой программы, установления форматов и выполнения некоторых действий. Их несколько десятков, и полный перечень (в жизни избыточный) приведен в документации по Oracle.

Примеры:

DESCRIBE emp

SET LINESIZE 200
SET PAGESIZE 50>

COLUMN object_type FORMAT A20

CONNECT scott/tiger

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

DESC emp
SET LINES 200
COL object_type FOR A20
CONN scott/tiger

Для обработки вводимых команд SQL (а заодно блоков на PL/SQL) в SQL*Plus применяется внутренний буфер команды. Он обновляется при каждом новом наборе текста на SQL (или блока на PL/SQL). Команда SQL*Plus LIST позволяет выдать на экран текущее содержимое буфера, команда RUN или же символ "/" — запустить содержимое на исполнение, а команда EDIT — редактировать.

SQL Developer тоже позволяет пользователю обращаться к БД на SQL, но имеет графический интерфейс и графические средства отладки. Для SQL Developer фирмой Oracle и третьими фирмами разработан ряд расширений: для администрирования картографической информации в БД (Oracle Spatial), для графического администрирования средств анализа данных (Oracle Data Mining) и др.

С целью моделирования БД графическим образом можно использовать родственный продукт Oracle SQL Developer Data Modeler.

) и установить. Создать соединение со схемой SCOTT, открыть его. Посмотреть перечень и свойства таблиц в схеме SCOTT. Выдать запросы к данным таблиц.

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

Данные для дальнейших примеров

Таблицы

В дальнейших примерах чаще всего будут использоваться две таблицы, хранимые в "схеме данных" с именем SCOTT: это EMP и DEPT.

В Oracle и большинстве других подобных систем схемой данных называется специально оформляемое подмножество хранимых объектов, с которыми конкретные прикладные программы имеют право работать в данный момент. Понятие схемы данных задумано ради удобства работы с содержимым БД и защиты доступа. Объектами схемы в Oracle могут быть не только таблицы, но и прочие элементы, такие как индексы, процедуры и другие (в том числе так называемые "ограничения целостности"). Слово "схема" иногда употребляется и применительно к таблице. Под "схемой таблицы" понимают ее структуру вместе с некоторыми элементами описания.

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

Схема с именем SCOTT поставляется в Oracle с давних времен для демонстрационных целей.

Общее описание таблиц:

Данные в таблицах:

Таблица EMP имеет (первичный) ключ: столбец EMPNO; ключ в таблице DEPT — столбец DEPTNO. В таблице EMP столбец DEPTNO образует "внешний ключ", ссылающийся на значения из одноименного столбца таблицы DEPT:

Совпадение имен столбцов внешнего ключа и столбцов адресатов в SQL не обязательно, но в жизни приветствуется.

Таблица DUAL не принадлежит схеме SCOTT, а является "системной" (принадлежит схеме SYS) и доступна для выборки любому пользователю. Состоит из единственной строки и единственного столбца:

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

Такой характер ее употребления сложился не сразу. Когда она создавалась в старые времена, то имела две, а не одну, как нынче, строки (отсюда название DUAL) и служила для "удвоения" строк системной таблицы выполнением соединения (join) в запросе о распределении памяти на диске в табличном пространстве БД. Впоследствии эта ее задача оказалась неактуальной, и она приобрела нынешнее значение.

В схеме SCOTT присутствует еще пара таблиц, BONUS и SALGRADE, гораздо менее интересных и далее почти не востребованых.

Упражнение. Выясните в SQL*Plus или SQL Developer структуру и содержимое таблиц BONUS и SALGRADE в схеме SCOTT.

Со временем фирма Oracle разработала более правдоподобно и сложно устроенный демонстрационный пример, нежели схема SCOTT, на этот раз из нескольких схем: HR, OE, PM, SH, IX, BI. С ним часто можно столкнуться в нынешних примерах из разных источников. Далее в тексте предпочтение отдано все-таки схеме SCOTT: она небольшого размера, и ее обычно достаточно для решаемых задач.

Пользователи и полномочия

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

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

В БД Oracle самостоятельным является понятие пользователя, а понятие схемы подчинено ему. Так, по команде CREATE USER СУБД создаст в БД пользователя и одновременно автоматически — схему с тем же именем, к которой будет относить все объекты "этого пользователя", и только их. Схема будет удалена из БД также автоматически при удалении пользователя.

Большинство объектов БД, например, таблицы, индексы, процедуры вида SYNONYM, обязаны находиться в одной и ровно одной схеме ("принадлежать одному пользователю"). Однако небольшая часть объектов, например, вида PUBLIC SYNONYM или DIRECTORY, хранятся в БД вне всякой схемы и "не принадлежат никакому конкретному пользователю".

Демонстрационный пользователь SCOTT изначально обладает некоторым набором полномочий, например, создания таблиц и подключения к СУБД. В то же время ему изначально не приданы, скажем, полномочия создавать представления данных или же синонимы. Придать ему эти полномочия способны, например, пользователи SYS и SYSTEM. Эти пользователи могут быть названы "администраторами"; они имеются в любой БД Oracle, и при этом SYS может быть неформально назван "суперпользователем" или "главным администратором", а SYSTEM несколько ограничен в правах по сравнению с SYS.

Полномочия (= привилегии) выдаются командой GRANT и изымаются командой REVOKE.

Пример. Подсоединение в SQL*Plus в качестве SYS, простое создание пользователя YARD вместе со своей схемой и с паролем pass, наделение пользователя YARD некоторыми общими привилегиями:

CONNECT / AS SYSDBA
CREATE USER yard IDENTIFIED BY pass;
GRANT connect, resource, create view TO yard;
Подключение к СУБД под именем YARD и попытка обратиться к таблице EMP:
SQL> CONNECT yard/pass
Connected.
SQL> DESCRIBE emp
ERROR:
ORA-04043: object emp does not exist

Ошибка возникла потому, что пользователь YARD (как и любой другой) работает в собственной схеме БД, а в схеме YARD таблицы EMP нет — она имеется в схеме SCOTT. Разрешить же пользователю YARD обращаться к данным таблицы EMP в схеме SCOTT можно специально выданной командой GRANT, например:

CONNECT scott/tiger
GRANT SELECT ON emp TO yard;

В Oracle понятие "пользователь" естественно связывать не с физическим лицом, обращающимся к БД, а с приложением.