лабораторный практикум. Русакова М.С. Лабораторный практикум. Практикум по базам данных самара 2015 министерство образования и науки российской федерации
 Скачать 0.72 Mb.
|
|
Пример DECLARE Arg1 Owner.Non%type; Arg2 Owner.Ow%type; Arg3 Owner.AdO%type; CURSOR own_curs IS SELECT * FROM owner; BEGIN OPEN own_curs; FOR I IN 1..5 LOOP FETCH own_curs INTO Arg1, Arg2, Arg3; DBMS_OUTPUT.PUT_LINE (‘Фамилия владельца с номером ’ || TO_CHAR(Arg1) || ‘: ’ || Arg2 || ‘. Его адрес: ’ || Arg3 || ‘.’); END LOOP; CLOSE own_curs; END; / Теперь проиллюстрируем, как можно было обойтись без цикла FOR. 80 Пример DECLARE Arg1 Owner.Non%type; Arg2 Owner.Ow%type; Arg3 Owner.AdO%type; CURSOR own_curs IS SELECT * FROM owner; BEGIN OPEN own_curs; FETCH own_curs INTO Arg1, Arg2, Arg3; EXIT WHEN own_curs%NOTFOUND; DBMS_OUTPUT.PUT_LINE (‘Фамилия владельца с номером ’ || TO_CHAR(Arg1) || ‘: ’ || Arg2 || ‘. Его адрес: ’ || Arg3 || ‘.’); END LOOP; CLOSE own_curs; END; Примечание. Если вы используете атрибут %ROWCOUNT, добавьте проверку на отсутствие строк с помощью атрибута %NOTFOUND, потому что в случае, если команда FETCH не вернет строку, счетчик %ROWCOUNT не увеличивается. Неявный курсор Неявный курсор используется в том случае, если запрос возвращает только одну строку, и вы желаете сохранить результат работы запроса в каких-либо переменных. Неявный курсор не именуется, для него не нужно выполнять открытие и закрытие курсора, не нужно указывать команду FETCH. В простейшем случае неявный курсор может быть представлен обычным запросом, результат работы которого записывается в переменные (в запросе присутствует INTO). Пример DECLARE Arg1 Owner.Non%type; Arg2 Owner.Ow%type; Arg3 Owner.AdO%type; BEGIN SELECT Owner.NOn, Owner.Ow, Owner.AdO INTO Arg1, Arg2, Arg3 FROM Owner WHERE Owner.NOn = 7; END; / 81 Задания 1. Сделать выборку данных из таблицы Lease с использованием курсора и цикла с методом %NOTFOUND для получения данных о договорах, заключенных не позже 2005 г. 2. Сделать выборку данных из таблицы Lease с использованием курсора и цикла с методом %FOUND для получения данных об арендаторах, фамилия которых начинается с определенной буквы. Подсказка: используйте цикл WHILE. 3. Выбрать данные об арендаторах и владельцах объектов, которые заключили договор в определенную дату. 4. Поменяйте местами адреса арендаторов и владельцев недвижимости. Если арендаторов (или владельцев) меньше, чем владельцев (или арендаторов, соответственно), то «лишние» адреса должны оставаться на месте. Подсказка: используйте одновременно 2 курсора, можете еще посчитать, кого меньше... 5. Напишите неявный курсор, который будет находить самый большой номер договора и количество арендаторов, заключивших договоры. Требования к сдаче лабораторной работы 1. Необходимо, чтобы были созданы курсоры, реализующие как метод %FOUND, так и %NOTFOUND. 2. Должен быть реализован неявный курсор. 82 Лабораторная работа №12 Подпрограммы. Пакеты Подпрограмма – это поименованный блок PL/SQL, который принимает параметры и может быть вызван. PL/SQL имеет два типа подпрограмм, называемых процедурами и функциями. Обычно процедуру вызывают для того, чтобы выполнить некоторое действие, а функцию – для того, чтобы вычислить некоторое значение. Подпрограммы имеют декларативную часть, исполняемую часть и необязательную часть обработки исключений. Декларативная часть содержит объявления типов, курсоров, констант, переменных, исключений и вложенных подпрограмм. Все эти объекты локальны и перестают существовать после выхода из подпрограммы. Исполняемая часть содержит предложения, которые присваивают значения, управляют выполнением и манипулируют данными Oracle. Часть обработки исключений содержит обработчики, которые имеют дело с исключениями, возбуждаемыми при исполнении. Подпрограммы можно определять в любом инструменте Oracle, который поддерживает PL/SQL. Их можно объявлять в блоках PL/SQL, процедурах, функциях и пакетах. Однако подпрограммы должны объявляться в конце декларативной секции, после всех других программных объектов. Преимущества подпрограмм Расширяемость – позволяют вам приспосабливать средства PL/SQL для ваших потребностей. Модульность – позволяют вам разбивать ваши программы на управляемые, хорошо определенные логические модули. Это поддерживает методы проектирования сверху вниз и пошагового уточнения, характерные для структурного подхода к решению проблем. Способствуют используемости и сопровождаемости – однажды проверенную подпрограмму можно с уверенностью использовать в любом количестве приложений. Способствует абстрагированию – чтобы использовать подпрограммы, вам нужно знать, что они делают, а не как они это делают. Процедуры Процедура – это подпрограмма, которая выполняет специфическое действие. Хранимую процедуру можно создать командой CREATE PROCEDURE. Синтаксис: CREATE [OR REPLACE] PROCEDURE имя [(параметр [, параметр, ...]) ] IS [локальные объявления] BEGIN исполняемые предложения [EXCEPTION обработчики исключений] END [имя]; / где каждый «параметр» имеет следующий синтаксис: 83 имя_перем [IN | OUT | IN OUT] тип_данных [{:= | DEFAULT} знач] В отличие от спецификатора типа данных в объявлении переменной, спецификатор типа данных для параметра не может иметь ограничений. Например, следующее объявление name незаконно: PROCEDURE ... (name CHAR (20)) IS – незаконно; должно быть CHAR. Процедура имеет две части: спецификацию и тело. Спецификация процедуры начинается с ключевого слова PROCEDURE и заканчивается именем процедуры или списком параметров. Объявления параметров необязательны. Если процедура не принимает параметров, скобки также не кодируются. Тело процедуры начинается с ключевого слова IS и заканчивается ключевым словом END. Тело процедуры состоит из трех частей: декларативной части, исполняемой части и необязательной части обработки исключений. Декларативная часть содержит локальные объявления, которые помещаются между ключевыми словами IS и BEGIN. Ключевое слово DECLARE, которое начинает декларативную часть в анонимном блоке PL/SQL, здесь не используется. Исполняемая часть содержит предложения, которые помещаются между ключевыми словами BEGIN и EXCEPTION (или END). В исполняемой части процедуры должно быть хотя бы одно предложение. Часть обработки исключений содержит обработчики исключений, которые помещаются между ключевыми словами EXCEPTION и END. Пример Процедура замены адреса владельца с заданным номером. CREATE OR REPLACE PROCEDURE CHANGE_ADR (NUM IN Owner.NOn%type, ADDRESS Owner.AdO%type) IS BEGIN UPDATE Owner SET AdO=ADDRESS WHERE NOn=NUM; END; / Функция Функция – это подпрограмма, которая вычисляет значение. Функции структурируются так же, как и процедуры, с той разницей, что функции содержат фразу RETURN. Cинтаксис: FUNCTION имя [ (аргумент [, аргумент, ...]) ] RETURN тип_данн IS [локальные объявления] BEGIN исполняемые предложения [EXCEPTION обработчики исключений] 84 END [имя]; / где каждый «аргумент» имеет следующий синтаксис: имя_перем [IN | OUT | IN OUT] тип_данных [{:= | DEFAULT} знач] Примечание. Вызовы пользовательских функций могут появляться в процедурных предложениях, но НЕ в предложениях SQL. Предложение RETURN немедленно завершает выполнение подпрограммы и возвращает управление вызывающей программе. Выполнение продолжается с предложения, следующего за вызовом подпрограммы. Подпрограмма может содержать несколько предложений RETURN, в процедурах предложение RETURN не может содержать выражение. Фактические и формальные параметры Подпрограммы принимают и передают информацию через параметры. Переменные или выражения, которые специфицированы в списке параметров в вызове подпрограммы, называются фактическими параметрами. Переменные, объявленные в спецификации подпрограммы и используемые в теле подпрограммы, называются формальными параметрами. Фактический параметр и соответствующий ему формальный параметр должны иметь совместимые типы данных. Позиционная и именная нотация При вызове подпрограммы можно записывать фактические параметры, используя позиционную или именную нотацию. Иными словами, можно указывать соответствие между фактическими и формальными параметрами через позиции этих параметров или через их имена. Пример DECLARE nmb Owner.NOn%type; addr Owner.AdO%type; PROCEDURE CHANGE_ADR (NUM IN Owner.NOn%type, ADDRESS Owner.AdO%type) IS ... Процедуру credit теперь можно вызвать четырьмя логически эквивалентными способами: BEGIN CHANGE_ADR (nmb, addr); – позиционная нотация CHANGE_ADR (NUM => nmb, ADDRESS => addr); – именная нотация CHANGE_ADR (ADDRESS => addr, NUM => nmb); – именная нотация CHANGE_ADR (nmb, ADDRESS => addr); – смешанная нотация END; / 85 Первый вызов процедуры использует позиционную нотацию. Компилятор PL/SQL ассоциирует первый фактический параметр, nmb, с первым формальным параметром, NUM, а второй фактический параметр, addr, – со вторым формальным параметром, ADDRESS. Второй вызов процедуры использует именную нотацию. Стрелка ассоциирует формальный параметр слева от стрелки с фактическим параметром справа от стрелки. Третий вызов процедуры также использует именную нотацию и показывает, что вы можете задавать пары параметров в любом порядке. Поэтому вы не обязаны знать порядок, в котором перечислены формальные параметры. Четвертый вызов процедуры показывает, что вы можете смешивать позиционную и именную нотации. В данном случае первый параметр задан в позиционной, а второй – в именной нотации. Позиционная нотация в этом варианте должна предшествовать именной. Обратное не допускается. Например, следующий вызов процедуры незаконен: CHANGE_ADR (ADDRESS => addr, nmb); – незаконно. Моды параметров Моды параметров используются, чтобы определить поведение формальных параметров подпрограммы. Все три возможные моды: IN (по умолчанию), OUT и IN OUT – могут использоваться в любой процедуре. Что касается функций, то избегайте использования моды OUT или IN OUT в функциях. Параметр с модой IN передает значение вызываемой подпрограмме. Внутри подпрограммы такой параметр выступает как константа, поэтому ему нельзя присвоить значение. Параметр с модой OUT позволяет возвращать значение вызывающей программе. Внутри подпрограммы такой параметр выступает как неинициализированная переменная, поэтому его значение нельзя присваивать другим переменным или переприсвоить самому себе. Фактический параметр, соответствующий формальному параметру с модой OUT, должен быть переменной; он не может быть константой или выражением. Параметр IN OUT позволяет вам передавать в подпрограмму начальные значения и возвращать обновленные значения вызывающей программе. Внутри подпрограммы такой параметр выступает как инициализированная переменная. Поэтому ему можно присвоить значение, а его значение можно присваивать другим переменным. Иными словами, параметр IN OUT можно рассматривать как обычную переменную. Перекрытие имен PL/SQL позволяет перекрывать имена подпрограмм. Иными словами, вы можете использовать одно и то же имя для нескольких различных подпрограмм, если только их формальные параметры различаются по количеству, порядку или семействам типов данных. Перекрывающиеся имена подпрограмм могут появляться только в блоке, подпрограмме или пакете. Нельзя перекрывать имена независимых подпрограмм. Нельзя перекрывать две подпрограммы, если их формальные параметры различаются лишь именами или модами параметров. Более того, нельзя перекрывать две подпрограммы, если их формальные параметры различаются лишь типами данных, причем эти типы данных относятся к одному и тому же семейству. Наконец, нельзя перекрывать две функции, если они различаются лишь типами данных результирующего значения, даже если эти типы данных относятся к разным семействам. 86 Вызов хранимых подпрограмм Хранимые подпрограммы можно вызывать из триггера базы данных, другой хранимой подпрограммы, приложения прекомпилятора Oracle, приложения OCI или из инструмента Oracle, такого как SQL*Plus. Хранимая подпрограмма может вызывать другую хранимую подпрограмму. Например, в теле пакетированной подпрограммы может появиться вызов независимой процедуры. Приложение прекомпилятора или приложение OCI может вызывать хранимые подпрограммы из анонимных блоков PL/SQL. Пример EXEC SQL EXECUTE BEGIN CHANGE_ADR (3, ‘Самарская, 13-22’); END; END-EXEC; Вы можете вызывать хранимые подпрограммы интерактивно из инструментов Oracle, таких как SQL*Plus, SQL*Forms или SQL*DBA. Пример SQL> EXECUTE CHANGE_ADR (3, ‘Самарская, 13-22’); Этот вызов эквивалентен следующему анонимному блоку PL/SQL: SQL> BEGIN CHANGE_ADR (3, ‘Самарская, 13-22’); END; Примечание. Тело независимой или пакетированной хранимой подпрограммы может содержать любое предложение SQL или PL/SQL. Однако подпрограммы, участвующие в распределенной транзакции, триггерах базы данных и приложениях SQL*Forms, не могут вызывать хранимых подпрограмм, содержащих предложения COMMIT, ROLLBACK или SAVEPOINT. Примечание. Обращения к хранимым функциям могут появляться в процедурных предложениях, но не в предложениях SQL. Пакет Пакет – это объект базы данных, который группирует логически связанные типы, программные объекты и подпрограммы PL/SQL. Пакеты предлагают несколько преимуществ: модульность, облегчение проектирования приложений, скрытие информации, расширенная функциональность и лучшая производительность. Пакеты обычно состоят из двух частей (спецификации и тела), хотя иногда в теле нет необходимости. Спецификация пакета – это интерфейс с вашими приложениями; она объявляет типы, переменные, константы, исключения, курсоры и подпрограммы, доступные для использования в пакете. Тело пакета полностью определяет курсоры и подпрограммы, тем самым реализуя спецификацию пакета. 87 В отличие от подпрограмм, пакеты нельзя вызывать, передавать им параметры или вкладывать их друг в друга. В остальном формат пакета аналогичен формату подпрограммы. Синтаксис: CREATE [OR REPLACE] PACKAGE имя_пакета IS спецификация [объявления общих типов и объектов; спецификации подпрограмм] END [имя_пакета]; CREATE [OR REPLACE] PACKAGE BODY имя_пакета IS [объявления личных типов и объектов; тела подпрограмм;] [BEGIN предложения инициализации] END [имя_пакета]; Спецификация содержит общие объявления, которые видимы вашему приложению. Тело содержит детали реализации и закрытые объявления, которые скрыты от вашего приложения. Тело пакета реализует спецификацию пакета. Оно содержит определения всех курсоров и подпрограмм, объявленных в спецификации пакета. Тело пакета может также содержать закрытые объявления, которые определяют типы и объекты, необходимые для внутренней работы пакета. Сфера таких объявлений локальна в теле пакета. Поэтому объявленные здесь типы и объекты недоступны нигде, кроме тела пакета. Для обращения к типам, объектам и подпрограммам, объявленным в спецификации пакета, используются квалифицированные ссылки: имя_пакета.имя_типа имя_пакета.имя_объекта имя_пакета.имя_подпрограммы Обращаться к содержимому пакета можно из триггеров базы данных, хранимых подпрограмм, встроенных блоков PL/SQL, а также анонимных блоков PL/SQL, посылаемых в Oracle интерактивно через SQL*Plus или SQL*DBA. Вопросы 1. Чем отличается функция от процедуры? 2. Синтаксис функции. Синтаксис процедуры. 3. Какие существуют ограничения на вызовы пользовательских функций и процедур? 4. Фактические и формальные параметры. 5. Что такое позиционная и именная нотация? Смешанная нотация? Какие существуют ограничения при смешанной нотации? 6. Какие бывают моды параметров? В чем их особенности? 7. Значения параметров по умолчанию. 8. Ограничения на пространство имен подпрограмм. 9. В чем особенности и преимущества хранимых подпрограмм? Как синтаксически задать хранимую подпрограмму? 10. Что такое пакет? Что может находиться в спецификации пакета? В теле пакета? 11. Как вызвать пакетированную функцию? 88 Задания 1. Напишите процедуру, которая выводит на печать количество заключенных договоров для каждого арендатора недвижимости. 2. Напишите функцию, которая возвращает наименование самого дорогого объекта аренды. Предусмотрите обработку исключительной ситуации, когда таких объектов более одного, то функция должна вернуть наименование первого из них и выдать сообщение об ошибке. 3. Напишите процедуру, которая удаляет из базы арендаторов, не заключивших ни одного договора. Перед тем как произвести удаление записей процедура должна напечатать фамилии этих арендаторов и сообщение об удалении. 4. Напишите процедуру, которая определяет список недвижимости и ее владельцев (с адресами), договоры на которые заключены ранее 01.01.2007 года. Вывод данных должен быть сгруппирован по типам недвижимости и оформлен в читаемом виде, например: ________________________________________________ квартира 1к ________________________________________________ Марусина А.К., Самарская, 12-25 Иващенко П.Г., Кирова, 17-223 ________________________________________________ квартира 2к ________________________________________________ Сидоренко В.К., Полевая, 158-92 Соломкина П.А., Галактионовская, 129-32 Сороконожкин В.Л., Чапаевская, 33-31 ________________________________________________ Подсказка: используйте 2 курсора, один из которых будет полностью функционировать внутри цикла второго курсора. 5. Напишите функцию, которая будет рассчитывать процент дохода фирмы. Входным параметром должен быть номер договора. Выходным - доход фирмы от заданного договора. Процент считаем по принципу: сумма договора*(0.05+коэффициент). Размер коэффициента устанавливается в зависимости от вида недвижимости, но не может быть более 0.05. Пример: дом - коэффициент 0.03, 2х комнатная квартира - коэффициент 0.01 и т.п. Все коэффициенты прописываются как локальные переменные или как локальные константы в функции. 6. Оформите пакет, в котором будут находиться вышеперечисленные подпрограммы. |