ORACLE SQL 扩展DML-7727197
第二十章:DML语句的扩展drop table t1 purge;
drop table t2 purge;
create table t1 (empno number(4),ename varchar2(10),sal number(7,2));
create table t2 (ename varchar2(10),job varchar2(10),hiredate date);
insert all
into t1 values (empno,ename,sal)
into t2 values (ename,job,hiredate)
select empno,ename,sal,job,hiredate
from emp;
带条件的insert all
insert all
when sal>1000 then
into t1 values (empno,ename,sal)
when job='CLERK' then
into t2 values (ename,job,hiredate)
select empno,ename,sal,job,hiredate
from emp;
--insert first:满足第一个条件的行不会再走其它的枚举分支
insert first
when sal>1000 then
into t1 values (empno,ename,sal)
when job='CLERK' then
into t2 values (ename,job,hiredate)
select empno,ename,sal,job,hiredate
from emp;
旋转插入:
create table SALES_SOURCE_DATA
(empno number,
WEEK_ID number,
SALES_MON number,
SALES_TUE number,
SALES_WED number,
SALES_THUR number,
SALES_FRI number
);
insert into SALES_SOURCE_DATA values (178,1,2500,1200,10,3000,4500);
insert into SALES_SOURCE_DATA values (179,2,3500,200,111,1000,40);
EMPNO WEEK SALES
----- ---- -----
select empno,week_id week,sum(decode(WEEK_ID,1,SALES_MON,2,SALES_mon)) sales
from sales_source_data group by empno,week_id
union all
select empno,week_id week,sum(decode(WEEK_ID,1,SALES_tue,2,SALES_tue)) sales
from sales_source_data group by empno,week_id
union all
select empno,week_id week,sum(decode(WEEK_ID,1,SALES_wed,2,SALES_wed)) sales
from sales_source_data group by empno,week_id
union all
select empno,week_id week,sum(decode(WEEK_ID,1,SALES_thur,2,SALES_thur)) sales
from sales_source_data group by empno,week_id
union all
select empno,week_id week,sum(decode(WEEK_ID,1,SALES_fri,2,SALES_fri)) sales
from sales_source_data group by empno,week_id;
create table SALES_INFO
(empno number,
WEEK number,
SALES number
);
将SALES_SOURCE_DATA的数据全部插入到SALES_INFO
INSERT ALL
INTO sales_info VALUES(empno,week_id,sales_MON)
INTO sales_info VALUES (empno,week_id,sales_TUE)
INTO sales_info VALUES (empno,week_id,sales_WED)
INTO sales_info VALUES (empno,week_id,sales_THUR)
INTO sales_info VALUES (empno,week_id, sales_FRI)
SELECT empno, week_id, sales_MON, sales_TUE, sales_WED, sales_THUR,sales_FRI
FROM sales_source_data;
create table SALES_INFO2
(empno number,
WEEK number,
WEEK_name varchar2(4),
SALES number
);
INSERT ALL
INTO sales_info2 VALUES(empno,week_id,'MON',sales_MON)
INTO sales_info2 VALUES (empno,week_id,'TUE',sales_TUE)
INTO sales_info2 VALUES (empno,week_id,'WED',sales_WED)
INTO sales_info2 VALUES (empno,week_id,'THUR',sales_THUR)
INTO sales_info2 VALUES (empno,week_id, 'FIR',sales_FRI)
SELECT empno, week_id, sales_MON, sales_TUE, sales_WED, sales_THUR,sales_FRI
FROM sales_source_data;
外部表:
--/home/oracle/1.txt--
7369,SMITH,CLERK,7902,1980/12/17:00:00:00,852,,20
7499,ALLEN,SALESMAN,7698,1981/02/20:00:00:00,1673,300,30
7521,WARD,SALESMAN,7698,1981/02/22:00:00:00,1251,500,30
7566,JONES,MANAGER,7839,1981/04/02:00:00:00,2980,,20
7654,MARTIN,SALESMAN,7698,1981/09/28:00:00:00,1290,1400,30
7698,BLAKE,MANAGER,7839,1981/05/01:00:00:00,2900,,30
--/home/oracle/2.txt --
7782,CLARK,MANAGER,7839,1981/06/09:00:00:00,2450,,10
7839,KING,PRESIDENT,,1981/11/17:00:00:00,5000,,10
7844,TURNER,SALESMAN,7698,1981/09/08:00:00:00,1500,0,30
创建逻辑目录并授权:
conn / as sysdba
CREATE DIRECTORY TESTDIR AS '/home/oracle';
GRANT READ,WRITE ON DIRECTORY TESTDIR TO SCOTT;
创建外部表:
conn scott/tiger
CREATE TABLE scott.ext_emp
(emp_id number(4),
ename varchar2(12),
job varchar2(12) ,
mgr_id number(4) ,
hiredate date,
salary number(8),
comm number(8),
dept_id number(2))
ORGANIZATION EXTERNAL
(TYPE ORACLE_LOADER
DEFAULT DIRECTORY TESTDIR
ACCESS PARAMETERS(RECORDS DELIMITED BY NEWLINE
FIELDS TERMINATED BY ','
(emp_id char,
ename char,
job char,
mgr_id char,
hiredate char date_format date mask "yyyy/mm/dd:hh24:mi:ss",
salary char,
comm char,
dept_id char))
LOCATION('1.txt','2.txt'));
reject limit 40000;
查询外部表:
select * from ext_emp;
create table t (id int constraint pk_t_id primary key) tablespace users;
create index i_t_id on t(id) tablespace indx;
CREATE TABLE NEW_EMP
(empno NUMBER(6)PRIMARY KEY USING INDEX
(CREATE INDEX emp_id_idx ON
NEW_EMP(empno) tablespace example),
first_name VARCHAR2(20),ename VARCHAR2(25));
页:
[1]