정보보안공부
Oracle_ select명령을 이용한 단일행 함수 본문
SQL에서 사용되는 함수는 크게 단일행 함수와 복수행 함수로 구분할 수 있다. 단일행 함수는 여러 건의 데이터를 한꺼번에 처리하는 것이 아니라 한번에 하나씩 처리하는 함수이다. 반면 복수행 함수는 여러 건의 데이터를 동시에 입력을 받아서 결과값 1건을 만들어 주는 함수이다.
단일행 함수로는 문자함수, 숫자함수, 날짜함수, 변환함수, 일반함수가 있다.
# 문자함수
## INITCAP('컬럼 또는 문자열')
-> 첫글자만 대문자로 출력하고 나머지는 전부 소문자로 출력하는 함수
공백이 있는 경우 공백다음도 첫 글자로 생각해서 대문자로 출력된다.
## LOWER('컬럼 또는 문자열')
-> 입력되는 값을 전부 대문자로 변경하여 출력한다.
## UPPER('컬럼 또는 문자열')
-> 입력되는 값을 전부 대문자로 변경하여 출력한다.
## LENGTH('컬럼 또는 문자열') / LENGTHB('컬럼 또는 문자열')
-> 입력된 문자열의 길이(바이트 수)를 계산해주는 함수
한글일경우 LENGTH와 LENGTHB가 차이가 있다는 것을 확인할 수 있다.
한글은 한글자당 2바이트이다.
## CONCAT('문자열1', '문자열2') : || 연산자와 동일
-> || 연결연산자와 같은기능으로 문자와 문자를 이어준다.
## SUBSTR('컬럼 또는 문자열', 위치, 몇개)
-> 주어진 문자열에서 특정 길이의 문자만 골라낼 때 사용하는 함수이다.
주민번호에서 생년월일을 알고싶을때 특정 월일만 출력할 수 있다. 위의 결과를 보면 jumin컬럼에 3번째 위치부터 4개만 출력한다
## INSTR('컬럼 또는 문자열', 찾는 글자, 시작위치, 몇번째인지(기본값은 1))
-> 주어진 문자열에서 찾는문자가 해당위치부터 몇번째에 있는지 알고싶을 때 사용한다.
첫번째 3,1은 3번째 위치인 B부터 1번째로 나오는 - 의 위치이므로 4
두번째 1,3은 1번째 위치인 A부터 3번째로 나오는 - 의 위치이므로 6
세번째 2,3은 2번째 위치인 -부터 3번째로 나오는 - 의 위치이므로 6이다.
## SUBSTR + INSTR
-> 두개를 같이쓰면 유용하게 사용된다.
instr을 이용해 ) 표시가있는 위치를 반환하게되면 ) 표시도 포함되므로 -1을 한 위치까지의 개수를 substr로 잘라낸다.
## LPAD('컬럼 또는 문자열', 자릿수, '채울 문자')
-> 원래 데이터를 오른쪽에 두고 입력한 자릿수가 남아 빈자리가 있는 경우 왼쪽을 특정 기호나 문자로 채울때 사용한다.
10 자리중에 ID값을 제외한 나머지 공간을 왼쪽에 *로 채운다.
## RPAD('컬럼 또는 문자열', 자릿수, '채울 문자')
-> 원래 데이터를 왼쪽에 두고 입력한 자릿수가 남아 빈자리가 있는 경우 오른쪽을 특정 기호나 문자로 채울때 사용한다.
10 자리중에 ID값을 제외한 나머지 공간을 오른쪽에 *로 채운다.
## LTRIM('컬럼 또는 문자열', '제거할 문자')
-> 해당 데이터에서 제거할 문자가 왼쪽 첫번째에 있으면 제거한다.
## RTRIM('컬럼 또는 문자열', '제거할 문자')
-> 해당 데이터에서 제거할 문자가 오른쪽 첫번째에 있으면 제거한다.
## REPLACE('컬럼 또는 문자열', '문자1', '문자2')
-> 주어진 데이터에서 문자1을 문자2로 바꾸어 출력하는 함수이다. 주로 SUBSTR과 같이 사용된다.
# 숫자함수
## ROUND(숫자, 출력을 원하는 자리수)
-> 해당 숫자를 원하는 자리수 까지 반올림해준다.
2 : 소수 두번째 자리까지 반올림
1 : 소수 첫번째 자리까지 반올림
0 : 소수 첫번째 자리에서 반올림
-1: 정수 마지막 자리에서 반올림
## TRUNC(숫자, 원하는 자리수)
-> 해당 숫자를 원하는 자리까지 무조건 버림한다.
해당 자리수까지 버리는것만 빼면 ROUND와 동일하다
## MOD(숫자, 나누려는 수)
-> 주어진 숫자를 나누었을때 나머지를 구한다.
## CEIL(숫자)
-> 주어진 숫자와 가장 가까운 큰 정수를 구한다. ( 무조건 올림 )
## FLOOR(숫자)
-> 주어진 숫자와 가장 가까운 작은 정수를 구한다. ( 무조건 버림 )
## POWER(숫자1, 숫자2)
-> 숫자1의 숫자2제곱이다.
# 날짜함수
## SYSDATE
-> 시스템상에서 현재 날자와 시간을 출력해주는 함수이다.
## MONTHS_BEWEEN(날짜1, 날짜2)
-> 두 날짜 사이의 개월 수를 출력하는 함수이다.
-> 날짜2보다 날짜1이 더커야 양수로 나온다.
-> 날짜1과 날짜2가 같은 달일경우 특정 규칙으로 계산된 값이 나온다.
-> 2월 28일인달과 2월 29일인 달은 구분하지 못한다.
## ADD_MONTHS(날짜,숫자)
-> 주어진 날짜에 숫자만큼의 달을 추가한다.
현재날짜로부터 3개월뒤 날짜
## NEXT_DAY(날짜, 요일)
-> 주어진 날짜를 기준으로 돌아오는 가장 최근 요일의 날짜를 반환해주는 함수이다.
현재날짜(금요일)로부터 가장빨리 돌아오는 화요일날짜
## LAST_DAY(날짜)
-> 주어진 날짜가 속한 달의 마지막 날짜
## ROUND
-> 날짜의 ROUND는 낮 12:00를 기준으로 이시간을 넘어설 경우 다음날짜로 출력하고 이 시간이 안될 경우에는 당일로 출력한다.
## TRUNC
-> 날짜의 TRUNC는 무조건 당일날짜로 출력한다.
# 형변환함수
## TO_CHAR() : 날짜 -> 문자형
-> 날짜를 원하는 문자형태로 변환할 수 있다.
YYYY : 연도를 4자리로 표현한다.
YY: 연도의 끝의 2자리만 표현한다.
MM : 월을 2자리로 표현한다.
MONTH : 월을 뜻하는 이름 전체를 표시한다.
DD : 일을 숫자 2자리로 표시한다.
DAY : 요일에 해당하는 명칭을 표현한다.
HH24 : 하루를 24시간으로 표현한다.
MI : 분
SS : 초
## TO_CHAR() : 숫자형 -> 문자형
-> 숫자를 원하는 문자형으로 변환할 수 있다.
9 : 9의 개수만큼 자릿수
0 : 빈자리를 0으로 채움
$ : $ 표시를 붙여서 표시
. : 소수점 이하를 표시
, : 천 단위 구분 기호를 표시
숫자를 문자형태로 , 와 $를 이용해 표현
## TO_NUMBER('숫자처럼 생긴 문자')
-> 숫자처럼 생긴 문자를 숫자로 바꾸어준다.
숫자처럼 생긴 '5'라는 문자를 숫자로 변환, 5+'5' 와같이 자동형변환이 되서 잘 사용하진 않는다.
## TO_DATE(문자) : 문자 -> 날짜
-> 문자를 날짜형태로 표현한다.
언뜻 보기엔 차이가 없지만 위에는 날짜형태같은 문자형이고 아래는 날짜형태인 날짜형이다.
# 일반함수
## NVL()
-> NULL 값을 만나면 다른 값으로 치환해서 출력하는 함수이다.
NVL(comm,0) : comm값이 null이면 0으로 치환해서 출력한다.
NVL(comm,100) : comm값이 null이면 100으로 치환해서 출력한다.
치환 값이 문자일 경우 : NVL(컬럼, '문자')
## NVL2('컬럼 또는 문자열1','문자열2','문자열3')
-> NULL값이 아닐 경우 출력할 값을 지정할 수 있다.
-> 문자열1이 NULL이 아니면 문자열2, NULL이면 문자열3을 출력한다.
NVL2(comm, sal+comm, sal*0) : comm컬럼값이 null이 아니면 sal+comm, null이면 sal * 0 을 출력한다.
NVL과는 다르게 NULL일때, NULL이 아닐때를 한번에 지정할 수 있다.
## DECODE( )
-> DECODE( A, B, '1', NULL) : A가 B일 경우 '1'을 출력하는 경우 (NULL 생략가능)
deptno가 101이면 'hohoho'를 출력하고 아니면 null을 출력한다 'hohoho'뒤에 , null생략되어있다.
-> DECODE( A, B, '1', '2') : A가 B일 경우 '1'을 출력하고 아닐경우 '2'를 출력하는 경우
deptno가 101이면 'hohoho'를 출력하고 그게 아니면 '***'을 출력한다.
-> DECODE( A, B, '1', C, '2', '3') : A가 B일 경우 '1'을 출력하고 A가 C일 경우 '2'를 출력하고 둘 다 아닐 경우 '3'을 출력하는 경우
deptno가 101이면 'hohoho' 102이면 'gogogo' 103이면 'vovovo' 아무것도 아니면 '***'을 출력한다.
-> DECODE( A, B, DECODE( C, D, '1', NULL ) ) : A가 B일 경우 중에서 C가 D를 만족하면 '1'을 출력하고 C가 D가 아닐 경우 NULL을 출력하는 경우
deptno가 101인 사람중에서 name이 'Audie Murphy'인 사람은 'hoho'로 출력한다.
-> DECODE( A, B, DECODE( C, D, '1', '2') ) : A가 B일 경우 C가 D를 만족하면 '1'을 출력하고 C가 D가 아닐 경우 '2'를 출력하는 경우
deptno가 101인 사람중에서 name이 'Audie Murphy'인 사람은 'hoho'로 name이 'Audie Murphy'이 아닌 사람은 '***'을 출력한다.
-> DECODE( A, B, DECODE(C, D, '1', '2'), '3') : A가 B일 경우 중에서 C가 D를 만족하면 '1'을 출력하고 C가 D가 아닐 경우 '2'를 출력하고 A가 B가 아닐 경우 '3'을 출력하는 경우
deptno가 101인 사람중에서 name이 'Audie Murphy'인 사람은 'hoho'로 name이 'Audie Murphy'이 아닌 사람은 '***'을 출력하고 deptno가 101이 아닌사람은 '@@@'로 출력한다.
## CASE ... WHEN ... THEN ... END ... "컬럼명"
-> DECODE와 같은 역할을 할 수 있다. 차이점은 DECODE함수의 경우 함수내부에 , 로 구분하지만 CASE는 표현식 내부에 ,를 사용하지 않는다.
-> 비교조건이 '='로 사용되는 경우 ( DECODE와 동일 )
-> CASE '문자1'(조건) WHEN '문자2'(조건) THEN '문자3'(결과) ELSE '문자4'(조건불일치) END "컬럼명지정" : 문자1과 문자2가 같다면(비교조건 = ) 문자3출력 같지 않다면 문자4 출력한다.
CASE 뒤와 WHEN 뒤를 서로 비교해서 같으면 THEN 뒤가 출력된다. 아무것도 같지않으면 ELSE 뒤가 출력된다.
-> 비교조건이 '='가 아닌경우
-> CASE WHEN 조건 THEN '문자1'(결과) ELSE '문자2'(조건 불일치) END "컬럼명지정" : WHEN에 있는 조건( >, < 등등)이 일치하면 문자1출력 일치하지 않으면 문자2출력한다.
WHEN뒤에 비교하는 것을 비교연산자를 이용해 작성했을때 조건이 일치하면 THEN뒤가 출력되고 아니면 ELSE 뒤가 출력된다.