Примеры хранимых процедур и функций

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

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

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

Для фиксации всех перечисленных выше случаев в процедуру добавлен выходной параметр result, который после завершения процедуры возвращает код ошибки (0 – благополучное завершение процедуры) и может быть обработан клиентским приложением.

create procedure changephone(fiostud students.name_st%type,

newphone students.phone%type,

result out number)

as

oldphone students.phone%type; -- старый телефон

begin

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;

После создания функции проверить ее работоспособность можно совсем просто:

SELECT getphone('Иванов') FROM dual