Общее представление о базе данных (стр. 3 )

GROUP BY – для группировки записей по значениям одного или нескольких столбцов. Если в SQL–выражении исп–ся оп–ор WHERE, задающий фильтр записей, то GROUP BY находится и вып–ся после него. Для опр–ия, какие записи должны войти в группы, служит оп–ор HAVING, используемый совместно с GROUP BY. Если нет HAVING, то группировке принадлежат все записи выбранные WHERE. Если WHERE не исп–ся то группируются все записи исходной таблицы. GROUP BY собирает записи в группы и упорядочивает их по алфавиту.

HAVING(имеющие, при условии) применяется с группировкой и задает фильтр записей в группах. SELECT Регион, Сумма_заказа FROM Клиенты GROUP BY Регион, Сумма_заказа HAVING Сумма_заказа>500;

34. Команды INSERT, UPDATE, DELETE.

Значения могут быть помещены и удалены из полей тремя командами языка DML (Язык Манипулирования Данными): INSERT (ВСТАВИТЬ), UPDATE (МОДИФИЦИРОВАТЬ), DELETE (УДАЛИТЬ). В самой простой форме INSERT использует следующий синтаксис:

INSERT INTO имя табл VALUES (<value>, . . .); Если вам нужно ввести пустое значение (NULL), вы вводите его точно так же, как и обычное значение. Предположим, что ещё нет поля city для мистера Peel. Вы можете вставить в строку значение=NULL в это поле следующим образом: INSERT INTO Salespeople VALUES (1001, 'Peel', NULL, .12); Так как значение NULL это специальный маркёр, а не просто символьное значение, оно не заключается в одинарные кавычки. Вы можете также указывать столбцы, куда вы хотите вставить значение имени. Это позволяет вам вставлять имена в любом порядке.

Предположим, что вы берёте значения для таблицы Заказчиков из отчёта, выводимого на принтер, который помещает их в таком порядке: city, cname, cnum; и, для упрощения, вы хотите ввести значения в том же порядке: INSERT INTO Customers (city, cnamе, cnum) VALUES ('London', 'Honman', 2001); Обратите внимание, что столбцы rating и snum отсутствуют. Это значит, что в эти строки автоматически установлены значения по умолчанию. По умолчанию может быть введено значение NULL или другое значение, определяемое по умолчанию. Если ограничение запрещает использование значения NULL в данном столбце и в этот столбец не установлено значение по умолчанию, этот столбец должен быть обеспечен значением для любой команды INSERT, которая относится к таблице. INSERT, чтобы получать или выбирать значения из одной таблицы и помещать их в другую для использования их вместе с запросом. Чтобы сделать это, вы просто заменяете предложение VALUES (из предыдущего примера) на соответствующий запрос: INSERT INTO Londonstaff SELECT * FROM Salespeople WHERE city = 'London';

Вы можете удалять строки из таблицы командой модификации DELETE. Она может удалять только строки, а не индивидуальные значения полей, так что параметр поля является необязательным или недоступным. Чтобы удалить всё содержание таблицы Продавцов, вы можете ввести следующее условие: DELETE FROM Salespeople; Теперь, когда таблица пуста, её можно окончательно удалить командой DROP TABLE. Обычно вам нужно удалить только некоторые определённые строки из таблицы. Чтобы определить, какие строки будут удалены, вы используете предикат так же, как вы это делали для запросов. Например, чтобы удалить продавца Axelrod из таблицы, вы можете ввести DELETE FROM Salespeople WHERE snum = 1003; Мы использовали поле snum вместо поля sname, потому что лучшая тактика – использование первичных ключей, когда вы хотите, чтобы действию подвергалась одна, и только одна, строка. Для вас это аналогично действию первичного ключа. Конечно, вы можете также использовать DELETE с предикатом, который выбирал бы группу строк, как показано в этом примере: DELETE FROM Salespeople WHERE city = 'London';

Теперь, когда вы уже можете вводить и удалять строки таблицы, вы должны узнать, как изменять некоторые или все значения в существующей строке. Это выполняется командой UPDATE. Эта команда содержит предложение UPDATE, в котором указано имя используемой таблицы, и предложение SET, которое указывает на изменение, выполняемое для определенного столбца. Например, чтобы изменить оценки всех заказчиков на 200, вы можете ввести UPDATE Customers SET rating = 200; Конечно, вы не всегда захотите указывать все строки таблицы для изменения единственного значения, так что UPDATE, наподобие DELETE, может использовать предикаты. Вот как, например, можно выполнить изменение, одинаковое для всех заказчиков продавца Peel (имеющего snum=1001): UPDATE Customers SET rating = 200 WHERE snum = 1001;

Однако вы не обязаны ограничивать себя модифицированием единственного столбца с помощью команды UPDATE. Предложение SET может назначать любое число столбцов, отделяемых запятыми. Все указанные назначения могут быть сделаны для любой табличной строки, но только для одной в каждый момент времени. Предположим, что продавец Motika ушел на пенсию и мы хотим переназначить его номер новому продавцу: UPDATE Salespeople SET sname = 'Gibson', city = 'Boston', comm = .10 WHERE snum = 1004;

Эта команда передаст новому продавцу Gibson, всех текущих заказчиков бывшего продавца Motika и заказы в том виде, в котором они были скомпонованы для Motika, с помощью поля snum. Вы не можете, однако, модифицировать сразу несколько таблиц в одной команде, отчасти потому, что вы не можете использовать префиксы таблиц со столбцами, изменёнными предложением SET. Другими словами, вы не можете сказать "SET Salespeople. sname = Gibson" в команде UPDATE, вы можете сказать только так: "SET sname = Gibson".

Вы можете использовать скалярные выражения в предложении SET команды UPDATE, включив его в выражение поля, которое будет изменено. В этом их отличие от предложения VALUES команды INSERT, в котором выражения не могут использоваться; это свойство скалярных выражений – весьма полезная особенность. Предположим, что вы решили удвоить комиссионные всем вашим продавцам. Вы можете использовать следующее выражение: UPDATE Salespeople SET comm = comm * 2;

Всякий раз, когда вы обращаетесь к указанному значению столбца в предложении SET, произведённое значение может получится из текущей строки; прежде в ней будут сделаны какие–то изменения с помощью команды UPDATE. Естественно, вы можете скомбинировать эти особенности и сказать "удвоить комиссию всем продавцам в Лондоне" таким предложением: UPDATE Salespeople SET comm = comm * 2 WHERE city = 'London';

Предложение SET это не предикат. Оно может вводить пустые NULL–значения так же, как оно вводило значения, не используя какого–то специального синтаксиса (такого, например, как IS NULL). Так что, если вы хотите установить все оценки заказчиков в Лондоне в NULL, вы можете ввести следующее предложение: UPDATE customers SET rating = NULL WHERE city = 'London'; что обнулит все оценки заказчиков в Лондоне.

35. Команды управления доступа к данным. GRANT, REVOKE, SET ROLE.

Предоставление системных привилегий. Администратор базы данных предоставляет системные привилегии пользователям с помощью команды GRANT. Пользователь может пользоваться предоставленными привилегиями сразу после их получения. GRANT привилегия [,привилегия...] TO пользователь [, пользователь...]; Привилегия – предоставляемая системная привилегия, пользователь – имя пользователя.

CREATE SESSION – Позволяет начать сеанс работы с базой данных

CREATE TABLE – Позволяет создавать таблицы в пользовательской схеме

CREATE SEQUENCE – Позволяет создавать последовательности в пользовательской схеме

CREATE VIEW – Позволяет создавать представления в пользовательской схеме

CREATE PROCEDURE – Позволяет создавать хранимые процедуры, функции или пакеты в пользовательской схеме

Отмена привилегий, предоставленных другим пользователям, производится при помощи команды revoke. Команда REVOKE отменяет указанные вами привилегии для всех указанных пользователей, которым они были предоставлены. REVOKE { привилегия [, привилегия...] | ALL} ON объект FROM {пользователь [, пользователь...] | роль | PUBLIC} [CASCADE CONSTRAINTS]. CASCADE CONSTRAINTS – используется для отмены ограничений, наложенных на объект с помощью привилегии REFERENCES для сохранения ссылочной целостности. REVOKE select ON lease FROM las, volena;

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

SET ROLE Имя_роли; пр. SET ROLE RECORDS_CLERK; С помощью одного оператора можно назначить и несколько ролей: SET ROLE RECORDS_CLERK, ROLE2, ROLE3;

В некоторых реализациях языка (например, Oracle) все назначаемые пользователю роли автоматически становятся ролями по умолчанию, т. е. ролями, по умолчанию назначаемыми пользователю при входе в систему.

36. Управление транзакциями с помощью SQL.

Транзакции

INSERT – Добавляет новую строку в таблицу. UPDATE – Изменяет существующие строки таблицы. DELETE – Удаляет строки из таблицы. COMMIT – Делает все незафиксированные изменения постоянными. SAVEPOINT – Позволяет произвести откат до определенной точки сохранения. ROLLBACK – Отменяет все незафиксированные изменения данных.

Транзакции базы данных. Содержат что–либо из следующего: Команды DML, выполняющие единое согласованное изменение данных; Одна команда DDL; Одна команда DCL; Начинаются с выполнения первой исполняемой команды SQL; Заканчиваются одним из следующих событий: Выполнение команды COMMIT или ROLLBACK; Выполнение команды DDL или DCL (автоматическая фиксация) ; Ошибка, завершение сеанса работы или аварийный останов системы

Обработка транзакций. Транзакции обеспечивают большую гибкость, более широкий спектр средств управления при изменении данных в случае ошибки в пользовательском процессе или сбоя системы. Транзакции состоят из команд DML, которые вносят в данные одно согласованное изменение. Например, перевод средств между двумя счетами включает дебетование одного счета и кредитование другого на одну и ту же сумму. Успешно или неудачно выполненными должны быть одновременно признаны оба эти действия. Оставлять в базе данные о кредитовании без данных о дебетовании нельзя.

Типы транзакций Манипулирование данными (DML) – Состоит из произвольного количества предложений DML, воспринимаемых сервером ORACLE как одно целое или как логическая единица таблицы; Определение данных (DDL) – Состоит из одного предложения DDL; Управление данными (DCL) – Состоит из одного предложения DCL

Управление транзакциями. Для управления логикой транзакций используются команды COMMIT(Завершает текущую транзакцию, делая постоянными все произведенные изменения данных), SAVEPOINT(Устанавливает в текущей транзакции маркер точки сохранения (savepoint)), ROLLBACK(Прекращает текущую транзакцию, отменяя все произведенные изменения в данных). Каждое изменение данных, выполненное в ходе транзакции, является временным до тех пор, пока транзакция не будет зафиксирована. Состояние данных, предшествующее выполнению команд COMMIT или ROLLBACK: Операции по обработке данных изменяют только буфер базы данных. Следовательно, предыдущее состояние данных может быть восстановлено. Текущий пользователь может просмотреть результаты своих операции по обработке данных, запрашивая данные из таблиц. Другие пользователи не могут видеть результаты операций обработки данных, выполняемых текущим пользователем. Изменяемые строки блокируются, и другие пользователи не имеют возможности менять их содержимое.

Все внесенные изменения фиксируются с помощью команды COMMIT, в результате чего: Измененные данные записываются в базу данных. Предшествующее состояние базы данных теряется. Все пользователи могут видеть результаты выполненной операции. Блокировка с измененных строк снимается, другие пользователи получают возможность вносить в них изменения. Все точки сохранения удаляются. INSERT INTO owner (Non, Ow, AdO) VALUES (15, ‘’, ‘Фадеева 45–54’); UPDATE lease SET Non=54 WHERE Nlease = 3; COMMIT;

Все незафиксированные изменения отменяются командой ROLLBACK, в результате чего: Сделанные изменения теряются. Разблокируются строки, с которыми производились операции. DELETE FROM rent; ROLLBACK;

Команда SAVEPOINT позволяет создавать в текущей транзакции маркеры, разбивающие ее на единицы меньшего размера. После этого с помощью команды ROLLBACK TO SAVEPOINT можно отменять незафиксированные изменения до этого маркера. Создание второй точки сохранения с тем же именем, что и первая, вызывает удаление первой. Если при выполнении команды обнаружена ошибка, то часть транзакции можно отменить при помощи неявного отката. Если в ходе транзакции возникла ошибка в одной единственно команде DML, отменяется только результат этой команды, т. е. производится откат на уровне этой команды. Но изменения, внесенные во время этой транзакции предыдущими командами DML, сохраняются. Завершать транзакции рекомендуется явно.

37. Процедурное расширение SQL. Структура программы в PL/SQL.

PL/SQL – процедурное расширение языка SQL уже является языком программирования в отличие от SQL. Т е PL/SQL с одной стороны полностью совместим со стандартным SQL, а значит имеет все языковые возможности запроса к данным БД. С другой стороны это язык программирования, структурный язык, позволяющий создавать процедуры, функции и пакеты а значит создавать хранимые процедуры и функции, т е поддерживать архитектуру клиент–сервер. PL/SQL – язык сервера oracle. Тем самым PL/SQL и его аналоги являются попыткой сохранения мощности доступа к данным БД дополнительно получив мощности языков программирования 3 уровня (С, Pascal). Отсюда и возникла возможность оставить в качестве основной архитектуры 2–х звенную. PL/SQL – блочно–структурированный язык, т е в общем виде в программном модуле можно выделить след блоки.

Т е в модуле PL/SQL можно выделить следующие типы блоков:

1. Блок DECLARE предназначен для описания объектов программного модуля, те переменных, констант, типов данных и т д.

2. Тело программы может состоять из множества вложенных блоков BEGIN–END – 3

4. Блок EXEPTION в какой то степени является уникальным, т к впервые появился именно в расширениях языка SQL и только позже в ЯП.

38. Типы данных определяемые пользователем RECORD и TABLE.

Рассмотрим подробнее типы данных TABLE и RECORD, позволяющие создавать

одномерные массивы и записи, широко используемые в программах PL/SQL.

Таблица PL/SQL – это одномерный массив с неограниченным числом строк. Для

объявления этого массива (таблицы PL/SQL или TABLE) необходимо сначала

определить его тип данных.

Для описания типа данных TABLE используется синтаксис:

TYPE type_name IS TABLE OF { column_type | variable%TYPE |

table. column%TYPE } [NOT NULL] INDEX BY BINARY_INTEGER;

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

таблиц PL/SQL, и "column_type" – любой из скалярных типов данных: CHAR, DATE или

NUMBER. С помощью атрибута %TYPE можно установить "type_name" соответствующим

типу данных какой–либо переменной (variable) или столбца (table. column).

Имя (например, name_plsql_table), которое описывается табличным типом

данных, называется таблицей PL/SQL. Это описание, размещаемое в разделе DECLARE,

имеет вид:

name_plsql_table type_name;

Ссылки на строки таблицы PL/SQL осуществляются аналогично ссылкам на элементы

одномерного массива:

name_plsql_table(index) ,

где index принадлежит типу BINARY_INTEGER. Например, для ссылки на третью строку

в таблице PL/SQL "ename_tab" следует написать: ename_tab(3).

Для присвоения значения конкретной строке таблицы PL/SQL используется

синтаксис:

name_plsql_table(index) := expr;

Для ввода в таблицу PL/SQL значений из какого–либо столбца базовой таблицы

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

использовать цикл. (Примеры таких операций приведены в п. 12.4.)

Записи PL/SQL

Record PL/SQL – это совокупность полей, каждое из которых должно иметь

уникальное имя (в пределах записи). Эти поля могут принадлежать различным

типам данных.

Если создаваемая запись (sotr) соответствует описанию столбцов какой–либо

базовой таблицы (например, kadry), то ее объявление можно осуществить в разделе

DECLARE с помощью атрибута %ROWTYPE:

sotr kadry%ROWTYPE;

В противном случае для объявления записи необходимо сначала определить ее

тип данных. Для описания типа данных RECORD используется синтаксис:

TYPE type_name IS RECORD

( field_name1 {field_type | variable%TYPE | table. column%TYPE

| table%ROWTYPE} [NOT NULL],

field_name2 {field_type | variable%TYPE | table. column%TYPE

| table%ROWTYPE} [NOT NULL],

...);

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

записей PL/SQL, и "field_type" – любой тип данных. С помощью атрибута %TYPE

можно установить "type_name" соответствующим типу данных какой–либо переменной

(variable) или столбца (table. column). Атрибут %ROWTYPE позволяет определить

поле как запись, соответствующую описанию столбцов какой–либо базовой таблицы.

При объявлении типа записи можно присвоить ее полям некоторые значения. Если

же для поля вводится ограничение NOT NULL (для предотвращения назначения пустых

значений), то этому полю надо обязательно присвоить значение. Например:

TYPE SotrRecTyp IS RECORD (nomer NUMBER(4) NOT NULL := 1001,

familiy CHAR(20), dolgnost CHAR(14), otdel NUMBER(3) := 102);

Объявление создаваемой записи (например, name_plsql_record) производится в

разделе DECLARE и имеет вид:

name_plsql_record type_name;

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

name_plsql_record. field_name;

Для присвоения значения конкретному полю записи используется синтаксис:

name_plsql_record. field_name := expr;

Примеры использования записей в программах PL/SQL приведены ниже.

Для ввода в таблицу PL/SQL значений из какого–либо столбца базовой таблицы

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

использовать цикл.

39. Предложение INTO для присваивания переменным значений БД.

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

Пример: Declare

Current_stud % ROWTIPE

BEGIN

SELECT N_z, Fam, Im, INTO Current_stud. N_z, Current_stud. Fam, Current_stud. Im

FROM Student WHERE N_z=’25/05’ END

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

40. Курсоры.

Если результатом подзапроса при передаче данных из взу в прогу возвращается больше одной строки данных, тогда исп–ся механизм курсора. Курсор по существу представляет собой имя многострочного запроса(файла). Обработка данных курсора вып–ся: 0.объявление или описание курсора Declare 1. открытие курсора Open 2. циклическая обработка текущего значения, возвращаемого курсором, причем передача данных в переменных проги осущ–ся с помощью FETCH... INTOзакрытие курсора, те циклическая обработка возвращаемых значений подразумевает исп–е операторов цикла или разветвлений. Для более детального описания набора строк возвращаемых курсором допускается исп–е параметров курсора. CURSOR имя_курсора IS выражение_выборки; Напр DECLARE CURSOR list_gr (‘Ngr’, CHAR(5)) IS SELECT Nz, Fam, Im FROM student WHERE N_gr=Ngr... BEGIN... OPEN list_gr (‘1231.01’)

Выборка данных из курсора может быть выполнена в набор переменных подходящих типов командой FETCH: FETCH имя_курсора INTO [переменная1, переменная2...] | [имя_записи]; имя_курсора – имя описанного ранее курсора; переменная – выходная переменная для размещения результата; имя_записи – имя ранее определенной записи. Учитывая что курсор это сложный объект проги PLSQL предусматривает ряд спец атрибутов облегчающих работу с курсором %IS OPEN – возвращает истина если открыт и ложь в противном случае, %ROW COUNT –атрибут числового типа, содержит число строк, обработанных после открытия курсора, т. е в момент открытия курсора=0; %FOUND –логический атрибут, значение которого равно TRUE до тех пор, пока не окажется, что последняя команда FETCH не вернула строку; %NOT FOUND–истина токо в том случае, когда все строки курсора выбраны.

41. Операторы управления IF. . . THEN. . . , GO TO.

IF <условие> THEN <оператор> ELSE IF <усл> THEN <опер>... ELSE <опер> END IF

GO TO <метка> := <имя>

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

42. Циклы LOOP, WHILE, FOR.

LOOP <тело цикла + EXIT> END LOOP (петля) после выполнения последней команды в послед–ти управление снова передается 1–ой команде, и всё сначала. приводит к бесконечному повторению последовательности команд, если внутри нее нет команд EXIT (выход из цикла), RAISE (вызов обработчика исключительных ситуаций) или GOTO (безусловный переход). Например,

LOOP последовательность команд; IF условие THEN EXIT; END LOOP; приведет к выходу из цикла после выполнения последовательности команд, как только условие станет истинным.

Цикл WHILE предназначен для повторения последовательности команд, пока условие остается истинным:

WHILE условие LOOP последовательность команд; END LOOP;

Наиболее распространен цикл FOR, имеющий следующий синтаксис:

FOR описание параметра цикла LOOP последовательность команд; END LOOP;

43. Обработка ошибок в PL/SQL. Встроенные исключительные ситуации.

Специфика PL/SQL связана не только с возможностью обработки данных БД, но и с возможностью написания клиентских приложений, кот–е подразумевают ввод–вывод данных. ИС типа БД подвержены многим критическим операциям. Именно по этому технология БД впервые вынуждена была применить спец методы, механизмы позволяющие поддерживать целостность данных БД. В результате модули PL/SQL приобрели спец блок EXEPTION. И только позже обычные яп стали исп–ть аналогичные методы, однако даже самые первые компиляторы яп исп–ли встроенные обработки ошибок, кот–е передавались через системные переменные. PL/SQL выделяют 2 различных механизма обработки искл ситуаций.

Встроенные ис –системные переменные, к–ые хранятся и анализируются СУБД с целью управления БД. (аналогичны системным переменным яп, трансляторов).

Oracle включает четырнадцать встроенных исключительных ситуаций,

соответствующих типовым ошибкам:

CURSOR_ALREADY_OPEN – Попытка открытия уже открытого курсора

DUP_VAL_ON_INDEX – Попытка вставить дубликат значения для уникального индекса

INVALID_CURSOR – Попытка выполнения запрещенной операции с курсором (например, закрытие неоткрытого курсора)

INVALID_NUMBER – Отказ преобразования строки символов в число

LOGIN_DENIED – Неправильное имя пользователь/пароль

NO_DATA_FOUND – Предложение SELECT...INTO возвращает ноль строк

NOT_LOGGED_ON – Нет подключения к Oracle7

PROGRAM_ERROR – Внутренняя ошибка PL/SQL

STORAGE_ERROR – Пакет PL/SQL вышел из пределов памяти или если память разрушена

TIMEOUT_ON_RESOURCE – Истекло время ожидания ресурса Oracle7

TOO_MANY_ROWS – Предложение SELECT...INTO возвращает более одной строки

TRANSACTION_BACKED_OUT – Удаленный сервер отменил транзакцию

VALUE_ERROR – Арифметическая ошибка, ошибка преобразования, усечения или ограничения

ZERO_DIVIDE – Попытка деления на ноль

Если в раздел EXCEPTION программы (блока) включена фраза WHEN имя_исключения THEN текст_обработчика_исключения; с именем какого–либо встроенного исключения и возникла соответствующая ошибка,

то вместо прекращения исполнения программы и выдачи типового сообщения об

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

Такой обработчик может, например, выяснить ситуацию, при которой произошло

деление на ноль, и выдать правдоподобный результат операции деления или

прервать исполнение программы и дать сообщение об изменении каких–либо данных.

В последнем случае это может быть не типовое сообщение "Вы пытаетесь делить на

ноль", а любое подготовленное пользователем сообщение, например, инструкцию

длиной до 2048 символов.

Для выдачи сообщения об ошибке, обеспечения возврата в среду, из которой

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

текущей транзакции, целесообразно использовать процедуру RAISE_APPLICATION_ERROR(errnum, errtext);

где errnum – отрицательное целое число в диапазоне –20–20999 и errtext

– символьная строка длиной до 2048 символов.

Так как в большом приложении могут часто повторяться встроенные или

пользовательские исключительные ситуации, то целесообразно создать в базе

данных таблицу (например, USERERR) с уникальными номерами (error_number) и

текстами (error_text) исключений. Это позволит избежать определения лишних

сообщений об ошибках и сделать их согласованными во всем приложении.

При использовании такой таблицы и процедуры RAISE_APPLICATION_ERROR надо

описать в в разделе DECLARE блока две переменных (например, errnum типа NUMBER

и errtext типа VARCHAR2) и использовать в обработчике исключений конструкцию:

WHEN TOO_MANY_ROWS THEN

SELECT error_number, error_text INTO errnum, errtext FROM usererr

WHERE error_number = 20008;

RAISE_APPLICATION_ERROR(errnum, errtext);

Или WHEN TOO_MANY_ROWS THEN

SELECT error_number, error_text INTO errnum, errtext FROM usererr

WHERE errtext LIKE 'Много строк; обр%';

RAISE_APPLICATION_ERROR(errnum, errtext);

44. Процедура Raise_Application_Error.

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

Синтаксис вызова процедуры.

RAISE_APPLICATION_ERROR(номер_ошибки, сообщение[, {TRUE|FALSE}]);

где номер_ошибки – номер исключения в диапазоне от –20000 до –20999, сообщение – сообщение об исключении, заданное пользователем до 2048 байтов, TRUE|FALSE – необязательный логический параметр (TRUE указывает на то, что исключение необходимо помещать в стек предыдущих ошибок, FALSE указывает на то, что исключение должно заместить все предыдущие).

Пример использования процедуры RAISE_APPLICATION_ERROR.

...

EXCEPTION

...

WHEN NO_DATA_FOUND THEN

RAISE_APPLICATION_ERROR(–20000,'Указан неправильный офис!');

WHEN OTHERS THEN

RAISE_APPLICATION_ERROR(–20200,'Произошла непредвиденная ошибка!');

END;

ВАЖНО! Процедура RAISE_APPLICATION_ERROR вызывается только из хранимых подпрограмм.

45. Исключительные ситуации, определяемые пользователем.

Кроме встроенных могут быть использованы собственные исключительные

ситуации, имена которых необходимо описать в разделе DECLARE блока PL/SQL

(например, err_stavka EXCEPTION). В разделе EXCEPTION блока должен быть

описан соответствующий обработчик исключительной ситуации, например

WHEN err_stavka THEN

SELECT error_number, error_text INTO errnum, errtext FROM usererr

WHERE errtext LIKE 'Число ставок должно находиться%';

RAISE_APPLICATION_ERROR(errnum, errtext);

В теле основной программы определяемые пользователем ошибки обычно проверяются

с помощью операторов условия (IF...THEN). Для передачи управления обработчику

пользовательской исключительной ситуации в случае обнаружения ошибки

используется оператор RAISE имя_пользовательского_исключения

Например

IF :new. stavka NOT BETWEEN 0.25 AND 100 THEN

RAISE err_stavka;

END IF;

46. Анонимные блоки PL/SQL. Процедуры, функции и пакеты.

Анонимные блоки PL/SQL. Процедуры, функции и пакеты

В анонимном блоке PL/SQL нет раздела заголовка, блок начинается ключевым словом DECLARE.

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

Общий формат анонимного блока PL/SQL таков:

[ DECLARE... необязательные операторы объявлений... ]

BEGIN... исполняемые операторы...

[ EXCEPTION... необязательные операторы обработки исключений... ]

END;

Квадратные скобки указывают на необязательную часть синтаксиса. Ключевые слова BEGIN и END являются обязательными, и между ними должен быть как минимум один исполняемый оператор.

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

Именование процедур, функций и пакетов

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

Допустимые предложения и ограничения для процедур и пакетов

Тело независимой процедуры или пакетированной процедуры может быть любым законным блоком PL/SQL, который может содержать любые предложения DML, включая SELECT (которое должно включать фразу INTO или быть предложением SELECT в определении курсора), SELECT... FOR UPDATE, INSERT, UPDATE, DELETE или LOCK, а также любой код PL/SQL.

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

Обзор SESSION_ROLES не работает внутри хранимой процедуры. Хотя пользователям PL/SQL недоступны некоторые возможности ORACLE (например, предложение COMMIT в программной единице PL/SQL не может включать опцию FORCE), такие возможности предоставляются стандартными пакетированными процедурами. Эти процедуры описываются в приложении A этого документа.

Следующий перечень объясняет особенности использования типов данных LONG и LONG RAW внутри программных единиц PL/SQL:

* Предложение SQL внутри процедуры или пакета может вставлять данные в столбец типа данных LONG и LONG RAW.

* Если данные из столбца LONG или LONG RAW могут быть преобразованы в ограниченный тип данных (такой как CHAR или VARCHAR2), то такой столбец LONG или LONG RAW может адресоваться в предложении SQL внутри процедуры или пакета. Заметьте, что самый длинный тип данных в PL/SQL составляет 32 килобайта.

47. Триггеры базы данных. Операторные и строчные триггеры. Примеры.

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

Ограничение триггера – простое логическое выражение кот–е может быть задано в предложении when командой create trigger

CREATE TRIGGER reorder AFTER UPDATE OF onhand, reorder ON stock FOR EACH ROW WHEN (:new. onhand>=:new. reorder) BEGIN INSERT INTO reorder VALUES (:new. id, :new. onhand, :new. reorder); END reorder

2 типа триггеров: операторные(вып–ся 1 раз); строчные(вып–ся столько раз сколько строк связаны с данным телом тригера) FOR EACH ROW

Триггер на каскадное обновление (таблицы Rent –Realty)

CREATE OR REPLACE TRIGGER CASCADE_UPD_RNT_RLTY

AFTER UPDATE ON RENT

FOR EACH ROW

BEGIN

IF (:NEW. Type <> :OLD. Type) THEN

UPDATE REALTY SET REALTY. Type = :NEW. Type WHERE

REALTY. Type= :OLD. Type;

END IF;

END;

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

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

Преимущества представлений: 1)Ограничивается доступ к базе данных вследствие того, что представления могут отображать определенное подмножество базы данных. 2)С помощью простых запросов пользователь может такие же результаты, как с помощью сложных. Например, использование представлений позволяет осуществить выборку данных из нескольких таблиц без каких – либо знаний об операторе соединения таблиц (join). 3)Обеспечивается независимость данных для пользователей, посылающих случайные незапрограммированные запросы, и прикладных программ. Одно представление может использоваться для выборки данных из нескольких таблиц. 4)Использование представлений позволяет обеспечить доступ к данным по различным критериям для различных групп пользователей.

Создание представления CREATE [OR REPLACE] [FORCE | NOFORCE] VIEW имя [(псевдоним [, псевдоним]...)] AS подзапрос [WITH CHECK OPTION [CONSTRAINT ограничение]][WITH READ ONLY]

FORCE – создание представления независимо от того, существуют ли базовые таблицы

NOFORCE создание представления только при условии существования базовых таблиц. Значение принято по умолчанию. Псевдоним – имена выражений, выбранных в запросе для представления. Количество псевдонимов должно быть = количеству выражений, выбранных представлением. Подзапрос – полная команда SELECT. Для столбцов в списке SELECT можно использовать псевдонимы. WITH CHECK OPTION режим, при котором добавлять или обновлять можно только строки, доступные в представлении. Ограничение – имя, присвоенное ограничению CHECK OPTION. WITH READ ONLY запрет применения к данному представлению операций DML.

Запрос, который определяет представление, может содержать команду SELECT со сложным синтаксисом, включая соединения, группы и подзапросы.

Запрос, который определяет представление, не может содержать предложение ORDER BY

Если вы не указываете имя ограничения сами, система присвоит его по умолчанию в формате SYS_Сn.

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

Существуют 2 вида представлений: простые(одна таблица, нет ф–ий, операции DML есть) и сложные(одна и более таблиц, содержит ф–ии, операции DML нет). Основное отличие связано с операциями DML. Операции DML могут выполняться над представлениями в соответствии со следующими правилами:Удаление строки из представления возможно, если представление не содержит ничего из следующего(Условие соединения, Групповые функции, Предложение GROUP BY, Команда DISTINCT), Данные в представлении могут быть изменены, если представление не содержит ничего из вышеперечисленного и ничего из следующего списка:(Столбцы, описанные как выражения, например, Rn*1.5, Псевдостолбец ROWNUM), Добавление данных через представление возможно, если оно не содержит ничего из вышеперечисленного и если в базовой таблице нет столбцов типа NOT NULL, не включенных в представление. Все необходимые значения должны присутствовать в представлении. Помните, что через представление вы добавляете значения прямо в основную таблицу.