Oracle创建外部表

写在前面的一些废话

今天帮一个朋友在Windows下面弄Oracle发现我居然不会弄了…[悲催],而且运行起来也比较卡…令:本站备案刚刚通过了.

SQL*Loader创建外部表

在当前目录保存以下内容[文件名:ldr_case1.ctl]:

1
2
3
4
5
6
7
8
9
10
LOAD DATA
INFILE *
APPEND INTO TABLE BONUS -- 这个表必须是已经存在的
FIELDS TERMINATED BY ","
(ENAME,JOB,SAL)
BEGINDATA
SMITH,CLEAK,3904
ALLEN,SALESMAN,2891
WARD,SALESMAN,3128
KING,PRESIDENT,2523

可以使用下面的命令去生成相关文件:

1
[当前路径]: sqlldr SCOTT/TIGER control=ldr_case1.ctl external_table=generate_only

执行之后就会有一个ldr_case1.log里面会有相关脚本.
external_table有三个参数:

  1. NOT_USED:不使用外部表,默认值.
  2. GENERATE_ONLY:SQLLDR并不执行加载,而是生成创建外部表的SQL和处理数据的SQL.并保存在log文件中.[修改后可再SQL*Plus中执行]
  3. EXECUTE:首先创建外部表,然后通过外部表方式加载数据
  4. 执行上述命令之后,ldr_case1.log的内容如下[节选:创建DIRECTORY,创建TABLE部分]:

    1
    2
    3
    4
    5
    6
    7
    8
    9
    10
    11
    12
    13
    14
    15
    16
    17
    18
    19
    20
    21
    22
    23
    24
    25
    26
    27
    28
    29
    30
    31
    32
    33
    34
    35
    36
    37
    38
    39
    40
    41
    42
    43
    44
    45
    46
    47
    48
    49
    50
    51
    52
    53
    54
    55
    56
    57
    58
    59
    60
    61
    CREATE DIRECTORY statements needed for files
    ------------------------------------------------------------------------
    CREATE DIRECTORY SYS_SQLLDR_XT_TMPDIR_00000 AS '/home/oracle/Documents/oracle_product/external'


    CREATE TABLE statement for external table:
    ------------------------------------------------------------------------
    CREATE TABLE "SYS_SQLLDR_X_EXT_BONUS"
    (
      "ENAME" VARCHAR2(10),
      "JOB" VARCHAR2(9),
      "SAL" NUMBER
    )
    ORGANIZATION external
    (
      TYPE oracle_loader
      DEFAULT DIRECTORY SYS_SQLLDR_XT_TMPDIR_00000
      ACCESS PARAMETERS
      (
        RECORDS DELIMITED BY NEWLINE CHARACTERSET US7ASCII
        BADFILE 'SYS_SQLLDR_XT_TMPDIR_00000':'ldr_case1.bad'
        LOGFILE 'ldr_case1.log_xt'
        READSIZE 1048576
        SKIP 6
        FIELDS TERMINATED BY "," LDRTRIM
        REJECT ROWS WITH ALL NULL FIELDS
        (
          "ENAME" CHAR(255)
            TERMINATED BY ",",
          "JOB" CHAR(255)
            TERMINATED BY ",",
          "SAL" CHAR(255)
            TERMINATED BY ","
        )
      )
      location
      (
        'ldr_case1.ctl'
      )
    )REJECT LIMIT UNLIMITED


    INSERT statements used to load internal tables:
    ------------------------------------------------------------------------
    INSERT /*+ append */ INTO BONUS
    (
      ENAME,
      JOB,
      SAL
    )
    SELECT
      "ENAME",
      "JOB",
      "SAL"
    FROM "SYS_SQLLDR_X_EXT_BONUS"


    statements to cleanup objects created by previous statements:
    ------------------------------------------------------------------------
    DROP TABLE "SYS_SQLLDR_X_EXT_BONUS"
    DROP DIRECTORY SYS_SQLLDR_XT_TMPDIR_00000

    Oracle LOGO
    image-1845

    CREATE TABLE方式创建外部表

    其实我最喜欢的是这种方式,至少目前是.

    1
    2
    3
    4
    5
    6
    7
    8
    9
    10
    11
    //文件保存为:tes.ctl

    LOAD DATA
    INFILE *
    TRUNCATE INTO TABLE TES
    FIELDS TERMINATED BY ","
    (TNAME,TAGE,TINFO char(2000))
    BEGINDATA
    'tes',20,'dfsafsdafjklsdafdjksalkfjaslkfjkdsalfdjksalkfjsalfkdjsalkfdlsakfjklsd''sas',22,'dfsafsaewqioeuwoiqruewoiqruiejkfsadljfldksjakfldsaljkfjklasdfjksaldfsadsalfkjslkafjkdlsakjfdjlsafkjslkfjdslkafkjdsaf'
    'fdsa',22,'fdsafsdafldsjaklfjsalkfksmncklmsa;djkl;sajfoiewfjsaljfdlskafjlksajflklksjaklfjdslajflksd'
    'fdsasa',25,'dsafsadlkfjlkwejewioqfwiqfl;ksa;klfdslkafdlkjsajlkfdksjlajfkl;sa;jkfdsaldkjsal;fkskj'

    别激动,往下看

    1
    2
    3
    4
    5
    6
    7
    8
    9
    10
    11
    12
    13
    14
    15
    16
    17
    18
    19
    20
    21
    22
    23
    24
    25
    26
    27
    28
    29
    30
    31
    32
    33
    34
    //文件保存为:tes.sql

    CONN  / AS SYSDBA; //连接到系统
    STARTUP FORCE //如果数据库已经启动,此行将先关闭数据库,然后再次启动.
    CREATE DIRECTORY TES AS '/home/oracle/Documents/oracle_product/external/test/'; //必须是已经存在的目录,并且有可读写权限
    GRANT READ,WRITE ON DIRECTORY TES TO SCOTT; //授权
    -- CREATE TEST TABLE.
    CREATE TABLE TES(
        TNAME VARCHAR2(10),
        TAGE NUMBER,
        TINFO VARCHAR2(2000)
    )
    -- external table.
    ORGANIZATION EXTERNAL
    (
        TYPE ORACLE_LOADER
        DEFAULT DIRECTORY TES
    -- 加载数据
        ACCESS PARAMETERS(
            RECORDS DELIMITED BY NEWLINE
            SKIP 6
            FIELDS TERMINATED BY ","
            (TNAME,TAGE,TINFO)
        )
            LOCATION('tes.ctl')
    -- end 加载数据
    );
    -- end 声明外部表
    -- output table data.
    SELECT * FROM TES;
    -- delete table.
    DROP TABLE TES;
    -- delete directory.
    DROP DIRECTORY TES;

    可以在进入SQL*Plus界面之后,直接执行

    1
    @tes.sql

    可以完成以下操作:–>启动数据库[已经启动的将先被关闭]–>创建外部表–>授权给SCOTT用户–>创建表–>导入数据–>使用SELECT输出表数据–>删除DIRECTORY对象–>删除创建的表.