검색결과 리스트
CASE에 해당되는 글 1건
- 2015.08.29 [SQLD] 2과목 SQL 기본및 활용 - 1장 6절 함수
글
[SQLD] 2과목 SQL 기본및 활용 - 1장 6절 함수
1. 내장함수 개요
- 함수는 벤더에서 제공하는 함수인 내장함수와 사용자가 정의할 수 있는 사용자 정의함수로 구분할 수 있다.
- 내장함수는 함수의 입력값이 단일행 값이 되는 단일행 함수와 여러개의 값이 입력되는 다중행 함수로 구분할 수 있다.
- 다중행 함수는 집계함수, 그룹함수, 윈도우함수로 나눌 수 있다.
* 함수는 입력되는 값이 아무리 많아도 출력은 하나만 된다는 M:1관계라는 특징을 가지고있다.
1) 단일행 함수의 종류 ( 오라클 / SQL server)
종류 | 내용 | 함수의 예 |
문자형 함수 | 문자를 입력하면 문자나 숫자 값을 반환한다. | LOWER, UPPER, SUBSTR/SUBSTRING, LENGTH/LEN, LTRIM, RTRIM, TRIM, ASCII |
숫자형 함수 | 숫자를 입력하면 숫자 값을 반환한다 | ABS, MOD, ROUND, SIGN, CHR/CHAR, CEIL/CEILING, FLOOR, EXP,LOG, LN, POWER, SIN, COS, TAN |
날짜형 함수 | DATE 타입의 값을 연산한다. | SYSDATE/GENDATE, EXTRACT/DATEPART, TO_NUMBER(TOCHAR(d.'yyyy' | 'MM'|'DD')) / YEAR | MONTH | DAY |
변환형 함수 | 문자, 숫자, 날짜형 값의 데이터 타입을 변환한다. | TO_NUMBER, TO_CHAR, TO_DATE / CAST, CONVERT |
NULL 관련 함수 | NULL을 처리하기 위한 함수 | NVL/ISNULL, NULLIF, COALESCE |
* 단일행 함수의 중요한 특징.
- SELECT, WHERE, ORDER BY 절에 사용 가능하다.
- 각 행들에 대해 개별적으로 작용하여 데이터 값들을 조작하고, 각각의 행에 대한 조작 결과를 리턴한다.
- 여러 인자를 입력해도 단 하나의 결과만 리턴한다.
- 함수의 인자로 상수, 변수, 표현식이 가능하며 하나의 인수를 가지는 경우도 있지만 여려개의 인자를 가질 수 있다.
- 특별한 경우가 아니면 함수의 인자로 함수를 사용하는 함수의 중첩이 가능하다.
2. 문자형 함수.
- 문자 데이터를 매개변수로 받아들여서 문자나 숫자값의 결과를 돌려주는 함수이다.
몇몇 문자형 함수의 경우는 결과를 숫자로 리턴하는 함수도 있다.
문자형 함수 | 함수 설명 |
LOWER(문자형) | 문자열의 알파벳 문자를 소문자로 바꿔준다. |
UPPER(문자형) | 문자열의 알파벳 문자를 대문자로 바꿔준다. |
ASCII(문자) | 문자나 숫자를 ASCII코드 번호로 바꿔준다. |
CHR/CHAR(ASCII 번호) | ASCII 코드 번호를 문자로 바꿔준다. |
CONCAT(문자열1, 문자열2) | 문자열1과 문자열2를 연결한다 - 오라클의 || - SQL server의 + 와 같은 역할 |
SUBSTR/ SUBSTRING (문자열, m [,n]) | 문자열 중 m위치에서 n 개의 문자길이에 해당하는 문자를 돌려준다.(n이 생략될 경우 마지막 문자까지) |
LENGTH /LEN(문자열) | 문자열의 개수를 숫자값으로 돌려준다. |
LTRIM (문자열 [, 지정문자]) | 문자열의 첫 문자부터 확인해서 지정문자가 나타나면 해당 문자를 제거한다. SQL server경우 LTRIM함수에 지정문자를 사용할 수 없다. 즉 공백만 제거한다. |
RTRIM (문자열 [,지정문자]) | 문자열의 마지막 문자부터 확인해서 지정 문자가 나타나는 동안 해당 문자를 제거한다. SQL server경우 RTRIM함수에 지정문자를 사용할 수 없다. 즉 공백만 제거한다. |
TRIM ([leading | trailing | both 지정문자 FROM 문자열) | 문자열에서 머리말, 꼬리말 또는 양쪽에 있는 지정 문자를 제거한다. (leading | trailing | both가 생략되면 both가 디폴트) SQL server경우 TRIM함수에 지정문자를 사용할 수 없다. 즉 공백만 제거한다. |
* 문자형 함수 적용 시 리턴되는 결과
LOWER('SQL Expert') | sql expert |
UPPER('SQL Expert') | SQL EXPERT |
ASCII('A') | 65 |
CHR(65) / CHAR(65) | A |
CONCAT('RDBMS','SQL') 'RDBMS' || 'SQL' / | RDBMSSQL |
SUBSTR('SQL Expert',5,3) / SUBSTRING('SQL Expert',5,3) | Exp |
SUBSTR('SQL Expert',5) | Expert ( 맨끝까지 출력) |
LENGTH('SQL Expert') / LEN('SQL Expert') | 10 (공백도 길이로 간주) |
LTRIM('xxxYYYZZxYZ','x') | YYYZZxYZ (왼쪽부터 x를 지우며 x와 다른값을 만나는 이후 문자열에 대해서는 전체다 출력.) |
RTRIM('XXYYzzXYzz','z') | XXYYzzXY (오른쪽부터 x를 지우며 x와 다른값을 만나는 이후 문자열에 대해서는 전체다 출력.) |
RTRIM('XXYYzzXYzz ') | XXYYzzXYzz * 공백 제거 결과출력 공백제거 및 CHAR와 VARCHAR 데이터 유형을 비교할 때 용이하게 사용됨. |
TRIM('x' FROM 'xxYYZZxYZxx') | YYZZxYZ |
TRIM(leading 'x' FROM 'xxYYZZxYZxx') | YYZZxYZxx (LTRIM과 같은결과) |
TRIM(trailing 'x' FROM 'xxYYZZxYZxx') | xxYYZZxYZ (RTRIM과 같은결과) |
3. 숫자형 함수
- 숫자데이터를 입력받아 처리하고 숫자를 리턴한다.
숫자형 함수 | 함수설명 |
ABS(숫자) | 숫자의 절대값 리턴 |
SIGN(숫자) | 숫자가 양수인지 음수인지 0 인지 구분 |
MOD(숫자1,숫자2) | 숫자1을 숫자2로 나눈 값의 나머지 리턴 % 연선자로 대체가능 (7%3) |
CEIL/CEILING(숫자) | 숫자보다 크거나 같은 최소 정수를 리턴한다. |
FLOOR(숫자) | 숫자보다 작거나 같은 최대 정수를 리턴한다. |
ROUND(숫자 [, m]) | 숫자를 소수점 m+1자리에서 반올림하여 리턴. m이 생략되면 디폴트 값은 0 |
TRUNCATE(숫자 [,m]) | 숫자를 소수 m+1 자리에서 버린 후 리턴 m 이 생략되면 디폴트 값은 0 SQL server에서는 제공하지 않는다. |
SIN, COS, TAN | 삼각함수 값을 리턴 |
EXP() POWER() SQRT() LOG() LN() | 숫자의 지수 리턴 거듭제곱 리턴 제곱근 리턴 자연로그값 리턴 |
- 숫자형 함수 적용되었을 때 리턴되는 값
숫자형 함수 사용 | 결과 값 및 설명 |
ABS(-15) | 15 |
SIGN(-20) | -1 |
SIGN(0) | 0 |
SIGN(20) | 1 |
MOD(7/3) | 1 |
7%3 | 1 |
CEIL(38.123) / CEILING(38.123) | 39 |
CEIL(38) / CEILING(38) | 38 (같으면 같은값) |
CEIL(-38.123) / CEILING(- 38.123) | -38 ( -38이 -38.123 보다 크므로) |
FLOOR(38,123) | 38 |
FLOOR(-38.123) | -39 (-39가 -38.123 보다 작으므로) |
FLOOR(-38) | -38 (같으면 같은값) |
ROUND(38.5235,3) | 38.524 ( 3째 자리까지 표현 4째자리 반올림) |
ROUND(38.5235,1) | 38.5 (소수 첫재짜리 까지 표현 둘째자리 반올림) |
ROUND(38.5235,0) | 39 |
ROUND(38.5235) | 39 (인수가 없을 경우 0이 DEFAULT) |
TRUNC(38.5235,3) | 38.523 |
TRUNC(38.5235,1) | 38.5 |
TRUNC(38.5235,0) | 38 |
TRUNC(38.5235) | 38(인수가 없을 경우 0이 DEFAULT) |
4. 날짜형 함수
- 날짜형 함수는 DATE 타입의 값을 연산하는 함수
날짜형 함수 | 함수 설명 |
SYSDATE / GETDATE() | 현재 날짜와 시각을 출력한다. |
EXTRACT('YEAR' | 'MONTH' | DAY' from d) / DATEPART('YEAR' | 'MONTH' | DAY ,d) | 날짜 데이터에 년/월/일 데이터를 출력할 수 있다. 시간 / 분 /초도 가능함 |
TO_NUMBER(TO_CHAR(d,'YYYY') / YEAR(d), TO_NUMBER(TO_CHAR(d,'MM') / MONTH(d), TO_NUMBER(TO_CHAR(d,'DD') / DAY(d) | 날짜 데이터에서 년/월/일 데이터를 출력할 수 있다. ORACLE EXTRACT YEAR/MONTH/DAY옵션이나 SQL SERVER DEPART YEAR/MONTH/DAY 옵션과 같은 기능이다. TO_NUMBER 함수 제외시 문자로 출력. |
- 단일행 날짜형 데이터 연산
연산 | 결과 | 설명 |
날짜 + 숫자 | 날짜 | 숫자만큼의 날수를 날짜에 더한다 |
날짜 - 숫자 | 날짜 | 숫자만큼의 날수를 날짜에 뺀다 |
날짜1 - 날짜2 | 날짜 수 | 다른 하나의 날짜에서 하나의 날짜를 빼면 일수가 나온다 |
날짜 + 숫자/24 | 날짜 | 시간을 날짜에 더한다 |
ex) 오라클
SELECT ENAME, HIREDATE,
TO_NUMBER(TO_CHAR(HIREDATE,'YYYY')) YYYY,
TO_NUMBER(TO_CHAR(HIREDATE,'MM')) MM,
TO_NUMBER(TO_CHAR(HIREDATE,'DD')) DD
FROM EMP;
ENAME HIREDATE YYYY MM DD
---------- --------- ---------- ---------- ----------
seungho 17-DEC-80 1980 12 17
ALLEN 20-FEB-81 1981 2 20
WARD 22-FEB-81 1981 2 22
JONES 02-APR-81 1981 4 2
ex) SQL server 함수
SELECT ENAME, HIREDATE,
DATEPART(YEAR, HIREDATE) 입사년도,
DATEPART(MONTH, HIREDATE) 입사월,
DATEPART(DAY, HIREDATE) 입사일
FROM EMP;
ex) SQL server 함수2
SELECT ENAME, HIREDATE
YEAR(HIREDATE) 입사년도,
MONTH(HIREDATE) 입사월,
DAY(HIREDATE) 입사일
FROM EMP;
5. 변환형 함수
- 특정 데이터 타입을 다양한 형식으로 출력하고 싶을 경우에 사용되는 함수
종류 | 설명 |
명시적 데이터 유형 변환 | 데이터 변환형 함수로 데이터 유형을 변환하도록 명시해 주는 경우 |
암시적 데이터 유형 변환 | 데이터베이스가 자동으로 데이터 유형을 변환하여 계산하는 경우 |
* 암시적 데이터 유형변환의 경우 성능 저하가 발생할 수 있으며 자동적으로 데이터베이스가 알아서 계산하지 않는 경우가 있어서 에러를 발생할 수 있으므로 명시적인 데이터 유형 변환방법을 사용하는것이 바람직함.
변환형 함수- ORACLE | 함수설명 |
TO_NUMBER(문자열) | alhanumeric 문자열을 숫자로 변환한다. |
TO_CHAR(숫자 | 날짜 [, FORMAT]) | 숫자나 날짜를 주어진 FORMAT 형태로 문자열 타입으로 변환한다 |
TO_DATE(문자열 [, FORMAT]) | 문자열을 주어진 FORMAT 형태로 날짜 타입으로 변환한다. |
변환형 함수 - SQL server | 함수 설명 |
CAST (expression AS data_type [(length)]) | expression을 목표 데이터 유형으로 변환한다. |
CONVERT (data_type [(length)], expression [,style]) | expression을 목표 데이터 유형으로 변환한다. |
EX) 오라클
SCOTT@PROD>select sysdate from dual;
SYSDATE ---------
28-AUG-15 SCOTT@PROD>select TO_CHAR(SYSDATE,'YYYY/MM/DD') 2 FROM DUAL;
TO_CHAR(SY ---------- 2015/08/28
SCOTT@PROD>SELECT TO_CHAR(SYSDATE,'YYYY,MON,DAY') 2 FROM DUAL;
TO_CHAR(SYSDATE,'YYYY,MON,DAY') ------------------------------------------------------ 2015,AUG,FRIDAY
SCOTT@PROD>SELECT TO_CHAR(SYSDATE,'YYYY.MM.DD') FROM DUAL;
TO_CHAR(SY ---------- 2015.08.28
|
EX) SQL server
SELECT CONVERT(VARCHAR(10)M GETDATE(),11) AS CURRENTDATE CURRENT DATE ________________ 2015/08/28 |
EX) 금액을 달러와 원화로 표기
SCOTT@PROD>SELECT TO_CHAR(123456789/1200,'$999,999,999.99') 2 FROM DUAL;
TO_CHAR(12345678 ---------------- $102,880.66
SELECT TO_CHAR(123456789/1200,'$9999999.99') FROM DUAL; TO_CHAR(1234 ------------
$102880.66 * 원하는 형식을 9와 , 로 자를 수 있다. SELECT TO_CHAR(123456789/1200,'L9999999.99') FROM DUAL; TO_CHAR(1234 ------------
₩102,880.66 * L을 쓰면 로컬 화폐 단위로 출력한다. |
6. CASE 표현
- IF -THAN ELSE 논리와 유사한 방식
- ANSI/ISO SQL 표준에서는 CASE Expression이라고 표시되어 있는데 함수와 같은 성격을 갖는다.
- ORACLE의 Decode 함수와 같은 기능을 한다.
SELECT ENAME
CASE WHEN SAL > 2000
THEN SAL
ELSE 2000
END REVISED_SALARY
FROM EMP;
CASE 표현 | 함수설명 |
CASE SIMPLE_CASE_EXPRESSION 조건 ELSE 표현절 END | SIMPLE_CASE_EXPRESSION 조건이 맞으면 SIMPLE_CASE_EXPRESSION 조건 내의 THEN 절을 수행하고 조건이 맞지 않는 경우 ELSE 절을 수행한다. |
CASE SEARCHED_CASE_EXPRESSION 조건 ELSE 표현절 END | SEARCHED_CASE_EXPRESSION 조건이 맞으면 SEARCHED_CASE_EXPRESSION 조건내의 THEN 절을 수행하고 조건이 맞지 않는경우 ELSE 절을 수행한다 |
DECODE( 표현식, 기준값1, 값1 [, 기준값2,값2,...., 디폴드값]) | ORACLE에서만 사용되는 함수로 표현식의 값이 기준값1이면 값1을 출력하고 기준값2임ㄴ 값2를 출력한다. 그리고기준값이 없으면 디폴트 값을 출력한다. CASE 표현의 SIMPLE_CASE_EXPRESSION 조건절과 동일하다. |
- SIMPLE_CASE_EXPRESSION
* CASE 조건 뒤에 바로 조건에 사용되는 칼럼이나 표현식을 표시하고 WHEN 절에서 앞에서 정의한 칼럼이나 표현식과 같은지 판단하는 문장으로 (=) 조건만 사용한다면 SEARCHED_CASE_EXPRESSION보다 간단하게 사용할 수 있는 장점이 있다.
CASE
EXPR WHEN COMPARISON_EXPR THEN RETURN_EXPR
ELSE 표현절
END
SELECT LOC CASE LOC WHEN 'NEW YORK' THEN 'EAST' WHEN 'BOSTON' THEN 'EAST' ELSE 'ETC' END as AREA FROM DEPT; LOC AREA ---------------- ---------- NEW YORK EAST BOSTON EAST |
- SEARCHED_CASE_EXPRESSION
*SEARCHED_CASE_EXPRESSION 다음에 칼럼이나 표현식을 표시하지않고 다음 WHEN 절에서 EQUI(=), >, >= , <, <= 을 이용한 조건절을 사용하여 SIMPLE_CASE_EXPRESSION보다 훨씬 다양한 조건을 저장할 수 있는 장점이 있다.
CASE
WHEN CONDITION THEN RETURN_EXPR
ELSE 표현절
END
SELECT ENAME, CASE WHEN SAL >=3000 THEN 'HIGH' WHEN SAL >=1000 THEN 'MID' ELSE 'LOW' END AS SALARY_GRADE FROM EMP; ENAME SALARY_GRADE ---------- ---- GOODBOY LOW ALLEN MID WARD MID JONES MID MARTIN MID BLAKE MID CLARK MID SCOTT HIGH KING HIGH TURNER MID ADAMS MID JAMES LOW FORD HIGH MILLER MID |
7. NULL 관련 함수.
가. NVL / ISNULL 함수.
* NULL 특성
- 넓값은 아직 정의되지 않은 값으로 0 또는 공백과 다르다.(0은 숫자이며 공백은 문자이다.)
-테이블을 생성할 때 NOT NULL 조건 또는 PRIMARY KEY로 정의되지 않은 모든 데이터 유형은 널값을 표현할 수 있다.
- 널 값을 포함하는 연산의 경우 결과값도 널 값이다. 모르는 데이터에 숫자를 더하거나 빼도 결과는 마찬가지로 모르는것과 같은 이치.
- 결과값을 NULL이 아닌 다른 값을 얻고자 할 때 NVL/ISNULL함수를 사용한다. NULL 값의 대상이 숫자 유형 데이터인 경우는 주로 0으로 문자유형 데이터인 경우 블랭크보다는 'x'같이 해당 시스템에서 의미없는 문자로 바꾸는 경우가 많다.
연산 | 연산의 결과 |
NULL + 2, 2 + NULL | NULL |
NULL - 2, 2 - NULL | NULL |
NULL * 2, 2 * NULL | NULL |
NULL / 2, 2 / NULL | NULL |
일반형 함수 | 함수설명 |
NVL(표현식1, 표현식2) / ISNULL(표현식1, 표현식2) | 표현식1의 결과값이 NULL이면 표현식2의 값을 출력한다. 단, 표현식과 표현식2의 결과 데이터 값이 같아야 한다. NULL관련 가장 많이 사용되는 함수이므로 중요함. |
NULLIF(표현식1, 표현식2) | 표현식1이 표현식2와 같으면 NULL, 같지않으면 표현식 1을 리턴한다. |
COALESCE(표현식1,표현식2,....) | 임의의 개수 표현식에서 NULL이 아닌 최초의 표현식을 나타낸다. 모든 표현식이 NULL이라면 NULL을 리턴한다. |
- NVL(NULL 판단대상컬럼, 'NULL 일 때 대체값')
EX)
SELECT PLAYER_NAME 선수명, POSITION, NVL(POSITION,'없음')
FROM PLAYER
WHERE TEAM_ID='K08';
- ISNULL(SQL server)
EX)
SELET PLAYER_NAME 선수명, POSITION, ISNULL(POSITION,'없음')
FROM PLAYER;
- CASE를 이용한 표현
EX)
SELECT PLAYER_NAME 선수명, POSITION
CASE WHEN POSITION IS NULL THEN '없음'
ELSE POSITION
END AS 포지션
FROM PLAYER;
* NULL값의 비교연선은 IS NULL!!!!
Q. 급여와 커미션을 포함한 연봉을 계산하라.
A.
SELECT ENAME, SAL, COMM, (SAL*12)+COMM 연봉A, SAL*12+NVL(COMM,0) 연봉B
FROM EMP;
* NVL 함수를 사용하지 않은 연봉A의 결과값은 NULL과 더하기 연산을 하기 때문에 NULL이 나온다.
나. NULL과 공집합.
- 일반적인 NULL / ISNULL 함수 사용.
1) 정상적으로 매니저 정보를 가지고 있는 SCOTT의 매니저를 출력한다.
SELECT MGR FROM EMP WHERE ENAME='SCOTT';
MGR
------------------
7566
2 )매니저에 NULL이 있는 KING의 매니저를 출력.
SELECT MGR FROM EMP WHERE ENAME='KING';
MGR
------------------
3) 매니저가 NULL인 경우 빈칸이 아닌 9999로 표현
SELECT MGR, NVL(MGR,9999) FROM EMP WHERE ENAME='KING';
MGR NVL(MGR,9999)
---------- -------------
9999
- 공집합의 NVL / ISNULLL 함수 사용
* SELECT 1 FROM DUAL WHERE 1=2; (공집합 발생시키는 쿼리.)
1) 공집합을 발생시키기 위해 사원 테이블에 존재하지않는 'HANI' 라는 이름으로 데이터 검색
SELECT MGR FROM EMP WHERE ENAME='HANI';
no rows selected
2) NVL / ISNULL 함수를 이용하여 9999로 변경 시도
SELECT NVL(MGR,9999) FROM EMP WHERE ENAME='HANI';
no rows selected
* 인수의 값이 공집합인 경우에는 NVL, ISNULL을 사용해도 역시 공집합이 출력됨.
* NVL 함수는 NULL값을 다른 대상으로 바꾸는 함수지 공집합을 대상으로 하지 않는다.
3) 적절한 집계함수를 찾아서 NVL 함수 대신 적용.
SELECT MAX(MGR) MGR FROM EMP WHERE ENAME='HANI';
MGR
----------
* NULL값이 출력됨.
* 다른 함수와 달리 집계함수와 Scalar Subquery의 경우 인수의 결과값이 공집합이 null 인 경우에도 null을 출력한다.
4) 집계 함수를 인수로 한 NVL/ISNULL 함수를 이용해서 공집합인 경우에도 9999를 출력하게한다.
SELECT NVL(MAX(MGR),9999) MGR FROM EMP WHERE ENAME='HANI';
MGR
----------
9999
* 공집합의 경우 NVL 을 사용해도 공집합이 출력되므로 그룹함수와 NVL 함수를 같이 사용해서 처리한다.
다. NULLIF
- NULLIF 함수는 EXPR1이 EXPR2와 같으면 NULL 출력 다르면 EXPR1 리턴한다.
- 특정 값을 NULL로 대채하는 경우에 유용함.
1) NULLIF 함수 사용
SELECT ENAME, EMPNO, MGR, NULLIF(MGR,7698) NULLIF
FROM EMP;
2) CASE문 사용
SELECT ENAME, EMPNO, MGR,
CASE WHEN MGR=7698 THEN NULL
ELSE MGR
END NULL_CASE
FROM EMP;
라. 기타 NULL 관련함수 COALESCE
- COALESCE 함수는 인수의 숫자가 한정되어 있지 않으며, 임의의 개수 EXPR에서 NULL이 아닌 최초의 EXPR를 나타낸다. 만약 모든 EXPR이 NULL이라면 NULL을 리턴한다.
SELECT ENAME, COMM, SAL, COALESCE(COMM,SAL) COAL
FROM EMP;
* CASE로 표현
SELECT ENAME, COMM, SAL,
CASE WHEN COMM IS NOT NULL THEN COMM
ELSE (CASE WHEN SAL IS NOT NULL THEN SAL
ELSE NULL
END)
END COAL
FROM EMP;
'자격증을 위한 > SQLD' 카테고리의 다른 글
[SQLD] 2과목 SQL 기본및 활용 - 1장 8절 ORDER BY 절 (0) | 2016.03.09 |
---|---|
[SQLD] 2과목 SQL 기본및 활용 - 1장 7절 GROUP BY, HAVING 절 (0) | 2015.08.29 |
[SQLD] 2과목 SQL 기본및 활용 - 1장 5절 WHERE (0) | 2015.08.29 |
[SQLD] 2과목 SQL 기본및 활용 - 1장 4절 TCL (0) | 2015.08.29 |
[SQLD] 2과목 SQL 기본및 활용 - 1장 3절 DML (0) | 2015.08.26 |
RECENT COMMENT