220901_ORACLE_국비_DELETE/MERGE/DCL/TRANSACTION/무결성 제약 조건

3. DELETE

• 레코드 삭제
• 형식 : delete from table_name
            where 조건절; 
• where절이 없으면 모든 레코드가 삭제됨 / commit되지 않음

 

<emp_02테이블에서 근무지가 'NEW YORK'인 사원들을 삭제>

 

확인)

10번부서 사원들이 삭제됨

 

4. MERGE

• 테이블 합병
• 구조가 같은 두 테이블을 하나의 테이블로 합치는 기능
• 기존테이블에 자료가 존재하는 경우 : 새로운 값으로 update(갱신)
  자료가 존재하지 않는 경우 : 새로운 행으로 insert(추가)
• 형식  

merge into(변경할 테이블명)            using(값을 가져올 테이블명)            on (조인 조건) when matched then                         update set                         컬럼1 = 값1,                         컬럼2 = 값2 when not matched then                        insert(컬럼1, 컬럼2..)                       values(값1, 값2)

→ 값을 변경할 테이블 선언 → 어디서 값을 가져올지 명시 → 조인 조건 명시 → 매치되는 값이 있을때                - 수정(update)                - 컬럼1에 값1 대치 → 매치되는 값이 없을때                - 새로운값 삽입

 

 

<emp_01, emp_02 병합>

기존 emp_01, emp_02)

 

병합)

 

확인)

5개행이 추가됨

---

DCL(Data Control Language)

• 데이터 제어 언어 
• 종류 : 트렌젝션 저장 및 취소하는 COMMIT, ROLLBACK
            DB권한 부여 및 삭제하는 GRANT / REVOKE

 

---

Transaction(트렌젝션)

• 데이터처리의 한 단위 / 하나의 논리적인 작업단위로 수행되는 일련의 작업
• oracle의 transaction : 여러개의 SQL 명령문을 1개의 작업 단위로 처리
• 특성 : ACID                           

ACID
Atomicity(원자성)	분리할 수 없는 1개의 단위 작업이 모두 수행되거나 수행되지 않아야한다  All or Nothing
Consistency(일관성)	트렌젝션에서 사용되는 모든 데이터는 일관되어야함
Isolation(격리성)	현재 트렌젝션이 접근하고 있는 데이터는  다른 트렌젝션으로부터 격리되어야함 (트렌젝션 완료전까지 참조 불가능)
Durability(영속성)	트렌젝션이 정상종료되면 오류가 발생하더라도 영구적으로 적용되어야함

 

• 명령어

COMMIT	트렌젝션의 처음과 끝을 나타냄 COMMIT~COMMIT까지가 Transaction
ROLLBACK	트렌젝션을 취소하고 난 뒤 처음부터 다시 시작하거나  SAVEPOINT부터 취소하고 다시 되돌리는 기능
SAVEPOINT	현재까지의 트렌젝션을 특정이름으로 지정할 때 사용

 

• commit, rollback명령어의 장점
  1. 데이터 무결성 보장
  2. 영구적인 변경 전 데이터 변경사항 확인가능
  3. 논리적 연관된 작업 그룹화 가능

 

 

1.COMMIT& ROLLBACK

COMMIT

• Transaction (insert, update, delete)작업내용을 실제 데이터에 저장
• 이전 데이터가 완전히 update됨
• 모든 사용자가 변경된 데이터 결과 확인 가능

ROLLBACK

• Transaction (insert, update, delete) 작업내용 취소
• 이전 COMMIT한 부분까지 취소함

 

자동 COMMIT과 자동 ROLLBACK이 명령되는 경우

• SQLPLUS가 정상종료되었다면 자동 COMMIT
                       비정상종료되었다면 자동 ROLLBACK
• DDL, DCL명령문이 수행된 경우 자동 COMMIT
• 정전, 컴퓨터다운 시 자동 ROLLBACK

 

 

<emp복사본 emp_03에서 10번 부서 사원 삭제 후 commit>

삭제)

 

commit)

 

확인)

다른사용자가 확인가능

 

 

<emp복사본 emp_03에서 30번 부서 사원 삭제 후 rollback>

삭제)

 

 

rollback)

 

확인)

 

 

<emp_03 자동 commit 확인>

emp_03에서 30번 부서 삭제 전)

 

삭제 후 DDL 사용)

 

확인)

자동 commit됨

 

 

2. SAVEPOINT

• 트렌젝션을 작게 분할 가능
• ROLLBACK TO SAVEPOINT를 사용해 SAVEPOINT까지 ROLLBACK가능
• 형식 : savepoint point_name      → 특정 위치 지정
            rollback to point_name;    → 특정 위치로 작업취소

 

 

<emp 복사본인 emp_03테이블 생성 후 30번,20번,10번 삭제 후 savepoint>

savepoint_1)

 

savepoint_2)

 

savepoint_3)

 

rollback to savepoint_1)

 

rollback to savepoint_2)

 

 

---

 

데이터 무결성 제약조건(Data Integrity Constraint Rule)

• 테이블에 부적절한 자료가 입력되는것을 방비하기 위해 테이블을 생성할 때 각 컬럼에 대해 정의하는 규칙들
• 무결성 : 데이터베이스 내에 있는 데이터 정확성 유지
• 제약조건 : 바람직하지 않은 데이터가 저장되는것을 방지하는것
• 특징 
  1. 테이블 생성시 무결성 제약조건을 정의하여 프로그래밍 과정을 줄여줌
  2. 데이터베이스 서버에 의해 무결성 제약조건이 관리되어 데이터 오류 발생 가능성 줄여줌
  3. 테이블에 대해 정의되고 데이터 딕셔너리에 저장됨
  4. 일시적으로 활성화 또는 비활성화 할 수 있음
• 무결성 제약 조건

무결성 제약 조건	역할
NOT NULL	NULL 허용X
UNIQUE	중복된 값 허용X  항상 유일한 값을 갖도록 함
PRIMARY KEY(기본키)	NULL 허용X 중복X NOT NULL조건, UNIQUE조건 결합된 형태
FOREIGN KEY(외래키)	참조되는 테이블의 컬럼의 값이 존재하면 허용
CHECK	저장가능한 데이터 값의 범위나 조건을 저장해 설정한 값만 허용

 

• constraint_type 제약조건 종류
  1. C : Check, NOT NULL
  2. U : UNIQUE
  3. P : PRIMARY KEY
  4. R : FOREIGN KEY

1. NOT NULL 제약조건

• 특정 컬럼은 반드시 값이 입력되도록 필수 입력 컬럼으로 지정하는것
• NOT NULL은 INSERT할때 누락되어선 안됨
• NULL값이면 오류

 

<nou null테이블 생성>

 

새로운 행 삽입_1)

tel은 null허용하기때문에 새로운 행 삽입가능

 

새로운 행 삽입_2)

 

오류)

login_pwd는 not null이기때문에 새로운 행 삽입 불가능

 

확인)

 

2. UNIQUE

<table_notnull테이블 unique사용>

 

새로운 행 삽입)

 

오류)

동일한 login_id가 있으므로 오류가 뜸

 

확인)

 

 

<제약조건 확인>

 

 

---

<예제>

dept복사본인 dept_02, emp복사본인 emp_02로 사용할 것

- update -

<dept_02테이블에서 20번 부서의 근무지을 40번 부서의 근무지으로 변경>

 

확인)

 

 

<emp_02테이블에서 'SALESMAN'의 급여는 2500, 입사일은 그제(22.08.30)날짜로 변경>

 

확인)

 

 

<emp_02테이블에서 사원 이름에 'T'가 들어가는 사원의 월급을 7000인상하고 입사일을 모레로 변경>

 

확인)

 

 

<emp_02테이블에서 'CHICAGO'에서 근무하는 사원의 입사일을 오늘(22.09.01)날짜로 변경>

 

확인)

 

 

<emp_02테이블에서 사원들 중 09월에 입사한 사원들의 월급을 1000인상>

방법1_substr)

 

방법2_instr)

 

확인)

 

 

- delete - 

<커미션이 null인 사원 삭제>

 

확인)

 

 

삭제 후 emp_02(emp복사본)생성

<emp_02테이블에서 부서별로 급여가 가장 높은 사원 삭제>

select절의 값이 1개 이상이므로 다중행 연산자 사용

 

확인)