当前位置导航:炫浪网>>网络学院>>编程开发>>Oracle教程

把oracle表里的数据导成insert语句


  Internet上还有一种免费的MYSQL数据库很流行。有些时候我们需要把oracle里的数据导入MYSQL里。
  
  生成insert into 表名 .... 是一种很简单直接的方法。
  
  今年六月份从www.arikaplan.com/oracle.html看到一个可以生成insert into 表名 ....语句的存储过程genins_output。
  
  我按中文习惯的时间格式YYYY-MM-DD HH24:MI:SS改了改,并新写了一个存储过程genins_file.sql。
  
  它可以把小于16383条记录表里的数据导成(insert into 表名 ....)OS下文件。
  
  调用它之前,DBA要看看数据库的初始化参数 UTL_FILE_DIR 是否已经正确地设置:
  
  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)
  
      SQL> exit
  
  可以在OS目录/oracle/logs下看到insert_emp.sql文件。
  
  注意事项: 生成(insert into 表名 ....)OS下文件最多32767行。
  
  因为我一条insert分成两行,所以最多处理16383条记录的表。
  
  在MYSQL数据库里运行insert_emp.sql,就可以方便地把oracle数据转移到MYSQL数据库里。
  
  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;
  /
相关内容
赞助商链接