insert select 和 外部表

 

加载多表数据: insert all ---------- insert all    into sal_history values(empid,hiredate,sal)    into mgr_history values(empid,mgr,sal) select employee_id empid,hire_date hiredate,salary sal,manager_id mgr from employees where employee_id > 200 所有的数据都insert到指定的表。 conditional insert all ---------------------- INSERT ALL WHEN SAL > 10000 THEN    INTO sal_history VALUES(EMPID,HIREDATE,SAL) WHEN MGR > 200 THEN    INTO mgr_history VALUES(EMPID,MGR,SAL) SELECT employee_id EMPID,hire_date HIREDATE,salary SAL, manager_id MGR FROM employees WHERE employee_id > 200; 只要满足条件，就insert数据。 Conditional first insert ------------------------ INSERT FIRST    WHEN SAL > 10000 THEN            INTO sal_history VALUES(EMPID,HIREDATE,SAL)    WHEN MGR > 200 THEN            INTO mgr_history VALUES(EMPID,MGR,SAL) SELECT employee_id EMPID,hire_date HIREDATE,salary SAL, manager_id MGR FROM employees 当第一次满足条件时，insert数据，以后满足条件的跳过。 Pivoting insert --------------- INSERT ALL INTO sales_info VALUES (employee_id,week_id,sales_MON) INTO sales_info VALUES (employee_id,week_id,sales_TUE) INTO sales_info VALUES (employee_id,week_id,sales_WED) INTO sales_info VALUES (employee_id,week_id,sales_THUR) INTO sales_info VALUES (employee_id,week_id,sales_FRI) SELECT EMPLOYEE_ID, week_id, sales_MON, sales_TUE, sales_WED, sales_THUR,sales_FRI FROM sales_source_data; SQL> select * from sales_source_data; EMPLOYEE_ID    WEEK_ID SALES_MON SALES_TUE SALES_WED SALES_THUR SALES_FRI ----------- ---------- ---------- ---------- ---------- ---------- ----------         176          6       2000       3000       4000       5000       6000 SQL> select * from sales_info; EMPLOYEE_ID       WEEK      SALES ----------- ---------- ----------         176          6       2000         176          6       3000         176          6       4000         176          6       5000         176          6       6000 将一行记录，转换成多行记录。 ---------------------------------------------------------------------------- External tables are a complement to SQL*Loader and are especially useful for environments where the complete external source has to be joined with existing database objects and transformed in a complex manner, or where the external data volume is large and used only once. SQL*Loader, on the other hand, might still be the better choice for loading of data where additional indexing of the staging table is necessary. This is true for operations where the data is used in independent complex transformations or the data is only partially used in further processing. 外部表 ------ 1.创建目录 CREATE DIRECTORY emp_dir AS 'c:/oracle/datapump'; grant write,read on DIRECTORY external_table_dir to 创建外部表用户; 2.创建外部表 CREATE TABLE oldemp (empno NUMBER, empname CHAR(20), birthdate DATE) -- 外部表列名 ORGANIZATION EXTERNAL ( TYPE ORACLE_LOADER -- 外部表转载类型    DEFAULT DIRECTORY external_table_dir -- 默认文件存取目录 ACCESS PARAMETERS   -- 存储参数设置 ( RECORDS DELIMITED BY NEWLINE -- 一个新行代表一条记录    BADFILE 'bad_emp'   --    LOGFILE 'log_emp'   -- 日志文件名，[可以指定目录格式为"LOGFILE 目录名:'文件名'"]    FIELDS TERMINATED BY ',' -- 字段分隔符    MISSING FIELD VALUES ARE NULL -- 文件中为空的字段，插入NULL    (empno CHAR,    -- 外部数据文件字段类型设置    empname CHAR,    birthdate CHAR date_format date mask "DD-MM-YYYY"    ) ) LOCATION ('oldemp.txt')    -- 外部数据文件名 ) PARALLEL 5      -- 装载并行度 REJECT LIMIT 200;     -- 该子句指定对在查询外部数据时可能产生的错误的数量的限制。 实例： CREATE TABLE oldemp (empno NUMBER, empname CHAR(20), birthdate DATE) ORGANIZATION EXTERNAL ( TYPE ORACLE_LOADER    DEFAULT DIRECTORY external_table_dir ACCESS PARAMETERS ( RECORDS DELIMITED BY NEWLINE    BADFILE 'bad_emp'    LOGFILE 'log_emp'    FIELDS TERMINATED BY ','    MISSING FIELD VALUES ARE NULL    (empno CHAR,    empname CHAR,    birthdate CHAR date_format date mask "DD-MON-YYYY"    ) ) LOCATION ('oldemp.txt') ) PARALLEL 5 REJECT LIMIT 200; CREATE TABLE oldemp (empno NUMBER, empname CHAR(20), birthdate DATE) ORGANIZATION EXTERNAL ( TYPE ORACLE_LOADER    DEFAULT DIRECTORY external_table_dir ACCESS PARAMETERS ( RECORDS DELIMITED BY NEWLINE    BADFILE 'bad_emp'    LOGFILE 'log_emp'    FIELDS TERMINATED BY ','    MISSING FIELD VALUES ARE NULL    (empno CHAR,    empname CHAR,    birthdate date "DD-MON-YYYY"    ) ) LOCATION ('oldemp.txt') ) PARALLEL 5 REJECT LIMIT 200;