前言
我们工作的时候经常会有一些数据是在开发期整理好，到客户那预制到处据库中去，于是乎就有了下面的存储过程。
=======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;
/
