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

create or replace package body pkg_drawing as 
    const_sqltxt constant varchar2(1000 char):= 'select '||
                ' id,name, title, revision, type_id, update_date, update_user_id'|| 
                ' from drawing tbl where 1=1 '; 

    --------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 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.name',p_name);
        p_wheretxt := p_wheretxt || pkg_lib.fun_add_like('tbl.title',p_title);
        p_wheretxt := p_wheretxt || pkg_lib.fun_add_like('tbl.revision',p_revision);
        
        return const_sqltxt||p_wheretxt;
    end;

    --------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) is 
    begin
        if p_id > 0 then 
            update drawing set
                name = p_name,
                title = p_title,
                revision = p_revision,
                type_id = p_type_id,
                update_user_id = p_update_user_id,
                update_date = sysdate
            where id = p_id;
            commit;
        else
            insert into drawing(id,name, title, revision, type_id, update_date, update_user_id)
            values (seq_drawing.nextval,p_name, p_title, p_revision, p_type_id, sysdate, p_update_user_id);
            commit;
        end if;
    end;

    --------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) 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_name, p_title, p_revision, 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_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) is 
        p_sqltxt varchar2(4000);
    begin
        p_sqltxt := fun_search_query(p_name, p_title, p_revision, 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 drawing where id = p_id;
        if t_var > 0 then
            delete from drawing where id = p_id;
        commit;
        end if;
    end;

end pkg_drawing;
/
    

class DBTable
{
    //Название таблицы
    public string TableName;

    //Количество полей в таблице
    public int FieldCount;

    //Массив с названиями полей таблицы
    public string[] FieldName;

    //Массив с названиями типов полей таблицы
    public string[] FieldTypeName;

    //Массив с указанием длины строковых полей таблицы
    public string[] FieldLength;

    //Часть запроса для сортировки по умолчанию
    public string defaultOrderString;

    //Часть пути для сохранения сгенерированного кода
    public string filesPath;

}
    

using System;
using System.Collections.Generic;
using System.Collections;
using System.Linq;
using System.Text;
using System.IO;

namespace PLSQLCodeGenerator
{
    class CodeGenerator
    {
        //Константы tab1, tab2, tab3, tab4 предназначены для установки отступов
        //в начале строк генерируемого приложения.
        public const string tab1 = "    ";
        public const string tab2 = "        ";
        public const string tab3 = "            ";
        public const string tab4 = "                ";

        //В этих константах хранятся часто применяемые участки кода.
        public const string p_recordset = " p_recordset out types.ref_cursor ";
        public const string add_equal_m1 = "p_wheretxt := p_wheretxt || pkg_lib.fun_add_equal_m1";
        public const string add_like = "p_wheretxt := p_wheretxt || pkg_lib.fun_add_like";

        //Здесь хранятся окончания имен для генерируемых файлов
        //В трех файлах сохраняются запросы на создание объектов,
        //спецификация и тело пакета.
        public const string obj_filename = @"Objects.txt";
        public const string package_spec_filename = @"PackageSpec.txt";
        public const string package_body_filename = @"PackageBody.txt";

        //Сокращенное имя таблицы по умолчанию.
        public const string tbl = "tbl";

        //Процедура для вывода сгенерированного результата в файл.
        public void PutResult(List<string> pList, String filePath)
        {
            if (File.Exists(filePath))
            {
                File.Delete(filePath);
            }
            using (StreamWriter sw = File.CreateText(filePath))
            {
                foreach (string str in pList)
                {
                    sw.WriteLine(str);
                }
            }
        }

        //Возвращается текст списка переменных
        //в виде: "p_number, p_title, p_revision, p_type_id",
        //используется при генерации тела пакета.
        public string GetParamListMain(DBTable table)
        {
            string result = "";
            for (int i = 0; i < table.FieldCount; i++)
            {
                result = result + "p_" + table.FieldName[i];
                if (i < table.FieldCount - 1) result = result + ", ";

            }
            return result;
        }

        //К тексту списка переменных добавляются:
        //в начало - следующее значение последовательности
        //в конец - текущая дата и идентификатор пользователя.
        //Используется при генерации запроса вставки строки.
        //Результат получается следующего вида:
        //"seq_drawing.nextval,p_number, p_title, p_revision, p_type_id, sysdate, p_update_user_id".
        public string GetParamListAll(DBTable table)
        {
            return "seq_" + table.TableName + ".nextval," + GetParamListMain(table) +
               ", sysdate, p_update_user_id";
        }

        //Возвращается текст списка полей таблицы
        //в виде: "number, title, revision, type_id".
        //Используется при генерации запросов SELECT и INSERT.
        public string GetFieldListMain(DBTable table)
        {
            string result = "";
            for (int i = 0; i < table.FieldCount; i++)
            {
                result = result + table.FieldName[i];
                if (i < table.FieldCount - 1) result += ", ";
            }
            return result;
        }

        //К тексту списка полей таблицы добавляются
        //поля идентификатора таблицы, даты последнего обновления,
        //а также идентификатора пользователя.
        //Результат получается следующего вида:
        //"id,number, title, revision, type_id, update_date, update_user_id".
        public string GetFieldListAll(DBTable table)
        {
            return "id," + GetFieldListMain(table) + ", update_date, update_user_id";
        }

        //Создается список параметров, применяемый
        //в сигнатурах функций и процедур как в теле,
        //так и в спецификации пакета.
        //Результат получается следующего вида:
        //        p_number in varchar2,
        //        p_title in varchar2,
        //        p_revision in varchar2,
        //        p_type_id in number
        public List<string> GetParametersDeclaration(DBTable table, bool is_last)
        {
            //Переменная result имеет тип List<string> и является набором строк.
            //В ней сохраняется сгенерированный код, который впоследствии
            //может быть выведен в файл.
            List<string> result = new List<string>();
            for (int i = 0; i < table.FieldCount; i++)
            {
                string temp = tab2;
                temp += "p_" + table.FieldName[i] + " in " + table.FieldTypeName[i];
                if (i < table.FieldCount - 1 || !is_last) temp += ",";
                result.Add(temp);
            }
            return result;
        }

        //Формируется запрос на создание таблицы.
        //Результат возвращается в виде набора строк.
        public List<string> GetCreateTable(DBTable table)
        {
            List<string> result = new List<string>();
            result.Add("create table " + table.TableName + " ( ");
            result.Add("id" + tab3 + "number primary key,");
            //В цикле для каждого поля таблицы
            //формируется код объявления.
            for (int i = 0; i < table.FieldCount; i++)
            {
                string temp = "";
                temp = table.FieldName[i] + tab3 + table.FieldTypeName[i];
                if (!(table.FieldLength[i] == ""))
                {
                    temp += "(" + table.FieldLength[i] + ")";
                }
                temp += ",";
                result.Add(temp);
            }
            //Всегда добавляются
            //поля update_date и update_user_id.
            result.Add("update_date" + tab3 + "date,");
            result.Add("update_user_id" + tab3 + "number");
            result.Add(")");
            return result;
        }

        //Формирование запроса на создание последовательности Oracle.
        public List<string> GetCreateSequence(DBTable table)
        {
            List<string> result = new List<string>();
            result.Add("create sequence seq_" + table.TableName);
            result.Add("start with 1");
            result.Add("maxvalue 999999999999999999999999999");
            result.Add("minvalue 1");
            result.Add("nocycle");
            result.Add("nocache");
            result.Add("noorder");
            return result;
        }

        //В одном файле сохраняются запросы
        //на создание таблицы и последовательности.
        public void SaveCreateObjects(DBTable table)
        {
            List<string> result = new List<string>();
            //В набор строк добавляется запрос на создание таблицы.
            result.AddRange(GetCreateTable(table));
            //Запросы будут разделены пустой строкой.
            result.Add("");
            //В набор строк добавляется запрос на создание последовательности.
            result.AddRange(GetCreateSequence(table));
            PutResult(result, table.filesPath + obj_filename);
        }

        //Функция для создания кода спецификации пакета.
        public List<string> GetPackageSpecification(DBTable table)
        {
            List<string> result = new List<string>();
            //Объявление спецификации пакета.
            result.Add("create or replace package pkg_" + table.TableName + " as ");

            //Объявление функции, формирующей текст запроса.
            result.Add("");
            result.Add(tab1 + "--------function to create search query------------------");
            result.Add(tab1 + "function fun_search_query(");
            result.AddRange(GetParametersDeclaration(table, true));
            result.Add(tab1 + ") return varchar2;");

            //Объявление процедуры сохранения записи.
            result.Add("");
            result.Add(tab1 + "--------procedure to save------------------");
            result.Add(tab1 + "procedure prc_save(p_id number,");
            result.AddRange(GetParametersDeclaration(table, false));
            result.Add(tab2 + "p_update_user_id in number);");

            //Объявление процедуры поиска записей по заданным критериям.
            result.Add("");
            result.Add(tab1 + "--------search procedure------------------");
            result.Add(tab1 + "procedure prc_search(");
            result.AddRange(GetParametersDeclaration(table, false));
            result.Add(tab2 + "p_page in number,");
            result.Add(tab2 + "p_pagesize in number,");
            result.Add(tab2 + "p_order_by varchar2,");
            result.Add(tab2 + "p_order_type varchar2,");
            result.Add(tab2 + "p_recordset out types.ref_cursor);");

            //Объявление процедуры подсчета количества записей по заданным критериям.
            result.Add("");
            result.Add(tab1 + "--------count procedure------------------");
            result.Add(tab1 + "procedure prc_count(");
            result.AddRange(GetParametersDeclaration(table, false));
            result.Add(tab2 + "p_pagesize in number,");
            result.Add(tab2 + "p_recordset out types.ref_cursor);");

            //Объявление процедуры выборки одной строки по заданному идентификатору.
            result.Add("");
            result.Add(tab1 + "--------show one item procedure ------------------");
            result.Add(tab1 + "procedure prc_show_by_id(p_id number, " + p_recordset + ");");

            //Объявление процедуры удаления строки.
            result.Add("");
            result.Add(tab1 + "--------delete procedure ------------------");
            result.Add(tab1 + "procedure prc_delete(p_id number);");

            //Конец спецификации пакета
            result.Add("");
            result.Add("end pkg_" + table.TableName + ";");
            result.Add("/");
            
            //Возвращается набор строк, результат работы процедуры.
            return result;
        }

        //Сохранение спецификации пакета в отдельном файле.
        public void SavePackageSpecification(DBTable table)
        {
            PutResult(GetPackageSpecification(table), table.filesPath + package_spec_filename);
        }

        //Здесь формируется код функции создания запроса.
        public List<string> GetSearchFunction(DBTable table)
        {
            List<string> result = new List<string>();
            result.Add(tab1 + "--------function to create search query------------------");
            result.Add(tab1 + "function fun_search_query(");
            result.AddRange(GetParametersDeclaration(table, true));
            result.Add(tab2 + ") return varchar2 is");

            result.Add(tab2 + "p_wheretxt varchar2(4000);");
            result.Add(tab1 + "begin");
            result.Add(tab2 + "--------------equals------------------------");

            //В цикле формируется код вида:
            //p_wheretxt := p_wheretxt || pkg_lib.fun_add_equal_m1('tbl.type_id',p_type_id);
            for (int i = 0; i < table.FieldCount; i++)
            {
                if (table.FieldTypeName[i] == "number")
                {
                    result.Add(tab2 + add_equal_m1 + "('" + tbl + "." + table.FieldName[i] + 
                       "',p_" + table.FieldName[i] + ");");
                }
            }
            result.Add("");
            result.Add(tab2 + "--------------likes------------------------");

            //В цикле формируется код вида:
            //p_wheretxt := p_wheretxt || pkg_lib.fun_add_like('tbl.number',p_number);
            //p_wheretxt := p_wheretxt || pkg_lib.fun_add_like('tbl.title',p_title);
            //p_wheretxt := p_wheretxt || pkg_lib.fun_add_like('tbl.revision',p_revision);
            for (int i = 0; i < table.FieldCount; i++)
            {
                if (table.FieldTypeName[i] == "varchar2")
                {
                    result.Add(tab2 + add_like + "('" + tbl + "." + table.FieldName[i] + 
                       "',p_" + table.FieldName[i] + ");");
                }
            }

            result.Add(tab2);
            result.Add(tab2 + "return const_sqltxt||p_wheretxt;");
            result.Add(tab1 + "end;");
            return result;
        }

        //Формируется код процедуры поиска записей.
        public List<string> GetSearchProcedure(DBTable table)
        {
            List<string> result = new List<string>();
            result.Add(tab1 + "--------search procedure------------------");
            result.Add(tab1 + "procedure prc_search(");
            result.AddRange(GetParametersDeclaration(table, false));
            result.Add(tab2 + "p_page in number,");
            result.Add(tab2 + "p_pagesize in number,");
            result.Add(tab2 + "p_order_by varchar2,");
            result.Add(tab2 + "p_order_type varchar2,");
            result.Add(tab2 + "p_recordset out types.ref_cursor) is ");

            result.Add(tab3 + "sqltxt varchar2(4000);");
            result.Add(tab3 + "p_sqltxt varchar2(4000);");
            result.Add(tab3 + "p_sqltxt_page varchar2(4000);");
            result.Add(tab3 + "p_startpage number;");
            result.Add(tab3 + "p_maxpage number;");
            result.Add(tab3 + "p_ordersqltxt varchar2(1000);");

            result.Add(tab1 + "begin");
            result.Add(tab2 + "p_startpage := p_pagesize*(p_page-1);");
            result.Add(tab2 + "p_maxpage := p_pagesize*p_page;");
            result.Add(tab2 + "p_sqltxt := fun_search_query(" + GetParamListMain(table) + ");");
            result.Add(tab2 + "p_ordersqltxt := pkg_lib.fun_sorting_query(p_order_by,p_order_type,'" 
               + tbl + "." + table.defaultOrderString + "');");
            result.Add(tab2 + "p_sqltxt_page := 'select * from 
               (select s1.*, rownum rnum from ('||p_sqltxt|| p_ordersqltxt|| ') s1) ' ||");
            result.Add(tab4 + "'where rnum<=:max_row_to_fetch and rnum >
            :min_row_to_fetch ' || p_ordersqltxt;");
            result.Add(tab2 + "open p_recordset for p_sqltxt_page using p_maxpage,p_startpage;");
            result.Add(tab1 + "end;");
            return result;
        }

        //Формируется код процедуры сохранения записи.
        public List<string> GetSaveProcedure(DBTable table)
        {
            List<string> result = new List<string>();
            result.Add(tab1 + "--------procedure to save------------------");
            result.Add(tab1 + "procedure prc_save(p_id number,");
            result.AddRange(GetParametersDeclaration(table, false));
            result.Add(tab2 + "p_update_user_id in number) is ");
            result.Add(tab1 + "begin");
            result.Add(tab2 + "if p_id > 0 then ");
            result.Add(tab3 + "update " + table.TableName + " set");

            //В этом цикле генерируется часть запроса UPDATE вида:
            //  number = p_number,
            //  title = p_title,
            //  revision = p_revision,
            //  type_id = p_type_id,
            for (int i = 0; i < table.FieldCount; i++)
            {
                string temp = tab4;
                temp += table.FieldName[i] + " = p_" + table.FieldName[i] + ",";
                result.Add(temp);
            }

            result.Add(tab4 + "update_user_id = p_update_user_id,");
            result.Add(tab4 + "update_date = sysdate");
            result.Add(tab3 + "where id = p_id;");
            result.Add(tab3 + "commit;");
            result.Add(tab2 + "else");
            result.Add(tab3 + "insert into " + table.TableName + 
            "(" + GetFieldListAll(table) + ")");
            result.Add(tab3 + "values (" + GetParamListAll(table) + ");");
            result.Add(tab3 + "commit;");
            result.Add(tab2 + "end if;");

            result.Add(tab1 + "end;");
            return result;
        }

        //Формируется код процедуры подсчета количества записей.
        public List<string> GetCountProcedure(DBTable table)
        {
            List<string> result = new List<string>();
            result.Add(tab1 + "--------count procedure------------------");
            result.Add(tab1 + "procedure prc_count(");
            result.AddRange(GetParametersDeclaration(table, false));
            result.Add(tab3 + "p_pagesize in number,");
            result.Add(tab3 + "p_recordset out types.ref_cursor) is ");
            result.Add(tab2 + "p_sqltxt varchar2(4000);");
            result.Add(tab1 + "begin");
            result.Add(tab2 + "p_sqltxt := fun_search_query
            (" + GetParamListMain(table) + ");");
            result.Add(tab2 + "p_sqltxt := 'select count(1) cnt, ceil(count(1)/:p_pagesize) 
            pagecount from ('||p_sqltxt||') ';");
            result.Add(tab2 + "open p_recordset for p_sqltxt using p_pagesize;");
            result.Add(tab1 + "end;");
            return result;
        }

        //Формируется код процедуры выборки одной записи.
        public List<string> GetShowOneItemProcedure(DBTable table)
        {
            List<string> result = new List<string>();
            result.Add(tab1 + "--------show one item procedure ------------------");
            result.Add(tab1 + "procedure prc_show_by_id(p_id number, 
            " + p_recordset + ") is ");

            result.Add(tab1 + "begin");

            result.Add(tab2 + "if p_id > 0 then");
            result.Add(tab3 + "open p_recordset for");
            result.Add(tab3 + "const_sqltxt || ' and " + tbl + ".id =
             :p_id' using p_id;");
            result.Add(tab2 + "end if;");

            result.Add(tab1 + "end;");
            return result;
        }

        //Формируется код процедуры удаления записи.
        public List<string> GetDeleteProcedure(DBTable table)
        {
            List<string> result = new List<string>();
            result.Add(tab1 + "--------delete procedure ------------------");
            result.Add(tab1 + "procedure prc_delete(p_id number) is ");
            result.Add(tab2 + "t_var number;");
            result.Add(tab1 + "begin");
            result.Add(tab2 + "select count(1) into t_var from " + 
            table.TableName + " where id = p_id;");
            result.Add(tab2 + "if t_var > 0 then");
            result.Add(tab3 + "delete from " + table.TableName + 
            " where id = p_id;");
            result.Add(tab2 + "commit;");
            result.Add(tab2 + "end if;");
            result.Add(tab1 + "end;");
            return result;
        }

        //Функция для создания кода тела пакета.
        public List<string> GetPackageBody(DBTable table)
        {
            List<string> result = new List<string>();
            //Начало пакета.
            result.Add("create or replace package body pkg_" + 
            table.TableName + " as ");

            //Генерация запроса и константы, в которой она будет сохранена.
            result.Add(tab1 + "const_sqltxt constant varchar2(1000 char):= 'select '||");
            result.Add(tab4 + "' " + GetFieldListAll(table) + "'|| ");
            result.Add(tab4 + "' from " + table.TableName + " " +
             tbl + " where 1=1 '; ");

            //Добавление к телу пакета кода функции, возвращающей запрос.
            result.Add("");
            result.AddRange(GetSearchFunction(table));

            //Добавление к телу пакета кода процедуры, сохраняющей запись.
            result.Add("");
            result.AddRange(GetSaveProcedure(table));

            //Добавление к телу пакета кода процедуры, выполняющей поиск записей.
            result.Add("");
            result.AddRange(GetSearchProcedure(table));

            //Добавление к телу пакета кода процедуры, возвращающей количество записей.
            result.Add("");
            result.AddRange(GetCountProcedure(table));

            //Добавление к телу пакета кода процедуры, возвращающей одну запись.
            result.Add("");
            result.AddRange(GetShowOneItemProcedure(table));

            //Добавление к телу пакета кода процедуры, удаляющей запись.
            result.Add("");
            result.AddRange(GetDeleteProcedure(table));

            //Конец тела пакета.
            result.Add("");
            result.Add("end pkg_" + table.TableName + ";");
            result.Add("/");

            return result;
        }

        //Сохранение тела пакета в одном файле.
        public void SavePackageBody(DBTable table)
        {
            PutResult(GetPackageBody(table), table.filesPath + package_body_filename);
        }

        //Конструктор, вызывающий процедуры для генерации кода
        //создания объектов, спецификации и тела пакета
        //и сохранения их в трех файлах.
        public CodeGenerator(DBTable table)
        {
            SaveCreateObjects(table);
            SavePackageSpecification(table);
            SavePackageBody(table);
        }
    }
}