linux执行insert脚本,生成insert脚本的脚本

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;

}