1.通过sql/存储过程
2.通过java程序
0.sql
-- created by yangtingkun
select 'insert into yourtable values (' || col_num || ', ''' || col_char || ''', to_date(' || to_char(col_date, 'yyyy-mm-dd hh24:mi:ss' ||, '''yyyy-mm-dd hh24:mi:ss'');' from yourtable;
1.通过存储过程生成insert脚本
drop table t_data
/
create table t_data
as
select object_id from dba_objects
where rownum < 100
/
set serveroutput on
declare
begin
dbms_output.enable(100000);
for c_1 in (select * from t_data where rownum < 5) loop
dbms_output.put_line('insert into t_data values');
dbms_output.put_line('('||c_1.object_id||');');
end loop;
end;
/
2.使用java
/**
* 根据输入的sql生成insert脚本 created by husthxd 2005-03-03 v0.1
* @param conn
* @param sqlstmt
* @param uri 输出的文件名称
* @return
* @throws GFPortalException
*/
public boolean createSqlFile(Connection conn, String sqlstmt, String tablename,String uri) throws GFPortalException {
//执行语句
PreparedStatement pstmt;
ResultSet rs = null;
//连接
try {
//获取sql语句
pstmt =
conn.prepareStatement(sqlstmt,
ResultSet.TYPE_SCROLL_INSENSITIVE,
ResultSet.CONCUR_READ_ONLY);
} catch (Exception e) {
logger.debug("查询初始化失败!" + e);
throw new GFPortalException("查询初始化失败!" + e);
}
//获取结果集
try {
rs = pstmt.executeQuery(); //结果集
ResultSetMetaData rsmd = rs.getMetaData(); //元数据
PrintWriter out = new PrintWriter(new FileWriter(uri), true);
while (rs.next()) {
StringBuffer row = new StringBuffer();
row.append("insert into " + tablename + " values(");
int count = rsmd.getColumnCount();
//获取其中的一条记录
for (int i = 1; i <= count; i++) {
//放入到文件中
if (!CommonUtility.isNull(rs.getObject(i)))//判断是否为null
{
switch (rsmd.getColumnType(i)) {
case Types.BIT:
case Types.INTEGER:
case Types.TINYINT:
case Types.BIGINT:
case Types.REAL:
case Types.FLOAT:
case Types.DOUBLE:
case Types.NUMERIC:
case Types.DECIMAL:
case Types.LONGVARBINARY:
case Types.VARBINARY:
case Types.BINARY:
row.append(rs.getObject(i));
break;
case Types.BLOB:
case Types.CLOB:
break;
case Types.DATE:
case Types.TIME:
case Types.TIMESTAMP:
case Types.NULL:
row.append("to_date('");
row.append(CommonUtility.getYMDHMM(rs.getDate(i)));
row.append("'");
row.append(",'yyyy-mm-dd hh24:mi:ss')");
break;
case Types.LONGVARCHAR:
case Types.CHAR:
case Types.VARCHAR:
case Types.OTHER:
default:
row.append("'");
row.append(rs.getString(i));
row.append("'");
break;
}//end switch
} else {
row.append("''");
}
if (i == count)//判断是否最后一列
{
row.append(");");
} else {
row.append(",");
}
}
//完成一行
out.println(row.toString());
//释放资源
row = null;
}
} catch (Exception e) {
logger.debug("查询出错!" + e);
throw new GFPortalException("查询出错!" + e.getMessage());
} finally {
try {
ConnMgr.closePreparedStatement(pstmt);
rs.close();
pstmt.close();
} catch (Exception e2) {
}
}
return true;
}
来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/6906/viewspace-21715/,如需转载,请注明出处,否则将追究法律责任。
转载于:http://blog.itpub.net/6906/viewspace-21715/