SQL TCL, DCL과 객체

1. TCL : 트랜잭션 제어어

- 트랜잭션

업무를 수행하기 위한 최소 수행 단위

 

ex) 샤워를 하나의 업무로 볼 때

1. 샴푸

2. 트리트먼트 바르고 5분 방치

3. 양치하기

4. 세수하기

5. 바디샤워

 

 

1.1 트랜잭션의 필요성

- 송금 트랜잭션

1. LLL의 계좌 잔액 1000원 이상인지 확인하기

2. LLL 계좌에 1000원 차감하기

3. AAA 계좌에 1000원 추가하기

4. 송금 완료 COMMIT

 

- 계좌 생성하기

CREATE TABLE 카카오뱅크(
    계좌번호 VARCHAR2(50),
    이름 VARCHAR2(12),
    잔액 NUMBER
);

INSERT INTO 카카오뱅크 VALUES('1234-123-1234', 'LLL', 5000);

INSERT INTO 카카오뱅크 VALUES('2222-222-1234', 'AAA', null);
COMMIT;

SELECT*FROM 카카오뱅크;

 

- 송금하기

-- 1. 잔액확인
SELECT*FROM 카카오뱅크
WHERE 이름 = 'LLL' AND 잔액>= 1000;

-- 2. LLL 계좌 1000 차감
UPDATE 카카오뱅크 SET 잔액 = 4000
WHERE 이름 = 'LLL';

-- 3. AAA 계좌 1000 추가
UPDATE 카카오뱅크 SET 잔액 = 1000
WHERE 이름 = 'AAA';

-- 4. 송금완료
COMMIT;

 

- 만약 2번까지 실행하고 전상상 오류가 생긴다면?

 

1. 잔액확인

2.LLL 계좌 1000 차감

3. AAA계좌 1000 추가(오류발생)

4. 송금완료

 

1000원의 데이터의 유실 발생!

데이터 유실을 막으려면 '송금' 트랙잭션 단위로 실행

중간에 오류가 나면 '송금' 실행 전으로 모든 작업 취소

 

 

1.3 트랜잭션의 4가지 특성

- 원자성(Aotomicity) : All or Nothing 모두 실행되거나 전혀 실행되지 않거나 

- 일관성(Consistency) : 언제나 일관성 있는 상태로 유지하는 것

- 고립성(Isolation) : 트랜잭션 실행 시 다른 트랜잭션의 영향을 받지 않는 것

- 지속성(Durability) : 성공적인 수행된 트랜잭션은 영원히 반영되는 것

 

 

1.4 COMMIT/ROLLBACK

- COMMIT

수행한 트랜잭션을 데이터베이스에 영구적으로 반영할 때 사용

마지막 COMMIT시점 이후 실행한 트랜잭션 결과를 저장 

 

- ROLLBACK

실행한 트랜잭션 결과에 대해 취소하고 싶을 때 사용

마지막 COMMIT 시점까지만 복구 가능

 

ROLLBACK;
-- 롤백을 하면 마지막 COMMIT시점으로 돌아가서 INSERT 했던 값이 사라진다.
-- DML (INSERT, UPDATE, DELETE)은 테이블에 바로 영구반영이 되지 않는다. 꼭 COMMIT 필요!
-- DROP, TURNCATE는 복구 불가능!

 

 

1.5 SESSION

데이터베이스에 접속해 있는 연결 상태

SQL DEVELOER를 2개 사용하면 세션 2개가 연결된 상태

 

- 읽기 일관성

트랜잭션이 완료되기 전까지 데이터를 직접 조작하는 SESSION 외에 다른 SESSION에서는 데이터 조작 전 상태의 내용이 일관적으로 조회/출력/검색되는 특성 확정된 데이터만 검색됨(읽기 일관성 보장)

 

 

1.6 LOCK

특정세션에서 조작중일 때 트랙잭션이 완료(COMMIT, ROLLBACK)를 하기 전까지 다른 세션에서 해당 데이터를 조작할 수 없는 상태

 

둘 이상 세션이 같은 행을 조작하려고 할 때 충돌하는 현상

LOCK 상태

서로 다른 행을 조작하면 LCOK 발생 X

UPDATE 카카오뱅크 SET 잔액 = 10000
WHERE 이름 = 'LLL';
(2번 세션에서 LLL 변경 시도)
commit; (커밋 전 까지 2번 세션에서 LLL 변경을 할 경우 LOCK 발생)

 

2. DCL

 

- 사용자 생성

CREATE USER 사용자이름
IDENTIFIED BY 비밀번호

 

SQLPLUS로 접속하여 SYTEM 관리자계정으로 접속 후 DCLTEST 계정 생성

SQL> CONN SYSTEM/12345;
Connected.

SQL> CREATE USER DCLTEST IDENTIFIED BY 12345;

User created.

 

- GRANT

GRANT 시스템 권한
TO USER
WITH ADMIN OPTION 받은 권한을 재부여 할 수 있는 옵션

 

- REVOKE

REVOKE 시스템 권한
FROM USER

 

- ROLE

CONNECT DB접속에 필요한 권한
RESOUCE 테이블, 시퀀스 등 객체 생성 권한
DBA DB를 관리하는 대부분의 시스템 권한

 

 

2.1 스키마

데이터 베이스에 저장되는 데이터 구조와 제약조건 등을 정의한 구조

테이블 뷰 인덱스 시퀀스 등 HR계정의 모든 객체는 HR의 스키마

--원래는 다음처럼 스키마.테이블.컬럼 형태로 접근 해야 되지만
SELECT HR.EMPLOYEES.EMPLOYEE_ID
FROM HR.EMPLOYEES

--자동으로 접속한 계정의 스키마로 접근 가능
SELECT EMPLOYEE_ID
FROM EMPLOYEES

- DCLTEST 계정에게 직원테이블 조회하고 데이터 수정할 권한 부여

SQL> GRANT SELECT, UPDATE
  2  ON HR.EMPLOYEES
  3  TO DCLTEST;

 

 

3. 객체

데이터베이스 내에 존재하는 논리적인 저장 구조

 

- 테이블 : 데이터를 저장하는 객체

- 뷰 : 테이블인 것처럼 사용하는 객체

- 인덱스 :  테이블에 있는 데이터를 빠르게 찾는 객체

- 시퀀스 :  일련번호를 부여할 때 사용하는 객체

- 함수:  특정 연산을 하고 값을 반환하는 객체

- 프로시저 : 함수와 비슷하지만 값을 반환하지 않는 객체

 

 

3.1 뷰 VIEW

가상의 테이블

하나 이상의 테이블을 조회하는 SELECT문을 저장하는 객체

SELECT문을 저장하기 때문에 물리적 데이터 저장은 X

 

- 뷰 사용 목적

편의성 : 자주 사용하는 SELECT 문장 저장하기 위해 사용

보안성 : 테이블의 특정 데이터를 을 노출 시키지 않기 위해 사용

 

CREATE VIEW 뷰 이름

AS 저장할 SELECT문

 

DROP VIEW 뷰이름 ( 뷰삭제)

CREATE VIEW 뷰 이름
AS 저장할 SELECT문

DROP VIEW 뷰이름 ( 뷰삭제)

--로그인 정보가 들어 있는 뷰 만들어보기
CREATE VIEW 로그인정보 AS
SELECT 회원ID, 비밀번호 FROM 네이버회원;

-- 뷰 조회
SELECT * FROM 로그인정보;
DROP VIEW 로그인정보;

 

- 실습

부서별 최고급여 뷰를 만들고 해당뷰를 사용하여 부서별로 가장 높은 연봉을 가진 직원들의 정보 출력 
부서 ID,최고 급여 , 부서ID기준으로 정렬, 최고급여 별칭 붙이기

DROP VIEW 부서별최고급여;
CREATE VIEW 부서별최고급여 as
SELECT department_id, max(salary) as 최고급여
FROM employees
GROUP BY department_id 
ORDER BY department_id;

 

부서별로 최고급여를 받는 직원들의 정보 출력 (이름, 급여, 부서ID, 부서별 최고 급여)
부서별 최고급여 >> 뷰 부서별 최고급여
원하는 정보가 서로 다른 테이블에 있기 때문에 JOIN를 해야 한다.
join 조건 부서 ID 일치,

SELECT first_name, salary, E.department_id, 최고급여
FROM employees E
     , 부서별최고급여 DMAX
WHERE E.department_id = DMAX.department_id 
and E.salary = DMAX.최고급여;

 

 

3.2 시퀀스

특정 규칙에 맞는 숫자를 생성해 주는 객체, 대기순번표나 직원 ID

 

CREATE SEQUENCE 직원ID_NUM
START WITH 100 -- 시작숫자
INCREMENT BY 2 -- 증감숫자
MAXVALUE 120 -- 최대값
MINVALUE 100 -- 최소값
NOCYCLE -- 반복여부
;

시퀀스이름.CURRVAL -- 마지막 번호 반환
시퀀스이름.NEXTVAL -- 다음 번호 반환

 

 

3.3 ROWNUM

임시 행번호

행의 개수를 제한할 때 사용

SELECT ROWNUM, employee_ID, First_name
FROM Employees
Where rownum <= 10;

ROWNUM는 무조건 1을 포함해서 가지고 와야 한다.

 

- 급여가 높은 순서대로 상위 5명 직원 출력

SELECT FIRST_NAME, salary
FROM employees
Where rownum <= 5
ORDER BY salary DESC;

다음 SQL 문장은 제대로 실행되지 않는다.
WHY 실행 순서가 FROM WHERE SELECT ORDER BY라서
정렬이 되기 전엔 행의 개수를 제한(ROWNUM)하기 때문에
행을 5개로 먼저 제한 후 그 안에서 정렬
원하는 대로 출력을 하려면 인라인 뷰 (이미 정렬이 되어있는 테이블)를 활용

SELECT *
FROM (SELECT FIRST_NAME, salary
        FROM employees
        WHERE salary is not null
        ORDER BY salary DESC)
Where ROWNUM <= 5;

 

3.4 페이징기술

ROWNUM와 TOP-N 조합해서 원하는 게시글만 보여주는 기술

 

게시판 번호

SELECT *
FROM (SELECT *
        FROM 게시판
        ORDER BY 게시판번호)
WHERE ROWNUM <= 20;

 

게시판 번호를 기준으로 21~40개 게시글의 모든 칼럼 출력

SELECT *
FROM (SELECT ROWNUM AS RN,게시글.*
FROM (SELECT *
        FROM 게시판
        ORDER BY 게시판번호) 게시글
WHERE ROWNUM <= 40)
WHERE RN >= 21;

 

N번 게시판에서 20개씩 출력

SELECT *
FROM (SELECT ROWNUM AS RN,게시글.*
FROM (SELECT *
        FROM 게시판
        ORDER BY 게시판번호) 게시글
WHERE ROWNUM <= 20*N)
WHERE RN >= 20*(N-1)+1;

인라인뷰 1 : 게시판 번호로 먼저 정렬
인라인뷰 2 : ROWNUM의 개수를 원하는 만큼 가지고 오기 위해서 칼럼 형태로 지정