Основы Oracle PL/SQL (часть 1)

1.

БАЗЫ ДАННЫХ
Основы Oracle PL/SQL (часть 1)
Основы использования регулярных выражения в СУБД
Oracle
Преподаватель Савченко Н.А.

2.

Основы Oracle PL/SQL часть 1
PL/SQL
• PL/SQL - это вариант языка SQL,
дополненный возможностями
процедурных языков программирования
• Команды языка SQL для изменения и
запросов данных включены в
процедурные единицы кода
Российский государственный университет нефти и газа (НИУ) имени И.М.Губкина
ФАКУЛЬТЕТ КОМПЛЕКСНОЙ БЕЗОПАСНОСТИ ТЭК
2

3.

Основы Oracle PL/SQL часть 1
Среда PL/SQL
Машина PL/SQL
Блок
PL/SQL
Блок
PL/SQL
PL/SQL
SQL
Исполнитель
процедурных
операторов
Исполнитель команд SQL
Сервер Oracle
Российский государственный университет нефти и газа (НИУ) имени И.М.Губкина
ФАКУЛЬТЕТ КОМПЛЕКСНОЙ БЕЗОПАСНОСТИ ТЭК
3

4.

Основы Oracle PL/SQL часть 1
Структура блока PL/SQL
DECLARE (необязательно)
переменные, курсоры,
пользовательские исключения
BEGIN (обязательно)
- Команды SQL
- Команды PL/SQL
EXCEPTION (необязательно)
Действия при возникновении ошибки
END; (обязательно)
Российский государственный университет нефти и газа (НИУ) имени И.М.Губкина
ФАКУЛЬТЕТ КОМПЛЕКСНОЙ БЕЗОПАСНОСТИ ТЭК
4

5.

Основы Oracle PL/SQL часть 1
Выполнение команд и блоков PL/SQL
DECLARE
v_variable VARCHAR2(5);
BEGIN
SELECT имя_столбца
INTO v_variable
FROM имя таблицы;
EXCEPTION
WHEN имя_исключения THEN
. . .
END;
Российский государственный университет нефти и газа (НИУ) имени И.М.Губкина
ФАКУЛЬТЕТ КОМПЛЕКСНОЙ БЕЗОПАСНОСТИ ТЭК
5

6.

Основы Oracle PL/SQL часть 1
Типы блоков
Анонимный блок
Процедура
Функция
[DECLARE]
PROCEDURE имя
IS
FUNCTION имя
RETURN тип_данных
IS
BEGIN
BEGIN
[EXCEPTION]
[EXCEPTION]
BEGIN
--команды
RETURN значение;
[EXCEPTION]
END;
END;
END;
—команды
—команды
Российский государственный университет нефти и газа (НИУ) имени И.М.Губкина
ФАКУЛЬТЕТ КОМПЛЕКСНОЙ БЕЗОПАСНОСТИ ТЭК
6

7.

Основы Oracle PL/SQL часть 1
Программные конструкции
Конструкции сервера базы данных
Анонимные блоки
Хранимые процедуры
Хранимые функции
Хранимые пакеты
Триггеры базы данных
Объектные типы
Российский государственный университет нефти и газа (НИУ) имени И.М.Губкина
ФАКУЛЬТЕТ КОМПЛЕКСНОЙ БЕЗОПАСНОСТИ ТЭК
7

8.

Основы Oracle PL/SQL часть 1
Переменные в PL/SQL
Используются для:
• Временного хранения данных
• Манипулирования хранимыми значениями
• Повторного использования
• Простоты сопровождения
Российский государственный университет нефти и газа (НИУ) имени И.М.Губкина
ФАКУЛЬТЕТ КОМПЛЕКСНОЙ БЕЗОПАСНОСТИ ТЭК
8

9.

Основы Oracle PL/SQL часть 1
Работа с переменными в PL/SQL
• Объявление и инициализация переменных в
декларативной секции
• Присвоение новых значений переменным в
исполняемой секции
• Передача значений в блоки PL/SQL с помощью
параметров
• Просмотр результатов с помощью выходных
переменных
Российский государственный университет нефти и газа (НИУ) имени И.М.Губкина
ФАКУЛЬТЕТ КОМПЛЕКСНОЙ БЕЗОПАСНОСТИ ТЭК
9

10.

Основы Oracle PL/SQL часть 1
Типы переменных
• Переменные PL/SQL
- Скалярные
- Составные
- Ссылочные
- LOB (большие объекты)
• Прочие переменные
Российский государственный университет нефти и газа (НИУ) имени И.М.Губкина
ФАКУЛЬТЕТ КОМПЛЕКСНОЙ БЕЗОПАСНОСТИ ТЭК
10

11.

Основы Oracle PL/SQL часть 1
Типы переменных
Типы переменных
• BOOLEAN- логическое значение
• DATE - для работы с датами
• NUMBER - числовые данные с точностью
и масштабом
• VARCHAR2 - для работы с текстовой
информацией
• BLOB - большие объекты
Российский государственный университет нефти и газа (НИУ) имени И.М.Губкина
ФАКУЛЬТЕТ КОМПЛЕКСНОЙ БЕЗОПАСНОСТИ ТЭК
11

12.

Основы Oracle PL/SQL часть 1
Объявление переменных PL/SQL
Синтаксис
идентификатор [CONSTANT] тип_данных
[NOT NULL] [:= | DEFAULT выражение];
Примеры
DECLARE
v_hiredate
v_depno
v_location
c_comm
DATE;
NUMBER(2) NOT NULL :=10;
VARCHAR2(13) :=‘Atlanta’;
CONSTANT NUMBER :=1400;
Российский государственный университет нефти и газа (НИУ) имени И.М.Губкина
ФАКУЛЬТЕТ КОМПЛЕКСНОЙ БЕЗОПАСНОСТИ ТЭК
12

13.

Основы Oracle PL/SQL часть 1
Рекомендации по
объявлению переменных PL/SQL
• Следуйте правилам присвоения имен
• Инициализируйте переменные, описанные как NOT
NULL, и константы
• Объявляйте не более одного идентификатора на строку
кода
• Инициализируйте идентификаторы с помощью
оператора присваивания (:=) или зарезервированного
слова DEFAULT
Российский государственный университет нефти и газа (НИУ) имени И.М.Губкина
ФАКУЛЬТЕТ КОМПЛЕКСНОЙ БЕЗОПАСНОСТИ ТЭК
13

14.

Основы Oracle PL/SQL часть 1
Правила присвоения имен
• Две переменые могут иметь одинаковые имена, если находятся в
разных блоках.
• Имена (идентификаторы) переменных не должны совпадать с
именами столбов таблицы, используемых в этом блоке.
DECLARE
employee_id NUMBER(6);
Примените правила именования к
BEGIN
идентификаторам PL/SQL:
SELECT
employee_id
например, v_employee_id
INTO
employee_id
FROM
employee
WHERE
last_name = ‘Kochhar’;
END;
Российский государственный университет нефти и газа (НИУ) имени И.М.Губкина
ФАКУЛЬТЕТ КОМПЛЕКСНОЙ БЕЗОПАСНОСТИ ТЭК
14

15.

Основы Oracle PL/SQL часть 1
Инициализация переменных и ключевых
слов
• Оператор присваивания (:=)
• Ключевое слово DEFAULT
• Ограничение NOT NULL
Синтаксис
идентификатор := выражение;
Примеры:
v_hiredate := ’01.01.2001’;
v_ename := ‘Maduro’;
Российский государственный университет нефти и газа (НИУ) имени И.М.Губкина
ФАКУЛЬТЕТ КОМПЛЕКСНОЙ БЕЗОПАСНОСТИ ТЭК
15

16.

Основы Oracle PL/SQL часть 1
Основные скалярные типы данных
• CHAR [(максимальная_длина)]
• VARCHAR2 (максимальная_длина)
• LONG
• NUMBER [(точность, масштаб)]
• BOOLEAN
• DATE
• BINARY_INTEGER
Российский государственный университет нефти и газа (НИУ) имени И.М.Губкина
ФАКУЛЬТЕТ КОМПЛЕКСНОЙ БЕЗОПАСНОСТИ ТЭК
16

17.

Основы Oracle PL/SQL часть 1
Основные скалярные типы данных (пример)
DECLARE
v_job varchar2(9);
v_count
BINARY_INTEGER :=0;
v_ttl_sal NUMBER(9,2) :=0;
v_ordate
DATE := sysdate + 7;
c_txrate
CONSTANT NUMBER(3,2) := 8,25;
v_valid
BOOLEAN NOT NULL := TRUE;
Российский государственный университет нефти и газа (НИУ) имени И.М.Губкина
ФАКУЛЬТЕТ КОМПЛЕКСНОЙ БЕЗОПАСНОСТИ ТЭК
17

18.

Основы Oracle PL/SQL часть 1
Атрибут %TYPE
• Используется для объявления переменной:
- На основе столбца базы данных
- На основе уже объявленной переменной
• Перед %TYPE указываются:
- Имена таблицы и столбца базы данных
- Имя уже объявленной переменной
Российский государственный университет нефти и газа (НИУ) имени И.М.Губкина
ФАКУЛЬТЕТ КОМПЛЕКСНОЙ БЕЗОПАСНОСТИ ТЭК
18

19.

Основы Oracle PL/SQL часть 1
Объявление переменных с атрибутом
%TYPE
Синтаксис:
идентификатор таблица.имя_столбца%TYPE;
Пример:
...
v_name
employees.last_name%TYPE;
v_balance
NUMBER(7,2);
v_min_balance
v_balance%TYPE := 10;
...
19
Российский государственный университет нефти и газа (НИУ) имени И.М.Губкина
ФАКУЛЬТЕТ КОМПЛЕКСНОЙ БЕЗОПАСНОСТИ ТЭК
19

20.

Основы Oracle PL/SQL часть 1
Объявление логических (булевых)
переменных
Булевой переменной може быть присвоено только значение
TRUE, FALSE или NULL
• Переменные связываются логическими операторами AND,
OR и NOT
• Переменные всегда возвращают значения TRUE, FALSE,
NULL.
• Возвращать булево значение могу арифметические,
символьные выражения и выражения даты.
Российский государственный университет нефти и газа (НИУ) имени И.М.Губкина
ФАКУЛЬТЕТ КОМПЛЕКСНОЙ БЕЗОПАСНОСТИ ТЭК
20

21.

Основы Oracle PL/SQL часть 1
Составные типы данных
TABLE
RECORD
NESTED
TABLE
VARRAY
Российский государственный университет нефти и газа (НИУ) имени И.М.Губкина
ФАКУЛЬТЕТ КОМПЛЕКСНОЙ БЕЗОПАСНОСТИ ТЭК
21

22.

Основы Oracle PL/SQL часть 1
DBMS_OUTPUT.PUT_LINE
• Процедура пакета, поставляемого вместе с Oracle
• Альтернативный способ вывода данных из блока PL/SQL
• Для включения вывода в SQL*Plus введите
SET SERVEROUTPUT ON
DECLARE
v_sal NUMBER(9,2) := 60000;
BEGIN
v_sal := v_sal/12;
DBMS_OUTPUT.PUT_LINE (‘The months
salary is ‘||
TO_CHAR(v_sal));
END;
Российский государственный университет нефти и газа (НИУ) имени И.М.Губкина
ФАКУЛЬТЕТ КОМПЛЕКСНОЙ БЕЗОПАСНОСТИ ТЭК
22

23.

Основы Oracle PL/SQL часть 1
Синтаксис блока PL/SQL и указание по
написанию команд
• Команда может занимать несколько строк
• Лексические единицы могут разделяться пробелами:
- Ограничители
- Идентификаторы
- Литералы
- Комментарии
Российский государственный университет нефти и газа (НИУ) имени И.М.Губкина
ФАКУЛЬТЕТ КОМПЛЕКСНОЙ БЕЗОПАСНОСТИ ТЭК
23

24.

Основы Oracle PL/SQL часть 1
Ограничители (простые символы)
Это простые или составные символы,
имеющие особое значение для PL/SQL
Символ
Значение
+
Оператор сложения
-
Оператор вычитания/отрицания
*
Оператор умножения
/
Оператор деления
=
Оператор отношения
;
Символ конца строки
Российский государственный университет нефти и газа (НИУ) имени И.М.Губкина
ФАКУЛЬТЕТ КОМПЛЕКСНОЙ БЕЗОПАСНОСТИ ТЭК
24

25.

Основы Oracle PL/SQL часть 1
Ограничители (составные символы)
Символ
Значение
<>
Оператор сложения
!=
Оператор вычитания/отрицания
||
Оператор умножения
--
Оператор деления
/*
Оператор отношения
*/
Символ конца строки
:=
Оператор присваивания
Российский государственный университет нефти и газа (НИУ) имени И.М.Губкина
ФАКУЛЬТЕТ КОМПЛЕКСНОЙ БЕЗОПАСНОСТИ ТЭК
25

26.

Основы Oracle PL/SQL часть 1
Идентификаторы
• Могут включать до 30 символов
• Должны начинаться с алфавитного символа
• Могут содержать цифры, знак доллара, почеркивание и знак
решетки
• Не могут содержать символы, такие как дефис, косая черта и
пробелы
• Имя идентификатора не должно совпадать с именем столбца
таблицы в базе данных
• Не могут быть зарезервированными словами
Российский государственный университет нефти и газа (НИУ) имени И.М.Губкина
ФАКУЛЬТЕТ КОМПЛЕКСНОЙ БЕЗОПАСНОСТИ ТЭК
26

27.

Основы Oracle PL/SQL часть 1
Литералы
• Символьные литералы и даты должны быть заключены
в апостофы
v_name := ‘Henderson’;
• Числа могут представлять собой простые значения или
значения в экспотенциальном представлении (с
мантиссой и порядком)
Российский государственный университет нефти и газа (НИУ) имени И.М.Губкина
ФАКУЛЬТЕТ КОМПЛЕКСНОЙ БЕЗОПАСНОСТИ ТЭК
27

28.

Основы Oracle PL/SQL часть 1
Комментарии
• Однострочному комментарию предшествуют два символа
тире ( -- )
• Многострочные комментарии обрамляются символами /* и
*/
Пример
DECLARE
...
v_sal NUMBER (9,2);
BEGIN
/* выводим на экран сообщение Hello world! */
dbms_output.put_line(‘Hello world!’);
END;
Российский государственный университет нефти и газа (НИУ) имени И.М.Губкина
ФАКУЛЬТЕТ КОМПЛЕКСНОЙ БЕЗОПАСНОСТИ ТЭК
28

29.

Основы Oracle PL/SQL часть 1
Функции SQL в PL/SQL
• Функции, доступные в процедурах операторах:
- Однострочные числовые
- Однострочные символьные
Те же,
- Функции преобразования типов данных
что в SQL
- Функции для работы с датами
- Функции для работы с Timestamp
- GREATEST и LEAST
- Смешанные функции
• Функции, недоступные в процедурных операторах:
- DECODE
- Групповые функции
}
Российский государственный университет нефти и газа (НИУ) имени И.М.Губкина
ФАКУЛЬТЕТ КОМПЛЕКСНОЙ БЕЗОПАСНОСТИ ТЭК
29

30.

Основы Oracle PL/SQL часть 1
Использование функций SQL в PL/SQL
Создание списка рассылочных адресов для
организации
v_mailing_address := v_name||’
‘||v_address||CHR(10)||v_state;
Преобразование фамилий служащих в символы
нижнего регистра
v_ename
:= LOWER(v_name);
Российский государственный университет нефти и газа (НИУ) имени И.М.Губкина
ФАКУЛЬТЕТ КОМПЛЕКСНОЙ БЕЗОПАСНОСТИ ТЭК
30

31.

Основы Oracle PL/SQL часть 1
Вложенные блоки и область действия
переменных
• PL/SQL блоки могут быть вложенными один в
другой везде, где допустима исполняемая команда
• Вложенный блок становится командой
• Секция обработки исключений может содержать
вложенные блоки
• Область действия идентификатора - это часть
программы (блок, подпрограмма или пакет), из
которой можно ссылаться на этот идентификатор
Российский государственный университет нефти и газа (НИУ) имени И.М.Губкина
ФАКУЛЬТЕТ КОМПЛЕКСНОЙ БЕЗОПАСНОСТИ ТЭК
31

32.

Основы Oracle PL/SQL часть 1
Вложенные блоки и область действия
переменных (пример)
...
x INTEGER;
BEGIN
...
DECLARE
y NUMBER;
BEGIN
y := x;
END;
...
END;
Область действия х
Область
действия y
Российский государственный университет нефти и газа (НИУ) имени И.М.Губкина
ФАКУЛЬТЕТ КОМПЛЕКСНОЙ БЕЗОПАСНОСТИ ТЭК
32

33.

Основы Oracle PL/SQL часть 1
Квалификатор идентификатора
• Квалификатором может быть метка, окружающего блока
• Для уточнения идентификатора используйте в качестве
префикса имя блока
<<outer>>
DECLARE;
birthdate DATE;
BEGIN
DECLARE
birthdate DATE;
BEGIN
...
outer.birthdate := TO_DATE(’03/09/2014’,
‘DD/MM/YYYY’);
END;
...
END;
Российский государственный университет нефти и газа (НИУ) имени И.М.Губкина
ФАКУЛЬТЕТ КОМПЛЕКСНОЙ БЕЗОПАСНОСТИ ТЭК
33

34.

Основы Oracle PL/SQL часть 1
Операторы в PL/SQL
}
Логические операторы
Арифметические операторы
Оператор конкантенации
Скобки для управления
последовательностью операции
Как в SQL
• Оператор возведения в степень (**)
Российский государственный университет нефти и газа (НИУ) имени И.М.Губкина
ФАКУЛЬТЕТ КОМПЛЕКСНОЙ БЕЗОПАСНОСТИ ТЭК
34

35.

Основы Oracle PL/SQL часть 1
Примеры операторов в PL/SQL
• Увеличение счетчиков циклов
v_count := v_count + 1;
• Установка значения логического индикатора
v_equal := (v_n1 = v_n2);
• Проверка наличия определенного значения
номера служащего
v_valid := (v_empno IS NOT NULL);
Российский государственный университет нефти и газа (НИУ) имени И.М.Губкина
ФАКУЛЬТЕТ КОМПЛЕКСНОЙ БЕЗОПАСНОСТИ ТЭК
35

36.

Основы Oracle PL/SQL часть 1
Команды SQL в PL/SQL
• Выборка строки данных из базы данных с помощью
команды SELECT. Должна быть возвращена только
одна строка
• Внесение изменений в строки таблиц базы данных с
помощью команд DML
• Управление транзакциями с помощью команд
COMMIT, ROLLBACK, SAVEPOINT
• Использование неявных курсоров для определения
результата выполнения команд DML
Российский государственный университет нефти и газа (НИУ) имени И.М.Губкина
ФАКУЛЬТЕТ КОМПЛЕКСНОЙ БЕЗОПАСНОСТИ ТЭК
36

37.

Основы Oracle PL/SQL часть 1
Команды SELECT в PL/SQL
• Выборка данных из базы данных производится с помощью
команды SELECT
Синтаксис
SELECT
INTO
FROM
[WHERE
список_выборки
{имя_переменной[, имя_переменной] ...
| имя_записи}
таблица
условие];
Российский государственный университет нефти и газа (НИУ) имени И.М.Губкина
ФАКУЛЬТЕТ КОМПЛЕКСНОЙ БЕЗОПАСНОСТИ ТЭК
37

38.

Основы Oracle PL/SQL часть 1
Команды SELECT в PL/SQL продолжение
• Предложение INTO обязательно
• Запросы должны возвращать одну и только одну строку
Синтаксис
DECLARE
v_deptno
NUMBER(4);
v_location_id
NUMBER(4);
BEGIN
SELECT
department_id, location_id
INTO
v_deptno, v_location_id
FROM
departments
WHERE
department_name = ‘Sales’;
...
END;
Российский государственный университет нефти и газа (НИУ) имени И.М.Губкина
ФАКУЛЬТЕТ КОМПЛЕКСНОЙ БЕЗОПАСНОСТИ ТЭК
38

39.

Основы Oracle PL/SQL часть 1
Выборка данных в PL/SQL
• Выборка даты найма и оклада для определенного служащего
Пример
DECLARE
v_hire_date
v_salary
BEGIN
SELECT
INTO
FROM
WHERE
...
END;
employees.hire_date%TYPE;
employees.salary%TYPE;
hire_date, salary
v_hire_date, v_salary
employees
employee_id = 100;
Российский государственный университет нефти и газа (НИУ) имени И.М.Губкина
ФАКУЛЬТЕТ КОМПЛЕКСНОЙ БЕЗОПАСНОСТИ ТЭК
39

40.

Основы Oracle PL/SQL часть 1
Манипулирование данными в PL/SQL
Внесение изменений в таблицы базы данных с
помощью команд DML
• INSERT
• UPDATE
• DELETE
Российский государственный университет нефти и газа (НИУ) имени И.М.Губкина
ФАКУЛЬТЕТ КОМПЛЕКСНОЙ БЕЗОПАСНОСТИ ТЭК
40

41.

Основы Oracle PL/SQL часть 1
Вставка данных
Пример добавления информации о новом служащем
в таблицу EMPLOYEES
BEGIN
INSERT INTO employees
(employee_id, first_name, last_name, email,
hire_date, job_id, salary)
VALUES
(employees_seq.NEXTVAL, ‘Ruth’, ‘Cores’,
‘RCORES’,
sysdate, ‘AD_ASST’, 4000);
END;
Российский государственный университет нефти и газа (НИУ) имени И.М.Губкина
ФАКУЛЬТЕТ КОМПЛЕКСНОЙ БЕЗОПАСНОСТИ ТЭК
41

42.

Основы Oracle PL/SQL часть 1
Обновление данных
Пример увеличения оклада всех служащих из таблицы
EMPLOYEES, должность которых - клерк на складе:
DECLARE
v_sal_inc
BEGIN
UPDATE
SET
WHERE
END;
employees.salary%TYPE := 800;
employees
salary = salary + v_sal_inc
job_id = ‘ST_CLERK’;
Российский государственный университет нефти и газа (НИУ) имени И.М.Губкина
ФАКУЛЬТЕТ КОМПЛЕКСНОЙ БЕЗОПАСНОСТИ ТЭК
42

43.

Основы Oracle PL/SQL часть 1
Курсор SQL
• Курсор - это частная рабочая область SQL
• Имеется два типа курсоров:
- Неявные курсоры
- Явные курсоры
• Сервер Oracle использует неявные курсоры для
синтаксического разбора и выполнения команд SQL
пользователя
• Явные курсоры явно объявляются программистами
Российский государственный университет нефти и газа (НИУ) имени И.М.Губкина
ФАКУЛЬТЕТ КОМПЛЕКСНОЙ БЕЗОПАСНОСТИ ТЭК
43

44.

Основы Oracle PL/SQL часть 1
Атрибуты неявного курсора SQL
Атрибуты неявного курсора SQL позволяют пользователю
проверить результат выполнения своей команды SQL
SQL%ROWCOUNT
Количество строк, обработанных последней
командой SQL (целое число)
SQL%FOUND
Логический атрибут, возвращаюий значение
«истинно», если последняя команда SQL
обработала одну или несколько строк
SQL%NOTFOUND
Логический атрибут, возвращающий значений
«истинно», если последняя команда SQL не
обработала ни одной строки
SQL%ISOPEN
Всегда возвращает значение «ложно», т.к.
PL/SQL закрывает неявные курсоры сразу после
выполнения
Российский государственный университет нефти и газа (НИУ) имени И.М.Губкина
ФАКУЛЬТЕТ КОМПЛЕКСНОЙ БЕЗОПАСНОСТИ ТЭК
44

45.

Основы Oracle PL/SQL часть 1
Атрибуты курсора SQL
Пример удаления строк с конкретным номером служащего
таблицы EMPLOYEES и вывод количества удаленных строк
DECLARE
v_employee_id employees.employee_id%TYPE := 176;
BEGIN
DELETE FROM employees
WHERE
employee_id = v_employee_id;
dbms_output.put_line (SQL%ROWCOUNT ||
‘row deleted’);
END;
Российский государственный университет нефти и газа (НИУ) имени И.М.Губкина
ФАКУЛЬТЕТ КОМПЛЕКСНОЙ БЕЗОПАСНОСТИ ТЭК
45

46.

Основы Oracle PL/SQL часть 1
Управление потоком операция в блоках
PL/SQL
• Логическим потоком операций можно управлять
с помощью команд условного управления IF и
циклов различных типов
• Условные команды IF:
— IF-THEN-END IF
— IF-THEN-ELSE-END IF
— IF-THEN-ELSEIF-END IF
• Безусловный переход GOTO
Российский государственный университет нефти и газа (НИУ) имени И.М.Губкина
ФАКУЛЬТЕТ КОМПЛЕКСНОЙ БЕЗОПАСНОСТИ ТЭК
46

47.

Основы Oracle PL/SQL часть 1
Операторы IF
Синтаксис
IF условие THEN
команды;
[ELSEIF условие THEN
команды;]
[ELSE
команды;]
END IF;
Задать номер менеджера 102 для служащего Gietz
IF UPPER(v_last_name)= ‘GIETZ’ THEN
v_mng := 102;
END IF;
Российский государственный университет нефти и газа (НИУ) имени И.М.Губкина
ФАКУЛЬТЕТ КОМПЛЕКСНОЙ БЕЗОПАСНОСТИ ТЭК
47

48.

Основы Oracle PL/SQL часть 1
Простые операторы IF
Для служащего Vargas установить:
• должность (job_id) SA_REP
• номер отдела 80
...
IF v_name = ‘Vargas’ THEN
v_job
:= ‘SA_REP’;
v_deptno
:= 80;
END IF;
...
Российский государственный университет нефти и газа (НИУ) имени И.М.Губкина
ФАКУЛЬТЕТ КОМПЛЕКСНОЙ БЕЗОПАСНОСТИ ТЭК
48

49.

Основы Oracle PL/SQL часть 1
Составные операторы IF
Если фамилия служашего Vargas и его оклад
более 6500:
устанавливаем номер отдела 60
...
IF v_name = ‘Vargas’ AND salary > 6500 THEN
v_deptno
:= 60;
END IF;
...
Российский государственный университет нефти и газа (НИУ) имени И.М.Губкина
ФАКУЛЬТЕТ КОМПЛЕКСНОЙ БЕЗОПАСНОСТИ ТЭК
49

50.

Основы Oracle PL/SQL часть 1
Управление потоком операция с помощью
оператора IF-THEN-ELSE
TRUE
Действие в THEN (включая
дальнейшие команды IF)
FALSE
Условие IF
Действие в ELSE (включая
дальнейшие команды IF)
Российский государственный университет нефти и газа (НИУ) имени И.М.Губкина
ФАКУЛЬТЕТ КОМПЛЕКСНОЙ БЕЗОПАСНОСТИ ТЭК
50

51.

Основы Oracle PL/SQL часть 1
Операторы IF-THEN-ELSE
Установка флага, если с даты найма сотрудника
прошло 5 лет
...
IF MONTHS_BETWEEN(SYSDATE, v_hire_date)/12 >5 THEN
v_five_years := TRUE;
ELSE
v_five_years := FALSE;
END IF;
...
Российский государственный университет нефти и газа (НИУ) имени И.М.Губкина
ФАКУЛЬТЕТ КОМПЛЕКСНОЙ БЕЗОПАСНОСТИ ТЭК
51

52.

Основы Oracle PL/SQL часть 1
Операторы IF-THEN-ELSEIF
Установить премиальные для служащего в
зависимости от отдела
...
IF
v_deptno = 10 THEN
v_bonus := 5000;
ELSIF v_deptno = 80 THEN
v_bonus := 7500;
ELSE
v_bonus := 2000;
END IF;
...
Российский государственный университет нефти и газа (НИУ) имени И.М.Губкина
ФАКУЛЬТЕТ КОМПЛЕКСНОЙ БЕЗОПАСНОСТИ ТЭК
53

53.

Основы Oracle PL/SQL часть 1
CASE-выражение
• CASE-выражение выбирает и возвращает
результат
CASE
WHEN условие1 THEN итог := результат1;
WHEN условие2 THEN итог := результат2;
...
WHEN условиеN THEN итог := результатN;
[ELSE итог := результатN+1;]
END;
Российский государственный университет нефти и газа (НИУ) имени И.М.Губкина
ФАКУЛЬТЕТ КОМПЛЕКСНОЙ БЕЗОПАСНОСТИ ТЭК
54

54.

Основы Oracle PL/SQL часть 1
Пример CASE-выражения
DECLARE
v_grade CHAR(1);
v_rez
VARCHAR2(20);
BEGIN
SELECT SUBSTR(upper(last_name),1,1)
INTO
v_grade
FROM hr.employees
WHERE rownum <2;
CASE
WHEN v_grade = 'A' THEN v_rez := 'Excellent';
WHEN v_grade = 'B' THEN v_rez := 'Very good';
WHEN v_grade = 'C' THEN v_rez := 'Good';
ELSE v_rez := 'No such grade';
END CASE;
dbms_output.put_line (‘Grade:’|| v_grade ||
‘Result’ || v_rez);
END;
55
Российский государственный университет нефти и газа (НИУ) имени И.М.Губкина
ФАКУЛЬТЕТ КОМПЛЕКСНОЙ БЕЗОПАСНОСТИ ТЭК
55

55.

Основы Oracle PL/SQL часть 1
Обработка неопределенных значений
Запомните следующие правила:
• Простые операции сравнения при наличии неопределенного
значения всегда возвращают NULL
• Логическая операция NOT, примененная к неопределенному
значению, вернет NULL
В команде условного перехода, есди условие выработало
значение NULL, соответствующая последовательность команд
не выполняется
Российский государственный университет нефти и газа (НИУ) имени И.М.Губкина
ФАКУЛЬТЕТ КОМПЛЕКСНОЙ БЕЗОПАСНОСТИ ТЭК
56

56.

Основы Oracle PL/SQL часть 1
Циклы: операторы LOOP
• Циклы позволяют выполнять одну команду или
последовательность команд несколько раз
• Имеется три типа циклов:
- Простой цикл
- Цикл FOR
- Цикл WHILE
Российский государственный университет нефти и газа (НИУ) имени И.М.Губкина
ФАКУЛЬТЕТ КОМПЛЕКСНОЙ БЕЗОПАСНОСТИ ТЭК
57

57.

Основы Oracle PL/SQL часть 1
Простые циклы
Синтаксис
LOOP
команда1;
...
EXIT [WHEN условие];
END LOOP;
-- ограничитель
-- операторы, команды
-- оператор EXIT
Российский государственный университет нефти и газа (НИУ) имени И.М.Губкина
ФАКУЛЬТЕТ КОМПЛЕКСНОЙ БЕЗОПАСНОСТИ ТЭК
58

58.

Основы Oracle PL/SQL часть 1
Пример простого цикла
DECLARE
v_country_id locations.country_id%TYPE := ‘CA’;
v_location_id locations.location_id%TYPE;
v_counter
NUMBER(2) := 1;
v_city
locations.city%TYPE := ‘Montreal’;
BEGIN
SELECT MAX(location_id) INTO v_location_id
FROM locations
WHERE country_id = v_country_id;
LOOP
INSERT INTO locations(location_id, city,
country_id)
VALUES ((v_location_id + v_counter), v_city,
v_country_id);
v_counter := v_counter + 1;
EXIT WHEN v_counter > 3;
END LOOP;
END;
59
Российский государственный университет нефти и газа (НИУ) имени И.М.Губкина
ФАКУЛЬТЕТ КОМПЛЕКСНОЙ БЕЗОПАСНОСТИ ТЭК
59

59.

Основы Oracle PL/SQL часть 1
Циклы WHILE
Синтаксис
WHILE условие LOOP
команда1;
команда2;
...
END LOOP;
Условие оценивается в начале каждой итерации
Российский государственный университет нефти и газа (НИУ) имени И.М.Губкина
ФАКУЛЬТЕТ КОМПЛЕКСНОЙ БЕЗОПАСНОСТИ ТЭК
60

60.

Основы Oracle PL/SQL часть 1
Пример цикла WHILE
DECLARE
v_country_id locations.country_id%TYPE := ‘CA’;
v_location_id locations.location_id%TYPE;
v_counter
NUMBER := 1;
v_city
locations.city%TYPE := ‘Montreal’;
BEGIN
SELECT MAX(location_id) INTO v_location_id
FROM locations
WHERE country_id = v_country_id;
WHILE v_counter <=3 LOOP
INSERT INTO locations(location_id, city,
country_id)
VALUES ((v_location_id + v_counter), v_city,
v_country_id);
v_counter := v_counter + 1;
END LOOP;
END;
Российский государственный университет нефти и газа (НИУ) имени И.М.Губкина
ФАКУЛЬТЕТ КОМПЛЕКСНОЙ БЕЗОПАСНОСТИ ТЭК
61

61.

Основы Oracle PL/SQL часть 1
Управление потоком операция в блоках
PL/SQL
Синтаксис
FOR индекс IN [REVERSE]
нижняя_граница..верхняя_граница LOOP
команда1;
команда2;
...
END LOOP;
• Цикл FOR - это быстрый способ проверки количества
повторов цикла
• Объявление индекса (переменной цикла) не требуется;
индекс описывается неявно
• Обязательно требуется указать нижнюю и верхнюю границу
Российский государственный университет нефти и газа (НИУ) имени И.М.Губкина
ФАКУЛЬТЕТ КОМПЛЕКСНОЙ БЕЗОПАСНОСТИ ТЭК
62

62.

Основы Oracle PL/SQL часть 1
Пример цикла FOR
Вставка трех мест расположения отделов с кодом страны CA и
городом Монреаль
DECLARE
v_country_id locations.country_id%TYPE := ‘CA’;
v_location_id locations.location_id%TYPE;
v_city
locations.city%TYPE := ‘Montreal’;
BEGIN
SELECT MAX(location_id) INTO v_location_id
FROM locations
WHERE country_id = v_country_id;
FOR i IN 1..3 LOOP
INSERT INTO locations(location_id, city,
country_id)
VALUES ((v_location_id + v_counter), v_city,
v_country_id);
END LOOP;
END;
Российский государственный университет нефти и газа (НИУ) имени И.М.Губкина
ФАКУЛЬТЕТ КОМПЛЕКСНОЙ БЕЗОПАСНОСТИ ТЭК
63

63.

Основы Oracle PL/SQL часть 1
Циклы FOR особенности
• Ссылаться на индекс можно только внутри цикла,
вне цикла он не определен
• Нельзя ссылаться на индекс в качестве
переменной в левой части оператора
присваивания
Российский государственный университет нефти и газа (НИУ) имени И.М.Губкина
ФАКУЛЬТЕТ КОМПЛЕКСНОЙ БЕЗОПАСНОСТИ ТЭК
64

64.

Основы Oracle PL/SQL часть 1
Вложенные циклы и метки
• Возможно несколько уровней вложения циклов
• Для различения циклов и блоков используются
метки
• Выход из внешнего цикла осуществляется с
помощью оператора EXIT, содержащего ссылку
на метку
Российский государственный университет нефти и газа (НИУ) имени И.М.Губкина
ФАКУЛЬТЕТ КОМПЛЕКСНОЙ БЕЗОПАСНОСТИ ТЭК
65

65.

Основы Oracle PL/SQL часть 1
Вложенные циклы и метки (пример)
...
BEGIN
<<Outer_loop>>
LOOP
v_counter := v_counter +1;
EXIT WHEN v_counter >10;
<<Inner_loop>>
LOOP
...
EXIT Outer_loop WHEN total_done = ‘YES’;
-- Leave both loops
EXIT WHEN inner_done = ‘YES’;
-- Leave inner loop only
...
END LOOP Inner_loop;
...
END LOOP Outer_loop;
END;
Российский государственный университет нефти и газа (НИУ) имени И.М.Губкина
ФАКУЛЬТЕТ КОМПЛЕКСНОЙ БЕЗОПАСНОСТИ ТЭК
66

66.

Основы Oracle PL/SQL часть 1
Составные типы данных
• Два типа:
- PL/SQL RECORDS (записи)
- Коллекции PL/SQL
- INDEX BY таблица
- Вложенная таблица
- VARRAY
• Имеются внутренние компоненты
• Могут использоваться повторно
Российский государственный университет нефти и газа (НИУ) имени И.М.Губкина
ФАКУЛЬТЕТ КОМПЛЕКСНОЙ БЕЗОПАСНОСТИ ТЭК
67

67.

Основы Oracle PL/SQL часть 1
Записи PL/SQL
• Должны содержать один или более компонентов (полей)
скалярного типа, типа RECORD или типа INDEX BY таблица
• По структуре сходны с записями в языкам третьего
поколения (3GL)
• Не эквивалентны строкам таблицы базы данных
• Совокупность полей рассматривается как логическая единица
• Удобны для выборки строки данных из таблицы при
обработке
Российский государственный университет нефти и газа (НИУ) имени И.М.Губкина
ФАКУЛЬТЕТ КОМПЛЕКСНОЙ БЕЗОПАСНОСТИ ТЭК
68

68.

Основы Oracle PL/SQL часть 1
Объявления записи PL/SQL
Синтаксис
TYPE имя_типа IS RECORD
(объявление_поля[, объявление_поля]...);
идентификатор
имя_типа;
Где объявление_поля:
имя_поля {тип_поля | переменная%TYPE
| таблица.столбец%TYPE | таблица%ROWTYPE}
[[NOT NULL] {:= | DEFAULT} выражение]
Российский государственный университет нефти и газа (НИУ) имени И.М.Губкина
ФАКУЛЬТЕТ КОМПЛЕКСНОЙ БЕЗОПАСНОСТИ ТЭК
69

69.

Основы Oracle PL/SQL часть 1
Создание записи PL/SQL
Пример объявления переменной для хранения
имени, должности и оклада:
...
TYPE emp_record_type IS RECORD
(last_name VARCHAR2(25),
job_id
VARCHAR2(10),
salary
NUMBER(8,2));
emp_record emp_record_type;
...
Российский государственный университет нефти и газа (НИУ) имени И.М.Губкина
ФАКУЛЬТЕТ КОМПЛЕКСНОЙ БЕЗОПАСНОСТИ ТЭК
70

70.

Основы Oracle PL/SQL часть 1
Атрибут %ROWTYPE
• Используется для объявления переменной на
основе совокупности столбцов в таблице или
представлении базы данных
• Перед %ROWTYPE указывается имя таблицы
• Поля записи наследуют имена и типы данных от
столцов таблицы или представления
Российский государственный университет нефти и газа (НИУ) имени И.М.Губкина
ФАКУЛЬТЕТ КОМПЛЕКСНОЙ БЕЗОПАСНОСТИ ТЭК
71

71.

Основы Oracle PL/SQL часть 1
Преимущества атрибута %ROWTYPE
• Количество базовых столбцов и типы данных в
них могут быть неизвестны
• Количество и типы базовых столбцов могут
меняться во время выполнения
• Полезен при выборке строки с помощью команды
SELECT *
Российский государственный университет нефти и газа (НИУ) имени И.М.Губкина
ФАКУЛЬТЕТ КОМПЛЕКСНОЙ БЕЗОПАСНОСТИ ТЭК
72

72.

Основы Oracle PL/SQL часть 1
INDEX BY таблицы
• Включают два компонента
- Первичный ключ типа BINARY_INTEGER
- Столбец со скалярным типом данных или
типом данных RECORD
• Увеличиваются динамически, т.к. не имеют
ограничений
Российский государственный университет нефти и газа (НИУ) имени И.М.Губкина
ФАКУЛЬТЕТ КОМПЛЕКСНОЙ БЕЗОПАСНОСТИ ТЭК
73

73.

Основы Oracle PL/SQL часть 1
Объявление INDEX BY таблиц
Синтаксис:
TYPE имя_типа IS TABLE OF
{тип_столбца | переменная%TYPE
| таблица.столбец%TYPE} [NOT NULL]
| таблица.%ROWTYPE
[INDEX BY BINARY_INTEGER];
идентификатор
имя_типа;
Пример объявления INDEX BY таблицы для
хранения фамилий
TYPE ename_table_type IS TABLE OF
employees.last_name%TYPE
INDEX BY BINARY_INTEGER;
ename_table ename_table_type;
Российский государственный университет нефти и газа (НИУ) имени И.М.Губкина
ФАКУЛЬТЕТ КОМПЛЕКСНОЙ БЕЗОПАСНОСТИ ТЭК
74

74.

Основы Oracle PL/SQL часть 1
Структура INDEX BY таблицы
Уникальный
идентификатор
Столбец
…
…
1
значение1
2
Значение2
3
значение3
…
…
BINARY_INTEGER
Скалярный тип
Российский государственный университет нефти и газа (НИУ) имени И.М.Губкина
ФАКУЛЬТЕТ КОМПЛЕКСНОЙ БЕЗОПАСНОСТИ ТЭК
75

75.

Основы Oracle PL/SQL часть 1
Пример создания INDEX BY таблицы
DECLARE
TYPE ename_table_type IS TABLE OF
employees.last_name%TYPE
INDEX BY BINARY_INTEGER;
TYPE hiredate_table_type IS TABLE OF DATE
INDEX BY BINARY_INTEGER;
ename_table ename_table_type;
hiredate_table hiredate_table_type;
BEGIN
ename_table(1)
:= ‘CAMERON’;
hiredate_table(8)
:= SYSDATE + 7;
IF ename_table.EXISTS(1) THEN
INSERT INTO ...
...
END;
Российский государственный университет нефти и газа (НИУ) имени И.М.Губкина
ФАКУЛЬТЕТ КОМПЛЕКСНОЙ БЕЗОПАСНОСТИ ТЭК
76

76.

Основы Oracle PL/SQL часть 1
Использование методов INDEX BY таблиц
• Для упрощения использования INDEX BY таблиц
в PL/SQL имеются следующие методы:
- EXISTS
- NEXT
- COUNT
- TRIM
- FIRST и LAST
- DELETE
- PRIOR
Российский государственный университет нефти и газа (НИУ) имени И.М.Губкина
ФАКУЛЬТЕТ КОМПЛЕКСНОЙ БЕЗОПАСНОСТИ ТЭК
77

77.

Основы Oracle PL/SQL часть 1
INDEX BY таблицы записей
• Объявление типа TABLE с помощью атрибута %ROWTYPE
• Объявление переменной PL/SQL для хранения информации
об отделе
Пример:
DECLARE
TYPE dapt_table_type IS TABLE OF
departments%ROWTYPE
INDEX BY BINARY_INTEGER;
dept_table dept_table_type;
-- Each element of dept_table is a record
Российский государственный университет нефти и газа (НИУ) имени И.М.Губкина
ФАКУЛЬТЕТ КОМПЛЕКСНОЙ БЕЗОПАСНОСТИ ТЭК
78

78.

Основы Oracle PL/SQL часть 1
Пример INDEX BY таблицы записей
DECLARE
TYPE emp_table_type IS TABLE OF
employees%ROWTYPE
INDEX BY BINARY_INTEGER;
my_emp_table emp_table_type;
v_count NUMBER(3) := 104;
BEGIN
FOR i IN 100..v_count
LOOP
SELECT * INTO my_emp_table(i) FROM employees
WHERE employee_id = i;
END LOOP;
FOR i IN my_emp_table.FIRST..my_emp_table.LAST
LOOP
dbms_output.put_line(my_emp_table(i).last_name);
END LOOP;
END;
Российский государственный университет нефти и газа (НИУ) имени И.М.Губкина
ФАКУЛЬТЕТ КОМПЛЕКСНОЙ БЕЗОПАСНОСТИ ТЭК
79

79.

Успехов в освоении
курса
Савченко Наталья Александровна
[email protected]
http://www.fdo.gubkin.ru
Российский государственный университет нефти и газа (НИУ) имени И.М.Губкина
ФАКУЛЬТЕТ КОМПЛЕКСНОЙ БЕЗОПАСНОСТИ ТЭК
80