2012.01.10 SQL 6일차(ITAS, CTAS, SQL 명령어들의 분류, 그 중 DML), 트렌젝션, 시퀀스

대량의 데이터를 다른 테이블로 옮기는 방법 2가지(ITAS, CTAS) ; 아이타스 , 씨타스

ITAS : Insert into Table name Select

예제 :
insert into member2

select * from member;

CTAS : Create Table name AS Select ...
예제 :
create table member2
as select * from member;
차이점 :

ITAS는 옮길 대상의 테이블이 존재하고, 데이터만 옮기고 싶을 때 사용하고,
CTAS는 대상 테이블이 존재하지 않을 때, 사용합니다.

-----------------------------------------------------------------------------

SQL 명령어들의 분류(외우시는게 공부하는데 편합니다)

DML(Data Manipulate Language, 데이터의 내용과 관련)

: insert, update, delete, merge

DDL(Data Definition Language, 데이터의 구조와 관련)

:  create, alter, truncate, drop

DCL(제어어)

    : grant, revoke

TCL
    : commit, rollback
----------------------------------------------------------------------------- 
서버프로세스 : SQL> 에서 치는 모든 문장을 우리 대신 오라클한테 전달해서 실행해줌
----------------------------------------------------------------------------- 

DML(데이터 조작어)

예제에 사용할 member 테이블의 구조:

SQL> desc member
 Name                                      Null?    Type
 ----------------------------------------- -------- ---------------------------- 
USERID                                             VARCHAR2(10) 
USERNAME                                           VARCHAR2(10) 
PASSWD                                             VARCHAR2(10) 
IDNUM                                              VARCHAR2(10) 
PHONE                                              NUMBER(13) 
ADDRESS                                            VARCHAR2(20) 
REGDATE                                            DATE 
INTEREST                                           VARCHAR2(15) 
EMAIL                                              VARCHAR2(50)
 COUNTRY                                            VARCHAR2(20) 

1. 단일 행 입력
INSERT INTO table(col1, col2) VALUES(value1, value2)
table 우측에 특별한 컬럼을 쓰지 않는다면, 모든 컬럼을 지칭함.
주의사항 : VALUES안에 자료 입력 시 숫자 뺴고는 전부 ' '으로 묶어서 써줘야 합니다.(날짜도 ' ' 사용)

INSERT INTO member
VALUES('gy14', 'hoon', '1234', '851108', 01050060272,
'SUwon',sysdate, 'Guitar', 'naver', 'Korea');
1 row created. 

INSERT INTO member(userid, passwd)
VALUES('g12', '12948');

1 row created. 

2. NULL의 입력
묵시적 입력 : 바로 위의 예시처럼 member의 userid, passwd를 제외한 다른 모든 컬럼은

                   구조상 특별한 DEFAULT값이 없으면 자동으로 NULL들어갑니다.
     주의 : 바로 위의 예시처럼 userid, passwd를 명시하고, 데이터를 입력하지 않으면 오류가 발생합니다.
명시적 입력 : NULL 사용, 그냥 NULL써주면 됩니다.

insert into member
values('youtube',null,'3981',null, null,null, null, null, null, null);

1 row created. 

3. 날짜입력하기
기본적으로 'DD-MON-YY' 형태로 입력하고,
입력 시 TO_DATE함수를 써서 편리상 원하는 형태로 넣어 줄 수 있습니다. (저장은 DD-MON-YY 형식으로 저장됩니다.)
처음예시처럼 sysdate 사용도 가능합니다.

SQL> insert into member(userid, username, regdate)
  2  values('Insert','Date',to_date('2004/02/06','rrrr/mm/dd'));
-------------행 추가 후------------------------------
  1  select * from member
  2* where userid='Insert'

USERID     USERNAME   PASSWD     IDNUM           PHONE ADDRESS  REGDATE      INTEREST        EMAIL      COUNTRY
---------- ---------- ---------- ---------- ---------- -------- ------------ --------------- ---------- ----------
Insert     Date                                                 06-FEB-04                               Korea 

  1  insert into member(userid, username, regdate)
  2* values('Insert', 'Date2', '05-MAY-11')
      SQL> /          <--- TO_DATE()를 쓰지 않고 넣어줄때는 DD-MON-YY형식으로 써주어야 합니다.

1 row created.
SQL> select userid, username, regdate
  2  from member
  3  where userid='Insert';
USERID     USERNAME   REGDATE
---------- ---------- ------------
Insert     Date       06-FEB-04
Insert     Date2      05-MAY-11    << 

4. 다중 행 입력
서브쿼리를 이용합니다.서브쿼리를 이용하여 자신이나 다른 테이블의 데이터를 복사하여 여러행을 동시에 입력할 수 있다.

INSERT INTO professor
SELECT * FROM professor	
WHERE profno > 9905 

5. 데이터 수정(UPDATE)
     : 자료가 바뀌기 때문에 특히 조심해야 합니다. 조건같은거 확실히 확인하세요, 두번 확인하세요.
 

UPDATE table

SET column = value [, column=value,…]

[WHERE condition];

* WHERE 절을 생략하면 테이블의 모든 행을 수정함

* condition : 칼럼 이름, 표현식, 상수 ,서브쿼리, 비교 연산자로 구성된 검색조건

* 단일행 변경

  1  update member
  2  set userid='gywgyw'
  3* where userid='youtube'
SQL> /

1 row updated. 

* 서브쿼리를 이용한 데이터 수정

UPDATE table

SET (column1, column2,……) = (SELECT s_column1,s_column2…)

FROM table2

WHERE condition2)

[WHERE condition1];
* 서브쿼리에 WHERE썻다고 메인쿼리에 WHERE절 쓰는거 잊지마세요, 안쓰면 망합니다. 

* 사용예)
학번이 10201인 학생의 학년과 학과 번호를 10103학번 학생의 학년과 학과번호
와 동일하게 수정하세요.

SQL>   UPDATE student
	SET (grade, deptno) = (SELECT grade, deptno
				FROM student
				WHERE studno = 10103)
	WHERE studno = 10201;
 

6. 데이터 삭제(DELETE)
* 단일행삭제

 문법) DELETE [FROM ] table

         [WHERE condition];

 *WHERE 절을 생략하면 테이블의 모든 행이 삭제 됨
 

* 서브쿼리를 이용한 여러행 삭제

* 사용예)
학생 테이블에서 컴퓨터 공학과에 소속된 학생을 모두 삭제하여라.
SQL>  DELETE FROM student
	WHERE deptno = ( SELECT deptno
			    FROM departmet
			    WHERE dname = ‘컴퓨터공학과’);

8행이 삭제되었습니다. 
 

7. 데이터 병합(MERGE)
    : 구조가 같은 두개의 테이블을 비교하여 하나의 테이블로 합치기 위한 명령어.

문법)    MERGE INTO [table] [alias]

USING [table | view| subquery ] alias

ON [ join condition]

WHEN MATCHED THEN

UPDATE SET …..

WHEN NOT MATCHED THEN

INSERT INTO ….

VALUES …;

MERGE INTO EMP a USING
   (Select  *  FROM  HR.employees) b
ON (a.emp_no =b. employee_id )
WHEN MATCHED THEN UPDATE SET a.salary =b.salary
WHEN NOT MATCHED THEN INSERT
   (emp_no, last_name, first_name, dept_no, 
    hire_date, salary)
VALUES
  (employee_id,last_name, first_name, department_id,
   hire_date, salary); 

Merge에 대한 고찰

1일				TOTAL table
1억건		;-----> 병합 후		31억건
2일
1억건			한달간의 특정상품 판매량 보고 싶을때, 일별로 나눠져 있으면
			TOTAL table이 없으면 select 31번 걸어야 한다.--->  엄청 느림
30일			소계를 쓸때는 TOTAL table 하나만 이용해서 하는게 빠르다.
1억건
고찰
매일 00:01 마다 merge한다고 치면 1일날은 그냥 넣고
2일날은 1억권 비교해서 넣고
3일날은 1+2억권 비교해서 넣고
…
이러면 엄청 느려진다
이럴때는 INDEX가 잘 짜여져 있어야 한다. --> 이부분은 나중에

----------------------------------------------------------------------------- 
트랜젝션관리

개념 : 여러개의 작업을 셋트로 묶어서 하나로 처리하는 개념
       : 관계형 데이터베이스에서 실행되는 여러 개의 SQL 명령문을 하나의 논리적인 작업 단위로
     처리하는 개념으로 ALL-or-Nothing 방식으로 처리됨. 
       : 트랜젝션은 여러개의 DML문이 모여서 하나의 트랜젝션 (Tx)을 구성하기 떄문에 트랜젝션의 크기는 다양하다.

개념예제1)
Insert  ---- 1
Update --- 2
delete  ---- 3
을 수행했는데, 보니까 3번이 수행하면 안되는 작업인데 수행했다는 걸 알아차리고
3번을 취소하기 위해 rollback(작업취소)하게 되면, 3번만 취소되는것이 아니고, 하나의 트랜젝션 모두가 취소가 됩니다.

즉, rollback 수행 시 1,2,3 모두 작업 취소.
이때 1,2,3을 하나의 Tx(트랜젝션)이라고 합니다.

개념예제2)
insert    -----  1
update   -----  2
delete  -----   3
create table   ----- 4
한 후  2번을 취소하고 싶어서 rollback수행하면 어떻게 될까요?
rollback을 수행해도 변하는 건 없습니다.
    이유는?
트랜젝션은 DML들이 모여서 구성되는 작업단위인데, create의 경우 DDL명령어이기 때문에
이미 1,2,3번 트랜젝션은 단순히 rollback으로는 작업취소를 할 수 없습니다.
--------------
트랜젝션 관리 명령문
COMMIT : 트랜잭션 내의 모든 SQL 명령문에 의해 변경된 작업 내용을 디스크에 영구적으로 저장하고 트랜잭션을 종료.
ROLLBACK : 트랜잭션 내의 모든 SQL 명령문에 의해 변경된 작업 내용을 전부 취소하고 트랜잭션을 종료

-----------------------------------------------------------------------------  


시퀀스(SEQUENCE)
    ; 일정한 규칙으로 연속적으로, 일정하게 증가하거나 감소하는 값을 자동으로 생성하기 위한 객체.
   예) 번호표기계, 게시판 글번호

CREATE SEQUENCE sequence
[INCREMENT BY n] <- 시퀀스 번호의 증가 값으로 기본값은 1
[START WITH n] <- 시퀀스 시작번호로 기본값은 1
[MAXVALUE n | NOMAXVALUE] <- 생성 가능한 시퀀스 최대값
[MINVALUE n | NOMINVALUE] <-CYCLE일 경우 새로 시작되는 값
[CYCLE | NOCYCLE] <- 시퀀스 번호를 순환 사용할 것인지 지정
[CACHE n | NOCACHE] <- 시퀀스 생성속도를 개선하기 위해 캐싱여부 지정

여기서 MAXVALUE일 경우는 이해할 때 별 문제가 없지만
MINVALUE 는 CYCLE일 경우 해당 시퀀스가 MAXVALUE까지 증가하고,
    MAXVALUE를 초과해서 증가하게 되면 MINVALUE부터 다시 시작해서 증가하게됩니다.
CACHE의 경우 속도와 관련이 있어서 이슈가 많이 되는 부분입니다.

ISSUE1)
CACHE관련 이슈
이해를 돕기 위해 상황을 가정합니다.

은행에 번호표기계가 있고, 번호표는 하나 뽑으면 다음 번호표가 나오기까지 1초가 걸립니다.
은행에 항상 12:00에 100명의 손님이 한꺼번에 들어온다고 가정했을때,

손님들은, 특히 마지막 손님은 100초를 기다려야 합니다.

그래서 은행점장이 직원보고, 12:00되기 전에 번호표 100개를 미리 뽑아놓고, 손님이 들어오는 대로 바로바로 나눠주라고 시킵니다.
이 상황을 보고 CACHE를 사용했다고 합니다.
(여기서는 번호표 100개 미리 뽑아놓으라고 했으니까  CACHE 100 이겠죠..)

ISSUE2) 
SEQUENCE 의 롤백?
또 하나의 이슈는 시퀀스는 롤백이 되는가? 입니다.
시퀀스를 하나 만들어 놓고,
insert into table(시퀀스.NEXTVAL, 글내용) 형식으로 100번까지 연속적으로 추가하다가.. 어! 작업취소하고 싶다 해서
rollback하게 되면 insert into 는 DML이기때문에 rollback이 되지만
100번까지 생성된 시퀀스의 경우에는 다시 0이 되지 않습니다.
결론 : 시퀀스는 롤백이 안된다.

시퀀스 확인 및 생성 함수
CURRVAL : 현재번호 확인
NEXTVAL : 다음 번호생성

시퀀스 생성 사용 예제)
  시작 번호는 1, 증가치는 1, 최대 값은 100인 s_seq 시퀀스를 생성하여라.

SQL>  CREATE SEQUENCE s_seq
	INCREMENT BY 1
	START WITH 1
	MAXVALUE 100;
 

 *NEXTVAL 로 다음 시퀀스 생성
SQL> select s_seq.NEXTVAL from dual;

   NEXTVAL
----------
         1

SQL> /

   NEXTVAL
----------
         2

SQL> /

   NEXTVAL
----------
         3
 

*CURRVAL로 현재 시퀀스 확인
  1* select s_seq.CURRVAL from dual
SQL> /

   CURRVAL
----------
         3

SQL> /

   CURRVAL
----------
         3
 

 s_seq 시퀀스를 삭제하여라.
 SQL>  DROP SEQUENCE s_seq;