Хранимые программные единицы, страница 4

CREATE PROCEDURE create_dept (new_dname CHAR, new_loc CHAR) IS

BEGIN

   INSERT INTO dept

      VALUES (deptno_seq.NEXTVAL, new_dname, new_loc);

END;

Предположим, что пользователь scott дал привилегию EXECUTE на эту процедуру пользователю blake. Когда пользователь blake вызовет эту подпрограмму, утверждение INSERT будет выполнено с привилегиями пользователя scott, т.е. процедура вставит данные в таблицу dept, находящуюся в схеме scott, а не blake.

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

Переносимость подпрограмм

Хранимая подпрограмма позволяет реализовать программную логику обработки данных в таблицах, находящихся, как в собственной, так и в других схемах. Существует несколько методов обращения подпрограммы к объектам, находящимся в другой схеме.

Один из способов - указывать имя схемы при ссылке на объект (INSERT INTO blake.dept ...). Однако это препятствует переносимости программного кода. Предположим, имеется несколько пользователей, каждый из которых должен выполнить одну и ту же подпрограмму для обновления таблиц в своей собственной схеме. В этом случае каждый раз при вызове программной единицы придется менять исходный код, чтобы указать имя текущей схемы. Это неудобно, к тому же, создатель подпрограммы должен иметь соответствующие привилегии на изменяемые объекты, т.к. они не принадлежат его собственной схеме. Причем, эти привилегии должны быть даны явно (не через роль).

Другой способ - скопировать подпрограмму в ту схему, к объектам которой она обращается. Но это затрудняет сопровождение программной единицы, т.к. она хранится в нескольких местах, и, при внесении изменений в программный код, править приходится все копии подпрограммы.

Лучший способ - использование предложения AUTHID при создании программной единицы, которое указывает, что хранимые единицы и SQL предложения, вызываемые подпрограммой, должны выполняться с привилегиями пользователя, который вызвал эту хранимую программную единицу (текущего пользователя). Такие подпрограммы не привязаны к конкретной схеме, и выполнять их могут различные пользователи для изменения своих собственных данных.

CREATE PROCEDURE create_dept (new_dname CHAR, new_loc CHAR)

   AUTHID CURRENT_USER IS

BEGIN

   INSERT INTO dept

      VALUES (deptno_seq.NEXTVAL, new_dname, new_loc);

END;

Информация о хранимых подпрограммах

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

Представление	Описание
USER_SOURCE	Исходные тексты программ для всех хранимых программных модулей
USER_ERRORS	Список сообщений об ошибках компиляции по всем хранимым подпрограммам
USER_OBJECTS	Общая информация о хранимых программных единицах в текущей схеме

Описание хранимой программной единицы, включая список формальных параметров, можно получить с помощью команды SQL*Plus DESCRIBE. Команда показывает описание подпрограммы только в том случае, если она скомпилирована без ошибок.

SQL> DESCRIBE pres_sal

FUNCTION pres_sal RETURNS NUMBER

 Argument Name                  Type                    In/Out Default?

 ------------------------------ ----------------------- ------ --------

 V_SAL                          NUMBER                  IN

USER_OBJECTS

Колонка	Описание
OBJECT_NAME	Имя хранимой программной единицы
OBJECT_ID	Внутренний номер программной единицы
OBJECT_TYPE	Тип подпрограммы (PROCEDURE, FUNCTION)
CREATED	Дата создания программной единицы
LAST_DDL_TIME	Дата последней модификации программной единицы
TIMESTAMP	Дата и время последней компиляции подпрограммы
STATUS	Статус программной единицы (VALID – компиляция прошла успешно, INVALID – с ошибками)

SQL> SELECT object_name, object_type, status FROM user_objects

  2  WHERE object_type='PROCEDURE' OR object_type='FUNCTION';

OBJECT_NAME          OBJECT_TYPE        STATUS

-------------------- ------------------ -------

EMP_SAL              PROCEDURE          INVALID

PRES_SAL             FUNCTION           VALID

USER_SOURCE

Колонка	Описание
NAME	Имя хранимой программной единицы
TYPE	Тип подпрограммы (PROCEDURE, FUNCTION)
LINE	Номер строки исходного кода
TEXT	Текст строки исходного кода

SQL> SELECT text FROM user_source WHERE name='EMP_SAL' ORDER BY line;

TEXT

--------------------------------------------------------------------------------

PROCEDURE emp_sal

       (v_job IN VARCHAR2,

        v_sal_add IN NUMBER := 100,

        v_sal_out OUT NUMBER)

IS

BEGIN

   SELECT sal INTO v_sal_emp FROM emp WHERE LOWER(job)=LOWER(v_job);

   v_sal_out := v_sal_emp + v_sal_add;

EXCEPTION

   WHEN OTHERS THEN

      DBMS_OUTPUT.put_line('Error occurred');

END emp_sal;

12 rows selected.

USER_ERRORS

Колонка	Описание
NAME	Имя хранимой программной единицы
TYPE	Тип подпрограммы (PROCEDURE, FUNCTION)
SEQUENCE	Последовательный номер
LINE	Номер строки кода источника, в котором произошла ошибка
POSITION	Позиция в строке кода источника, в котором произошла ошибка
TEXT	Текст сообщения об ошибке

SQL> SELECT line || '/' || position pos, text FROM user_errors

  2  WHERE name='EMP_SAL' ORDER BY line;

POS   TEXT

----- --------------------------------------------------

7/20  PLS-00201: identifier 'V_SAL_EMP' must be declared

7/4   PL/SQL: SQL Statement ignored

8/17  PLS-00201: identifier 'V_SAL_EMP' must be declared

8/4   PL/SQL: Statement ignored

Зависимости между объектами базы данных

Одни объекты базы данных могут содержать в своем описании ссылки на другие объекты базы данных. Например, хранимая программная единица, в которой используется утверждение SELECT, ссылается на соответствующие таблицы или представления базы данных. Объекты базы данных, содержащие ссылки на объекты базы данных, называются зависимые объекты. А объекты базы данных, на которые ссылаются зависимые объекты, называются ссылаемые объекты.