用存储过程把表里的数据导成insert语句

上一篇 / 下一篇 2008-06-11 21:25:04 / 个人分类：pl/sql

SQL> show parameters utl_file_dir;

可以看到该参数的当前设置。

如果没有值，必须修改数据库的initsid.ora文件，将utl_file_dir 指向一个你想用PL/SQL file I/O 的路径。
重新启动数据库。此参数才生效。

调用它，可以把表里的数据生成(insert into 表名 ....)OS下文件的过程genins_file方法:

        SQL>exec genins_file('emp','/oracle/logs','insert_emp.sql');
                                            |         |                |
                                       表名,可变     |         生成OS下文件名,可变
                                         |
                                utl_file_dir路径名,不变(我设置的是/oracle/logs)

存储过程原码
CREATE OR REPLACE PROCEDURE genins_file(
p_table  IN varchar2,
p_output_folder IN VARCHAR2,
p_output_file   IN VARCHAR2)
IS
  --
  l_column_list    VARCHAR2(32767);
  l_value_list     VARCHAR2(32767);
  l_query          VARCHAR2(32767);
  l_cursor         NUMBER;
  ignore   NUMBER;
  l_insertline1    varchar2(32767);
  l_insertline2    varchar2(32767);
  cmn_file_handle       UTL_FILE.file_type;
  --
  FUNCTION get_cols(p_table VARCHAR2)
  RETURN VARCHAR2
  IS
    l_cols VARCHAR2(32767);
    CURSOR l_col_cur(c_table VARCHAR2) IS
            SELECT column_name
            FROM   user_tab_columns
            WHERE  table_name = upper(c_table)
            ORDER BY column_id;
  BEGIN
    l_cols := null;
    FOR rec IN l_col_cur(p_table)
    LOOP
      l_cols := l_cols || rec.column_name || ',';
    END LOOP;
    RETURN substr(l_cols,1,length(l_cols)-1);
  END;
  --
  FUNCTION get_query(p_table IN VARCHAR2)
  RETURN VARCHAR2
  IS
    l_query VARCHAR2(32767);
      CURSOR l_query_cur(c_table VARCHAR2) IS
        SELECT 'decode('||column_name||',null,''null'','||
               decode(data_type,'VARCHAR2','''''''''||'||column_name ||'||'''''''''
               ,'DATE','''''''''||to_char('||column_name||',''YYYY-MM-DD HH24:MI:SS'')||'''''''''
               ,column_name
               ) || ')' column_query
          FROM user_tab_columns
         WHERE table_name = upper(c_table)
        ORDER BY column_id;
  BEGIN
    l_query := 'SELECT ';
    FOR rec IN l_query_cur(p_table)
    LOOP
      l_query := l_query || rec.column_query || '||'',''||';
    END LOOP;
    l_query := substr(l_query,1,length(l_query)-7);
    RETURN l_query || ' FROM ' || p_table;
  END;
  --
BEGIN
  l_column_list  := get_cols(p_table);
  l_query        := get_query(p_table);
  l_cursor := dbms_sql.open_cursor;
  DBMS_SQL.PARSE(l_cursor, l_query, DBMS_SQL.native);
  DBMS_SQL.DEFINE_COLUMN(l_cursor, 1, l_value_list, 32767);
  ignore := DBMS_SQL.EXECUTE(l_cursor);
  --
  IF NOT UTL_FILE.IS_OPEN(cmn_file_handle) THEN
        cmn_file_handle := UTL_FILE.FOPEN (p_output_folder, p_output_file, 'a',32767);
  END IF;

  LOOP
    IF DBMS_SQL.FETCH_ROWS(l_cursor)>0 THEN
      DBMS_SQL.COLUMN_VALUE(l_cursor, 1, l_value_list);
      l_insertline1:='INSERT INTO '||p_table||' ('||l_column_list||')';
      l_insertline2:=' VALUES ('||l_value_list||');';
      UTL_FILE.put_line (cmn_file_handle, l_insertline1);
      UTL_FILE.put_line (cmn_file_handle, l_insertline2);
    ELSE
      EXIT;
    END IF;
  END LOOP;
  IF NOT UTL_FILE.IS_OPEN(cmn_file_handle) THEN
     UTL_FILE.FCLOSE (cmn_file_handle);
  END IF;
END;
/

导入论坛引用链接收藏分享给好友推荐到圈子管理举报

TAG:

查看全部评论