Приложение А. Пример генератора пакетов 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
)
    

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

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

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

create sequence seq_drawing
start with 1
maxvalue 999999999999999999999999999
minvalue 1
nocycle
nocache
noorder
    

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

Пакеты будут содержать процедуры для поиска, обновления, вставки, удаления записей из таблицы. В спецификации пакета 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;
/
    

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

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

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

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".