[SQLD] 시험준비 3주 3일차 - DML4, DCL, TCL

오늘로 진도를 모두 끝내고

내일부터는 기출문제를 풀면서 오답노트를 해보자

시험 9일 남았다

 

 

 

POINT10. DECODE 와 CASE 문

 

• DECODE

- DECODE 문으로 IF문을 구현할 수 있다. 즉, 특정 조건이 참이면 A, 거짓이면 B로 응답한다.

DECODE의 구조

EMP 테이블에서 EMPNO 가 1000과 같으면 TRUE, 다르면 FALSE 를 반환해 보여준다

 

 

• CASE문

- CASE 문은 IF~THEN ~ELSE - END 의 프로그래밍 언어처럼 조건문을 사용할 수 있다.

- 조건문을 WHEN 구에 사용하고 THEN 은 해당 조건이 참이면 실행되고 거짓이면 ELSE구 가 실행된다.

CASE

EMPNO가 10이면 A를 출력하고 1001이면 B를 출력한다. 둘다 해당되지 않으면 C를 출력한다.

 

 

 

POINT11. POWNUM과 ROWID

 

• ROWNUM

- ROWNUM 은 Oracle DB 의 SELECT 문 결과에 대해서 논리적인 일련번호를 부여한다.

- ROWNUM 은 조회되느느 행 수를 제한할 때 많이 사용된다.

- ROWNUM 은 화면에 데이터를 출력할 때 부여되는 논리적 순번이다. 만약 ROWNUM을 사용해서 페이지 단위를 출력을 하기 위해서는 인라인 뷰를 사용해야 한다.

 

인라인 뷰

인라인 뷰

인라인뷰는 select 문에서 from 절에 사용되는 서브쿼리를 의미한다.

 

rownum 으로 여러 행을 조회

 

특정행의 리스트 조회 ( 웹게시판 형태 )

위의 예 처럼 rownum 과 between 구를 사용해서 웹페이지 조회를 구현할 수 있다.

 

 

• ROWID

- ROWID 는 Oracle db 내에서 데이터를 구분할 수 있는 유일한 값이다.

- ROWID 는 "SELECT ROWID, EMPNO FROM EMP" 와 같은 SELECT 문으로 확인할 수 있다.

- ROWID 를 통해서 데이터가 어떤 데이터 파일, 어느 블록에 저장되어 있는지 알 수 있다.

 

ROWID 구조

구조	길이	설명
오브젝트 번호	1~6	오브젝트 별로 유일한 값을 가지고 있으며, 해당 오브젝트가 속해 있는 값이다.
상대 파일 번호	7~9	테이블스페이스에 속해 있는 데이터 파일에 대한 상대 파일번호 이다.
블록 번호	10~15	데이터 파일 내부에서 어느 블록에 데이터가 있는지 알려준다.
데이터 번호	16~18	데이터 블록에 데이터가 저장되어 있는 순서를 의미한다.

 

ROWID

 

 

POINT.12 WITH 구문

- WITH 구문은 서브쿼리를 사용해서 임시 테이블이나 뷰처럼 사용할 수 있는 구문이다.

- 서브쿼리 블록에 별칭을 지정할 수 있다.

- 옵티마이저는 SQL을 인라인 뷰나 임시 테이블로 판단한다.

with 구문 사용

emp 의 list 칼럼 중 3~7인 것의 행들을 viewdata 라는 임시테이블에 서브쿼리를 사용해 저장

이후 임시테이블 전체 조회 한 결과

 

 

 

POINT.13 DCL (Data Control Language)

 

• GRANT

- GRANT 문은 DB 사용자에게 권한을 부여한다.

- DB사용을 위해선느 권한이 필요하며 연결, 입력, 수정, 삭제, 조회를 할 수 있다.

grant문

privileges 는 권한을 의미하며 object 는 테이블명이다.

user 는 oracle db 사용자를 지정하면 된다.

 

Privileges(권한)

권한	설명
SELECT	지정된 테이블에 대해 SELECT 권한을 부여한다.
INSERT	지정된 테이블에 대해서 INSERT 권한을 부여한다.
UPDATE	지정된 테이블에 대해서 UPDATE 권한을 부여한다.
DELETE	지정된 테이블에 대해서 DELETE 권한을 부여한다.
REFERENCES	지정된 테이블을 참조한느 제약조건을 생성하는 권한을 부여한다.
ALTER	지정된 테이블에 대해서 수정할 수 있는 권한을 부여한다.
INDEX	지정된 테이블에 대해서 인덱스를 생성할 수 있는 권한을 부여한다.
ALL	테이블에 대한 모든 권한을 부여한다.

GRANT 예시

JOODASA 사용자에게 EMP 테이블의 SELECT, INSERT, UPDATE, DELETE 권한을 부여함

 

WITH GRANT OPTION

GRANT 옵션	설명
WITH GRANT OPTION	- 특정 사용자에게 권한을 부여할 수 있는 권한을 부여한다. - 권한을 A 사용자가 B에 부여하고 B가 다시 C르르 부여한 후에 권한을 취소하면 모든 권한이 취소된다.
WITH ADMIN OPTION	- 테이블에 대한 모든 권한을 부여한다. - 권한을 A 사용자에게 B에 부여하고 다시 C에게 부여한 후에 권한을 취소하면 B사용자 권한만 취소된다.

 

 

 

• REVOKE

- REVOKE 문은 DB 사용자에게 부여된 권한을 회수한다.

REVOKE

 

 

 

 

 

 

POINT.14 TCL (Transaction Control Language)

• COMMIT

- COMMIT 은 INSERT, UPDATE, DELETE 문으로 변경한 뎅터를 DB에 반영한다.

- COMMIT 이 완료되면 DB 변경으로 인한 LOCK 이 해제된다.(UNLOCK)

- COMMIT 이 완료되면 다른 모든 DB 사용자는 변경된 데이터를 조작할 수 있다.

- COMMIT 을 실행하면 하나의 트랜잭션 과정을 종료한다.

 

 

• ROLLBACK

- ROLLBACK 을 실행하면 데이터에 대한 변경 사용으르 모두 취소하고 트랜잭션을 종료한다.

- INSERT, UPDATE, DELETE 문의 작업을 모두 취소한다. 단 이전에 COMMIT 한 곳 까지만 복구된다.

- ROLLBACK 을 실행하면 LOCK 이 해제되고 다른 사용자도 DB 행을 조작할 수 있다.

 

 

 

• SAVEPOINT(저장점)

- SAVEPOINT 는 트랜잭션을 작게 분할하여 관리하는 것으로, SAVEPOINT 를 사용하면 지정된 위치 이후의 트랜잭션만 ROLLBACK 할 수 있다.

- 지정된 SAVEPOINT 까지만 데이터 변경을 취소하고 싶을 경우는 "ROLLBACK TO <SAVEPOINT명>" 을 실행한다.

savepoint

이때 rollback to t2 가 실행된 후 commit 되는데

그러면 t2 이후에 입력된 실행문들은 모두 취소되어

list 10, testnum 20 행 만 남아 입력된다.

 

 

 

 

오늘은 여기까지 하고 내일은 sql 기본 문제들을 다 풀어보자

그리고 이론 다 끝난줄 알았는데 sql 활용 파트가 아직 남아있었네;;

 

다음 주말 안에 활용 파트를 모두 끝내버리고 빨리 기출문제 풀이로 넘어가야겠다.