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

Для создания этих хранимых программ в схемах других пользователей требуется наличие системной привилегии CREATE ANY PROCEDURE, предоставленной явно или через роль. Для создания триггеров требуются отдельные привилегии CREATE TRIGGER и CREATE ANY TRIGGER.

DDL-команды CREATE для создания хранимых программ PL/SQL имеют необязательные ключевые слова CREATE [OR REPLACE], указывающую на замену существующей программы новой программой с тем же именем. Если слова OR REPLACE не указаны в команде CREATE, а хранимая программа с таким именем в базе данных уже есть, то создание программы завершится с ошибкой.

SQL> CREATE PROCEDURE proc1 AS

2 BEGIN

3 NULL;

4 END;

5 /

Procedure created.

SQL> CREATE PROCEDURE proc1 AS

2 BEGIN

3 NULL;

4 END;

5 /

CREATE PROCEDURE proc1 AS

*

ERROR at line 1:

ORA-00955: name is already used by an existing object

SQL> CREATE OR REPLACE PROCEDURE proc1 AS

2 BEGIN

3 NULL;

4 END;

5 /

Procedure created.

Можно было бы сначала удалить существующую программу, а потом создать новую с тем же именем, но рекомендуется так не делать по следующей причине.

Для хранимых программ PL/SQL пользователям и ролям базы данных предоставляются объектные привилегии на их выполнение. Если удалить хранимую программу, то эти привилегии пропадут (правильнее сказать – автоматически отзовутся в связи с удалением объекта доступа). После того, как хранимая программа с таким же именем заново будет создана, привилегии эти сами по себе не восстановятся, владельцу программы придется предоставлять их другим пользователям снова. При пересоздании хранимой программы DDL-командой CREATE OR REPLACE с привилегиями на ее выполнение ничего не происходит.

Находящиеся в базе данных хранимые программы можно перекомпилировать с помощью 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;