body.sql

oracle dal gen,生成.NET ORACLE DAL层代码

create or replace PACKAGE BODY PKG_GEN AS  

  function get_version return varchar2 is
  begin
    return g_version;
  end;
  
  function get_procedure_params(
                p_table_name in varchar2, 
                p_prefix in varchar2 default null, 
                p_postfix in varchar2 default null,                 
                p_delimiter in varchar2 default ', ',
                p_pk in integer default 0,
                p_fk in integer default 0) return varchar2 is
    l_pk_param    clob;
  begin 
    for r in (select column_name, data_type from table_info where lower(table_name) = lower(p_table_name) and pk = p_pk and fk = p_fk)
    loop  
        /* Building input parameters */
        if (length(l_pk_param) > 0) then l_pk_param := l_pk_param || p_delimiter; end if;
        
        l_pk_param := l_pk_param || p_prefix || r.column_name || p_postfix;  
        
        l_pk_param := replace(l_pk_param, '#COLNAME', r.column_name);
        l_pk_param := replace(l_pk_param, '#TYPE', r.data_type);        
    end loop;        
    
    return l_pk_param;
  end;

  function generate_merge_procedure(p_table_name in varchar2) return clob is
  
    l_proc          clob;
    l_insert_column clob;
    l_insert_param  clob;
    l_update_column clob;
    
    l_param      clob;    
    l_pk_if         clob;
    l_pk_where      clob;
    
    l_id_param_out  varchar2(256);
  begin
    l_proc := 
      '
      procedure merge_#TABLENAME(#PARAM) is
        l_rowid   rowid;
        l_exist   integer := 0;
      begin
        pre_merge(#INSERTPARAMS);
      
        for r in (select * from #TABLENAME where 1=1 #PKWHERE)
        loop
          l_exist := 1;
        end loop;
      
        if (l_exist = 0) then
          insert into #TABLENAME (#INSERTCOLUMNS) VALUES(#INSERTPARAMS) returning rowid into l_rowid;            
          select #PKCOLUMNS into #PKINTOCOLUMNS from #TABLENAME where rowid = l_rowid;           
        else
          update #TABLENAME SET #UPDATECOLUMNS WHERE 1=1 #PKWHERE;                         
          
          #RETURNPARAM
        end if;
        
        post_merge(#INSERTPARAMS);
        
        commit;
      end;
      ';
  
    /* Building input parameters */              
    l_param := get_procedure_params(p_table_name, 'p_', ' in #TYPE') || ', ' || get_procedure_params(p_table_name, 'x_', ' out nocopy #TYPE', p_pk => 1);
    l_insert_column := get_procedure_params(p_table_name);
    l_insert_param := get_procedure_params(p_table_name, 'p_');
    l_update_column := get_procedure_params(p_table_name, '', ' = p_#COLNAME');

    l_pk_if := get_procedure_params(p_table_name, 'p_', ' is null', ' and ', 1);
    l_pk_where := get_procedure_params(p_table_name, ' and ', ' = p_#COLNAME', '', 1);
    
    l_proc := replace(l_proc, '#TABLENAME', p_table_name);
    l_proc := replace(l_proc, '#PARAM', l_param);
    l_proc := replace(l_proc, '#PKIF', l_pk_if);
    
    l_proc := replace(l_proc, '#PKCOLUMNS', get_procedure_params(p_table_name, p_pk => 1));
    l_proc := replace(l_proc, '#PKINTOCOLUMNS', get_procedure_params(p_table_name, 'x_', p_pk => 1));
    
    l_proc := replace(l_proc, '#INSERTCOLUMNS', l_insert_column);
    l_proc := replace(l_proc, '#INSERTPARAMS', l_insert_param);
    l_proc := replace(l_proc, '#UPDATECOLUMNS', l_update_column);
    l_proc := replace(l_proc, '#PKWHERE', l_pk_where);
    l_proc := replace(l_proc, '#RETURNPARAM', get_procedure_params(p_table_name, 'x_', ' := p_#COLNAME; ', '', p_pk => 1));    
    l_proc := replace(l_proc, '#LOCALPARAM', get_procedure_params(p_table_name, 'l_', ' #TYPE; ', '', p_pk => 1));    
    
    return l_proc;
  end;    


  function generate_select_function(p_table_name in varchar2, p_order_by_columns in varchar2 default null) return clob is
    l_proc        clob;
    l_columns     clob;
    l_pk_param    clob;
    l_pk_if       clob;
    l_pk_where    clob;
    l_pk_using    clob;
  begin 
    l_proc :=
      '    
      function get_#TABLENAME(#PKPARAM, p_where in varchar2 default null) return sys_refcursor is
        cur       sys_refcursor;
        sql_stmt  varchar2(32000);
      begin
        pre_get(#PKUSING, p_where);
        
        sql_stmt := ''select #COLUMNS from #TABLENAME where 1=1'';
      
        if (#PKIF) then
          sql_stmt := sql_stmt || p_where;
          open cur for sql_stmt;
        else
          sql_stmt := sql_stmt || '' #PKWHERE #ORDERBY''; 
          open cur for sql_stmt using #PKUSING;
        end if;
        
        post_get(#PKUSING, p_where);
        
        return cur;
      end;
      ';              

    l_proc := replace(l_proc, '#TABLENAME', p_table_name);
    l_proc := replace(l_proc, '#COLUMNS', get_procedure_params(p_table_name));
    l_proc := replace(l_proc, '#PKPARAM', get_procedure_params(p_table_name, 'p_', ' in #TYPE', p_pk => 1));
    l_proc := replace(l_proc, '#PKIF', get_procedure_params(p_table_name, 'p_', ' is null', ' and ', 1));
    l_proc := replace(l_proc, '#PKWHERE', get_procedure_params(p_table_name, ' and ', ' = p_#COLNAME', '', 1));
    l_proc := replace(l_proc, '#PKUSING', get_procedure_params(p_table_name, 'p_', p_pk => 1));
    
    if (p_order_by_columns is not null) then
      l_proc := replace(l_proc, '#ORDERBY', ' order by ' || p_order_by_columns);
    else
      l_proc := replace(l_proc, '#ORDERBY', '');
    end if;
    
    return l_proc;
  end;
  
  function generate_delete_procedure(p_table_name in varchar2) return clob is
    l_proc  clob;
    l_id_type  clob;
  begin
    l_proc := 
      '
      procedure delete_#TABLENAME(#PKPARAM) is
      begin
        pre_delete(#PARAM);
      
        if (#PKIF) then
          delete from #TABLENAME;
        else
          delete from #TABLENAME where 1=1 #PKWHERE;
        end if;
        
        post_delete(#PARAM);
        
        commit;
      end;
      ';
    
    l_proc := replace(l_proc, '#TABLENAME', p_table_name);
    l_proc := replace(l_proc, '#PARAM', get_procedure_params(p_table_name, 'p_', '', p_pk => 1));
    l_proc := replace(l_proc, '#PKPARAM', get_procedure_params(p_table_name, 'p_', ' in #TYPE', p_pk => 1));
    l_proc := replace(l_proc, '#PKIF', get_procedure_params(p_table_name, 'p_', ' is null', ' and ', 1));
    l_proc := replace(l_proc, '#PKWHERE', get_procedure_params(p_table_name, ' and ', ' = p_#COLNAME', '', 1));
    
    return l_proc;
  end;

  function generate_package(p_table_name in varchar2, p_package_name in varchar2) return clob is
    l_pkg_header      clob;
  begin
    l_pkg_header := 
      '
      create or replace package #PACKAGENAME as
          procedure pre_get(#PKPARAM, p_where in varchar2);
          function get_#TABLENAME(#PKPARAM, p_where in varchar2 default null) return sys_refcursor;
          procedure post_get(#PKPARAM, p_where in varchar2);
          
          procedure pre_merge(#PARAM);
          procedure merge_#TABLENAME(#MERGEPARAM);
          procedure post_merge(#PARAM);
          
          procedure pre_delete(#PKPARAM);
          procedure delete_#TABLENAME(#PKPARAM);
          procedure post_delete(#PKPARAM);
       end;
      ';
                    
    l_pkg_header := replace(l_pkg_header, '#PACKAGENAME', p_package_name);
    l_pkg_header := replace(l_pkg_header, '#TABLENAME', p_table_name);
    l_pkg_header := replace(l_pkg_header, '#PKPARAM', get_procedure_params(p_table_name, 'p_', ' in #TYPE', p_pk => 1));
    l_pkg_header := replace(l_pkg_header, '#PARAM', get_procedure_params(p_table_name, 'p_', ' in #TYPE'));
    l_pkg_header := replace(l_pkg_header, '#MERGEPARAM', get_procedure_params(p_table_name, 'p_', ' in #TYPE') || ', ' || get_procedure_params(p_table_name, 'x_', ' out nocopy #TYPE', p_pk => 1));
                    
    return l_pkg_header;                    
  end;
  
  
  function generate_package_body(p_table_name in varchar2, p_package_name in varchar2) return clob is
    l_pkg_body      clob;
    l_param           clob;
  begin
    l_pkg_body := 
      '
      create or replace package body #PACKAGENAME as
        /*
         *  Get section
         */
        procedure pre_get(#PKPARAM, p_where in varchar2) is
        begin
          null;
        end;
        
        #SELECT
        
        procedure post_get(#PKPARAM, p_where in varchar2) is
        begin
          null;
        end;
        
        
        /*
         *  Merge section
         */
        
        procedure pre_merge(#PARAM) is
        begin
          null;
        end;
        
        #MERGE
        
        procedure post_merge(#PARAM) is
        begin
          null;
        end;
        
        
        /*
         *  Delete section
         */
        
        procedure pre_delete(#PKPARAM) is
        begin
          null;
        end;
        
        #DELETE
        
        procedure post_delete(#PKPARAM) is
        begin
          null;
        end;
        
      begin        
        null;
      end;
      ';
                    
    l_pkg_body := replace(l_pkg_body, '#PACKAGENAME', p_package_name);
    l_pkg_body := replace(l_pkg_body, '#SELECT', generate_select_function(p_table_name));
    l_pkg_body := replace(l_pkg_body, '#MERGE', generate_merge_procedure(p_table_name));
    l_pkg_body := replace(l_pkg_body, '#DELETE', generate_delete_procedure(p_table_name));
    l_pkg_body := replace(l_pkg_body, '#PKPARAM', get_procedure_params(p_table_name, 'p_', ' in #TYPE', p_pk => 1));
    l_pkg_body := replace(l_pkg_body, '#PARAM', get_procedure_params(p_table_name, 'p_', ' in #TYPE'));
    
                        
    return l_pkg_body;                    
  end;
  
END PKG_GEN;