- Главная
- Информатика
- Работа с составными типами данных. Применение коллекций, записей и объектных типов
Содержание
- 2. Рассматриваемые вопросы Создание пользовательских записей PL/SQL Создание записи с атрибутом %ROWTYPE Создание таблицы PL/SQL INDEX BY
- 3. Составные типы данных Два типа данных: PL/SQL RECORDs (записи) PL/SQL коллекции Nested table collections (Вложенные таблицы),
- 4. DECLARE m_COMPANY VARCHAR2(30); m_CUST_REP INTEGER; m_CREDIT_LIMIT NUMBER; Но иногда удобнее использовать, так называемый составной тип. Одним
- 5. Для создания записи необходимо: Объявить тип данных RECORD; Объявить переменную этого типа данных. TYPE type_name IS
- 6. SQL> SET SERVEROUTPUT ON SQL> DECLARE 2 TYPE is_SmplRec IS RECORD 3 ( 4 m_Fld1 VARCHAR2(10),
- 7. SET SERVEROUTPUT ON DECLARE TYPE is_SmplRecOne IS RECORD ( m_Fld1 VARCHAR2(10), m_Fld2 VARCHAR2(30), m_DtFld DATE, m_Fld3
- 8. DECLARE TYPE is_Customers IS RECORD ( m_COMPANY CUSTOMERS.COMPANY%TYPE, m_CUST_REP CUSTOMERS.CUST_REP%TYPE, m_CREDIT_LIMIT CUSTOMERS.CREDIT_LIMIT%TYPE ); MY_CUST is_Customers; BEGIN
- 9. Атрибут %ROWTYPE Используется для объявления переменной типа "запись« на основе совокупности столбцов в таблице или представлении
- 10. Составной тип TABLE! В своей сути это одномерный массив скалярного типа. Он не может содержать тип
- 11. SET SERVEROUTPUT ON DECLARE TYPE m_SmplTable IS TABLE OF VARCHAR2(128) INDEX BY BINARY_INTEGER; TYPE m_SmplTblData IS
- 12. Преимущества использования %ROWTYPE Количество базовых столбцов базы данных и типы данных в них могут быть неизвестны.
- 13. Атрибут %ROWTYPE В первом объявлении на слайде создается запись с такими же именами полей и типами
- 14. DECLARE TYPE is_Customers IS TABLE OF CUSTOMERS%ROWTYPE INDEX BY BINARY_INTEGER; MY_CUST is_Customers; BEGIN SELECT * INTO
- 15. The %ROWTYPE Attribute ... DEFINE employee_number = 124 DECLARE emp_rec employees%ROWTYPE; BEGIN SELECT * INTO emp_rec
- 16. ... DEFINE employee_number = 124 DECLARE emp_rec retired_emps%ROWTYPE; BEGIN SELECT employee_id, last_name, job_id, manager_id, hire_date, hire_date,
- 17. SET SERVEROUTPUT ON SET VERIFY OFF DEFINE employee_number = 124 DECLARE emp_rec retired_emps%ROWTYPE; BEGIN SELECT *
- 18. SQL> SET SERVEROUTPUT ON SQL> DECLARE TYPE m_SmplTable IS TABLE OF VARCHAR2(128) INDEX BY BINARY_INTEGER; MY_TBL
- 19. SET SERVEROUTPUT ON -- DELETE DECLARE TYPE m_SmplTable IS TABLE OF VARCHAR2(128) INDEX BY BINARY_INTEGER; MY_TBL
- 20. SET SERVEROUTPUT ON -- EXISTS DECLARE TYPE m_SmplTable IS TABLE OF VARCHAR2(128) INDEX BY BINARY_INTEGER; MY_TBL
- 21. Расширения оператора GROUP BY Операторы ROLLUP, CUBE и GROUPING SETS являются расширениями предложения GROUP BY. Операторы
- 22. Пример. Запрос просуммировал данные по зарплатам на уровнях по отделам, должностям и подвел общий итог
- 23. Функция CUBE Оператор CUBE формирует результирующий набор, представляющий собой многомерный куб. То есть, результирующий набор содержит
- 24. Пример ниже показывает запрос, ранее уже использованный, но с функцией CUBE для получения дополнительной агрегированной информации:
- 25. Различия между CUBE и ROLLUP: CUBE создает результирующий набор, содержащий статистические выражения для всех комбинаций значений
- 26. Пример:
- 27. Функция GROUPING SETS GROUPING SETS является дальнейшее расширение предложения GROUP BY, которые можно использовать для указания
- 28. Следующий запрос вычисляет все 8 (2 * 2 * 2) группировки, но только по группам: SELECT
- 29. Объединение группировок Объединение групп - краткий путь для создания полезной комбинации группировок. Каскадной группировки указано, перечислив
- 31. Интерактивный запрос значений переменных в командах Oracle При рассмотрении команды INSERT мы использовали переменную подстановки. Достаточно
- 32. Переменные в SQL*Plus можно предопределить с помощью команд ACCEPT или DEFINE. Команда ACCEPT считывает строку пользовательских
- 33. Пользовательские переменные можно определять до выполнения команды SELECT. Для определения и установки значений пользовательских переменных SQL*Plus
- 34. Пример: Напишите запрос, который вы выводил информацию об имени, фамилии и заработной плате сотрудников из таблицы
- 36. Конструкции MERGE и WITH Оператор MERGE MERGE — DML-оператор вставки (INSERT)/обновления (UPDATE)/удаления (DELETE, начиная с Oracle
- 37. Пример. Следующий пример использует таблицу BONUSES в примере схемы ОЕ со значением бонуса по умолчанию в
- 38. 4) SELECT * FROM bonuses ORDER BY employee_id; EMPLOYEE_ID BONUS ----------- ------ 153 100 154 100
- 39. 6) SELECT * FROM bonuses ORDER BY employee_id; EMPLOYEE_ID BONUS ----------------- ----------- 153 180 154 175
- 40. Пример : Слияние строк - пример показывает соответствия EMPLOYEE_ID в таблице COPY_EMP к EMPLOYEE_ID в таблице
- 41. Использование конструкции WITH Oracle допускает вынесение определений подзапросов из тела основного запроса с помощью особой фразы
- 42. WITH A AS ( SELECT ... ) , B AS ( SELECT ... FROM x )
- 43. Пример . Запрос создает запросы DEPT_COSTS и AVG_COST, а затем использует их в теле основного запроса.
- 44. Формулирование рекурсивных запросов С версии Oracle 11.2 фраза WITH может использоваться для формулирования рекурсивных запросов, в
- 45. Предложение SELECT для исходного множества строк Oracle называет опорным (anchor) членом фразы WITH. Предложение SELECT для
- 46. Операция UNION ALL здесь используется символически, в рамках определенного контекста, для указания способа рекурсивного накопления результата.
- 47. Пример с дополнительным разъяснением способа выполнения: SQL> WITH 2 anchor1234 ( n ) AS ( --
- 48. Пример. Вычислить какое количество сотрудников ежегодно поступает на работу с 2002 по 2010 гг. WITH period
- 49. Необязательный оператор START WITH (начать с) - если задано, то позволяет указать, с какой вершины (строки)
- 51. Чтобы получить нормальную иерархию нужно использовать специальный оператор, который называется PRIOR. Это обычный унарный оператор, точно
- 52. 3. Oracle выбирает следующие одно за другим поколения дочерних строк. Сначала выбираются потомки строк, полученных на
- 54. Пример . Показать менеджеров и сотрудников пока employee_id=101. SELECT employee_id, last_name, job_id, manager_id FROM employees START
- 55. Оператор SELECT, осуществляющий древовидный запрос, может использовать псевдостолбец LEVEL, содержащий уровень вложенности для каждой строки. Для
- 57. Используя функцию LPAD и псевдостолбец LEVEL можно украсить выводимый результат, предварительно отформатировав столбец «tree» : SELECT
- 58. Напишем запрос, который возвращал бы из таблицы hr.employees информацию: имя сотрудника; фамилию сотрудника; уровень подчиненности (самый
- 60. Чтобы указать БД в Oracle, что сортировать надо только в пределах одного уровня иерархии, нам поможет
- 61. Транзакции в Oracle SQL. В Oracle нет явного оператора, чтобы начать транзакцию, но и нет автоматического
- 62. COMMIT - завершает транзакцию и делает любые выполненные в ней изменения постоянными. Освобождаются блокировки. ROLLBACK -
- 63. Некоторые особенности выполнения транзакций в Oracle: 1.Транзакция обычно состоит из нескольких операторов DML. Если один оператор
- 64. 3. Oracle анонимный блок PL/SQL считает оператором. Например, begin оператор1; оператор2; end; То есть для него
- 65. Распределённая транзакция имеет то же свойство, что и обычная: все или ничего. Только фиксация происходит в
- 67. Скачать презентацию
Рассматриваемые вопросы
Создание пользовательских записей PL/SQL
Создание записи с атрибутом %ROWTYPE
Создание таблицы
Рассматриваемые вопросы
Создание пользовательских записей PL/SQL
Создание записи с атрибутом %ROWTYPE
Создание таблицы
Создание таблицы записей PL/SQL INDEX BY
Различия между записями, таблицами и таблицами записей
Расширения оператора GROUP BY
Составные типы данных
Два типа данных:
PL/SQL RECORDs (записи)
PL/SQL коллекции
Nested table collections
Составные типы данных
Два типа данных:
PL/SQL RECORDs (записи)
PL/SQL коллекции
Nested table collections
varray collections
Associative arrays,
string indexed collections
К составным типам данных, называемым также коллекциями (collections), относятся RECORD, TABLE, Nested TABLE и VARRAY. Тип данных RECORD ("запись") используется для обработки взаимосвязанных, но разных данных как одной логической единицы. Тип данных TABLE ("таблица'") используется для ссылок на коллекции данных как на единый объект.
Запись - это группа взаимосвязанных элементов данных, которые хранятся в полях; каждый из этих элементов данных имеет собственное имя и тип данных. Таблица имеет столбец и главный ключ, что позволяет вам обращаться к строкам как к массивам. После того, как таблицы или записи определены, они могут использоваться повторно.
DECLARE
m_COMPANY VARCHAR2(30);
m_CUST_REP INTEGER;
m_CREDIT_LIMIT NUMBER;
Но иногда удобнее использовать, так называемый составной тип.
DECLARE
m_COMPANY VARCHAR2(30);
m_CUST_REP INTEGER;
m_CREDIT_LIMIT NUMBER;
Но иногда удобнее использовать, так называемый составной тип.
TYPE тип записи IS RECORD (
поле1 тип1 [NOT NULL] [:= выражение1]
поле2 тип2 [NOT NULL] [:= выражение2]
…………………………………………………………………..
поле-n тип-n [NOT NULL] [:= выражение-n] );
Можно указывать ограничение NOT NULL, переменной вне записи исходное значение и ограничение NOT NULL не обязательны!
Для создания записи необходимо:
Объявить тип данных RECORD;
Объявить переменную этого типа данных.
Для создания записи необходимо:
Объявить тип данных RECORD;
Объявить переменную этого типа данных.
TYPE type_name IS RECORD
(field_declaration[, field_declaration]…);
identifier type_name;
field_name {field_type | variable%TYPE
| table.column%TYPE | table%ROWTYPE}
[[NOT NULL] {:= | DEFAULT} expr]
Где field_declaration:
SQL> SET SERVEROUTPUT ON
SQL> DECLARE
2 TYPE is_SmplRec IS RECORD
3 (
SQL> SET SERVEROUTPUT ON
SQL> DECLARE
2 TYPE is_SmplRec IS RECORD
3 (
5 m_Fld2 VARCHAR2(30) := 'Buber',
6 m_DtFld DATE,
7 m_Fld3 INTEGER := 1000,
8 m_Fld4 VARCHAR2(100) NOT NULL := 'System'
9 );
10 MY_SMPL is_SmplRec;
11 BEGIN
12 DBMS_OUTPUT.enable;
13 DBMS_OUTPUT.put_line(MY_SMPL.m_Fld2||' '||MY_SMPL.m_Fld4);
14 END;
15 /
Buber System
Как видно процедура успешно выполнилась и вернула значения. Здесь хорошо видно, как мы проинициализировали переменные записи внутри объявления. Так же одно из полей объявленное как NOT NULL сразу получило значение.
SET SERVEROUTPUT ON
DECLARE
TYPE is_SmplRecOne IS RECORD
( m_Fld1 VARCHAR2(10),
m_Fld2
SET SERVEROUTPUT ON
DECLARE
TYPE is_SmplRecOne IS RECORD
( m_Fld1 VARCHAR2(10),
m_Fld2
m_DtFld DATE,
m_Fld3 INTEGER,
m_Fld4 VARCHAR2(100));
TYPE is_SmplRecTwo IS RECORD
( m_Fld1 VARCHAR2(10),
m_Fld2 VARCHAR2(30),
m_DtFld DATE,
m_Fld3 INTEGER,
m_Fld4 VARCHAR2(100));
MY_SMPLONE is_SmplRecOne;
MY_SMPLTWO is_SmplRecTwo;
BEGIN
MY_SMPLONE.m_Fld3 := 100; MY_SMPLONE.m_Fld4 := 'Buber';
MY_SMPLTWO.m_Fld3 := MY_SMPLONE.m_Fld3;
MY_SMPLTWO.m_Fld4 := MY_SMPLONE.m_Fld4;
DBMS_OUTPUT.enable;
DBMS_OUTPUT.put_line(TO_CHAR(MY_SMPLTWO.m_Fld3));
DBMS_OUTPUT.put_line(MY_SMPLTWO.m_Fld4);
END;
DECLARE
TYPE is_Customers IS RECORD
(
m_COMPANY CUSTOMERS.COMPANY%TYPE,
m_CUST_REP CUSTOMERS.CUST_REP%TYPE,
m_CREDIT_LIMIT CUSTOMERS.CREDIT_LIMIT%TYPE
);
MY_CUST is_Customers;
BEGIN
SELECT COMPANY, CUST_REP,
DECLARE
TYPE is_Customers IS RECORD
(
m_COMPANY CUSTOMERS.COMPANY%TYPE,
m_CUST_REP CUSTOMERS.CUST_REP%TYPE,
m_CREDIT_LIMIT CUSTOMERS.CREDIT_LIMIT%TYPE
);
MY_CUST is_Customers;
BEGIN
SELECT COMPANY, CUST_REP,
FROM CUSTOMERS WHERE CUST_NUM = 2108;
DBMS_OUTPUT.enable;
DBMS_OUTPUT.put_line(MY_CUST.m_COMPANY||' '||
TO_CHAR(MY_CUST.m_CUST_REP)||' ‘
|| TO_CHAR(MY_CUST.m_CREDIT_LIMIT));
END;
Здесь объявлена запись на основе типов таблицы CUSTOMERS, и выбрана одна запись из этой таблицы с помощью, оператора INTO, все это отправлено в переменные записи
Атрибут %ROWTYPE
Используется для объявления переменной типа "запись« на основе совокупности столбцов
Атрибут %ROWTYPE
Используется для объявления переменной типа "запись« на основе совокупности столбцов
Перед %ROWTYPE указывается имя таблицы.
Поля записи наследуют имена и типы данных от столбцов таблицы или представления. Последовательность полей также наследуется из структуры таблицы (по умолчанию).
Такой тип значительно облегчает программирование операций со строками, позволяя выполнять выборку строки целиком в одну переменную типа "запись", а также предотвращает необходимость перепрограммирования блоков в случае изменения структуры таблицы.
Составной тип TABLE! В своей сути это одномерный массив скалярного типа.
Составной тип TABLE! В своей сути это одномерный массив скалярного типа.
SET SERVEROUTPUT ON
DECLARE
TYPE m_SmplTable IS TABLE OF VARCHAR2(128)
INDEX BY BINARY_INTEGER;
TYPE m_SmplTblData
SET SERVEROUTPUT ON
DECLARE
TYPE m_SmplTable IS TABLE OF VARCHAR2(128)
INDEX BY BINARY_INTEGER;
TYPE m_SmplTblData
INDEX BY BINARY_INTEGER;
MY_TBL m_SmplTable;
MY_TBL_DT m_SmplTblData;
BEGIN
MY_TBL(1) := 'Buber';
MY_TBL_DT(2) := SYSDATE - 1;
DBMS_OUTPUT.enable;
DBMS_OUTPUT.put_line(TO_CHAR(MY_TBL(1)));
DBMS_OUTPUT.put_line(TO_CHAR(MY_TBL_DT(2)));
END;
Мы объявили две одномерные коллекции m_SmplTable, m_SmplTblData, одна из них содержит элементы размерностью VARCHAR2(128), другая DATE. Затем объявили две переменные данного типа - MY_TBL, MY_TBL_DT и присвоили первому элементу строкового массива значение "Buber", а второму элементу массива дат, значение текущей даты минус 1. Результат вывели на консоль. При этом хорошо видно, что тип TABLE очень схож с таблицей БД и содержит обычно два ключа KEY и VALUE, ключ и значение. Ключ имеет тип BINARY_INTEGER:
Преимущества использования %ROWTYPE
Количество базовых столбцов базы данных и типы данных в
Преимущества использования %ROWTYPE
Количество базовых столбцов базы данных и типы данных в
Количество и типы базовых столбцов могут меняться во время выполнения.
Полезен при выборке строки с помощью команды SELECT *.
Атрибут %ROWTYPE
В первом объявлении на слайде создается запись с такими же
Атрибут %ROWTYPE
В первом объявлении на слайде создается запись с такими же
В следующем примере, сотрудник выходит на пенсию. Информация о вышедших на пенсию сотрудниках добавляется в таблицу, которая хранит эту. Пользователю известен номер сотрудника. Запись о сотруднике, заданная пользователем выбирается из EMPLOYEES и хранится в переменной emp_rec, которая объявлена с помощью атрибута %ROWTYPE.
DEFINE employee_number = 124
DECLARE
emp_rec employees%ROWTYPE;
BEGIN
SELECT * INTO emp_rec FROM employees
WHERE employee_id = &employee_number;
INSERT INTO retired_emps(empno, ename, job, mgr, hiredate,leavedate, sal, comm, deptno)
VALUES (emp_rec.employee_id, emp_rec.last_name, emp_rec.job_id,emp_rec.manager_id, emp_rec.hire_date, SYSDATE, emp_rec.salary,emp_rec.commission_pct, emp_rec.department_id);
COMMIT;
END;
DECLARE
TYPE is_Customers IS TABLE OF CUSTOMERS%ROWTYPE
INDEX BY BINARY_INTEGER;
MY_CUST is_Customers;
BEGIN
SELECT * INTO
DECLARE
TYPE is_Customers IS TABLE OF CUSTOMERS%ROWTYPE
INDEX BY BINARY_INTEGER;
MY_CUST is_Customers;
BEGIN
SELECT * INTO
FROM CUSTOMERS WHERE CUST_NUM = 2108;
DBMS_OUTPUT.enable;
DBMS_OUTPUT.put_line(TO_CHAR(MY_CUST(100).CUST_NUM)||'
'||MY_CUST(100).COMPANY||' '||
TO_CHAR(MY_CUST(100).CUST_REP)||'
'||TO_CHAR(MY_CUST(100).CREDIT_LIMIT));
END;
Здесь мы объявили коллекцию, с типом запись из таблицы CUSTOMERS! Итак, объявляем тип:
TYPE is_Customers IS TABLE OF CUSTOMERS%ROWTYPE
INDEX BY BINARY_INTEGER;
Это значит, что каждая строка коллекции содержит полную запись из таблицы БД CUSTOMERS. Далее 100 элементу массива присваиваем значение записи таблицы с индексом 2108.
The %ROWTYPE Attribute
...
DEFINE employee_number = 124
DECLARE
emp_rec employees%ROWTYPE;
BEGIN
SELECT
The %ROWTYPE Attribute
...
DEFINE employee_number = 124
DECLARE
emp_rec employees%ROWTYPE;
BEGIN
SELECT
Если служащий удаляется, информация о служащем добавлена к таблице, которая содержит информацию об уволенных служащих retired_emps. Пользователь поставляет код ( id) служащего. Запись о служащем, определенным пользователем с id=124, elfktyf из таблицы служащих и сохранена в emp_rec переменную, которая объявлена, используя аттрибут %ROWTYPE. Запись, которая вставлена в таблицу retired_emps
...
DEFINE employee_number = 124
DECLARE
emp_rec retired_emps%ROWTYPE;
BEGIN
SELECT employee_id, last_name, job_id, manager_id,
...
DEFINE employee_number = 124
DECLARE
emp_rec retired_emps%ROWTYPE;
BEGIN
SELECT employee_id, last_name, job_id, manager_id,
WHERE employee_id = &employee_number;
INSERT INTO retired_emps VALUES emp_rec;
END; /
SELECT * FROM retired_emps;
SET SERVEROUTPUT ON
SET VERIFY OFF
DEFINE employee_number = 124
DECLARE
emp_rec retired_emps%ROWTYPE;
BEGIN
SELECT
SET SERVEROUTPUT ON
SET VERIFY OFF
DEFINE employee_number = 124
DECLARE
emp_rec retired_emps%ROWTYPE;
BEGIN
SELECT
emp_rec.leavedate:=SYSDATE;
UPDATE retired_emps SET ROW = emp_rec
WHERE empno=&employee_number;
END;
/
SELECT * FROM retired_emps;
SQL> SET SERVEROUTPUT ON
SQL> DECLARE
TYPE m_SmplTable IS TABLE OF VARCHAR2(128)
INDEX BY
SQL> SET SERVEROUTPUT ON
SQL> DECLARE
TYPE m_SmplTable IS TABLE OF VARCHAR2(128)
INDEX BY
MY_TBL m_SmplTable;
BEGIN
FOR i IN 1..10 LOOP
MY_TBL(i) := TO_CHAR(i+5);
END LOOP;
DBMS_OUTPUT.enable;
DBMS_OUTPUT.put_line('Count table is: '||TO_CHAR(MY_TBL.COUNT));
END;
С помощью цикла FOR мы ввели в таблицу 10 значений и применив атрибут - COUNT получили количество записей в таблице. Следующий атрибут DELETE. Тут чуть сложнее. Во-первых, он имеет аргументы:
DELETE - удаляет все строки таблицы.
DELETE(n) - удаляет n-ю строку коллекции.
DELETE(n,m) - удаляет строки коллекции с n по m.
SET SERVEROUTPUT ON
-- DELETE
DECLARE
TYPE m_SmplTable IS TABLE OF VARCHAR2(128)
INDEX BY BINARY_INTEGER;
MY_TBL
SET SERVEROUTPUT ON
-- DELETE
DECLARE
TYPE m_SmplTable IS TABLE OF VARCHAR2(128)
INDEX BY BINARY_INTEGER;
MY_TBL
BEGIN
MY_TBL(1) := 'One'; MY_TBL(3) := 'Three';
MY_TBL(-2) := 'Minus Two'; MY_TBL(0) := 'Zero';
MY_TBL(100) := 'Hundred';
DBMS_OUTPUT.enable;
DBMS_OUTPUT.put_line(MY_TBL(1)); DBMS_OUTPUT.put_line(MY_TBL(3));
DBMS_OUTPUT.put_line(MY_TBL(-2)); DBMS_OUTPUT.put_line(MY_TBL(0));
DBMS_OUTPUT.put_line(MY_TBL(100));
DBMS_OUTPUT.put_line('Count table is: '||TO_CHAR(MY_TBL.COUNT));
MY_TBL.DELETE(100);
DBMS_OUTPUT.put_line('Count table is: '||TO_CHAR(MY_TBL.COUNT));
MY_TBL.DELETE(1,3);
DBMS_OUTPUT.put_line('Count table is: '||TO_CHAR(MY_TBL.COUNT));
MY_TBL.DELETE;
DBMS_OUTPUT.put_line('Count table is: '||TO_CHAR(MY_TBL.COUNT));
END;
В данном случае сначала мы удалили 100-ю запись коллекции, затем с 1 по 3 и затем очистили всю коллекцию.
SET SERVEROUTPUT ON
-- EXISTS
DECLARE
TYPE m_SmplTable IS TABLE OF VARCHAR2(128)
INDEX BY BINARY_INTEGER;
MY_TBL
SET SERVEROUTPUT ON
-- EXISTS
DECLARE
TYPE m_SmplTable IS TABLE OF VARCHAR2(128)
INDEX BY BINARY_INTEGER;
MY_TBL
BEGIN
MY_TBL(1) := 'Miller';
MY_TBL(3) := 'Kolobok';
DBMS_OUTPUT.enable;
DBMS_OUTPUT.put_line('Count table is: '||TO_CHAR(MY_TBL.COUNT));
IF (MY_TBL.EXISTS(1)) THEN
DBMS_OUTPUT.put_line(MY_TBL(1));
ELSE DBMS_OUTPUT.put_line('MY_TBL(1) is not exist!');
END IF;
IF (MY_TBL.EXISTS(3)) THEN
DBMS_OUTPUT.put_line(MY_TBL(3));
ELSE DBMS_OUTPUT.put_line('MY_TBL(3) is not exist!');
END IF;
IF (MY_TBL.EXISTS(2)) THEN
DBMS_OUTPUT.put_line(MY_TBL(2));
ELSE DBMS_OUTPUT.put_line('MY_TBL(2) is not exist!');
END IF; END;
Count table is: 2
Miller
Kolobok
MY_TBL(2) is not exist!
Объявленной коллекции существуют только две строки с номером 1 и 3! А строка с номером 2 не существует.
Расширения оператора GROUP BY
Операторы ROLLUP, CUBE и GROUPING SETS являются
Расширения оператора GROUP BY
Операторы ROLLUP, CUBE и GROUPING SETS являются
Группировка ROLLUP приводит к созданию промежуточных итогов по столбцу назначения. Промежуточные итоги вычисляются от самого низкого уровня до общей суммы. То есть, включая ROLLUP во фразе GROUP BY, мы указываем Oracle, чтобы он просуммировал данные по уровнях указанных столбцов и подвел общий итог.
SELECT [column,] group_function(column). . .
FROM table
[WHERE condition]
[GROUP BY [ROLLUP] group_by_expression]
[HAVING having_expression];
[ORDER BY column];
Пример. Запрос просуммировал данные по зарплатам на уровнях по отделам, должностям
Пример. Запрос просуммировал данные по зарплатам на уровнях по отделам, должностям
Функция CUBE
Оператор CUBE формирует результирующий набор, представляющий собой многомерный куб. То
Функция CUBE
Оператор CUBE формирует результирующий набор, представляющий собой многомерный куб. То
Если запрос с CUBE возвращает больше значений, чем вам нужно, то лишнее можно спрятать в представление или вложенный запрос.
В дополнение к групповым под итогам и общим итогам, созданным ROLLUP, CUBE автоматически вычисляет все возможные комбинации возможных под итогов. Это предоставляет агрегированную, просуммированную информацию для каждой категории.
SELECT [column,] group_function(column)...
FROM table [WHERE condition]
[GROUP BY [CUBE] group_by_expression]
[HAVING having_expression]
[ORDER BY column];
Пример ниже показывает запрос, ранее уже использованный, но с функцией CUBE
Пример ниже показывает запрос, ранее уже использованный, но с функцией CUBE
SELECT department_id, job_id, SUM(salary)
FROM employees
WHERE department_id < 60
GROUP BY CUBE (department_id, job_id) ;
Различия между CUBE и ROLLUP: CUBE создает результирующий набор, содержащий статистические
Различия между CUBE и ROLLUP: CUBE создает результирующий набор, содержащий статистические
Функция GROUPING
Для того, чтобы однозначно определить промежуточные итоги и чтобы улучшить обработку NULL-значений в строках, созданных ROLLUP и CUBE, корпорация Oracle представила новую функцию GROUPING, которая возвращает значение 1, если строка - это под итог, созданная ROLLUP или CUBE, и 0 в противном случае. Пример в следующим слайде показывает запрос, уже использованный ранее, с функциями DECODE и GROUPING, применение которых позволяет более элегантно обрабатывать null-значения, созданные ROLLUP и CUBE.
SELECT [column,] group_function(column) .. ,
GROUPING(expr)
FROM table [WHERE condition]
[GROUP BY [ROLLUP][CUBE] group_by_expression]
[HAVING having_expression]
[ORDER BY column];
Пример:
Пример:
Функция GROUPING SETS
GROUPING SETS является дальнейшее расширение предложения GROUP BY,
Функция GROUPING SETS
GROUPING SETS является дальнейшее расширение предложения GROUP BY,
SELECT department_id, job_id, manager_id, AVG(salary)
FROM employees
GROUP BY
GROUPING SETS
((department_id, job_id, manager_id),
(department_id, manager_id),(job_id, manager_id));
Сравните предыдущий пример со следующей альтернативой:
SELECT department_id, job_id, manager_id, AVG(salary)
FROM employees
GROUP BY CUBE(department_id, job_id, manager_id);
Следующий запрос вычисляет все 8 (2 * 2 * 2) группировки,
Следующий запрос вычисляет все 8 (2 * 2 * 2) группировки,
SELECT department_id, job_id, manager_id, AVG(salary)
FROM employees
GROUP BY department_id, job_id, manager_id
UNION ALL
SELECT department_id, NULL, manager_id, AVG(salary)
FROM employees
GROUP BY department_id, manager_id
UNION ALL
SELECT NULL, job_id, manager_id, AVG(salary)
FROM employees
GROUP BY job_id, manager_id;
Выполните все примеры и сравните.
Объединение группировок
Объединение групп - краткий путь для создания полезной комбинации группировок.
Объединение группировок
Объединение групп - краткий путь для создания полезной комбинации группировок.
Пример. Рассмотрим пример объединенной группировки:
(job_id, manager_id) (1)
(department_id,job_id, manager_id) (2)
(job_id)(3)
(department_id,manager_id)(4)
(department_id) (5)
Рассчитывается общая зарплата для каждой из этих групп. Запрос и результат представлены на следующем слайде
Интерактивный запрос значений переменных в командах Oracle
При рассмотрении команды INSERT
Интерактивный запрос значений переменных в командах Oracle
При рассмотрении команды INSERT
одиночный амперсанд (&);
двойной амперсанд (&&);
команда DEFINE.
В SQL*Plus можно использовать переменные подстановки с одним амперсандом (&) для временного хранения значений.
SELECT employee_id, last_name, salary, department_id
FROM employees
WHERE employee_id = &employee_num ;
Переменные в SQL*Plus можно предопределить с помощью команд ACCEPT или DEFINE.
Переменные в SQL*Plus можно предопределить с помощью команд ACCEPT или DEFINE.
условие WHERE;
предложение ORDER BY;
выражение столбца;
имя таблицы.
В предложении WHERE даты и символьные значения должны быть заключены в апострофы. Это правило относится и к переменным подстановки. Амперсанд можно использовать также с функциями типа UPPER и LOWER. Чтобы пользователю не нужно было вводить должность заглавными буквами, используйте функцию UPPER (‘&job_title’).
SELECT employee_id, last_name, job_id, &column_name
FROM employees
WHERE &condition
ORDER BY &order_column ;
Пользовательские переменные можно определять до выполнения команды SELECT. Для определения и
Пользовательские переменные можно определять до выполнения команды SELECT. Для определения и
DEFINE переменная = значение (создает пользовательскую переменную с типом данных CHAR).
DEFINE job_title = IT_PROG
DEFINE job_title
DEFINE JOB_TITLE = "IT_PROG" (CHAR)
Если для удаления переменных используется команда UNDEFINE, проверить изменения можно командой DEFINE. При выходе из SQL*Plus все переменные, заданные во время сеанса, теряются.
Использование двойного знака «&&» для переменной подстановки. Для многократного использования значения переменной без повторных приглашений пользователю ввести значение можно использовать переменную подстановки с двойным амперсандом (&&). Пользователь получает приглашение ввести значение только один раз.
SELECT employee_id, last_name, job_id, &&column_name
FROM employees
ORDER BY &column_name;
Значение введенной после сдвоенного знака «&» переменной подстановки («table_name» в данном случае) запоминается в SQL*Plus, и в следующий раз при обращении к этой переменной подстановки ее значение вводится автоматически:
SELECT * FROM &&table_name;
Пример: Напишите запрос, который вы выводил информацию об имени, фамилии и
Пример: Напишите запрос, который вы выводил информацию об имени, фамилии и
должен запрашивать у пользователя информацию о номере отдела и выводить информацию только о пользователях соответствующего отдела;
должен показывать пользователю не только результаты запроса, но и текст запроса со значением, введенным пользователем;
Примечание: Если вы используете Oracle SQL Developer, то для вывода результатов запроса в текстовом виде нужно использовать клавишу F5.
Код соответствующих команд может выглядеть так:
SET VERIFY ON;
SELECT first_name As "Имя", last_name As "Фамилия",
salary As "Оклад"
FROM employees
WHERE department_id = &Номер_Отдела;
Конструкции MERGE и WITH
Оператор MERGE
MERGE — DML-оператор вставки (INSERT)/обновления (UPDATE)/удаления
Конструкции MERGE и WITH
Оператор MERGE
MERGE — DML-оператор вставки (INSERT)/обновления (UPDATE)/удаления
В сервере Oracle Database 10g будут срабатывать триггеры BEFORE UPDATE, INSERT и/или DELETE – в зависимости от указанных в операторе MERGE операций, так как в этом операторе предложения WHEN MATCHED THEN UPDATE (когда совпадают, то обновить) и WHEN NOT MATCHED THEN INSERT (когда не совпадают, то вставить) являются обязательными.
Оператор MERGE иногда называют еще UPSERT = UPdate + inSERT, так как он как бы объединяет в себе два этих оператора. Данный оператор позволяет обновить основную таблицу с помощью вспомогательной таблицы. При совпадении ключевых полей в строках обеих таблиц строки основной таблицы обновляются данными из совпавших строк вспомогательное таблицы; все не совпавшие строки из вспомогательной таблицы добавляются в основную таблицу.
Пример. Следующий пример использует таблицу BONUSES в примере схемы ОЕ со
Пример. Следующий пример использует таблицу BONUSES в примере схемы ОЕ со
Предварительные действия для получения доступа к схеме ОЕ:
1)
ALTER USER oe ACCOUNT UNLOCK;
ALTER USER oe IDENTIFIED BY OE;
GRANT SELECT ON oe.orders TO hr;)
2)
CREATE TABLE bonuses(employee_id NUMBER,
bonus NUMBER DEFAULT 100);
3)
INSERT INTO hr.bonuses(employee_id)
(SELECT e.employee_id FROM hr.employees e, oe.orders o
WHERE e.employee_id = o.sales_rep_id
GROUP BY e.employee_id);
4)
SELECT *
FROM bonuses
ORDER BY employee_id;
EMPLOYEE_ID BONUS
----------- ------
4)
SELECT *
FROM bonuses
ORDER BY employee_id;
EMPLOYEE_ID BONUS
----------- ------
153 100
154 100
155 100
156 100
158 100
159 100
160 100
161 100
5)
MERGE INTO bonuses1 D
USING (SELECT employee_id, salary, department_id
FROM employees WHERE department_id = 80) S
ON (D.employee_id = S.employee_id)
WHEN MATCHED THEN
UPDATE
SET D.bonus = D.bonus + S.salary*.01
DELETE
WHERE (S.salary > 8000)
WHEN NOT MATCHED THEN
INSERT (D.employee_id, D.bonus)
VALUES (S.employee_id, S.salary*.01)
WHERE (S.salary <= 8000);
6)
SELECT *
FROM bonuses
ORDER BY employee_id;
EMPLOYEE_ID BONUS
----------------- -----------
153
6)
SELECT *
FROM bonuses
ORDER BY employee_id;
EMPLOYEE_ID BONUS
----------------- -----------
153
154 175
155 170
159 180
160 175
161 170
164 72
165 68
166 64
167 62
171 74
172 73
173 61
179 62
Пример : Слияние строк - пример показывает соответствия EMPLOYEE_ID в таблице
Пример : Слияние строк - пример показывает соответствия EMPLOYEE_ID в таблице
MERGE INTO copy_emp AS c
USING employees e
ON (c.employee_id = e.employee_id)
WHEN MATCHED THEN
UPDATE SET
c.first_name = e.first_name,
c.last_name = e.last_name,
c.email = e.email,
c.phone_number = e.phone_number,
c.hire_date = e.hire_date,
c.job_id = e.job_id,
c.salary = e.salary,
c.commission_pct = e.commission_pct,
c.manager_id = e.manager_id,
c.department_id = e.department_id
WHEN NOT MATCHED THEN
INSERT VALUES(e.employee_id, e.first_name, e.last_name,
e.email, e.phone_number, e.hire_date, e.job_id,
e.salary, e.commission_pct, e.manager_id,
e.department_id);
Использование конструкции WITH
Oracle допускает вынесение определений подзапросов из тела основного
Использование конструкции WITH
Oracle допускает вынесение определений подзапросов из тела основного
Фраза WITH используется в двух целях:
• для придания запросу формулировки, более понятной программисту (просто subquery factoring) и
• для записи рекурсивных запросов (recursive subquery factoring).
Используя оператор WITH, можно определить блок запроса, прежде чем использовать его в запросе. Это особенно удобно, если запрос имеет несколько ссылок на один и тот же блок запросов и в нем есть объединения и агрегатные функции.
Использование WITH, сервер Oracle получает результаты запроса блока и сохраняет его во временном табличном пользователя. Это может улучшить производительность.
Фраза WITH предшествует фразе SELECT и позволяет привести сразу несколько предварительных формулировок подзапросов для ссылки на них в ниже формулируемом основном запросе. Общая схема употребления демонстрируется следующей схемой:
WITH
A AS ( SELECT ... )
, B AS (
WITH
A AS ( SELECT ... )
, B AS (
, C AS ( SELECT ... FROM x, y )
SELECT ... FROM A, B, C
;
Пример. Необходимо выдать список департаментов, имеющих фонд заработной платы больший, чем 1/8 фонда заработной платы всего предприятия.
WITH
summary AS (
SELECT d.department_name AS department,
SUM(e.salary) AS dept_total
FROM employees e, departments d
WHERE e.department_id = d.department_id
GROUP BY d.department_name)
SELECT department, dept_total
FROM summary
WHERE dept_total > (
SELECT SUM(dept_total) * 1/8
FROM summary )
ORDER BY dept_total DESC;
Пример . Запрос создает запросы DEPT_COSTS и AVG_COST, а затем использует
Пример . Запрос создает запросы DEPT_COSTS и AVG_COST, а затем использует
WITH
dept_costs AS (
SELECT d.department_name, SUM(e.salary) AS dept_total
FROM employees e JOIN departments d
ON e.department_id = d.department_id GROUP BY d.department_name),
avg_cost AS ( SELECT SUM(dept_total)/COUNT(*) AS dept_avg FROM dept_costs)
SELECT * FROM dept_costs WHERE dept_total >(SELECT dept_avg FROM avg_cost)
ORDER BY department_name;
Пример . Выдать фамилию сотрудников, у которых зарплата больше средней по компании, их зарплату и департамент (два метода):
SELECT e1.Last_name,e1.salary,e1.department_id, e2.average
FROM employees e1,
(SELECT ROUND(AVG(salary)) as average
FROM employees ) e2 WHERE e1.salary>e2.average ORDER BY salary;
-----с оператором------------WITH------------------
WITH t1 AS
(SELECT last_name,salary FROM employees),
t2 AS (SELECT ROUND(AVG(salary)) AS average FROM employees)
seleCT last_name,salary,average
FROM t1,t2 WHERE salary > average ORDER BY salary;
Формулирование рекурсивных запросов
С версии Oracle 11.2 фраза WITH может использоваться для
Формулирование рекурсивных запросов
С версии Oracle 11.2 фраза WITH может использоваться для
Общий алгоритм вычисления фразой WITH таков:
Результат := пусто;
Добавок := исходный SELECT ...;
Пока Добавок не пуст выполнять:
Результат := Результат
{UNION ALL | UNION | INTERSECT | EXCEPT}
Добавок;
Добавок := рекурсивный SELECT ... FROM Добавок …;
конец цикла;
Предложение SELECT для исходного множества строк Oracle называет опорным (anchor) членом
Предложение SELECT для исходного множества строк Oracle называет опорным (anchor) членом
Приведем простой пример употребления фразы WITH для построения рекурсивного запроса:
WITH
numbers ( n ) AS (
SELECT 1 AS n FROM dual -- исходное множество -- одна строка
UNION ALL -- символическое "объединение" строк
SELECT n + 1 AS n -- рекурсия: добавок к предыдущему результату
FROM numbers -- предыдущий результат в качестве источника данных
WHERE n < 5 -- если не ограничить, будет бесконечная рекурсия
)
SELECT n FROM numbers -- основной запрос
;
Операция UNION ALL здесь используется символически, в рамках определенного контекста, для
Операция UNION ALL здесь используется символически, в рамках определенного контекста, для
Результат:
N
----
1
2
3
4
5
Строка с n = 1 получена из опорного запроса, а остальные строки — из рекурсивного. Из примера видна оборотная сторона рекурсивных формулировок: при неаккуратном планировании они допускают "бесконечное" выполнение (на деле — пока хватит ресурсов СУБД для сеанса или же пока администратор не прервет запрос или сеанс). С фразой CONNECT BY "бесконечное" выполнение в принципе невозможно. Программист обязан отнестись к построению рекурсивного запроса ответственно.
Еще один вывод из этого примера: подобно случаю с CONNECT BY, вынесенный рекурсивный подзапрос применим вовсе не обязательно только к иерархически организованным данным.
Пример с дополнительным разъяснением способа выполнения:
SQL> WITH
2 anchor1234 ( n
Пример с дополнительным разъяснением способа выполнения:
SQL> WITH
2 anchor1234 ( n
3 SELECT 1 FROM dual UNION ALL
4 SELECT 2 FROM dual UNION ALL
5 SELECT 3 FROM dual UNION ALL
6 SELECT 4 FROM dual
7 )
8 , numbers ( n ) AS ( -- рекурсивный
9 SELECT n FROM anchor1234
10 UNION ALL
11 SELECT n + 1 AS n
12 FROM numbers
13 WHERE n < 5
14 )
15 SELECT n FROM numbers
16 ;
Разбор решения:
N
----------
1 опорный запрос
……..
4 опорный запрос
2 рекурсия 1
……..
5 рекурсия 1
3 рекурсия 2
4 рекурсия 2
5 рекурсия 2
4 рекурсия 3
5 рекурсия 3
5 рекурсия 4
Пример. Вычислить какое количество сотрудников ежегодно поступает на работу с 2002
Пример. Вычислить какое количество сотрудников ежегодно поступает на работу с 2002
WITH period ( year ) AS ( SELECT 2002 AS year FROM dual
UNION ALL
SELECT year + 1 AS year FROM period WHERE year < 2010 )
SELECT p.year, COUNT ( e.employee_id )
FROM employees e RIGHT OUTER JOIN period p
ON p.year = EXTRACT ( YEAR FROM e.hire_date )
GROUP BY p.year
ORDER BY p.year;
Древовидные запросы
В Oracle реализованы так называемые запросы, предназначенные для работы с данными, организованными в виде дерева. Для реализации дерева в виде таблицы в ней должно быть дополнительных два поля: id узла и id родительского узла. Также должен быть корень (корни).
SELECT [LEVEL], column, expr... FROM table
[WHERE condition(s)]
[START WITH condition(s)]
[CONNECT BY PRIOR condition(s)] ;
Для реализации древовидных запросов имеются два дополнительных предложения:
START WITH - для идентификации коренных строк
CONNECT BY - для связи строк-потомков и строк-предков
Необязательный оператор START WITH (начать с) - если задано, то позволяет
Необязательный оператор START WITH (начать с) - если задано, то позволяет
Обязательный оператор CONNECT BY. Условие после CONNECT BY (соединить по) - задает зависимость между родительскими и дочерними вершинами (строками) иерархии. Тут надо сказать Ораклу, как долго продолжать цикл. Что-то в духе while в обычных языках программирования. Например, мы можем попросить достать нам 10 строк: ROWNUM <=10 – он и создаст нам в цикле ровно 10 одинаковых строк. ROWNUM это "псевдостолбец", в котором нумеруются строки, начиная от 1 в порядке их выдачи. Его можно использовать не только в иерархических запросах. Но это уже другая история.
Фигурирующие в документации по Oracle "псевдостолбцы" подобны "системным переменным", но в отличие от них способны давать в запросах на разных строках разные значения, которые вычисляются по мере выполнения определенных фаз обработки запроса и доступны для использования на последующих фазах обработки, образуя как бы дополнительный "столбец".
Чтобы получить нормальную иерархию нужно использовать специальный оператор, который называется PRIOR.
Чтобы получить нормальную иерархию нужно использовать специальный оператор, который называется PRIOR.
Используя информацию, заданную в этих предложениях, Oracle формирует иерархию следующим образом:
1. Выбирает корневую строку (строки) иерархии в соответствии с условиями заданными во фразе START WITH. Если эта фраза опущена, то Oracle будет использовать все строки таблицы в качестве корневых (попробуйте). Условие в START WITH может содержать подзапрос.
2. Для каждой корневой строки выбираются дочерние строки, каждая из которых должна удовлетворять условию фразы CONNECT BY по отношению к одной из корневых строк.
3. Oracle выбирает следующие одно за другим поколения дочерних строк. Сначала
3. Oracle выбирает следующие одно за другим поколения дочерних строк. Сначала
4. Если запрос содержит фразу WHERE, то Oracle исключает из иерархии все строки, которые не удовлетворяют условию, заданному в этой фразе.
5. Oracle возвращает результат запроса, в котором выбранные строки расположены в иерархическом порядке, то есть потомки выводятся после своих родителей.
Порядок строк — это хорошо, но нам было бы трудно понять, две строки рядом это родитель и его потомок или два брата-потомка одного родителя. Пришлось бы сверять id и pid., Oracle предлагает в помощь дополнительный псевдостолбец LEVEL. Как легко догадаться, в нем записывается уровень записи по отношению к корневой. Так, 1-ая запись будет иметь уровень 1, ее потомки уровень 2, потомки потомков — 3 и т.д.
Пример . Показать менеджеров и сотрудников пока employee_id=101.
SELECT employee_id, last_name, job_id,
Пример . Показать менеджеров и сотрудников пока employee_id=101.
SELECT employee_id, last_name, job_id,
START WITH employee_id = 101
CONNECT BY PRIOR manager_id = employee_id ;
Оператор SELECT, осуществляющий древовидный запрос, может использовать псевдостолбец LEVEL, содержащий уровень
Оператор SELECT, осуществляющий древовидный запрос, может использовать псевдостолбец LEVEL, содержащий уровень
Пример .
START WITH employee_id = 100;
SELECT LPAD(' ',2*(LEVEL-1)) || last_name org_chart, department_id, manager_id, job_id
FROM employees
START WITH job_id = 'AD_PRES'
CONNECT BY PRIOR employee_id = manager_id;
Используя функцию LPAD и псевдостолбец LEVEL можно украсить выводимый результат, предварительно
Используя функцию LPAD и псевдостолбец LEVEL можно украсить выводимый результат, предварительно
SELECT LPAD (' ', (LEVEL-1)*3) || LEVEL || ' ' || last_name AS tree, EMPLOYEE_ID,job_id,manager_id
FROM EMPLOYEES
CONNECT BY PRIOR EMPLOYEE_ID = manager_id;
Напишем запрос, который возвращал бы из таблицы hr.employees информацию:
имя сотрудника;
фамилию сотрудника;
уровень
Напишем запрос, который возвращал бы из таблицы hr.employees информацию:
имя сотрудника;
фамилию сотрудника;
уровень
путь подотчетности в формате /руководитель1/руководитель2/ сотрудник.
SELECT first_name "NAME", last_name "LAST_NAM",
LEVEL - 1 As "Level podchin", SYS_CONNECT_BY_PATH (first_name || ' ' || last_name, '/') As "Podotchetn"
FROM employees
START WITH employee_id = 100
CONNECT BY PRIOR employee_id = manager_id
ORDER By LEVEL;
Чтобы указать БД в Oracle, что сортировать надо только в пределах
Чтобы указать БД в Oracle, что сортировать надо только в пределах
SELECT first_name "NAME", last_name "LAST_NAM",
LEVEL - 1 As "Level podchin", SYS_CONNECT_BY_PATH (first_name || ' ' || last_name, '/') As "Podotchetn"
FROM employees
START WITH employee_id = 100
CONNECT BY PRIOR employee_id = manager_id
ORDER SIBLINGS By first_name ;
Пример. Запрос вниз по иерархии должностей
SELECT LEVEL - 1 As "Level podchin",
SYS_CONNECT_BY_PATH ( job_id, '/' ) rrrrr, last_name "Fam"
FROM employees
START WITH job_id = 'AD_PRES'
CONNECT BY PRIOR employee_id = manager_id ;
Запрос «в строку»
SELECT SYS_CONNECT_BY_PATH(last_name,' --> ') TREE
FROM employees
CONNECT BY PRIOR manager_id=employee_id
Транзакции в Oracle SQL.
В Oracle нет явного оператора, чтобы начать
Транзакции в Oracle SQL.
В Oracle нет явного оператора, чтобы начать
Все изменения со стороны индивидуальных команд DML заносятся Oracle в БД только группами, в рамках транзакции, по завершению транзакции.
Завершение транзакции с фиксацией изменений, внесенных операторами DML, происходит только по выдаче (а) команды COMMIT или (б) оператора DDL (скрытым образом завершающего свои действия по изменению таблиц словаря-справочника той же командой COMMIT).
Операторы управления транзакциями:
COMMIT
ROLLBACK
SAVEPOINT
ROLLBACK TO
SET TRANSACTION
COMMIT - завершает транзакцию и делает любые выполненные в ней изменения
COMMIT - завершает транзакцию и делает любые выполненные в ней изменения
ROLLBACK - Оператор отката завершает транзакцию и отменяет все выполненные в ней и незафиксированные изменения. Для этого он читает информацию из сегментов отката и восстанавливает блоки данных в состояние, в котором они находились до начала транзакции. Освобождаются блокировки.
По завершении транзакции необходимо явно указывать одну из команд завершения транзакции иначе за вас это сделает среда, в которой вы работаете (а среда не всегда это делает так, как вы предполагаете).
SAVEPOINT - Позволяет создать в транзакции точку сохранения. В одной транзакции можно выполнять оператор SAVEPOINT несколько раз, устанавливая несколько точек сохранения. Точки сохранения позволяют устанавливать маркеры внутри транзакции таким образом, чтобы была возможность отмены только части работы, проделанной в транзакции. Оправдано использование точек сохранения в продолжительных и сложных транзакциях. ORACLE освобождает блокировки, которые были установлены отменённым оператором.
ROLLBACK TO <точка сохранения> - Этот оператор используется совместно с представленным выше оператором SAVEPOINT. Транзакцию можно откатить до указанной точки сохранения, не отменяя все сделанные до нее изменения. Таким образом, можно выполнить два оператора UPDATE, затем — оператор SAVEPOINT, а после него — два оператора DELETE. При возникновении ошибки или исключительной ситуации в ходе выполнения операторов DELETE транзакция будет откатываться до указанной оператором SAVEPOINT точки сохранения; при этом будут отменяться операторы DELETE, но не операторы UPDATE.
SET TRANSACTION - Этот оператор позволяет устанавливать атрибуты транзакции, такие как уровень изолированности и то, будет ли она использоваться только для чтения данных или для чтения и записи. Этот оператор также позволяет привязать транзакцию к определенному сегменту отката.
Некоторые особенности выполнения транзакций в Oracle:
1.Транзакция обычно состоит из нескольких операторов
Некоторые особенности выполнения транзакций в Oracle:
1.Транзакция обычно состоит из нескольких операторов
Savepoint statement1;
Оператор1;
If error then rollback to statement1;
Savepoint statement2;
Оператор2;
If error then rollback to statement2;
2. Понятие неделимости распространяется на необходимую глубину. Например, мы вставляет записи в таблицу 1, что вызывает срабатывание триггера на вставку записей в таблицу 2, что в свою очередь вызывает срабатывание триггера на обновление таблицы 3 и так далее. Если в какой-то момент происходит откат нашего оператора по таблице 1, то отменяются и все изменения, произведенные в таблице 2,3, и т.д. То есть или все изменения фиксируются, или все отменяется.
3. Oracle анонимный блок PL/SQL считает оператором. Например, begin оператор1; оператор2;
3. Oracle анонимный блок PL/SQL считает оператором. Например, begin оператор1; оператор2;
4. Ограничение целостности проверяются после выполнения каждого sql-оператора. Oracle разрешает делать некоторые строки таблицы несогласованными до конца выполнения sql-оператора.
5. В Oracle есть возможность отложить проверку целостности на любой момент времени до конца транзакции. Это реализуется с помощью ограничения deferrable таблицы и перевода ограничения в режим deferred.
6. В целях экономии места в сегментах отката не фиксируйте изменения чаще, чем это нужно по логике программы. Просто нужно определить оптимальный размер сегментов отката.
7. В Oracle можно использовать распределенные транзакции, то есть выполнять транзакцию, в которой операторы работают на удаленных сервера (распределенная база данных). Для доступа к удаленной базе данных используется объект database link. Распределённая транзакция выглядит примерно так:
update table1 set x=1;
update remote_table1@remote_database set x=1;
commit;
Распределённая транзакция имеет то же свойство, что и обычная: все или
Распределённая транзакция имеет то же свойство, что и обычная: все или
8. В Oracle продолжительность транзакции не ограничивается, потому что проблемы поедания ресурсов блокировками не существует. Транзакция длится столько, сколько нужно приложению. Единственная проблема: при очень длительных транзакциях и маленьком сегменте отката возможна ошибка ORA-1555.
Существует рекомендации Тома Кайта, которые (как мне кажется) очень верны:
При разработке приложений баз данных я использую очень простую мантру:
если можно, сделай это с помощью одного оператора SQL(потому что это будет быстрее);
если это нельзя сделать с помощью одного оператора SQL, сделай это в PL/SQL;
если это нельзя сделать в PL/SQL, попытайся использовать хранимую процедуру на языке Java;
если это нельзя сделать в Java, сделай это в виде внешней процедуры на языке С;
если это нельзя реализовать в виде внешней процедуры на языке С, надо серьезно подумать, зачем это вообще делать.