生成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;
    }

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/6906/viewspace-21715/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/6906/viewspace-21715/