21. 03. 11.

다중 행 입력 - PIVOTING INSERT

- OLTP(OnLine Transaction Processing) 업무에서 사용되는 데이터를 데이터웨어하우스 업무에서 사용되는 분석용 데이터로 변환하는 경우에 유용

- 하나의 행을 여러 개의 행으로 나누어서 입력하는 기능

- Unconditional INSERT ALL 명령문과 거의 동일

- INTO 절에서 하나의 테이블만 지정

=> 통계 분석

 

-- 예제를 위한 테이블 생성

CREATE TABLE SALES(
    SALES_NO NUMBER(4),
    WEEK_NO NUMBER(2),
    SALES_MON NUMBER(7,2),
    SALES_TUE NUMBER(7,2),
    SALES_WED NUMBER(7,2), 
    SALES_THU NUMBER(7,2),
    SALES_FRI NUMBER(7,2)
);

INSERT INTO SALES VALUES (1101, 4, 100, 150, 80, 60, 120);
INSERT INTO SALES VALUES (1102, 5, 300, 300, 230, 120, 150);

CREATE TABLE SALES_DATA (
    SALES_NO NUMBER(4),
    WEEK_NO NUMBER(2),
    DAY_NO NUMBER(2),
    SALES NUMBER(7,2)
);

 

-- PIVOTING INSERT 명령문을 사용하여 SALES 테이블의 요일별 데이터를 통합하여
-- SALES_DATA 테이블에 하나의 행으로 입력하여라.

INSERT ALL
INTO SALES_DATA VALUES(SALES_NO, WEEK_NO, 1, SALES_MON)
INTO SALES_DATA VALUES(SALES_NO, WEEK_NO, 2, SALES_TUE)
INTO SALES_DATA VALUES(SALES_NO, WEEK_NO, 3, SALES_WED)
INTO SALES_DATA VALUES(SALES_NO, WEEK_NO, 4, SALES_THU)
INTO SALES_DATA VALUES(SALES_NO, WEEK_NO, 5, SALES_FRI)
SELECT *
FROM SALES;
-- 10개 행 삽입

 

-- SALES_DATA를 이용해 주간별 매출 총액 구하기

SELECT WEEK_NO, SUM(SALES)
FROM SALES_DATA
GROUP BY WEEK_NO;

 

 

데이터 수정

- UPDATE

 

-- 교수번호가 9903인 교수의 현재 직급을 부교수로 수정하여라

UPDATE PROFESSOR SET POSITION = '부교수' WHERE PROFNO = 9903;

- UPDATE ~ SET < 한 묶음으로 외워

 

 

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

- 다른 테이블에 저장된 데이터를 검색하여 한꺼번에 여러 칼럼 수정

- SET 절의 칼럼 이름은 서브쿼리의 칼럼 이름과 달라도 됨.

- 데이터 타입과 칼럼 수는 반드시 일치

 

 

-- 서브쿼리를 이용하여 학번이 10201인 학생의 학년과 학과번호를
-- 10103 학번 학생의 학년과 학과번호와 동일하게 수정하여라.

UPDATE STUDENT
SET (GRADE, DEPTNO) = (SELECT GRADE, DEPTNO FROM STUDENT WHERE STUDNO = 10103)
WHERE STUDNO = 10201;

 

서브쿼리를 이용한 데이터 삭제

 

-- 학생테이블에서 컴퓨터공학과에 소속된 학생을 모두 삭제하여라.

SELECT NAME, DEPTNO FROM STUDENT
WHERE DEPTNO =
(
    SELECT DEPTNO
    FROM DEPARTMENT
    WHERE DNAME = '컴퓨터공학과'
);

 

 

MERGE

- 구조가 같은 두 개의 테이블을 비교하여 하나의 테이블로 합치기 위한 데이터 조작어

- WHEN 절의 조건절에서 결과 테이블에 해당 행이 존재하면 UPDATE에 의해 새로운 값으로 수정, 그렇지 않으면 INSERT로 새로운 행 삽입

- 대량의 데이터를 분석하기 위한 업무에 유용

- 예) 전자상거래 회사에서 하루 수만 건의 데이터를 평소에는 판매 데이터를 월 단위로 분리하여 별도의 테이블에서 관리하다가 연말에 판매 실적 분석을 위해 하나의 테이블로 합치는 경우

 

 

MERGE INTO : 하나의 테이블로 합치기 위한 결과 테이블
USING : 테이블, 뷰, 서브쿼리에 대한 별명 지정
ON : 조인 조건 지정
WHEN MATCHED THEN : ON 절의 조인 조건을 만족하는 행이 존재하면 지정된 값으로 행을 UPDATE
WHEN NOT MATCHED THEN : ON 절의 조인 조건을 만족하지 않을 경우 새로운 행으로 INSERT

WHEN MATCHED THEN 절과 WHEN NOT MATCHED THEN 절에서는 테이블이나 뷰 이름 대신에 USING 절에서 지정한 별명 사용

 

-- professor 테이블과 professor_temp 테이블을 비교하여
-- professor 테이블에 있는 기존데이터는 professor_temp 테이블의 데이터에 의해 수정하고,
-- professor 테이블에 없는 데이터는 신규로 입력한다.

 

-- MERGE 위해 재료 만듦 --

CREATE TABLE PROFESSOR_TEMP AS
SELECT *
FROM PROFESSOR
WHERE POSITION = '교수';

UPDATE PROFESSOR_TEMP
SET POSITION = '명예교수'
WHERE POSITION = '교수';

INSERT INTO PROFESSOR_TEMP
VALUES (9999, '김도경', 'AROM21', '전임강사', 200, SYSDATE, 10, 101);

SELECT * FROM PROFESSOR;
SELECT * FROM PROFESSOR_TEMP;

MERGE INTO PROFESSOR P
USING PROFESSOR_TEMP T
ON (P.PROFNO = T.PROFNO)
WHEN MATCHED THEN
    UPDATE SET P.POSITION = T.POSITION 
WHEN NOT MATCHED THEN
    INSERT VALUES(T.PROFNO, T.NAME, T.USERID, T.POSITION, T.SAL, T.HIREDATE, T.COMM, T.DEPTNO);

 

트랙잭션 관리

COMMIT : 트랙잭션 내의 모든 SQL 명령문에 의해 변경된 작업 내용을 디스크에 영구적으로 저장하고 트랜잭션을 종료.

 

ROLLBACK : 변경된 작업 내용을 전부 취소하고 트랜잭션을 종료

 

시퀀스

- 유일한 식별자

- 기본 키 값을 자동으로 생성하기 위해 일련번호 생성 객체

- 여러 테이블에서 공유 가능

- 예) 웹 게시판에서 글 등록 순서대로 번호를 하나씩 할당하여 기본키로 지정하고자 할 때.

 

-- SEQ_MY라는 이름을 가지는 시퀀스를 생성 / 삭제하시오(시험)

CREATE SEQUENCE SEQ_MY;
DROP SEQUENCE SEQ_MY;

 

-- 게시판 (글번호, 제목, 글내용, 작성자, 조회수, 작성일)

CREATE TABLE TBL_BOARD (
    BOARDNO NUMBER,
    TITLE VARCHAR2(2000),
    CONTENT CLOB,
    WRITER VARCHAR2(100),
    HITCOUNT NUMBER DEFAULT O,
    REGDATE DATE DEFAULT SYSDATE
);

 

 

CURRVAL과 NEXTVALUE 함수

- INSERT, UPDATE 문에서 사용

- 서브쿼리, GROUP BY, HAVING, ORDER BY, DISTINCT와 함께 사용할 수 없으며, 컬럼의 기본값으로 사용할 수 없음.

 

- CURRVAL : 시퀀스에서 생성된 현재 번호를 확인

- NEXTVAL : 시퀀스에서 다음 번호 생성

 

INSERT INTO tbl_board VALUES (SEQ_BOARD.NEXTVAL, '글 제목 테스트', '글 내용', '작성자', 0, SYSDATE);

 

 

시퀀스를 이용한 기본 키 생성

- 기본키로 사용할 수 있는 적절한 칼럼이 없거나 다수의 칼럼을 결합해야 식별이 가능한 경우에는 시퀀스를 사용

- NEXTVAL 함수를 사용

 

 

시퀀스 정의 변경

 

 

테이블 관리

테이블 생성

 

-- 연락처 정보를 저장하기 위한 주소록(address)테이블을 생성하여라.

CREATE TABLE ADDRESS (
    ID NUMBER(3)
    , NAME VARCHAR2(50)
    , ADDR VARCHAR2(100)
    , PHONE VARCHAR2(30)
    , EMAIL VARCHAR2(100)
);

테이블 생성 확인 : SELECT * FROM TAB;

테이블 구조 확인 : DESC ADDRESS; (DECS = DESCRIBE)

 

 

DEFAULT

- 칼럼의 입력 값이 생략될 경우에 NULL 대신에 입력되는 기본 값을 지정하기 위한 기능

- 기본값: 리터럴 값, 표현식, SQL함수, SYSDATE, USER 사용.

- 칼럼이나 의사칼럼(NEXTVAL, CURRVAL)은 사용할 수 없음.

 

 

서브쿼리를 이용한 테이블 생성

- CREATE TABLE 명령문에서 서브쿼리 절을 이용하여 다른 테이블의 구조와 데이터를 복사하여 새로운 테이블 생성 가능.

- 서브쿼리의 출력 결과가 테이블의 초기 데이터로 삽입

- 칼럼 이름을 명시하지 않을 경우 서브쿼리 칼럼 이름과 동일

- 무결성 제약조건은 NOT NULL 조건만 복사

(기본 키, 참조 키와 같은 무결성 제약 조건은 사용자의 재정의 필요)

- DEFAULT 옵션에서 정의한 값은 그대로 복사

 

 

-- 서브쿼리 절을 이용하여 주소록 테이블의 구조와 데이터를 복사하여 addr_second 테이블을 생성하여라

CREATE TABLE ADDRESS_SECOND (ID, NAME, ADDR, PHONE, E_MAIL)
AS SELECT * FROM ADDRESS; -- 별로 추천 안 함.

CREATE TABLE ADDRESS_SECOND
AS SELECT ID, NAME, ADDR, PHONE, EMAIL AS E_MAIL FROM ADDRESS;

 

 

테이블 구조 복사

- 서브쿼리를 이용한 테이블 생성시 데이터는 복사하지 않고 기존 테이블의 구조만 복사 가능

- 서브쿼리의 WHERE 조건절에 거짓이 되는 조건을 지정하여 출력 결과 집합이 생성되지 않도록 지정.

- condition 예) WHERE 1 = 2

 

-- 주소록테이블에서 id, name 칼럼만 복사하여 addr_third 테이블을 생성하여라

CREATE TABLE ADDRESS_THIRD
AS SELECT ID, NAME FROM ADDRESS
WHERE 1 = 2;

INSERT INTO ADDRESS_THIRD
VALUES (101, '이름');

 

 

테이블 구조 변경

- ALTER TABLE 명령문 이용

- 칼럼 추가, 삭제, 타입이나 길이의 재정이와 같은 작업

 

481부터~

 

테이블에 칼럼 추가테이블 칼럼 삭제테이블 칼럼 변경테이블 이름 변경테이블 삭제TRUNCATE 명령문DELETE, DROP, TRUNCATE 비교

 

 

데이터 사전

- 테이블, 칼럼, 뷰 등과 같은 정보를 조회하기 위해 사용

 

데이터 사전의 종류

- 다수의 사용자가 동일한 데이터를 공유

- 읽기 전용 뷰로 구성

- 데이터베이스 관리자나 사용자에게 데이터 사전에 저장된 정보 조회 허용

- 용도에 따라 USER, ALL, DBA 접두어를 사용하여 분류

 

 

-- USER_ 데이터 사전 뷰
SELECT TABLE_NAME FROM USER_TABLES;

-- ALL_ 데이터 사전 뷰
SELECT OWNER, TABLE_NAME FROM ALL_TABLES;

-- DBA_ 데이터 사전 뷰
SELECT OWNER, TABLE_NAME FROM DBA_TABLES;

 

데이터 무결성

데이터 무결성 제약조건의 개념

- 데이터의 정확성과 일관성을 보장

 

• 데이터 무결성 제약조건의 장점 

- 테이블 생성시 무결성 제약조건을 정의 가능

- 테이블에 대해 정의, 데이터 딕셔너리에 저장되므로 응용 프로그램에서 입력된 모든 데이터에 대해 동일하게 적용

- 제약조건을 활성화, 비화성화 할 수 있는 융통성

 

데이터 무결성 제약조건의 종류

- NOT NULL, 고유키, 기본키, 참조키, CHECK

 

 

NOT NULL 무결성 제약조건

- 테이블 레벨이 아닌 열 레벨로만 지정 가능.

- NOT NULL 지정된 칼럼은 기존 데이터를 NULL로 수정하면 오류 발생

 

고유키(unique key) 무결성 제약조건

- 열 레벨 또는 테이블 레벨로 정의할 수 있음.

- NULL은 고유키 제약조건에 위배되지 않으므로 입력할 수 있음. (NULL은 항상 UNIQUE함)

- 고유키 제약조건이 정의되면 테이블에 제약조건과 같은 이름의 인덱스가 자동적으로 생성.

 

기본키(primary key) 무결성 제약조건

- 테이블 내에 저장된 행을 유일하게 식별할 수 있는 식별자로 사용

- 관계형 데이터 모델에서 테이블은 행을 원소로 가지는 집합으로 정의되어 집합 내의 모든 행을 구별할 수 있는 식별자가 필요

- 하나 이상의 칼럼에 의해 테이블의 모든 행을 구별하기 위한 식별자를 정의하기 위한 제약 조건