`

平面数据的导出与导入

阅读更多
平面数据的导出与导入_1


较少数据的导出

1、	proc
/*
** 程序名称  load_acctlist.pc
** 用途:将外部文件数据加载到数据库
*/
#include <stdio.h>
#include <string.h>
#include <ctype.h>

#define MAX_VNAME_LEN     30
#define MAX_INAME_LEN     30

static char *     USERID = NULL;
static char *   SQLSTMT = NULL;
static char *   ARRAY_SIZE = "10";

#define vstrcpy( a, b ) \
(strcpy( a.arr, b ), a.len = strlen( a.arr ), a.arr)

EXEC SQL INCLUDE sqlca;
EXEC SQL INCLUDE sqlda;

extern SQLDA *sqlald();
extern void   sqlclu();



static void die( char * msg )
{
    fprintf( stderr, "%s\n", msg );
    exit(1);
}


/*
    this array contains a default mapping
    I am using to constrain the
       lengths of returned columns.  It is mapping,
    for example, the Oracle
       NUMBER type (type code = 2) to be 45 characters
    long in a string.
*/

static int lengths[] =
{ -1, 0, 45, 0, 0, 0, 0, 0, 2000, 0, 0,
 18, 25, 0, 0, 0, 0, 0, 0, 0, 0,
  0, 0, 512, 2000 };


static void process_parms( argc, argv )
int    argc;
char *    argv[];
{
int    i;

    for( i = 1; i < argc; i++ )
    {
        if ( !strncmp( argv[i], "userid=", 7 ) )
              USERID = argv[i]+7;
        else
        if ( !strncmp( argv[i], "sqlstmt=", 8 ) )
              SQLSTMT = argv[i]+8;
        else
        if ( !strncmp( argv[i], "arraysize=", 10 ) )
              ARRAY_SIZE = argv[i]+10;
        else
        {
            fprintf( stderr,
                    "usage: %s %s %s\n",
                     argv[0],
                    "userid=xxx/xxx sqlstmt=query ",
                    "arraysize=<NN>\n" );
            exit(1);
        }
    }
    if ( USERID == NULL  || SQLSTMT == NULL )
    {
        fprintf( stderr,
                "usage: %s %s %s\n",
                 argv[0],
                "userid=xxx/xxx sqlstmt=query ",
                "arraysize=<NN>\n" );
        exit(1);
    }
}

static void sqlerror_hard()
{
    EXEC SQL WHENEVER SQLERROR CONTINUE;

    fprintf(stderr,"\nORACLE error detected:");
    fprintf(stderr,"\n% .70s \n", sqlca.sqlerrm.sqlerrmc);

    EXEC SQL ROLLBACK WORK RELEASE;
    exit(1);
}



static SQLDA * process_1(char * sqlstmt, int array_size )
{
SQLDA *    select_dp;
int     i;
int        j;
int        null_ok;
int        precision;
int        scale;
int        size = 10;

    fprintf( stderr, "Unloading '%s'\n", sqlstmt );
    fprintf( stderr, "Array size = %d\n", array_size );


    EXEC SQL WHENEVER SQLERROR DO sqlerror_hard();
       EXEC SQL PREPARE S FROM :sqlstmt;
       EXEC SQL DECLARE C CURSOR FOR S;

    if ((select_dp = sqlald(size,MAX_VNAME_LEN,MAX_INAME_LEN))
                   == NULL )
        die( "Cannot allocate  memory for select descriptor." );

    select_dp->N = size;
    EXEC SQL DESCRIBE SELECT LIST FOR S INTO select_dp;
    if ( !select_dp->F ) return NULL;

    if (select_dp->F < 0)
    {
        size = -select_dp->F;
        sqlclu( select_dp );
        if ((select_dp =
                sqlald (size, MAX_VNAME_LEN, MAX_INAME_LEN))
                      == NULL )
        die( "Cannot allocate  memory for descriptor." );
        EXEC SQL DESCRIBE SELECT LIST FOR S INTO select_dp;
    }
    select_dp->N = select_dp->F;

    for (i = 0; i < select_dp->N; i++)
        select_dp->I[i] = (short *) malloc(sizeof(short) *
                                                array_size );

    for (i = 0; i < select_dp->F; i++)
    {
        sqlnul (&(select_dp->T[i]),
                &(select_dp->T[i]), &null_ok);
        if ( select_dp->T[i] <
                     sizeof(lengths)/sizeof(lengths[0]) )
        {
            if ( lengths[select_dp->T[i]] )
                 select_dp->L[i]  = lengths[select_dp->T[i]];
            else select_dp->L[i] += 5;
        }
        else select_dp->L[i] += 5;

        select_dp->T[i] = 5;
        select_dp->V[i] = (char *)malloc( select_dp->L[i] *
                                               array_size );

        for( j = MAX_VNAME_LEN-1;
             j > 0 && select_dp->S[i][j] == ' ';
             j--);
        fprintf (stderr,
                "%s%.*s", i?",":"", j+1, select_dp->S[i]);
    }
    fprintf( stderr, "\n" );


    EXEC SQL OPEN C;
    return select_dp;
}


static void process_2( SQLDA * select_dp, int array_size )
{
int    last_fetch_count;
int        row_count = 0;
short    ind_value;
char    * char_ptr;
int    i,
       j;

    for ( last_fetch_count = 0;
          ;
          last_fetch_count = sqlca.sqlerrd[2] )
    {
        EXEC SQL FOR :array_size FETCH C
                      USING DESCRIPTOR select_dp;

        for( j=0; j < sqlca.sqlerrd[2]-last_fetch_count; j++ )
        {
            for (i = 0; i < select_dp->F; i++)
            {
                ind_value = *(select_dp->I[i]+j);
                char_ptr  = select_dp->V[i] +
                                  (j*select_dp->L[i]);

                printf( "%s%s", i?",":"",
                             ind_value?"(null)":char_ptr );
            }
            row_count++;
            printf( "\n" );
        }
        if ( sqlca.sqlcode > 0 ) break;
    }

    sqlclu(select_dp);

    EXEC SQL CLOSE C;

    EXEC SQL COMMIT WORK;
    fprintf( stderr, "%d rows extracted\n", row_count );
}



main( argc, argv )
int    argc;
char *    argv[];
{
EXEC SQL BEGIN DECLARE SECTION;
VARCHAR   oracleid[50];
EXEC SQL END DECLARE SECTION;
SQLDA    * select_dp;


    process_parms( argc, argv );

    /* Connect to ORACLE. */
    vstrcpy( oracleid, USERID );

    EXEC SQL WHENEVER SQLERROR DO sqlerror_hard();

    EXEC SQL CONNECT :oracleid;
    fprintf(stderr, "\nConnected to ORACLE as user: %s\n\n",
             oracleid.arr);

    EXEC SQL ALTER SESSION
      SET NLS_DATE_FORMAT = 'DD-MON-YYYY HH24:MI:SS';

    select_dp = process_1( SQLSTMT, atoi(ARRAY_SIZE) );
    process_2( select_dp , atoi(ARRAY_SIZE));

    /* Disconnect from ORACLE. */
    EXEC SQL COMMIT WORK RELEASE;
    exit(0);
}
    

2、过程
create or replace function  dump_txt( p_query     in varchar2,
                                      p_separator in varchar2 default ',',
                                      p_dir       in varchar2 ,
                                      p_filename  in varchar2 )
return number
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
    execute immediate 'alter session set nls_date_format=''yyyy-mm-dd hh24:mi:ss''';
    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 ;


create or replace function  dump_txt( p_query     in varchar2,
                                      p_separator in varchar2 default ',',
                                      p_dir       in varchar2 ,
                                      p_filename  in varchar2 )
return number
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
    execute immediate 'alter session set nls_date_format=''yyyy-mm-dd hh24:mi:ss''';
    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 );
            if l_columnValue is not null then
            l_columnValue:='"'||l_columnValue||'"';
            else
            l_columnValue:=nvl(l_columnValue,'NULL');
           end if; 
            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_txt的使用方法

1、建立一个系统文件目录 d:\ex_file
2、建立一个oracle目录,并将读写权限赋予scott用户
conn sys/sys@gx as sysdba
CREATE OR REPLACE DIRECTORY  extxt AS 'D:\ex_file';
grant read,write on directory extxt  to scott;

3、	导出某一个查询的结果
select dump_txt( 'select * from emp',',','EXTXT','emp.txt' )  from dual

emp.txt
5555,ggg,CLERK,7900,2008-07-22 00:00:00,0,,10
7369,SMITH,CLERK,7902,1980-12-17 00:00:00,800,,20
7499,ALLEN,SALESMAN,7698,1981-02-20 00:00:00,1600,300,30
7521,WARD,SALESMAN,7698,1981-02-22 00:00:00,1250,500,30
7566,JONES,MANAGER,7839,1981-04-02 00:00:00,2975,,20
7654,MA & RTIN,SALESMAN,7698,1981-09-28 00:00:00,1250,1400,30
7698,BLAKE,MANAGER,7839,1981-05-01 00:00:00,2850,,30
7782,CLARK,MANAGER,7839,1981-06-09 00:00:00,2450,,10
7788,SCOTT,ANALYST,7566,1987-04-19 00:00:00,3000,,20
7839,KING,PRESIDENT,,1981-11-17 00:00:00,5000,,10
7844,TURNER,SALESMAN,7698,1981-09-08 00:00:00,1500,0,30
7900,JAMES,CLERK,7698,1981-12-03 00:00:00,950,,30
7902,FORD,ANALYST,7566,1981-12-03 00:00:00,3000,,20
7934,MILLER,CLERK,7782,1982-01-23 00:00:00,1300,,10
7876,ADAMS,CLERK,7788,1987-04-23 00:00:00,1100,,20


数据导入

select dump_txt( 'select * from all_objects',',','EXTXT','all_object2.txt' )  from dual

create table t_dump as
select * from all_objects
where 1=2


select replace(dbms_metadata.get_ddl('TABLE','T_DUMP'),'"','') from dual


  CREATE TABLE SCOTT.T_DUMP 
   (  
  OWNER VARCHAR2(30) NOT NULL ENABLE, 
  OBJECT_NAME VARCHAR2(30) NOT NULL ENABLE, 
  SUBOBJECT_NAME VARCHAR2(30), 
  OBJECT_ID NUMBER NOT NULL ENABLE, 
  DATA_OBJECT_ID NUMBER, 
  OBJECT_TYPE VARCHAR2(19), 
  CREATED DATE NOT NULL ENABLE, 
  LAST_DDL_TIME DATE NOT NULL ENABLE, 
  TIMESTAMP VARCHAR2(19), 
  STATUS VARCHAR2(7), 
  TEMPORARY VARCHAR2(1), 
  GENERATED VARCHAR2(1), 
	SECONDARY VARCHAR2(1), 
	NAMESPACE NUMBER NOT NULL ENABLE, 
	EDITION_NAME VARCHAR2(30)
   ) PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 NOCOMPRESS LOGGING
  STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
  PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT)
  TABLESPACE USERS 
  
 
drop table T_ext 

  CREATE TABLE T_ext
  (
  OWNER VARCHAR2(30), 
  OBJECT_NAME VARCHAR2(30), 
  SUBOBJECT_NAME VARCHAR2(30), 
  OBJECT_ID NUMBER, 
  DATA_OBJECT_ID NUMBER, 
  OBJECT_TYPE VARCHAR2(19), 
  CREATED DATE, 
  LAST_DDL_TIME DATE, 
  TIMESTAMP1 VARCHAR2(19), 
  STATUS VARCHAR2(7), 
  TEMPORARY1 VARCHAR2(1), 
  GENERATED1 VARCHAR2(1), 
  SECONDARY VARCHAR2(1), 
  NAMESPACE NUMBER, 
  EDITION_NAME VARCHAR2(30)
  )
  ORGANIZATION external
  (
    TYPE oracle_loader 
    DEFAULT DIRECTORY EXTXT
    ACCESS PARAMETERS
    (
      RECORDS DELIMITED BY NEWLINE
      SKIP 1
      BADFILE EXTXT:'emp.bad'
      LOGFILE EXTXT:'t.log_xt'
      READSIZE 1048576
      FIELDS TERMINATED BY "," OPTIONALLY ENCLOSED BY '"' LDRTRIM
      REJECT ROWS WITH ALL NULL FIELDS
      (
  OWNER  CHAR(255) TERMINATED BY "," OPTIONALLY ENCLOSED BY '"', 
  OBJECT_NAME  CHAR(255) TERMINATED BY "," OPTIONALLY ENCLOSED BY '"',
  SUBOBJECT_NAME CHAR(255) TERMINATED BY "," OPTIONALLY ENCLOSED BY '"', 
  OBJECT_ID CHAR(255) TERMINATED BY "," OPTIONALLY ENCLOSED BY '"', 
  DATA_OBJECT_ID  CHAR(255) TERMINATED BY "," OPTIONALLY ENCLOSED BY '"', 
  OBJECT_TYPE CHAR(255) TERMINATED BY "," OPTIONALLY ENCLOSED BY '"', 
  CREATED date mask "yyyy-mm-dd hh24:mi:ss" TERMINATED BY "," OPTIONALLY ENCLOSED BY '"' , 
  LAST_DDL_TIME date mask "yyyy-mm-dd hh24:mi:ss" TERMINATED BY "," OPTIONALLY ENCLOSED BY '"' ,  
  TIMESTAMP1 CHAR(255) TERMINATED BY "," OPTIONALLY ENCLOSED BY '"',  
  STATUS CHAR(255) TERMINATED BY "," OPTIONALLY ENCLOSED BY '"',  
  TEMPORARY1 CHAR(255) TERMINATED BY "," OPTIONALLY ENCLOSED BY '"',  
  GENERATED1 CHAR(255) TERMINATED BY "," OPTIONALLY ENCLOSED BY '"', 
  SECONDARY CHAR(255) TERMINATED BY "," OPTIONALLY ENCLOSED BY '"', 
  NAMESPACE CHAR(255) TERMINATED BY "," OPTIONALLY ENCLOSED BY '"', 
  EDITION_NAME CHAR(255) TERMINATED BY "," OPTIONALLY ENCLOSED BY '"'   
      )
    )
    location
    (
      EXTXT:'all_object2.txt',EXTXT:'all_object1.txt'
    )
  )
  PARALLEL
  REJECT LIMIT UNLIMITED

select * from T_ext
调试方法

  CREATE TABLE T_ext_in
  (
  OWNER VARCHAR2(30), 
  OBJECT_NAME VARCHAR2(30), 
  SUBOBJECT_NAME VARCHAR2(30), 
  OBJECT_ID NUMBER, 
  DATA_OBJECT_ID NUMBER, 
  OBJECT_TYPE VARCHAR2(19), 
  CREATED DATE, 
  LAST_DDL_TIME DATE, 
  TIMESTAMP1 VARCHAR2(19), 
  STATUS VARCHAR2(7), 
  TEMPORARY1 VARCHAR2(1), 
  GENERATED1 VARCHAR2(1), 
  SECONDARY VARCHAR2(1), 
  NAMESPACE NUMBER, 
  EDITION_NAME VARCHAR2(30)
  ) parallel nologging
  
  
  insert /*+append*/ into T_ext_in
  select /*+parallel*/ * from T_ext
  --2.5s


SQL> conn scott/tiger
已连接。
SQL> @D:\ex_file\insert.SQL
时间好长…….


原因:
1、	绑定变量;
2、	多进程的直接加载;


如果从客户端加载文本数据,则用sql*loader

 

分享到:
评论

相关推荐

    数据导入导出

    Oracle提供了一系列工具和技术,使得用户能够高效地从外部源导入数据,或者将数据库中的数据导出到其他存储介质或系统。在这个过程中,数据的完整性和一致性是至关重要的,因为错误的数据导入导出可能导致数据丢失或...

    UE4参考平面和相机如何导入到ae中

    在"UE4参考平面和相机如何导入到ae中"这个主题中,我们将探讨如何将UE4中的参考平面和相机数据转换并导入到Adobe After Effects中,以便进一步编辑和特效处理。 首先,"UE4FBXtoMAXtoAE"这个描述暗示了我们可能需要...

    2.CFX数据如何导出到tecplot.exe

    介绍了如何将CFX中的计算得到流体数据导入到tecplot中进行显示,通过录制的视频,可以详细指导每个操作的步骤

    (R语言)R数据导入导出手册 英文版

    此外,R语言支持将数据导出为文本文件,以便于数据的共享和备份。文本文件的导出可以是普通的文本格式,也可以是XML格式,后者在数据的结构化存储和跨平台数据交互方面具有优势。 在处理电子表格数据时,R语言可以...

    平面坐标导出、DINI03数据格式(简约版)、电子水准手簿

    在IT行业中,尤其是在测绘与地理信息系统(GIS)领域,平面坐标导出、DINI03数据格式以及电子水准手簿是三个重要的概念。这些技术在现代测量工作中扮演着不可或缺的角色,帮助专业人士高效、准确地进行地形测量和...

    地下管线信息系统中CAD文件的导入与导出.pdf

    在GIS环境下,将管线信息导出时,将数据导出为DXF格式文件是一个关键步骤。DXF文件是一种可以在AutoCAD软件中打开查看和编辑的矢量图形格式。导出的DXF文件保留了管线的图形信息和空间位置信息,使得GIS数据可以方便...

    测绘资料-unity_UDB-2020-5-19-支持导出平面.zip

    综上所述,"测绘资料-unity_UDB-2020-5-19-支持导出平面.zip" 文件包提供的内容涵盖了Unity中处理测绘数据的核心技术,包括UDB的使用、测绘数据的导入与展示,以及3D场景到平面图的导出。对于那些需要在Unity环境中...

    SQL语句导入导出大全

    标题与描述解析:《SQL语句导入导出大全》这一标题明确指出了文章的主要内容将围绕SQL语言中的数据导入导出技术展开。描述部分进一步强调了文档的重点在于使用SQL语句进行EXCEL文件的导入导出操作,这表明文档不仅会...

    PFC5.0几何体的创建、输入和导出.docx

    #### 三、几何体数据的导入与导出 几何体数据可以通过以下几种文件格式导入到PFC5.0中: - .stl - .dxf - .geom (Itasca默认格式) 这些格式覆盖了大多数常见的几何体数据交换需求。通过使用`geometryimport`命令...

    ProCAST指定节点步数内容仿真结果导出图文说明

    本文将详细介绍如何在ProCAST中导出特定步骤的节点仿真结果,包括温度、应力、位移等多种数据类型,并结合ReadS.py脚本进行数据分析。 #### 二、ProCAST导出ci_data.txt的基本流程 在ProCAST中导出仿真结果时,...

    如何在informatica里导入excel文件当做源文件

    在 PowerCenter Designer 中,打开源-&gt;从数据库导入,在数据源中选择 Excel 驱动,配置数据源-&gt;系统 DSN-&gt;添加-&gt;输入数据源驱动名称-&gt;选择需要导入的 Excel 文件。最后确定,选择 Excel 驱动,选择之前定义的范围。 ...

    SQL Server导入导出数据时最常见的一个错误解决方法

    在SQL Server中,数据导入和导出是数据库管理和迁移过程中的常见操作,它允许用户将数据从一个数据源转移到另一个数据源。SQL Server提供了一个直观的工具,即SQL Server导入和导出向导,用于简化这个过程。然而,...

    坐标导出到EXCEL

    7. 数据导出与共享:完成分析后,可以将工作簿保存为Excel文件(.xlsx)或者CSV格式,便于与其他应用程序兼容,也可以分享给其他人。 以上就是将坐标数据导出到Excel的基本过程及相关知识点。在实际应用中,可能还...

    基于ArcMap导入GPS实测数据与矢量成图.pdf

    为了确保数据能够正确无误地导入到ArcMap中,首先需要将GPS采集的数据导出至电脑,并保存为Excel文件格式。之后,可以通过Excel软件将这些坐标数据转换为CSV格式文件,便于后续在ArcMap中进行处理。 #### 三、坐标...

    PDMS导入CAD工具_维图小工具_PDMS小插件.zip

    这不仅包括从CAD到PDMS的数据导入,也可能涉及到从PDMS向CAD导出数据。一个高效的数据交换工具,如本插件,能为设计师在不同设计阶段提供支持,确保设计流程的连贯性和高效性。 此外,值得注意的是,虽然插件的...

    点云ptx格式导入与提取

    本话题将围绕“点云ptx格式导入与提取”这一主题,深入讲解相关知识点。 首先,PTX格式是点云数据的一种常见存储格式,由德国公司3D Systems开发,通常用于其3D扫描仪和软件。这种格式包含了点云的颜色和几何信息,...

    SQL Server 2005 DTS导入平面数据出现错误解决方案

    然而,在处理平面数据源时,可能会遇到一些问题,特别是在数据列的长度超过预设限制时。本篇文章将详细解释这些错误,并提供相应的解决方案。 当尝试使用SQL Server 2005的DTS导入平面文件数据时,用户可能会遇到...

    数据转换-纬地到中海达

    1. 数据导出:在纬地中,将所需的线路设计、地形等数据导出为文本文件或其他可读取的格式。这通常涉及到选择合适的菜单选项,设置输出参数,如坐标系、单位等。 2. 格式转换:由于中海达手簿可能需要特定的文件格式...

Global site tag (gtag.js) - Google Analytics