[SQLD] 함수(Function)

함수(Function)

 

 함수 조직도

< 함수 조직도 >

Infotance.

함수는 입력되는 값이 아무리 많아도 출력은 하나만 된다는 M:1 관계라는 중요한 특징을 가지고 있다.

 

 

단일행 함수의 종류

종류	내용
문자형 함수	문자를 입력하면 문자나 숫자 값을 반환한다.
숫자형 함수	숫자를 입력하면 숫자 값을 반환한다.
날짜형 함수	DATE 타입의 값을 연산한다.
변환형 함수	문자, 숫자, 날짜형 값의 데이터 타입을 변환한다.
NULL 관련 함수	NULL을 처리하기 위한 함수

일단 큼지막 하게 대충 알아보고 아래에 상세하게 알아보자.

 

 

단일행 함수의 중요 특징

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

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

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

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

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

 

문자형 함수

  -> 문자 데이터를 매개 변수로 받아들여서 문자나 숫자 값의 결과를 돌려주는 함수.

 

 

 

 

단일행 문자형 함수의 종류

문자형 함수	설명
LOWER( str1 )	str1의 알파벳 문자를 소문자로 바꿔준다.
UPPER( str1 )	str1의 알파벳 문자를 대문자로 바꿔준다.
ASCII( char )	ASCII 코드 번호를 문자나 숫자로 바꾸어 준다.
CONCAT( str1, str2)	str1과 str2를 연결한다. 3개도 연결이 가능하다.
SUBSTRING/SUBSTR( str1, m, n)	str1 중 m번째 위치에서 n개의 문자 길이에 해당하는 문자를 돌려준다. n을 생략할 경우에 마지막 문자까지 돌려준다.
LENGTH/LEN( str1 )	str1의 길이를 숫자 값으로 돌려준다.
LTRIM	지정 문자가 나타나면 해당 문자를 제거한다. ( 왼쪽부터 )
RTRIM	지정 문자가 나타나면 해당 문자를 제거한다. ( 오른쪽부터)
TRIM	문자열에서 머리말, 꼬리말 또는 양쪽에 있는 지정 문자를 제거한다.
CHR/CHAR( ascii number )	Char나 숫자를 ascii 코드 번호로 바꿔준다.

SQL Server에서는 TRIM 종류를 공백밖에 제거하지 못한다. 문자 지정이 불가능하다.

 

 

 

예시를 위한 테이블 소개.

< 예제 테이블 >

 

 

 

 단일행 문자형 함수의 사례

UPPER 함수   -> SELECT UPPER(first_name) FROM my_contacts;

LOWER 함수  -> SELECT LOWER(first_name) FROM my_contacts;

ASCII 함수  -> SELECT ASCII(first_name) FROM my_contacts;

함수  사용 결과 ASCII는 문자열이 있으면 맨 앞 문자만 변환하는 것 같다.  그러니까 ASCII함수를 쓰려면 문자 하나만 사용 가능하다.

CHAR 함수  -> SELECT CHAR(65) FROM my_contacts;

65를 ASCII인 문자 'A' 로 바꿈

CONCAT 함수  -> SELECT CONCAT(last_name,'   ', first_name) FROM my_contacts;

3개의 문자열도 CONCAT을 사용할 수 있다.

SUBSTRING 함수  -> SELECT SUBSTRING(profession,3,8) FROM my_contacts;

LENGTH 함수  -> SELECT LENGTH(profession) FROM my_contacts;

TRIM 함수  -> SELECT TRIM(profession) FROM my_contacts;

저는 SQL server사용자 이므로 문자 지정 불가... Oracle 사용시 지정문자가 삭제됩니다.

 

 

 

 

숫자형 함수

 

단일행 숫자형 함수 종류

숫자형 함수	설명
ABS( num )	NUM의 절대값을 돌려준다.
SIGN( num )	NUM이 양수인지, 음수인지 0인지를 구별한다.
MOD( num1, num2)	num1을 num2로 나누어 나머지 값을 리턴한다.  MOD 함수는 연산자 %로도 대체 가능하다.
CEIL( num )	num보다 크거나 같은 최소 정수를 리턴한다.
FLOOR( num )	num보다 작거나 같은 최대 정수를 리턴한다.
ROUND( num, m )	num을 소수점 m+1자리에서 반올림하여 리턴한다.
TRUNG( num, m )	num을 소수점 m+1 자리에서 잘라버린다.
SIN, COS, TAN 등	숫자의 삼각함수 값을 리턴한다.
EXP(), POWER(), SQRT(), LOG(), LN()	숫자의 지수, 거듭 제곱, 제곱근, 자연로그 값을 리턴한다.

 

예시를 위한 간단한 테이블 소개.

< 간단 예시 테이블 >

간단한 숫자 테이블 만들어봤습니다.(column "sosu" 부분을 위해선 DEC(10,5) 사용했습니다.)

 

단일행 숫자형 함수 사례

ABS 함수  -> SELECT ABS(number) FROM num1;

SIGN 함수  -> SELECT SIGN(number) FROM num1;

양수면 '1' 로 음수면 '-1', 0이면 '0'으로 표시

MOD 함수  -> SELECT MOD(number, sosu) FROM num1;

CEILING 함수  -> SELECT CEILING( sosu) FROM num1;

숫자보다 크거나 같은 최소 정수를 리턴

FLOOR 함수  -> SELECT FLOOR( sosu) FROM num1;

숫자보다 작거나 같은 최대 정수를 리턴

ROUND 함수  -> SELECT round( sosu, 2 ) FROM num1;

TRUNG 함수  SQL server에서는 제공이 안된답니다.  무튼, 숫자를 m+1 자리에서 잘라서 버린다.  나머지 함수는 그냥 되는 것이므로 사진 설명 없습니다!

 

 

 

 

날짜형 함수

- DATE 타입의 값을 연산하는 함수.

날짜형 함수	설명
SYSDATE / GETDATE()	현재 날짜와 시각을 출력한다.
EXTRACT / DATEPART	날짜 데이터에서 년 / 월 / 일 데이터를 출력할 수 있다.  시간 / 분 / 초 가능
TO_NUMBER / YEAR(), MONTH(), DAY()	날짜 데이터에서 년 / 월 / 일 데이터를 출력할 수 있다.

DATE 변수가 데이터베이스에 어떻게 저당되는지 살펴보면, 데이터베이스는 날짜를 저장할 때 내부적으로 세기, 년, 월, 일, 시, 분, 초와 같은 숫자 형식으로 변환하여 저장한다.

날짜는 여러 가지 형식으로 출력이 되고, 날짜 계산에도 사용되기 때문에 그 편리성을 위해서 숫자형으로 저장하는 것이다.

데이터베이스는 날짜를 숫자로 저장하기 때문에 덧셈, 뺄셈 같은 산술 연산자로도 계산이 가능하다.

즉, 날짜에 숫자 상수를 더하거나 뺄 수 있다.

 

 

 

변환형 함수

  - 특정 데이터 타입을 다양한 형식으로 출력하고 싶을 경우에 사용되는 함수

 

데이터 유형 변환의 종류

종류	설명
명시적 데이터 변환	변환형 함수로 데이터 유형을 변환하도록 명시해 주는 경우
암시적 데이터 변환	데이터베이스가 자동으로 데이터 유형을 변환하여 계산하는 경우

   - 암시적 데이터 우형 변환의 경우 성능저하가 발생할 수 있으며, 자동적으로 데이터베이스가 알아서 계산하지 않는 경우가 있어 에러를 발생할 수 있으므로 명시적인 데이터 유형 변환 방법을 사용하는 것이 바람직 하다.

 

단일행 변환형 함수의 종류

변환형 함수 - SQL	설명
CAST(expression AS data_type [length])	Expression을 목표 데이터 유형으로 변환한다.
CONVERT(data_type [length], expression [style])	Expression을 목표 데이터 유형으로 변환한다.

  - 변환형 함수를 사용하여 출력 형식을 지정할 때, 숫자형과 날짜형의 경우 상당히 많은 포맷이 벤더 별로 제공된다. 벤더별 데이터유형과 함께 데이터 출력의 포맷 부분은 벤더의 고유 항목이 많으므로 매뉴얼을 참고할것.!

 

 

CASE 표현 

->  IF-THEN-ELSE 논리와 유사한 방식으로 표현식을 작성해서 SQL의 비교 연산 기능을 보완하는 역할을 한다.

 단일행 CASE 표현의 종류

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 등 DEFAULT VALUE])	Oracle에서만 사용되는 함수로, 표현식의 값이 기준값 1이면 값 1을 출력하고, 기준값 2이면 값 2를 출력한다. 그리고 기준값이 없으면 DEFAULT 값을 출력한다. CASE 표현의 SIMPLE_CASE_EXPRESSION 조건과 동일하다.

  - 첫 번째 SIMPLE_CASE_EXPRESSION은 CASE 다음에 바로 조건에 사용되는 칼럼이나 표현식을 표시하고, 다음 WHEN 절에서 앞에서 정의한 칼럼이나 표현식과 같은지 아닌지 판단하는 문장으로 (=) 조건만 사요안다면 SEARCHED_CASE_EXPRESSION보다 간단하게 사용할 수 있는 장점이 있다.

 

  - 두 번째 SEARCHED_CASE_EXPRESSION은 CASE 다음에는 칼럼이나 표현식을 표시하지 않고, 다음 WHEN절에서 (=) 조건 포함 여러 조건(>,>=,<,<=)을 이용한 조건절을 사용할 수 있기 때문에 SIMPLE_CASE_EXPRESSION보다 훨씬 다양한 조건을 적용할 수 있는 장점이 있다.

 

 

사원 정보에서 급여가 3000 이상이면 상등급으로, 1000 이상이면 중등급으로, 1000 미만이면 하등급으로 분류하시오.

select name,            case when salary>= 3000 then 'height'            case when salary>= 1000 then 'mid'            else 'low'            end as salary_grade from salary_table

- CASE 표현은 함수의 성질을 가지고 있으므로, 다른 함수처럼 중첩해서 사용할 수 있다.

 

사원 정보에서 급여가 2000 이상이며니 보너스를 1000으로, 1000 이상이면 500으로, 1000 미만이면 0으로 계산한다.

select name, salary            case when salary>=2000 then 1000            else(                    case when salary>=1000 then 500                    else 0            end)            end as bonus from salary_table;

 

 

NULL 관련 함수

  * NVL/ISNULL 함수

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

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

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

    - 결과값을 NULL이 아닌 다른 값을 얻고자 할 때 NVL/ISNULL 함수를 사용한다. NULL 값의 대상이 숫자 유형 데이터인 경우는 주로 0(Zero)으로, 문자 유형 데이터인 경우는 블랭크보다는 'X' 같이 해당 시스템에서 의미 없는 문자로 바꾸는 경우가 많다.

 

 

단일행 NULL 관련 함수의 종류

일반형 함수	설명
NVL( 표현식 1, 표현식 2)/ ISNULL( 표현식1, 표현식2)	표현식1의 결과값이 NULL이면 표현식 2의 값을 출력한다. 단, 표현식 1과 표현식 2의 결과 데이터 타입이 같아야 한다.
NULLIF( 표현식 1, 표현식 2)	표현식 1이 표현식 2와 같으면 NULL을 같지 않으면 표현식 1을 리턴 .
COALESCE( 표현식 1, 표현식 2 등 )	임의의 개수 표현식에서 NULL이 아닌 최초의 표현식을 나타낸다. 모든 표현식이 NULL이라면 NULL을 리턴한다.