1. 다중 테이블 INSERT
--서브쿼리의 결과를 1개 이상의 테이블에 입력 할 수 있기 때문에 기존의 데이터베이스에 구축된 많은 데이터를 데이터웨어 하우스로 구축하는 업무에 매우 유용하다
**무조건 INSERT
//빈 테이블 생성
SQL> create table t1
2 as select empno, ename, hiredate
3 from emp
4 where 1 != 1;
SQL> create table t2
2 as select empno, ename, sal
3 from emp
4 where 0=1;
--다중 테이블 insert
SQL> insert all
2 into t1 values (empno, ename, hiredate)
3 into t2 values (empno, ename, sal)
4 select empno, ename, hiredate,sal
5 from emp
6 where deptno=10;
**조건부 INSERT ALL
SQL> insert all
2 when hiredate >= '81/01/01' then
3 into t1 values (empno, ename, hiredate)
4 when sal>3000 then
5 into t2 values (empno, ename, sal)
6 select empno, ename, hiredate, sal
7 from emp;
**조건부 INSERT FIRST
--분류작업에 용의함, 테이블이 상호 배타적임
SQL> insert first
2 when sal <= 1000 then
3 into sal1000 values (empno, ename, sal)
4 when sal <= 3000 then
5 into sal3000 values (empno, ename, sal)
6 else
7 into salmax values (empno, ename, sal)
8 select empno, ename, sal
9 from emp;
**피벗팅(Pivoting) INSERT
---행데이터를 열데이터로 변환
--테이블 생성
CREATE TABLE SALES_DATA
(EMPNO NUMBER(4),
SPRING_SALE NUMBER(9),
SUMMER_SALE NUMBER(9),
AUTUMN_SALE NUMBER(9),
WINTER_SALE NUMBER(9));
INSERT INTO SALES_DATA VALUES (100, 3000, 5000, 6000, 7000);
INSERT INTO SALES_DATA VALUES (200, 4000, 2000, 3000, 5000);
INSERT INTO SALES_DATA VALUES (300, 6000, 3000, 4000, 4000);
INSERT INTO SALES_DATA VALUES (400, 3000, 1000, 5000, 2000);
CREATE TABLE SALES_SEASON
(EMPNO NUMBER(4),
SEASON_CODE CHAR,
SALES NUMBER(9));
--피벗팅 INSERT
SQL> insert all
2 into sales_season values ( empno, '1', spring_sale)
3 into sales_season values ( empno, '2', summer_sale)
4 into sales_season values ( empno, '3', autumn_sale)
5 into sales_season values ( empno, '4', winter_sale)
6 select empno, spring_sale, summer_sale, autumn_sale, winter_sale
7 from sales_data;
2. EXTERNAL TABLE
--데이터베이스에는 테이블에 대한 정의만 저장되고 실제 데이터는 데이터베이스 외부에 저장되는 읽기 전용 테이블. DML을 사용할 수 없다.
--권한부여
SQL> conn system/123456
SQL> grant create any directory to scott;
--외부파일 읽어드릴 폴더이름 지정
SQL> create directory emp_dir as 'c:\oracle'; --디렉토리명을 emp_dir로 설정
--외부 테이블 파일 읽기
SQL> create table extemp (
2 empno number, ename varchar2(10), job varchar2(10), mgr number, hiredate date)
3 organization external
4 (type oracle_loader
5 default directory emp_dir
6 access parameters
7 (records delimited by newline
8 badfile 'BAD_EMP'
9 logfile 'LOG_EMP'
10 fields terminated by ','
11 (empno char,
12 ename char,
13 job char,
14 mgr char,
15 hiredate char date_format date mask "YY/MM/DD")
16 )
17 location('EMP.TXT')
18 )
19 parallel 5
20 reject limit 200;
--예제 주소 파일 읽기
SQL> create table zipcode(
2 zipcode char(7),
3 sido varchar2(6),
4 gugun varchar2(27),
5 dong varchar2(39),
6 ri varchar(67),
7 bunji varchar2(18),
8 seq number(5))
9 organization external(
10 type oracle_loader
11 default directory emp_dir
12 access parameters(
13 records delimited by newline
14 badfile 'BAD_EMP'
15 logfile 'LOG_EMP'
16 fields terminated by ','(
17 zipcode char,
18 sido char,
19 gugun char,
20 dong char,
21 ri char,
22 bunji char,
23 seq char)
24 )
25 location('zipcode.csv')
26 )
27 parallel 7
28 reject limit 200;
--출력
SQL> select * from zipcode
2 where rownum<10; ---불러올 행의 갯수 제한하기
3. 분석 함수
**RANK
--ORDER BY 뒤에 지정된 컬럼을 기준으로 정렬한 뒤, 해당 행에 대한 순위를 계산한다.
SQL> select empno, ename, hiredate, rank() over (order by hiredate) rank
2 from emp;
///PARTITION BY 그룹별 랭킹
SQL> select empno, ename, deptno, sal,
2 rank() over (partition by deptno order by sal desc) rank
3 from emp;
//DENSE_RANK
--동 순위가 있어도 그 다음 순위를 건너띄지 않음
SQL> select empno, ename, deptno, sal,
2 rank() over (order by sal desc) rank,
3 dense_rank() over (order by sal desc) dense_rank
4 from emp;
//CUME_DIST
--최대값 1을 기준으로 순위를 0~1사이의 값으로 표시
SQL> select empno, ename, deptno, sal,
2 rank() over (order by sal desc) rank,
3 cume_dist() over (order by sal desc) cume_dist
4 from emp;
//PERCENT_RANK
--CUME_DIST와 유사하지만, 계산 방법은 약간 다르다
--PERCENT_RANK = (파티션 내 자신의 RANK-1)/(파티션 내의 행 개수-1)
SQL> select empno, ename, deptno, sal,
2 rank() over (order by sal desc) rank,
3 percent_rank() over (order by sal desc) percent_rank
4 from emp;
//NTILE(N)
--파티션 내의 행들을 N개로 분류하여 행의 위치를 표시한다
SQL> select empno, ename, sal,
2 ntile(7) over (order by sal desc) ntile
3 from emp;
//ROW_NUMBER
--파티션 내에 행들에 대하여 차례로 순번을 표시한다
SQL> select empno, ename, sal,
2 row_number() over (order by sal desc) row_number
3 from emp;
4. 향상된 MERGE 문장
--MERGE 문장의 UPDATE 및 INSERT 구문에 WHERE 절을 추가하여, 특정 조건에 만족하지 않는 행들은 INSERT 및 UPDATE가 수행되지 않도록 할 수 있다.
--테이블 생성
SQL> create table emp20
2 (empno number(4),
3 ename varchar2(10),
4 job varchar2(10),
5 sal number(7,2));
--데이터 입력
SQL> insert into emp20(empno, ename, job, sal)
2 select empno, ename, job, 0
3 from emp
4 where deptno = 20;
--데이터 확인
SQL> select * from emp20;
--향상된 merge
SQL> merge into emp20 n
2 using emp o
3 on (n.empno = o.empno)
4 when matched then
5 update set
6 n.ename = o.ename,n.job = o.job, n.sal = o.sal
7 where o.job='CLERK'
8 when not matched then
9 insert (n.empno, n.ename, n.job, n.sal)
10 values (o.empno, o.ename, o.job, o.sal)
11 where o.job='CLERK';
5. 정규 표현식
--정규 표현식은 문자열의 검색 및 조작에 있어서 단순 및 복잡한 패턴을 모두 사용 할 수 있는 방법을 제공해준다.
․ REGEXP_LIKE : 주어진 패턴에 일치하는 문자열을 검색한다.
․ REGEXP_REPLACE : 일치하는 문자를 검색하고 지정된 패턴으로 교체한다.
․ REGEXP_INSTR : 문자열을 검색하고, 해당문자의 위치를 정수값으로 리턴한다.
․ REGEXP_SUBSTR : 정규 표현식에 일치하는 일부 문자열을 리턴한다.
--REGEXP_LIKE
SQL> select * from reg_test
2 where regexp_like(name, '^ste(v|ph)en$');
--REGEXP_INSTR
SQL> select name,
2 regexp_instr(name, '[^[:alpha:]]')
3 from reg_test
4 where regexp_instr(name, '[^[:alpha:]]')>1;
//[ : 표현식의 시작
//^ : NOT
// [:alpha:] : 알파벳
// ] : 표현식의 끝
--REGEXP_SUBSTR
SQL> select regexp_substr(name, '[^ ]+')
2 from reg_test;
--첫 번째 공백이 나타나기 전의 문자열을 리턴
--REGEXP_REPLACE
SQL> select regexp_replace(name, '(.)', '₩1 ')
2 from reg_test;
--각 문자 뒤에 공백을 추가
6
댓글 없음:
댓글 쓰기