sumin

※ 날짜 함수

날짜 함수는 DATE 함수나 TIMESTAMP 함수와 같은 날짜형을 대상으로 연산을 수행해 결과를 반환하는 함수다. 날짜 함수 역시 대부분 반환 결과는 날짜형이나 함수에 따라 숫자를 반환할 때도 있다.

1. SYSDATE, SYSTIMESTAMP

SYSDATE와 SYSTIMESTAMP는 현재일자와 시간을 각각 DATE, TIMESTAMP 형으로 반환한다.

◈ 예제

2. ADD_MONTHS (date, integer)

ADD_MONTHS 함수는 매개변수로 들어온 날짜에 interger 만큼의 월을 더한 날짜를 반환한다.

◈ 예제

3. MONTHS_BETWEEN(date1, date2)

MONTHS_BETWEEN 함수는 두 날짜 사이의 개월 수를 반환하는데, date2가 date1보다 빠른 날짜가 온다.

◈ 예제

4. LAST_DAY(date)

LAST_DAY는 date 날짜를 기준으로 해당 월의 마지막 일자를 반환한다.

◈ 예제

5. ROUND(date, format), TRUNC(date, format)

ROUND와 TRUNC는 숫자 함수이면서 날짜 함수로도 쓰이는데, ROUND는 format에 따라 반올림한 날짜를, TRUNC는 잘라낸 날짜를 반환한다.

◈ 예제

6. NEXT_DAY (date, char)

NEXT_DAY는 date를 char에 명시한 날짜로 다음 주 주중 일자를 반환한다.

◈ 예제

※ 변환 함수

변환 함수란 서로 다른 유형의 데이터 타입으로 변환해 결과를 반환하는 함수를 말한다. 3장에서 언급했듯이 오라클이 자동으로 형변환을 해주는 것을 묵시적 형변환이라 하는데 이 절에서 학습하는 변환 함수를 통해 형변환을 직접 처리하는 것을 명시적 형변환이라고 한다.

1. TO_CHAR (숫자 혹은 날짜, format)

숫자나 날짜를 문자로 변환해 주는 함수가 바로 TO_CHAR로 매개변수로는 숫자나 날짜가 올 수 있고 반환 결과를 특정 형식에 맞게 출력할 수 있다.

◈ 예제

◈ 예제

매개변수로 오는 숫자, 날짜에 따라 자주 사용되는 포맷을 정리하면 다음 표와 같다.

2. TO_NUMBER(expr, format)

문자나 다른 유형의 숫자를 NUMBER 형으로 변환하는 함수다.

◈ 예제

3. TO_DATE(char, format), TO_TIMESTAMP(char, format)

문자를 날짜형으로 변환하는 함수다. 형식 매개변수로는 [표 4-1]에 있는 항목이 올 수 있으며, TO_DATE는 DATE 형으로 TO_TIMESTAMP는 TIMESTAMP 형으로 변환해 값을 반환한다.

◈ 예제

◈ 예제

※ 문자 함수는 연산 대상이 문자이며 반환 값은 함수에 따라 문자나 숫자를 반환한다.

1. INITCAP(char), LOWER(char), UPPER(char)

INITCAP 함수는 매개변수로 들어오는 char의 첫 문자는 대문자로 나머지는 소문자로 반환하는 함수다. 첫 문자를 인식하는 기준은 공백과 알파벳(숫자 포함)을 제외한 문자다. 공백이나 알파벳이 아닌 문자를 만난 후 다음 첫 알파벳 문자를 대문자로 변환한다.

◈ 예제

LOWER 함수는 매개변수로 들어오는 문자를 모두 소문자로, UPPDER 함수는 대문자로 변환해 반환한다.

◈ 예제

2. CONCAT(char1, char2), SUBSTR(char, pos, len), SUBSTRB(char, pos, len)

CONCAT 함수는 ‘||’ 연산자처럼 매개변수로 들어오는 두 문자를 붙여 반환한다.

◈ 예제

SUBSTR는 문자 함수 중 가장 많이 사용되는 함수로, 잘라올 대사 문자열인 char의 pos번째 문자부터 len길이만큼 잘라낸 결과를 반환하는 함수다. pos 값으로 0이 오면 디폴트 값인 1 즉 첫 번째 문자를 가리키며 음수가 오면 char 문자열 맨 끝에서 시작한 상대적 위치를 의미한다. len 값이 생략되면 pos번째 문자부터 나머지 모든 문자를 반환한다.

◈ 예제

SUBSTR 함수는 문자 개수 단위로 문자열을 자르는 반면 SUBSTRB는 문자 개수가 아닌 문자열의 바이트(BYTE) 수만큼 잘라낸 결과를 반환한다(SUBSTRB의 맨 끝 글자인 ‘B’는 BYTE를 의미함)

◈ 예제

한글에서는 잘라올 길이로 4를 명시했지만 한 글자가 2바이트를 차지하므로 ‘가나’ 두 글자만 반환되었음을 확인할 수 있다.

3. LTRIM(char, set), RTRIM(char, set)

LTRIM 함수는 매개변수로 들어온 char 문자열에서 set으로 지정된 문자열을 왼쪽 끝에서 제거한 후 나머지 문자열을 반환한다. 두 번째 매개변수인 set은 생략할 수 있으며 디폴트로 공백 문자 한 글자가 사용된다. RTRIM 함수는 반대로 오른쪽 끝에서 제거한 뒤 나머지 문자열을 반환한다.

◈ 예제

LTRIM이나 RTRIM 모두 왼쪽과 오른쪽 기준으로 set에 명시된 문자를 한 번씩만 제거한다는 점에 주의해야 한다. 또한 다음과 같이 set 문자로 명시한 문자가 맨 왼쪽이나 맨 오른쪽에 없을 때, 즉 문자열 중간에 있다면 문자열 전체를 반환한다.

◈ 예제

4. LPAD(expr1, n, expr2), RPAD(expr1, n, expr2)

LPAD 함수는 매개변수로 들어온 expr2 문자열(생략할 때 디폴트는 공백 한 문자)을 n자리만큼 왼쪽부터 채워 expr1을 반환하는 함수다. 매개변수 n은 expr2와 expr1이 합쳐져 반환되는 총 자릿수를 의미한다. 예를 들어, 서울의 지역 전화번호는 ‘02’인데 전화번호 컬럼에 지역번호가 없으면 LPAD 함수로 번호 02를 자동으로 채워 넣을 수 있다.

◈ 예제

전화번호 총 자릿 수는 8자리이고, 각 번호 앞에 ‘(02)’를 붙인다면 총 12자리가 된다.

RPAD는 LPAD와는 반대로 오른쪽에 해당 문자열을 채워 반환한다.

5.  REPLACE(char, search_str, replace_str), TRANSLATE(expr, FROM_str, to_str)

REPLACE 함수는 char 문자열에서 search_str 문자열을 찾아 이를 replace_str 문자열로 대체한 결과를 반환하는 함수다.

◈ 예제

문자열에서 공백을 제거할 때 TRIM을 사용하지만 TRIM함수는 문자열 공백은 제거하지 못한다. 하지만 REPLACE 함수를 사용하면 문자열 전체에 있는 공백을 모두 제거할 수 있다.

◈ 예제

TRANSLATE 함수는 REPLACE와 유사하다. expr 문자열에서 FROM_str에 해당하는 문자를 찾아 to_str로 바꾼 결과를 반환하는데 REPLACE와 다른 점은 문자열 자체가 아닌 문자 한 글자씩 매핑해 바꾼 결과를 반환한다.

◈ 예제

※ 숫자 함수 란 수식 연산을 하는 함수로 연산 대상 즉, 매개변수나 반환 값이 대부분 숫자 형태다.

1. ABS(n)

ABS 함수는 매개변수로 숫자를 받아 그 절대값을 반환하는 함수다.

◈ 예제

2. CEIL(n)과 FLOOR(n)

CEIL 함수는 매개변수 n과 같거나 가장 큰 정수를 반환한다.

◈ 예제

FLOOR 함수는 CEIL 함수와는 반대로 매개변수 n보다 작거나 가장 큰 정수를 반환한다.

◈ 예제

3. ROUND(n,i) 와 TRUNC(n1,n2)

ROUND 함수는 매개변수 n을 소수점 기준 (i+1)번 째에서 반올림한 결과를 반환한다. i는 생략할 수 있고 디폴트 값은 0, 즉 소수점 첫 번째 자리에서 반올림이 일어나 정수 부분의 일의 자리에 결과가 반영된다.

◈ 예제

◈ 예제

ROUND(10.154, 3)는 네 번째 자리가 0이므로 10.154가 반환되었다. n이 0일 때는 i에 입력된 숫자에 상관없이 무조건 0을 반환하며, i가 음수이면 소수점을 기준으로 왼쪽 i번째에서 반올림이 일어난다.

◈ 예제

첫 번째는 n이 0이므로 0이 반환됐고 두 번째는 i가 -1이므로 115에서 5가 반올림되어 120이, 세 번째 컬럼에는 -2가 입력되어 115에서 백의 자리 1이 반올림되어 결과는 100이 되었다.

TRUNC 함수는 반올림을 하지 않고 n1을 소수점 기준 n2자리에서 잘라낸 결과를 반환한다. n2 역시 생략할 수 있으며 디폴트 값은 0이고, 양수일 때는 소수점 기준으로 오른쪽, 음수일 때는 소수점 기준 왼쪽 자리에서 잘라낸다.

◈ 예제

4. POWER(n2,n1) 와 SQRT(n)

POWER 함수는 n2를 n1 제곱한 결과를 반환한다. n1은 정수와 실수 모두 올 수 있는데, n2가 음수일 때 n1은 정수만 올 수 있다.

◈ 예제

◈ 예제

SQRT 함수는 n의 제곱근을 반환한다.

◈ 예제

5. MOD(n2,n1) 와 REMAINDER(n2,n1)

MOD 함수는 n2를 n1으로 나눈 나머지 값을 반환한다.

◈ 예제

REMAINDER 함수 역시 n2를 n1으로 나눈 나머지 값을 반환하는데, 나머지를 구하는 내부적 연산 방법이 MOD 함수와는 약간 다르다.

• MOD → n2 - n1 * FLOOR (n2/n1)

• REMAINDER → n2 - n1 * ROUND (n2/n1)

◈ 예제

6. EXP(n), LN(n), LOG(n2,n1)

EXP는 지수 함수로 e(e=2.71828183…)의 n제곱 값을 반환하고 LN 함수는 자연로그함수로 밑수가 e인 로그 함수다. 반면 LOG는 n2를 밑수로 하는 n1의 로그 값을 반환한다.

◈ 예제

1. 연산자

● 수식 연산자: +, -, *, /

‘+’와 ‘-’는 연산대상이 1개인 단항 연산자로 쓰일 때 각각 양수와 음수를 나타낸다. 또한 두 수의 연산 즉 이항 연산자로 사용될 때는 각각 덧셈과 뺄셈 연산을 한다. 그리고 ‘*’는 곱셈, ‘/’는 나눗셈 연산을 한다.

● 문자 연산자 : ||

‘||’는 두 문자를 붙이는(연결하는) 연산을 수행한다. 

◈예제

아래의 쿼리는 사원 테이블에서 “사번-사원명” 형태로 추출하는 SELECT 문이다.

● 논리 연산자: >, <, >=, <=, =, <>, !=, \^=

논리 연산을 수행하는 연산자로 수학에서 사용하는 부등호와 쓰임새는 같다. 두 값이 같은지를 판단하는 등호 연산자(=)의 반대인 비동등 연산자로는 ‘< >’, ‘!=’, ‘\^=’가 있는데 세 연산자의 사용법과 반환 결과는 모두 같다. 또한 값을 비교할 때 숫자 뿐만 아니라 문자와 날짜형도 비교 가능하다. 

2. 표현식

표현식(Expression)은 한개 이상의 값과 연산자와 SQL 함수가 결합된 식이다. 특정 조건에 따라 값을 변경해서 보이는 CASE 표현식(흔히 CASE문이라고도 한다)에 대해 알아본다.

● 기본구문

사원 테이블에서 각 사원의 급여에 따라 5000 이하로 급여를 받는 사원은 C, 5000~15000은 B, 15000 이상은 A등급을 반환하는 쿼리를 작성해 보자.

WHEN 다음에 조건을 기술하고 THEN 다음에는 앞에서 기술한 조건에 만족하면 실제 출력되는 값을 명시하고 있는데, 주의할 점은 THEN 이하 출력 값들의 데이터 타입은 반드시 일치시켜야 한다. 즉 앞의 문장은 THEN 다음에 있는 ‘C 등급’, ‘B 등급’은 데이터 타입이 모두 문자 형태로 동일하게 명시했는데, 만약 ‘C 등급’, ‘3’, ‘4’처럼 문자형과 숫자형을 혼합해 기술하면 오류가 발생한다. CASE 표현식은 다른 프로그래밍 언어에서도 제공하는 기능으로 그 쓰임새는 비슷하나 프로그래밍 언어나 DBMS 종류에 따라 구문 형식이 약간씩 다르다.

조건식

조건식 (Condition}은 한 개 이상의 표현식과 논리 연산자가 결합된 식으로 TRUE, FALSE, UNKNOWN 세 가지 타입을 반환한다. WHERE절이 바로 조건식에 포함된다.

1. 비교 조건식

비교 조건식은 논리 연산자나 ANY, SOME, ALL 키워드로 비교하는 조건식을 말한다. 여기서는 ANY, SOME, ALL 비교에 대해서 알아 볼텐데 먼저 ANY를 살펴 보자.

◈ 예제

쿼리는 급여가 2000이거나 3000, 4000인 사원을 추출하는 것이다. ANY가 아무것 또는 하나란 뜻이다.

위 문장은 세 가지 값, 즉 급여가 2000이나 3000이나 4000 중 하나라도 일치하는 모든 사원을 추출한 것이다.

따라서 ANY는 OR 조건으로 변환이 가능하며 다음 문장도 같은 결과를 반환한다.

반면, ALL은 모든 조건을 만족해야 한다. 다음 쿼리를 보자.

모든조건을 만족하는 행이 없기 때문에 선택된 행이 없다. ANY와는 반대로 ALL 조건식은 AND 조건으로 변환할 수 있다.

마지막으로 알아 볼 SOME은 ANY와 동일하게 사용되며 동작한다.

2. 논리 조건식

논리 조건식은 조건절에서 AND, OR, NOT을 사용하는 조건식을 말한다. AND는 모든 조건을 만족해야 하고 OR는 여러 조건 중 하나만 만족해도 TRUE를 반환된다. NOT은 조건식 평가 결과가 FALSE일 때 원하는 결과 즉 TRUE를 반환한다.

◈ 예제

쿼리는 NOT으로 급여가 2500보다 크거나 같지 않은 사원을 반환한다. 

3. NULL 조건식

NULL 조건식은 어떠한 값이 NULL인지를 체크하는 조건식이다.  UPDATE문에서 NULL인지의 여부는 IS NULL과 IS NOT NULL로 체크한다. 다시 강조하지만 특정 컬럼 값이 NULL인지의 여부를 체크할 때 등호 연산자(=, < >)를 사용하면 제대로 비교하지 못한다. 급여가 NULL 인지아닌지 체크하려면 “salary = NULL” ,  “salary < > NULL” 이런식으로 비교하면 안 되고 “salary IS NULL” 혹은 “salary IS NOT NULLL” 형태로 비교해야 한다.

4. BETWEEN AND 조건식

BETWEEN은 범위에 해당되는 값을 찾을 때 사용하는데 크거나 같고 작거나 같은 값을 찾는다. 따라서 ‘>=’와 ‘<=’ 논리 연산자로 변환이 가능하다.

◈ 예제

다음은 급여가 2000에서 2500 사이에 해당하는 사원을 조회하는 쿼리다.

5. IN 조건식

IN 조건식은 조건절에 명시한 값이 포함된 건을 반환하는데 ANY와 비슷하다.

◈ 예제

쿼리는 급여가 2000, 3000, 4000에 포함되는 사원을 추출한 결과로, IN 역시 OR 조건으로 변환이 가능하며 ‘=ANY’형태로 바꿔 쓸 수 있다

◈ 예제

쿼리는 NOT IN을 사용해서 급여가 2000, 3000, 4000이 아닌 건을 조회하고 있다. 조건을 풀어 쓰면 “salary < > 2000 AND salary < > 3000 AND salary < > 4000”이며, “< >ALL”로도 바꿔 쓸 수 있다.

6. LIKE 조건식

LIKE 조건식은 문자열의 패턴을 검색할 때 사용하는 조건식이다. 

◈ 예제

사원 테이블에서 사원이름이 ‘A’로 시작되는 사원을 조회하는 쿼리를 작성한다면 다음과 같이 LIKE 조건식을 사용한다.

LIKE ‘A%’의 의미는 사원명 첫 글자는 ‘A’로 시작하되 나머지는 어떤 글자가 와도 상관없이 모두 조회하라는 의미다.

◈ 예제

'%’는 앞, 뒤, 중간 어디나 올 수 있으며 조건식을 검색할 때 대소문자를 구분한다는 점을 주의하자. ‘%’와는 다르게 ‘_’(밑줄, 언더스코어)는 나머지 글자 전체가 아닌 한 글자만 비교한다.

아래 쿼리의 결과를 보면 ‘이수빈지’는 ‘%’를 사용했을 때 조회되었지만  ‘_’를 사용했을 때는 한 글자만 비교하므로 검색되지 않았다.

1. COMMIT 과 ROLLBACK

COMMIT은 변경한 데이터를 데이터베이스에 마지막으로 반영하는 역할을, ROLLBACK은 그 반대로 변경한 데이터를 변경하기 이전 상태로 되돌리는 역할을 한다.

● 기본구문

UPDATE, INSERT, MERGE, DELETE문을 실행하면 데이터에 변화가 생긴다. 하지만 영구적으로 변경되는 것은 아니다. SQL Developer에서 다음 문장을 실행해 보자.

cmd창을 열어서 SQLPlus로 테이블을 조회해 보겠다.

조회된 데이터가 하나도 없다. 오라클을 비롯한 RDBMS 시스템은 트랜잭션 기능을 지원하는데 COMMIT이나 ROLLBACK문을 실행하기 전까지 변경된 데이터는 현재 세션에만 볼 수 있고 최종적으로 데이터베이스에 반영된 상태가 아니다. 따라서 테이블에 데이터가 없는 것이다. COMMIT을 실행하면 데이터가 반영되고 ROLLBACK을 실행하면 변경 이전 상태로 복귀한다. 그럼 COMMIT 문을 실행해 보자.

2. TRUNCATE문

데이터를 삭제할 때는 DELETE문을 사용하는데 이와 같은 기능을 수행하는 문장이 바로 TRUNCATE문이다. DELETE문은 데이터를 삭제한 후에 COMMIT을 실행해야 데이터가 완전히 삭제되고, 반대로 ROLLBACK을 실행하면 데이터가 삭제되기 전으로 복귀된다. 하지만 DDL문에 속하는 TRUNCATE문은 한번 실행하면 데이터가 바로 삭제되고 ROLLABCK을 실행하더라도 삭제 전 상태로 복귀되지 않는다. 또한 TRUNCATE문에는 WHERE 조건을 붙일 수 없다. 즉 테이블 데이터 전체가 바로 삭제된다. 복구할 방법이 없으므로 TRUNCATE문을 사용할 때는 항상 주의를 기울여야 한다.

● 기본구문