Присваивание переменным значений базы данных

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

 

SELECT … INTO … - когда возвращается 1 строка

 

BEGIN

SELECT id, customerid, orderdate

INTO currentorder.id, currentorder.customerid, currentorder.orderdate

FROM orders

WHERE id=453;

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

Курсор - это имя многострочного запроса или указатель на контекстную область, с помощью которого программа PL/SQL управляет этой областью и ее состоянием во время обработки.
В языке PL/SQL курсоры используются для управления обработкой SQL-операторов select. В одних случаях курсоры объявляются явно, а других программист предоставляет PL/SQL самому выполнить эту операцию.
Явное объявление курсора производится в области DECLARE, причем указанный в определении SQL-оператор содержит команды select.
Для обработки явного курсора в PL/SQL необходимо выполнить 4 шага:
1) Объявление курсора

При объявлении курсора ему назначается имя и ставится в соответствие некоторый оператор SELECT.
Синтаксис объявления курсора таков:
CURSOR имя_курсора IS оператор_select
где имя_курсора - это имя курсора,

оператор_select - запрос, который будет обрабатываться.

 

Открытие курсора для запроса

Синтаксис открытия курсора таков:
OPEN имя_курсора;
где имя_курсора - предварительно объявленный курсор.

Выбор результатов в переменные PL/SQL

Производится считывание строк из курсора.

Оператор FETCH имеет две формы:
FETCH имя_курсора INTO список_переменных;
и
FETCH имя_курсора INTO запись_ PL/SQL;
где имя_курсора - обозначает объявленный и открытый курсор,

список_переменных - представляет собой список объявленных переменных PL/SQL, разделенных запятыми,

запись_ PL/SQL - предварительно объявленная запись PL/SQL.

Закрытие курсора

Когда выбран весь активный набор, курсор следует закрыть и освободить отведенные для него ресурсы.
Синтаксис закрытия курсора таков:
CLOSE имя_курсора;
где имя_курсора - ранее открытый курсор.

Неявно объявляемые курсоры
Оператор select указывается в теле блока, и PL/SQL берет на себя всю заботу об определении курсора, выполняя соответствующие действия неявно. При этом программисту не требуется вносить в секцию DECLARE никаких дополнительных объявлений.
Каждый оператор select выполняется в пределах контекстной области и поэтому имеет курсор, указывающий на конкретную контекстную область. Такой курсор называется SQL-курсором. В отличии от явных курсоров SQL-курсор не открывается и не закрывается программой. PL/SQL неявно открывает SQL-курсор, обрабатывает SQL- оператор и закрывает этот курсор.

Примеры:

CURSOR ordercursor IS select id, customerid, orderdate from orders;

 

DECLARE

CURSOR ordercursor (ordernumber NUMBER) IS

SELECT id, customerid, orderdate FROM orders

WHERE id > ordernumber;

BEGIN

OPEN ordercursor (3)

В данном примере возвращаемый набор ordercursor включает строки таблицы orders, для которых идентификатор id > 3

 

Атрибуты курсора:

%ISOPEN - открыт курсор (true, false)

%ROWCOUNT - число строк, выбранных после открытия курсора

%NOTFOUND - последняя команда выборки возвратила строку, то

%FOUND (%NOTFOUND – false, %FOUND – true) и наоборот

 

8. Процедуры, функции, пакеты.

Процедуры

Процедуры (или хранимые процедуры) – это определенный набор инструкций, написанных на языке PL/SQL, и операторов DML. Вызов процедуры приводит к выполнению содержащихся в ней инструкций. Процедура хранится в базе данных, поэтому она и называется хранимой.

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

Создание процедуры
Синтаксис оператора CREATE OR REPLACE PROCEDURE таков:
CREATE [OR REPLACE] PROCEDURE имя_процедуры
[(аргумент [{IN | OUT |IN OUT}] тип,
аргумент [{IN | OUT |IN OUT}] тип }] {IS | AS}
тело_процедуры,
где имя_процедуры - это имя создаваемой процедуры,

аргумент - имя параметра процедуры,

тип - это тип соответствующего параметра,

тело_ процедуры - блок PL/SQL

Между именем входного параметра и его типом можно указывать два специальных слова – IN и/или OUT. Указание IN будет означать, что в данной переменной процедуре было передано входное значение. Указание OUT означает, то эту переменную можно использовать для возврата какого-то значения. Можно также указывать одновременно IN и OUT. Если ничего не указано, то подразумевается IN.

 

Для изменения текста процедуры необходимо удалить и повторно создать ее, поэтому ключевые слова OR REPLACE (или заменить) позволяют выполнить такую операцию за один раз.

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

CREATE OR REPLACE PROCEDURE имя_процедуры AS
/* Раздел объявлений. */
BEGIN /* Выполняемый раздел. */
EXCEPTION /* Раздел исключительных ситуаций. */
END [ имя_процедуры ].

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

Значения параметров по умолчанию
Как и переменные, формальные параметры процедуры или функции могут иметь значения по умолчанию. Значение по умолчанию указывается следующим образом:
имя_параметра [ вид ] тип {:= | DEFAULT} исходное_значение,
где имя_параметра - это имя формального параметра,

вид - вид параметра (IN, OUT или INOUT),

исходное_значение - значение, присваиваемое формальному параметру по умолчанию. Можно применять:= или ключевое слово DEFAULT.
Удаление процедур
DROP PROCEDURE имя_процедуры.

Хранимые процедуры

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

 

CREATE PROCEDURE deletecustomer (custid IN INTEGER) AS

last VARCHAR2(50);

first VARCHAR2(50);

BEGIN

SELECT lastname, firstname INTO last, first

FROM customer WHERE id=custid;

INSERT INTO customerhistory VALUES (custid, last, first)

DELETE FROM customer WHERE id=custid;

EXCEPTION

WHEN no_data_found THEN

RAISE_APPLICATION_ERROR (-20123, ‘invalid Customer ID’)

END deletecustomer;

 

Функции

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

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

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

Синтаксис для создания хранимой функции:
CREATE [OR REPLACE] FUNCTION имя_функции [(аргумент [{IN | OUT |IN OUT}] тип, аргумент [{IN | OUT |IN OUT}] тип)]
RETURN возвращаемый _тип {IS | AS}
тело_функции,
где имя_функции - это имя функции;

аргумент и тип аналогичны при создании процедуры;

возвращаемый_тип - это тип значения, возвращаемого функцией; тело_функции - блок PL/SQL

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

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

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

Удаление функций
DROP FUNCTION имя_функции.

 

CREATE FUNCTION findcustid (last IN VARCHAR2, first IN VARCHAR2)

RETURN INTEGER AS

custid INTEGER;

BEGIN

SELECT id INTO custid FROM customer

WHERE lastname=last AND firstname=first;

RETURN custid;

EXCEPTION

WHEN no_data_found THEN

RAISE_APPLICATION_ERROR (-20101, ‘invalid Customer ID’)

END findcustid;

Пакеты

Пакет - это конструкция PL/SQL, позволяющая хранить связанные объекты в одном месте. Преимущества пакета:

-облегчает процесс разработки;

- дополнительная функциональность (глобальные переменные);

- повышает производительность системы.

Пакет состоит из двух различных частей: описания и тела, каждая из которых хранится по отдельности в словаре данных. В пакет могут входить: процедуры, функции, курсоры, типы и переменные.
CREATE [OR REPLACE] PACKAGE имя_пакета {IS |AS}
описание_процедуры |
описание_функции |
объявление_переменной |
определение_типа |
объявление_исключительной_ситуации |
объявление_курсора
END [ имя_пакета ];
Тело пакета
Тело пакета (package body) - это объект словаря данных, хранящийся отдельно от заголовка пакета. Любое предварительное объявление в заголовке пакета должно быть раскрыто в его теле. Описание процедуры или функции должно быть таким же и включать в свой состав имя подпрограммы, имена ее параметров и вид каждого параметра.
Вызов процедуры аналогичен вызову процедуры, не включенной в пакет. Единственное отличие такого вызова - присутствие перед именем процедуры имени пакета.

 

CREATE OR REPLACE PACKAGE customermanager IS

PROCEDURE newcustomer (company IN VARCHAR2 DEFAULT null,

last IN VARCHAR2, first IN VARCHAR2, …);

FUNCTION findcustid (last IN VARCHAR2, first IN VARCHAR2)

RETURN INTEGER;

PROCEDURE updatecustomer (custid IN INTEGER, fieldtype IN CHAR, newvalue IN VARCHAR2);

PROCEDURE deletecustomer (custid IN INTEGER);

PROCEDURE deletecustomer (last IN VARCHAR2, first IN VARCHAR2);

END customermanager;

 

CREATE OR REPLACE PACKAGE BODY customermanager AS

….

END customermanager;

9. Триггеры, их основные свойства и значение.

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

- Реализации сложных ограничений целостности данных, которые невозможно осуществить через описательные ограничения;
- Сложные проверки для защиты информации

- Слежения за информацией, хранимой в таблице, путем записи вносимых изменений и пользователей, вносящих эти изменения
- Автоматического оповещения других программ о том, что делать в случае изменения информации, содержащейся в таблице

Элементы триггера
Обязательными элементами триггера являются его имя, активизирующее событие и тело. Условие WHEN необязательно.
Типы триггеров
Тип триггера определяется тем, какое событие его активизирует: INSERT (ввод), UPDATE (обновление) или DELETE (удаление). Триггеры могут активизироваться до (BEFORE) или после (AFTER) операции, а также для строки (FOR EACH ROW) или оператора.

Ограничения для строчного триггера

FOR EACH ROW

WHEN (new.onhand < new.reorder)

Условные предикаты

INSERTING, UPDATING, DELETING

Ограничения, налагаемые на триггеры
В триггере нельзя задавать операторы управления транзакциями: COMMIT, ROLLBACK или SAVEPOINT.

Использование:old и:new в строковых триггерах
Строковый триггер срабатывает один раз для каждой строки, обрабатываемой активизирующим оператором. Внутри триггера можно обращаться к строке, обрабатываемой в данный момент. Для этого служат две псевдозаписи:old и:new. Хотя синтаксически они рассматриваются как записи, фактически они записями не являются. Поэтому их называют псевдозаписями. Псевдозапись:old не определена для операторов INSERT, а псевдозапись:new - для операторов DELETE.

Пример:

 

CREATE TRIGGER deletecustomer

BEFORE DELETE ON customer

FOR EACH ROW

BEGIN

INSERT INTO customerhistory

VALUES (:old.id,:old.lastname,:old.firstname);

END deletecustomer;

 

10. Параллельные архитектуры БД; масштабируемость, надежность, производительность.

Три основные архитектурные направления:

· Симметричные многопроцессорные системы (SMP) - форма сильносвязанных многопроцессорных систем, разделяющих единую оперативную память и дисковую подсистему;

· Слабосвязанные многопроцессорные системы (кластеры) - совокупность компьютеров, объединенных в единую систему быстродействующей сетью и имеющих общую дисковую подсистему;

· Системы с массовым параллелизмом (MPP) - системы с сотнями и даже тысячами процессоров, имеющие многоуровневую структуру оперативной памяти

Группы требований, определяющих качества современной СУБД:

· масштабируемость;

· производительность;

· возможность смешанной загрузки разными типами задач;

· обеспечение постоянной доступности данных (надежность или катастрофоустойчивость).

Масштабируемость - свойство вычислительной системы обеспечивать предсказуемый рост системных характеристик (число поддерживаемых пользователей, быстроты реакции, общей производительности) при добавлении к ней вычислительных ресурсов.

Факторы, влияющие на производительность СУБД:

· поддержка параллелизма (параллельный ввод/вывод, параллельные средства и утилиты администрирования, параллельная обработка запросов к базе данных)

· реализация многопотоковой архитектуры