DB-함수

함수는 다양한 기준으로 분류할 수 있는데, 벤더에서 제공하는 함수인 내장 함수(Built-in Function)와 사용자가 정의할 수 있는 함수(User Defined Function)로 나눌 수 있다. 

본 절에서는 각 벤더에서 제공하는 데이터베이스를 설치하면 기본적으로 제공되는 SQL 내장 함수에 대해 설명한다

 

내장 함수는 다시 함수의 입력 값이 단일행 값이 입력되는 단일행 함수(Single-Row Fun ction)와 여러 행의 값이 입력되는 다중행 함수(Multi-Row Function)로 나눌 수 있다. 

== 결국엔 함수에는 행이 들어가야된다는거고 행이 들어갈수 있는 어떤곳이라도 함수사용은 가능하다.

다중행 함수는 다시 집계 함수(Aggregate Function), 그룹 함수(Group Fun ction), 윈도우 함수(Window Function)로 나눌 수 있다.

 

단일 함수 종류는 이와 같고 함수에서 중요한 것은 인수를 몇개 넣어도 결국 아웃풋은 단 하나라는 것이다. 각 행들에 대해 개별적으로 작용하여 데이터값들을 조작한다. 또한 함수의 인자로 함수를 사용하는 함수중첩이 가능하다. 

단일행 함수의 특징으로는 

- SELECT, WHERE, ORDER BY 절에 사용 가능하다. 

- 각 행(Row)들에 대해 개별적으로 작용하여 데이터 값들을 조작하고, 각각의 행에 대한 조작 결과를 리턴한다.

- 여러 인자(Argument)를 입력해도 단 하나의 결과만 리턴한다.

- 함수의 인자(Arguments)로 상수, 변수, 표현식이 사용 가능하고, 하나의 인수를 가지 는 경우도 있지만 여러 개의 인수를 가질 수도 있다.

- 특별한 경우가 아니면 함수의 인자(Arguments)로 함수를 사용하는 함수의 중첩이 가능하다.

하나씩 살펴보자.

여기서 변수라는게 애매해서 찾아보니...

 

칼럼값, 바인드변수(SQL 외부에서 정의한 변수) , PL/SQL 변수 등등이라는데 우리가 주요하게 살펴볼 것은 칼럼값이다.

칼럼값은 해당 칼럼에 대해 특정 행이 가지는 값을 말하는건 맞는데 그 모든 값들은 칼럼이름으로 대체할 수 있다.

여기서 first_name의 칼럼값들은 John , Jane , Emily가 맞다. 하지만 SQL 문에서 컬럼 이름을 함수의 인자로 사용하는 경우, 함수는 해당 컬럼 이름을 기반으로 각 행(row)에 대해 그 컬럼에 저장된 값을 인자로 받아 처리한다. 간단하게는 칼럼 이름이 각 행의 칼럼값들을 대표한다.

 

 

문자형 함수

문자형 함수는 문자 데이터를 매개 변수로 받아들여서 문자나 숫자 값의 결과를 돌려주는 함수이다. 몇개는 문자형 결과를 숫자로 리턴하는 것도 있긴하다. 

아래는 이 함수들의 예시이다.

SELECT CONCAT(PLAYER_NAME, ' 축구선수') 선수명 
FROM PLAYER; 
CONCAT 함수는 Oracle의 '||' 합성 연산자와 같은 기능이다. 

SELECT PLAYER_NAME || '축구선수' AS 선수명 FROM PLAYER;

||도 마찬가지로 문자열을 합치는 연산자란다.

 

숫자형 함수

SELECT ENAME, ROUND(SAL/12,1), TRUNC(SAL/12,1) FROM EMP;

인수로 SAL을 주고 있는데 칼럼이름이다. 즉 해당 칼럼의 각각의 행에 대하여 수행하는것이다.

 

날짜형 함수

칼럼이 DATE 타입일때 연산하는 함수들이다.  DBMS는 내부적으로 날짜를 저장할때 세기(Century), 년(Year), 월(Month), 일(Day), 시(Hours), 분(Minutes), 초(Seconds)와 같은 숫자 형식으로 변환하여 저장한다. 따라서 날짜타입에 대한 연산의 경우 숫자형을 기본으로 동작한다. 

이렇게 각각을 분리해서 저장하기에 EXTRACT로 파싱이 가능하다.

SELECT ENAME,HIREDATE
EXTRACT(YEAR FROM HIREDATE) 입사년도,
EXTRACT(MONTH FROM HIREDATE) 입사월, 
EXTRACT(DAY FROM HIREDATE) 입사일 
FROM EMP;

SELECT ENAME, HIREDATE, 
TO_NUMBER(TO_CHAR(HIREDATE,'YYYY')) 입사년도, 
TO_NUMBER(TO_CHAR(HIREDATE,'MM')) 입사월, 
TO _NUMBER(TO_CHAR(HIREDATE,'DD')) 입사일 
FROM EMP;

서로 같은 결과임. EXTRACT함수의 또다른 방법이 TO_NUMBER(TO_CHAR(칼럼, 'YYYY'|'MM'|'DD'))

 

변환형 함수

특정 데이터 타입을 다양한 형식으로 출력하고 싶을 경우에 사용되는 함수(우리가 생각하는 형변환 맞음)

이때 포맷은 벤더별로 기본적으로 제공을 한다. 메뉴얼 봐야된다. 

SELECT TO_CHAR(SYSDATE, 'YYYY/MM/DD') 날짜, 
TO_CHAR(SYSDATE, 'YYYY. MON, DAY') 문자형 
FROM DUAL;

주어진 포맷대로 

2012/07/19와

2012. 7월 , 월요일

로 출력됨.

 

SELECT TO_CHAR(123456789/1200,'$999,999,999.99') 환율반영달러, 
TO_CHAR(123456789,'L999,999,999') 원화 
FROM DUAL;

$102,880.66
₩123,456,789

마찬가지로 주어진 포맷대로 포맷팅하여 출력된다.

 

CASE 표현

특정 칼럼값들을 기준으로 

IF일때는 칼럼값을 어떻게~

THEN 일때는 칼럼값을 어떻게~

ELSE 일때는 칼럼값을 어떻게 하는지 CASE에 따라 다르게 할때 쓴다.

SELECT LOC, 
CASE LOC 
WHEN 'NEW YORK' THEN 'EAST' 
WHEN 'BOSTON' THEN 'EAST' 
WHEN 'CHICAGO' THEN 'CENTER' 
WHEN 'DALLAS' THEN 'CENTER' 
ELSE 'ETC' 
END as AREA 
FROM DEPT;

AREA열로
LOC 값이 NEW YORK이면 EAST로
BOSTON이면 EAST로
CHICAGO면 CENTER로
DALLAS면 CENTER로
나머지는 ETC로 표기하라.

 

NULL관련 함수

- 널 값은 아직 정의되지 않은 값으로 0 또는 공백과 다르다. 0은 숫자이고, 공백은 하나의 문자이다. 

- 테이블을 생성할 때 NOT NULL 또는 PRIMARY KEY로 정의되지 않은 모든 데이터 유형은 널 값을 포함할 수 있다. 

- 널 값을 포함하는 연산의 경우 결과 값도 널 값이다. 모르는 데이터에 숫자를 더하거나 빼도 결 과는 마찬가지로 모르는 데이터인 것과 같다. 

- 결과값을 NULL이 아닌 다른 값을 얻고자 할 때 NVL 함수를 사용한다. NULL 값의 대상이 숫자 유형 데이터인 경우는 주로 0(Zero)으로, 문자 유형 데이터인 경우는 블랭크보다는 ‘x’ 같이 해당 시스템에서 의미 없는 문자로 바꾸는 경우가 많다. (주로 NULL을 숫자형으로 바꿀때는 NVL을 이용하여 숫자 0 혹은 문자열 'x'로 바꾼다는거다. 하지만 이렇게 바꾸는게 시스템에서 바꾸는게 아니라 NVL함수로 내가 인수를 넘김으로써 지정하는것이므로 어떻게 바꾸는지에 관한건 마음대로 한다.)

 

NVL함수(NULL > 특정값)

NVL (NULL 판단대상 , 'NULL일때 대체값')

SELECT NVL(NULL,'NVL-OK') NVL_TEST
FROM DUAL;

NULL 값이면 NVL-OK라는 문자열로 바꾸겠다는거

SELECT NVL('NOT-NULL','NVL-OK') NVL_TEST
FROM DUAL;

현재 NOT-NULL 은 NULL이 아니기에 NVL-OK로 바뀌지 않고 그대로 NOT-NULL로 출력된다

SELECT PLAYER_NAME 선수명, POSITION, NVL(POSITION,'없음') 포지션 
FROM PLAYER 
WHERE TEAM_ID = 'K08'
즉 POSITION 의 속성값 중 NULL이 있으면 그것은 '없음'으로 대체하라는것


SELECT ENAME 사원명, 
SAL 월급, 
COMM 커미션, 
(SAL * 12) + COMM 연봉A, 
(SAL * 12) + NVL(COMM,0) 연봉B 
FROM EMP;

COMM열의 속성값이 NULL이면 숫자형 0으로 바꾼다. 바꾸고 (SAL*12)와 더하고 이것을 연봉B로 출력한다.

 

공집합과 NULL의 차이

공집합은 쿼리 조건에 맞는 데이터가 한 건도 없는 경우를 말한다.

데이터값으로 오는 NULL과는 차이가 있다.

앞에서 살표보았던 NVL도 특정 행의 NULL값을 기준으로 변경하는거지 공집합을 기준으로 하는 것이 아니다.

그렇다면 이러한 공집합은 어떻게 NVL처럼 다른값으로 대체할까?

 집계함수와 Scalar Subquery 같은 경우 인수의 결과 값이 공집합이어도 NULL을 출력한다.

따라서 집계함수와 Scalar Subquery를 한번 거쳐서 NULL로 뽑아내고 NVL로 NULL값을 다른값으로 교체해야한다.

SELECT NVL(MAX(MGR), 9999) MGR FROM EMP WHERE ENAME='JSC';

 MAX함수로 우선 해당 데이터가 없으면 NULL값으로 뽑고 이 NULL값을 NVL로 대체한다.

 

공집합과 NULL이 헷갈리는 이유는 NULL도 표현될때는 NULL로 표기가 아니라 공백으로 표기되고 공집합도 공백으로 표기되기에 주의가 필요하다.

 

NULLIF (특정값 > NULL)

NULL값을 다른 값으로 대체했다면 NULLIF는 특정값을 NULL로 대체한다.

NULLIF (EXPR1 , EXPR2)

EXPR1과 EXPR2가 서로 같으면 NULL을, 같지 않으면 EXPR1을 리턴한다. 

SELECT ENAME,EMPNO,MGR,NULLIF(MGR,7698) NUIF
FROM EMP;

이는 CASE문으로 표현할 수 있다.

SELECT ENAME, EMPNO , MGR ,
CASE WHEN MGT=7698
	THEN NULL
    ELSE MGT
END NUIF
FROM EMP;

MGR행의 값이 7698일 경우 NUIF 칼럼에 NULL로 표기된다(=공백이다).

 

COALESCE 함수

각 행의 값에 대하여 NULL값이 아닌 최초의 EXPR을 가져가 쓴다. 만약 인자값이 모두 NULL이면 NULL을 리턴한다. 이때 COALESCE함수는 인자 갯수에 대한 조건이 없다.

COALESCE (EXPR1, EXPR2 ...);


SELECT ENAME, COMM,SAL, COALESCE(COMM,SAL) CAOL
FROM EMP;

SELECT ENAME, COMM, SAL,
	CASE COMM IS NOT NULL
    THEN COMM
    ELSE (CASE WHEN SAL IS NOT NULL
    		THEN SAL 
            ELSE NULL
            END)
    END COAL
FROM EMP;