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

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, выполнение процедуры прекращается и управление передается вызвавшему процедуру блоку.

Функции

Команда создания функции имеет следующий синтаксис: