4/28 - 시퀀스, 사용자 권한, 롤 관리, PL/SQL

시퀀스

---

• 오라클 데이터베이스에서 특정 규칙에 맞는 연속 숫자를 생성하는 객체

-- 현재 시퀀스 확인
select seq_dept_sequence.currval from dual;

-- 다음 시퀀스 생성
select seq_dept_sequence.nextval from dual;

출력 결과

NEXTVAL
		  1
		  
-- 4번 더 실행 시
NEXTVAL
		  5

• 시퀀스 데이터 넣어보기

create table dept_sequence
    as select *
    from dept
    where 1<>1;

select * from user_sequences;

insert into dept_sequence (deptno, dname, loc) values (seq_dept_sequence.nextval, 'DATABASE', 'SEOUL');
select * from dept_sequence;

출력 결과

SEQ_DEPT_SEQUENCE	1	999999	1	N	N	20	21

19	DATABASE	SEOUL

제약 조건

---

• 종류
  • NOT NULL
    • 지정한 열에 NULL을 허용하지 않음
    • NULL을 제외한 데이터의 중복 허용
  • UNIQUE
    • 지정한 열이 유일한 값을 가져야 함
    • NULL 값은 중복에서 제외
    • 중복될 수 없음
  • PRIMARY KEY
    • 저정한 열이 유일한 값이면서, NULL을 허용하지 않음
    • 테이블에 하나만 지정 가능
  • FOREIGN KEY
    • 다른 테이블의 열을 참조하여 존재하는 값만 입력 가능
  • CHECK
    • 설정한 조건식을 만족하는 데이터만 입력 가능
• RENAME
  • 제약 조건 이름 변경
• DROP
  • 제약 조건 삭제

데이터 무결성

---

• 영역 무결성
  • 열에 저장되는 값의 적정 여부 확인
  • 적절한 형식의 데이터, NULL여부 같은 정해 놓은 범위를 만족하는 데이터임을 규정
• 개체 무결성
  • 테이블 데이터를 유일하게 식별할 수 있는 기본 키는 반드시 값을 가지고 있어야 함
  • NULL, 중복 불가
• 참조 무결성
  • 참조 테이블의 외래 키 값은 참조 테이블의 기본 키로서 존재
  • NULL 가능

Primary Key (PK)

---

• 유일하게 하나만 있는 값
• UNIQUE와 NOT NULL의 제약 조건 특성을 모두 가짐
• INDEX도 적용되어 있음

Foreign Key (FK)

---

• 서로 다른 테이블 간 관계를 정의하는 데 사용하는 제약 조건

Default

---

• 기본 값을 지정할 때 사용

사용자

---

• 데이터 베이스에 접속하여 데이터를 관리하는 계정

스키마

---

• 데이터 베이스에서 데이터 간의 관계, 구조, 제약 조건 등 데이터를 저장 및 관리하기 위해 정의한 데이터베이스 구조의 범위
• 데이터 베이스 구조

사용자 생성

---

• 사용자 생성 후 CONN 사용

SQL> create user oraclestudy
  2  identified by oracle;

User created.

SQL> conn oraclestudy/oracle
ERROR:
ORA-01045: user ORACLESTUDY lacks CREATE SESSION privilege; logon denied

• 권한 주고 다시 conn 사용

SQL> grant create session to oraclestudy;

Grant succeeded.

SQL> conn oraclestudy/oracle
Connected.
SQL>

Alter

---

• 사용자 정보(패스워드) 변경

Drop

---

• 사용자 삭제
• CASCADE
  • 사용자와 객체 모두 삭제

GRANT

---

• 권한 부여

REVOKE

---

• 권한 회수

롤

---

• 여러 종류의 권한을 묶어 놓은 그룹
• 사전 정의된 롤
  • CONNECT 롤
    • 사용자가 데이터베이스에 접속하는 데 필요한 CREATE SESSION 권한을 가지고 있음
  • RESOURCE 롤
    • 사용자가 테이블, 시퀀스를 비롯한 여러 객체를 생성할 수 있는 기본 시스템 권한을 묶어놓은 롤
  • DBA 롤
    • 데이터베이스를 관리하는 시스템 권한을 대부분 가지고 있음
  • 사용자 정의 롤
    • 사용자가 필요에 의해 직접 권한을 포함시킨 롤

PL/SQL

---

• 오라클에서만 사용할 수 있는 프로그래밍 언어
• 종류
  • DECLARE (선언부)
  • BEGIN (실행부)
  • EXCEPTION (예외처리부)

set serveroutput on
begin
    dbms_output.put_line('Hello PL/SQL!');
end;

실행 결과

Hello PL/SQL!

PL/SQL 프로시저가 성공적으로 완료되었습니다.

• Select 한 결과를 커서를 통해 출력

-- Select 한 결과를 커서를 통해 출력
declare
    cursor cur is select empno, ename from emp;
begin
    for r in cur loop
        dbms_output.put_line(r.empno || r.ename);
    end loop;
end;

출력 결과

7369SMITH
7499ALLEN
7521WARD
7566JONES
7654MARTIN
7698BLAKE
7782CLARK
7788SCOTT
7839KING
7844TURNER
7876ADAMS
7900JAMES
7902FORD
7934MILLER

PL/SQL 프로시저가 성공적으로 완료되었습니다.

프로시저

---

• 어떤 작업처리를 위해 미리 만들어 놓은 PL/SQL

-- 프로시저
-- 어떤 작업처리를 위해 미리 만들어 놓은 PL/SQL
-- emp_temp2의 모든 데이터를 삭제하는 프로시저 생성(교체)

create or replace PROCEDURE deleteEmp
is 
begin 
    delete from emp_temp2
    commit;
end;

-- 위 프로시저 실행
execute deleteEmp;

-- 매니저에 해당하는 job을 삭제
create or replace PROCEDURE deleteJob(vjob varchar2)
is 
begin 
    delete from emp_temp where job = vjob;
    commit;
end;

-- 위 프로시저 실행
execute deletejob('MANAGER');

-- 프로시저와 함수의 차지 (리턴 존재)
-- 사원번호를 입력받아 해당 연봉을 리턴
create or replace function getAnnSal(vempno in number)
return number
is
    vsal number;
    vcomm number;
begin
    select sal, comm into vsal, vcomm
    from emp where empno = vempno;
    return vsal*12 + nvl(vcomm,0);
end;

-- 함수 실행
variable annSal number;
execute :annSal := getAnnSal(7499);
print annSal;

출력 결과

Function GETANNSAL이(가) 컴파일되었습니다.

PL/SQL 프로시저가 성공적으로 완료되었습니다.

    ANNSAL
----------
     19500

트리거

---

• 특정 상황, 문장이 발생되면 자동으로 실행되는 객체

-- 직원이 입사하면 (emp2에 insert) 자동으로 출력
create or replace trigger emp_trigger
after insert on emp_temp2
begin
    dbms_output.put_line('입사를 축하합니다.');
end;

insert into emp_temp2 (empno, ename, job,mgr, hiredate, sal, comm,deptno) values (7777,'jiwon','clerk',7902,sysdate,100,30,0);

출력 결과

입사를 축하합니다.

1 행 이(가) 삽입되었습니다.

인덱스

---

• 검색 속도 향상을 위해 사용
• B-Tree 알고리즘 사용
• 장점
  • 검색 속도 향상
  • 시스템 부하 감소
  • 성능 향상
• 단점
  • insert/update/delete 성능 저하
  • 인덱스 생성 시간 소요
  • 추가 공간 필요
• 실행 계획 확인 해보기

-- 실행 계획 (explain plan)
-- 실행 계획 (explain plan)
explain plan for select * from emp where ename = 'SMITH';

select * from table(dbms_xplan.display);

출력 결과

설명되었습니다.
Plan hash value: 3956160932
 
--------------------------------------------------------------------------
| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |      |     1 |    87 |     3   (0)| 00:00:01 |
|*  1 |  TABLE ACCESS FULL| EMP  |     1 |    87 |     3   (0)| 00:00:01 |
--------------------------------------------------------------------------
 
Predicate Information (identified by operation id):
---------------------------------------------------
 
   1 - filter("ENAME"='SMITH')
 
Note
-----
   - dynamic sampling used for this statement (level=2)

• TABLE ACCESS FULL
  • 테이블 전체를 조회
  • 엄청 느려짐
• Cost
  • 자원
• Row
  • 행 갯수
  • 필터링 여부
• 인덱스 생성 후 실행 계획 실행

-- emp의 ename에 인덱스 생성
create index idx_emp on emp(ename)

출력 결과

Index IDX_EMP이(가) 생성되었습니다.

Plan hash value: 306890541
 
---------------------------------------------------------------------------------------
| Id  | Operation                   | Name    | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |         |     1 |    87 |     2   (0)| 00:00:01 |
|   1 |  TABLE ACCESS BY INDEX ROWID| EMP     |     1 |    87 |     2   (0)| 00:00:01 |
**|*  2 |   INDEX RANGE SCAN          | IDX_EMP |     1 |       |     1   (0)| 00:00:01 |**
---------------------------------------------------------------------------------------
 
Predicate Information (identified by operation id):
---------------------------------------------------
 
   2 - access("ENAME"='SMITH')
 
Note
-----
   - dynamic sampling used for this statement (level=2)

• INDEX RANGE를 통해 인덱스를 탄 것을 알 수 있음
• 일단 인덱스를 태워야 빨라질 수 있다.
• 인덱스를 적용 시키는 위치 (속성, 열)
  • where 조건 절에 자주 사용되는 컬럼
  • 조인 조건
  • 복합 인덱스 (두 개 이상의 컬럼)
  • 날짜, 정수, 문자 (고정 길이) → 인덱스가 잘 걸림
  • PK, UNIQUE 제약 조건은 자동 생성
  • 값을 가공하기 전 상태에서 조건 지정
  • 모델링(설계) 시 고민 필요
  • Like 검색에서는 잘 안 먹으니 유의
  • Order By
  • 인덱스 스캔 범위 (검색 순서)
  • table full scan (random access) 줄이기
• 복합 인덱스

-- 복합 인덱스
create index idx_emp3 on emp(ename, mgr);
select * from emp where ename = 'WARD' and mgr = 7698;

• 인덱스 가공 (substr)

-- 가공 금지 (가공 전 적용)
EXPLAIN PLAN FOR
select * from emp where substr(ename,1,1) = 'W'; -- 인덱스 미적용 
SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY);

Plan hash value: 3956160932
 
--------------------------------------------------------------------------
| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |      |     1 |    87 |     3   (0)| 00:00:01 |
|*  1 |  TABLE ACCESS FULL| EMP  |     1 |    87 |     3   (0)| 00:00:01 |
--------------------------------------------------------------------------
 
Predicate Information (identified by operation id):
---------------------------------------------------
 
   1 - filter(SUBSTR("ENAME",1,1)='W')
 
Note
-----
   - dynamic sampling used for this statement (level=2)

• TABLE ACCESS FULL 을 확인할 수 있음 (인덱스 미적용)
• 인덱스 가공 X

EXPLAIN PLAN FOR
select * from emp where ename like 'W%'; -- 인덱스 적용
SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY);

Plan hash value: 306890541
 
---------------------------------------------------------------------------------------
| Id  | Operation                   | Name    | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |         |     1 |    87 |     2   (0)| 00:00:01 |
|   1 |  TABLE ACCESS BY INDEX ROWID| EMP     |     1 |    87 |     2   (0)| 00:00:01 |
|*  2 |   INDEX RANGE SCAN          | IDX_EMP |     1 |       |     1   (0)| 00:00:01 |
---------------------------------------------------------------------------------------
 
Predicate Information (identified by operation id):
---------------------------------------------------
 
   2 - access("ENAME" LIKE 'W%')
       filter("ENAME" LIKE 'W%')
 
Note
-----
   - dynamic sampling used for this statement (level=2)

• 인덱스가 적용된 것을 볼 수 있음

데이터 모델링 (설계)

---

• 현실 세계 사건(대상)들을 데이터화 하는 과정
• 현실은 너무 복잡해서 추상화 (개념화) 해서 단순하게 표현
• 모델링을 하려면 업무 프로세스 이해 필요
• 추상화 → 상세화
• 추상화, 단순화, 명확(상세)화
• 모델링 순서 (개논물)
  • 개념적 모델링 → 논리적 모델링 → 물리적 모델링
• 개념적 모델링
  • 현실의 사건 (대상) 데이터적으로 표현
  • 엔티티와 속성 도출
  • 산출물은 **ERD**를 만드는 과정
• 논리적 모델링
  • 개념적 → 논리적 변환
  • 식별자, 릴레이션(관계) 정의
  • 정규화
• 물리적 모델링
  • 논리적 → 물리적
  • 실제 DB에 생성
    • 엔티티 - 테이블
    • 속성 - 컬럼
    • 릴레이션 - FK
• 엔티티 (테이블)
  • 개념, 객체, 행위(사건)
  • 유일한 식별자 존재
  • 2개 이상의 인스턴스
• 속성
  • 데이터의 가장 작은 단위
  • 특징, 상태, 값
  • 단일/복합/다중 값 속성
    • 단일 - 하나의 값
      • 이름, 가격…
    • 복합 - 여러 개가 섞인 것
      • 하나의 속성에서 여러 하위 속성
      • 거리, 도시, 우편 번호…
      • 서울시 마포구 XXX동 XXX번지
    • 다중 - 중복된 것
      • 하나의 엔티티가 여러 값
      • 이메일, 전화 번호(고객 1명이 여러 전화번호..)….
      • 홍길동 낚시|게임|독서
      • 김길동 영화|독서|낮잠
    • 기본
      • 직접적으로 저장되는 값
      • 이름, 나이, 연락처 …
    • 설계
      • 속성의 정의와 이를 어떻게 설계할 것인지
    • 파생
      • 다른 속성 값을 바탕으로 계산

관계 (Relationship)

---

• 엔티티간의 관계
• 존재관계
  • 상/하, 부모/자식, 사원/부서
• 행위(사건)관계
  • 주문, 수강 신청
• 관계 차수 (차수 : Cardinality)
  • 1:1 (일대일)
    • 고객 - 주소
  • 1:N (일대다)
    • 고객 - 주문
  • N:M (다대다)
    • 상품 - 주문ERD

      ---

ERD

---

 

 

• 비 식별 관계는 점선으로

식별자 (Identify)

---

• 식별(구분) 할 수 있는 값
• PK, FK

정규화 (Normalization)

---

• 데이터의 일관성, 중복, 유연성을 위해 데이터를 분해하는 과정
• 장점
  • 중복 최소화
  • 비즈니스 변경에 따른 모델의 변경 최소화
  • 보통 3차 까지 수행
• N차 정규화가 끝난 자료 → N차 정규형
• 정규화 단계
  • 1차 - PK 지정
  • 2차 - 기본키가 2개 이상 분해
  • 3차 - 기본키를 제외한 컬럼 간 종속관계 발생
  • 4차 - 여러 컬럼이 하나의 컬럼을 종속 (다중값)
  • 5차 - 조인에 의해 종속성 발생
  • BCNF - 서로 중첩된 것
    • EX) 교수 - 학생 전공이 중첩
• 조인
  • 장점
    • 중복 감소
    • 유연성
  • 단점
    • 복잡
    • 조회 시 조인을 해야하는 경우가 많이 생김
    • 리소스 사용율 상승

반정규화 (Dnomalization)

---

• 중복을 허용
• 조인을 줄임
• 성능 향상
• 언제? (조회용 테이블 따로 만듬, 뷰와 비슷)
  • 특정 범위만 조회하는 경우
  • 요약/집계 → 결과가 저장된 테이블 생성
  • 계산된 값(컬럼)
  • 테이블 분할(수직/수평)

메모

---

• 이미 NULL이 있는데 해당 태이블을 NOT NULL로 바꿀 경우 오류가 남
• 권한 부여 - grant
• 권한 회수 - revoke