В Oracle традиционно основным языком хранимых процедур является язык PL/SQL, но поддерживаются и процедуры на других языках, прежде всего - на языках C++ и Java. В последнем случае хранимая процедура или функция называется внешней. В рамках нашего курса рассмотрим основной вариант – хранимая процедура на PL/SQL.
Необязательная конструкция OR REPLACE позволяет заменять процедуру с таким же именем. Это очень удобно в процессе отладки.
Аналогично создается хранимая функция:
CREATE [OR REPLACE] FUNCTION имя[(список_параметров)]
RETURN тип_результата, возвращаемого функцией
AS
блок PL/SQL, обязательно содержащий оператор
RETURN выражение
В списке параметров должен быть описан режим использования каждого параметра: IN (только входной – используется по умолчанию), OUT (только выходной), IN OUT (и входной, и выходной). Режим использования указывается после имени параметра. Типы параметров, как и типы переменных, можно указывать явно или с помощью ссылки на соответствующий столбец или таблицу.
При описании локальных переменных подпрограммы разрешено опускать ключевое слово DECLARE.
Удалить хранимую процедуру или функцию можно при помощи команды DROP.
Примеры хранимых процедур и функций
В качестве примеров приведем две хранимые подпрограммы для нашей демонстрационной базы студентов и их оценок.
Первая из процедур демонстрирует применение неявного курсора и предназначена для изменения телефона студента. Ее входными параметрами являются фамилия и новый телефон студента. Конечно, первым входным параметром должна быть не фамилия, а личный код студента, но таким образом мы хотим продемонстрировать исключительную ситуацию TOO_MANY_ROWS. В случае наличия однофамильцев, а также в случае отсутствия студента с такой фамилией процедура должна сообщить о возникшей исключительной ситуации.
Также целесообразно отдельно обработать случай, когда старый телефон совпадает с новым, поэтому операции обновления не требуется.
Для фиксации всех перечисленных выше случаев в процедуру добавлен выходной параметр result, который после завершения процедуры возвращает код ошибки (0 – благополучное завершение процедуры) и может быть обработан клиентским приложением.
select phone into oldphone from students where name_st=fiostud;
if oldphone!=newphone then
update students set phone=newphone where name_st=fiostud;
result:=0;
else
result:=1; -- старый и новый номера совпали
end if;
exception
when NO_DATA_FOUND then -- нет такого студента
result:=2;
when TOO_MANY_ROWS then
result:=3; -- есть однофамильцы
when OtherS then
result:=4; -- непредвиденная исключительная ситуация
end;
Выполнив команду создания данной процедурв в SQL*Plus, мы получим сообщение «Процедура создана». В случае, если в процедуре обнаружены синтаксические ошибки, выдается другое сообщение «Процедура создана с ошибками компиляции». Получить информацию об обнаруженных ошибках можно с помощью запроса к представлению словаря Oracle user_errors:
SELECT line, text FROM user_errors
Хранимая процедура запускается на выполнение по команде из клиентского приложения.
Чтобы запустить ее на выполнение из SQL*Plus в целях отладки, необходимо поместить ее в блок PL/SQL, перед которым объявить переменную для выходного параметра:
var e number;
begin
changephone('Иванов', '555555', :e);
end;
Проверить значение переменной е можно при помощи команды:
PRINT e
В качестве второго примера приведем функцию, которая принимает в качестве входного параметра фамилию студента и возвращает строку, содержащую телефоны всех студентов с такой фамилией (возможно, пустую строку, если студентов с такой фамилией нет). Здесь демонстрируется применение явного курсора.
CREATE Function getphone (fiostud students.name_st%type)
RETURN varchar
as
cursor c is
select phone from students
where name_st= fiostud; -- телефоны всех студентов с заданной фамилией
res varchar(50); -- строка результата
ph students.phone%type; -- переменная для команды fetch
BEGIN
open c;
res:='';
loop – цикл для извлечения данных из курсора
fetch c into ph;
EXIT WHEN NOT c%found;
res:=res||ph||' ';
end loop;
RETURN res ;
END;
Если использовать цикл по курсору, тело функции получится короче:
BEGIN
res:='';
for crec in c loop
res:=res||crec.phone||' ';
end loop;
RETURN res ;
END;
После создания функции проверить ее работоспособность можно совсем просто:
