Сетевые базы данных

Конспект лекций | назад

Конспект лекций

Содержание

1. Введение

1.1. Основные особенности языка SQL

1.2. Алфавит языка

1.3. Лексическая структура языка

1.4. Стандартные процедуры и функции

1.5. Процедуры ввода/вывода

1.6. Таблицы учебной база данных

Вопросы для самоконтроля

2. Простые типы данных

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

2.2. Символьные типы данных

2.3. Тип данных даты-времени

2.4. Преобразование типов

2.5. Порядок вычисления выражений

Вопросы для самоконтроля

3. Типы команд языка SQL

3.1. Команды обработки данных

3.2. Управление транзакциями

3.3. Команды определения данных

3.4. Команды управления данными

Вопросы для самоконтроля

4. Управление привилегиями

4.1. Понятие привилегии

4.2. Типы привилегий

4.3. Системные привилегии

4.4. Объектные привилегии

4.5. Роли

5. Язык PL/SQL

5.1. Блочная структура программ

5.2. Раздел DECLARE

5.3. Раздел BEGIN-END

5.4. Раздел EXCEPTION

Вопросы для самоконтроля

6. Простые типы данных PL/SQL

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

6.2. Символьные типы данных

6.3. Тип данных даты-времени

6.4. Логический тип данных

6.5. Тип данных %TYPE

6.6. Преобразование типов

6.7. Порядок вычисления выражений

Вопросы для самоконтроля

7. Операторы языка PL/SQL

7.1. Оператор присваивания

7.2. Составной оператор

7.3. Условный оператор

7.4. Оператор выбора CASE

7.5. Оператор простого цикла (цикл LOOP)

7.6. Оператор цикла с параметром (цикл FOR)

7.7. Оператор цикла с предусловием (цикл WHILE)

7.8. Оператор перехода GOTO

Вопросы для самоконтроля

8. Работа с базами данных

8.1. Выборка данных

8.2. Курсоры

8.3. Изменение данных в таблицах

8.4. Транзакции

Вопросы для самоконтроля

9. Хранимые подпрограммы PL/SQL

9.1. Процедуры

9.2. Функции

9.3. Пакеты

9.4. Области действия и область видимости имен

9.5. Параметры процедур и функций

9.6. Триггеры

Вопросы для самоконтроля

10. Стандартные пакеты PL/SQL

10.1. Пакет DBMS_OUTPUT

11. Интегрированная среда Oracle 10g XE

11.1 Как начать работу с Oracle 10g XE

11.2 Ваша первая программа

Ответы на вопросы для самоконтроля

Рекомендуемая литература

1. Введение

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

- представляют данные в форме двумерных таблиц

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

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

- предоставляют информацию о своем содержании и структуре в системных таблицах

- управляются посредством языка SQL

- поддерживают пустые (NULL) значения

Таблицы

Таблицы являются основными структурными элементами реляционных баз данных. Каждая таблица хранит информацию об одном типе объекта. Свойства таблиц описаны в разделе “Реляционная модель данных”.

Межтабличные связи

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

Физическая реализация

Физическая структура базы данных и пути доступа к данным не задаются пользователем. Эта работа возлагается на систему управления базами данных (СУБД). Пользователь указывает, что ему нужно, а как это сделать – “знает” программное обеспечение СУБД. Благодаря этому БД является гибкой, поскольку данные могут физически перемещаться или изменяться без необходимости перекодировки прикладных программ.

Системные таблицы

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

Язык SQL

Пользователи реляционной БД выдают команды на языке SQL (Structured Query Language, язык структурированных запросов), напоминающем английский. SQL является непроцедурным языком, т.е. он обрабатывает сразу весь набор строк, а не по отдельной строке за один раз.

Пустые значения

Реляционные БД должны поддерживать пустые значения (NULL) для столбцов (полей), значения которых являются неизвестными или неопределенными. Пустые значения – это не пробелы или нули в числовых полях; они обрабатываются иначе, чем обычные значения столбцов. Например, вводя информацию о новом заказчике, можно знать его имя, но не знать номер его банковского счета. Поле номера банковского счета для новой записи заказчика, следовательно, нужно пока оставить пустым.

Описание первой версии языка SQL (SEQUEL2) было опубликовано в 1976 г. в ноябрьском номере журнала IBM Journal of R&D. В1979 г. корпорация Oracle выпустила первую коммерчески доступную реализацию SQL. SQL не является языком программирования в традиционном представлении. На нем пишутся не программы, а запросы к базе данных. Поэтому SQL – декларативный язык. Это означает, что с его помощью можно сформулировать, что необходимо получить, но нельзя указать, как это следует сделать. В частности, в отличие от процедурных языков программирования (СИ, Паскаль, Ада), в языке SQL отсутствуют такие операторы, как if/then/else, for, while, break, continue и т.д..

1.1. Основные особенности языка SQL, обусловившие его выдающийся успех, заключаются в следующем:

1. Это традиционный язык запросов реляционных баз данных;

2. Обрабатывает сразу набор строк данных, а не по отдельной строке за один раз;

3. Набор операторов языка отражает действия, которые необходимо выполнять для хранения, изменения данных в БД и извлечения этих данных;

4. Синтаксис языка напоминает синтаксис простых предложений английского языка.

Мы будем изучать реализацию языка SQL фирмы Oracle для компьютеров под управлением операционной системы Windows XP – систему Oracle 10g Express Edition.

Система Oracle 10g Express Edition (XE) имеет интегрированную среду программирования, т.е. совокупность программ, объединенных в общую систему, которая позволяет писать и редактировать программы, компилировать их, компоновать, отлаживать и запускать на выполнение, не выходя из среды. Включает в себя:

1. универсальный текстовый многооконный редактор для создания и корректировки текстов;

2. компилятор – программу, переводящую текст программы в машинные коды;

3. систему контекстной информационной помощи.

1.2. Алфавит языка

– совокупность допустимых в языке символов. Состоит из следующих символов:

1. заглавные и строчные латинские буквы и знак “подчерк”: A…Z, a…z, _

2. цифры 0…9

3. 22 специальных символа: +, -, *, /, >, <, (, ), [, ], @, #, $, ….

1.3. Лексическая структура языка

включает следующие элементы:

1. служебные
(зарезервированные, ключевые) слова (52 слова): select, from, where… . Это группа слов, смысл которых фиксирован в языке, их нельзя использовать в качестве имен, вводимых программистом,

2. идентификаторы
(имена) - для обозначения таблиц, полей, типов… Образуются из букв и цифр, начинаются всегда обязательно с буквы, длина не должна превышать 30 символа. Не существует различия между прописными и строчными буквами: Например, IDX, idx, Idx – один и тот же идентификатор. Допускается в именах использовать заглавные и строчные латинские буквы, а также символы $, # и _, если они не являются первыми символами имени. Имена не могут содержать знаков пунктуации, пробелов или дефисов.

Пример

:

Верная запись: Tab1, name_a, g255

Неверная запись: 1tab, name a, g*255

3. в тексте программы допускаются фрагменты пояснительного текста – комментарии (только для скриптов и программ PL/SQL), которые располагаются между знаками /* */ и компилятором игнорируются. Для однострочных комментариев можно использовать два дефиса.

Пример:

--комментарий

/*комментарий*/

/* тоже комментарий, но

на двух строках */

1.4. Стандартные процедуры и функции

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

1. Числовые функции

y := abs(x); y = |x|

y := sin(x); y = sin x

y := cos(x); y = cos x

y := atan(x); y = arctg x

y := exp(x); y = ex

y := ln(x); y = ln x

y := nvl(x, значение); возвращает значение, если x имеет значение null, иначе возвращает число x

y := power(x,z); y = xz

y := sqrt(x); y = √x

y := round(x [, z]); округление вещественного числа x до (необязательно) z десятичных знаков. Если параметр z опущен, то округление производится до целого числа. Если z является отрицательным, округление производится влево от десятичной точки.

round(4.76)=5; round(3.26,1)=3.3; round(28.5,-1)=30

y := trunc(x [, z]); усечение вещественного числа x до (необязательно) z десятичных знаков. Если параметр z опущен, то усечение производится до целого числа. Если z является отрицательным, усечение производится влево от десятичной точки.

trunc (4.76)=4; trunc (3.26,1)=3.2; trunc (28.5,-1)=20

y := mod(x, z); остаток от деления x на z

Арифметические функции можно использовать только с величинами целого или вещественного типа. Результат арифметических функций (кроме sqr и abs) – вещественный. У sqr и abs тип результата совпадает с типом аргумента.

2. Символьные функции

y := ASCII(x); возвращает числовое значение (в коде ASCII) символа x

y := chr(x); возвращает символ, код ASCII которого x

y := concat(x,z); дописывает z в конец x и затем возвращает полученную строку

y := length(x); возвращает количество символов в строке x

y := lower(x); конвертирует буквенные символы строки x в символы нижнего регистра

y := nvl(x, значение); возвращает значение, если x имеет значение null, иначе возвращает x

y := power(x,z); y = xz

y := sqrt(x); y = √x

y := trim([удаляемая_строка FROM] x); удаляет символы с правого и левого концов строки x. Можно задать дополнительный параметр удаляемая_строка, позволяющий указать удаляемые символы; если он не задан, удаляются пробелы

3. Функции для работы с датой-временем

y := add_months(x, z); возвращает результат добавления z месяце к значению x. Если z отрицательно, из значения x вычитается z месяцев.

y := last_day(x);возвращает дату последнего дня месяца, для даты, содержащейся в x.

y := months_between(x, z); возвращает число месяце между x и z. Если x появляется в календаре раньше, чем z, возвращаемое значение будет отрицательным; иначе – положительным.

y := round(x [, единицы]); округляет x. По умолчанию x округляется до начала ближайших суток. Можно предоставить необязательный параметр для единиц округления. Например, если значение строки единицы равно YYYY, x будет округлен до первого дня ближайшего года.

y := sysdate; возвращает текущие значения даты и времени, установленные операционной системой машины, на которой размещена база данных.

y := trunc(x [, единицы]); усекает x. По умолчанию x усекается до начала ближайших суток. Можно предоставить необязательный параметр для единиц усечения. Например, если значение строки единицы равно MM, x будет усечен до первого дня ближайшего месяца.

4. Функции конвертирования

y := to_char(p [, формат]); конвертирует число p в строку VARCHAR2. Можно предоставить дополнительный параметр формат, который определяет формат x

y := to_char(d [, формат]); конвертирует d типа DATE в строку VARCHAR2.

y := to_date(x [, формат]); конвертирует x в тип данных DATE

1.5. Процедуры вывода

В системе управления базами данных Oracle имеется пакет программ ввода-вывода DBMS_OUTPUT (см. п. 10), который поставляется вместе с дистрибутивом системы. Пакет содержит несколько процедур обеспечивающих вывод данных, что удобно при тестировании и отладки программ PL/SQL. В данном курсе для нас будет полезна процедура вывода строки PUT_LINE. Процедура PUT_LINE имеет ограничение на длину выводимой строки – не более 255 символов. Ниже приведен пример вывода на экран значения системной даты.

DECLARE

tmp varchar2(20);

BEGIN

tmp := to_char(sysdate,’dd.mm.yyyy’);

DBMS_OUTPUT.PUT_LINE(‘Сегодня ’||tmp||’ г.‘);

END;

/

Результат выполнения программы: Сегодня 01.09.2009 г.

1.6. Таблицы учебной база данных

Изучение материала данного курса основано на использовании учебной базы данных, которая состоит из трех таблиц:

- SAL – таблица, содержащая данные о продавцах

- CUST – таблица, содержащая данные о покупателях

- ORD – таблица, содержащая данные о заказах (покупках), сделанных покупателями и оформленных продавцами.

Таблицы имеют следующие структуры.

Таблица продавцов SAL

Наименование поля | Тип данных поля | Назначение
SNUM | NUMBER(4) | Уникальный номер продавца
SNAME | VARCHAR2(10) | Имя продавца
CITY | VARCHAR2(10) | Город, где работает продавец
COMM | NUMBER(7,2) | Комиссионные, которые получает продавец

Таблица покупателей CUST

Наименование поля | Тип данных поля | Назначение
CNUM | NUMBER(4) | Уникальный номер покупателя
CNAME | VARCHAR2(10) | Имя покупателя
CITY | VARCHAR2(10) | Город, где живет покупателя
RATING | NUMBER(3) | Рейтинг покупателя
SNUM | NUMBER(4) | Номер продавца, обслуживающего покупателя

Таблица заказов ORD

Наименование поля | Тип данных поля | Назначение
ONUM | NUMBER(4) | Уникальный номер заказа
AMT | VARCHAR2(10) | Сумма заказа
ODATE | DATE | Дата выполнения заказа
CNUM | NUMBER(4) | Номер покупателя, сделавшего заказ
SNUM | NUMBER(4) | Номер продавца, оформившего заказ

Таблицы учебной БД хранят следующие данные

Таблица продавцов SAL

SNUM | SNAME | CITY | COMM
1001 | Peel | London | 0,12
1002 | Serres | San Jose | 0,13
1003 | Axelrod | New York | 0,10
1004 | Motica | London | 0,11
1007 | Rifkin | Barcelona | 0,15

Таблица покупателей CUST

CNUM | CNAME | CITY | RATING | SNUM
2001 | Hoffman | London | 100 | 1001
2002 | Giovanni | Rome | 200 | 1003
2003 | Liu | San Jose | 200 | 1002
2004 | Grass | Berlin | 300 | 1002
2006 | Clemens | London | 100 | 1001
2007 | Pereira | Rome | 100 | 1004
2008 | Cisneros | San Jose | 300 | 1007

Таблица заказов ORD

ONUM | AMT | ODATE | CNUM | SNUM
3001 | 18,69 | 03.01.2009 | 2008 | 1007
3002 | 1900,10 | 03.01.2009 | 2007 | 1004
3003 | 767,19 | 03.01.2009 | 2001 | 1001
3005 | 5160,45 | 03.01.2009 | 2003 | 1002
3006 | 1098,16 | 03.01.2009 | 2008 | 1007
3007 | 75,75 | 04.01.2009 | 2004 | 1002
3008 | 4723 | 05.01.2009 | 2006 | 1001
3009 | 1713,23 | 04.01.2009 | 2002 | 1003
3010 | 1309,95 | 06.01.2009 | 2004 | 1002
3011 | 9891,88 | 06.01.2009 | 2006 | 1001

Вопросы для самоконтроля

1.1. В каком виде представляются данные в реляционных БД?

1.2. Как пользователь задает физическую структуру реляционной базы данных?

1.3. Что означают пустые значения (NULL) в реляционных БД?

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

1.5. К какому типу языков относится язык SQL?

1.6. Как обрабатывает данные язык SQL?

1.7. Какие структурные элементы в реляционных базах данных являются основными?

1.8. Какие ограничения накладываются на идентификаторы в Oracle 10g Express Edition?

1.9. Какая информация содержится в системных таблицах Oracle 10g Express Edition?

1.10. Какие ограничения накладываются на использование служебных слов в Oracle 10g Express Edition?

2. Простые типы данных

В определениях столбцов таблиц Oracle могут использоваться следующие простые типы данных SQL:

- NUMBER
- CHAR
- VARCHAR2
- DATE

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

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

Для хранения чисел в базах данных Oracle используется тип данных NUMBER. Тип данных NUMBER хранит числа в формате с фиксированной и плавающей точками. В таких столбцах могут храниться числа практически любой величины. При этом гарантируется их переносимость между различными операционными системами, на которых работает Oracle. Максимальная точность – до 38 знаков. В столбцах типа NUMBER могут храниться следующие значения:

- положительные числа в интервале от 1 х 10–130 до 9.99...9 х 10125 (до 38 значащих цифр);

- отрицательные числа от –1 х 10–130 до –9.99...9 х 10125 (до 38 значащих цифр);

- ноль

Для столбца NUMBER можно, по желанию, задать точность (общее число знаков) и масштаб (число цифр после десятичной точки):

имя_столбца NUMBER (точность, масштаб)

Если точность не задана, данные хранятся так, как они вводятся. Если не задан масштаб, он принимается равным 0. Oracle гарантирует переносимость чисел с точностью до 38 цифр. Можно задавать масштаб и не задавать точность:

имя_столбца NUMBER (*, масштаб).

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

Входные данные | Спецификация | Хранение
7,456,123.89 | NUMBER | 7 456 123.89
7,456,123.89 | NUMBER(*,1) | 7 456 123.9
7,456,123.89 | NUMBER(9) | 7 456 123
7,456,123.89 | NUMBER(9,2) | 7 456 123.89
7,456,123.89 | NUMBER(9,1) | 7 456 123.9
7,456,123.89 | NUMBER(6) | (Неверно, превышена точность)
7,456,123.89 | NUMBER(7,-2) | 7 456 100

2.2. Символьные типы данных

Oracle два типа данных для хранения символьных строк – CHAR и VARCHAR2. В столбце такого типа можно хранить любые символы. Символьные данные хранятся как строки, каждое значение байта которых соответствует символу по соответствующей таблице кодировки (обычно называемой символьным набором или кодовой страницей). Примером символьного набора является 7-битовый ASCII код (American Standard Code for Information Interchange). Oracle поддерживает как однобайтовые так и многобайтовые схемы кодирования.

В столбцах типа данных CHAR хранятся символьные строки фиксированной длины. Когда создается таблица со столбцом типа CHAR, для этого столбца задается длина (в байтах, а не в символах) между 1 и 2000 (по умолчанию 1). Oracle для этого типа данных гарантирует:

- когда в таблице добавляется или обновляется строка, значение столбца типа CHAR имеет фиксированную длину;

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

- если задаются значения длиннее, чем объявленные, с пробелами в конце, эти пробелы удаляются до заданной длины;

- если значение слишком велико, Oracle возвращает ошибку.

Столбцы типа данных VARCHAR2 хранят символьные значения переменной длины. При создании таблицы со столбцом VARCHAR2 задается максимальная длина столбца (в байтах, а не в символах) от 1 до 4000 байтов. Каждое значение столбца хранится как поле переменной длины. Если значение превышает заданную длину для столбца, Oracle возвращает ошибку. Например, столбец типа VARCHAR2 объявлен с длиной 100 символов. В однобайтовой схеме кодирования, если в заданном значении содержится только 25 символов для конкретной строки, в этом столбце будут храниться только 25 символов (25 байтов), а не 100.

2.3. Тип данных даты-времени

Значения столбцов типа данных DATE хранят значения даты и времени. Тип данных DATE хранит год, включая век, месяц, день, часы, минуты и секунды (после полуночи). Oracle может хранить даты от 1 января 4712 года до н.э. до 31 декабря 4712 года н.э.

Oracle использует свой внутренний формат для хранения значений дат. Значения даты хранятся в полях фиксированной длины, каждое длиной 7 байт. Соответственно – век, год, месяц, день, час, минута и секунда. При вводе и выводе дат стандартный формат Oracle по умолчанию равен DD-MON-YY. Например, 12-NOV-92.

Этот формат по умолчанию можно изменить для всего экземпляра в целом с помощью параметра NLS_DATE_FORMAT. Формат по умолчанию можно изменять и только для конкретного сеанса с помощью команды ALTER SESSION. Для того, чтобы вводить даты в отличном от стандартного формате, используется функция TO_DATE с соответствующей форматной маской. Например:

TO_DATE (‘November 13, 1992’, ‘MONTH DD, YYYY’)

Для вывода даты и времени в заданном строковом формате используется функция TO_CHAR с соответствующей форматной маской. Например функция

TO_CHAR(sysdate,’dd.mm.yyyy hh24:mi’)

вернет системную (текущую) дату и время в виде часов в 24-часовом формате и минут.

Время хранится в 24-х часовом формате HH:MI:SS. По умолчанию время в столбце типа DATE равно 12:00:00 А.М. (полночь), если значение времени не задано явно. Если задается только время, часть, соответствующая дате, по умолчанию равна первому дню текущего месяца. Для того, чтобы ввести значение времени указанной даты, используется функция TO_DATE с форматной маской, задающей время, как в следующем примере:

INSERT INTO birthdays (bname, bday) VALUES

(‘ANDY’, TO_DATE(’13.08.2009 14:56:00’, ‘DD.MM.YYYY HH24:MI:SS’));

2.4. Преобразование типов

В Oracle автоматическое преобразование типов весьма ограничено. Возможно автоматическое преобразование чисел в символьные строки и наоборот (и некоторые другие), если не возникает неоднозначного толкования этого процесса. В документации по Oracle рассматриваются варианты и ограничения на автоматическое преобразование типов. Тем не менее, рекомендуется использовать функции явного преобразования для корректного выполнения команд SQL и программ PL/SQL.

2.5. Порядок вычисления выражений

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

Приоритет операций:

1. вычисления в круглых скобках ( )

2. вычисления значений функций

3. унарные операции not

4. операции типа умножение *, /, div, mod, and

5. операции типа сложение +, -, or, xor

6. операции сравнения >, <, <=, >=, =, <>

Вопросы для самоконтроля

2.1. Что такое точность и масштаб в числовых типах данных?

2.2. Какова максимальная точность в числовых типах данных?

2.3. Какие типы данных в языке SQL относятся к символьным?

2.4. В чем отличия типов данных CHAR и VARCHAR2?

2.5. Какова максимальная длина типа данных VARCHAR2 в SQL?

2.6. Какие элементы даты не хранятся в столбцах типа дата-время – век, год, месяц, день, час, минута или секунда?

2.7. Какая функция используется для преобразования строки в тип дата-время?

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

2.9. Возможно ли автоматическое преобразование одного типа данных в дугой?

2.10. Возможно ли автоматическое преобразование числового типа данных в строковый тип?

3. Типы команд языка SQL

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

- язык обработки данных DML (Data Manipulation Language)

- управление транзакциями (Transaction control)

- язык определения данных DDL (Data Definition Language)

- язык управления данными DCL (Data Control Language)

3.1. Команды обработки данных

Команды языка DML осуществляют извлечение, вставку, изменение и удаление строк в таблицах БД Oracle. Такими командами являются SELECT, INSERT, UPDATE и DELETE.

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

3.1.1. Извлечение данных с помощью запросов

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

SELECT [ DISTINCT ] список выбора

FROM список таблиц

WHERE условие

GROUP BY поле [,] [ HAVING условие ]

ORDER BY поле | позиция [ ASC | DESC ]

Здесь список выбора - список полей или выражений, которые должны отображаться в выводимом наборе данных. Список таблиц – одна или несколько таблиц БД, из которых должны извлекаться данные. Условие – логическое выражение, определяющее набор строк (или групп строк) таблицы, которые должны участвовать в выборке. Поле | позиция – имя поля из списка выбора или его порядковый номер в этом списке, по которому сортируются выходные данные.

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

SELECT * FROM sal

В этой команде звездочка (*) используется для указания, что выбираются все поля таблицы. В команде отсутствует фраза WHERE, что означает выбор всех строк таблицы SAL.

Фраза DISTINCT используется для того, чтобы исключить повторяющиеся (полностью совпадающие) строки из результирующего набора строк.

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

SELECT snum, sname FROM sal WHERE city='London'

В этом примере список выбора включает два поля - snum и sname, - а условие “city='London'” означает, что при выполнении запроса просматриваются только те строки, в которых поле city имеет значение “London”.

Построение выражений

Для построения выражений, используемы во фразах WHERE и HAVING, можно применять реляционные, булевские и специальные операторы, а также их комбинации. К реляционным операторам относятся следующие:

= - равно

> - больше, чем

< - меньше, чем

>= - больше или равно

<= - меньше или равно

<>, !=, ^= - не равно.

Стандартными булевыми операторами, распознаваемыми SQL, являются:

AND - логическое И

OR - логическое ИЛИ

NOT - логическое ОТРИЦАНИЕ

Следующий пример показывает, как выбрать имена и рейтинги тех покупателей, которые живут в Лондоне или в Риме.

SELECT сname, rating FROM cust WHERE city='London' OR city='Rome'

К специальным операторам относятся такие, как IN, BETWEEN и LIKE. Оператор IN задает множество, принадлежность к которому может задаваться в условии выборки, и является альтернативой применению оператора OR. Так, последний пример можно записать и другим способом для достижения того же результата.

SELECT сname, rating FROM cust WHERE city IN ('London', 'Rome')

Оператор BETWEEN похож на оператор IN, но BETWEEN задает диапазон значений. После ключевого слова BETWEEN вводится начальное значение диапазона, затем ключевое слово AND и конечное значение. Следующая команда будет извлекать всех продавцов с комиссионными от 0.10 до 0.12 (т.е. граничные значения попадают в выборку):

SELECT * FROM sal WHERE comm BETWEEN 0.10 AND 0.12

Оператор LIKE используется, чтобы находить подстроки в указанном поле таблицы. Этот оператор применим только к полям типа CHAR или VARCHAR. В искомой строке могут использоваться групповые символы (wildkards):

- символ подчеркивания “_” замещает любой одиночный символ,

- знак процента “%” – группу из любого числа символов.

В следующем примере запрашиваются сведения о всех заказчиках, в имени которых второй буквой является буква i:

SELECT * FROM cust WHERE cname LIKE '_i%'

Агрегатные функции и группировка

Агрегатные (обобщающие, многострочные, групповые) функции обрабатывают множества значений столбца таблицы или группы столбцов и возвращают обобщенное значение для группы. Наиболее употребимы следующие функции:

COUNT- количество строк, выбранных в запросе

SUM - арифметическая сумма всех значений столбца

AVG - среднее значение всех значений столбца

MAX - наибольшее значение из всех значений столбца

MIN - наименьшее значение из всех значений столбца

Например, для нахождения общей суммы всех заказов в таблице ORD можно ввести следующий запрос:

SELECT SUM (amt) FROM ord

Агрегатные функции могут применяться к группам строк (а не ко всем строка таблицы) при использовании опции GROUP BY. При этом автоматически выполняется разбивка на группы значений в тех столбцах, которые указаны как аргументы в опции GROUP BY. Например, требуется найти наиболее крупный заказ для каждого продавца. Можно сделать раздельный запрос для каждого из них, выбрав MAX(amt) из таблицы Заказов для каждого значения поля snum. Однако, GROUP BY позволяет это сделать одной командой:

SELECT snum, MAX(amt) FROM ord

GROUP BY snum

Если требуется получить групповые данные не по всем группам, а только по некоторым, то для указания критерия отбора опция GROUP BY дополняется опцией HAVING. Предположим, что в предыдущем примере потребовалось вывести информацию только для групп, чьи максимальные закаы превышают 3000. Это можно сделать следующим образом:

SELECT snum, MAX(amt) FROM ord

GROUP BY snum

HAVING MAX(amt) > 3000

Сортировка результирующих данных

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

SELECT * FROM cust ORDER BY cname;

SELECT * FROM cust ORDER BY city DESC;

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

SELECT cnum, cname, city, rating, snum FROM cust ORDER BY cname;

SELECT cnum, cname, city, rating, snum FROM cust ORDER BY 2;

Использование значений Null

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

Чтобы значения Null в результатах выборки или в выражениях обрабатывалось требуемым образом, необходимо использовать специальную скалярную функцию NVL. Эта функция имеет два аргумента: 1) поле (или переменная), которое может содержать null-значение, и 2) заменяющее значение (см. описание числовых и символьных функций в п.1.4). Функция возвращает значение, заданное пользователем, когда значение поля есть null. В качестве примера использования функции NVL рассмотрим команду SELECT, выбирающую данные о покупателях. При это для получения нулевого рейтинга вместо null-значения применяется функция NVL в списке вывода.

SELECT cnum, cname, city, nvl(rating,0) as rating

FROM cust

ORDER BY cname;

Наш запрос выведет рейтинг, равный 0, для всех тех покупателей, у которых он равен null. Но рейтинги тех покупателей, у кого они имеют конкретные числовые значения (не null), будут выведены такими, какими они хранятся в таблице, т.е. функция NVL не окажет на них никакого воздействия. Следует отметить, что если применять функцию NVL к полю, содержащему символьные данные, то в качестве второго аргумента нужно задать строковое значение. Обратите внимание на фразу “as rating” – так задан алиас (псевдоним) для столбца, содержащего рейтинги. Если бы мы этого не сделали, столбец бы имел название “nvl(rating,0)” в результирующем наборе строк.

Объединение данных в связанных таблицах

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

SELECT cname, sname, sal.city FROM sal, cust

WHERE sal.city = cust.city

Команда SELECT в приведенном примере выводит объединенные данные из связанных таблиц. Для выполнения объединения нам потребовалось 1) во фразе FROM перечислить таблицы (sal и cust), данные из которым нужно выбрать, и 2) во фразе WHERE указать имена полей, по которым производится связывание, приравняв их друг другу. Выполненное таким образом объединение называется внутренним. При внутреннем объединении в результирующий набор данных не будут включены строки какой-либо из таблиц, которые не имеют соответствия в другой таблице.

Если при объединении таблиц фразу WHERE опустить (или не указать в ней условие “sal.city = cust.city”), то мы получим так называемое декартово произведение. В этом случае в результирующий набор данных попадут данные из всех комбинаций строк обеих таблиц – каждая строка одной таблицы связывается с каждой строкой другой таблицы.

Язык SQL позволяет создавать объединения, которые включают и различные таблицы, и псевдонимы одной и той же таблицы. Следующий запрос, объединяет таблицу Заказчиков с собой, чтобы найти все пары заказчиков, обслуживаемых одним продавцом. Кроме того, этот запрос объединяет таблицу Заказчиков с таблицей Продавцов по номеру продавца для выбора имени продавца:

SELECT sname, sal.snum, a.cname, b.cname

FROM cust a, cust b, sal

WHERE a.snum = b.snum

AND sal.snum = a.snum

ORDER BY sname, a.cname

Подзапросы

Подзапрос – это оператор SELECT, вложенный внутрь другого оператора SELECT.

Есть два основных вида подзапросов:

- однострочные подзапросы. Возвращают ноль строк или одну строку

- многострочные подзапросы. Возвращают от одной до нескольких строк

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

- многостолбцовые подзапросы. Возвращают более одного столбца

- коррелированные подзапросы. Ссылаются на один или несколько столбцов во внешнем операторе SQL.

- Вложенные подзапросы. Подзапросы, помещенные внутрь другого подзапроса. Глубина вложений может составлять до 255.

Пример однострочного подзапроса во фразе WHERE

SELECT sname, city, comm FROM sal

WHERE snum =

(SELECT snum FROM sal WHERE sname=

'Peel');

В этом примере из строки таблицы sal для продавца Peel выбираются имя продавца (sname), город, в котором он работает (city) и его комиссионные (comm). В подзапросе в качестве условия выбора мы указываем имя продавца, и по этому имени извлекается номер продавца snum. Найденный в подзапросе номер snum используется теперь в условии выбора основного запроса.

Пример подзапроса во фразе FROM (встроенное представление)

SELECT snum, sname, comm

FROM

(SELECT snum, sname, comm

FROM sal WHERE city =

'London');

Здесь подзапрос помещен во фразу FROM вместо имени таблицы, поэтому он называется встроенным представлением (о представлениях см. п. 3.3.3). Подзапрос формирует данные только о продавцах, работающих в Лондоне.

Пример использования оператора IN с многострочным подзапросом

SELECT snum, sname, comm FROM sal

WHERE snum IN

(SELECT snum FROM sal WHERE city =

'London');

Здесь основной запрос выводит данные только о продавцах, работающих в Лондоне, как и в предыдущем примере. Но теперь в условии WHERE основного запроса выбираются номера snum только такие, которые возвращаются подзапросом. А подзапрос возвращает номера snum только продавцов, работающих в Лондоне.

3.1.2. Вставка, обновление и удаление строк в таблицах

Рассмотрим вопрос о том, как вводить, изменять и удалять данные таблиц с помощью команд INSERT, UPDATE и DELETE языка SQL.

Команда INSERT может быть построена, исходя из следующего синтаксиса.

INSERT INTO таблица

(поле [,])

{ VALUES ( выражение [,] ) | запрос }

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

INSERT INTO sal

VALUES (1008, ‘Tom’, NULL, NULL)

В следующем примере показан второй вариант команды INSERT: команда вставляет строки в таблицу SAL_COPY, копируя их из таблицы SAL

INSERT INTO sal_copy

SELECT * FROM sal

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

UPDATE таблица

SET { поле = { выражение | запрос } [,] | ( поле [,]) = запрос }

[ WHERE условие ]

В приведенном ниже примере командой UPDATE увеличивается на 50 рейтинг покупателей, чьи заказы оформлял продавец с номером 1001.

UPDATE cust SET rating=rating + 50

WHERE snum = 1001;

Команда DELETE имеет следующий синтаксис.

DELETE [ FROM ] таблица

[ WHERE условие ]

Если в команде DELETE опустить фразу WHERE, то при таком простом варианте будут удалены все строки из таблицы. Следует отметить, что при этом сама таблица не будет удалена их базы данных, но она станет пустой. Пример удаления всех заказов из таблицы ORD показан ниже.

DELETE FROM ord

Для удаления строк, отвечающих заданному условию, в команду DELETE требуется включить фразу WHERE с условием удаления. Так, следующем примере удаляются заказы, сумма которых не превышает 100.

DELETE FROM ord WHERE amt <= 100;

3.2. Управление транзакциями

ТРАНЗАКЦИЯ - это логическая единица работы, составленная из одной или нескольких команд SQL. Транзакция - это атомарная единица; результаты всех команд SQL, составляющих транзакцию, должны быть либо все ПОДТВЕРЖДЕНЫ (применены к базе данных), либо все ОТМЕНЕНЫ. Транзакции должны состоять лишь из тех команд SQL, которые в совокупности осуществляют одно согласованное изменение данных.

Транзакция начинается с первой выполняемой команды SQL. Транзакция заканчивается, когда она либо подтверждается, либо отменяется, что может быть сделано явно (командами COMMIT или ROLLBACK) или неявно (когда выдается команда DDL).

3.2.1. Подтверждение транзакций

ПОДТВЕРЖДЕНИЕ транзакции означает объявление постоянными всех изменений, которые были выполнены командами SQL внутри этой транзакции.

Для подтверждения транзакции используйте команду COMMIT. Следующие предложения эквивалентны и подтверждают текущую транзакцию:

COMMIT WORK;

COMMIT;

3.2.2. Откат транзакций

ОТКАТ означает отмену всех изменений данных, которые были сделаны командами SQL в неподтвержденной транзакции.

Чтобы отменить всю, используйте команду ROLLBACK. Например, каждое из следующих предложений откатывает всю текущую транзакцию:

ROLLBACK WORK;

ROLLBACK;

3.3. Команды определения данных

Команды языка определения данных (Data Definition Language, DDL) определяют, создают и поддерживают объекты, а также уничтожают их, когда объекты больше не нужны.

3.3.1. Таблицы

Для создания таблиц используют команду CREATE TABLE, которая имеет следующий синтаксис:

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

(имя_столбца тип_данных [DEAFAULT выражение] [ограничение_столбца] [,])

PRIMARY KEY

CONSTRAINT имя_ограничения AS запрос

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

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

ALTER TABLE mytab ADD col5 CHAR(5);

ALTER TABLE mytab MODIFY col2 VARCHAR2(200);

Столбцы не могут быть переименованы.

Переименование таблиц

RENAME mytab TO tabnew;

Удаление таблиц

Для удаления и данных, и определения таблицы можно использовать команду DROP TABLE; данные не нужно удалять отдельно.

DROP TABLE имя_таблицы;

3.3.2. Последовательности

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

Последовательность создается командой CREATE SEQUENCE, имеющей следующий синтаксис:

CREATE SEQUENCE имя_последовательности

[ INCREMENT BY integer ]

[ START WITH integer ]

[ MAXVALUE integer | NOMAXVALUE ]

[ MINVALUE integer | NOMINVALUE ]

[ CYCLE | NOCYCLE ]

[ CACHE integer | NOCACHE ]

[ ORDER | NOORDER ]

sequence.NEXTVAL | возвращает следующее (очередное) значение последовательности
sequence.CURRVAL | возвращает последнее сгенерированное значение последовательности sequence. Этот псевдостолбец будет доступен только после использования псевдостолбца sequence.NEXTVAL.

3.3.3. Представления

Представление это хранимая команда SELECT, которая выполняется при каждом использовании имени представления в команде SQL. Представление может использоваться в запросах Oracle так, как если бы это была таблица.

Представления можно использовать в командах INSERT и UPDATE, если выполнены два следующих условия:

1. представление основано на одной таблице

2. в представление включены все NOT NULL-столбцы таблицы

Для создания представлений используется команда CREATE VIEW, которая имеет следующий синтаксис:

CREATE [ OR REPLACE ] VIEW имя_представления

AS select-команда

[ WITH READ ONLY | WITH CHECK OPTION ]

3.3.4. Синонимы

Синонимы – это псевдонимы для объектов базы данных. Для создания синонимов используется следующая команда:

CREATE [ PUBLIC ] SYNONYM имя_синонима

FOR имя_объекта

3.4. Команды управления данными

Команды управления данными используются для администрирования объектов и приложений баз данных, для управления доступом пользователей к объектам и приложениям БД. Двумя наиболее используемыми командами этой категории являются GRANT и REVOKE.

Работа пользователя с базой данных определяется набором разрешений, назначенных данному пользователю администратором БД. Эти разрешения называются привилегиями. Для наделения пользователя привилегиями используется команда GRANT, а для отмены привилегий – команда REVOKE.

Описание команд GRANT и REVOKE дано в разделе 4. Управление привилегиями

Вопросы для самоконтроля

3.1. Какие команды языка SQL относятся к командам DML?

3.2. В каких случаях необходима опция GROUP BY в команде SELECT?

3.3. В чем различие между операторами IN и BETWEEN?

3.4. К какой группе команд SQL относится команда ROLLBACK?

3.5. Каково назначение команды COMMIT?

3.6. Что означает аббревиатура DDL?

3.7. В чем сходство и разница между командами UPDATE и ALTER?

3.8. Что такое последовательность?

3.9. Чем представление отличается от команды SELECT?

3.10. Какие вы знаете команды управления данными?

4. Управление привилегиями

4.1. Понятие привилегии

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

Например, для подключения к БД и создания в ней таблиц необходимо иметь привилегии CREATE SESSION (создание сессии) и CREATE TABLE (создание таблицы). Такого рода привилегии для каждого пользователя назначает администратор БД.

4.2. Типы привилегий

В Oracle имеются два типа привилегий – системные и объектные. Системные привилегии регламентирую работу с объектами базы данных, например создание, изменение или удаление таблиц. Объектные привилегии определяют возможности использования уже существующих объектов, или, другими словами, доступ к объектам – например удаление данных из таблицы.

4.3. Системные привилегии

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

CREATE SESSION – разрешение на установление сеанса связи с базой данных

CREATE TABLE – разрешение на создание таблиц

CREATE INDEX – разрешение на создание индексов

CREATE PROCEDURE – разрешение на создание подпрограмм

Предоставление системных привилегий выполняется командой GRANT, которая имеет следующий синтаксис.

GRANT {привилегия | имя_роли} [, { привилегия | имя_роли }] ...

TO { пользователь | имя_роли | PUBLIC}

[, { пользователь | имя_роли | PUBLIC}] ...

[WITH ADMIN OPTION]

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

GRANT create table, create procedure TO tom;

GRANT create table TO public;

Отмена системных привилегий выполняется командой REVOKE, которая имеет следующий синтаксис.

REVOKE { привилегия | имя_роли } [, { привилегия | имя_роли }] ...

FROM { пользователь | имя_роли | PUBLIC}

[, { пользователь | имя_роли | PUBLIC}] ...

Приведем пример изъятия системной привилегии на создание процедур у пользователя Tom.

REVOKE create procedure FROM tom;

Следует отметить, что привилегии, выданные пользователю PUBLIC, не могут быть изъяты у какого-либо отдельно пользователя. Их можно изъять толь так же, как они даны, – у пользователя PUBLIC (у всех).

Таблицы с префиксом USER_ системного словаря данных DICTINARY позволяют определить, какими системными привилегиями обладает пользователь.

4.4. Объектные привилегии

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

SELECT – разрешение на чтение данных из таблицы

INSERT – разрешение на добавление данных в таблицу

UPDATE – разрешение на изменение данных в таблице

DELETE – разрешение на удаление данных из таблицы

EXECUTE – разрешение на выполнение программы

Команда GRANT для предоставления объектной привилегии имеет синтаксис, показанный ниже.

GRANT { привилегия | ALL [PRIVILEGES]} [ (колонка [,колонка] ...) ]

[, { привилегия | ALL [PRIVILEGES]} [ (колонка [,колонка] ...) ] ]

ON [схема.]объект

TO { пользователь | имя_роли | PUBLIC}

[, { пользователь | имя_роли | PUBLIC}] ...

[WITH ADMIN OPTION]

Синтаксис команды позволяет давать привилегии на использование, например, не только всей таблицы, но можно дать разрешения только на указанные столбцы. В примере выдаются объектные привилегии на чтение и вставку данных в таблицу Tab1 пользователю Tom.

GRANT select, insert ON tab1 TO tom;

Команда REVOKE для отмены объектных привилегий имеет следующий синтаксис.

REVOKE { привилегия | ALL [PRIVILEGES]}

[, { привилегия | ALL [PRIVILEGES]} ] ...

ON [схема.] объект

FROM { пользователь | имя_роли | PUBLIC}

[, { пользователь | имя_роли | PUBLIC}] ...

[CASCADE CONSTRAINTS]

В примере у пользователя Tom изымается право на вставку данных в таблицу Tab1, при этом право на чтение данных из таблицы (предоставленное в предыдущем примере) у него остается.

REVOKE insert ON tab1 FROM tom;

Таблицы с префиксом USER_ системного словаря данных DICTINARY позволяют определить, какими объектными привилегиями обладает пользователь.

4.5. Роли

Роль – это совокупность системных и/или объектных привилегий, сгруппированных под одним именем, чтобы облегчить предоставление и отмену этих привилегий. Как только пользователю будет дана роль, все привилегии, связанные с ней, будут унаследованы пользователем.

Роли могут быть назначены не только пользователю Oracle, но и другим ролям, а также предопределенному имени пользователя PUBLIC.

4.5.1. Создание ролей

Для создания роли используется команда CREATE ROLE. Имена ролей не должны конфликтовать с именами пользователей, уже созданных в базе данных. Синтаксис команды CREATE ROLE имеет следующий вид:

CREATE ROLE имя_роли

[ NOT IDENTIFIED | IDENTIFIED {BY пароль | EXTERNALLY} ]

В показанном ниже примере создается роль под именем salperson.

CREATE ROLE salperson

Следующий пример показывает создание роли под именем manager с паролем secret, который потребуется указать при включении роли командой SET ROLE.

CREATE ROLE manager IDENTIFIED BY secret ;

4.5.2. Изменение ролей

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

ALTER ROLE имя_роли

[ NOT IDENTIFIED | IDENTIFIED {BY пароль | EXTERNALLY} ]

Например, отменить пароль у роли manager можно такой командой

ALTER ROLE manager NOT IDENTIFIED

4.5.3. Удаление ролей

Удаление роли выполняется командой DROP ROLE имя_роли. При удалении роли не требуется отменять ее для всех пользователей или ролей, которым она была предоставлена Это будет выполнено автоматически.

4.5.4. Предоставление привилегий ролям

Предоставление ролям привилегий выполняется аналогично тому, как привилегии предоставляются пользователю – командой GRANT. Только вместо имени пользователя используется имя роли. Например:

GRANT create table, create procedure TO manager

GRANT select, update ON delegates TO manager

4.5.5. Удаление привилегий у ролей

Удаление привилегий выполняется командой REVOKE. Например:

REVOKE create procedure FROM manager

4.5.6. Предоставление ролей

Роли предоставляются (назначается) пользователям или другим ролям с использованием того же варианта команды GRANT, который используется для предоставления системных привилегий (см. п. 4.3). В следующем примере показано, как роль salperson назначается пользователю Tom:

GRANT salperson TO Tom

4.5.6. Отмена ролей

Синтаксис команды REVOKE для отмены ролей похож на синтаксис команды для отмены системных привилегий (см. п. 4.3). Это видно из следующего примера:

REVOKE salperson FROM Tom

5. Язык PL/SQL

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

5.1. Блочная структура программ

Программа на языке PL/SQL состоит из блоков. Блок PL/SQL – это люба часть кода PL/SQL, в которой имеются операторы BEGIN и END. Он может иметь или не иметь операторы DECLARE и EXCEPTION. Блоки имеют следующую структуру:

DECLARE

(список объявлений переменных)

BEGIN

(список команд SQL и PL/SQL)

EXCEPTION

(обработчики исключений)

END;

Программы PL/SQL можно разделить на две категории по способу их хранения. К первой категории относят программы, хранящиеся в виде обычных текстовых файлов. В этом случае текст программы оформляется в виде безымянного (анонимного) блока. Безымянные блоки не имеют внешних параметров и не могут быть вызваны из других программ. Все примеры данного параграфа являются безымянными блоками.

Вторая категория программ – это хранимые в базе данных Oracle программы PL/SQL. Хранимые программы (процедуры, функции, пакеты и триггеры) будут описаны в параграфе 9.

5.2. Раздел DECLARE

Раздел DECLARE является необязательным разделом блока PL/SQL. Этот раздел необходим, если требуется объявить переменные или другие программные конструкции, которые будут использоваться в разделах BEGIN и/или EXCEPTION. В показанном ниже примере переменные A и B объявляются в разделе DECLARE, а затем используются в разделе BEGIN.

DECLARE

A number;

B number;

BEGIN

A := 2;

B := A * 100;

END;

5.3. Раздел BEGIN-END

Раздел BEGIN-END является обязательным разделом блока PL/SQL – именно в нем находятся исполняемые команды программы. Внутри данного раздела могут размещаться другие (внутренние) блоки PL/SQL. Глубина вложений блоков друг в друга не ограничена. В примере показан блок с вложенным в него другим блоком. Следует обратить внимание, что в каждом из блоков в разделе DECLARE объявлены переменные A и B. Однако для каждого блока это свои переменные. Во внешнем блоке переменные A и B, объявленные во внутреннем блоке, не видны. А во внутреннем блоке переменные внешнего блока видны, но в данном примере они “перекрыты” своими объявлениями переменных с такими же именами – поэтому внешние переменные тоже не видны.

DECLARE

A number;

B number;

BEGIN

A := 2;

B := A * 100;

DECLARE

A number;

B number;

BEGIN

A := 3;

B := A * 100;

dbms_output

.put_line('1. B='||B);

END;

dbms_output

.put_line('2. B='||B);

END;

Если выполнить эту программу то будут выведены две строки:

1. B=300

2. B=200

5.4. Раздел EXCEPTION

Раздел EXCEPTION - необязательный раздел блока PL/SQL. Этот раздел используется для обработки ошибок, возникающих при исполнении (а не во время компиляции) программы. Если при исполнении какой-либо из команд раздела BEGIN возникает ошибка, то управление сразу же передается в раздел EXCEPTION. Обратно из раздела EXCEPTION в раздел BEGIN управление не передается.

Внутри данного раздела могут размещаться другие (внутренние) блоки PL/SQL.

В разделе EXCEPTION находятся так называемые обработчики ошибок. Количество обработчиков может быть произвольным и определяется программистом. Каждые обработчик – это отдельная программная секция, которая начинается фразой WHEN тип_ошибки THEN. В этой фразе слова WHEN и THEN – ключевые слова PL/SQL, а тип_ошибки – определяет тип обрабатываемой в данной секции ошибки. В Oracle PL/SQL ошибки могут быть предустановленного типа и ошибки, определяемые пользователем. Таким образом каждый обработчик обрабатывает ошибки только указанного типа. Есть одно исключение из этого правила, о чем будет сказано ниже.

Вслед за фразой WHEN тип_ошибки THEN размещаются любые операторы программы, которые программист сочтет необходимыми. Обычно это команды записи в журнальные таблицы для фиксации факта возникновения ошибки (тип ошибки, время ее возникновения и т.п.) или вызов процедуры, в которой выполняется дополнительная обработка.

В приведенном примере блок PL/SQL содержит раздел EXCEPTION, содержащий два обработчика.

DECLARE

A number;

B number;

BEGIN

A := 0;

B := 100/A;

EXCEPTION

WHEN ZERO_DIVIDE THEN

B := 0;

WHEN OTHERS THEN

Proc_err;

END;

Первый обработчик – ZERO_DIVIDE – обрабатывает ошибку, возникающую при делении на ноль. Это обработчик просто выполняет обнуление переменной B.

Второй обработчик – OTHERS – это обработчик особого типа, т.е. то самое исключение, о чем говорилось выше. Обработчик OTHERS выполняется тогда, когда тип возникшей ошибки не обрабатывается ни одним из имеющихся обработчиков или если других обработчиков нет. Так как этот обработчик “перехватывает” ошибки любых типов, его всегда нужно помещать в разделе EXCEPTION после всех других обработчиков.

Вопросы для самоконтроля

5.1. Какие необязательные разделы содержит блок PL/SQL?

5.2. Каковы отличительные особенности безымянного блока PL/SQL?

5.3. Какие программные единицы хранятся в базе данных?

5.4. В каком разделе блока PL/SQL не допускаются внутренние блоки?

5.5. Что такое обработчик ошибок?

5.6. В каких случаях управление ходом выполнения программы передается в раздел EXCEPTION?

5.7. Как вызвать безымянный блок из другой программы?

5.8. В какой последовательности нужно размещать обработчики в разделе EXCEPTION?

5.9. Для чего используется обработчик OTHERS?

5.10. Какие операторы программы можно размещать в обработчике ошибок?

6. Простые типы данных PL/SQL

Язык PL/SQL позволяет использовать скалярные (простые) переменные, а также составные структуры данных различных типов. В данном курсе будут рассматриваться простые типы данных. Для изучения составных типов следует обратиться к рекомендуемой литературе.

Как и в SQL, в языке PL/SQL наиболее употребимыми типами данных являются:

- NUMBER

- CHAR

- VARCHAR2

- DATE

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

- BOOLEAN

- %TYPE

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

Переменные типа NUMBER используются для хранения чисел в формате с фиксированной и плавающей точками. Диапазоны чисел, которые можно присваивать переменным, определяются следующими значениями:

- положительные числа в интервале от 1 х 10–130 до 9.99...9 х 10125 (до 38 значащих цифр);

- отрицательные числа от –1 х 10–130 до –9.99...9 х 10125 (до 38 значащих цифр);

Для объявления переменных типа NUMBER в разделе объявлений программы используется следующий формат

имя_переменной

NUMBER(точность, масштаб),

где имя_переменной – любая комбинация букв латинского алфавита и цифр, отвечающая правилам формирования идентификаторов языка PL/SQL (см. п.1.3); точность – максимальное общее количество цифр числа; масштаб – количество цифр дробной части числа (см. п.2.1). В следующем примере показано объявление двух переменных – целой A (целое число с точностью не более 10 цифр) и вещественной B:

DECLARE

A number(10);

B number(10,2);

BEGIN

(список команд SQL и PL/SQL)

EXCEPTION

(обработчики исключений)

END;

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

6.2. Символьные типы данных

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

6.2.1. Символьные типы фиксированной длины

Для объявления переменных символьного типа фиксированной длины в разделе объявлений программы используется следующий формат

имя_переменной

CHAR(длина),

где имя_переменной – любая комбинация букв латинского алфавита и цифр, отвечающая правилам формирования идентификаторов языка PL/SQL (см. п.1.3); длина – количество байт символьной строки. Максимальная длина строки равна 32 767 байт, однако максимальная длина типа CHAR в базе данных равна 2000 байт (см. п.2.1). В следующем примере показано объявление переменной типа CHAR:

DECLARE

C char(50);

BEGIN

(список команд SQL и PL/SQL)

EXCEPTION

(обработчики исключений)

END;

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

Для объявления переменных символьного типа переменной длины в разделе объявлений программы используется следующий формат

имя_переменной

VARCHAR2(длина),

где имя_переменной – любая комбинация букв латинского алфавита и цифр, отвечающая правилам формирования идентификаторов языка PL/SQL (см. п.1.3); длина – количество байт символьной строки. Максимальная длина строки равна 32 767 байт, однако максимальная длина типа VARCHAR2 в базе данных составляет 4000 байт (см. п.2.1). В следующем примере показано объявление переменной типа VARCHAR2:

DECLARE

D varchar2(250);

BEGIN

(список команд SQL и PL/SQL)

EXCEPTION

(обработчики исключений)

END;

6.3. Тип данных даты-времени

Для объявления переменных типа даты-времени в разделе объявлений программы используется следующий формат

имя_переменной

DATE,

где имя_переменной – любая комбинация букв латинского алфавита и цифр, отвечающая правилам формирования идентификаторов языка PL/SQL (см. п.1.3). Тип данных DATE используется для хранения информации, связанной со временем, включая даты, часы, минуты и секунды. В следующем примере показано объявление переменной типа DATE:

DECLARE

E date;

BEGIN

(список команд SQL и PL/SQL)

EXCEPTION

(обработчики исключений)

END;

6.4. Логический тип данных

Для объявления переменных логического типа в разделе объявлений программы используется следующий формат

имя_переменной

BOOLEAN,

где имя_переменной – любая комбинация букв латинского алфавита и цифр, отвечающая правилам формирования идентификаторов языка PL/SQL (см. п.1.3). Тип данных BOOLEAN используется для хранения логических значений (TRUE, FALSE и NULL). В следующем примере показано объявление переменной типа DATE:

DECLARE

F boolean;

BEGIN

(список команд SQL и PL/SQL)

EXCEPTION

(обработчики исключений)

END;

6.5. Тип данных %TYPE

Тип данных %TYPE используется в программах PL/SQL для неявного указания типа объявляемой переменной. При этом указывается поле существующей таблицы базы данных и ключевое слово %TYPE. Формат такой ссылки следующий:

имя_переменной

имя_таблицы.имя_поля%TYPE,

где имя_переменной – любая комбинация букв латинского алфавита и цифр, отвечающая правилам формирования идентификаторов языка PL/SQL (см. п.1.3); имя_таблицы – имя таблицы базы даннфх; имя_поля – имя поля таблицы. Такой способ объявления дает возможность объявить переменную того же типа, что и указываемое поле таблицы. Таким образом, тип переменной в программе будет всегда соответствовать типу данных поля таблицы, даже если тип поля будет изменен (например увеличена длина символьного поля).

В следующем примере показано объявление переменной Str с использованием типа %TYPE. Переменная будет иметь точно тот же тип, что тип поля street для хранения названия улицы:

DECLARE

Str my_tab.street%TYPE;

BEGIN

(список команд SQL и PL/SQL)

EXCEPTION

(обработчики исключений)

END;

6.6. Преобразование типов

В PL/SQL можно выполнять преобразования скалярных типов данных, принадлежащим разным семействам типов. В пределах одного семейства типы данных можно преобразовывать произвольно, за исключением ограничений, налагаемых на переменные. Например, переменную типа CHAR(10) нельзя преобразовать в переменную VARCHAR2(5), так как будет недостаточно места для хранения переменной. Из-за ограничение точности и масштаба нельзя преобразовать переменную типа NUMBER(3,2) в переменную типа NUMBER(3) и наоборот. При таких нарушениях компилятор не будет выдавать ошибки, но во время выполнения программы будет зафиксирована ошибка.

6.6.1. Явное преобразование типов

PL/SQL содержит встроенные функции, предназначенные для преобразования типов. В таблице 6.1 показаны енкоторые функции преобразования типов данных.

Таблица 6.1. Функции преобразования типов данных PL/SQL и SQL

Функция | Описание | Семейства типов, допустимых для преобразования
TO_CHAR | Преобразует аргумент к типу VARCHAR2 в зависимости от указанного формата (указание формата необязательно) | Числовые тиы, типы даты
TO_DATE | Преобразует аргумент к типу DATE в зависимости от указанного формата (указание формата необязательно) | Символьные типы
TO_NUMBER | Преобразует аргумент к типу NUMBER в зависимости от указанного формата (указание формата необязательно) | Символьные типы

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

DECLARE

A varchar2(10);

BEGIN

A := TO_CHAR (sysdate, ’dd.mm.yyyy’);

EXCEPTION

(обработчики исключений)

END;

6.6.2. Неявное преобразование типов

В PL/SQL выполняется автоматическое преобразование типов данных указанных ниже семейств, когда это возможно:

- строк символов и чисел

- строк символов и дат

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

DECLARE

D date;

BEGIN

D := ’01.09.2009’;

EXCEPTION

(обработчики исключений)

END;

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

6.7. Порядок вычисления выражений

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

Приоритет операций:

1. вычисления в круглых скобках ( )

2. вычисления значений функций

3. унарные операции not

4. операции типа умножение *, /, mod, and

5. операции типа сложение +, -, or, xor

6. операции сравнения >, <, <=, >=, =, <>

Следует учесть, что в языке PL/SQL логические операции имеют более высокий приоритет, чем операции отношения. В связи с этим, в сложных логических выражениях обычно необходимо расставлять скобки. Если, например, b и с имеют тип INTEGER , то выражение

а = b and с < d

вызовет сообщение о синтаксической ошибке, так как сначала выполнится операция b and с. Правильным будет выражение:

(а = b) and (с < d)

Вопросы для самоконтроля

6.1. Каковы максимальные размеры типов данных CHAR и VARCHAR2 в PL/SQL?

6.2. Что означает тип данных %TYPE?

6.3. Какие функции явного преобразования типов вы знаете?

6.4. Чем отличаются типы данных CHAR и VARCHAR2 языка SQL от этих же типов данных в PL/SQL?

6.5. Чем отличаются тип данных DATE языка SQL от типа данных DATE в PL/SQL?

6.6. Какие значения может хранить переменная типа BOOLEAN?

6.7. Какие типы данных требуют обязательного указания длины при объявлении переменных?

6.8. Возможно ли неявное преобразование строки в дату?

6.9. С помощью какой функции можно преобразовать строку в число?

6.10. Возможно ли неявное преобразование переменной типа NUMBER(5) в переменную типа NUMBER(4,1)?

7. Операторы языка PL/SQL

7.1. Оператор присваивания

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

7.2. Составной оператор

Во многих языках программирования используется составной оператор, который представляет собой совокупность последовательно выполняемых операторов, заключенных в операторные скобки begin … end. В PL/SQL в качестве составного оператора используется блок PL/SQL (см. п.5.1).

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

DECLARE

D date;

BEGIN

D := ’01.09.2009’;

END;

/

7.3. Условный оператор

Реализует алгоритмические конструкции “обход” и “развилка”. Условный оператор типа “обход” имеет синтаксис

IF условие THEN

(операторы SQL или PL/SQL)

END IF,

где условие – любое логическое выражение. Если условие принимает значение TRUE (истина), то выполняются операторы SQL или PL/SQL, располагающиеся между ключевыми словами THEN и END IF. В противном случае эти операторы обходятся и выполняются операторы, следующие за ключевыми словами END IF.

Условный оператор типа “развилка” имеет синтаксис

IF условие THEN

(операторы SQL или PL/SQL)

ELSIF условие THEN

(операторы SQL или PL/SQL)

ELSE

(операторы SQL или PL/SQL)

END IF;

В этой конструкции обязательно выполняется одна из групп операторов – либо операторы после THEN, либо операторы после ELSE. Выполнение операторов SQL или PL/SQL после ключевого слова THEN происходит, если выполняется условие после IF или после ELSIF. Если ни одно из этих условий не является истинным, будут выполнены операторов SQL или PL/SQL, следующие за ELSE.

Обратите внимание на секцию ELSIF (без буквы E после S) – она не является обязательной. Но ее использование позволяет избежать вложения операторов IF друг в друга при реализации сложных условий ветвления.

Примеры.

DECLARE

H number;

BEGIN

select to_char(sysdate,’hh24’) into H from dual;

if (H>=8 AND H<=17) then

update my_tab set timesave=sysdate;

end if;

END;

/

В этом примере командой select считывается текущий час из системной даты sysdate в переменную H. Обратите внимание, что в данной команде select используется виртуальная таблица DUAL. В Oracle всегда доступна эта таблица, которая возвращает только одну строку, а в качестве возвращаемых значений будут получены в результаты вычисления всех выражений и функций, которые присутствуют в списке вывода после ключевого слова select. Форма используемой здесь команды select (с фразой into) рассматривается в параграфе 8. Затем, используя оператор IF, проверяется, попадет ли текущий час в рабочее время. Если это так, то в поле timesave таблицы my_tab записывается текущие дата и время. Если же программа выполняется в нерабочее время (т.е. условие не выполняется), то никаких изменений в базе данных происходит.

DECLARE

H number;

BEGIN

select to_char(sysdate,’hh24’) into H from dual;

if (H>=8 AND H<=12) then

update my_tab set priznak=1;

elsif (H>=13 AND H<=17) then

update my_tab set priznak=2;

else

update my_tab set priznak=null;

end if;

END;

/

В этом примере в переменную H также считывается текущий час. Если его значение попадает в предобеденное рабочее время, то в поле priznak записывается 1. Если значение попадает в послеобеденное рабочее время, то в поле priznak записывается 2. Если выполнение программы происходит в нерабочее время, то в поле priznak записывается пустое значение – null.

7.4. Оператор выбора CASE

Оператор CASE является еще одной конструкцией языка PL/SQL для выполнения условных вычислений по типу “развилка”. PL/SQL предлагает два варианта оператора CASE для управления условными вычислениями. Первый вариант имеет следующий синтаксис

CASE выражение

WHEN значение1 THEN действия;

WHEN значение2 THEN действия;

…

ELSE действия;

END CASE;

Здесь выражение и значениеN – любые выражения, которое поочередно сравнивается на равенство. Если выражение равно значению1, то выполняются действия, указанные после соответствующей фразы THEN, после чего выполнение оператора CASE завершается. В противном случае проверяется, равно ли выражение и значение2, и так далее пока не будут обнаружено равенство выражения и одного из значений. Однако если этого не произойдет ни с одним из значений, то будут выполнены действия, определяемые во фразе ELSE.

Пример.

DECLARE

N number;

BEGIN

H := Last_day(sysdate);

CASE N

WHEN 28 THEN

update my_tab set priznak=1;

WHEN 29 THEN

update my_tab set priznak=1;

WHEN 30 THEN

update my_tab set priznak=2;

ELSE

update my_tab set priznak=3;

END CASE;

END;

/

В примере сначала переменной N присваивается число дней в текущем месяце (применяется функция Last_day к системной дате) . Затем проверяется, если число дней в текущем месяце равно 28 или 29, то текущий месяц – февраль, и в таблицу записывается признак 1. На этом оператор CASE завершается. Если текущий месяц не февраль, то проверяется – является ли текущий месяц месяцем, в котором 30 дней. Если да, то признаку присваивается 2. Если все выполненные проверки не дали результата, то значит текущий месяц имеет 31 день, и признаку присваивается значение 3.

Второй вариант оператора CASE имеет синтаксис

CASE

WHEN выражение1 THEN действия;

WHEN выражение2 THEN действия;

…

ELSE действия;

END CASE;

В данной конфигурации выражение1, выражение1, … выражениеN могут быть любыми, а не только выражениями на равенство. При этом фраза CASE никаких выражений не содержит. Перепишем пример из п. 7.3, используя данный вариант оператора CASE.

DECLARE

H number;

BEGIN

select to_char(sysdate,’hh24’) into H from dual;

case

when (H>=8 AND H<=12) then

update my_tab set priznak=1;

when (H>=13 AND H<=17) then

update my_tab set priznak=2;

else

update my_tab set priznak=null;

end case;

END;

7.5. Оператор простого цикла (цикл LOOP)

Оператор цикла LOOP является основной конструкцией, позволяющей повторять выполнение набора операторов (команд) несколько раз. В этой конструкции нет никакого способа выйти из цикла автоматически. Для выхода используется оператор “EXIT” или “EXIT WHEN (условие)” внутри LOOP. Например:

DECLARE

cnt number;

BEGIN

cnt := 0;

LOOP

INSERT INTO test VALUES(2 * cnt);

cnt := cnt +1;

IF cnt >= 8 THEN

EXIT;

END IF;

END LOOP;

END;

/

7.6. Оператор цикла с параметром (цикл FOR)

В тех случаях, когда нужно повторить набор команд фиксированное число раз, используется конструкция FOR.

Например:

DECLARE

cnt number;

BEGIN

cnt := 0;

FOR cnt IN 1 .. 8 LOOP

INSERT INTO test VALUES(2 * cnt);

END LOOP;

END;

7.7. Оператор цикла с предусловием (цикл WHILE)

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

DECLARE

cnt number;

BEGIN

cnt := 0;

WHILE cnt < 8 LOOP

INSERT INTO test VALUES(2 * cnt);

cnt := cnt +1;

END LOOP;

END;

7.8. Оператор перехода GOTO

Операторы условных (IF-THEN и CASE) и циклических (LOOP, FOR и WHILE) вычислений (см. пп. 7.3–7.7) позволяют разрабатывать программы с нелинейной последовательностью выполнения команд. Используя эти операторы, мы можем перемещаться по программе, обходя группы команд или возвращаясь циклически к уже выполненным команда. Кроме этого PL/SQL предоставляет возможность совершать перемещения по программе с помощью оператора GOTO.

Использовать это оператор при программировании не рекомендуется, но в некоторых случаях GOTO является правильным решением.

Оператор GOTO имеет следующий синтаксис:

GOTO имя_метки;

Имя_метки

– это идентификатор какого-либо места программы, который синтаксически оформляется следующим образом:

<<имя_метки>>

Двойные угловые скобки являются ограничителями метки, имена_меток играют роль указателей, доступных во время выполнения программы. В следующем примере показано, как GOTO используется для обхода участка программы.

BEGIN

DBMS_OUTPUT.PUT_LINE(‘Начало блока’);

GOTO m_last;

DBMS_OUTPUT.PUT_LINE(‘Пропускаемый оператор’);

RETURN ;

<<m_last>>

DBMS_OUTPUT.PUT_LINE(‘Конец блока’);

END;

/

В этом примере используется функция вывода на экран – DBMS_OUTPUT.PUT_LINE (см. 10). В результате выполнения программы получим следующий вывод:

Начало блока

Конец блока

В PL/SQL следует соблюдать следующие правила использования оператора GOTO^

- GOTO не может ссылаться на метку, находящуюся во вложенном блоке;

- нельзя, находясь вне оператора IF, выполнить оператор GOTO, ведущий внутрь этого оператора;

- находясь внутри оператора IF, нельзя выполнить GOTO, ведущий к метке, находящейся внутри другого оператора IF;

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

Вопросы для самоконтроля

7.1. Какие формы условного оператора IF применяются в PL/SQL?

7.2. Какие условные операторы используются в PL/SQL кроме IF?

7.3. Чем отличается оператор LOOP от оператора FOR?

7.4. Как можно выйти из цикла LOOP?

7.5. Возможно ли использование оператора GOTO в программах PL/SQL?

7.6. Какой оператор цикла нужно использовать, если известно необходимое число проходов цикла?

7.7. Как синтаксически оформляется метка, на которую ссылается оператор GOTO?

7.8. Можно ли ссылаться на метку, находящуюся во внешнем блоке?

7.9. Допустимо ли перейти из секции EXCEPTION в секцию BEGIN с помощью оператора GOTO?

7.10. Какой из операторов правильно использовать для выхода их цикла – EXIT или

EXIT WHEN?

8. Работа с базами данных

Основное назначение языка PL/SQL – разработка программ для работы с базами данных (БД) Oracle. Программы PL/SQL могут взаимодействовать с БД только посредством SQL.

8.1. Выборка данных

Для выборки данных, хранящихся в таблицах БД используется команда SELECT … INTO… Эта команда позволяет извлекать из базы данных конкретное значение и помещать его в переменные, которые затем можно использовать в программе. Например:

DECLARE

min_comm number;

max_comm number;

BEGIN

SELECT MIN(comm), MAX(comm) INTO min_comm, max_comm

FROM sal

WHERE city = ‘London’;

END;

/

Данная команда выберет минимальные и максимальные комиссионные, выдаваемые продавцам из Лондона, и поместит результат в переменные min_comm и max_comm. Следует обратить внимание на фразу INTO, после которой перечисляются переменные. Наличие этой фразы отличает команду SELECT языка PL/SQL от команды SELECT языка SQL.

Однако, при использовании команды SELECT … INTO … возникает проблема, если из базы данных будет выбрано несколько строк: будет зафиксировано исключение TOO_MANY_ROWS. Проблема возникнет также, если в БД не окажется ни одной строки, отвечающей заданным условиям выборки. Для обработки запросов, возвращающих несколько строк, приложение должно явно объявить курсор (см. п. 8.2).

8.2. Курсоры

Курсоры являются одним из механизмов, использующих SQL, для обработки данных, хранящихся в БД.

Курсор – это рабочая область SQL-оператора, расположенная в оперативной памяти. Когда приложение посылает SQL-оператор серверу Oracle, сервер открывает по меньшей мере один курсор для обработки этого оператора. Для простых операторов INSERT, UPDATE и DELETE курсоры открываются автоматически. Это также относится к операторам SELECT … INTO, возвращающим одну строку. Такие курсоры называются неявными.

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

8.2.1. Создание и работа с курсорами

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

CURSOR c_sal IS

SELECT sname, comm FROM sal WHERE city = ‘London’;

v_sname sal.sname%TYPE;

v_comm sal.comm%TYPE;

Здесь фраза CURSOR имя_курсора IS используется для определения курсора – в данном примере определяется курсор c_sal. После фразы CURSOR … IS указывается команда SELECT, которая и определяет выбираемые данные. Следует обратить внимание на то, что в определении курсора используется команда SELECT языка SQL, а не команда SELECT … INTO.

После определения курсора для фактического выполнения запроса и извлечения требуемых строк нужно его открыть командой OPEN:

OPEN c_sal;

После открытия курсора данные выбираются (если они существуют в БД) из базы данных и помещаются в курсор. Однако, данные все еще не перемещены в какие-либо переменные для обработки. Для такого перемещения используется следующий шаг с применением оператора FETCH:

FETCH c_sal INTO v_sname, v_comm;

Теперь данные, хранящиеся в первой выбранной из БД строке, перенесены в переменные PL/SQL v_sname, и v_comm., и эти переменные можно использовать в вычислениях, операторах IF … THEN … ELSE и т.п. Когда потребуется выбрать следующую строку, нужно снова выдать оператор FETCH. В завершение работы с курсором его закрывают командой CLOSE:

CLOSE c_sal;

Приведем пример программы, выбирающей с помощью курсора две сроки данных и выводящих их содержимое на экран.

DECLARE

CURSOR c_sal IS

SELECT sname, comm FROM sal WHERE city = ‘London’;

v_sname sal.sname%TYPE;

v_comm sal.comm%TYPE;

BEGIN

FETCH c_sal INTO v_sname, v_comm;

DBMS_OUTPUT.PUT_LINE(‘sname=’|| sname’ v_comm=’|| v_comm);

FETCH c_sal INTO v_sname, v_comm;

DBMS_OUTPUT.PUT_LINE(‘sname=’|| sname’ v_comm=’|| v_comm);

END;

/

Заметим, что выборку нескольких строк из курсора можно производить, используя команды циклов. В следующем примере используется цикл FOR для получения того же результата, что и предыдущем примере программы.

DECLARE

CURSOR c_sal IS

SELECT sname, comm FROM sal WHERE city = ‘London’;

v_sname sal.sname%TYPE;

v_comm sal.comm%TYPE;

BEGIN

FOR i IN 1..2 LOOP

FETCH c_sal INTO v_sname, v_comm;

DBMS_OUTPUT.PUT_LINE(‘sname=’|| sname’ v_comm=’|| v_comm);

END LOOP;

END;

/

Обратите внимание, что переменную цикла i в разделе DECLARE определять не требуется – она оператором FOR определяется автоматически.

8.2.2. Курсорные атрибуты

Для анализа необходимости и успешности выполнения этого этапа существуют четыре встроенные переменные:

%ISOPEN

%FOUND

%NOTFOUND

%ROWCOUNT

Первые три атрибута являются булевскими и могут принимать значения TRUE, FALSE или NULL. Атрибут %ISOPEN показывает, открыт курсор или нет. Атрибут %FOUND показывает, прочитана ли строка из курсора после выполнения команды FETCH. Если %FOUND имеет значение FALSE, то строка не прочитана, и это означает, что в курсоре строк больше нет (все уже выбрано или вообще не было ни одной строки). Атрибут %NOTFOUND является противоположным по смыслу атрибуту %FOUND и введен для удобства.

Атрибут %ROWCOUNT является целочисленным – это счетчик количества прочитанных строк из курсора.

Примеры применения атрибутов показаны в разделе 8.2.3.

8.2.3. Параметры курсора

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

DECLARE

CURSOR c_cust(p_city VARCHAR2) IS

SELECT * FROM cust WHERE city=p_city;

v_cust c_cust%ROWTYPE;

v_city c_cust%TYPE;

BEGIN

v_city := 'London';

OPEN c_cust(v_city);

LOOP

FETCH c_cust INTO v_cust;

EXIT WHEN (c_cust%NOTFOUND);

DBMS_OUTPUT.PUT_LINE(v_cust.cname||' has '||v_cust.rating);

END LOOP;

IF (c_cust%ISOPEN) THEN CLOSE c_cust;

END;

/

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

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

8.2.4. Курсорный цикл FOR

Курсорный цикл FOR автоматически объявляет переменную или запись, куда можно будет считывать строки курсора. В нем также автоматически открывается курсор, а по завершении цикла курсор закрывается. Такой цикл будет пройден столько раз, сколько строк имеется в запросе, определенном в курсоре. В блоке PL/SQL данный цикл может быть определен следующим образом:

DECLARE

CURSOR c_cust(p_city VARCHAR2) IS

SELECT * FROM cust WHERE city=p_city;

BEGIN

v_city := 'London';

FOR v_cust IN c_cust(v_city) LOOP

DBMS_OUTPUT.PUT_LINE(v_cust.cname||' has '||v_cust.rating);

END LOOP;

END;

/

В тех случаях, когда оператор SELECT достаточно прост, курсорный цикл может иметь еще более компактный вид:

BEGIN

FOR v_sal IN (SELECT sname,comm FROM sal) LOOP

DBMS_OUTPUT.PUT_LINE(v_sal.sname||' has '||v_sal.comm);

END LOOP;

END;

/

Компактность, как видно из примера, достигается за счет того, что отдельного объявления курсора не требуется.

8.3. Изменение данных в таблицах

Команды изменения данных (INSERT, UPDATE и DELETE) имеют в PL/SQL точно такой же синтаксис, как и в SQL. Добавляется только возможность использовать переменные наряду с полями таблиц и литералами. В приведенной ниже программе используются те же примеры, что и п. 3.1.2, но в командах вместо явного указания некоторых значений используются переменные.

DECLARE

vName varchar2(20) := ‘Tom’’;

vNum number := 1001;

vAmt number := 100;

BEGIN

INSERT INTO sal

VALUES (1008, vName, NULL, NULL)

UPDATE cust SET rating=rating + 50

WHERE snum = vNum;

DELETE FROM ord WHERE amt <= vAmt;

END;

/

Обратите внимание на то, как определены переменные в разделе DECLARE – им присвоены начальные значения.

8.4. Транзакции

Понятие транзакции в PL/SQL остается таким же, как и SQL (см. п. 3.2). ТРАНЗАКЦИЯ - это логическая единица работы, составленная из одной или нескольких команд SQL. Транзакция - это атомарная единица; результаты всех команд SQL, составляющих транзакцию, должны быть либо все ПОДТВЕРЖДЕНЫ (применены к базе данных), либо все ОТМЕНЕНЫ. Транзакции должны состоять лишь из тех команд SQL, которые в совокупности осуществляют одно согласованное изменение данных.

Транзакция начинается с первой выполняемой команды SQL. Транзакция заканчивается, когда она либо подтверждается, либо отменяется, что может быть сделано явно (командами COMMIT или ROLLBACK) или неявно (когда выдается команда DDL).

Следует добавить, что в PL/SQL транзакция неявно подтверждается, если программа (не содержащая команды COMMIT) завершилась успешно. Транзакция неявно отменяется, если программа (не содержащая команды ROLLBACK) завершилась аварийно.

Вопросы для самоконтроля

8.1. В каких случаях для выбора данных из БД можно использовать команду SELECT … INTO?

8.2. Для чего используется фраза INTO в команде SELECT … INTO?

8.3. Что такое курсор?

8.4. Чем отличается явный курсор от неявного?

8.5. Какая из команда выбора определяется в явном курсоре – SELECT или SELECT … INTO?

8.6. Какие атрибуты курсора вам известны?

8.7. Какой используется оператор для перемещения данных из курсора в переменные программы?

8.8. Можно ли использовать явный курсор для команды UPDATE?

8.9. В каких случаях используется неявный курсор для команды SELECT … INTO?

8.10. Можно ли использовать курсорный цикл FOR без определения явного курсора?

9. Хранимые подпрограммы PL/SQL

Процедуры, функции и пакеты являются именованными блоками, хранящимися в базе данных. Их часто называют единым термином – хранимые процедуры. Они являются объектами базы данных и, как и все объекты БД, имеют имена. Хранимые процедуры могут иметь входные и выходные параметры и могут вызываться из других программ. Как и все другие объекты БД, процедуры, функции и пакеты создаются командой CREATE, а удаляются командой DROP.

9.1. Процедуры

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

CREATE [OR REPLACE] PROCEDURE имя_процедуры

[ (параметр [{IN | OUT | IN OUT}] тип,

…

(параметр [{IN | OUT | IN OUT}] тип)] {IS | AS}

/* раздел объявлений */

BEGIN

/* выполняемы раздел */

EXCEPTION

/* раздел исключительных ситуаций */

END [имя_процедуры];

/

Если сравнить структуру безымянного блока и структуру команда CREATE PROCEDURE, то можно отметить их сходство. Отличием является то, что вместо ключевого слова DECLARE используется набор ключевых слов команды CREATE, и после последнего из этого набора ключевого слова IS (или AS) начинается раздел объявлений.

Однако собственно команда CREATE – непростая конструкция. Команда может содержать необязательную фразу OR REPLACE, Если эта фраза указана, то процедура создается, даже если в БД уже есть процедура с таким же именем. В этом случае вновь создаваемая процедура заместит ту, которая уже есть в БД. Если же фраза OR REPLACE отсутствует, то сначала нужно удалить из БД процедуру с таким же именем командой DROP, а затем выполнять команду CREATE PROCEDURE.

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

Создаваемая процедура может иметь параметры, которые определяются так же, как и переменные в разделе объявлений – имя и тип данных. Но есть два отличия. Первое – это то, что при указании типа данных NUMBER, CHAR или VARCHAR2 не указывается их размер. Второе отличие заключается в указании, является ли параметр входным, выходным или и тем и другим (см. п. 9.5).

Приведем пример создания хранимой процедуры с именем MY_POC и одним входным параметром.

CREATE [OR REPLACE] PROCEDURE my_proc

(p_City IN varchar2) IS

cnt number;

BEGIN

select count(*) into cnt from sal where cyti= p_City;

DBMS_OUTPUT.PUT_LINE(‘count = ’||cnt);

END my_proc;

/

Создаваемая процедура считывает количество продавцов, проживающих в городе, название которого передается в параметре. Затем это количество выводится на экран.

9.2. Функции

Функции очень похожи на процедуры. Функции и процедуры представляют собой различные формы блоков PL/SQL. Однако вызов процедуры сам по себе является оператором PL/SQL, в то время как вызов функции – это часть некоторого выражения. Синтаксис, применяемый при создании хранимой функции, очень похож на синтаксис создания процедуры:

CREATE [OR REPLACE] FUNCTION имя_функции

[ (параметр [{IN | OUT | IN OUT}] тип,

…

(параметр [{IN | OUT | IN OUT}] тип)]

RETURN возвращаемый_тип {IS | AS}

/* раздел объявлений */

BEGIN

/* выполняемы раздел */

EXCEPTION

/* раздел исключительных ситуаций */

END [имя_процедуры];

/

Именно потому, что функция используется как часть выражения, в команде CREATE FUNCTION присутствует фраза RETURN возвращаемый_тип. Этой фразой задается тип возвращаемого функцией значения.

Внутри тела функции оператор RETURN применяется для возврата управления в вызывающую среду с некоторым значением. Общий синтаксис оператора выглядит следующим образом:

RETURN выражение;

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

Во всем остальном команда CREATE FUNCTION аналогична команде CREATE PROCEDURE, описанной в п. 9.2.

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

CREATE [OR REPLACE] FUNCTION my_func

(p_City IN varchar2)

RETURN number IS

cnt number;

BEGIN

select count(*) into cnt from sal where cyti= p_City;

RETURN cnt;

END my_func;

/

9.3. Пакеты

Хранимая процедура – это минимальная программная единица в базе данных Oracle. Однако, существует возможность сгруппировать несколько процедур в пакет. Этот пакет можно считать приложением, которое содержит несколько процедур. Для создания пакета используется следующий формат:

CREATE [OR REPLACE] PACKAGE name IS

(определение общедоступных переменных, процедур и функций)

END [name];

CREATE [OR REPLACE] PACKAGE BODY name IS

(определение локальных переменных, процедур и функций)

(реализация процедур и функций)

[BEGIN

(инициализация)]

END [name];

Здесь определения процедур и функций аналогичны тем, которые создаются командой CREATE PROCEDURE. Но поскольку они уже находятся в пакете, в них не нужно включать слова "CREATE OR REPLACE".

Существует еще одна конструкция, встроенная Oracle в определение пакета. Процедуры и функции, включенные в состав пакета, доступны многим пользователям (при наличии привилегий). Вместе с тем, Oracle позволяет определить защищенные (скрытые) компоненты, используемые только процедурам и функциям из общедоступного раздела. Скрытые процедуры размещаются в так называемом теле пакета (package body), как это показано в следующем примере.

CREATE OR REPLACE PACKAGE score_all

AS

PROCEDURE score_answers

(custid IN NUMBER,

term IN NUMBER DEFAULT 30) ;

END;

CREATE OR REPLACE PAKAGE BODY score_all

AS

PROCEDURE letter_grade

…

IS

(объявления переменных)

BEGIN

(операторы SQL и PL/SQL)

END letter_grade;

PROCEDURE score_answers

(custid IN NUMBER,

term IN NUMBER DEFAULT 30)

IS

(определение переменных)

BEGIN

(операторы SQL и PL/SQL)

END;

END;

9.4. Области действия и область видимости переменных

Область действия переменных – это фрагмент программы, в котором возможно обращение к этой переменной. Для переменной PL/SQL – это фрагмент с момента ее объявления и до конца блока. Это показано в следующем примере.

DECLARE -- начало внешнего блока

A number;

BEGIN

DECLARE -- начало внутреннего блока

B number;

BEGIN

END; -- конец внутреннего блока

END; -- конец внешнего блока

Здесь переменная A имеет область действия и во внешнем блоке и во внутреннем. Для переменной B областью действия является только внутренний блок.

Область видимости переменной – это фрагмент программы, в котором возможно обращение к этой переменной без дополнительных квалификаторов. Область видимости всегда лежит в пределах области действия; если переменная находится вне области своего действия, она невидима. Рассмотрим следующий пример.

DECLARE -- начало внешнего блока

A number;

C date;

BEGIN -- 1

DECLARE -- начало внутреннего блока

B number;

C char(2);

BEGIN -- 2

END; -- конец внутреннего блока

-- 3

END; -- конец внешнего блока

В этой программе в точке 1 видимы переменные A и C типа date. В точке 2 видимы переменные A, B и C типа char(2), а переменная C типа date не видна хотя и находится в области своего действия. Дело в том, что объявленная во внутреннем блоке переменная C (т.е. переменная с таким же именем) перекрывает видимость переменной C, определенной во внешнем блоке. В точке 3 видимы переменные A и C типа date, которая снова становится видна, т.к. закончилась область действия переменной C типа char(2).

Если переменная находится в своей области действия, но не видна – то как обратиться к ней в программе? Рассмотрим пример, в котором программа из предыдущего примера несколько изменена.

<<bl_outer>>

DECLARE -- начало внешнего блока

A number;

C date;

BEGIN -- 1

DECLARE -- начало внутреннего блока

B number;

C char(2);

BEGIN -- 2

END; -- конец внутреннего блока

-- 3

END; -- конец внешнего блока

В этой программе внешний блок является помеченным меткой bl_outer. Используя эту метку перед переменной, можно во внутреннем блоке обратиться к переменной C типа date – благодаря использованию метки она становится видна..

9.5. Параметры процедур и функций

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

Таблица 1. Виды параметров

Вид | Описание
IN | Значение фактического параметра передается в процедуру при ее вызове. Внутри процедуры формальный параметр рассматривается в качестве константы PL/SQL – параметра только для чтения – и не может быть изменен. Когда процедура завершается и управление программой возвращается в вызывающую среду, фактический параметр не изменяется.
OUT | Любое значение, которое имеет фактический параметр при вызове процедуры, игнорируется. Внутри процедуры формальный параметр рассматривается как неинициированная переменная PL/SQL, то есть содержит NULL-значение, и можно как записать в него значение, так и считать значение из него. Когда процедура завершается и управление программой возвращается в вызывающую среду, содержимое формального параметра присваивается фактическому параметру (в Oralce 8i этот режим можно изменить с помощью модификатора NOCOPY, позволяющего применять параметр по ссылке).
IN OUT | Этот вид представляет комбинацию видов IN и OUT. Значение фактического параметра передается в процедуру при ее вызове. Внутри процедуры формальный параметр рассматривается в качестве инициализированной переменной, и можно как записать в него значение, так и считать значение из него. Когда процедура завершается и управление программой возвращается в вызывающую среду, содержимое формального параметра присваивается фактическому параметру (в Oralce 8i этот режим можно изменить с помощью модификатора NOCOPY, как и для параметра OUT).

При описании процедур запрещается указывать длину параметров типа CHAR и VARCHAR2, а также точность и/или масштаб параметров типа NUMBER, поскольку ограничения принимаются от фактических параметров. Единственным способом наложить ограничения на формальные параметры является использование атрибута %TYPE. В этом случае ограничение распространяется не на фактический параметр, а на формальный.

CREATE OR REPLACE PROCEDURE myProc

(p_Par1 IN OUT cust.rating%TYPE,

p_Par2 OUT VARCHAR2 DEFALT NULL) IS

BEGIN

p_Par1 := 250;

END myProc;

/

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

Считывание данных из параметра OUT нельзя в Oracle версий, предшествующих Oracle 8i v.8.0.4 (за исключением v 7.3.4).

Если в процедуре или функции нет параметров, то не нужно указывать круглые скобки ни в объявлении подпрограммы, ни в ее вызове.

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

CREATE OR REPLACE PROCEDURE Proc_1 IS

nRtg NUMBER (4) := 120;

vVal VARCHAR(20) := ‘Tokyo’;

BEGIN

MyProc(nRtg, vVal);

END myProc;

/

При именованном представлении для каждого аргумента указывается как формальный, так и фактический параметры. Это позволяет при желании установить собственный порядок аргументов.

CREATE OR REPLACE PROCEDURE Proc_1 IS

nRtg NUMBER (4) := 120;

vVal VARCHAR(20) := ‘Tokyo’;

BEGIN

MyProc(p_Par2 => vVal, p_Par1 => nRtg);

END myProc;

9.6. Триггеры

Триггеры являются именованными блоками PL/SQL и этим похожи на процедуры и функции. Но есть и существенные отличия. Триггеры всегда хранятся в БД как автономные объекты, не имеют аргументов и не могут входить в состав других подпрограмм и пакетов. Вызов триггеров из других программ невозможен – они запускаются автоматически, когда происходит запускающие их события. Событиями, запускающими триггеры, являются команды DML (INSERT, UPDATE, DELETE). Системные события (например, запуск базы данных) и некоторые команды DDL.
Назначения триггеров могут быть различными, включая следующие:

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

Различают три основных типа триггеров: триггеры DML, триггеры замещения (instead-of) и системные триггеры. Как и другие объекты БД триггеры (независимо от их типа) создаются командой CREATE, изменяются командой ALTER и удаляются командой DROP.

9.6.1. Триггеры DML

Триггеры DML создаются для таблиц БД и активизируются командами DML (INSERT, UPDATE, DELETE), выполняемыми над таблицами. Срабатывание триггера может выполняться до или после выполнения команды (BEFOR и AFTER). При этом триггер может срабатывать один раз при выполнении команды DML (триггер уровня команды) или срабатывать на каждую строку, обрабатываемую командой (триггер уровня строки). Режим обработки каждой строки определяется наличием фразы FOR EACH ROW в команде создания триггера.
Синтаксис команды создания триггера DML следующий:

CREATE [OR REPLACE] TRIGGER имя_триггера
{BEFORE | AFTER } активизирующее_событие
[конструкция_REFERENCING]
[WHEN условие_срабатывания]
[FOR EACH ROW]
тело_триггера;

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

Список_команд_DML[ FOR список_полей ] ON имя_таблицы

Если в писок_команд_DML входит более одной команды, то они разделяются оператором OR. В этом случае для того, чтобы определить тип выполняемой команды, используются логические функции INSERTING, UPDATING и DELETING.
В строковых триггерах (триггерах уровня строки) для обращения к имеющимся (старым) и новым значениям полей строки используются идентификаторы корреляции – :old и :new. Компилятор PL/SQL рассматривает их как записи типа таблицы (%ROWTYPE), указанной в определении триггера. Поэтому используя, например, ссылку :new.поле можно «узнать» новое значение указанного поля. Конструкция_REFERENCINGиспользуется для замены идентификаторов :old и :new на какие-либо другие имена и имеет синтаксис

REFERENCING[ OLD AS имя_старого ] [ NEW AS имя_нового ].

Условие_срабатывания во фразе WHEN может присутствовать только в строковых триггерах и проверяется для каждой обрабатываемой строки. В итоге триггер будет срабатывать только для тех строк, для которых это условие истинно. В условии срабатывания можно ссылаться на записи :old и :new, но только без использования двоеточий.
Тело_триггера - это блок PL/SQL, который должен содержать, по крайней мере, секцию выполнения команд. Если имеется секция объявлений, то тело триггера должно начинаться ключевым словом DECLARE.
В теле триггера можно использовать процедуру
raise_application_error(код, сообщение_об_ошибке)
для отмены DML-операции, вызвавшей срабатывание триггера. Обычно это используется после проверки какого-то условия, и если оно не выполняется, то триггер запрещает выполнение DML-команды. В процедуре raise_application_error код – это отрицательное целое число в диапазоне от -20000 до -20999, а сообщение_об_ошибке – это сообщение, которое будет появляться на экране. Например, процедура может быть вызвана так: raise_application_error(-20001, ‘Сегодня изменения данных запрещены’), если Вы создаете триггер, который проверяет – не является ли текущая дата 31 декабря.
В приведенном ниже примере создается строковый триггер, предназначенный для формирования первичного ключа (поле snum) при вставке строки в таблицу Sal. В теле триггера используется последовательность SQ_Sal, которая предварительно должна быть создана.

CREATE OR REPLACE TRIGGER Gen_Sal_Snum
BEFORE INSERT ON Sal
FOR EACH ROW
BEGIN
SELECT SQ_Sal.NEXTVAL INTO :new.snum FROM dual;
END Gen_Sal_Snum;

9.6.2. Триггеры замещения

Триггеры DML, рассмотренные в предыдущем разделе, выполняются как дополнение к операциям INSERT, UPDATE и DELETE. Назначение триггеров замещения несколько иное – они активизируются для выполнения действий вместо операций DML. Эти триггеры создаются только для представлений. Синтаксис их создания следующий

CREATE [OR REPLACE] TRIGGER имя_триггера
INSTEAD OF активизирующее_событие
[конструкция_REFERENCING]
[WHEN условие_срабатывания]
[FOR EACH ROW]
тело_триггера;

В отличие от триггеров DML команда CREATE создания триггеров замещения содержит ключевые слова INSTEAD OF вместо BEFORE или AFTER. Все остальные компоненты имеют тот же смысл, что и в команде создания триггеров DML. Следует отметить, что предложение FOR EACH ROW не является обязательным для триггеров замещения. Эти триггеры являются строковыми независимо от того, указано FOR EACH ROW или нет.

9.6.3. Системные триггеры

Системные триггеры активизируются событиями двух видов: выполнение команд DDL и события базы данных. И те и другие события изменяют БД. Команды DDL (CREATE, ALTER или DROP) создают, изменяют или удаляют объекты базы данных. К событиям БД относятся запуск/останов сервера, регистрация/отключение пользователя и ошибки сервера. Синтаксис создания триггеров данного типа имеет следующий вид:

CREATE [OR REPLACE] TRIGGER имя_триггера
{BEFORE | AFTER } {список_событий_DDL |список событий_базы_данных}
ON { DATABASE | [схема.]SCHEMA}
[WHEN условие_срабатывания]
тело_триггера;

где список_событий_DDL – одна или несколько команд DDL, разделенных ключевым словом OR, а список событий_базы_данных – одно или несколько событий базы данных (также разделенных ключевым словом OR) .

Вопросы для самоконтроля

9.1. Почему хранимые процедуры называются “хранимыми”?

9.2. Где хранятся хранимые процедуры?

9.3. Можно ли хранимые процедуры вызывать из других программ?

9.4. Сколько операторов RETURN может быть помещено в тело функции?

9.5. Какие виды параметров хранимых процедур вам известны?

9.6. Требуется ли для параметров типа CHAR и VARCHAR2 в хранимых процедурах указывать длину?

9.7. Если переменная определена во внутреннем блоке, распространяется ли область ее действия на внешний блок?

9.8. Если переменная определена во внешнем блоке, всегда ли она видна во внутреннем блоке?

9.9. Можно ли использовать тип %TYPE для параметров хранимых процедур?

9.10. Чем отличаются триггеры от подпрограмм?

10. Стандартные пакеты PL/SQL

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

10.1. Пакет DBMS_OUTPUT

Пакет DBMS_OUTPUT очень удобен при отладке программ. У него имеется ограничение на длину выходной строки, которая не должна превышать 255 символов. Однако использование стандартной функции SUBSTR позволяет обойти это ограничение, что будет продемонстрировано в примерах.

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

BEGIN

DBMS_OUTPUT. PUT_LINE (‘Hello, World!’);

END;

/

Следующий пример показывает, как с помощью функции SUBSTR выводить строки длиной более 255 символов. Функция SUBSTR возвращает подстроку заданной длины. Она имеет три аргумента: 1) строка, из которой выбирается подстрока; 2) начальная позиция подстроки в строке и 3) длина подстроки.

DECLARE

v_string VARCHAR2(500);

v_length NUMBER(10);

BEGIN

SELECT text, text_length INTO v_string, v_length

FROM dba_views

WHERE view_name = ‘DBA_COL_PRIVS’;

DBMS_OUTPUT.PUT_LINE( ‘View DBA_COL_PRIVS is ’ || v_length || ‘ bytes’ );

DBMS_OUTPUT.PUT_LINE( ‘TEXT’ );

DBMS_OUTPUT.PUT_LINE( ‘====’ );

DBMS_OUTPUT.PUT_LINE( SUBSTR(v_string, 1, 250) );

DBMS_OUTPUT.PUT_LINE( SUBSTR(v_string, 251, 250) );

END;

/

Из примера легко понять, как строка v_string длиной 500 символов выводится двумя подстроками по 250 символов каждая.

11. Интегрированная среда Oracle 10g XE

11.1 Как начать работу с Oracle 10g XE

Прежде чем начать работу с СУБД Oracle 10g XE, ее нужно установить на компьютере. Процесс установки (инсталляции) описан в файле Инсталляция.doc. Если вы правильно выполнили все действия, описанные в этом файле, то инсталляция завершится открытием Web-браузера с окном регистрации, как показано на рис. 1.

Рис. 1.

Если вы решили начать работу позднее, а не сразу после установки, то для открытия окна регистрации выполните следующие действия: кнопка Пуск | Программы | Oracle Database 10g Express Edition | Go to Database Home Page.

В окне регистрации введите логин SYSTEM и тот пароль, который вы вводили при установке Oracle 10g XE. Появится окно с компонентами домашней страницы, показное на рис. 2. Щелкните мышкой по стрелке на пиктограмме SQL и выпадающем меню выберите пункт “SQL Commands”, а затем подпункт “Enter Command” (см. рис. 3)

Рис. 2.

Рис. 3.

После выбора “Enter Command” откроется окно SQL Commands, показное на рисунке 4. В этом окне можно выполнять команды SQL и блоки PL/SQL. Для ознакомления с технологией работы обратитесь к файлу “Методические_указания.doc”.

Рис. 4.

11.2 Ваша первая программа

Ваша первая программа – программа вывода приветствия – продемонстрирует работу в среде Oracle 10g XE. Введите в окне ввода текст следующей программы

BEGIN

DBMS_OUTPUT. PUT_LINE (‘Hello, World!’);

END;

/

Убедитесь, что текст “Hello, World!” в вашей программе заключен в прямые апострофы, и щелкните мышкой по кнопке Run. В окне вывода должно появиться ваше приветствие, как показано на рисунке 5.

Рис. 5.

Ответы на вопросы для самоконтроля

1.1. В виде двумерных таблиц.

1.2. Пользователь не задает физическую структуру реляционной базы данных.

1.3. Пустые значения (NULL) в реляционных БД означают, что они не определены, или неизвестны.

1.4. Язык SQL.

1.5. Язык SQL относится к типу непроцедурных, декларативных языков.

1.6. Язык SQL обрабатывает данные наборами строк, а не отдельными строками.

1.7. Таблицы.

1.8. Идентификаторы в Oracle 10g Express Edition могу иметь длину не более 30 символов, состоять из букв латинского алфавиты и цифр и начинаться с буквы. Могут использоваться также символы $, # и _.

1.9. В системных таблицах Oracle 10g Express Edition содержится информация о всех объектах базы данных.

1.10. Служебные слова нельзя использовать в качестве имен объектов.

2.1. Точность в числовых типах данных – это общее количество цифр, а масштаб – количество цифр дробной части числа.

2.2. 38 цмфр.

2.3. CHAR и VARCHAR2.

2.4. CHAR – это строковый тип фиксированной длины, а VARCHAR2 – строковый тип переменной длины.

2.5. 4000 байтов.

2.6. Все элементы даты хранятся в столбцах типа дата-время – век, год, месяц, день, час, минута или секунда?

2.7. Функция TO_DATE.

2.8. Функция TO_CHAR.

2.9. Да.

2.10. Да.

3.1. SELECT, INSERT, UPDATEи DELETE.

3.2. Опция GROUP BY необходима в команде SELECT, когда агрегатная функция вычисляется для группы строк, а не для всех строк таблицы.

3.3. Операторам IN задает произвольное множество значений, а BETWEEN – диапазон значений, включая границы диапазона.

3.4. Команда ROLLBACK относится к группе команд управления транзакциями.

3.5. Команда COMMIT фиксирует в БД выполненные изменения данных.

3.6. Язык определения данных.

3.7. Сходство в том, что UPDATE и ALTER выполняют изменения в БД. Различие в том, что UPDATE изменяет данные в таблицах, а ALTER изменяет объекты БД, в том числе и таблицы.

3.8. Последовательность – это генератор неповторяющихся целых чисел.

3.9. Представление – это объект БД, содержащий команду SELECT, к которому можно обратиться по имени.

3.10. GRANT, REVOKE.

5.1. DECLARE, EXCEPTION.

5.2. Безымянный блок PL/SQL не хранится в БД, не имеет параметров, не может быть вызван из другой программы.

5.3. Процедуры, функции, пакеты.

5.4. В разделе DECLARE.

5.5. Обработчик ошибок – это секция раздела EXCEPTION, которая начинается ключевым словом WHEN и в которой выполняется обработка ошибок определенного типа.

5.6. При возникновении ошибки программы во время ее выполнения.

5.7. Безымянный блок нельзя вызвать из другой программы.

5.8. В любой последовательности, за исключением обработчика OTHERS – этот обработчик должен быть последним.

5.9. Обработчик OTHERS используется, если ошибка не обработана никакими другими обработчиками или если других обработчиков нет.

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

6.1. Максимальные размеры типов данных CHAR и VARCHAR2 в PL/SQL – 32767 байтов.

6.2. Тип данных %TYPE – это ссылочный тип: ссылается на тип указанного поля таблицы.

6.3. TO_NUMBER, TO_CHAR, TO_DATE.

6.4. Типы данных CHAR и VARCHAR2 языка SQL имеют максимальную длину 2000 и 4000 байтов соответственно, а эти же типов данных в PL/SQL имеют максималбную длину 32767 байтов.

6.5. Ничем.

6.6. Переменная типа BOOLEAN может хранить значения TRUE, FALSE, NULL.

6.7. CHAR и VARCHAR2.

6.8. Да.

6.9. С помощью функции TO_NUMBER.

6.10. Неявное преобразование переменной типа NUMBER(5) в переменную типа NUMBER(4,1) невозможно из-за различий в размере целой части числа.

7.1. 1) IF THEN – END IF, 2) IF – THEN – [ELSIF] – ELSE – END IF.

7.2. Оператор CASE.

7.3. Оператор LOOP – бесконечный цикл, а оператора FOR – цикл с заданным числом проходов.

7.4. Выйти из цикла LOOP можно, используя операторы EXIT и EXIT WHEN.

7.5. Да.

7.6. Оператор цикла FOR.

7.7. Синтаксически метка оформляется как <<имя метки>>.

7.8. Можно.

7.9. Нет.

7.10. Оба оператора – EXIT и

EXIT WHEN.

8.1. Для выбора данных из БД можно использовать команду SELECT … INTO, если она извлекает только одну строку.

8.2. Фраза INTO в команде SELECT … INTO используется для перечисления переменных, в которые будут помещаться извлеченные из БД значения.

8.3. Курсор – это область памяти, которая выделяется для выполнения команд SELECT, INSERT, UPDAET или DELETE.

8.4. Явный курсор объявляется программистом для выборки из таблиц БД произвольного числа строк данных, а неявный курсор создается автоматически при выполнении команд SELECT…INTO, INSERT, UPDAET или DELETE.

8.5. SELECT.

8.6. %ISOPEN, %FOUNT, %NOTFOUND, %ROWCOUNT.

8.7. Оператор FETCH.

8.8. Нет.

8.9. Неявный курсор для команды SELECT … INTO создается, если команда выбирает из БД только одну строку.

8.10. Можно, если команду SELECT указать в самом операторе курсорного цикла FOR.

9.1. Хранимые процедуры называются “хранимыми”, потому что они хранятся в базе данных как объекты.

9.2. Хранимые процедуры хранятся в базе данных.

9.3. Хранимые процедуры можно вызывать из других программ.

9.4. Любое количество операторов RETURN может быть помещено в тело функции.

9.5. Виды параметров хранимых процедур – IN, OUT и IN OUT.

9.6. Не требуется.

9.7. Нет, не распространяется.

9.8. Не всегда. Она не вида, если во внутреннем блоке определена переменная с таким же именем.

9.9. Можно использовать тип %TYPE для параметров хранимых процедур.

9.10. Триггеры не могут быть вызваны из программы, они запускаются на выполнение автоматически при наступлении запускающего события. Триггеры не имеют параметров.

Рекомендуемая литература

Настоящий конспект лекций содержит весь необходимый теоретический и практический материал для выполнения лабораторных работ, контрольной работы и получения зачета по дисциплине “Сетевые базы данных”. Однако в случае непонимания каких-либо моментов при изучении дисциплины желательно воспользоваться литературой из приведенного ниже списка.

1. Стив Бобровский. Oracle Database 10g XE для Windows. Эффективное использование: Пер. с англ. – “Лори”, 2009. – 486 с.

2. Джейсон Прайс. SQL для Oracle 10g. Пер. с англ. – “Лори”, 2007. – 566 с.

3. Скотт Урман, Рон Хардман, Майкл МакЛафлин. Oracle Database 10g. Программирование на языке PL/SQL. Пер. с англ. – “Лори”, 2007. – 792 с.

наверх

назад