一个自动生成预制SQL 的存储过程（二）

前言

　　我们工作的时候经常会有一些数据是在开发期整理好，到客户那预制到处据库中去，于是乎就有了下面的存储过程。

=======Oracle版本====================================================

create or replace package HRspGenInsertSQL is
TYPE T_CURSOR IS REF CURSOR;
/*
SQL调用模板，如果提示缓冲区溢出需要修改输出Tab中的缓冲区大小
=====================================================
declare
      temp1 varchar(4000);
      cur1 SYS_REFCURSOR;
begin
 HRspGenInsertSQL.GetInsertSQL('HRobjects','HRobjects_NM','2',cur1);
 loop
   fetch cur1 into temp1;
   exit when cur1%notfound;
   dbms_output.put_line(temp1);
   end loop;
 close cur1;
end;
=====================================================
p_tablename 表名
p_tableNM  表内码字段名
p_ifExists 是否包含if exists 判断语句 1:包含;0:不包含;2:如果存在先删除，再插入
*/
Procedure GetInsertSQL(
                              p_tablename varchar,
                              p_tableNM   varchar,
                              p_ifExists  char,
                              p_ReCursor0 out T_Cursor
                  );
end HRspGenInsertSQL;
/
create or replace package body HRspGenInsertSQL is
/*
SQL调用模板，如果提示缓冲区溢出需要修改输出Tab中的缓冲区大小
注意不要修改SQL的左对齐
=====================================================
declare
      temp1 varchar(4000);
      cur1 SYS_REFCURSOR;
begin
 HRspGenInsertSQL.GetInsertSQL('HRobjects','HRobjects_NM','2',cur1);
 loop
   fetch cur1 into temp1;
   exit when cur1%notfound;
   dbms_output.put_line(temp1);
   end loop;
 close cur1;
end;
=====================================================
p_tablename 表名
p_tableNM  表内码字段名
p_ifExists 是否包含if exists 判断语句 1:包含;0:不包含;2:如果存在先删除，再插入
*/
  Procedure GetInsertSQL(
                              p_tablename varchar,
                              p_tableNM   varchar,
                              p_ifExists  char,
                              p_ReCursor0 out T_Cursor
                           ) is
  vSql        varchar2(4000) := ' ';
  sqlValues varchar2(4000);
  sqlInsert varchar2(4000);
  vSqlTemp varchar2(4000) := ' ';
  NM varchar(36);
  cur T_Cursor;
  curSql T_Cursor;
  Column_Name varchar2(30);
  Data_Type varchar2(30);
  Data_Length number;
  vNumber int;
  begin

    execute immediate' truncate table TEMP_UsertableData ';
  vSql := ' select '||p_tableNM||' from '|| p_tablename||' order by '||p_tableNM;
  open  cur for vSql;
  vNumber := 0;
  loop
  /*开始循环*/
    fetch cur into NM;
    exit when cur%notfound;
     
      vSql :=' (' ;
      sqlValues := 'values (''||'  ;
      /*======================================*/
      vSqlTemp := ' select Column_Name,Data_Type,Data_Length from user_tab_columns where Table_Name = upper('''||p_tablename||''')';
      open curSql for vSqlTemp;
      loop
           /*开始循环*/
          fetch curSql into Column_Name,Data_Type,Data_Length;
          exit when curSql%notfound;
          vSql := vSql || Column_Name || ',';

          if(Data_Type in ('LONG','FLOAT','NUMBER')) then
             sqlValues := sqlValues || 'case when '|| Column_Name ||' is null then ''NULL'' else ' || 'cast('|| Column_Name || ' as varchar(30))'||' end' ;
          end if;
          if(Data_Type in ('DATE')) then
             sqlValues := sqlValues || 'case when '|| Column_Name ||' is null then ''NULL'' else '||''''''''' || ' || 'cast('|| Column_Name ||' as varchar(50))'|| '||'''''''''||' end' ;
          end if;
          if(Data_Type in ('VARCHAR2','CLOB','BLOB')) then
             sqlValues := sqlValues || 'case when '|| Column_Name ||' is null then ''NULL'' else '||''''''''' || ' || 'replace('|| Column_Name||','''''''','''''''''''')' || '||'''''''''||' end' ;
          end if;
          if(Data_Type in ('NVARCHAR2','NCLOB')) then
             sqlValues := sqlValues || 'case when '|| Column_Name ||' is null then ''NULL'' else '||'''N'''''' || ' || 'replace('|| Column_Name||','''''''','''''''''''')' || '||'''''''''||' end';
          end if;
          if(Data_Type in ('CHAR')) then
             sqlValues := sqlValues || 'case when '|| Column_Name ||' is null then ''NULL'' else '||''''''''' || ' || 'cast(replace('|| Column_Name||','''''''','''''''''''') as Char(' || cast(Data_Length as varchar)  || '))||'''''''''||' end';
          end if;
          if(Data_Type in ('NCHAR')) then
             sqlValues := sqlValues || 'case when '|| Column_Name ||' is null then ''NULL'' else '||'''N'''''' || ' || 'cast(replace('|| Column_Name||','''''''','''''''''''') as Char(' || cast(Data_Length as varchar)  || '))||'''''''''||' end';
          end if;
          sqlValues := sqlValues || '||'',''||';
      END LOOP;
      /*结束循环*/
      close curSql;
      /*======================================*/

      if(Trim(p_ifExists) is not null and TRIM(p_ifExists) = '1') then
      begin
       sqlInsert := 'declare vCount int := 0;
begin
    select count(1) into vCount from '||p_tablename||' where '||p_tablename||'.'||p_tableNM||' = '''||NM||''';
    if(vCount < 1 ) then';
        sqlInsert := REPLACE(sqlInsert,'''','''''');
       execute immediate('insert into TEMP_UsertableData(datasql,OrderNm) values(''' || sqlInsert ||''','||vNumber||')');
        vNumber := vNumber+1;
      end;
      else if(Trim(p_ifExists) = '2') then
      begin
       sqlInsert := 'declare vCount int := 0;
begin
    select count(1) into vCount from '||p_tablename||' where '||p_tablename||'.'||p_tableNM||' = '''||NM||''';
    if(vCount > 0 ) then';
        sqlInsert := REPLACE(sqlInsert,'''','''''');
       execute immediate('insert into TEMP_UsertableData(datasql,OrderNm) values(''' || sqlInsert ||''','||vNumber||')');
        vNumber := vNumber+1;
       sqlInsert := '        delete from '||p_tablename||' where '||p_tablename||'.'||p_tableNM||' = '''||NM||''' ;';
       sqlInsert := REPLACE(sqlInsert,'''','''''');
       execute immediate('insert into TEMP_UsertableData(datasql,OrderNm) values(''' || sqlInsert ||''','||vNumber||')');
        vNumber := vNumber+1;
      end;
      end if;
      end if;

      if(TRIM(p_ifExists) = '1' OR Trim(p_ifExists) = '2') then
      begin
           sqlInsert := 'select '||'''        INSERT INTO '||p_tablename||''|| SUBSTR(vSql,1,length(vSql)-1)||') ' || SUBSTR(sqlValues,1,length(sqlValues)-4) || ');'','||vNumber||'
           from ' ||p_tablename||' where '||p_tablename||'.'||p_tableNM||' = '''||NM||''' ';
           execute immediate('insert into TEMP_UsertableData(datasql,OrderNm)' || sqlInsert );
           vNumber := vNumber+1;
          insert into TEMP_UsertableData(datasql,OrderNm)
          values ('    end if;
end;
/',vNumber);
           vNumber := vNumber+1;
      end;
      else
      begin
           sqlInsert := 'select '||'''INSERT INTO '||p_tablename||''|| SUBSTR(vSql,1,length(vSql)-1)||') ' || SUBSTR(sqlValues,1,length(sqlValues)-4) || ')'','||vNumber||'
           from ' ||p_tablename||' where '||p_tablename||'.'||p_tableNM||' = '''||NM||''' ';
           execute immediate('insert into TEMP_UsertableData(datasql,OrderNm)' || sqlInsert );
           vNumber := vNumber+1;
           insert into TEMP_UsertableData(datasql,OrderNm)
           values ('/',vNumber);
           vNumber := vNumber+1;
      end;
      end if;

END LOOP;
/*结束循环*/
close cur;
/*取最终结果*/
  vSqlTemp := 'select datasql from TEMP_UsertableData order by OrderNm ';
    open p_ReCursor0 for vSqlTemp;

  end GetInsertSQL;
end HRspGenInsertSQL;
/