end;
Проверьте результат.
Пример создания процедуры c входными параметрами и выходным параметром:
CREATE OR REPLACE PROCEDURE ADMIN_BOOKS.Count_Books_Itogo (Count_pages IN Int, Title IN Char, Itogo OUT Int)
IS begin
Select count(Code_book) INTO Itogo from Books WHERE Pages>=Count_pages AND Title_book LIKE '%'||trim(Title)||'%';
end Count_Books_Itogo;
Задание 4. Создайте данную процедуру в разделе Schema/ Procedure в
схеме ADMIN_BOOKS через утилиту Enterprise MANAGER Console. За-
пустите ее в утилите SQL *Plus Worksheet с помощью программы: SET SERVEROUTPUT ON
DECLARE
I INTEGER;
begin
ADMIN_BOOKS.Count_Books_Itogo (56, 'Руслан и Людмила', I); DBMS_OUTPUT.PUT_LINE(I);
end;
Проверьте результат.
Пример создания процедуры без параметров для увеличения значения ключевого поля в таблице Purchases в 2 раза:
CREATE OR REPLACE PROCEDURE ADMIN_BOOKS.update_proc IS
BEGIN
UPDATE Purchases SET Code_purchase = Code_purchase*2; END update_proc;
Задание 5. Создайте данную процедуру в разделе Schema/ Procedure в
схеме ADMIN_BOOKS через утилиту Enterprise MANAGER Console. За-
пустите ее в утилите SQL *Plus Worksheet с помощью программы: begin
ADMIN_BOOKS.update_proc;
end;
Процедура не возвращает никаких данных.
65
Пример создания процедуры с входным параметром и значением по умолчанию для увеличения значения ключевого поля в таблице Purchases в заданное количество раз (по умолчанию в 2 раза):
CREATE OR REPLACE PROCEDURE ADMIN_BOOKS.update_proc2 (p IN INT:= 2)
IS BEGIN
UPDATE Purchases SET Code_purchase = Code_purchase*p; END update_proc2;
Задание 6. Создайте данную процедуру в разделе Schema/ Procedure в
схеме ADMIN_BOOKS через утилиту Enterprise MANAGER Console. За-
пустите ее в утилите SQL *Plus Worksheet с помощью программы: begin
ADMIN_BOOKS.update_proc2; end;
или begin
ADMIN_BOOKS.update_proc2(4);
end;
Процедура не возвращает никаких данных.
Пример создания функции c входными параметрами и RETURN:
CREATE OR REPLACE function ADMIN_BOOKS.checkname (param IN int, ch IN char)
RETURN integer
AS
type_end integer; begin
SELECT count(Name_author) INTO type_end FROM authors WHERE Code_author = param AND Name_author=ch;
RETURN type_end;
end checkname;
Задание 7. Создайте данную функцию в разделе Schema/ Function в
схеме ADMIN_BOOKS через утилиту Enterprise MANAGER Console. За-
пустите ее в утилите SQL *Plus Worksheet с помощью программы: SET SERVEROUTPUT ON
DECLARE
I INTEGER; begin
I:=ADMIN_BOOKS.checkname(12,'Пушкин А.С.');
66
DBMS_OUTPUT.PUT_LINE(I); end;
Пример создания функции с входными и выходным параметрами. Создать функцию для определения количества заказов, совершенных за указанный период:
CREATE OR REPLACE function ADMIN_BOOKS.count_purchases (d1 DATE, d2 DATE)
RETURN integer AS
count_ integer; begin
SELECT count(Code_purchase) INTO count_ from Purchases WHERE Date_order BETWEEN d1 AND d2;
RETURN count_; end count_purchases;
Задание 8. Создайте данную функцию в разделе Schema/ Function в
схеме ADMIN_BOOKS через утилиту Enterprise MANAGER Console. За-
пустите ее в утилите SQL *Plus Worksheet с помощью программы: SET SERVEROUTPUT ON
DECLARE
I INTEGER; begin
I:=ADMIN_BOOKS.count_purchases(TO_DATE('12.06.2007', 'dd-mm- yyyy'),TO_DATE('30.06.2007', 'dd-mm-yyyy') ); DBMS_OUTPUT.PUT_LINE(I);
end;
Проверьте результат.
Создание хранимых процедур с выходным параметром – набором строк
Для вывода в качестве результата выполнения процедуры набора строк используют курсор типа SYS_RefCursor.
Например:
create or replace procedure ADMIN_BOOKS.List_Authors (cursor1 out SYS_REFCURSOR) is
begin
open cursor1 for
select Name_Author from ADMIN_BOOKS.Authors; end List_Authors;
67
Пример запуска процедуры и просмотра набора строк, полученных из нее:
SET SERVEROUTPUT ON Declare
type cur_select1 is ref cursor; cur_select2 cur_select1;
name1 ADMIN_BOOKS.Authors.Name_Author %type; begin
ADMIN_BOOKS.List_Authors(cur_select2); loop
FETCH cur_select2 INTO name1;
exit when cur_select2%NOTFOUND OR cur_select2%NOTFOUND IS NULL;
DBMS_OUTPUT.PUT_LINE(cur_select2%ROWCOUNT || '. ' || name1); end loop;
close cur_select2; end;
Варианты заданий к лабораторной работе №5
Общие положения
В лабораторной работе в примерах тела процедур и функций написаны так, что их можно полностью копировать, вставлять в утилиту SQL *Plus Worksheet и запускать (F5) для их создания на сервере. Если создавать процедуру или функцию в Enterprise MANAGER Console через мастер соз- дания, то первая строка с конструкцией CREATE OR REPLACE PROCEDURE имя_процедуры или CREATE OR REPLACE FUNCTION
имя_функции не копируется, а само имя_процедуры или имя_функции за- носится в поле Name.
Создать примеры хранимых процедур и функций в вашем табличном пространстве. Проверьте их работу.
По вариантам на базе таблиц, созданных в лабораторной работе №1, в утилите SQL *Plus Worksheet создайте процедуры и функции, причем каж- дую программу сохранять на диске в отдельном файле с названием Фами-
лияСтудента_ЛАб_5_№_варианта_№_задания.
Список заданий
Вариант 1
1.Вывести список сотрудников, у которых есть хотя бы один ребенок.
2.Вывести список детей, которым выдали подарки в указанный пери- од.
3.Вывести список родителей, у которых есть несовершеннолетние де- ти.
68
4.Вывести информацию о подарках со стоимостью больше указанного числа, отсортированных по дате.
Вариант 2
1.Вывести список приборов с указанным типом.
2.Вывести количество отремонтированных приборов и общую стои- мость ремонтов у указанного мастера.
3.Вывести список владельцев приборов и количество их обращений, отсортированный по количеству обращений по убыванию.
4.Вывести информацию о мастерах с разрядом больше указанного числа или с датой приема на работу меньше указанной даты.
Вариант 3
1.Вывести список цветков с указанным типом листа.
2.Вывести список кодов продаж, по которым продано цветов на сумму больше указанного числа.
3.Вывести дату продажи, сумму, продавца и цветок по указанному ко- ду продажи.
4.Вывести список цветов и сорт для цветов с высотой больше указан- ного числа или цветущий.
Вариант 4
1.Вывести список лекарств с указанным показанием к применению.
2.Вывести список дат поставок, по которым продано больше указанно- го числа одноименного лекарства.
3.Вывести дату поставки, сумму, ФИО руководителя от поставщика и название лекарства по коду поступления больше указанного числа.
4.Вывести список лекарств и единицы измерения для лекарств с коли- чеством в упаковке больше указанного числа или кодом лекарства меньше определенного значения.
Вариант 5
1.Вывести список сотрудников с указанной должностью.
2.Вывести список списанного оборудования по указанной причине.
3.Вывести дату поступления, название оборудования, ФИО ответст- венного и дату списания для оборудования, списанного в указанный период.
4.Вывести список оборудования с указанным типом или с датой по- ступления больше определенного значения.
Вариант 6
1.Вывести список блюд с весом больше указанного числа.
2.Вывести список продуктов, в названии которых встречается указан- ный фрагмент слова.
3.Вывести объем продукта, название блюда, название продукта с ко- дом блюда от указанного начального значения по определенному конечному значению.
69