29일차: Oracle – 데이터딕셔너리/DML/TCL/제약조건 2021.07.23

목차

1.     데이터 딕셔너리

2.     DML SQL문

3.     TCL SQL문

4.     데이터 무결성 제약 조건

 

1. 데이터 딕셔너리와 데이터 딕셔너리 뷰 <9장>               → ★2021_07_23_01 참고

 

• 오라클의 객체

- 테이블, 뷰, 시퀀스, 인덱스, 동의어, 프로시저, 트리거

 

• 뷰

- 기본 테이블을 관리하는 가상 테이블

 

• 데이터 딕셔너리

- 데이터 딕셔너리(시스템 테이블) : system의 DB, USER 테이블

- 데이터베이스 자원을 효율적으로 관리하기 위한 다양한 정보를 저장하는 시스템 테이블

- 데이터 딕셔너리는 사용자가 테이블을 생성하거나 사용자를 변경하는 등의 작업을 할 때 데이터베이스 서버에 의해 자동으로 갱신되는 테이블

- 사용자는 데이터 딕셔너리의 내용을 직접 수정하거나 삭제할 수 없음

- 데이터 딕셔너리를 사용자가 조회해 보면 시스템이 직접 관리하는 테이블이기에 암호 같은 기호만 보여질 뿐 내용을 알 수 없음

 

• 데이터 딕셔너리 뷰

- 오라클은 데이터 딕셔너리의 내용을 사용자가 이해할 수 있는 내용으로 변환하여 제공

- 데이터 딕셔너리 뷰는 접두어 따라 다음의 세 종류가 있음

- xxxx에는 자기 소유의 객체가 복수 형태로 들어감 (ex. user_tables)

 

 

- DBA_XXXX : select * from dba_tables;     → SYSTEM 계정 / SYSTEM 계정 객체 조회

                   select * from dba_users;      → SYSTEM 계정 / 오라클 시스템의 계정 정보 검색

- ALL_XXXX : select * from all_tables;        → SCOTT 계정 / 자기 계정(SCOTT) 소유 또는 권한을

                                                                              부여받은 객체 등의 관한 정보를 조회

- USER_XXXX : select * from user_tables;   → SCOTT 계정 / SCOTT 계정 소유의 테이블 등 조회

 

2. DML SQL문 <10장>                      → ★2021_07_23_01 참고

 

• DML (Data Manipulation Language) : 데이터 조작어

 

1) Insert SQL문

- 데이터 생성 명령어

 

<방법 1 : 일반적인 방식>

• 형식 1) insert into 테이블명(컬럼1, 컬럼2, ...) values(데이터1, 데이터2, ...);

            ex) insert into dept01(deptno, dname, loc) values(10, 'ACCOUNTING', 'NEW YORK');

• 형식 2) insert into values(데이터1, 데이터2, ...);

            ex) insert into dept01 values(20, 'RESEARCH', 'DALLAS');

 

• NULL 값 삽입

1) 컬럼 누락

 ex) insert into dept01(deptno, dname) values(40, '개발부');

 2) 명시적으로 null 입력

 ex) insert into dept01 values(50, '기획부', null);

 

<방법 2 : 서브 쿼리로 데이터 입력>

insert into dept02 select * from dept;   

→ 서브 쿼리로 테이블 생성할 때는 as를 쓰지만, insert에서는 as 쓰지 않음

insert into dept02 select * from dept02;   

→ 짧은 시간 안에 많은 데이터 입력 가능

 

<방법 3 : insert all 명령문으로 다중 테이블에 데이터 입력>

insert all

    into emp_hir values(empno, ename, hiredate)

    into emp_mgr values(empno, ename, mgr)

    select empno, ename, hiredate, mgr from emp where deptno=20;

 

2) update SQL문

- 데이터 수정 명령어

- 형식 : update 테이블명 set 컬럼1 = 수정할 값1,

                                     컬럼2 = 수정할 값2

                                 where 조건절;

 

① 특정 데이터 수정

Q. 모든 사원들의 급여를 10% 인상

update emp01 set sal=sal*1.1;

 

② 모든 데이터 수정

Q. 급여가 3000 이상인 사원만 급여를 10% 인상

update emp02 set sal = sal*1.1 where sal >= 3000;

 

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

Q. 20번 부서의 지역명(DALLAS)을 40번 부서의 지역명(BOSTON)으로 수정

update dept01 set loc = (select loc from dept01 where deptno=40)

    where deptno = 20;

 

3) delete SQL문

- 데이터 삭제 명령어

- 형식 : delete from 테이블명 where 조건식;

 

① 모든 데이터 삭제

delete from dept01;

 

② 특정 데이터 삭제 : 조건을 만족하는 데이터 삭제

delete from dept01 where deptno=30;

 

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

Q. 사원 테이블(EMP02)에서 부서명이 SALES인 부서인 사원을 삭제

delete from emp02 where deptno =

   (select deptno from dept where dname='SALES');

 

4) 테이블을 합병하는 MERGE

- 구조가 같은 두개의 테이블을 하나의 테이블로 합치는 기능

- merge 명령을 수행할 때 기존에 존재하는 행(row)이 있으면 새로운 값으로 수정(update)되고, 존재하지 않으면 새로운 행(row)으로 추가(insert)된다.

 

1. create table emp01 as select * from emp;                                 → 14개

2. create table emp02 as select * from emp where job='MANAGER';  → 3개

3. update emp02 set job='Test';

4. insert into emp02 values(8000, 'SONG', 'top', 7566, '2018/02/22', 1200, 10, 20);

5. select * from emp01;

   select * from emp02;

6. merge into emp01

         using emp02

         on(emp01.empno = emp02.empno)

         when matched then

              update set emp01.ename = emp02.ename,

                         emp01.job = emp02.job,

                         emp01.mgr = emp02.mgr,

                         emp01.hiredate = emp02.hiredate,

                         emp01.sal = emp02.sal,

                         emp01.comm = emp02.comm,

                         emp01.deptno = emp02.deptno

         when not matched then

              insert values(emp02.empno, emp02.ename, emp02.job,                        

                         emp02.mgr,emp02.hiredate,

                         emp02.sal, emp02.comm,emp02.deptno);

                        

7. select * from emp01;  → 합병된 결과 확인

 

3. TCL SQL문 <11장>             → ★2021_07_23_02 참고

 

1) 트랜잭션의 이해

• Transaction Control Language (TCL) : 트랜잭션 처리어

- Transaction (트랜잭션) : 하나의 논리적인 작업 단위

- 하나의 트랜잭션은 All-OR-Nothing 방식으로 처리됨(완전하게 처리되거나 아예 안되거나)

- 데이터의 일관성을 유지하면서 안정적으로 데이터를 복구시키기 위해 사용

 

2) 트랜잭션 제어 명령어

• commit : 트랜잭션을 종료시키는 명령어

• rollback : 트랜잭션을 취소시키는 명령어

• savepoint : 복구할 시점(저장점)을 지정하는 명령어

 

① COMMIT

- 트랜잭션을 종료

- commit 전까지 DML SQL문은 컴퓨터의 메모리 상에서만 업데이트

- commit 명령 단계에서 메모리 상에서 실행했던 명령이 데이터베이스(DB) 상에 반영되면서 거래 종료(롤백 불가)

- ex) delete from dept01 where deptno=20;

       commit;     → 트랜잭션 종료

       rollback;     → 트랜잭션이 종료되었기 때문에 삭제된 20번 데이터는 복구 불가

 

• 자동 커밋 : 자동으로 커밋 실행

1) 정상적인 종료 : quit, exit, con.close()

2) DDL (create, alter, rename, drop, truncate), DCL (grant, revoke) 명령이 실행되었을 때

 

delete from dept01 where deptno=40;     

→ 40번 데이터 삭제

 

create table dept03 as select * from dept; 

→ create 명령어로 테이블 생성 (DDL)자동 커밋 실행(DDL)

 

rollback;

select * from dept01;              

→ 삭제된 40번 데이터 복구 안 됨

 

 

 

• SQL Developer 프로그램 내의 버튼을 통해서도 커밋과 롤백 가능

 

② ROLLBACK

- 트랜잭션을 취소

- 형식: rollback;

 

• 자동 롤백 : 자동으로 롤백 실행

- 비정상적인 종료(강제로 창을 닫는 경우, 컴퓨터가 다운되는 경우)를 하는 경우

 

③ SAVEPOINT

- 복구할 시점(저장점)을 지정

 

 

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

① SQL*PLUS가 정상 종료되었다면 자동으로 commit 되지만, 비정상 종료되었다면 자동으로 rollback 됨

② DDL과 DCL 명령문이 수행된 경우 자동으로 commit 됨

③ 정전이 발생했거나 컴퓨터 Down시(컴퓨터의 전원이 끊길 시) 자동으로 rollback 됨

④ 오라클과 연동된 프로그램(ex. 이클립스)에서는 자동으로 commit됨

 

4. 데이터 무결성 제약 조건 <13장>             → ★2021_07_23_02 참고

 

• 무결성 제약 조건

- 테이블에 부적절한 데이터가 입력되는 것을 방지하기 위해서 테이블을 생성할 때, 각 컬럼에 대해서 정의하는 여러가지 규칙을 의미

- ex) not null, unique, primary key, foreign key, check, default

 

1) NOT NULL 제약 조건

- null 값을 허용하지 않는다.

- 반드시 값을 입력해야 한다.

- ex)

      emp02 테이블 생성

      create table emp02 ( empno number(4) not null,

                                  ename varchar2(12) not null,

                                  job varchar2(12),

                                 deptno number(2) );

 

      emp02 테이블에 정상적으로(제약 조건에 위배되지 않도록) 데이터 입력

      insert into emp02 values(1111, '홍길동', 'MANAGER', 30);

 

      제약 조건(not null)에 위배

      insert into emp02 values(null, null, 'SALESMAN', 30);   → 오류 발생

 

2) UNIQUE 제약 조건

- 유일한 값만 입력할 수 있다.

- 중복된 값을 입력할 수 없다.

- null 값은 입력할 수 있다.

- ex)

      emp03 테이블 생성

      create table emp03( empno number(4) unique,

                                 ename varchar2(12) not null,

                                 job varchar2(12),

                                deptno number(2) );

 

      emp03 테이블에 정상적으로(제약 조건에 위배되지 않도록) 데이터 입력

      insert into emp03 values(1111, '유재석', '개발자', 10);

      select * from emp03;

 

      제약 조건(unique)에 위배

      insert into emp03 values(1111, '조세호', '개발자', 20);   → 오류 발생

 

      unique 제약 조건이 있는 컬럼에 null 값 입력 가능

      insert into emp03 values(null, '조세호', '개발자', 20);    → 입력 성공

      insert into emp03 values(null, '김땡땡', 'PD', 30);         → null 값 중복 가능