Находящиеся в базе данных хранимые программы можно перекомпилировать с помощью DDL-команды ALTER:
SQL> ALTER PROCEDURE proc1 COMPILE;
Procedure altered.
Как и другие объекты базы данных, хранимые программы могут быть удалены. Пользователю не требуются дополнительные привилегии для удаления программ в своей схеме, для удаления программ в схеме другого пользователя необходимо наличие привилегии DROP ANY PROCEDURE.
SQL> DROP PROCEDURE proc1;
Procedure dropped.
Процедуры и функции
Функция отличается от процедуры тем, что функция возвращает значение указанного при создании функции типа данных, а процедура ничего не возвращает. Вызов функции всегда включается в некоторое выражение, то есть возвращаемый функцией результат обязательно нужно куда-то детьприсвоить его значение некоторой переменной или передать в качестве параметра другой функции или процедуре. Функции на PL/SQL можно использовать в предложениях SQL наряду со встроенными функциями языка SQL.
Обычно процедуры и функции создаются для решения определенных небольших задач. При продуманной структуре исходного кода каждая процедура или функция со всеми разделами и вложенными блоками должна умещаться на одном экране (максимум 30-40 строк). Если код процедуры или функции разрастается, то имеет смысл продумать его декомпозицию, использовать пакеты или перегружаемые программы.
Процедуры
Команда создания процедуры имеет следующий синтаксис:
CREATE [OR REPLACE]
раздел заголовка блока PL/SQL
PROCEDURE
[имя схемы.]имя процедуры
[(имя параметра [{IN | OUT | IN OUT}] тип данных
[,имя параметра [{IN | OUT | IN OUT}] тип данных ])]
{IS | AS}
остальные разделы блока PL/SQL (объявлений,исполняемый,обработки исключений)
В процедурах не используется ключевое слово DECLAREобъявление пользовательских типов данных, переменных, курсоров начинается сразу после ключевого слова AS. Областью видимости объявленных здесь элементов будет являться вся процедура. В разделе объявлений процедуры можно реализовать и другую процедуру или функцию, которые будут видны только внутри родительской процедуры:
CREATE OR REPLACE PROCEDURE proc2 AS
FUNCTION nested_proc RETURN INTEGER IS
BEGIN
NULL;
END;
BEGIN
nested_proc();
END;
Пусть таблица tab1 создана следующей DDL-командой:
CREATE TABLE tab1 (at1 NUMBER, at2 DATE);
Создадим процедуру insRec, которая заносит в таблицу 1/2 переданного значения числового параметра и текущую дату.
SQL> CREATE OR REPLACE PROCEDURE insRec(p_arg1 IN NUMBER) AS
2 coeff CONSTANT NUMBER := 0.5;
3 BEGIN
4 INSERT INTO tab1 VALUES(coeff*p_arg1,SYSDATE);
5 END;
/
Procedure created.
После создания процедуру можно вызвать из любого блока PL/SQL, указав ее имя и параметры.
SQL> DECLARE
2 l_arg1 NUMBER := 240;
3 BEGIN
4 insRec(l_arg1);
5 END;
6 /
PL/SQL procedure successfully completed.
SQL> SELECT * FROM Tab1;
AT1 AT2
120 04.05.2015
В SQL*Plus для вызова процедур есть команда EXECUTE.
SQL> EXECUTE insRec(100);
PL/SQL procedure successfully completed.
SQL> SELECT * FROM Tab1;
AT1 AT2
120 04.05.2015
50 04.05.2015
В процедурах можно использовать команду RETURN. Как только в потоке команд в процедуре встретится команда RETURN, выполнение процедуры прекращается и управление передается вызвавшему процедуру блоку.
Функции
Команда создания функции имеет следующий синтаксис:
CREATE [OR REPLACE] FUNCTION
раздел заголовка блока PL/SQL
[имя схемы.]имя функции
[(имя параметра [{IN | OUT | INOUT}] тип данных
[,имя параметра [{IN | OUT | INOUT}] тип данных ])] RETURN тип данных AS
остальные разделы блока PL/SQL (объявлений,исполняемый,обработки исключений)
Пусть таблица tab1 создана и заполнена следующим образом:
CREATE TABLE tab1 (at1 NUMBER, at2 DATE);
INSERT INTO tab1 VALUES(5, SYSDATE);
INSERT INTO tab1 VALUES(6, SYSDATE);
INSERT INTO tab1 VALUES(7, SYSDATE+1);
Создадим функцию, которая вычисляет сумму значений столбцов таблицы, таких, что дата попадает в заданный интервал.
SQL> CREATE OR REPLACE FUNCTION sumRecInt(arg1 IN DATE,
2 arg2 IN DATE) RETURN NUMBER AS
3 sum_var NUMBER := 0;
4 BEGIN
5 SELECT SUM(at1) INTO sum_var FROM tab1
6 WHERE at2 BETWEEN arg1 AND arg2;
7 RETURN sum_var;
8 END;
9 /
Function created.
SQL> BEGIN
2 DBMS_OUTPUT.PUT_LINE(sumRecInt(SYSDATE-1/2, SYSDATE+1/2));
3 END;
4 /
11
PL/SQL procedure successfully completed.
Ход вычислений функции обязательно должен завершаться вызовом в ее теле команды RETURN возвращаемое значение. Если этого не произойдет, то возникнет ошибка этапа выполнения:
SQL> CREATE FUNCTION func2 RETURN INTEGER AS
2 BEGIN
3 NULL;
4 END;
5 /
Function created.
SQL> BEGIN
2 DBMS_OUTPUT.PUT_LINE(func2);
3 END;
4 /
BEGIN
*
ERROR at line 1:
ORA-06503: PL/SQL: Function returned without value
ORA-06512: at "U1.FUNC2", line 3
ORA-06512: at line 2
Иногда на лекциях студентами задается вопрос, поддерживаются ли в PL/SQL рекурсивные функции, то есть функции, вызывающие сами себя. Поддерживаются, приведем пример наиболее понятной на все времена рекурсивной функции:
SQL> CREATE OR REPLACE FUNCTION factorial(n IN INTEGER) RETURN INTEGER IS
2 BEGIN
3 IF n=0 THEN
4 RETURN 1;
5 ELSE
6 RETURN n*factorial(n-1);
7 END IF;
8 END;
9 /
Function created.
SQL> DECLARE
2 l_number INTEGER := 3;
3 BEGIN
4 DBMS_OUTPUT.PUT_LINE(factorial(l_number));
5 DBMS_OUTPUT.PUT_LINE(factorial(COS(0)));
6 END;
7 /
6
1
PL/SQL procedure successfully completed.
Параметры процедур и функций
Процедуры и функции могут иметь параметры, для которых указываются имена, типы данных и режимы передачи значений.
Важно понимать различия между формальными и фактическими параметрами. Формальные параметры указываются в списке параметров заголовка программы при ее объявлении, тогда как фактические параметрыэто значения и выражения, которые помещаются в список параметров при ее вызове. Иными словами, значения фактических параметров передаются при вызове внутрь процедур и функций, где становятся значениями формальных параметров. Фактическим параметром при первом вызове функции factorial являлась переменная l_number, объявленная в вызывающем блоке. Эта переменная имела значение 3, которое и было использовано внутри функции (3!=6). При втором вызове функции factorial фактическим параметром являлось выражение COS(0). Как известно, 1!=1;
Соответствие формальных и фактических параметров
Соответствие между формальными и фактическими параметрами можно устанавливать двумя способами:
связывание по позиции (неявное связывание);
связывание по имени.
При неявном связывании фактические параметры указываются в круглых скобках после имени программы в той же последовательности, в которой были перечислены формальные параметры при создании программы.
Связывание формальных и фактических параметров по имени осуществляется с помощью конструкций вида
имя формального параметра => имя фактического параметра
С точки зрения выполнения программы нет разницы между используемыми ней способами установления соответствия между параметрами, которые обычно определяются принятым стилем программирования, корпоративными стандартами кодирования и рядом других факторов. В программах с небольшим числом формальных параметров оправдано использование соответствия параметров по позиции. В программах с большим числом формальных параметров связывание параметров по имени более информативно, более ясно показывает связь между формальными и фактическими параметрами.
SQL> CREATE PROCEDURE print(phrase IN VARCHAR2,punctuation_mark IN CHAR) IS
2 BEGIN
3 DBMS_OUTPUT.PUT_LINE(phrase||' '||punctuation_mark);
4 END;
5 /
Procedure created.
SQL> BEGIN
2 print('Hello,world','!');
3 END;
4 /
Hello,world !
PL/SQL procedure successfully completed.
SQL> BEGIN
2 print(punctuation_mark=>'!',phrase=>'Hello,world');
3 END;
4 /
Hello,world !
PL/SQL procedure successfully completed.
Если у программы в будущем появятся новые формальные параметры, то код, в котором она вызывается со связыванием параметров по имени, останется работоспособным. Если для новых параметров указаны значения по умолчанию, то они будут использованы как фактические параметры, если значения по умолчанию отсутствуют, то новые параметры получат значения NULL. А вот все вызовы со связыванием по позиции при появлении у вызываемой программы новых формальных параметров потребуется изменить так, чтобы фактических параметров снова стало столько же, сколько формальных.
Отметим, что при хорошем стиле программирования не принято объявлять процедуры и функции с большим (больше 10) числом параметров скалярных типов данных. В этом случае надо использовать небольшое число параметров составных типов данных (записи PL/SQL или коллекции).