[SQLD] SQL 기본 및 활용 Section 01. SQL 기본 (3) 내장형 함수부터 끝까지

---

9. 내장형 함수 ( BUILT-IN FUNCTION )

• 모든 데이터베이스는 SQL에서 사용할 수 있는 내장형 함수를 가짐
• DBMS 벤더별로 약간의 차이가 있지만 거의 비슷한 방법으로 사용이 가능함
• 내장형 함수로는 형변환 함수, 문자열 및 숫자형 함수, 날짜형 함수 등이 있음

 

---

01. DUAL 테이블

• DUAL 테이블은 오라클 데이터베이스에 의해 자동으로 생성되는 테이블임
• 오라클 데이터베이스 사용자가 임시로 사용할 수 있는 테이블로 내장형 함수를 실행할 때도 사용 가능함
• 모든 사용자에게 공유되는 특별한 단일 행 테이블로서 주로 상수, 순수 함수, 다른 SQL 표현식의 결과값을 조회할 때나 테스트 쿼리나 일시적인 결과를 생성할 때 사용함

 

SELECT * FROM DUAL;

 

DUAL 테이블

 

SELECT SYSDATE FROM DUAL;

SELECT 2 + 2 FROM DUAL;

 

---

02. 내장형 함수의 종류

 

문자열 함수

문자열 함수	설명
ASCII(문자)	- 문자 혹은 숫자를 ASCII 코드값으로 변환
CHR/CHAR(ASCII 코드 값)	- ASCII 코드값을 문자로 변환 - 오라클은 CHR, MYSQL은 CHAR 사용
SUBSTR(문자열, m, n)	- 문자열에서 m번째 위치부터 n개를 잘라서 그 부분만 리턴
CONCAT(문자열1, 문자열2)	- 문자열1과 문자열2를 결합 - 오라클은 ||, MS-SQL은 +를 사용가능
LOWER(문자열)	- 영문자를 소문자로 변환
UPPER(문자열)	- 영문자를 대문자로 변환
LENGTH/LEN(문자열)	- 공백을 포함해 문자열의 길이를 리턴
LTRIM(문자열, 지정문자)	- 왼쪽에서 지정된 문자를 삭제 - 지정된 문자를 생략하면 공백을 삭제
RTRIM(문자열, 지정문자)	- 오른쪽에서 지정된 문자를 삭제 - 지정된 문자를 생략하면 공백을 삭제
TRIM(문자열, 지정된 문자)	- 왼쪽 및 오른쪽에서 지정된 문자를 삭제 - 지정된 문자를 생략하면 공백을 삭제

 

SELECT ASCII('A'), ASCII('Z') FROM DUAL;

SELECT SUBSTR('ABCDEFG', 2, 4) FROM DUAL;

SELECT CONCAT('ABC', 'DEF') FROM DUAL;

SELECT LTRIM('AAABC', 'A') FROM DUAL;

SELECT TRIM('    ABC  ') FROM DUAL;

 

 

 

 

 

 

 

날짜형 함수

날짜형 함수	설명
SYSDATE	- 오늘의 날짜를 날짜 타입으로 알려줌
EXTRACT(YEAR from SYSDATE)	- 오늘 날짜에서 년, 월, 일 추출 - 오라클에는 YEAR(날짜), MONTH(날짜) 등의 내장함수가 없어서 이걸로 써야 함

 

 

 

숫자형 함수

숫자형 함수	설명
ABS(숫자)	- 절댓값을 리턴
SIGN(숫자)	- 양수, 음수, 0을 구별
MOD(숫자1, 숫자2)	- 숫자1을 숫자2로 나누어 나머지를 계산 - 다른 SQL에서는 %를 사용해도 됨. 오라클은 안됨
CEIL/CEILING(숫자)	- 올림
FLOOR(숫자)	- 내림
ROUND(숫자, m)	- 소수점 m 자리에서 반올림 - m의 기본값은 0
TRUNC(숫자, m)	- 소수점 m자리에서 절삭 - m의 기본값은 0

 

SELECT ABS(-50), SIGN(5), SIGN(0), SIGN(-100) FROM DUAL;

SELECT MOD(50, 3), TRUNC(50 / 3) FROM DUAL;

SELECT CEIL(20.3), FLOOR(20.3), ROUND(20.3333, 2) FROM DUAL;

 

 

 

 

 

---

10. DECODE와 CASE문

 

---

01. DECODE

• 간단한 IF문으로 어떤 값이 참이면 A, 거짓이면 B로 리턴함

 

SELECT * FROM CUSTOMER;

SELECT DECODE(SIDO, '서울', '일치', '불일치') FROM CUSTOMER;

 

CUSTOMER 전체

 

 

 

---

02. CASE

• CASE문은 CASE~THEN~ELSE~END처럼 조건문으로 사용할 수 있음
• 해당 조건이 참이면 THEN이 실행되고 거짓이면 ELSE문이 실행됨

CASE [expression]
	WHEN condition_1 THEN result_1
    WHEN condition_2 THEN result_2
    ...
    WHEN condition_n THEN result_n
    ELSE result
END

END를 꼭 붙여야 하며, expression이 있을 때는 WHEN 안의 조건문이 expression을 기준으로 비교를 수행하고, 없을 경우 조건문을 기준으로 비교를 수행한다

 

 

SELECT * FROM CUSTOMER;

SELECT CASE SGG
    WHEN '강남구' THEN '주요 구역'
    WHEN '마포구' THEN '주요 구역'
    ELSE '나머지'
    END
    AS "구역 체크"
    FROM CUSTOMER;
    
SELECT CASE
    WHEN SGG = '강남구' THEN '주요 구역'
    WHEN SGG = '마포구' THEN '주요 구역'
    ELSE '나머지'
    END
    AS "구역 체크"
    FROM CUSTOMER;

 

CUSTOMER 테이블

 

CASE WHEN ~ THEN ~ ELSE ~ END문

 

 

---

11. ROWNUM과 ROWID

---

01. ROWNUM

• ROWNUM은 오라클 데이터베이스의 SELECT문 결과에 대해 논리적인 일련번호를 부여함
• 조회되는 행 수를 제한할 때 많이 사용됨
• ROWNUM은 화면에 데이터를 출력할 때 부여되는 논리적 순번임. 만약 ROWNUM을 이용해 페이지 단위 출력을 하기 위해서는 인라인 뷰(Inline View)를 사용해야 함

 

🔍인라인 뷰 : FROM절에 사용되는 내부 SELECT문

SELECT * FROM
(
	SELECT * FROM CUSTOMER
);

 

 

SELECT * FROM CUSTOMER;

SELECT * FROM
(
    SELECT ROWNUM list, SGG, SIDO, UPJONG_CLASS1, YMD FROM CUSTOMER
)
WHERE list BETWEEN 11 AND 15;

 

 

이렇게 BETWEEN과 조합해 데이터의 개수를 제한해서 들고 올 수 있으므로 게시판 페이지네이션을 구현하는데 사용된다. MySQL의 LIMIT구와 같다

 

 

 

---

02. ROWID

• ROWID는 오라클 데이터베이스 내에서 데이터를 구분할 수 있는 유일한 값이며 모든 데이터에 부여되어 있음
• ROWID를 통해서 데이터가 어떤 데이터 파일, 어느 블록에 저장되어 있는지 알 수 있음

SELECT * FROM CUSTOMER;

SELECT ROWID, SGG, SIDO FROM CUSTOMER;

 

 

 

---

03. ROWID 구조

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

 

 

---

12. WITH

• 서브쿼리를 이용해 임시 테이블이나 뷰처럼 사용할 수 있는 구문
• 서브쿼리 블록에 별칭 지정 가능
• 옵티마이저는 SQL을 인라인 뷰나 임시 테이블로 판단함

 

SELECT * FROM CUSTOMER;

WITH temporaryData AS
(
    SELECT * FROM CUSTOMER
    WHERE YMD >= '20200101'
    ORDER BY YMD DESC
)

SELECT * FROM temporaryData;

 

 

위의 코드와 같이 WITH 별칭 AS (SELECT문) 을 이용해 별칭을 가진 서브쿼리를 만들 수 있다.

 

 

 

---

13. DCL ( Data Control Language )

---

01. GRANT

• 데이터베이스 사용자에게 권한을 부여함
• 권한 부여를 통해 해당 사용자는 연결, 입력, 수정, 삭제, 조회 등을 할 수 있다

 

GRANT SELECT, INSERT, UPDATE, DELETE
    ON CUSTOMER
    TO GUEST WITH GRANT OPTION;

 

위와 같이 지정한 CUSTOMER 테이블에 대해 SELECT, INSERT, UPDATE, DELETE를 할 수 있는 권한을 GUEST에게 부여한다. 이때 WITH GRANT OPTION을 통해 이 권한을 다시 다른 사람에게 부여할 수 있는 권한을 GUEST에게 함께 준다.

 

 

권한 종류

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

 

 

WITH GRANT OPTION과 WITH ADMIN OPTION

옵션	설명
WITH GRANT OPTION	- 특정 사용자에게 권한을 부여할 수 있는 권한을 부여 - 권한을 A 사용자가 B 사용자에게 부여하고 B 사용자가 다시 C 사용자에게 권한을 부여했을 때, A가 권한을 취소하면 모든 사용자의 권한이 회수됨
WITH ADMIN OPTION	- 테이블에 대한 모든 권한을 부여 - 권한을 A 사용자가 B 사용자에게 부여하고 B 사용자가 다시 C 사용자에게 권한을 부여했을 때, 권한을 취소하면 B 사용자에 대해서만 권한이 회수됨

 

 

 

---

02. REVOKE

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

 

REVOKE SELECT, INSERT, DELETE, UPDATE
    ON CUSTOMER
    FROM GUEST;

 

 

---

14. TCL ( Transaction Control Language )

 

---

01. COMMIT

• COMMIT은 INSERT, UPDATE, DELETE문으로 변경한 데이터를 데이터베이스에 반영하는 것
• 변경 이전 데이터는 잃어버리므로 주의
• 다른 모든 데이터베이스 사용자가 변경된 데이터를 볼 수 있도록 함
• COMMIT이 완료되면 데이터베이스 변경으로 인한 LOCK이 해제됨
• 따라서 다른 모든 데이터베이스 사용자가 변경된 데이터를 조작할 수 있음
• COMMIT을 실행하면 하나의 트랜잭션 과정이 종료됨

 

🔍AUTO COMMIT

• SQL PLUS 프로그램을 정상적으로 종료하는 경우 자동 COMMIT됨
• DDL OR DCL을 사용하는 경우 자동 COMMIT됨
• SET AUTOCOMMIT ON; 을 사용하면 자동 COMMIT됨

 

 

---

02. ROLLBACK

• ROLLBACK을 실행하면 데이터에 대한 변경 사용을 모두 취소하고 트랜잭션 종료
• INSERT, UPDATE, DELETE문의 작업을 모두 취소하고 이전 COMMIT까지 복구
• ROLLBACK 실행하면 LOCK이 해제되고 다른 사용자도 데이터베이스 행을 조작할 수 있음

 

 

---

03. SAVEPOINT

• 트랜잭션을 작게 분할해 관리하는 것. SAVEPOINT를 이용하면 지정된 위치 이후의 트랜잭션만 ROLLBACK가능

SELECT * FROM EMP;

INSERT INTO EMP VALUES (8, '라경민', 500, '1000', sysdate);

SAVEPOINT save_1;

SELECT * FROM EMP;

INSERT INTO EMP VALUES (9, '순경민', 600, '1001', sysdate);

SELECT * FROM EMP;

ROLLBACK TO save_1;

SELECT * FROM EMP;

ROLLBACK;

SELECT * FROM EMP;

 

 

SAVEPOINT save_1을 만든 시점

 

순경민을 삽입한 시점

 

save_1로 ROLLBACK을 실행한 시점

 

그냥 ROLLBACK을 실행한 시점

 

 

위와 같이 SAVEPOINT를 중간에 생성하고 ROLLBACK TO (SAVEPOINT명)을 실행하면 그 시점으로 롤백된다. 하지만 COMMIT을 중간에 실행해버리고 save_1으로 다시 돌아가려고 하면 에러가 발생한다.

 

 

그냥 ROLLBACK을 실행하면 SAVEPOINT에 관계없이 데이터의 모든 변경사항을 저장하지 않는다.