SQLD 3-6 ] 함수
❏ 함수 (Function)
- 벤더에서 제공하는 함수인 '내장 함수' 와 '사용자가 정의할 수 있는 함수' 로 나뉜다.
- 내장 함수 에는 단일행함수 / 다중행 함수가 있다.
- 단일행 함수 : 함수의 입력 값이 단일행 값이 입력되는 함수(Single-Row Function)
- 다중행 함수 : 함수의 입력 값이 여러행 값이 입력되는 함수(Multi-Row Function)
- 다중행 함수에는 또 집계함수 / 그룹함수 / 윈도우 함수 로 나뉠 수 있다.
- 함수는 입력되는 값이 아무리 많아도 출력은 하나만 된다는 M:1 관계라는 중요한 특징을 갖고 있다.
- 이번 3-6 함수 절에서는 내장 함수 안에 단일행 함수 이 하나만 다룹니다!
❏ 단일행 함수의 종류
단일행 함수의 특징
1. SELECT, WHERE, ORDER BY 절에 사용 가능하다.
2. 각 행(Row)들에 대해 개별적으로 작용하여 데이터 값들을 조작하고, 각각의 행에 대한 조작 결과를 리턴한다
3. 여러 인자(Argument)를 입력해도 단 하나의 결과만 리턴한다
4. 함수의 인자(Argument)로 상수, 변수, 표현식 이 사용가능하고, 하나의 인수를 가지는 경우도 있지만 여러개의
인수를 가질 수도 있다.
5. 특별한 경우가 아니면 함수의 인자(Argument)로 함수를 사용하는 함수의 중첩이 가능하다.
이런 특징들 왠지 알아둬야할것 같기도 하고. 단일행함수의 특징 중 올바르지 않은것을 고르라는 문제가 나올수도
❏ 문자형 함수
- 문자를 입력하면 문자나 숫자 값을 반환한다.
한번씩 끄적여봐야겠다
1. LOWER(문자열) : 문자열을 모두 소문자로.
2. UPPER(문자열) : 문자열을 모두 대문자로.
3. ASCII(문자) : 문자,숫자 -> 아스키번호.
4. CHR(아스키번호) : 아스키번호 -> 문자, 숫자
5. CONCAT(문자열1, 문자열2) : 문자열1과 문자열2 잇기.
6. SUBSTR(문자열, m, n) : 문자열 중 처음부터 m 번째 위치에서 n 개.
7. LENGTH(문자열) : 문자열의 갯수 리턴
8. LTRIM(문자열, 지정문자) : 왼쪽부터 지정문자를 검색해서 trim. 잘라낸다. 1회. 한번 잘라내는 활동 하면 끝.
9. RTRIM(문자열, 지정문자) : 오른쪽부터 지정문자 검색. // SQL SERVER 에서는 지정문자가 '공백' 만 잘라낼수있다
지정문자가 생략되면 공백 이 default.
10. TRIM( [옵션] 지정문자 from 문자열) : 지정문자를 문자열에서 제거하는데.. (옵션 생략하면 기본 both)
옵션 에는 머리말(leading), 꼬리말(trailing), 양쪽(both) 가 있다. 무슨 차이가 있는지 보자
select trim( 옵션 'x' from 'xxYYZZxYZxx') from dual;
TRIM(BOTH)
------------
YYZZxYZ
TRIM(LEADING)
----------------
YYZZxYZxx
TRIM(TRAILING)
----------------
xxYYZZxYZ
both 는 ->> 문자열 <<- 이렇게 양쪽에서 x를 1회 쳐내는거고
leading 는 ->> 문자열
trailing 는 문자열 <<-
leading 과 trailing 은 LTRIM, RTRIM 이랑 기능이 같은데,,??;;
그냥 both 쓰는 용도로 trim 을 쓴다고 생각하자.
- LENGTH(Oracle vs Mssql)
Oracle SELECT LENGTH('SQL Expert') FROM DUAL; result = 10 Mssql SELECT LEN('SQL Expert') result = 10 - CONCAT(Oracle vs Mssql)
Oracle SELECT CONCAT('SQL', ' Expert') FROM DUAL; result = SQL Expert Mssql SELECT 'SQL'+ ' Expert' result = SQL Expert
- LENGTH + CONCAT(Oracle vs Mssql)
Oracle SELECT LENGTH(CONCAT('SQL', ' Expert')) FROM DUAL; result = 10 Mssql SELECT LEN('SQL'+ ' Expert') result = 10
보면 다 from dual; 로 되있다. oracle 은 select ~ from ~ 이 구문의 필수라서
from 무조건 써야되서 그냥. dual 이라는 테이블을 써준다. 뭐 그냥 테스트? 용도의 테이블 정도라고 생각.
DUAL 테이블의 특성
1. 사용자 SYS 가 소유하며 모든 사용자가 액세스 가능한 테이블이다
2. SELECT ~ FROM ~ 의 형식을 갖추기 위한 일종의 DUMMY 테이블이다.
3. DUMMY 라는 문자열 유형에 칼럼에 'X' 라는 값이 들어 있는 행을 1건 포함 하고 있다.
반면 SQL Server 에서는 dual이 없습니다..
함수는 여러개 중첩해서 사용이 가능하다. 함수 내부에 다른 함수를 사용하며
안쪽에 위치해 있는 함수부터 실행되어 그 결과의 값이 바깥쪽의 함수에 인자로 사용된다.
기본적인 예.. length 길이 구하는 함수에 칼럼이나 표현식. 이런것도 된다.
LENGTH(DDD||TEL)
❏ 숫자형 함수
- 숫자 데이터를 입력받아 숫자를 리턴.
2. SIGN(숫자) : 숫자 가 양수이면 1, 0이면 0, 음수면 -1 을 리턴
3. MOD(숫자1, 숫자2) : 숫자1 을 숫자2로 나눠서 나머지 값을 리턴. % 로도 쓸수 있다. MOD(7,3) = 7%3
4. CEIL(숫자) : 숫자보다 크거나 같은 최소 정수 리턴. ceiling 우리말로 천장.. roundup 처럼 그냥 올림 인거같은데?
ceil(38.123) 이면 올려버리면 39 라서? -38.123 도 올리면 -38 이고.
5. FLOOR(숫자) : 걍 내림.
6. ROUND(숫자, m) : m+1 자리에서 반올림해서 리턴. m이 없으면 디폴트로는 0.
저기 위에 예제 보는게 제일 이해가 빠르다.
7. TRUNC(숫자, m) : m+1 자리에서 잘라서 버린다. SQL SERVER 에서는 없는 함수.
floor 랑 똑같이 내려 버리는건데. 차이점은 floor 은 정수 단위로 내려버리는거고 이 trunc 는
소숫점 자리를 정해서 내릴수 있구나
8. SIN(숫자), COS(숫자), TAN(숫자) ... : 아니 이런것도..??!
9. EXP(), POWER(), SQRT(), LOG(), LN() : 지수, 거듭제곱, 제곱근, 자연로그 ... ??!
❏ 날짜형 함수
- DATE 타입의 값을 연산하는 함수
1. SYSDATE : 현재 날짜와 시각을 출력한다. 오라클에서는 날짜밖에 안나오던데.. sql server 에서는 시간 까지 나온다.
2. EXTRACT( year | month | day from date ) : date 에서 년/월/일 을 뽑아 낼 수 있다.
| select ename, hiredate, extract (year from hiredate) as 입사년도 from amp; |
ENAME HIREDATE 입사년도 ---------- -------- ---------- JAMES 81/12/03 1981 FORD 81/12/03 1981 MILLER 82/01/23 1982 |
3. TO_NUMBER ( TO_CHAR( date, 'YYYY' )) : 2번이랑 똑같은 뜻. 먼저 TO_CHAR 함수로 date 에서 yyyy 추출 후
TO_NUMBER 로 숫자로 변환. 'MM', "DD' 도 있다.
TO_NUMBER 를 안쓴다면 형태 는 그냥 문자형으로 되있겠지.
❏ 변환형 함수
- 특정 데이터 타입을 다양한 출력하고 싶을 경우에 사용.
암시적 데이터 유형 변환은 성능저하가 발생할 수 있고, 또 알아서 계산을 못하면... 에러가 나기 때문에
명시적 데이터 유형 변환을 사용하는게 좋다!
1. TO_NUMBER (문자열) : 문자열 타입을 숫자 타입으로 변환
2. TO_CHAR (숫자|날짜 , format) : 숫자나 날짜를 문자형 타입으로 변환하는데, 뒤에 format 에 따라 달라진다.
(1) 날짜 format 대표적 2가지
| select to_char(sysdate, 'yyyy/mm/dd') as 날짜, to_char(sysdate, 'yyyy, mon, day') as 문자형 from dual; |
날짜 문자형 ---------- ------------------------- 2016/02/23 2016, 2월 , 화요일 |
(2) 숫자 format 대표적 2가지
| select to_char(123456789/1200, '$999,999,999.99') as 환율반영달러, to_char(123456789, 'L999,999,999') as 원화 from DUAL; |
환율반영달러 원화 ---------------- ---------------------- $102,880.66 ￦123,456,789 |
❏ CASE 표현
- if - then - else - end 논리와 유사한 방식으로 표현식을 작성해서 비교연산 기능을 보완한다.
- 방법은 Simple case expression 과 Searched case expression 두 가지 방법이 있다.
(1) simple case expression
- 간단하다! 바로 예로 보자
| select LOC, case LOC when 'NEW YORK' then 'EAST' when 'CHICAGO' then 'CENTER' else 'ETC' end as AREA from dept; |
LOC AREA ------------- ------ NEW YORK EAST DALLAS ETC CHICAGO CENTER BOSTON ETC |
select LOC 칼럼이랑,
case LOC 칼럼안에 when(if) 'NEW YORK' 이면(then) 'EAST'
'CHICAGO' 이면 'CENTER' 아니면(else) 'ETC'
마지막으로 이 한 칼럼의 이름은 AREA 로 할께요.
- ORACLE 의 DECODE 함수와 같은 기능이라고 한다.
DECODE 로도 한번 해볼까.
- DECODE(표현식, 기준값1, 값1, 기준값2, 값2, .... , 디폴트 값)
| select loc, decode(loc, 'NEW YORK', 'EAST', 'CHICAGO', 'CENTER', 'ETC') as AREA from dept; |
LOC AREA ------------- ------ NEW YORK EAST DALLAS ETC CHICAGO CENTER BOSTON ETC |
as area 라고 별칭 안해주면 그냥 출력할때 칼럼명이 decode 라고 뜨넹
(2) searched case expression
- 심플 표현은 그냥 = 으로 같은지, 아닌지. 이퀄 조건만 이었는데..
- 이건 여러 조건 < <= >= .. 이런 다양한 조건들을 사용할 수 있다!
| select ENAME, case when SAL >= 3000 then 'HIGH' when SAL >= 1000 then 'MID' else 'LOW' end as SALARY_GRADE from emp; |
ENAME SALARY_GRADE ---------- --------- JAMES LOW FORD HIGH MILLER MID |
보면... 원래 심플 표현은 case 다음 대상 칼럼명 하나 잡아두고 했는데
여긴 좀 자유로운 느낌 case 로 스타트 끊고 그냥 when 으로 조건 달고 then ..
다른점은 심플은 한 칼럼 잡고 딱딱하게 그게 이거면 뭐 그게 이거면 뭐.
서치는 그냥. 조건 뭐가 뭐이상이면 뭐고.. 이거 하나 차이 인거 같다.
이거 한번 짜보세요
< 사원정보 에서 급여가 2000 이상이면 보너스를 1000으로, 1000 이상이면 500으로, 1000미만이면 0으로 계산한다 >
< 사원정보 테이블명 : EMP, 급여 칼럼명 : SAL, 출력해야할 칼럼명 : ENAME, SAL, BONUS >
답:
| SELECT ENAME, SAL, CASE WHEN SAL >= 2000 THEN 1000 ELSE (CASE WHEN SAL >= 1000 THEN 500 ELSE 0 END) END as BONUS FROM EMP; |
SELECT ENAME, SAL, CASE WHEN SAL >= 2000 THEN 1000 WHEN SAL >= 1000 THEN 500 ELSE 0 END as BONUS FROM emp; |
왼쪽이나 오른쪽이나 같은건데, 보통 그냥 생각대로 짜려면 오른쪽으로 하는게 편한데
왼쪽은 중첩해서 사용할 수 있는걸 보여주려고 한겁니다. 저렇게 else 안에 또 표현식 처음부터 넣고..
난 오른쪽이 편하다
❏ NULL 관련 함수
- NULL 의 특징 다시한번 알고 가자. 3-5에서 널 특징 한거같은데 . 무슨 신인것처럼 말한거. 또 설명하고 가는거 보니
중요해서 그런가..?
1. NULL 값은 아직 정의되지 않은 값으로 0 또는 공백과 분명히 다르다. 0은 숫자고, 공백은 문자다.
2. 테이블을 생성할 때 NOT NULL 또는 PRIMARY KEY 로 정의되지 않은 모든 데이터 유형은 NULL 값을 포함할 수 있다
3. NULL 값을 포함하는 연산의 경우 결과도 NULL이다. (기억난다. 사칙연산 결과는 NULL 이고 논리는 false 라고했지)
4. 결과값을 NULL 이 아닌 다른값을 얻고자 할때 쓰는게 NVL/ISNULL 이다.
NULL 값의 대상이 숫자 유형인 데이터 경우에는 주로 0 으로
문자 유형 데이터 경우면 공백 보다는 'x' 같이 의미 없는 문자로 바꾸는 경우가 많다
(1). NVL(판단 대상, 'NULL 일때 대체값')
- 대상이 NULL 이면 대체값을 출력하는데, 단 둘다 타입이 같아야한다!!!
| select player_name 선수명, position, NVL(position, '없음') 포지션 from player where team_id = 'K08'; |
선수명 POSITION 포지션
-------------------- ---------- ----------
차경복 DF DF
정학범 없음
안익수 없음
차상광 없음
권찬수 GK GK
|
근데, NVL 함수를 다중행 함수의 인자로 사용하는 경우 오히려 부하가 발생해서 굳이 NVL 함수를 사용할 필요가 없다
다중행함수는 입력값으로 전체 건수가 NULL 값인 경우만 함수의 결과가 NULL 이 나오고 일부만 나오면 제외한다.
무슨말인지는 잘 모르겠지만. 자세한건 3-7에서...
(2) NULL과 공집합
1. 일반적인 NVL 함수 사용
- 매니저가 NULL 인 경우 빈칸이 아닌 9999로 출력해라
- select NVL(MGR, 9999) as MGR from EMP where ENAME = 'KING';
- 그냥 흔히 쓰는 NVL 형식.
2. 공집합의 NVL 함수 사용
- 공집합..? 조건에 맞는 데이터가 한 건도 없는 경우를 공집합이라고 한다. NULL 데이터와는 다르다.
- select NVL(MAX(MGR), 9999) as MGR from EMP where ENAME ='JSC';
- 음.. 집계함수를 인수로 한 NVL 함수를 이용해서 공집합인 경우에도 빈칸이 아닌 9999로 출력한다,.
- 공집합의 경우는 NVL 함수를 사용해도 공집합이 출력되므로, 그룹함수와 NVL 함수를 같이 사용한다.
- select 를 했는데 결과가 공집합입니다! 를 알려줘야 하니.. 이렇게 쓰는구나. 공집합 != NULL
(3) NULLIF
- NULLIF (표현식1, 표현식2) : 표현식1과 표현식2 가 같으면 NULL을, 같지 않으면 표현식1 을 리턴한다.
특정값을 NULL 을 대체할때 유용하다는데..?
- MGR 와 7698이 같으면 NULL 표시하고 같지않으면 MGR 을 표시한다.
- SELECT ENAME, EMPNO, MGR, NULLIF(MGR,7698) as NUIF FROM EMP;
유용한건지는 아직 모르겠네
(4) COALESCE
- 어려운 단어이다.. 임의의 개수 EXPR 에서 NULL 이 아닌 최초의 EXPR 을 나타낸다..?
만약 모든 EXPR 이 NULL 이라면 NULL 을 리턴한다 . . ?
- COALESCE (EXPR1, EXPR2, ..... )
- 우선순위로 1번째 부터 검사.. NULL 이 없으면 1번째꺼 쓰고, 만약 1번째꺼가 NULL 이다. 그럼
두번째꺼 갖다쓰고. 다 NULL 이면 NULL. 예제로 보자
- SELECT ENAME, COMM, SAL, COALESCE (COMM, SAL) as COAL FROM EMP;
ENAME COMM SAL COAL
---------- ---------- ---------- ----------
SMITH 800 800
ALLEN 300 1600 300
WARD 500 1250 500
JONES 2975 2975
MARTIN 140 1250 1400
BLAKE 2850 2850
CLARK 2450 2450
SCOTT 3000 3000
이렇게.