SSISO Community

시소당

중첩테이블 & Oracle Loader

중첩  테이블 ( Nested Table )

create or replace type emp_type       -- 존재하지 않으면 새로만들고 , 존재하면 수정하여라.
as object                                            -- type(원형) 을 생성.  이 원형은 하나의 객체로 쓰인다.
(empno number(4)
,ename varchar2(10)
,job varchar2(9)
,mgr number(4)
,hiredate date
,sal number(7,2)
,comm number(7,2)
);
 

create or replace type emp_tab_type   -- emp_tab_type 이 실질적으로 사용되는 중첩테이블의 데이터타입이다.
as table of emp_type                            -- emp_type 객체를 테이블 형태로 사용하겠다
 
 
 
-------------------------------------
 
 
 
 
create table dept_and_emp           --  중첩테이블 생성
(deptno number(2) primary key
,dname varchar2(14)
,loc varchar2(13)
,emps emp_tab_type       
) nested table emps store as emps_nt;        
                                                      ↘
                                                        중첩테이블 emps 의 내용은 emps_nt 테이블에 저장된다.
-- emps는 중첩되어진 테이블이다.
-- dep_and_emp 테이블이 생성되면서, emps_nt 라는 테이블이 생성된다.
 

alter table emps_nt
add constraint emps_nt_empno_unique unique(empno);

/*
alter table emps_nt
add constraint mgr_fk foreign key(mgr) reference emps_nt(empno);  --오류
*/
 

insert into dept_and_emp
select D.*, cast(multiset(select empno, ename, job, mgr, hiredate, sal, comm
                        from emp
                        where deptno=D.deptno) as emp_tab_type)
from dept D
 
 
commit;
 
 
select * from dept_and_emp   -- sqlplus 에서만 지원된다.
 
 
 
select deptno, dname, loc, E.*     -- 위 내용을 oraclegate에서 볼려면 이와같이...
from dept_and_emp D, table(D.emps) E;
DEPTNO DNAME LOC EMPNO ENAME JOB MGR HIREDATE SAL COMM
10 ACCOUNTING NEW YORK 7782 CLARK MANAGER 7839 1981-06-09 00:00:00 2450 
10 ACCOUNTING NEW YORK 7839 KING PRESIDENT  1981-11-17 00:00:00 5000 
10 ACCOUNTING NEW YORK 7934 MILLER CLERK 7782 1982-01-23 00:00:00 1300 
20 RESEARCH DALLAS 7369 SMITH CLERK 7902 1980-12-17 00:00:00 800 
20 RESEARCH DALLAS 7566 JONES MANAGER 7839 1981-04-02 00:00:00 2975 
20 RESEARCH DALLAS 7788 SCOTT ANALYST 7566 1987-04-19 00:00:00 3000 
20 RESEARCH DALLAS 7876 ADAMS CLERK 7788 1987-05-23 00:00:00 1100 
20 RESEARCH DALLAS 7902 FORD ANALYST 7566 1981-12-03 00:00:00 3000 
30 SALES CHICAGO 7499 ALLEN SALESMAN 7698 1981-02-20 00:00:00 1600 300
30 SALES CHICAGO 7521 WARD SALESMAN 7698 1981-02-22 00:00:00 1250 500
30 SALES CHICAGO 7654 MARTIN SALESMAN 7698 1981-09-28 00:00:00 1250 1400
30 SALES CHICAGO 7698 BLAKE MANAGER 7839 1981-05-01 00:00:00 2850 
30 SALES CHICAGO 7844 TURNER SALESMAN 7698 1981-09-08 00:00:00 1500 0
30 SALES CHICAGO 7900 JAMES CLERK 7698 1981-12-03 00:00:00 950 
 
 
 
 
 
 
중첩테이블의 업데이트, insert, delete
 

update table(select emps
            from dept_and_emp
            where deptno=10)
        set comm=100;
 

insert into table(select emps
                from dept_and_emp
                where deptno=10)
values(1234,'이종석','DBA',7839,sysdate,9900,5000);
 
 
delete table(select emps
                from dept_and_emp
                where deptno=10)
where ename='이종석'
 
 
select deptno, dname, loc, E.*   
from dept_and_emp D, table(D.emps) E
where deptno=10;
 
 

sleect /* + nested_table_get_refs */
from emps_nt;              -- 중첩테이블 내용만 가져온다. (비공식...)
 
 

select E.*           -- (공식...)
from dept_and_emp D, table(D.emps) E
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
               
 
 
 
 

*Oracle Loader*
 
 
1. 테이블 생성
create table depart_test
(did number(2)
,dname varchar2(20)
,last_update date
);
 
 
2. c:\sqltest\sqlloader.ctl 파일 생성
c:\sqltest\sqlloader.ctl --메모장...
 
load data
infile *
into table depart_test          -- depart_test 테이블은 비어있는 테이블 상태이어야 한다.
fields terminated by ','          -- 컬럼의 구분은 , 로 하겠다.
optionally enclosed by '"'      -- 데이터의 시작과 끝은 " 로 구분한다.
(did, dname, last_updated date 'yyyy-mm-dd')
begindata          -- 이하 데이터를 입력한다.
10,전산학과,2000-01-05
11,기계공학과,2000-07-05
12,"식품,공학과",1999-03-02
13,경영학과,2001-03-05
 
 
3. cmd 명령...
 
C:\sqltest>sqlldr userid=scott/tiger control=c:\sqltest\sqlloader1.ctl log=c:\sq
ltest\sqlloader1.log
SQL*Loader: Release 9.2.0.1.0 - Production on 월 Jul 4 11:30:57 2005
Copyright (c) 1982, 2002, Oracle Corporation.  All rights reserved.
커밋 시점에 도달 - 논리 레코드 개수 4
 
 
 
select * from depart_test
 
DID DNAME        LAST_UPDATE
10   전산학과       2000-01-05 00:00:00
11   기계공학과    2000-07-05 00:00:00
12   식품,공학과   1999-03-02 00:00:00
13   경영학과       2001-03-05 00:00:00
 
 
 
 
 
별도 외부 데이터파일 읽어와서 입력해보기...
 
 
 
1. c:\sqltest\student1.dat   파일 생성
 
c:\sqltest\student1.dat  메모장...
 
홍길동, 전산학과
김유신, 기계공학과
이순신, 식품공학과
유관순, 경영 학과
 
 
2.  student 테이블 생성
 
create table student
(hakbun number(3)
,name varchar2(10)
,depart varchar2(20)
);
 
 
3. c:\sqltest\sqlloader2.ctl 생성
 
 
load data
infile 'c:\sqltest\student1.dat'
insert  into table student                                        -- insert | replace | truncate | append
fields terminated by ','
(hakbun sequence(101,1)   -- 데이터파일에는 학번이 없다...   sequence 는 자동증가...
,name char
,depart char)
 
 
insert into table 테이블명   -- 기존데이터가 없어야 한다.
replace                              -- 기존데이터를 모두 delete 하고 입력한다.
truncate                            -- 기존데이터를 모두 truncate 하고 입력한다.
append                             -- 기존데이터에 추가입력한다.
 
 
 
 
4. cmd 명령...
 
C:\sqltest>sqlldr userid=scott/tiger control=c:\sqltest\sqlloader2.ctl log=c:\sq
ltest\sqlloader2.log
SQL*Loader: Release 9.2.0.1.0 - Production on 월 Jul 4 11:51:40 2005
Copyright (c) 1982, 2002, Oracle Corporation.  All rights reserved.
커밋 시점에 도달 - 논리 레코드 개수 4
 
select * from student;
 
HAKBUN NAME  DEPART
101       홍길동  전산학과
102       김유신  기계공학과
103       이순신  식품공학과
104       유관순  경영 학과
 
 
 
 
 
 
null값이 존재하는 외부 데이터파일 읽어와서 입력하기...
 
 
1. c:\sqltest\sudent2.dat 파일 생성
 
1, 박찬호, 전자공학과
2, 김병현,
3, 이동국, 경영학과
4, 박지은,
 
2. c:\sqltest\sqlloader3.ctl 파일 생성
 
load data
infile 'c:\sqltest\student2.dat'
replace into table student        -- 테이블의 기존데이터를 delete 하고 입력하겠다
fields terminated by ','
trailing nullcols              -- 마지막 컬럼은 null값이 올수있다는 설정...(설정없이 null값이 들어오면 오류)
(hakbun integer external       -- integer 는 os 에서 정의되어진 데이터타입이다
,name char
,depart char)
 
 
3. cmd 명령...
 
C:\sqltest>sqlldr userid=scott/tiger control=c:\sqltest\sqlloader3.ctl log=c:\sq
ltest\sqlloder3.log
SQL*Loader: Release 9.2.0.1.0 - Production on 월 Jul 4 12:20:25 2005
Copyright (c) 1982, 2002, Oracle Corporation.  All rights reserved.
커밋 시점에 도달 - 논리 레코드 개수 3
커밋 시점에 도달 - 논리 레코드 개수 4
 
 
select * from student;
 
HAKBUN NAME DEPART
1            박찬호  전자공학과
2            김병현 
3            이동국  경영학과
4            박지은 
 
 
 
또다른 null값 받아오기....
 
 
c:\sqltest\sudent2.dat 파일  수정
 
1, 박찬호, 전자공학과
2, 김병현,,              -- 콤마를 두개찍어 null값을 표현한다.
3, 이동국, 경영학과
4, 박지은,,
 
 
 c:\sqltest\sqlloader4.ctl ...
 
load data
infile 'c:\sqltest\student2.dat'
replace into table student
fields terminated by ','
(hakbun integer external
,name char
,depart "nvl(:depart, '미지정')"
)
 
 
cmd 명령 ...
 
C:\sqltest>sqlldr userid=scott/tiger control=c:\sqltest\sqlloader4.ctl log=c:\sq
ltest\sqlloder4.log
SQL*Loader: Release 9.2.0.1.0 - Production on 월 Jul 4 12:25:22 2005
Copyright (c) 1982, 2002, Oracle Corporation.  All rights reserved.

커밋 시점에 도달 - 논리 레코드 개수 3
커밋 시점에 도달 - 논리 레코드 개수 4
 
 
select * from student;
 
HAKBUN NAME  DEPART
1           박찬호  전자공학과
2           김병현  미지정
3           이동국  경영학과
4           박지은  미지정
 
 
 
 
여러가지 함수를 사용해서 외부 데이터파일을 읽어오자...
 
c:\sqltest\student3.dat ...
 
1, SEO Yh, Computer
2, Kim YS, electronic
3, lee ss, FOOD
 
 
c:\sqltest\sqlloader5.ctl ...
 
load data
infile 'c:\sqltest\student3.dat'
truncate into table student
fields terminated by ','
(hakbun integer external
,name char "lower(:name)"     -- name 은 소문자로
,depart char "upper(:depart)" -- depart 는 대문자로
)
 
cmd 명령...
 
C:\sqltest>sqlldr userid=scott/tiger control=c:\sqltest\sqlloader5.ctl log=c:\sq
ltest\sqlloder5.log
SQL*Loader: Release 9.2.0.1.0 - Production on 월 Jul 4 12:34:38 2005
Copyright (c) 1982, 2002, Oracle Corporation.  All rights reserved.
커밋 시점에 도달 - 논리 레코드 개수 2
커밋 시점에 도달 - 논리 레코드 개수 3
 
select * from student;
 
HAKBUN NAME      DEPART
1            seo yh    COMPUTER
2            kim ys     ELECTRONIC
3            lee ss      FOOD
 
 
 
 
마지막....
 
create table newstudent
(hakbun number(3)
,name varchar2(10)
,iphakday varchar2(15)       -- 년,월,일 만 보게하기 위해 데이터타입을 문자로...
,jubun varchar2(13)
,sex varchar2(2)
);
 
 
c:\sqltest\newstudent.dat...
 
1, 김유신,,8210231234567,,
2, 엄정화,,8110232234567,,
3, 남자애,,0003013234567,,
4, 여자애,,0103014234567,,
 
 
c:\sqltest\sqlloader6.ctl...
 
load data
infile 'c:\sqltest\newstudent.dat'
insert into table newstudent
fields terminated by ','
(hakbun integer external
,name char
,iphakday char "to_char(sysdate, 'yyyy-mm-dd')"
,jubun char
,sex "case substr(:jubun,7,1) when '1' then '남'
         when '3' then '남'
         else '여'
      end"
)
 
select * from newstudent;
 
HAKBUN NAME IPHAKDAY    JUBUN                SEX
1           김유신 2005-07-04 8210231234567 남
2           엄정화 2005-07-04 8110232234567 여
3           남자애 2005-07-04 0003013234567 남
4           여자애 2005-07-04 0103014234567 여

 

 

 

 

(문제)
 
 
 
 
(1) newstudent2 ... nai 추가 .. , 입력해보시오..
 
 
1. c:\sqltest\newstudent.dat 파일 수정
 
1, 김유신,,8210231234567,,,     -- 컬럼수에 맞게 , 를 수정해준다.
2, 엄정화,,8110232234567,,,
3, 남자애,,0003013234567,,,
4, 여자애,,0103014234567,,,
 
 
 
2. newstudent2 테이블 생성

create table newstudent2
(hakbun number(3)
,name varchar2(10)
,iphakday varchar2(15)     
,jubun varchar2(13)
,sex varchar2(2)
,nai number(3)
);
 
 
 
3.  c:\sqltest\sqlloader7.ctl 파일 생성
 
load data
infile 'c:\sqltest\newstudent.dat'
insert into table newstudent2
fields terminated by ','
(hakbun integer external
,name char
,iphakday char "to_char(sysdate, 'yyyy-mm-dd')"
,jubun char
,sex "case substr(:jubun,7,1) when '1' then '남'
         when '3' then '남'
         else '여'
      end"
,nai " case (substr(:jubun,7,1))
     when '1' then 2006-(1900+substr(:jubun,1,2))
     when '2' then 2006-(1900+substr(:jubun,1,2))
     else 2006-(2000+substr(:jubun,1,2))
     end "
)
 
 
4. cmd 명령...
 
select * from newstudent2;
 
C:\sqltest>sqlldr userid=scott/tiger control=c:\sqltest\sqlloader7.ctl log=c:\sq
ltest\sqlloader7.log
SQL*Loader: Release 9.2.0.1.0 - Production on 월 Jul 4 13:42:14 2005
Copyright (c) 1982, 2002, Oracle Corporation.  All rights reserved.
커밋 시점에 도달 - 논리 레코드 개수 3
커밋 시점에 도달 - 논리 레코드 개수 4
 
 
HAKBUN NAME IPHAKDAY JUBUN SEX NAI
1  김유신 2005-07-04 8210231234567 남 24
2  엄정화 2005-07-04 8110232234567 여 25
3  남자애 2005-07-04 0003013234567 남 6
4  여자애 2005-07-04 0103014234567 여 5
 
 
 
(2) wdo.csv 파일을 읽어다가 테이블 생성해서 넣으시오....
 
 
1. 테이블 생성
 

create table zipcode
(A varchar2(50)
,B varchar2(50)
,C varchar2(50)
,D varchar2(50)
,E varchar2(50)
,F varchar2(50)
,G varchar2(50)
,H number(10)
,I number(10)
)
 
 
2. c:\sqltest\zipsqlloader.ctl 생성
 
load data
infile 'c:\woo.csv'
replace into table zipcode
fields terminated by ','
(A char
,B char
,C char
,D  "nvl(:D, ' ')"
,E  "nvl(:E, ' ')"
,F  "nvl(:F, ' ')"
,G  "nvl(:G, ' ')"
,H integer external
,I integer external
)
 
 
 
3.  cmp 명령...
 
 
C:\sqltest>sqlldr userid=scott/tiger control=c:\sqltest\zipsqlloader.ctl log=c:\
sqltest\zipsqlloder.log
SQL*Loader: Release 9.2.0.1.0 - Production on 월 Jul 4 14:17:16 2005
Copyright (c) 1982, 2002, Oracle Corporation.  All rights reserved.
커밋 시점에 도달 - 논리 레코드 개수 64
커밋 시점에 도달 - 논리 레코드 개수 128
커밋 시점에 도달 - 논리 레코드 개수 192
 
 
select * from zipcode;
 
A     B        C        D             E   F   G  H           I         
경기 가평군 가평읍 가평군청                 477701 477800
경기 가평군 가평읍 가평경찰서              477702 477800
경기 가평군 가평읍 가평교육청              477703 477800
  -- 테이블에 입력된 데이터 갯수가 원본 데이터갯수와 차이가 있는 이유는, woo.bad 파일로 빠져있기 때문이다...
      (형식이 불일치하는 부분 데이터......)
 
 
 
 
 
 
 
 
 
 
 
 
 
 
External Table 생성
               ; 읽기만 가능하고, DML 등 데이터 조작은 불가하다
               ; 외부 데이터의 검색을 손쉽게 하기위해 , 외부테이블을 생성 및 검색한다.
 
 
 
1. 데이터 파일 복사 (mssql ...)
 
c:\>bcp pubs.dbo.stores out c:\sqltest\stores.txt -c -t"," -r\n -Usa -Ptco99 -Sh172            
   --   -c : 문자 , -t"," : 데이터구분자  ...
 
 
2. 경로 디렉토리 생성
 
create or replace directory stores_dir as 'c:\sqltest';   -- stores_dir 의 경로는 c:\sqltest 이다.
 
 
3. 외부 테이블 생성

 create table scott.stores_ext
(stor_id varchar2(4)
,stor_name varchar2(40)
,stor_address varchar2(40)
,city varchar2(20)
,state varchar2(2)
,zip varchar2(5)
)
organization external                       --External Table 임을 설정
(type oracle_loader
default directory stores_dir
              -- 데이터파일의 경로
access parameters                          --  접근 방법
 (records delimited by newline        -- 행의 구분은 줄바꿈으로 하겠다.
  badfile 'stores_bad.bad'               -- 형식에 안맞는 데이터는 bad파일로 분린하겠다.
  logfile 'store_log.log'
  fields terminated by ','
   (stor_id char
   ,stor_name char
   ,stor_address char
   ,city char
   ,state char
   ,zip char)
 )
 location('stores.txt')
 )
 reject limit unlimited;
 

 
select * from scott.stores_ext;
 
 
 
 
 
 
 
 
 
 
 
 
 
 
제 10 장 데이터조작
 
 
 
▶  merge : 병합 (insert + update)
                :오라클 서버의 데이터 동기화
 
 
    (Oracle 서버 A)                              (Oracle  서버 B)
       scott.table A                                 scott.table A
      insert.....         →→→→→→→→→→ ......
 
 
 
 
 
 
Merge Statement
 
 
 
(컴 1,2) 넷서비스 추가...  ,  jikwon2 테이블 생성...
 
create table jikwon2
(jno number(3)
,jname varchar2(10)
,addr varchar2(20)
,constraint jikwon2_jno_pk primary key(jno));
 
 
(컴1)
 
insert into jikwon2 values(1,'홍길동','서울 송파구');
 
 
(컴1,2) 데이터베이스 링크 생성...
 
create database link ajslink
connect to scott identified by tiger         -- 연결할 서버의 유저명과 패스워드.
using 'winajs' ;                                       -- using + '넷서비스명'
 
 
 
(컴1) merge 생성
 
merge into jikwon2@w172link R   -- 상대방 테이블,
using jikwon2 L                                 -- 내 테이블
on (L.jno=R.jno)
when matched then         
      update set R.jname=L.jname, R.addr=L.addr                    -- 다른 부분을 찾아 상대방을 update 하고
when not matched then   
       insert(jno, jname, addr) values(L.jno, L.jname, L.addr); --  insert 한다
 
commit;     -- merge 도 commit을 해주어야 한다~!
 
 
 
 
 
 
 
 
 
  Unconditionnal INSERT ALL

         ; 조건절을 기술할 필요가 없으며 여러개의 into ~ value 절을 사용할수 있다
         ; subquery 로부터 한번에 하나씩 row를 리턴받아 각각 insert 절을 수행한다.
        

create table scott.dept_purc
(no number(4)
,name varchar2(25)
,hiredate date
,salary number
,comm number
);
 
 
 
create table scott.dept_ship    -- 테이블 복사
as
select * from scott.dept_purc
 
 
create tablescott.dept_it
as
select * from scott.dept_purc;
 

create table scott.dept_sales
as
select * from scott.dept_purc;
< /DIV>
 

create table scott.high_tax
(no number(4)
,namevarchar2(10)
,hiredate date
,salary number(6)
,tax number(10,2)
);
 
 
create table scott.high_step
(no number(4)
,name varchar2(10)
,hiredate date
,salary number(6)
,salarystep number(10)
);
 
 
insert all

into scott.high_tax(no, name, salary, tax)
values(employee_id, last_name, salary, salary*0.1)

into scott.high_step(no, name, salary, salarystep
)
values(employee_id, last_name, salary, 1)

select employee_id, last_name, salary
from hr.employees
where salary>=10000;
 
 
select * from scott.high_tax;

select * from scott.high_step;
 
 
 
 
 9장 연습 문제
 
--1.
select *
from user_constraints
where table_name='EMP'
 
select *
from user_cons_columns
where table_name='EMP'
 
--2.
create table emp_demo
as
select empno, ename, job, mgr, hiredate, deptno
from emp
 
--3.
create table emp_dept
as
select E.empno, E.ename, E.job, D.dname, D.loc
from
(select empno, ename, job, deptno
from emp) E,
(select dname, loc, deptno
from dept) D
where E.deptno=D.deptno
 
select * from emp_dept
 
--4.
create table emp_grade
as
select E.*, S.grade
from
(select empno, ename, job, sal, comm
from emp) E, salgrade S
where S.losal <= E.sal and E.sal <= S.hisal
order by 6 desc;
 
select * from emp_grade;

--5.
select *
from user_constraints
where table_name='DEPT'
 
alter table dept
disable constraint PK_DEPT cascade;
 
--다시 활성화...
alter table dept
enable constraint PK_DEPT;
 
select *
from user_constraints
where table_name='EMP';
 
alter table emp
enable constraint FK_DEPTNO;
 
--6.
alter table emp_dept
add constraint emp_dept_empno_pk primary key(empno);
 
select *
from user_constraints
where table_name='EMP_DEPT';
 
--7.
alter table emp_grade
modify sal number(16,4);
desc emp_grade;
 
--8.
truncate table emp_dept;

--9.
drop table emp_dept;
drop table emp_grade;

1452 view

4.0 stars