Хранимые процедуры PL/SQL: создание, выполнение, вывод, удаление и сокрытие кода

Хранимые процедуры PL/SQL: основы программированияВ этой заметке моего блога мы поговорим о создании, выполнении, удалении и других операциях с программами (процедурами) PL/SQL, выполняемыми с помощью SQL*Plus.



 

Создание хранимой процедуры PL/SQL

Для того чтобы написать собственную программу на PL/SQL, нужно воспользоваться одной из инструкций SQL CREATE. Например, если вы хотите создать хранимую функцию именем wordcount для подсчета количества слов в строке, выполните инструкцию CREATE FUNCTION:: 

CREATE FUNCTION wordcount (str IN VARCHAR2)
   RETURN PLS_INTEGER
AS
   ... здесь объявляются локальные переменные
BEGIN
   ... здесь реализуется алгоритм
END;
/

Как и в случае с простыми блоками BEGIN-END, приводившимися ранее, код этой инструкции в SQL*Plus должен завершаться символом косой черты, который размещается в отдельной строке.

Если администратор базы данных предоставил вам привилегию создания процедур CREATE PROCEDURE (которая также включает привилегию создания функций), эта инструкция заставит Oracle откомпилировать и сохранить в схеме заданную хранимую функцию. И если код будет откомпилирован успешно, вы увидите следующее сообщение: 

Function created.

Если в схеме Oracle уже имеется объект (таблица или пакет) с именем wordcount, выполнение инструкции CREATE FUNCTION завершится сообщением об ошибке:

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

По этой причине Oracle поддерживает инструкцию CREATE OR REPLACE FUNCTION — вероятно, вы будете использовать ее в 99 случаях из 100: 

CREATE OR REPLACE FUNCTION wordcount (str IN VARCHAR2)
   RETURN PLS_INTEGER
AS  -- то же, что в приведенном выше примере

Связка OR REPLACE позволяет избежать побочных эффектов, вызванных удалением и повторным созданием программ; она сохраняет все привилегии на объект, предоставленные другим пользователям или ролям. При этом она заменяет только объекты одного типа и не станет автоматически удалять таблицу с именем wordcount только потому, что вы решили создать функцию с таким же именем.

Программисты обычно сохраняют подобные команды (равно как и анонимные блоки, предназначенные для повторного использования) в файлах операционной системы. Например, для хранения рассматриваемой функции можно было бы создать файл wordcount.fun, а для его запуска применить команду SQL*Plus @

SQL> @wordcount.fun
Function created.

Как упоминалось ранее, SQL*Plus по умолчанию не выводит содержимое сценария на экран. Для того чтобы исходный код сценария, включая присвоенные Oracle номера строк, отображался на экране, воспользуемся командой SET ECHO ON. Особенно полезна эта команда в ходе диагностики. Давайте намеренно допустим в программе ошибку, закомментировав объявление переменной: 

SQL> SET ECHO ON
SQL> @wordcount.fun
SQL> CREATE OR REPLACE FUNCTION wordcount (str IN VARCHAR2)
2  RETURN PLS_INTEGER
3  AS
4     /* words PLS_INTEGER := 0; ***Намеренное внесение ошибки*** */
5     len PLS_INTEGER := NVL(LENGTH(str),0);
6     inside_a_word BOOLEAN;
7  BEGIN
8    FOR i IN 1..len + 1
9    LOOP
10       IF ASCII(SUBSTR(str, i, 1)) < 33 OR i > len
11       THEN
12          IF inside_a_word
13          THEN
14             words := words + 1;
15             inside_a_word := FALSE;
16          END IF;
17       ELSE
18          inside_a_word := TRUE;
19       END IF;
20    END LOOP;
21    RETURN words;
22 END;
23 /
Warning: Function created with compilation errors.
SQL>

Предупреждение сообщает нам о том, что функция была создана, но из-за ошибок компиляции ее выполнение невозможно. Нам удалось сохранить исходный код в базе данных; теперь нужно извлечь подробную информацию об ошибке из базы данных. Проще всего это сделать с помощью команды SQL*Plus SHOW ERRORS, которую можно сократить до SHO ERR

SQL> SHO ERR
Errors for FUNCTION WORDCOUNT:
LINE/COL ERROR
-------- ----------------------------------------------
14/13 PLS-00201: identifier 'WORDS' must be declared
14/13 PL/SQL: Statement ignored
21/4  PL/SQL: Statement ignored
21/11 PLS-00201: identifier 'WORDS' must be declared

Вывод других ошибок

Многие программисты Oracle знают только одну форму команды SQL*Plus:

SQL> SHOW ERRORS 

Они ошибочно полагают, что для получения дополнительной информации об ошибках, не встречавшихся при последней компиляции, необходимо обращаться с запросом к представлению USER_ERRORS. Однако если указать в команде SHOW ERRORS категорию и имя объекта, вы получите информацию о последних связанных с ним ошибках: 

SQL> SHOW ERRORS категория [схема.]объект

Например, чтобы просмотреть информацию о последних ошибках в процедуре wordcount, выполните такую команду:

SQL> SHOW ERRORS FUNCTION wordcount 

Будьте внимательны при интерпретации выходного сообщения:

No errors.

Оно выводится в трех случаях: (1) когда код объекта откомпилирован успешно; (2) вы задали неверную категорию (скажем, функцию вместо процедуры); и (3) объект с заданным именем не существует.

Полный список категорий, поддерживаемых этой командой, зависит от версии СУБД, но в него как минимум входят следующие категории: 

DIMENSION
FUNCTION
JAVA SOURCE
JAVA CLASS
PACKAGE
PACKAGE BODY
PROCEDURE
TRIGGER
TYPE
TYPE BODY
VIEW

Компилятор обнаружил оба вхождения переменной и сообщил точные номера строк и столбцов. Более подробную информацию об ошибке можно найти по идентификатору (в данном случае PLS-00201) в документации Oracle Database Error Messages.

Во внутренней реализации команда SHOW ERRORS обращается с запросом к представлению Oracle USER_ERRORS из словаря данных. В принципе вы можете обращаться к этому представлению и самостоятельно, но обычно это просто не нужно (см. врезку «Вывод других ошибок»).

Команда SHOW ERRORS часто добавляется послед каждой инструкции CREATE, создающей хранимую программу PL/SQL. Поэтому типичный шаблон для построения хранимых процедур в SQL*Plus может начинаться так:

CREATE OR REPLACE тип_программы
AS
   ... ваш код
END;
/
SHOW ERRORS

(Обычно я не включаю команду SET ECHO ON в сценарий, а просто ввожу ее в командной строке, когда это потребуется.)

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

 

Выполнение хранимой процедуры PL/SQL

Мы рассмотрели два способа вызова хранимой программы: заключение ее в простом блоке PL/SQL и использование команды EXECUTE среды SQL*Plus. Одни хранимые процедуры также можно использовать в других. Например, функция wordcount может использоваться в любом месте, где может использоваться целочисленное выражение. Короткий пример тестирования функции wordcount с входным значением CHR(9), которое является ASCII-кодом символа табуляции: 

BEGIN
   DBMS_OUTPUT.PUT_LINE('Введенная строка содержит ' || wordcount(CHR(9)) || '
      слов');
END;
/

Вызов функции wordcount включен в выражение как аргумент процедуры DBMS_OUTPUT. PUT_LINE. В таких случаях PL/SQL автоматически преобразует целое число в строку, чтобы соединить его с двумя другими литеральными выражениями. Результат получается следующим:

Введенная строка содержит 0 слов

Многие функции PL/SQL можно вызывать и из SQL-инструкций. Несколько примеров использования функции wordcount:

  • Включение в список выборки для вычисления количества слов в столбце таблицы:
SELECT isbn, wordcount(description) FROM books; 
  • Использование в ANSI-совместимой инструкции CALL для привязки выходных данных функции к переменной SQL*Plus и вывода результата: 
VARIABLE words NUMBER
   CALL wordcount('некоторый_ текст') INTO :words;
   PRINT :words

То же, но с выполнением функции из удаленной базы данных, определяемой ссылкой test.newyork.ora.com:

CALL Адрес электронной почты защищен от спам-ботов. Для просмотра адреса в вашем браузере должен быть включен Javascript.('некоторый_текст') INTO :words; 

Выполнение функции, принадлежащей схеме bob, при подключении к любой схеме с соответствующей привилегией:

SELECT bob.wordcount(description) FROM books WHERE id = 10007; 

 

Вывод хранимых процедур PL/SQL

Рано или поздно вам потребуется просмотреть список имеющихся хранимых процедур и последние версии их исходного кода, которые Oracle хранит в словаре данных. Эту задачу намного проще выполнить в графических служебных программах, но если у вас такой программы нет, можно написать несколько SQL-инструкций, извлекающих из словаря данных нужную информацию.

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

SELECT * FROM USER_OBJECTS;

Представление содержит сведения о каждом объекте: его имя, тип, время создания, время последней компиляции, состояние работоспособности и другую полезную информацию.

Если вам нужно получить данные об интерфейсе программы в SQL*Plus, проще всего воспользоваться командой DESCRIBE

SQL> DESCRIBE wordcount
FUNCTION wordcount RETURNS BINARY_INTEGER
Argument Name                  Type                    In/Out Default?
------------------------------ ----------------------- ------ --------
STR                            VARCHAR2                IN

Команда DESCRIBE также работает с таблицами, объектными типами, процедурами и пакетами. Чтобы просмотреть полный исходный код хранимых процедур, обратитесь с запросом к представлению USER_SOURCE или TRIGGER_SOURCE

 

Управление привилегиями и создание синонимов хранимых процедур

Созданную вами программу на PL/SQL обычно не может выполнять никто, кроме вас или администратора базы данных. Предоставить право на ее применение другому пользователю можно с помощью инструкции GRANT:

GRANT EXECUTE ON wordcount TO scott;

Инструкция REVOKE лишает пользователя этой привилегии:

REVOKE EXECUTE ON wordcount FROM scott; 

Привилегия выполнения EXECUTE также может быть представлена роли:

GRANT EXECUTE ON wordcount TO all_mis; 

а также всем пользователям Oracle:

GRANT EXECUTE ON wordcount TO PUBLIC; 

Если привилегия EXECUTE представляется отдельному пользователю (например, с идентификатором scott), затем — роли, в которую входит этот пользователь (например, all_mis), и наконец, — всем пользователям, Oracle запомнит все три варианта ее предоставления. Любой из них позволит пользователю scott выполнять программу. Но если вы захотите лишить данного пользователя этой возможности, то сначала следует отменить привилегию пользователя с идентификатором scott, а затем аннулировать привилегию на выполнение функции для всех пользователей (PUBLIC) и роли (или же исключить пользователя из этой роли).

Для просмотра списка привилегий, предоставленных другим пользователям и ролям, можно запросить информацию представления USER_TAB_PRIVS_MADE. Имена программ в этом представлении почему-то выводятся в столбце table_name

SQL> SELECT table_name, grantee, privilege
2 FROM USER_TAB_PRIVS_MADE
3 WHERE table_name = 'WORDCOUNT';
TABLE_NAME                     GRANTEE                        PRIVILEGE
------------------------------ ------------------------------ -----------
WORDCOUNT                      PUBLIC                         EXECUTE
WORDCOUNT                      SCOTT                          EXECUTE
WORDCOUNT                      MIS_ALL                        EXECUTE

Если пользователь scott имеет привилегию EXECUTE на выполнение программы wordcount, он, возможно, захочет создать для нее синоним, чтобы ему не приходилось указывать перед именем программы префикс с именем схемы: 

SQL> CONNECT scott/tiger
Connected.
SQL> CREATE OR REPLACE SYNONYM wordcount FOR bob.wordcount;

Теперь пользователь может выполнять программу, ссылаясь на ее синоним:

IF wordcount(localvariable) > 100 THEN... 

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

Синоним можно определить для процедуры, функции, пакета или пользовательского типа. В синонимах процедур, функций и пакетов может скрываться не только схема, но и база данных; синонимы для удаленных программ создаются так же просто, как и для локальных. Однако синонимы могут скрывать только идентификаторы схем и баз данных; синоним не может использоваться вместо пакетной подпрограммы.

Созданный синоним удаляется простой командой:

DROP SYNONYM wordcount;

 

Удаление хранимой программы (процедуры) PL/SQL

Если вы твердо уверены в том, что какая-либо хранимая программа вам уже не понадобится, удалите ее с помощью команды SQL DROP. Например, следующая команда удаляет хранимую функцию wordcount

DROP FUNCTION wordcount;

Полное удаление пакета, который может состоять из двух элементов (спецификации и тела):

DROP PACKAGE pkgname; 

Также можно удалить только тело пакета без отмены соответствующей спецификации:

DROP PACKAGE BODY pkgname; 

При удалении программы, которая вызывается из других программ, последние помечаются как недействительные (INVALID).

 

Сокрытие исходного кода хранимой программы (процедуры)  PL/SQL

При создании программы PL/SQL описанным выше способом ее исходный код сохраняется в словаре данных в виде обычного текста, который администратор базы данных может просмотреть и даже изменить. Для сохранения профессиональных секретов и предотвращения постороннего вмешательства в программный код перед распространением его следует зашифровать или скрыть иным способом.

Oracle предлагает приложение командной строки wrap, которое преобразует серию команд CREATE в комбинацию обычного текста и шестнадцатеричных кодов. Это действие не является шифрованием в прямом смысле слова, но все же направлено на сокрытие кода. Приведем несколько фрагментов преобразованного кода: 

FUNCTION wordcount wrapped
0
abcd
abcd
...разрыв...
1WORDS:
10:
1LEN:
1NVL:
1LENGTH:
1INSIDE_A_WORD:
1BOOLEAN:
...разрыв...
a5 b 81 b0 a3 a0 1c 81
b0 91 51 a0 7e 51 a0 b4
2e 63 37 :4 a0 51 a5 b a5
b 7e 51 b4 2e :2 a0 7e b4
2e 52 10 :3 a0 7e 51 b4 2e
d :2 a0 d b7 19 3c b7 :2 a0
d b7 :2 19 3c b7 a0 47 :2 a0

Но если вам понадобится полноценное шифрование (скажем, для передачи такой секретной информации, как пароль), полагаться на возможности wrap не следует. 

Вас заинтересует / Intresting for you:

Управление приложениями PL/SQL...
Управление приложениями PL/SQL... 3109 просмотров Stas Belkov Thu, 16 Jul 2020, 06:20:48
Встроенные методы коллекций PL...
Встроенные методы коллекций PL... 7004 просмотров sepia Tue, 29 Oct 2019, 09:54:01
Работа с числами в PL/SQL на п...
Работа с числами в PL/SQL на п... 16431 просмотров Antoniy Mon, 28 May 2018, 16:45:11
Основы языка PL/SQL: использов...
Основы языка PL/SQL: использов... 3032 просмотров Ирина Светлова Tue, 06 Feb 2018, 14:04:03
Войдите чтобы комментировать

OraCool аватар
OraCool ответил в теме #9503 08 окт 2019 04:50
Спасибо автору за статью. Все особенности, синтаксис и примеры очень достойно представлены.