Опубликован: 25.07.2012 | Уровень: специалист | Доступ: платный
Дополнительный материал 1:

Приложение А. Пример генератора пакетов PL/SQL

< Лекция 9 || Дополнительный материал 1: 1234
Аннотация: Приводится пример генератора пакетов PL/SQL. Рассматривается код генератора, сгенерированный код, а также код, созданный вручную и необходимый для работы сгенерированного кода.

Описание

Рассмотрим пример генерации кода пакета для одной таблицы. Пакет будет содержать функциональность для выполнения базовых и стандартных операций над записями в таблице. Это поиск и фильтрация записей, обновление, вставка и удаление строки. То есть все те операции, которые необходимо выполнять над практически любой таблицей в любой базе данных. Возьмем в качестве примера немного измененную таблицу drawing из "лекции 3" . Поля таблицы drawing следующие:

  • id (идентификатор),
  • name (название чертежа),
  • title (описание),
  • revision (номер последней версии чертежа),
  • type_id (идентификатор типа документа, внешний ключ).

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

create table drawing ( 
id            number primary key,
name            varchar2(100 char),
title            varchar2(1000 char),
revision            varchar2(10 char),
type_id            number,
update_date            date,
update_user_id            number
)
    
Пример A.1.

Как видим, кроме указанных полей имеются также поля update_date - для сохранения даты последнего обновления строки и update_user_id - для сохранения идентификатора пользователя (внешнего ключа), выполнившего последнее обновление строки таблицы. Здесь предполагается, что в базе имеется таблица пользователей приложения. Более подробно в примерах она рассматриваться не будет.

Последовательность

Нужен также запрос на создание последовательности (sequence) Oracle по следующему шаблону:

create sequence seq_drawing
start with 1
maxvalue 999999999999999999999999999
minvalue 1
nocycle
nocache
noorder
    
Пример A.2.

Спецификация пакета

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

create or replace package pkg_drawing as 

    --------function to create search query------------------
    function fun_search_query(
        p_name in varchar2,
        p_title in varchar2,
        p_revision in varchar2,
        p_type_id in number
    ) return varchar2;

    --------procedure to save------------------
    procedure prc_save(p_id number,
        p_name in varchar2,
        p_title in varchar2,
        p_revision in varchar2,
        p_type_id in number,
        p_update_user_id in number);

    --------search procedure------------------
    procedure prc_search(
        p_name in varchar2,
        p_title in varchar2,
        p_revision in varchar2,
        p_type_id in number,
        p_page in number,
        p_pagesize in number,
        p_order_by varchar2,
        p_order_type varchar2,
        p_recordset out types.ref_cursor);

    --------count procedure------------------
    procedure prc_count(
        p_name in varchar2,
        p_title in varchar2,
        p_revision in varchar2,
        p_type_id in number,
        p_pagesize in number,
        p_recordset out types.ref_cursor);

    --------show one item procedure ------------------
    procedure prc_show_by_id(p_id number,  p_recordset out types.ref_cursor );

    --------delete procedure ------------------
    procedure prc_delete(p_id number);

end pkg_drawing;
/
    
Пример A.3.

Описание содержимого пакета

В таблице представлены названия и описания стандартных функций и процедур, находящихся в пакете:

fun_search_query функция, формирующая текст запроса с условиями пакета. В нем для формирования части запроса используются функции пакета pkg_lib. То есть фактически генерируемый пакет сам генерирует часть текста запроса;
prc_search процедура, возвращающая результат поиска согласно указанным критериям в виде ссылки на курсор. Также сортирует результат поиска по параметрам p_order_by, p_order_type;
prc_count возвращает в виде курсора количество записей при поиске по указанным критериям, а также количество страниц с записями при размере страницы p_pagesize;
prc_save сохраняет запись в таблице базы данных. Если запись уже существует, то она обновляется (применяется запрос UPDATE), иначе создается новая (применяется запрос INSERT);
prc_delete удаляет запись из таблицы базы данных;
prc_show_by_id возвращает в результате поиска одну запись по указанному первичному ключу.

В теле пакета также имеется константа, содержащая часть текста запроса:

const_sqltxt constant varchar2(1000 char):= 'select '||
            ' id,number, title, revision, type_id, update_date, update_user_id'|| 
            ' from drawing tbl where 1=1 ';
    

Впоследствии к этому запросу будут добавляться необходимые условия для фильтрации и сортировки. Хотя текст запроса генерируется только для одной таблицы, генератор можно усовершенствовать для создания запроса с участием нескольких таблиц, с учетом их взаимосвязей. Для простоты часть текста запроса сформирована просто как "where 1=1".

< Лекция 9 || Дополнительный материал 1: 1234
Дмитрий Клочков
Дмитрий Клочков
Россия, Рубцовск
Волков Олег
Волков Олег
Украина, Днепропетровск