[SQLD] SQL 기본 - DECODE&CASE, ROWNUM&ROWID, WITH, DCL(Data Control Language), TCL(Transaction Control Language)

<DECODE와 CASE문>

1. DECODE문

- IF문 구현 가능

- 특정조건이 참이면 A, 거짓이면 B로 응답

DECODE(EMPNO,1000,'TRUE','FALSE'); // EMPNO가 1000이면 TRUE 아니면 FALSE 출력

DECODE( 컬럼명, 조건, 같을 경우 값, 틀릴 경우 값 )

 

DECODE(EMPNO,10,'기획부',20,'홍보부'); // EMPNO가 10이면 기획부 20이면 홍보부 출력

DECODE( 컬럼명, 조건1, 값1 , 조건2, 값2 )

 

 

2. CASE 문

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

SELECT CASE
		WHEN EMPNO = 1000 THEN 'A'    // EMPNO이 1000이면 A
        WHEN EMPNO = 1001 THEN 'B'	  // EMPNO이 1001이면 B
        ELSE 'C'   // 그 외는 C
       END
FROM EMP;

CASE 컬럼명

     WHEN  조건 1   THEN   값1

     WHEN  조건 2   THEN   값2

     ELSE   디폴트값

END

 

 

<ROWNUM과 ROWID>

1. ROWNUM

- Oracle 데이터베이스의 SELECT문 결과에 대해서 논리적인 일련번호를 부여

- ROWNUM은 조회되는 행 수를 제한할 때 많이 사용

- ROWNUM은 화면에 데이터를 출력할 때 부여되는 논리적 순번

(ROWNUM을 사용해 페이지 단위 출력을 하려면 인라인 뷰를 사용해야함)

* 인라인뷰 : SELECT 문에서 FROM절에 사용되는 서브쿼리를 의미

- Oracle은 ROWNUM을 사용하지만, SQL Server는 TOP문을 사용하고, MySQL은 LIMIT구를 사용한다.

ex) SQL Server : SELECT TOP(10) FROM EMP;

ex) MySQL : SELECT * FROM EMP LIMIT 10;

 

SELECT *
FROM EMP
WHERE ROWNUM<=1; // 한행을 조회

 

SELECT *
FROM  (SELECT ROWNUM list, ename // ROWNUM에 별칭 list 지정
    from emp)
WHERE list<=5; //서브 쿼리 테이블에서 5개 행 조회

 

SELECT *
FROM (SELECT ROWNUM list, ENAME
	FROM EMP)
WHERE list BETWEEN 5 AND 10; // 5행부터 10행까지 조회

* 웹 페이지 게시판 조회시 많이 사용

 

 

2. ROWID

- Oracle 데이터베이스 내에서 데이터를 구분할 수 있는 유일한 값

- 모든 테이블은 ROWID를 가지고 있음

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

 

- ROWID 구조

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

SELECT ROWID, EMPNO
FROM EMP; //ROWID 조회

 

 

 

<WITH 구문>

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

- 서브쿼리 블록에 별칭 지정 가능

- 옵티마이저는 SQL를 인라인뷰나 임시 테이블로 판단

- 같은 서브쿼리를 여러번 사용할 경우 재사용이 가능해 성능이 향상됨

WITH A AS
(SELECT * FROM EMP WHERE DEPTNO = 30)
SELECT * FROM A;

WITH 별칭 AS ( SUB쿼리 )

SELECT 컬럼명 FROM 별칭;

 

 

< DCL (Data Control Language) >

1. GRANT

- 데이터베이스 사용자에게 권한 부여

- 데이터베이스 사용을 위해서는 권한이 필요함 ( 연결, 입력, 수정, 삭제, 조회 등 권한)

 

- Privileges(권한)

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

 

GRANT SELECT, INSERT, UPDATE, DELETE;
	ON EMP
    TO JIWON;

GRANT 권한 ON 테이블명 TO 사용자이름;

 

 

- WITH GRANT OPTION

GRANT 옵션	설명
WITH GRANT OPTION	- 특정 사용자에게 권한을 부여할 수 있는 권한을 부여 - 권한을 회수하면 부여받은 사용자의 권한도 종속적으로 회수됨
WITH ADMIN OPTION	- 테이블에 대한 모든 권한을 부여 - 권한을 회수해도 부여받은 사용자의 권한은 독립적 (종속적으로 회수X)

 

GRANT SELECT, INSERT, UPDATE, DELETE;
	ON EMP
    TO JIWON
    WITH GRANT OPTION;

GRANT 권한 ON 테이블명 TO 사용자이름 WITH GRANT(ADMIN) OPTION;

 

 

2. REVOKE

- 데이터베이스 사용자에게 부여된 권한 회수

REVOKE ALL ON EMP FROM JIWON;

REVOKE 권한 ON 테이블명 TO 사용자이름;

 

 

< TCL (Transaction Control Language) >

1. COMMIT

- INSERT, UPDATE, DELETE문으로 변경한 데이터를 데이터베이스에 반영함

- 변경 전 이전 데이터는 잃어버림

- 다른 모든 데이터베이스 사용자는 변경된 데이터를 볼 수 있음

- COMMIT이 완료되면 데이터베이스 변경으로 인한 ROCK 해제

- COMMIT이 완료되면 다른 모든 데이터베이스 사용자는 변경된 데이터 조작 가능

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

(트랜잭션의 종료는 데이터베이스 사용자가 COMMIT 혹은 ROLLBACK으로 처리해야함)

 

https://codedragon.tistory.com/5889

 

 

2. ROLLBACK

- 데이터에 대한 변경 사용을 모두 취소하고 트랜잭션을 종료

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

- ROLLBACK을 실행하면 LOCK이 해제되고 다른 사용자도 데이터베이스 행을 조작할 수 있음

https://codedragon.tistory.com/5889

 

 

3. SAVEPOINT(저장점)

- 트랜잭션을 작게 분할 관리

- 지정된 위치 이후의 트랜잭션만 ROLLBACK할 수 있음

- 지정 : SAVEPOINT SAVEPOINT명;

- 실행 : ROLLBACK TO SAVEPOINT명;