查看: 6820|回复: 9

利用存储过程将表中数据导出到.txt文件

[复制链接]
论坛徽章:
41
管理团队成员
日期:2011-05-07 01:45:08紫蛋头
日期:2012-12-25 09:08:28鲜花蛋
日期:2013-01-27 22:27:23复活蛋
日期:2013-02-16 22:42:432013年新春福章
日期:2013-02-25 14:51:24一汽
日期:2013-08-16 09:20:30阿斯顿马丁
日期:2013-08-28 00:23:21红旗
日期:2013-09-27 21:21:05马上有车
日期:2014-02-19 11:55:14马上有房
日期:2014-02-19 11:55:14
发表于 2006-5-7 08:51 | 显示全部楼层 |阅读模式
看昨天论坛里有一个人问的,给写的一个例子,这里边假设表中只有字符型,数值型,日期型数据了,有投机取巧的嫌疑

--*********************************************************************
--***
--***首先以dba或者是其他具有create directory的用户创建directory,然后授权
--***
--*********************************************************************
CREATE OR REPLACE DIRECTORY dir_jiang AS 'd:\oracle';
GRANT read,write ON directory dir_jiang TO scott;

--*********************************************************************
--***
--***执行导出文本文件的存储过程:p_exp
--***p_tname:要导出的表明;p_query:where条件,根据这个条件导出,如果为null,则全表导出
--***
--***
--*********************************************************************
create or replace procedure p_exp(p_tname varchar2,p_query varchar2)
as
  v_line   varchar2(2000);                       --记录的数据
  v_sql    varchar2(2000);                       --动态sql变量
  v_fname  varchar2(40);                         --导出数据的文件名字
  cursor cursor_column                           --游标:通过数据字典查找出表有哪些字段
         is select column_name,data_type
         from user_tab_columns where table_name=upper(p_tname);
  type type_cursor is ref cursor ;
  mycursor type_cursor;
  outf utl_file.file_type;
  
begin
  v_fname:=p_tname||'.txt';
  for col in cursor_column loop
     if col.data_type='DATE' then
          v_sql:=v_sql||'to_char('||col.column_name||',''yyyy-mm-dd-hh24:mi:ss'')'||'||chr(9)||';
     else
          v_sql:=v_sql||col.column_name||'||chr(9)||';
     end if;
  end loop;
  v_sql:=rtrim(v_sql,'||chr(9)||');
  v_sql:='select '||v_sql||' from '||p_tname||' '||p_query;
  dbms_output.put_line(v_sql);
  outf:=utl_file.fopen('DIR_JIANG',v_fname,'w');
  open mycursor for v_sql;
  loop
    fetch mycursor into v_line;     
    exit when mycursor%notfound;
    utl_file.put_line(outf,v_line);
  end loop;
  utl_file.fclose(outf);
  dbms_output.put_line('导出成功');
end;
论坛徽章:
41
管理团队成员
日期:2011-05-07 01:45:08紫蛋头
日期:2012-12-25 09:08:28鲜花蛋
日期:2013-01-27 22:27:23复活蛋
日期:2013-02-16 22:42:432013年新春福章
日期:2013-02-25 14:51:24一汽
日期:2013-08-16 09:20:30阿斯顿马丁
日期:2013-08-28 00:23:21红旗
日期:2013-09-27 21:21:05马上有车
日期:2014-02-19 11:55:14马上有房
日期:2014-02-19 11:55:14
 楼主| 发表于 2006-5-7 08:55 | 显示全部楼层

Re: 利用存储过程将表中数据导出到.txt文件

最初由 skyjiang 发布
[B]看昨天论坛里有一个人问的,给写的一个例子,这里边假设表中只有字符型,数值型,日期型数据了,有投机取巧的嫌疑

--*********************************************************************
--***
--***首先以dba或者是其他具有create directory的用户创建directory,然后授权
--***
--*********************************************************************
CREATE OR REPLACE DIRECTORY dir_jiang AS 'd:\oracle';
GRANT read,write ON directory dir_jiang TO scott;

--*********************************************************************
--***
--***执行导出文本文件的存储过程:p_exp
--***p_tname:要导出的表明;p_query:where条件,根据这个条件导出,如果为null,则全表导出
--***
--***
--*********************************************************************
create or replace procedure p_exp(p_tname varchar2,p_query varchar2)
as
  v_line   varchar2(2000);                       --记录的数据
  v_sql    varchar2(2000);                       --动态sql变量
  v_fname  varchar2(40);                         --导出数据的文件名字
  cursor cursor_column                           --游标:通过数据字典查找出表有哪些字段
         is select column_name,data_type
         from user_tab_columns where table_name=upper(p_tname);
  type type_cursor is ref cursor ;
  mycursor type_cursor;
  outf utl_file.file_type;
  
begin
  v_fname:=p_tname||'.txt';
  for col in cursor_column loop
     if col.data_type='DATE' then
          v_sql:=v_sql||'to_char('||col.column_name||',''yyyy-mm-dd-hh24:mi:ss'')'||'||chr(9)||';
     else
          v_sql:=v_sql||col.column_name||'||chr(9)||';
     end if;
  end loop;
  v_sql:=rtrim(v_sql,'||chr(9)||');
  v_sql:='select '||v_sql||' from '||p_tname||' '||p_query;
  dbms_output.put_line(v_sql);
  outf:=utl_file.fopen('DIR_JIANG',v_fname,'w');
  open mycursor for v_sql;
  loop
    fetch mycursor into v_line;     
    exit when mycursor%notfound;
    utl_file.put_line(outf,v_line);
  end loop;
  utl_file.fclose(outf);
  dbms_output.put_line('导出成功');
end; [/B]


我这也算是一稿双投,其一是想让有兴趣的朋友们帮助改改,改成一个更通用的例子,其二,也是多混点积分 ,一稿双投完还不算,自己没啥事情在上来回回自己的帖子,据说这样长分快。
向这个哥们致敬:啥原因呢?看人家回帖子这个速度
lastwinner
路边野花不要,踩!

使用道具 举报

回复
论坛徽章:
47
马上加薪
日期:2014-02-19 11:55:142011新春纪念徽章
日期:2011-01-25 15:42:332011新春纪念徽章
日期:2011-01-25 15:42:152011新春纪念徽章
日期:2011-01-25 15:41:502011新春纪念徽章
日期:2011-01-25 15:41:012010新春纪念徽章
日期:2010-03-01 11:20:512010年世界杯参赛球队:日本
日期:2010-02-26 11:04:222010新春纪念徽章
日期:2010-01-04 08:33:08祖国60周年纪念徽章
日期:2009-10-09 08:28:00生肖徽章2007版:牛
日期:2009-09-10 11:14:59
发表于 2006-5-7 18:52 | 显示全部楼层

tom kyte 写过

http://asktom.oracle.com/~tkyte/flat/index.html.
create or replace function  dump_csv( p_query     in varchar2,
                                      p_separator in varchar2
                                                    default ',',
                                      p_dir       in varchar2 ,
                                      p_filename  in varchar2 )
return number
AUTHID CURRENT_USER
is
    l_output        utl_file.file_type;
    l_theCursor     integer default dbms_sql.open_cursor;
    l_columnValue   varchar2(2000);
    l_status        integer;
    l_colCnt        number default 0;
    l_separator     varchar2(10) default '';
    l_cnt           number default 0;
begin
    l_output := utl_file.fopen( p_dir, p_filename, 'w' );

    dbms_sql.parse(  l_theCursor,  p_query, dbms_sql.native );

    for i in 1 .. 255 loop
        begin
            dbms_sql.define_column( l_theCursor, i,
                                    l_columnValue, 2000 );
            l_colCnt := i;
        exception
            when others then
                if ( sqlcode = -1007 ) then exit;
                else
                    raise;
                end if;
        end;
    end loop;

    dbms_sql.define_column( l_theCursor, 1, l_columnValue,
                            2000 );

    l_status := dbms_sql.execute(l_theCursor);

    loop
        exit when ( dbms_sql.fetch_rows(l_theCursor) <= 0 );
        l_separator := '';
        for i in 1 .. l_colCnt loop
            dbms_sql.column_value( l_theCursor, i,
                                   l_columnValue );
            utl_file.put( l_output, l_separator ||  
                                    l_columnValue );
            l_separator := p_separator;
        end loop;
        utl_file.new_line( l_output );
        l_cnt := l_cnt+1;
    end loop;
    dbms_sql.close_cursor(l_theCursor);

    utl_file.fclose( l_output );
    return l_cnt;
end dump_csv;
/


You would use that for example like this:

create or replace procedure test_dump_csv
as
    l_rows  number;
begin
    l_rows := dump_csv( 'select *
                           from all_users
                           where rownum < 25',
                        ',', '/tmp', 'test.dat' );
end;
/


Make sure to read about the INIT.ORA parameters (utl_file_dir) you need to setup
!!!
在他的
Expert Oracle Database
Architecture
9iand 10g Programming
Techniques and Solutions
最后一章也讲了
Flat File Unload
One thing SQLLDR does not do, and that Oracle supplies no command-line tools for, is unload
data in a format understandable by SQLLDR or other programs. This would be useful for mov-
ing data from system to system without using EXP/IMPor EXPDP/IMPDP(the new Data Pump
replacements for EXPand IMP). Using EXP(DP)/IMP(DP)to move data from system to system
works fine—as long as both systems are Oracle.
■Note HTML DB provides a data export feature as part of its SQL Workshop.You may export the informa-
tion in a CSV format easily.This works well for a few megabytes of information,but it is not appropriate for
many tens of megabytes or more.
We will develop a small PL/SQL utility that may be used to unload data on a server in a
SQLLDR-friendly format. Also, equivalent tools for doing so in Pro*C and SQL*Plus are provided
on the Ask Tom web site at http://asktom.oracle.com/~tkyte/flat/index.html. The PL/SQL
utility will work fine in most small cases, but better performance will be had using Pro*C.
Pro*C and SQL*Plus are also useful if you need the files to be generated on the client and not
on the server, which is where PL/SQL will create them.
The specification of the package we will create is as follows:
ops$tkyte@ORA10G> create or replace package unloader
2  AUTHID CURRENT_USER
3  as
4  /* Function run -- unloads data from any query into a file
5                     and creates a control file to reload that
6                     data into another table
7
8      p_query      = SQL query to "unload".  May be virtually any query.
9      p_tname      = Table to load into.  Will be put into control file.
10      p_mode       = REPLACE|APPEND|TRUNCATE -- how to reload the data
11      p_dir        = directory we will write the ctl and dat file to.
12      p_filename   = name of file to write to.  I will add .ctl and .dat
13                     to this name
14      p_separator  = field delimiter.  I default this to a comma.
15      p_enclosure  = what each field will be wrapped in
16      p_terminator = end of line character.  We use this so we can unload
17                and reload data with newlines in it.  I default to
18               "|\n" (a pipe and a newline together) and "|\r\n" on NT.
19                You need only to override this if you believe your
20                data will have that sequence in it. I ALWAYS add the
21                OS "end of line" marker to this sequence, you should not
22      */
23      function run( p_query     in varchar2,
24                    p_tname     in varchar2,
25                    p_mode      in varchar2 default 'REPLACE',
26                    p_dir       in varchar2,
27                    p_filename  in varchar2,
28                    p_separator in varchar2 default ',',
29                    p_enclosure in varchar2 default '"',
30                    p_terminator in varchar2 default '|' )
31      return number;
32  end;
33  /
Package created.
Note the use of AUTHID CURRENT_USER. This permits this package to be installed oncein a
database and used by anyone to unload data. All the person needs is SELECTprivileges on the
table(s) he wants to unload and EXECUTEprivileges on this package. If we did not use AUTHID
CURRENT_USERin this case, then the owner of this package would need direct SELECTprivileges
on all tables to be unloaded.
■Note The SQL will execute with the privileges of the invoker of this routine.However,all PL/SQL calls will
run with the privileges of the definerof the called routine; therefore,the ability to use
UTL_FILE to write to a
directory is implicitly given to anyone with execute permission on this package.

使用道具 举报

回复
论坛徽章:
41
管理团队成员
日期:2011-05-07 01:45:08紫蛋头
日期:2012-12-25 09:08:28鲜花蛋
日期:2013-01-27 22:27:23复活蛋
日期:2013-02-16 22:42:432013年新春福章
日期:2013-02-25 14:51:24一汽
日期:2013-08-16 09:20:30阿斯顿马丁
日期:2013-08-28 00:23:21红旗
日期:2013-09-27 21:21:05马上有车
日期:2014-02-19 11:55:14马上有房
日期:2014-02-19 11:55:14
 楼主| 发表于 2006-5-8 10:05 | 显示全部楼层

Re: tom kyte 写过

最初由 hotiice 发布
[B]http://asktom.oracle.com/~tkyte/flat/index.html.
create or replace function  dump_csv( p_query     in varchar2,
                                      p_separator in varchar2
                                                    default ',',
                                      p_dir       in varchar2 ,
                                      p_filename  in varchar2 )
return number
AUTHID CURRENT_USER
is
    l_output        utl_file.file_type;
    l_theCursor     integer default dbms_sql.open_cursor;
    l_columnValue   varchar2(2000);
    l_status        integer;
    l_colCnt        number default 0;
    l_separator     varchar2(10) default '';
    l_cnt           number default 0;
begin
    l_output := utl_file.fopen( p_dir, p_filename, 'w' );

    dbms_sql.parse(  l_theCursor,  p_query, dbms_sql.native );

    for i in 1 .. 255 loop
        begin
            dbms_sql.define_column( l_theCursor, i,
                                    l_columnValue, 2000 );
            l_colCnt := i;
        exception
            when others then
                if ( sqlcode = -1007 ) then exit;
                else
                    raise;
                end if;
        end;
    end loop;

    dbms_sql.define_column( l_theCursor, 1, l_columnValue,
                            2000 );

    l_status := dbms_sql.execute(l_theCursor);

    loop
        exit when ( dbms_sql.fetch_rows(l_theCursor) <= 0 );
        l_separator := '';
        for i in 1 .. l_colCnt loop
            dbms_sql.column_value( l_theCursor, i,
                                   l_columnValue );
            utl_file.put( l_output, l_separator ||  
                                    l_columnValue );
            l_separator := p_separator;
        end loop;
        utl_file.new_line( l_output );
        l_cnt := l_cnt+1;
    end loop;
    dbms_sql.close_cursor(l_theCursor);

    utl_file.fclose( l_output );
    return l_cnt;
end dump_csv;
/


You would use that for example like this:

create or replace procedure test_dump_csv
as
    l_rows  number;
begin
    l_rows := dump_csv( 'select *
                           from all_users
                           where rownum < 25',
                        ',', '/tmp', 'test.dat' );
end;
/


Make sure to read about the INIT.ORA parameters (utl_file_dir) you need to setup
!!!
在他的
Expert Oracle Database
Architecture
9iand 10g Programming
Techniques and Solutions
最后一章也讲了
Flat File Unload
One thing SQLLDR does not do, and that Oracle supplies no command-line tools for, is unload
data in a format understandable by SQLLDR or other programs. This would be useful for mov-
ing data from system to system without using EXP/IMPor EXPDP/IMPDP(the new Data Pump
replacements for EXPand IMP). Using EXP(DP)/IMP(DP)to move data from system to system
works fine—as long as both systems are Oracle.
■Note HTML DB provides a data export feature as part of its SQL Workshop.You may export the informa-
tion in a CSV format easily.This works well for a few megabytes of information,but it is not appropriate for
many tens of megabytes or more.
We will develop a small PL/SQL utility that may be used to unload data on a server in a
SQLLDR-friendly format. Also, equivalent tools for doing so in Pro*C and SQL*Plus are provided
on the Ask Tom web site at http://asktom.oracle.com/~tkyte/flat/index.html. The PL/SQL
utility will work fine in most small cases, but better performance will be had using Pro*C.
Pro*C and SQL*Plus are also useful if you need the files to be generated on the client and not
on the server, which is where PL/SQL will create them.
The specification of the package we will create is as follows:
ops$tkyte@ORA10G> create or replace package unloader
2  AUTHID CURRENT_USER
3  as
4  /* Function run -- unloads data from any query into a file
5                     and creates a control file to reload that
6                     data into another table
7
8      p_query      = SQL query to "unload".  May be virtually any query.
9      p_tname      = Table to load into.  Will be put into control file.
10      p_mode       = REPLACE|APPEND|TRUNCATE -- how to reload the data
11      p_dir        = directory we will write the ctl and dat file to.
12      p_filename   = name of file to write to.  I will add .ctl and .dat
13                     to this name
14      p_separator  = field delimiter.  I default this to a comma.
15      p_enclosure  = what each field will be wrapped in
16      p_terminator = end of line character.  We use this so we can unload
17                and reload data with newlines in it.  I default to
18               "|\n" (a pipe and a newline together) and "|\r\n" on NT.
19                You need only to override this if you believe your
20                data will have that sequence in it. I ALWAYS add the
21                OS "end of line" marker to this sequence, you should not
22      */
23      function run( p_query     in varchar2,
24                    p_tname     in varchar2,
25                    p_mode      in varchar2 default 'REPLACE',
26                    p_dir       in varchar2,
27                    p_filename  in varchar2,
28                    p_separator in varchar2 default ',',
29                    p_enclosure in varchar2 default '"',
30                    p_terminator in varchar2 default '|' )
31      return number;
32  end;
33  /
Package created.
Note the use of AUTHID CURRENT_USER. This permits this package to be installed oncein a
database and used by anyone to unload data. All the person needs is SELECTprivileges on the
table(s) he wants to unload and EXECUTEprivileges on this package. If we did not use AUTHID
CURRENT_USERin this case, then the owner of this package would need direct SELECTprivileges
on all tables to be unloaded.
■Note The SQL will execute with the privileges of the invoker of this routine.However,all PL/SQL calls will
run with the privileges of the definerof the called routine; therefore,the ability to use
UTL_FILE to write to a
directory is implicitly given to anyone with execute permission on this package. [/B]


学习啊

使用道具 举报

回复
论坛徽章:
0
发表于 2006-5-8 10:53 | 显示全部楼层

咋回事阿!!!

我想试试,结果不能赋予写权限,不知为什么?
SQL> grant write on directory dir_jiang to scott;
grant write on directory dir_jiang to scott
      *
ERROR at line 1:
ORA-22928: invalid privilege on directories

使用道具 举报

回复
论坛徽章:
0
发表于 2006-5-8 11:33 | 显示全部楼层

大侠有心了

昨天我问的,不过是反方向的,主要是想知道在导入flat file到oracle数据库时候的data cleansing、error handling、及具体loading实现时的一些性能考虑。

比如我有一个flat file如下:
000EMP, SALES, 20060222
New York,        Shelby,                Manhattan,        Paul,                Young,                Paul Young,                ,        American Bank of New York,        1/1/2005 11:23:31 AM
Florida,        Jefferson,        Panama City,        Chris,                Davis,                Chris Davis,        Paul Young,        American Bank of Florida,        1/1/2005 11:23:31 AM
California,        Montgomery,        San Hose,        Louis,                Johnson,        Louis Johnson,        Paul Young,        American Bank of California,        1/1/2005 11:23:31 AM
New Jersey,        Hudson,                Jersey City,        Sam,                Mathew,                Sam Mathew,        Paul Young,        American Bank of New Jersey,        1/1/2005 11:23:31 AM
New York,        Shelby,                Manhattan,        Nancy,                Robinson,        Nancy Robinson,        Paul Young,        American Bank of New York,        1/1/2005 11:23:31 AM
Florida,        Jefferson,        Panama City,        Sheela,                Shellum,        Sheela Shellum,        Chris Davis,        American Bank of Florida,        1/1/2005 11:23:31 AM
California,        Montgomery,        Shelby,                Jeff,                Bill,                Jeff Bill,        Louis Johnson,        American Bank of California,        1/1/2005 11:23:31 AM
New Jersey,        Hudson,                Jersey City,        John,                Burrell,        John Burrell,        Sam Mathew,        American Bank of New Jersey,        1/1/2005 11:23:31 AM
999      8
想把它倒到诸如地区表、职员表等等

以上仅仅是个例子,想知道怎么做的,过程中使用cursor怎样才能更有效率等,也许我说得过于笼统了,其实我就想要个现实中实现flat file导入的例子。
sql loader部分基本搞明白了,主要是存储过程的实现不是很清楚

无论如何都谢谢楼主了!

使用道具 举报

回复
论坛徽章:
41
管理团队成员
日期:2011-05-07 01:45:08紫蛋头
日期:2012-12-25 09:08:28鲜花蛋
日期:2013-01-27 22:27:23复活蛋
日期:2013-02-16 22:42:432013年新春福章
日期:2013-02-25 14:51:24一汽
日期:2013-08-16 09:20:30阿斯顿马丁
日期:2013-08-28 00:23:21红旗
日期:2013-09-27 21:21:05马上有车
日期:2014-02-19 11:55:14马上有房
日期:2014-02-19 11:55:14
 楼主| 发表于 2006-5-8 12:54 | 显示全部楼层

Re: 大侠有心了

最初由 BigPants 发布
[B]昨天我问的,不过是反方向的,主要是想知道在导入flat file到oracle数据库时候的data cleansing、error handling、及具体loading实现时的一些性能考虑。

比如我有一个flat file如下:
000EMP, SALES, 20060222
New York,        Shelby,                Manhattan,        Paul,                Young,                Paul Young,                ,        American Bank of New York,        1/1/2005 11:23:31 AM
Florida,        Jefferson,        Panama City,        Chris,                Davis,                Chris Davis,        Paul Young,        American Bank of Florida,        1/1/2005 11:23:31 AM
California,        Montgomery,        San Hose,        Louis,                Johnson,        Louis Johnson,        Paul Young,        American Bank of California,        1/1/2005 11:23:31 AM
New Jersey,        Hudson,                Jersey City,        Sam,                Mathew,                Sam Mathew,        Paul Young,        American Bank of New Jersey,        1/1/2005 11:23:31 AM
New York,        Shelby,                Manhattan,        Nancy,                Robinson,        Nancy Robinson,        Paul Young,        American Bank of New York,        1/1/2005 11:23:31 AM
Florida,        Jefferson,        Panama City,        Sheela,                Shellum,        Sheela Shellum,        Chris Davis,        American Bank of Florida,        1/1/2005 11:23:31 AM
California,        Montgomery,        Shelby,                Jeff,                Bill,                Jeff Bill,        Louis Johnson,        American Bank of California,        1/1/2005 11:23:31 AM
New Jersey,        Hudson,                Jersey City,        John,                Burrell,        John Burrell,        Sam Mathew,        American Bank of New Jersey,        1/1/2005 11:23:31 AM
999      8
想把它倒到诸如地区表、职员表等等

以上仅仅是个例子,想知道怎么做的,过程中使用cursor怎样才能更有效率等,也许我说得过于笼统了,其实我就想要个现实中实现flat file导入的例子。
sql loader部分基本搞明白了,主要是存储过程的实现不是很清楚

无论如何都谢谢楼主了! [/B]


读文件中的数据要调用utl_file.GET_LINE的.你先自己写吧,我下午
给你完善以下

使用道具 举报

回复
论坛徽章:
41
管理团队成员
日期:2011-05-07 01:45:08紫蛋头
日期:2012-12-25 09:08:28鲜花蛋
日期:2013-01-27 22:27:23复活蛋
日期:2013-02-16 22:42:432013年新春福章
日期:2013-02-25 14:51:24一汽
日期:2013-08-16 09:20:30阿斯顿马丁
日期:2013-08-28 00:23:21红旗
日期:2013-09-27 21:21:05马上有车
日期:2014-02-19 11:55:14马上有房
日期:2014-02-19 11:55:14
 楼主| 发表于 2006-5-8 13:30 | 显示全部楼层

Re: 大侠有心了

最初由 BigPants 发布
[B]昨天我问的,不过是反方向的,主要是想知道在导入flat file到oracle数据库时候的data cleansing、error handling、及具体loading实现时的一些性能考虑。

比如我有一个flat file如下:
000EMP, SALES, 20060222
New York,        Shelby,                Manhattan,        Paul,                Young,                Paul Young,                ,        American Bank of New York,        1/1/2005 11:23:31 AM
Florida,        Jefferson,        Panama City,        Chris,                Davis,                Chris Davis,        Paul Young,        American Bank of Florida,        1/1/2005 11:23:31 AM
California,        Montgomery,        San Hose,        Louis,                Johnson,        Louis Johnson,        Paul Young,        American Bank of California,        1/1/2005 11:23:31 AM
New Jersey,        Hudson,                Jersey City,        Sam,                Mathew,                Sam Mathew,        Paul Young,        American Bank of New Jersey,        1/1/2005 11:23:31 AM
New York,        Shelby,                Manhattan,        Nancy,                Robinson,        Nancy Robinson,        Paul Young,        American Bank of New York,        1/1/2005 11:23:31 AM
Florida,        Jefferson,        Panama City,        Sheela,                Shellum,        Sheela Shellum,        Chris Davis,        American Bank of Florida,        1/1/2005 11:23:31 AM
California,        Montgomery,        Shelby,                Jeff,                Bill,                Jeff Bill,        Louis Johnson,        American Bank of California,        1/1/2005 11:23:31 AM
New Jersey,        Hudson,                Jersey City,        John,                Burrell,        John Burrell,        Sam Mathew,        American Bank of New Jersey,        1/1/2005 11:23:31 AM
999      8
想把它倒到诸如地区表、职员表等等

以上仅仅是个例子,想知道怎么做的,过程中使用cursor怎样才能更有效率等,也许我说得过于笼统了,其实我就想要个现实中实现flat file导入的例子。
sql loader部分基本搞明白了,主要是存储过程的实现不是很清楚

无论如何都谢谢楼主了! [/B]




=======================================================================================================
--*********************************************************************
--***
--***首先以dba或者是其他具有create directory的用户创建directory,然后授权
--***
--*********************************************************************
alter system set utl_file_dir='c:\oracle' scope=spfile;
shutdown immediate;
startup;

connect scott/tiger;

SQL> create or replace procedure p_imp(p_fname varchar2)
  2  as
  3    v_line   varchar2(2000);                       --记录的数据
  4    v_sql    varchar2(2000);                       --动态sql变量
  5    v_tname  varchar2(40);                         --表名字
  6      
  7    inf utl_file.file_type;
  8   
  9    v_deptno integer;
10    v_dname  varchar2(20);
11    v_loc    varchar2(20);  
12  begin
13    v_tname:=substr(p_fname,1,instr(p_fname,'.')-1) ;     --截取表的名字
14    inf:=utl_file.fopen('c:\oracle',p_fname,'r');
15    loop
16      begin
17         utl_file.get_line(inf,v_line);
18          dbms_output.put_line(v_line);
19      exception
20       when no_data_found   then exit ;
21      end;
22    end loop;
23    utl_file.fclose(inf);
24    dbms_output.put_line('写入成功');
25  end p_imp;
26  /

过程已创建。

SQL> set serveroutput on;
SQL> exec p_imp('jiang.txt');
10 ACCOUNTING     NEW YORK
20 RESEARCH       DALLAS
30 SALES          CHICAGO
40 OPERATIONS     BOSTON
写入成功

PL/SQL 过程已成功完成。

SQL>

使用道具 举报

回复
论坛徽章:
0
发表于 2007-4-26 16:07 | 显示全部楼层

有些地方不是很清楚

alter system set utl_file_dir='c:\oracle' scope=spfile;
shutdown immediate;
startup;

connect scott/tiger;

这一段是在哪里执行的?

还有表名是不是在填在 13 v_tname:=substr(p_fname,1,instr(p_fname,'.')-1) ;  这一句的 '.'  处?

希望加下我QQ:183222305,我好向大侠讨教讨教
最近刚刚好碰上做这个东西.

使用道具 举报

回复
论坛徽章:
1
蛋疼蛋
日期:2011-12-03 21:13:33
发表于 2007-8-15 15:22 | 显示全部楼层
要把select id||','||name from testtbl输出到txt文件,怎么改存储过程?或者有没有其他好方法?谢谢

使用道具 举报

回复

您需要登录后才可以回帖 登录 | 注册

本版积分规则 发表回复

TOP技术积分榜 社区积分榜 徽章 团队 统计 知识索引树 积分竞拍 文本模式 帮助
  ITPUB首页 | ITPUB论坛 | 数据库技术 | 企业信息化 | 开发技术 | 微软技术 | 软件工程与项目管理 | IBM技术园地 | 行业纵向讨论 | IT招聘 | IT文档
  ChinaUnix | ChinaUnix博客 | ChinaUnix论坛
CopyRight 1999-2011 itpub.net All Right Reserved. 北京盛拓优讯信息技术有限公司版权所有 联系我们 
京ICP备09055130号-4  北京市公安局海淀分局网监中心备案编号:11010802021510 广播电视节目制作经营许可证:编号(京)字第1149号
  
快速回复 返回顶部 返回列表