3.[Oracle] 단일 행 함수를 사용하여 출력 커스터마이즈를 해보자.

-SQL 함수

함수는 SQL의 매우 강력한 기능입니다. 다음 작업을 수행하는 데 함수를 사용할 수 있습니다.

• 데이터에 대해 계산 수행

• 개별 데이터 항목 수정

• 행 그룹에 대한 출력 조작

• 표시할 날짜 및 숫자의 형식 지정

• 열 데이터 유형 변환

 

-SQL 함수의 두 가지 유형

함수의 유형은 다음 두 가지입니다.

• 단일 행 함수

• 여러 행 함수

 

-단일 행 함수

• 데이터 항목을 조작합니다.

• 인수를 받아들이고 하나의 값을 반환합니다.

• 반환되는 각 행에서 실행됩니다.

• 행당 하나의 결과를 반환합니다.

• 데이터 유형을 수정할 수 있습니다.

• 중첩될 수 있습니다.

• 열이나 표현식을 인수로 받아들일 수 있습니다.

 

단일 행 함수는 데이터 항목을 조작하는 데 사용됩니다. 하나 이상의 인수를 받아들이고 query에 의해 반환되는 각 행에 대해 하나의 값을 반환합니다. 인수는 다음 중 하나가 될 수 있습니다.

• 유저가 제공하는 상수

• 변수 값

• 열 이름

• 표현식

 

단일 행 함수의 기능에는 다음이 포함됩니다.

• query를 통해 반환되는 각 행에서 실행

• 행당 하나의 결과 반환

• 참조되는 유형이 아닌 다른 유형의 데이터 값을 반환할 수 있음

• 하나 이상의 인수를 받아들일 수 있음

• SELECT, WHERE, ORDER BY 절에서 사용할 수 있고 중첩될 수 있음

 

이 구문에서 다음이 적용됩니다.

function_name 함수의 이름입니다.

arg1, arg2 함수에 사용될 임의의 인수입니다. 열 이름이나 표현식으로 나타낼 수 있습니다.

 

-문자 함수

단일 행 문자 함수는 입력으로 문자 데이터를 받아들이고 문자 및 숫자 값을 모두 반환할 수 있습니다. 문자 함수는 다음과 같이 분류될 수 있습니다.

• 대소문자 변환 함수

• 문자 조작 함수

 

함수	목적
LOWER(column|expression)	영문자 값을 소문자로 변환합니다.
UPPER(column|expression)	영문자 값을 대문자로 변환합니다.
INITCAP(column|expression)	영문자 값의 첫번째 문자를 대문자로 변환하고 나머지 문자는 소문자로 둡니다.
CONCAT(column1|expression1, column2|expression2)	첫번째 문자 값을 두번째 문자 값과 연결합니다. 연결 연산자(||)와 같은 기능입니다.
SUBSTR(column|expression, m[,n])	위치에서 시작하는 문자 값에서 n개의 문자 길이만큼 지정된 문자들을 반환합니다(m이 음수인 경우 문자 값 끝에서부터 카운트를 시작합니다. n이 생략된 경우 문자열의 끝까지 모든 문자가 반환됩니다.)
LENGTH(column|expression)	표현식의 문자 수를 반환합니다.
INSTR(column|expression, ’string’, [,m], [n] )	’string’, [,m], [n] ) 지정된 문자열의 숫자 위치를 반환합니다. 선택적으로 검색 시작 위치 m과 문자열의 발생 수 n 을 제공할 수 있습니다. m과 n의 기본값은 1이며, 이 경우 문자열의 처음부터 검색을 시작하고 첫번째로 찾은 결과를 보고합니다.
LPAD(column|expression,n,'string') RPAD(column|expression, n, 'string')	길이가 n이 되도록 왼쪽부터 문자식으로 채운 표현식을 반환합니다. 길이가 n이 되도록 오른쪽부터 문자식으로 채운 표현식을 반환합니다.
TRIM(leading|trailing|both, trim_character FROM trim_source)	문자열에서 선행 또는 후행 문자(또는 둘 다)를 자를 수 있습니다. trim_character 또는 trim_source가 문자 리터럴인 경우 작은 따옴표로 묶어야 합니다. 이 기능은 Oracle8 i 이상의 버전에서 제공됩니다.
REPLACE(text, search_string, replacement_string)	텍스트 표현식에서 문자열을 검색하여 해당 문자열을 찾으면 지정된 대체 문자열로 바꿉니다.

 

#이외에도 많은 함수가 존재합니다. 자주 쓰이는 것의 사용을 익히고 그 외의 것은 그때그때 찾아서 사용합니다.

 

-대소문자 변환 함수 사용

 

SELECT employee_id, last_name, department_id

FROM employees

WHERE LOWER(last_name) = 'higgins';

 

WHERE 절은 EMPLOYEES 테이블의 사원 이름을 higgins와 비교하도록 지정하는데, 이때 비교를 위해 LAST_NAME 열을 소문자로 변환합니다. 이제 두 이름이 모두 소문자이기 때문에 일치하는 항목을 찾게 되고 한 행이 선택됩니다. WHERE 절을 다음과 같이 다시 작성해도 동일한 결과를 얻을 수 있습니다. lower을 빼면 소문자로만 적용되기 때문에 아무값도 찾지 못합니다.

 

출력되는 이름은 데이터베이스에 저장된 그대로 나타납니다. 이름을 대문자로 표시하려면 SELECT 문에 UPPER 함수를 사용합니다.

 

SELECT employee_id, UPPER(last_name), department_id

FROM employees

WHERE INITCAP(last_name) = 'Higgins';

 

-문자 조작 함수

• CONCAT: 값을 함께 연결합니다.(CONCAT에서 사용할 수 있는 파라미터는 두 개로 제한됩니다.)

• SUBSTR: 지정된 길이의 문자열을 추출합니다.

• LENGTH: 문자열 길이를 숫자 값으로 표시합니다.

• INSTR: 이름 지정된 문자의 숫자 위치를 찾습니다.

• LPAD: 길이가 n이 되도록 왼쪽부터 문자식으로 채운 표현식을 반환합니다.

• RPAD: 길이가 n이 되도록 오른쪽부터 문자식으로 채운 표현식을 반환합니다.

• TRIM: 문자열에서 선행 문자나 후행 문자(또는 둘 다)를 자릅니다.(trim_character 또는 trim_source가 문자 리터럴인 경우 작은 따옴표로 묶어야 합니다.)

 

SELECT employee_id, CONCAT(first_name, last_name) NAME, job_id, LENGTH (last_name), INSTR(last_name, 'a') "Contains 'a'?"

FROM employees

WHERE SUBSTR(job_id, 4) = 'REP';

#여러개의 문자함수를 섞어 사용한 예시입니다. job_id의 번째 글자부터 REP인 값들 중에서 NAME이라는 성과 이름을 합친 열, lastname의 길이, INSTR을 이용해서 a가 몇 번 들어 갔는지를 출력해주는 예시입니다.

 

#alias할 때 큰따옴표를 쓸 때와 쓰지 않을 때를 구분할 줄 알아야 합니다.

 

-숫자 함수

 

ROUND(column|expression, n) : 열, 표현식 또는 값을 소수점 n자릿수로 반올림합니다. n이 생략된 경우 소수점 자릿수가 없습니다. (n이 음수인 경우 소수점 왼쪽의 숫자가 반올림됩니다.)

TRUNC(column|expression, n) : 열, 표현식 또는 값을 소수점 n자릿수로 자릅니다. n이 생략된 경우 기본값은 0입니다.

MOD(m,n) : m을 n으로 나눈 나머지를 반환합니다.

 

-ROUND 함수 사용

 

SELECT ROUND(45.923,2), ROUND(45.923,0), ROUND(45.923,-1)

FROM DUAL;

# 각각 순서대로 45.92, 46, 50 이렇게 출력 됩니다. 0은 0자리수로 반올림 이고, -1은 소수점 왼쪽에서 첫째 자릿수로의 반올림을 뜻합니다.

#Trunc 함수도 비슷한 쓰임입니다.

 

-MOD 함수 사용

직책이 Sales Representative인 모든 사원에 대해 급여를 5,000으로 나눈 나머지를 계산합니다.

 

SELECT last_name, salary, MOD(salary, 5000)

FROM employees

WHERE job_id = 'SA_REP';

 

 

-날짜 작업

• 오라클 데이터베이스는 내부 숫자 형식(세기, 년, 월, 일, 시, 분, 초)으로 날짜를 저장합니다.

• 기본 날짜 표시 형식은 DD-MON-RR입니다.

– 연도의 마지막 두 자릿수만 지정하면 21세기 날짜를 20세기 날짜로 저장할 수 있습니다.

– 같은 방식으로 20세기 날짜를 21세기에 저장할 수 있습니다.

 

SELECT last_name, hire_date FROM employees

WHERE hire_date < '01-FEB-88';

 

-RR 날짜 형식

RR 날짜 형식은 YY 요소와 비슷하지만 이 형식을 사용하여 다른 세기를 지정할 수 있습니다. YY 대신 RR 날짜 형식 요소를 사용하면 반환 값의 세기는 지정된 2자리 연도 및 현재 연도의 마지막 2자리에 따라 달라집니다. 슬라이드의 표는 RR 요소의 동작을 요약해서 보여줍니다.

 

현재 연도	지정된 날짜	해석(RR)	해석(YY)
1994	27-OCT-95	1995	1995
1994	27-OCT-17	2017	1917
2001	27-OCT-17	2017	2017
2048	27-OCT-52	1952	2052
2051	27-OCT-47	2147	2047

#헷갈리는 부분이므로 주의합니다.

 

-SYSDATE 함수 사용

 

SYSDATE 함수는 다음 값을 반환합니다.

• 날짜

• 시간

SYSDATE는 현재의 데이터베이스 서버 날짜 및 시간을 반환하는 날짜 함수입니다. SYSDATE는 다른 열 이름을 사용하듯이 사용할 수 있습니다. 예를 들어, 테이블에서 SYSDATE를 선택하여 현재 날짜를 표시할 수 있습니다. DUAL이라는 공용(public) 테이블에서 SYSDATE를 선택하는 것이 일반적입니다.

 

-날짜 연산

• 날짜에 숫자를 더하거나 빼서 결과 날짜 값을 구합니다.

• 두 날짜 사이의 일수를 알아내기 위해 빼기 연산을 합니다.

• 시간 수를 24로 나눠 날짜에 시간을 더합니다.

 

연산	결과	설명
날짜 + 숫자	날짜	날짜에 일 수를 더합니다.
날짜 – 숫자	날짜	날짜에서 일 수를 뺍니다.
날짜 – 날짜	일 수	한 날짜를 다른 날짜에서 뺍니다.
날짜 + 숫자/24	날짜	날짜에 시간 수를 더합니다.

 

SELECT last_name, (SYSDATE-hire_date)/7 AS WEEKS

FROM employees

WHERE department_id = 90;

# 부서 90의 모든 사원에 대해 성 및 근속 시간(주 단위 )을 표시합니다. 현재 날짜(SYSDATE)에서 사원이 채용된 날짜를 빼고 결과를 7로 나누어 근속 시간을 주 단위로 계산합니다.

#소수점이 많이 나타나므로 round((SYSDATE-hire_date)/7, 0) WEEKS 이렇게 사용도 가능합니다.

 

 

-날짜 조작 함수

숫자 값을 반환하는 MONTHS_BETWEEN을 제외한 모든 날짜 함수는 DATE 데이터 유형의 값을 반환합니다.

• MONTHS_BETWEEN(date1, date2): date1과 date2 사이의 월수를 찾습니다. 결과는 양수나 음수가 될 수 있습니다. date1이 date2보다 늦은 날짜인 경우 결과는 양수이고 date1이 date2보다 앞선 날짜인 경우 결과는 음수입니다. 결과에서 정수가 아닌 부분은 월의 일부분을 나타냅니다.

• ADD_MONTHS(date, n): date에 월수n을 추가합니다. n 값은 정수여야 하며 음수가 될 수도 있습니다.

• NEXT_DAY(date,'char'): date 다음에 오는 지정된 요일('char')의 날짜를 찾습니다. char 값은 요일을 나타내는 숫자나 문자열이 될 수 있습니다.

• LAST_DAY(date): date에 해당하는 날짜가 있는 월의 말일 날짜를 찾습니다.

 

날짜 함수 사용

함수	결과
MONTHS_BETWEEN ('01-SEP-95','11-JAN-94')	19.677419
ADD_MONTHS (‘31-JAN-96',1)	'29-FEB-96'
NEXT_DAY ('01-SEP-95','FRIDAY')	'08-SEP-95‘
LAST_DAY ('01-FEB-95')	'28-FEB-95‘