단일행 함수
함수의 두가지 유형
단일행 함수
• 데이터 항목을 조작합니다.
• 인수를 사용하고 하나의 값을 반환합니다.
• 반환되는 각 행에서 실행됩니다.
• 행당 하나의 결과를 반환합니다.
• 중첩될 수 있습니다.
함수 중첩
• 단일 행 함수는 어떠한 레벨로도 중첩될 수 있습니다.
• 중첩 함수는 가장 깊은 레벨에서 덜 깊은 레벨로
평가됩니다.
문자의 함수의 유형들
문자 함수
- LOWER : 문자열 전부를 소문자로 변환
- UPPER : 문자열 전부를 대문자로 변환
- INITCAP : 앞 문자만 대문자, 나머지는 소문자 변환
- SUBSTR( '문자열', 처음 INDEX, 자를 문자열 길이 ) : 문자열 자를 때 사용하는 함수
- LENGTH( '문자열' ) : 문자열의 길이를 나타낼 때 사용하는 함수
- LENGTHB( '문자열' ) : 문자열의 길이를 BYTE로 나타낼 때 사용하는 함수
SELECT LENGTHB('홍길동'), LENGTH('홍길동') FROM DUAL;
- INSTR( '문자열', '문자' ) : 2번째 문자열 길이를 나타내는 함수
INSTR([대상 문자열 데이터(필수)], [위치를 찾으려는 부분 문자 (필수)], [위치 찾기를 시작할 대상 문자열 데이터 위치(선택, 기본값은 1)], [시작 위치에서 찾으려는 문자가 몇 번째인지 지정( 선택, 기본값은 1)])
- REPLACE 함수
REPLACE([문자열 데이터 또는 열 이름(필수)],[찾는 문자(필수)],[대체할 문자(선택)] ---------------------------------------------------------------------------- SELECT '010-1111-1111' AS REPLACE_BEFORE, REPLACE('010-1111-1111', '-', ' ') AS REPLACE_AFTER_1, REPLACE('010-1111-1111','-') AS REPLACE_AFTER_2 FROM DUAL;
- TRIM : 삭제할 문자가 생략될 경우에 DEFAULT로 공백을 제거.
TRIM([삭제 옵션(선택)][삭제할 문자(선택)]FROM [원본 문자열 데이터(필수)])
- LTRIM : 왼쪽 삭제할 문자가 있을 경우
- RTRIM : 오른쪽 삭제할 문자가 있을 경우
숫자 함수
날짜 조작 함수
함수의 활용
- ADD_MONTHS를 사용해 1년후 - >
SELECT ADD_MONTHS(SYSDATE,12) FROM DUAL;- 다음 날 날짜 뽑기 - >
SELECT (SYSDATE+1) FROM DUAL;
날짜에 TO_CHAR 함수 사용
- 형식 모델:
• 작은따옴표로 묶어야 합니다.
• 대소문자를 구분합니다.
• 유효한 날짜 형식 요소를 포함할 수 있습니다.
• 쉼표로 날짜 값과 구분됩니다.
TO_CHAR 함수
: 날짜,숫자 등의 값을 문자열로 변환하는 함수
SELECT lnid, gender, bthday ,TO_CHAR(bthday,'MM/DD') ,TO_CHAR(bthday,'DD Month, YYYY') FROM tid WHERE id_typ = '1';
다양한 TO_CHAR의 활용법
- 날짜 포멧 변경 ( YYYY-MM-DD )
SELECT TO_CHAR(SYSDATE, 'YYYYMMDD') --20200723 , TO_CHAR(SYSDATE, 'YYYY/MM/DD') --2020/07/23 , TO_CHAR(SYSDATE, 'YYYY-MM-DD') --2020-07-23 , TO_CHAR(SYSDATE, 'YYYY-MM-DD HH24:MI:SS') --2020-07-23 11:10:52 FROM dual--> YYYY: 년, MM: 월, DD: 일, HH24: 24시간, HH: 12시간, MI: 분, SS:초
- 소수점 변경
SELECT TO_CHAR(123.456, 'FM990.999') --123.456 , TO_CHAR(1234.56, 'FM9990.99') --1234.56 , TO_CHAR(0.12345, 'FM9990.99') --0.12 FROM dual--> FM: 문자열의 공백제거 : 숫자의 최대 길이만큰 9999... 형식을 지정한다.
(9 : 값이 없으면 표시안함, 0: 값이 없으면 "0"으로 처리)
정수은 지정한 형식보다 값의 길이가 길면 오류, 소수 지정한 길이보다 길면 반올림
- 숫자의 천단위 콤마 찍기
SELECT TO_CHAR(123467, 'FM999,999') --123,467 , TO_CHAR(123467890, 'FM999,999,999') --123,467,890 , TO_CHAR(123467, 'FML999,999') --￦123,467 FROM dual
- 지정한 길이 만큼 "0"으로 채우기
SELECT TO_CHAR(123) --123 , TO_CHAR(123, 'FM00000') --00123 FROM dual
- 날짜의 "0" 없애기
SELECT TO_CHAR(SYSDATE, 'MM/DD') --07/03 , TO_CHAR(SYSDATE, 'FMMM/DD') --7/3 FROM dual
- 임의의 구분자로 날짜 형식 만들기
SELECT TO_CHAR(SYSDATE, '""YYYY"년 "MM"월 "DD"일"') --2020년 07월 23일 , TO_CHAR(SYSDATE, '""HH24"시 "MI"분 "SS"초"') --11시 12분 20초 FROM dual
- 시간의 오전, 오후 값 반환
SELECT TO_CHAR(SYSDATE, 'AM') --오전 , TO_CHAR(SYSDATE, 'AM HH:MI:SS') --오전 11:44:31 , TO_CHAR(SYSDATE, 'YYYY-MM-DD AMHH:MI:SS') --2020-07-23 오전11:44:31 FROM dual
- 날짜의 요일 반환
SELECT TO_CHAR(SYSDATE, 'D') --5 : 1(일)~7(토) , TO_CHAR(SYSDATE, 'DY') --목 , TO_CHAR(SYSDATE, 'DAY') --목요일 FROM dual
- 년기준 몇일, 몇주, 분기 반환
SELECT TO_CHAR(SYSDATE, 'DDD') --365일 기준 205일 , TO_CHAR(SYSDATE, 'WW') --1년 기준 30주 , TO_CHAR(SYSDATE, 'Q') --3분기 FROM dual
- 간편한 날짜 변환
SELECT TO_CHAR(SYSDATE, 'MON') --7월 , TO_CHAR(SYSDATE, 'DL') --2020년 7월 23일 목요일 FROM dual
- 다음은 TO_CHAR 함수와 함께 사용하여 숫자 값을 문자로
표시할 수 있는 몇 가지 형식 요소입니다.
NVL 함수
- 기본적으로 데이터에는 값이 없으면 DEFAULT가 NULL 이다 그러므로 NVL 함수를 사용해 NULL 값을 0의 값으로 넣거나 원하는 값으로 임의로 넣어주는 함수
SELECT lnact, lnact_seq, acct_typ, lnid, ln_amt, rate ,TRUNC((ln_amt * rate) / 12) AS 이자1 ,TRUNC((ln_amt * NVL(rate,0)) / 12) AS 이자2 FROM tacct WHERE branch = '10';
CASE 식 함수
- IF-THEN-ELSE 문 작업을 수행하여 조건부 조회를 편리하
게 수행하도록 합니다.SELECT lnid, bthday, score, gender, CASE gender WHEN '1' THEN '남' WHEN '2' THEN '여' ELSE '법인' END AS 성별 FROM tid WHERE id_typ = '1';
GROUP BY 절을 이용한 그룹 생성
AVG 및 SUM 함수 사용
SELECT SUM(ln_amt), AVG(ln_amt),
MAX(ln_amt), MIN(ln_amt)
FROM tacct
WHERE lmt_typ IS NULL ;
MIN 및 MAX 함수
- 숫자, 문자 및 날짜 데이터 유형에 대해 MIN 및 MAX 함수를 사용
SELECT MAX(ln_dt), MIN(ln_dt)
FROM tacct
WHERE lmt_typ IS NULL ;
COUNT 함수 사용
- COUNT(*)는 테이블의 행 수를 반환합니다.
- COUNT(expr)은 expr에 대해 null이 아닌 값을 가진 행의 수를 반환합니다.
DISTINCT 키워드 사용
- COUNT(DISTINCT expr)은 expr의 null이 아닌 구분 값의 수를 반환합니다.
데이터 그룹 생성
GROUP BY 절 구문
- GROUP BY 절을 사용하여 테이블의 행을 더 작은 그룹으로 나눌 수 있습니다.
- 그룹 함수에 속하지 않는 SELECT list의 모든 열은 GROUP BY 절에 있어야 합니다.
SELECT branch, SUM(ln_amt)
FROM tacct
WHERE lmt_typ IS NULL
GROUP BY branch;
- GROUP BY 열은 SELECT list에 없어도 됩니다.
다중 열에서 GROUP BY 정 사용
SELECT branch, prod_cd, SUM(ln_amt)
FROM tacct
WHERE lmt_typ IS NULL
GROUP BY branch, prod_cd ;
- 집계 함수가 아닌 SELECT list의 열이나 표현식은 GROUP BY 절에 있어야 합니다.
--> SELECT 하기위해 나타낼수 있는 컬럼의 수가 맞지 않아 나타나는 오류 ( 그룹함수를 통해 오류를 해결 )
그룹 함수를 사용한 잘못된 query
• WHERE 절은 그룹을 제한하는 데 사용할 수 없습니다.
• 그룹을 제한하려면 HAVING 절을 사용합니다.
• WHERE 절에서 그룹 함수를 사용할 수 없습니다.
HAVING 절을 사용하여 그룹 결과 제한
- HAVING 절을 사용할 경우 Oracle 서버는 다음과 같이 그룹을 제한합니다.
- 행이 그룹화됩니다.
- 그룹 함수가 적용됩니다.
- HAVING 절과 일치하는 그룹이 표시됩니다.
HAVING 절 사용
그룹화와 관련된 여러 함수
ROLLUP, CUBE, GROUPING SETS 함수
ROLLUP URL - https://velog.io/@dlghwns82/ORACLE-ROLLUP
조인 ( JOIN )
다중 테이블에서 데이터 얻어오기
-- > 이 그림을 보면, 두 테이블의 공통적인 컬럼을 통해 JOIN을 시켜 하나의 테이블 형태로 SELECT해서 가져 옴
JOIN의 ON 절
- ON절을 사용해 레코드 검색
• ON 절을 사용하여 조인할 열을 지정합니다.
• ON 절을 사용하면 코드를 이해하기 쉽습니다.
• 밑 코드를 보게 되면 TID 테이블과 TACCT 테이블과 공통된 LNID 컬럼을 ON 절에 같다.라고 표기하면서 JOIN을 합니다.
SELECT a.lnid, a.id_typ, a.bthday,
b.lnact, b.lnact_seq, b.ln_dt, b.ln_amt
FROM tid a
JOIN tacct b
ON a.lnid = b.lnid
WHERE b.lmt_typ IS NULL
ORDER BY b.lnact, b.lnact_seq ;
백엔드/ 개발자