Взаимодействие с сервером Oracle

1. Взаимодействие с сервером Oracle

2. Рассматриваемые вопросы

4-2
Какие команды SQL могут непосредственно
указываться в исполняемом блоке PL/SQL
Использование команд языка манипулирования
данными (DML) в PL/SQL
Использование предложения INTO для
запоминания значений, возвращаемых командой
SQL
Отличия между неявными и явными курсорами
Использование атрибутов курсоров SQL

3. Команды SQL в PL/SQL

4-3
Выборка строки данных из базы данных с
помощью команды SELECT. Должна быть
возвращена только одна строка.
Внесение изменений в строки таблиц базы данных
с помощью команд DML.
Управление транзакциями с помощью команд
COMMIT, ROLLBACK и SAVEPOINT.

4. Команды SELECT в PL/SQL

Выборка данных из базы данных производится с
помощью команды SELECT
Синтаксис:
SELECT
INTO
FROM
[WHERE
4-4
список_выборки
{имя_переменной[, имя_переменной]...
| имя_записи}
таблица
условие];

5. Команды SELECT в PL/SQL

Предложение INTO обязательно.
Запросы должны возвращать одну и только одну
строку.
Пример :
SET SERVEROUTPUT ON
DECLARE
fname VARCHAR2(25);
BEGIN
SELECT first_name INTO fname
FROM employees WHERE employee_id=200;
DBMS_OUTPUT.PUT_LINE(' First Name is : '||fname);
END;
/
4-5

6. Выборка данных в PL/SQL

Выборка даты найма и оклада для определенного
служащего.
Пример:
DECLARE
emp_hiredate
employees.hire_date%TYPE;
emp_salary
employees.salary%TYPE;
BEGIN
SELECT
hire_date, salary
INTO
emp_hiredate, emp_salary
FROM
employees
WHERE
employee_id = 100;
END;
/
4-6

7. Выборка данных в PL/SQL

Найти сумму окладов всех служащих для заданного
отдела.
Пример:
SET SERVEROUTPUT ON
DECLARE
sum_sal NUMBER(10,2);
deptno
NUMBER NOT NULL := 60;
BEGIN
SELECT SUM(salary) -- group function
INTO sum_sal FROM employees
WHERE department_id = deptno;
DBMS_OUTPUT.PUT_LINE ('The sum of salary is '
|| sum_sal);
END;
/
4-7

8. Правила присвоения имен

DECLARE
hire_date
employees.hire_date%TYPE;
sysdate
hire_date%TYPE;
employee_id
employees.employee_id%TYPE := 176;
BEGIN
SELECT
hire_date, sysdate
INTO
hire_date, sysdate
FROM
employees
WHERE
employee_id = employee_id;
END;
/
4-8

9. Правила присвоения имен

4-9
Во избежание неоднозначности предложения
WHERE придерживайтесь выработанной системы
присвоения имен.
Избегайте использования имен столбцов базы в
качестве идентификаторов.
Синтаксические ошибки могут возникнуть
вследствие того, что PL/SQL сначала ищет
столбец с данным именем в таблице.
Имена локальных переменных и формальных
параметров имеют приоритет над именами
таблиц базы данных.
Имена столбцов имеют приоритет над именами
локальных переменных.

10. Манипулирование данными в PL/SQL

Внесение изменений в таблицы базы данных с
помощью команд DML:
• INSERT
DELETE
• UPDATE
• DELETE
• MERGE
INSERT
UPDATE
4 - 10
MERGE

11. Вставка данных

Пример добавления информации о новом служащем в
таблицу 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;
/
4 - 11

12. Обновление данных

Пример увеличения оклада всех служащих из таблицы
EMPLOYEES, должность которых – клерк на складе:
DECLARE
sal_increase
employees.salary%TYPE := 800;
BEGIN
UPDATE
employees
SET
salary = salary + sal_increase
WHERE
job_id = 'ST_CLERK';
END;
/
4 - 12

13. Удаление данных

Пример удаления информации о служащих 10 отдела
из таблицы EMPLOYEES:
DECLARE
deptno
employees.department_id%TYPE := 10;
BEGIN
DELETE FROM employees
WHERE department_id = deptno;
END;
/
4 - 13

14. Слияние строк

Вставка или изменение строк в таблице COPY_EMP для
установления соответствия с таблицей EMPLOYEES.
DECLARE
empno employees.employee_id%TYPE := 100;
BEGIN
MERGE INTO copy_emp c
USING employees e
ON (e.employee_id = empno)
WHEN MATCHED THEN
UPDATE SET
c.first_name
= e.first_name,
c.last_name
= e.last_name,
c.email
= e.email,
. . .
WHEN NOT MATCHED THEN
INSERT VALUES(e.employee_id, e.first_name, e.last_name,
. . .,e.department_id);
END;
/
4 - 14

15. Курсор SQL

Курсор – это указатель на приватную область
памяти, выделенную сервером Oracle.
Имеется два типа курсоров:
– Неявные курсоры: создаются и внутренне
сопровождаются сервером Oracle для обработки
команд SQL
– Явные курсоры: явно объявляются программистом
4 - 15

16. Атрибуты курсора SQL, используемые неявными курсорами

Атрибуты курсора SQL позволяют пользователю
проверить результат выполнения своей команды SQL.
4 - 16
SQL%FOUND
Логический атрибут, возвращающий
значение “истинно“ (TRUE), если последняя
команда SQL обработала одну или
несколько строк.
SQL%NOTFOUND
Логический атрибут, возвращающий
значение “истинно“ (TRUE), если последняя
команда SQL не обработала ни одной
строки.
SQL%ROWCOUNT
Количество строк, обработанных последней
командой SQL (целое число).

17. Атрибуты курсора SQL, используемые неявными курсорами

Пример удаления строк с конкретным номером
служащего из таблицы EMPLOYEES и вывод количества
удаленных строк.
VARIABLE rows_deleted VARCHAR2(30)
DECLARE
empno employees.employee_id%TYPE := 176;
BEGIN
DELETE FROM employees
WHERE employee_id = empno;
:rows_deleted := (SQL%ROWCOUNT ||
' row deleted.');
END;
/
PRINT rows_deleted
4 - 17

18. Итоги

4 - 18
Использование в блоке PL/SQL команд DML.
Использование в командах SELECT предложения
INTO, обязательно указываемого в PL/SQL.
Отличия между явными и неявными курсорами.
Использование атрибутов курсора SQL для
определения результатов выполнения команды
SQL.

19. Обзор практического занятия 4

4 - 19
Выборка данных из таблицы
Вставка данных в таблицу
Обновление данных в таблице
Удаление строки из таблицы