SQLD 를 위한 SQL기본문법 정리 6 - Function_1

2024년도부터 function은 범위에 없으나,,,,,,,,

그냥 공부한셈 친걸로,,^^,,,,,,,,,,,,,,,,,,(눈물)

 

<함수 유형>

- 생성 주체 

 : 사용자 정의 함수 (User Defined Function)

 : 내장 함수 (Built-in Function) - 벤더 정의

- 적용 범위

 : 단일 행 함수 (Single-Row Function)

   > 문자형 함수, 숫자형 함수, 날짜형 함수

   > 제어 함수, 변환 함수, NULL 관련 함수

 : 다중 행 함수 (Multi-Row Function)

   > 그룹 함수 (Group Function) : 집계 함수(Aggregate Function) 포함

   > 윈도우 함수 (Window Function)

 

 

<단일행 함수 특징>

- 각 행(row)에 대해 개별적으로 작용. 그 결과를 반환.

 : 단일 행 내에 있는 하나 or 복수의 값을 인수로 사용

 : 여러 행에 걸친 값을 사용 x

- 함수 중첩 (함수의 인자로 함수 사용)이 가능 : 함수안에함수안에함수가 가능

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

 

SELECT PLAYER_NAME, LENGTH(PLAYER_NAME) AS 길이
FROM PLAYER;

 

<오라클의 단일행 내장 함수>

- 문자형 함수(문자형 변수처리) : CONCAT, SUBSTR, LENGTH, LTRIM, RTRIM, TRIM, LOWER, UPPER etc..

- 숫자형 함수(숫자형 변수처리) : MOD, ROUND, SIN, ABS, FLOOR etc..

- 변환 함수(문자, 숫자, date형 값의 타입 변환) : TO_CHAR, TO_NUMBER, TO_DATE

- 날짜형 함수(DATE타입의 변수처리) : SYSDATE, TO_NUMBER(TO_CHAR(d, 'DD'|'MM'|'YY'))

- 제어함수(논리값(참,거짓)에 대한 값의 처리) : CASE, DECODE

- NULL 관련 (NULL 처리) : NVL, NULLIF, COALESCE

 

< 문자형 함수 >

 

1) LOWER(문자열) : 모두 소문자로 만들어줌

SELECT LOWER('SQL expert') from dual;

 

2)UPPER(문자열) : 모두 대문자로 만들어줌

SELECT UPPER('SQL expert') FROM dual;

 

3) ASCII(문자) : 아스키코드를 알려준다

SELECT ASCII('A'), ASCII('5') FROM dual;

 

4) CHR(ASCII 코드) : 아스키코드를 문자열로 출력한다

SELECT CHR(65), CHR(53) FROM dual;

 

5) CONCAT(문자열1, 문자열2) : 문자열을 합쳐서 출력한다. (2개이상도됨) 'RDBMS'||'SQL'과 같음.

SELECT CONCAT('RDBMS','SQL') FROM dual;

 

6) SUBSTR(문자열,m) , SUBSTR(문자열,m,n) : m 번째 글자부터 m번째의 n번째 글자까지 추출한다.

SELECT SUBSTR('SQL Expert',5), SUBSTR('SQL Expert', 5,3) FROM dual;

 

7) LENGTH(문자열) : 글자 수를 출력한다

SELECT LENGTH('SQL Expert') FROM dual;

8) LTRIM(문자열) / LTRIM(문자열, 지정문자)

: 문자열의 왼쪽부터 시작해 다른 문자를 만나기 전까지 지정문자 제거 (공백값이 default)

SELECT LTRIM('xxxYYZZxYZx','x') FROM dual;

+  RTRIM(문자열), RTRIM(문자열, 지정문자) : 오른쪽부터 시작해서 다른 문자 만나기 전까지 지정문자 제거

 

9) TRIM(문자열) / TRIM(지정문자 FROM 문자열) 

: 문자열의 양쪽에서 시작해 다른 문자를 만나기 전까지 지정문자 제거 ( 공백값 default)

SELECT TRIM('x' FROM 'xxxYYZZxYZx'), TRIM('   xxxYYzz    ') 
FROM dual;

 

Q. PLAYER 테이블의 PLAYER_NAME의 맨 마지막 글자를 *로 바꿔라

SELECT PLAYER_NAME, CONCAT(SUBSTR(PLAYER_NAME,1, LENGTH(PLAYER_NAME)-1),'*') 비식별화 
FROM PLAYER;

 

 

< 숫자형 함수 >

 

1) ABS(숫자) : absolute value, 절대값. 부호 다 떼고 숫자만 보여준다

SELECT ABS(-15) FROM DUAL;

2) SIGN(숫자) : 크기는 없애고 방향만 있음. 

SELECT SIGN(-20), SIGN(0), SIGN(10) FROM DUAL;

 

3)  MOD(숫자1, 숫자2) : 나머지 값. 숫자1을 숫자2로 나눈 후 나머지 수

SELECT MOD(7,3) FROM DUAL;

 

Q. PLAYER 테이블에서 PLAYER_ID를 활용, 전체 선수를 4개의 그룹(0~3)에 배정하자

SELECT PLAYER_ID, PLAYER_NAME, MOD(PLAYER_ID,4) 그룹
FROM PLAYER;

 

4) CEIL(숫자) : 숫자보다 같거나 큰 최소의 숫자

SELECT CEIL(38.123), CEIL(-38.123) FROM DUAL;

+ FLOOR(숫자) : 숫자보다 같거나 작은 최소의 숫자

 

5) ROUND(숫자), ROUND(숫자,m) : 소수점 몇 개까지 남길 것인가 (반올림)

SELECT ROUND(123.1234), ROUND(123.1234,2) FROM DUAL;

+ TRUNC(숫자), TRUNC(숫자,m) : 소수점 몇 개까지 남길 것인가 ( 뒤에는 버림)

** ROUND 와 TRUNC 비교

SELECT ROUND(123.1284,2), TRUNC(123.1284,2) FROM DUAL;

 

++++ 그 외

SIN(숫자), SQRT(숫자):제곱근, LOG(숫자1,숫자2) :로그함수, LN(숫자):자연로그함수

EXP(숫자):지수함수, POWER(숫자1, 숫자2)

SELECT EXP(1), POWER(3,2) FROM DUAL;

 

<변환형 함수>

** 명시적(Explicit) 데이터 타입 변환 

: 함수를 사용하여 명시적으로 데이터 타입을 변환 ex) MOD(TO_NUMBER(PLAYER_ID),4

** 암시적(Implicit) 데이터 타입 변환

: 시스템이 자동으로 데이터 타입 변환 ex)MOD(PLAYER_ID, 4) > 문자열을 숫자로 변환

 성능 저하 및 에러 발생의 가능성 존재.

 

1) TO_CHAR(숫자|날짜), TO_CHAR(숫자|날짜, FORMAT) : 문자열로 변환

SELECT TO_CHAR(SYSDATE, 'YYYY/MM/DD') AS 타입1 FROM DUAL;

SYSDATE 날짜타입을 yyyy/mm/dd인 문자열로 바꾸고자 함.

2) TO_NUMBER(문자열) : 숫자로변환

SELECT TO_NUMBER('1') + TO_NUMBER('1') 계산1, 1+1 AS 계산 
FROM DUAL;

계산1은 명시적 변환. 계산은 암시적 변환

 

3)TO_DATE(문자열) / TO_DATE(문자열, FORMAT) : 날짜로 변환

SELECT EXTRACT (YEAR FROM TO_DATE ('20170123', 'YYYY/MM/DD'))  AS 연도
FROM DUAL;

 

<날짜형 함수>

1) SYSDATE : 현재 날짜와 시간

2) EXTRACT : 날짜 데이터에서 년/월/일 정보 추출

SELECT EXTRACT (YEAR FROM TO_DATE('2020/01/01'))
FROM DUAL;

 

3) TRUNC(날짜, 'DD') : 날짜 데이터에서 시/분/초를 잘라냄

SELECT TRUNC(SYSDATE, 'MM'),TRUNC(SYSDATE, 'DD'), TRUNC(SYSDATE, 'YYYY')
FROM DUAL;

 

Q. PLAYER_NAME, BIRTH_DATE와 함께 태어난 날부터 오늘까지의 지난 날수를 출력

SELECT PLAYER_NAME, BIRTH_DATE,TRUNC(SYSDATE - BIRTH_DATE) AS DAY_PASSED FROM PLAYER;

Q. 태어난 년도를 문자열로 출력해보자면

SELECT PLAYER_NAME, BIRTH_DATE, TO_CHAR(BIRTH_DATE, 'YYYY') AS DAY_PASSED
FROM PLAYER;

>>>> 이것을 계산하기 쉽게 명시적으로 숫자로 변경하면

SELECT PLAYER_NAME, BIRTH_DATE, TO_NUMBER(TO_CHAR(BIRTH_DATE, 'YYYY')) AS DAY_PASSED
FROM PLAYER;

이렇게 하는데 이러면 번거로우니까 한꺼번에 수행하기 위해

SELECT PLAYER_NAME, BIRTH_DATE,EXTRACT(YEAR FROM BIRTH_DATE) AS DAY_PASSED
FROM PLAYER;

 

EXTRACT 가 있는 것..!