10 데이터 조작어 데이터 조작어 데이터 입력 데이터 수정 데이터 삭제 MERGE 트랜잭션 관리 시퀀스.

Presentation on theme: "10 데이터 조작어 데이터 조작어 데이터 입력 데이터 수정 데이터 삭제 MERGE 트랜잭션 관리 시퀀스."— Presentation transcript:

1 10 데이터 조작어 데이터 조작어 데이터 입력 데이터 수정 데이터 삭제 MERGE 트랜잭션 관리 시퀀스

2 데이터 조작어 데이터 조작어 (DML:Data Manipulation Language)란? 종류 트랜잭션
테이블에 새로운 데이터를 입력하거나 기존 데이터를 수정 또는 삭제하기 위한 명령어 종류 INSERT : 새로운 데이터 입력 명령어 UPDATE : 기존 데이터 수정 명령어 DELETE : 기존 데이터 삭제 명령어 MERGE : 두개의 테이블을 하나의 테이블로 병합하는 명령어 트랜잭션 여러 개의 명령문을 하나의 논리적인 작업단위로 처리하는 기능 트랜잭션 관리 명령어 COMMIT : 트랜잭션의 정상적인 종료를 위한 명령어 ROLLBACK : 트랜잭션의 비정상적인 중단을 위한 명령어 10장. 데이터 조작어

3 데이터 입력 개요 테이블에 데이터를 입력하기 위한 명령어 INSERT 명령문 사용 데이터 입력 방법
단일 행 입력 : 한번에 하나의 행을 테이블에 입력하는 방법 다중 행 입력 : 서브쿼리를 이용하여 한번에 여러 행을 동시에 입력하는 방법 10장. 데이터 조작어

4 단일 행 입력 단일 행 입력 방법 INTO 절에 명시한 칼럼에 VALUES 절에서 지정한 칼럼 값을 입력
입력되는 데이터 타입은 칼럼의 데이터 타입과 동일해야 함 입력되는 데이터의 크기는 칼럼의 크기보다 작거나 동일해야 함 CHAR, VARCHAR2, DATE 타입의 입력 데이터는 단일인용부호(‘’)로 묶어서 입력 10장. 데이터 조작어

5 단일 행 입력 예 10장. 데이터 조작어

6 단일 행 입력 예 10장. 데이터 조작어

7 NULL 입력 NULL 입력 NULL 입력 방법 NULL : 미확인 값 또는 아직 적용되지 않은 값
묵시적인 방법 INSERT INTO 절에 해당 칼럼 이름과 값을 생략 예외 : 해당 칼럼에 NOT NULL 제약조건이 지정된 경우 불가능 명시적 방법 VALUES 절의 칼럼 값에 NULL , ‘ ’ 사용 10장. 데이터 조작어

8 명시적으로 NULL을 입력하는 예 10장. 데이터 조작어

9 날짜 데이터 입력 방법 날짜 형식 입력 해당 시스템에서 요구하는 기본 날짜 형식으로 입력 UNIX 기본 날짜 형식
: ‘DD-MON-YY’, ‘01-Mar-08’ 퍼스널 오라클 : ‘YY/MM/DD’, ‘08/03/01’ 필요에 따라서 TO_DATE 함수 사용 : TO_DATE(‘2005/01/01’, ‘YYYY/MM/DD’) 10장. 데이터 조작어

10 날짜 형식 입력하는 예 10장. 데이터 조작어

11 SYSDATE 함수를 이용한 현재 날짜 입력 SYSDATE 함수 시스템에 저장된 현재 날짜 데이터를 반환하는 함수
현재 시점의 날짜 값을 자동적으로 입력 10장. 데이터 조작어

12 다중 행 입력 다중 행 입력 방법 INSERT 명령문에서 서브쿼리 절을 이용
unconditiional INSERT ALL conditional INSERT ALL conditional FIRST INSERT pivoting INSERT지원 10장. 데이터 조작어

13 단일 테이블에 다중 행 입력 단일 테이블에 다중 행 입력 방법
INSERT 명령문에서 서브쿼리 절을 이용하여 자신이나 다른 테이블에 데이터를 복사하여 여러 행 동시 입력 INSERT 명령문의 VALUES절 대신 서브쿼리에서 검색된 결과 집합을 한꺼번에 입력 서브쿼리의 결과 집합은 INSERT 명령문에 지정된 칼럼 개수와 데이터 타입이 일치해야 함 서브쿼리를 이용한 다중 행 입력시 테이블에 기본 키, 고유 키 제약조건이 중복되지 않도록 주의 제약 조건을 위반할 경우 입력되지 않고 오류 발생 10장. 데이터 조작어

14 다중 행 입력 : subquery 사용예 10장. 데이터 조작어

15 다중 행 입력 - INSERT ALL INSERT ALL(unconditional INSERT ALL) 명령문
서브쿼리의 결과 집합을 조건 없이 여러 테이블에 동시에 입력 서브쿼리의 칼럼 이름과 데이터가 입력되는 테이블의 칼럼이 반드시 동일해야 함 ALL : 서브쿼리의 결과 집합을 해당하는 INSERT절에 모두 입력 FIRST : 서브쿼리의 결과 집합을 해당하는 첫번째 INSERT절에 입력 subquery : 입력 데이터 집합을 정의하기 위한 서브쿼리 10장. 데이터 조작어

16 height_info, weight_info 테이블 생성
10장. 데이터 조작어

17 다중 행 입력 - INSERT ALL 예 10장. 데이터 조작어

18 다중 행 입력 - Conditional INSERT ALL
서브쿼리의 결과 집합에 대해 WHEN 조건절에서 지정한 조건을 만족하는 행을 해당되는 테이블에 각각 입력 서브쿼리에서 검색된 행을 만족하는 조건이 여러 개일 경우 해당 테이블에 모두 입력 10장. 데이터 조작어

19 다중 행 입력 - Conditional INSERT ALL
ALL : WHEN~THEN~ELSE의 조건을 만족하는 서브쿼리의 모든 검색 결과를 입력하기 위한 옵션 WHEN 조건절 THEN : 서브쿼리의 결과 집합에 대한 비교 조건 서브쿼리의 결과 집합 중에서 조건절1을 만족하는 결과 행은 table1에 입력, 조건절 2을 만족하는 결과 행은 table2에 입력, 그리고 어느 조건절도 만족하지 않는 행은 table3에 입력 10장. 데이터 조작어

20 다중 행 입력 - Conditional INSERT ALL 예
10장. 데이터 조작어

21 다중 행 입력 – Conditional-First INSERT
서브쿼리의 결과 집합에 대해 WHEN 조건절에서 지정한 조건을 만족하는 첫번째 테이블에 우선적으로 입력하기 위한 명령문 서브쿼리의 결과 집합 중에서 조건을 만족하는 첫 번째 WHEN절 에서 지정한 테이블에만 입력하고 나머지 WHEN절 무시 10장. 데이터 조작어

22 다중 행 입력 – Conditional-First INSERT 예
10장. 데이터 조작어

23 다중 행 입력 – PIVOTING INSERT
OLTP(OnLine Transaction Processing) 업무에서 사용되는 데이터를 데이터 웨어하우스 업무에서 사용되는 분석용 데이터로 변환하는 경우에 유용 하나의 행을 여러 개의 행으로 나누어서 입력하는 기능 Unconditional INSERT ALL 명령문과 거의 동일 INTO 절에서 하나의 테이블만 지정 예 : 5개의 칼럼으로 구성된 요일별 판매 실적 데이터를 하나의 칼럼으로 통합할 때 하나의 칼럼으로 통합된 판매 데이터의 요일을 구분하기 위하여 요일 구분 칼럼을 추가 10장. 데이터 조작어

24 다중 행 입력 – PIVOTING INSERT
10장. 데이터 조작어

25 다중 행 입력 – PIVOTING INSERT
10장. 데이터 조작어

26 다중 행 입력 – PIVOTING INSERT
10장. 데이터 조작어

27 데이터 수정 데이터 수정 개요 UPDATE 명령문은 테이블에 저장된 데이터 수정을 위한 조작어 데이터 수정 방법
한번에 하나의 행을 수정하는 방법 WHERE 절의 검색 조건을 만족하는 모든 행에 대하여 집합 단위로 수정하는 방법 10장. 데이터 조작어

28 데이터 수정 예 10장. 데이터 조작어

29 서브쿼리를 이용한 데이터 수정 서브쿼리를 이용한 데이터 수정 개요 UPDATE 명령문의 SET 절에서 서브쿼리를 이용
다른 테이블에 저장된 데이터 검색하여 한꺼번에 여러 칼럼 수정 SET 절의 칼럼 이름은 서브쿼리의 칼럼 이름과 달라도 됨 데이터 타입과 칼럼 수는 반드시 일치 10장. 데이터 조작어

30 서브쿼리를 이용한 데이터 수정 예 10장. 데이터 조작어

31 서브쿼리를 이용한 데이터 수정 10장. 데이터 조작어

32 서브쿼리를 이용한 데이터 수정 10장. 데이터 조작어

33 데이터 삭제 데이터 삭제 개요 DELETE 명령문은 테이블에 저장된 데이터 삭제를 위한 조작어
WHERE 절을 생략하면 테이블의 모든 행 삭제 10장. 데이터 조작어

34 단일 행 삭제 예 10장. 데이터 조작어

35 서브쿼리를 이용한 데이터 삭제 서브쿼리를 이용한 데이터 삭제 개요 WHERE 절에서 서브쿼리 이용
다른 테이블에 저장된 데이터를 검색하여 한꺼번에 여러행의 내용을 삭제함 WHERE 절의 칼럼 이름은 서브쿼리의 칼럼 이름과 달라도 됨 데이터 타입과 칼럼 수는 일치 10장. 데이터 조작어

36 서브쿼리를 이용한 데이터 삭제 예 10장. 데이터 조작어

37 MERGE MERGE 개요 구조가 같은 두개의 테이블을 비교하여 하나의 테이블로 합치기 위한 데이터 조작어
구조가 같은 두개의 테이블을 비교하여 하나의 테이블로 합치기 위한 데이터 조작어 WHEN 절의 조건절에서 결과 테이블에 해당 행이 존재하면 UPDATE 명령문에 의해 새로운 값으로 수정,그렇지 않으면 INSERT 명령문으로 새로운 행을 삽입 대량의 데이터를 분석하기 위한 업무에 유용 예를 들면, 전자상거래 회사에서 하루 수만건의 데이터를 평소에는 판매 데이터를 월 단위로 분리하여 별도의 테이블에서 관리하다가 연말에 판매 실적 분석을 위해 하나의 테이블로 합치는 경우 10장. 데이터 조작어

38 MERGE 사용법 10장. 데이터 조작어

39 MERGE 사용 예 10장. 데이터 조작어

40 MERGE 사용 예 10장. 데이터 조작어

41 트랜잭션 관리 트랜잭션 개요 관계형 데이터베이스에서 실행되는 여러 개의 SQL명령문을 하나의 논리적 작업 단위로 처리하는 개념
COMMIT : 트랜잭션의 정상적인 종료 ROLLBACK : 트랜잭션의 전체 취소 10장. 데이터 조작어

42 COMMIT COMMIT 개요 하나의 트랜잭션에서 실행되는 모든 SQL 명령문의 처리 결과가 디스크에 안전하게 보장되는 것을 보장 처리 결과를 디스크에 영구적으로 저장 해당 트랜잭션에 할당된 CPU, 메모리 같은 자원이 해제 서로 다른 트랜잭션을 구분하는 기준 COMMIT 명령문을 실행하기 전에 하나의 트랜잭션에서 변경한 결과를 다른 트랜잭션에서 접근할 수 없도록 방지하여 일관성 유지 10장. 데이터 조작어

43 COMMIT 예 time 10장. 데이터 조작어

44 ROLLBACK ROLLBACK 개요 하나의 트랜잭션에서 실행된 SQL 명령문의 처리결과를 취소
CPU,메모리 같은 해당 트랜잭션에 할당된 자원을 해제 트랜잭션을 강제 종료 10장. 데이터 조작어

45 ROLLBACK 예 10장. 데이터 조작어

46 ROLLBACK 예 10장. 데이터 조작어

47 시퀀스 시퀀스 개요 유일한 식별자 기본 키 값을 자동 생성하기 위하여 일련번호 생성하기 위한 객체
예를 들면, 웹 게시판에서 글이 등록되는 순서대로 번호를 하나씩 할당하여 기본키로 지정하고자 할때 시퀀스를 편리하게 이용 여러 테이블에서 공유 가능 10장. 데이터 조작어

48 시퀀스 생성 INCREMENT BY n : 시퀀스 번호의 증가치로 기본은 1, 일반적으로 –1 사용
START WITH n : 시퀀스 시작번호, 기본값은 1 MAXVALUE n : 생성 가능한 시퀀스의 최대값 MAXVALUE n : 시퀀스 번호를 순환적으로 사용하는 cycle로 지정한 경우, MAXVALUE에 도달한 후 새로 시작하는 시퀀스값 CYCLE | NOCYCLE : MAXVALUE 또는 MINVALUE에 도달한 후 시퀀스의 순환적인 시퀀스 번호의 생성 여부 지정 CACHE n | NOCACHE : 시퀀스 생성 속도 개선을 위해 메모리에 캐쉬하는 시퀀스 개수, 기본값은 20 26 10장. 데이터 조작어

49 시퀀스 생성 예 10장. 데이터 조작어

50 CURRVAL 과 NEXTVALUE 함수 CURVAL NEXTVAL CURVAL, NEXTVAL
시퀀스에서 생성된 현재 번호를 확인 NEXTVAL 시퀀스에서 다음 번호 생성 CURVAL, NEXTVAL INSERT, UPDATE 문에서 사용 서브쿼리, GROUP BY, HAVING, ORDER BY, DISTINCT와 함께 사용할 수 없으며, 칼럼의 기본값으로 사용할 수 없음 10장. 데이터 조작어

51 CURRVAL 과 NEXTVALUE 함수 예
10장. 데이터 조작어

52 CURRVAL 과 NEXTVALUE 함수 예
10장. 데이터 조작어

53 시퀀스를 이용한 기본 키 생성 개요 기본키로 사용할 수 있는 적절한 칼럼이 없거나 다수의 칼럼을 결합해야 식별이 가능한 경우에는 시퀀스를 이용 NEXTVAL 함수를 사용 10장. 데이터 조작어

54 시퀀스를 이용한 기본 키 생성 예 10장. 데이터 조작어

55 시퀀스 정의 변경 시퀀스 정의 변경 시퀀스 생성후 증가치, 최소 값, 최대 값 등의 정의를 수정
변경된 시퀀스 정의는 새로 생성되는 시퀀스 값부터 적용 ALTER SEQUENCE 명령문을 사용하여 변경 START WITH절은 생성 직후의 시작 값을 의미, 변경 불가 10장. 데이터 조작어

56 시퀀스 삭제 시퀀스 삭제시 DROP SEQUENCE 명령문 사용 10장. 데이터 조작어