Иван Задворьев - Язык PL/SQL

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. А вот все вызовы со связыванием по позиции при появлении у вызываемой программы новых формальных параметров потребуется изменить так, чтобы фактических параметров снова стало столько же, сколько формальных.