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

//Объявление таблицы, его названия и количества полей
DBTable table = new DBTable();
table.TableName = "equipment";
table.FieldCount = 4;

//Метаданные для простоты примера заполняются напрямую в коде.
//Однако их можно считывать и из базы данных или файла XML.
table.FieldName = new string[table.FieldCount];
table.FieldTypeName = new string[table.FieldCount];
table.FieldLength = new string[table.FieldCount];

table.FieldName[0] = "tag_number";
table.FieldTypeName[0] = "varchar2";
table.FieldLength[0] = "100 char";

table.FieldName[1] = "model";
table.FieldTypeName[1] = "varchar2";
table.FieldLength[1] = "100 char";

table.FieldName[2] = "description";
table.FieldTypeName[2] = "varchar2";
table.FieldLength[2] = "200 char";

table.FieldName[3] = "type_id";
table.FieldTypeName[3] = "number";
table.FieldLength[3] = "";

table.defaultOrderString = "id";

//Указывается путь и часть названия файла вида:
//"A:\Result\equipment_" для вывода результатов.
//К нему впоследствии добавляется часть названия 
//файла в зависимости от типа сгенерированного кода:
//A:\Result\equipment_Objects.txt
//A:\Result\equipment_PackageSpec.txt
//A:\Result\equipment_PackageBody.txt
table.filesPath = @"A:\Result\" + table.TableName + "_";

//Вызывается конструктор для осуществления генерации кода
CodeGenerator cg = new CodeGenerator(table);
    

create table equipment ( 
id            number primary key,
tag_number            varchar2(100 char),
model            varchar2(100 char),
description            varchar2(200 char),
type_id            number,
update_date            date,
update_user_id            number
)

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

create or replace package pkg_equipment as 

    --------function to create search query------------------
    function fun_search_query(
        p_tag_number in varchar2,
        p_model in varchar2,
        p_description in varchar2,
        p_type_id in number
    ) return varchar2;

    --------procedure to save------------------
    procedure prc_save(p_id number,
        p_tag_number in varchar2,
        p_model in varchar2,
        p_description in varchar2,
        p_type_id in number,
        p_update_user_id in number);

    --------search procedure------------------
    procedure prc_search(
        p_tag_number in varchar2,
        p_model in varchar2,
        p_description 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_tag_number in varchar2,
        p_model in varchar2,
        p_description 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_equipment;
/
    

create or replace package body pkg_equipment as 
    const_sqltxt constant varchar2(1000 char):= 'select '||
                ' id,tag_number, model, description, type_id, update_date, update_user_id'|| 
                ' from equipment tbl where 1=1 '; 

    --------function to create search query------------------
    function fun_search_query(
        p_tag_number in varchar2,
        p_model in varchar2,
        p_description in varchar2,
        p_type_id in number
        ) return varchar2 is
        p_wheretxt varchar2(4000);
    begin
        --------------equals------------------------
        p_wheretxt := p_wheretxt || pkg_lib.fun_add_equal_m1('tbl.type_id',p_type_id);

        --------------likes------------------------
        p_wheretxt := p_wheretxt || pkg_lib.fun_add_like('tbl.tag_number',p_tag_number);
        p_wheretxt := p_wheretxt || pkg_lib.fun_add_like('tbl.model',p_model);
        p_wheretxt := p_wheretxt || pkg_lib.fun_add_like('tbl.description',p_description);
        
        return const_sqltxt||p_wheretxt;
    end;

    --------procedure to save------------------
    procedure prc_save(p_id number,
        p_tag_number in varchar2,
        p_model in varchar2,
        p_description in varchar2,
        p_type_id in number,
        p_update_user_id in number) is 
    begin
        if p_id > 0 then 
            update equipment set
                tag_number = p_tag_number,
                model = p_model,
                description = p_description,
                type_id = p_type_id,
                update_user_id = p_update_user_id,
                update_date = sysdate
            where id = p_id;
            commit;
        else
            insert into equipment(id,tag_number, model, description, type_id, update_date, 
             update_user_id)
            values (seq_equipment.nextval,p_tag_number, p_model, p_description,
             p_type_id, sysdate, p_update_user_id);
            commit;
        end if;
    end;

    --------search procedure------------------
    procedure prc_search(
        p_tag_number in varchar2,
        p_model in varchar2,
        p_description 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) is 
            sqltxt varchar2(4000);
            p_sqltxt varchar2(4000);
            p_sqltxt_page varchar2(4000);
            p_startpage number;
            p_maxpage number;
            p_ordersqltxt varchar2(1000);
    begin
        p_startpage := p_pagesize*(p_page-1);
        p_maxpage := p_pagesize*p_page;
        p_sqltxt := fun_search_query(p_tag_number, p_model, p_description, p_type_id);
        p_ordersqltxt := pkg_lib.fun_sorting_query(p_order_by,p_order_type,'tbl.id');
        p_sqltxt_page := 'select * from (select s1.*, rownum rnum from ('||p_sqltxt|| p_ordersqltxt|| ') s1) ' ||
                'where rnum<=:max_row_to_fetch and rnum > :min_row_to_fetch ' || p_ordersqltxt;
        open p_recordset for p_sqltxt_page using p_maxpage,p_startpage;
    end;

    --------count procedure------------------
    procedure prc_count(
        p_tag_number in varchar2,
        p_model in varchar2,
        p_description in varchar2,
        p_type_id in number,
            p_pagesize in number,
            p_recordset out types.ref_cursor) is 
        p_sqltxt varchar2(4000);
    begin
        p_sqltxt := fun_search_query(p_tag_number, p_model, p_description, p_type_id);
        p_sqltxt := 'select count(1) cnt, ceil(count(1)/:p_pagesize) pagecount from ('||p_sqltxt||') ';
        open p_recordset for p_sqltxt using p_pagesize;
    end;

    --------show one item procedure ------------------
    procedure prc_show_by_id(p_id number,  p_recordset out types.ref_cursor ) is 
    begin
        if p_id > 0 then
            open p_recordset for
            const_sqltxt || ' and tbl.id = :p_id' using p_id;
        end if;
    end;

    --------delete procedure ------------------
    procedure prc_delete(p_id number) is 
        t_var number;
    begin
        select count(1) into t_var from equipment where id = p_id;
        if t_var > 0 then
            delete from equipment where id = p_id;
        commit;
        end if;
    end;

end pkg_equipment;
/