시소당
중첩 테이블 ( 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 생성
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;