Погодаев А.К., Дозоров В.В. Программное обеспечение автоматизированных систем. Разработка информационной структуры: Часть 1

Подождите немного. Документ загружается.

Команда Описание

PAUSE Выводит пустую строку и строку с указанным

текстом, затем ожидает нажатие пользователем

[Return]. Или, выводит две пустые строки и

ожидает [Return].

PROMPT Посылает указанное сообщение или пустую

строку на экран.

RUN Выводит и выполняет текущую команду SQL

или блок PL/SQL из буфера SQL.

SAVE Сохраняет содержимое буфера в файле ОС.

SET Настраивает окружение SQL*PLUS для текущего

сеанса.

SHOW Выводит значения системных переменных

SQL*PLUS.

SQLPLUS Запускает SQL*PLUS из ОС.

START Выполняет указанный командный файл.

2.3. Общие сведения о SQL

SQL - это множество операторов, которые должен применять

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

Oracle. Прикладные программы и сервисные средства часто позволяют

обращаться к данным, минуя SQL, но сами эти средства

обрабатывают пользовательский запрос с помощью SQL - операторов.

Структурированный язык запросов (Structured Query Language –

SQL) появился в системе System R фирмы IBM в середине

семидесятых годов. В 1979 году Oracle Corporatuon представила

первую коммерческую версию SQL. Язык SQL также был применен в

системах DB2 и SQL/DS корпорации IBM. SQL давно стал

промышленным стандартом, но его развитие продолжается.

Американский национальный институт стандартов ANSI (American

National Standards Institute) объявил SQL стандартным языком для

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

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

базами данных, поддерживают SQL и придерживаются стандарта

ANSI SQL89. Хотя многие разработчики, в том числе и Oracle,

объявили о соответствии своих версий SQL спецификации ANSI

SQL92.

Мощность SQL удовлетворяет все категории пользователей,

включая прикладных программистов, администраторов баз данных,

конечных пользователей.

SQL - это непроцедурный язык благодаря следующим

свойствам:

- обрабатывает одновременно не одну, а множество записей;

- обеспечивает автоматическое управление доступом к данным.

SQL позволяет работать со структурами данных высокого

уровня. Вместо манипуляции отдельными записями можно управлять

наборами записей. Наиболее общая форма набора записей - таблица.

Все SQL - операторы имеют на входе набор записей и на выходе также

генерируют множество записей. Это свойство SQL - операторов

позволяет выход одного оператора использовать на входе другого.

SQL не требует спецификации метода доступа к данным. Это

свойство дает возможность сосредоточиться на получении

необходимого результата. Все SQL - операторы используют

оптимизатор запросов - часть RDBMS, определяющую наиболее

быстрые пути доступа к указанным данным. Оптимизатор запросов

знает обо всех существующих индексах и при необходимости их

использует; во время обращения к таблице нет необходимости знать,

имеет ли таблица индексы и какие они.

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

включая:

- запросы данных;

- добавление, изменение и удаление строк из таблицы;

- создание, модификацию и удаление объектов базы данных;

- управление доступом к базе данных и объектам базы;

- гарантирование согласованности данных.

Ранние системы управления базами данных используют для

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

унифицирует все эти задачи в одном согласованном языке.

2.3.1. Элементы языка SQL

Объекты базы данных - это элементы, поддерживаемые

Oracle RDBMS. Большинство объектов занимает память в базе

данных; некоторые объекты (например - табличные пространства)

используются для хранения других объектов базы данных. Таблицы -

это единственный объект, который хранит данные пользователя и к

которому он может обращаться непосредственно.

Объекты базы данных при создании получают имена. Все

объекты базы данных имеют создателя и владельца; обычно

создатель и владелец объекта является одним и тем же лицом.

Именование объектов базы данных подчиняется следующим

правилам:

1.Имя объекта базы данных должно быть длиной от 1 до 30

символов.

2.Имя не должно содержать кавычек.

3.Имя не является регистрочувствительным

4.Имя должно начинаться с буквы.

5.Имя может содержать только символы A-Z, 0-9, _,$ и #.

6.Имя не должно дублировать зарезервированные слова.

7.Имя не должно дублировать имя какого-либо иного объекта

базы данных, владельцем которого является тот же

пользователь.

8.Некоторые объекты (индексы, последовательности, таблицы

и обзоры) базы данных могут в дальнейшем определяться

вместе с именем пользователя - владельца данного объекта.

Например, кроме имени EMP можно использовать имя

USER.EMP.

Алиасы таблиц и столбцов, хотя и не являются объектами базы,

должны следовать указанным правилам.

Термины: literal, constant, value (литерал, константа, значение) в

Oracle имеют одинаковое значение; все представляют определенную

часть информации. Например - 'JACK', 'BLUE ISLAND', '101' -

символьные литералы; '01-JAN-89' – литерал типа дата; 5001 -

цифровой литерал. Заметьте, что символьные и датные литералы

заключены в одиночные кавычки. Кавычки позволяют Oracle

отличать имена объектов базы данных от символьных и датных

литералов.

Переменные в системе Oracle служат местом для помещения

литералов. Переменные Oracle работают аналогично переменным в

языках программирования. В любой данный момент времени

переменная может быть либо пустой, либо хранить литерал.

Переменные в основном используются во вложенном SQL.

Каждый литерал или переменная, с которыми работает система

Oracle, имеет определенный тип данных. Тип данных ассоциируется с

фиксированным набором свойств, присущих его значениям. Этот

набор свойств позволяет Oracle различать типы данных. Например -

сложение может выполняться с данными типа NUMBER, но не с

данными RAW.

Типы данных также ограничивают диапазон значений,

которые может принимать элемент данных. Например - тип данных

DATE не может принимать значение 29 февраля, если год не

високосный. Этот же тип данных не может принимать значение 2

или 'LITERA'.

Таблица 4

Основные типы данных Oracle

Тип данных Описание

NUMBER (p,s) Числовые данные переменной длины. Точность p

(общее количество цифр) может задаваться от 1

до 38. Масштаб s (число цифр после десятичной

точки) может быть от –84 до 127

NUMBER (p) Число с фиксированной точкой с нулевым

масштабом и точностью p

NUMBER Число с плавающей точкой с точностью 38

DATE Значение даты и времени фиксированной длины

CHAR (размер) Символьные данные фиксированной длины.

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

VARCHAR2

(размер)

Символьные данные переменной длины.

Максимальный размер – 2000 символов

LONG Символьные данные переменной длины.

Максимальный размер – до 2 Гбайт.

RAW Двоичные данные переменной длины.

Максимальный размер – 2000 символов

LONGRAW Двоичные данные переменной длины.

Максимальный размер – до 2 Гбайт.

Значение в базе данных предполагается того же типа, что и

тип столбца, которому оно принадлежит. Каждому столбцу таблицы

назначается определенный тип данных, а, следовательно - и каждому

значению, принадлежащему данному столбцу. Oracle назначает тип

данных небазовым значениям исходя из контекста их использования.

Например, если вставить '01-JAN-89' в столбец с типом DATE,

Oracle после проверки на правильность будет трактовать символьную

строку '01-JAN-89' как значение типа DATE.

В общем случае в выражении не должно быть смешения

типов данных. Например - выражение не может содержать

произведения 5 на 10, и затем сложения с ним 'JAMES'. Однако Oracle

будет выполнять преобразование одного типа данных в другой, если

это имеет смысл. Например, литеральная строка '10' имеет тип CHAR,

Oracle преобразует ее в NUMBER, если она используется в числовом

выражении.

Выражения (булевы, арифметические или строчные) могут

использовать при сравнении данные различных типов. В этих

случаях для выполнения оператора Oracle обязательно использует

преобразование данных. Для выполнения преобразования Oracle

может:

- преобразовать константу в тип данных столбца;

- преобразовать значение столбца к типу данных константы;

- преобразовать тип данных столбца к типу данных другого

столбца.

Преобразования данных являются контекстно-зависимыми,

поэтому нельзя ожидать, что во всех случаях будет выполняться тот

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

автоматические преобразования, необходимо указывать явные

преобразования, обеспечиваемые функциями SQL. Это диктуется

следующими соображениями:

- если применять функции явного преобразования, SQL -

операторы становятся проще для понимания;

- автоматическое преобразование данных может негативно

влиять на производительность, особенно если столбец

преобразовывается к типу данных константы;

- алгоритмы неявного преобразования могут меняться от версии к

версии и от одного продукта Oracle к другому и,

следовательно, поведение явных преобразований более

предсказуемо.

- Если в определенном столбце строки данные отсутствуют,

говорят что его значение null. Столбец с данными любого типа

может содержать значение null, если только он специально не

описан как NOT NULL в момент создания. Значение null

обладает следующими свойствами:

- присваивается, когда действительное значение неизвестно или

бессмысленно;

- не эквивалентно значению нуль;

- будет превращать в null любое выражение. Например, NULL

умноженное на 10 дает NULL.

Для временного преобразования null в действительное значение

используется функция NVL. Например, NVL(COLUMN,0) возвращает

0, если значение в столбце COLUMN - null и действительное

значение, если оно не null.

Напрямую со значениями null должны работать только

операции сравнения (IS NULL или IS NOT NULL). Если со значением

null используется любой другой оператор, результат всегда

неизвестный. Так как NULL представляет собой отсутствие данных,

он не может быть равен или не равен любому другому значению,

кроме null.

2.3.2. Основные операторы SQL

SQL-операторы делятся на следующие три типа:

DDL - Data Defenition Language (Язык Определения Данных):

эти операторы действуют на структуру объектов базы данных и

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

DDL – оператора Oracle неявно выполняет операцию COMMIT

(фиксацию всех изменений).

DML - Data Manipulation Language (Язык Манипуляции

Данными): выполняют различные действия над данными.

EMB - Embedded SQL (Встроенный SQL): операторы,

используемые для помещения DDL и DML - операторов внутрь

программы на процедурном языке.

Таблица 5

Основные операторы SQL

Описание Тип Действие

/* ... */ Помещает комментарий внутри или

между SQL - операторами.

ALTER INDEX DDL Переопределяет будущее распределение

памяти индекса

ALTER

SEQUENCE

DDL Переопределяет генерацию

последовательных номеров.

ALTER TABLE DDL Добавляет, переопределяет столбцы к

таблице.

Описание Тип Действие

ALTER USER DDL Меняет пароль пользователя,

умалчиваемое табличное

пространство и умалчиваемое

временное табличное пространство.

COMMIT DML Делает изменения, проведенные с

начала транзакции, постоянными.

CONNECT EMB Присоединяет (подключает)

пользователя к базе данных.

CREATE INDEX DDL Создает индекс для таблицы.

CREATE

SEQUENCE

DDL Создает последовательность пригодную

для генерирования основных ключей.

CREATE

SYNONYM

DDL Создает синоним для таблицы или

обзора.

CREATE TABLE DDL Создает таблицу и определяет ее

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

CREATE VIEW DDL Определяет обзор на одной или

нескольких таблицах или других

обзорах.

DELETE DML Удаляет все или выбранные строки из

таблицы.

DROP object DDL Удаляет индекс, последовательность,

синоним, таблицу или обзор из базы

данных.

GRANT DDL Создает новых пользователей базы

данных, назначает пароли и

предоставляет пользователям

привилегии системы Oracle.

Предоставляет пользователям

привилегии на таблицы, а также

привилегию использования ресурсов

табличного пространства.

INSERT DML Добавляет к таблице или обзору новые

строки.

LOCK TABLE DML Блокирует таблицу, временно

ограничивая к ней доступ другим

пользователям.

PREPARE EMB Выполняет разбор SQL - оператора.

RENAME DDL Изменяет имена таблиц, обзоров или

синонимов.

Описание Тип Действие

REVOKE DDL Отменяет привилегии по базе данных

или пользовательские привилегии по

доступу к таблицам.

ROLLBACK DML Откатывает все проведенные изменения

к точке сохранения или к началу

транзакции.

SAVEPOINT DML Устанавливает контрольную точку, к

которой впоследствии можно будет

выполнить откат.

SELECT DML Выполняет запрос; выбирает строки и

столбцы из одной или нескольких

таблиц.

UPDATE DML Изменяет данные в таблице.

VALIDATE

INDEX

DDL Проверяет целостность индекса.

2.3.3. Работа с таблицами

Таблицы – основные единицы хранения данных в реляционной

базе данных.

Создание таблиц

В общем виде команда создания таблицы выглядит так:

CREATE TABLE <имя_таблицы>(

<имя_столбца1> тип_данных1,

…

<имя_столбцаN> тип_данныхN);

Необходимо выбрать имя, ширину и тип данных для каждого

столбца в таблице. Столбцы должны быть перечислены в скобках

после имени таблицы. Информация о последовательных столбцах

должна быть разделена запятыми. Имена столбцов могут содержать до

30 символов. Для конкретной таблицы каждое имя столбца является

уникальным. Необходимо использовать спецификацию NOT NULL,

если существенны данные для определенного столбца. Тогда, если

будет предпринята попытка ввести запись без заполнения значения для

этого столбца, система Oracle возвратит сообщение об ошибке.

Просмотр структуры таблицы

Для просмотра спецификации столбцов таблицы можно

применить команду SQL DESCRIBE в виде:

DESCRIBE <имя_таблицы>;

После применения этой команды можно получить перечисление

имен, типов и размеров столбцов.

Модификация таблиц

После создания таблиц иногда возникает необходимость

модифицировать ее. Это можно осуществить с помощью команды

ALTER TABLE.

Добавление столбца обеспечивается командой:

ALTER TABLE <имя_таблицы>

ADD имя_столбца тип_данных;

Теперь все записи (т. е. строки), существующие в таблице,

будут расширены для включения этого дополнительного столбца. В

этот момент значения нового столбца в каждой строке будет NULL.

После добавления столбца, он будет располагаться справа от

имеющихся столбцов. Сначала все поля в новом столбце будут

содержать NULL. Добавляя новый столбец, нельзя выбрать NOT

NULL, если таблица уже содержит строки.

В любой момент можно изменить существующую базовую

таблицу, увеличив размер столбца или заменив в нем число

десятичных позиций:

ALTER TABLE <имя_таблицы>

MODIFY <имя_столбца> (тип_данных);

Можно заменить для столбца вариант NOT NULL на NULL,

добавив для спецификации столбца предложение NULL. Можно

заменить для столбца NULL на NOT NULL аналогичным образом, но

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

возникнуть необходимость добавить столбец NOT NULL к таблице,

которая уже создана, но это нельзя сделать в один этап. С помощью

предложения MODIFY эту операцию можно осуществить в три этапа:

1. Добавить столбец;

2. Заполнить некоторым непустым значением каждое поле в

столбце;

3. Использовать ALTER TABLE MODIFY для того, чтобы

добавить к столбцу спецификацию NOT NULL.

Для уменьшения ширины столбца или изменения ширины

столбца необходимо, чтобы все значения в столбце для всех строк

были пустыми (NULL).

Заполнение таблицы

После создания таблицы необходимо внести в нее данные. Эту

операцию можно осуществить с помощью команды INSERT. В общем

виде команда выглядит так:

INSERT

INTO <имя_таблицы> [(<имя_столбца1>, …)]

VALUES (‘<значение1>’, …);

Если список имен столбцов опущен, то значения для каждого

столбца должны быть перечислены в таблице и список значений

должен быть задан в той же последовательности, в какой следуют

столбцы в таблице. В случае, если значения расположены точно в

таком же порядке, как и столбцы, и ни одно значение не пропущено, то

можно опустить имена столбцов и написать так:

INSERT

INTO имя_таблицы

VALUES (значение1, значение2 , …);

Если отсутствуют значения для одного или нескольких

столбцов, то в качестве соответствующего значения можно указать

NULL, если этот столбец не содержит спецификации NOT NULL.

Причем, значения располагаются в таком же порядке, как и

столбцы в таблице.

Вообще говоря, вставляемые значения должны удовлетворять

следующим требованиям:

1. Если значение опущено и номера столбцов не выбраны, то

Oracle будет исходить из предположения, что каждый

столбец должен быть заполнен некоторым значением и

вставит NULL на место того значения, которое она считает

отсутствующим. (В случае, если у столбца нет спецификации

NOT NULL).

2. Вставляемые значения должны соответствовать типу

данных, в который они включаются.

3. Вставляемые значения должны разделяться запятыми.

4. Команда INSERT может использоваться для копирования

строк или частей строк из одной таблицы в другую.