함수(FUNCTION)
-문자열 함수-
[] : 생략가능
CONCAT(문자열, 문자열)
- 매개변수로 두 문자열을 결합하여 하나의 문자열로 반환
결합 연산자 ‘||’ 와 동일 기능 (그래서 ||를 더 많이 씀. 간단하니까)
(오라클 23 은 CONCAT(1,2,3) 이렇게 여러개 가능)
LOWER(문자열), UPPER(문자열), INITCAP(문자열)
- 주어진 문자열에 포함된 모든 글자를 소문자로(LOWER), 대문자로 (UPPER), 각 단어의 첫 글자만 대문자로(INITCAP) 변환
LENGTH(문자열)
- 주어진 문자열에 포함된 글자 수를 반환
한글은 3바이트
LPAD(문자열,크기[,채울문자열]), RPAD(문자열,크기[,채울문자열])
-
LPAD : 주어진 문자열을 정의된 크기의 기억공간에 오른쪽부터 채우고 남는 왼
쪽 공간에 ‘채울 문자열’을 padding 함 -
RPAD : 주어진 문자열을 정의된 크기의 기억공간에 왼쪽부터 채우고 남는 오른
쪽 공간에 ‘채울 문자열’을 padding 함 -
‘채울문자열’이 생략되면 공백이 채워짐
LTRIM(문자열[,제거문자열]), RTRIM(문자열[,제거문자열])
-
주어진 문자열 왼쪽부터(LTRIM) 또는 오른쪽부터(RTRIM) ‘제거할 문자열＇을 찿
아 제거함 -
‘제거할 문자열＇을 생략한 경우 공백을 제거함
TRIM(문자열)
-
주어진 문자열 왼쪽과 오른쪽에 존재하는 공백을 제거함
-
단, 문자열 내부의 공백은 제거하지 못함
SUBSTR(문자열, 시작위치[, 길이])
-
주어진 문자열에서 ‘시작위치’부터 ＇길이’만큼의 문자를 추출하여 반환
-
길이가 생략되면 ‘시작위치＇에서 나머지 모든 글자 반환
-
시작위치와 길이는 1부터 시작함
REPLACE(문자열, 문자열1 [,문자열1])
-
주어진‘문자열’에서 ‘문자열1’을 ‘문자열2’로 대치함
-
‘문자열＇에 ‘문자열1’이 완벽하게 일치해야 함
-
‘문자열2’가 생략되면 공백을‘문자열1’을 삭제-문자열 내부 공백 제거 가능
INSTR(문자열, 찾을 문자 [, 시작 위치][, 찾을 순번])
-
문자열과 찾을 문자는 필수 입력 값이다.
-
시작 위치는 위치를 찾을 문자열의 시작 데이터 위치, 찾을 순번은 찾으려는 문
자가 시작 위치에서 부터 몇 번째인지 지정한다. -
시작 위치가 음수일 경우 문자 열의 오른쪽 끝 부터 왼쪽 방향으로 검색한다.
문자열 함수 사용예
1) LOWER, INITCAP 예시
- 상품 테이블에서 분류코드 'P201'에 속한 자료의 상품코드,상품명,매입,매출단가를 조회
SELECT PROD_ID AS 상품코드,
PROD_NAME AS 상품명,
PROD_COST AS 매입단가,
PROD_PRICE AS 매출단가
FROM PROD
WHERE LOWER(LPROD_GU)='p201'; SELECT EMP_ID,
EMP_NAME,
LOWER (EMP_NAME),
INITCAP(LOWER(EMP_NAME))
FROM C##HR.EMP2) CONCAT 예시
- 회원 테이블에서 ‘충남’에 거주하는 회원정보를 조회하시오.
(단 주소는 기본주소와 상세주소를 결합하여 출력하되 한칸의 공백을 두 자료 사이에 추가하며, CONCAT함수를 사용하시오.) (회원번호,회원명,주소)
SELECT MEM_ID AS 회원번호,
MEM_NAME AS 회원명,
CONCAT(CONCAT(MEM_ADD1,' '),MEM_ADD2) AS 주소
FROM MEMBER
WHERE MEM_ADD1 LIKE '충남%';위 처럼 함수 안에 함수를 쓸 수 있음.
3) SUBSTR 예시
- 장바구니 테이블에서 6월에 판매된 정보를 출력하시오.
(일자, 상품번호, 수량이며 날짜순으로 출력할 것)
SELECT TO_DATE(SUBSTR(CART_NO,1,8)) AS 일자,
PROD_ID AS 상품번호,
CART_QTY AS 수량
FROM CART
WHERE SUBSTR(CART_NO,1,6) = '202006';
ORDER BY 일자;위처럼 ORDER BY 뒤에 별칭이나 숫자(행)을 넣어도 가능
TO_DATE를 써서 날짜형식으로 출력할 수 있음
4)
- 회원테이블의 주민등록 번호를 이용하여 나이를 계산하여 출력하시오
(회원번호, 회원명, 주민등록번호, 나이)
SELECT MEM_ID AS 회원번호,
MEM_NAME AS 회원명,
MEM_REGNO1||'-'||MEM_REGNO2 AS 주민등록번호,
EXTRACT(YEAR FROM SYSDATE) -
(CASE WHEN SUBSTR(MEM_REGNO2,1,1) IN('1','2') THEN
1900+TO_NUMBER(SUBSTR(MEM_REGNO1,1,2))
ELSE
2000+TO_NUMBER(SUBSTR(MEM_REGNO1,1,2))
END)AS 나이
FROM MEMBER;년원일, 시분초 각각 세트임. 년월 만 쓸수 없음.
년도 뽑을때는 EXTRACT(YEAR FROM SYSDATE) -> 년도만 딱 뽑힘 2025년이면 2025.
주민번호 뒷자리가 3,4 면 +2000, 뒷자리가 1,2면 +1900.
CASE WHEN 은 IF랑 같다.
TO_NUMBER 로 숫자로 바꿀 수 있다.
5) TRIM 예시
- 장바구니 테이블에서 장바구니 번호를 만들어 출력하시오
(오늘이 2020년 4월 15일이라 간주함)
SELECT TO_CHAR(SYSDATE,'YYYYMMDD')||
TRIM(TO_CHAR(TO_NUMBER(SUBSTR(MAX(CART_NO),9))+1,'00000'))
AS 장바구니번호1,
TO_CHAR(MAX(CART_NO)+1) AS 장바구니번호2
FROM CART
WHERE CART_NO LIKE '20200415%'TO_MAX : 제일 큰 값
||로 붙였을 경우 공백이 생겨서 TRIM 사용.
장바구니 번호1의 경우 문자로 변환후 출력하려면 굉장히 복잡.
장바구니 번호2의 경우 숫자로 변환하고 하니까 간단.
둘 다 결과값은 같다.
6) LPAD, RPAD 예시
SELECT NVL2(PROD_COLOR,LPAD(PROD_COLOR,7),'색상정보없음') AS 색상정보
FROM PROD;NVL2 : NULL처리 함수, NVL의 확장 버전. NULL값이면 '색상정보없음'을 출력.
LPAD를 써서 가운데쪽에 위치하도록 만듬.
SELECT PROD_COST AS 매입가격,
LPAD(PROD_COST,10,'*') AS 판매가격1,
RPAD(PROD_COST,10,'*') AS 판매가격2
FROM PROD;* : 수표보호문자
SELECT LTRIM('APPLEORANGEAABBCC','A'),
LTRIM('AABBDEF','AB'),
RTRIM('APPLEORANGEAABBCC','ABC'),
LTRIM(' APPLEORANGEAABBCC'),
LTRIM('APPLEOR ')
FROM DUAL;DUAL : 가상의 테이블을 만들어줌 (SYS. 이 생략되있음)
- 실행 결과
7) REPLACE 예시
SELECT PROD_NAME, REPLACE(PROD_NAME,'대우','APPLE'),
REPLACE(PROD_NAME,' ')
FROM PROD;모든 공백을 지울때 많이씀
- 실행 결과
-숫자 함수-
ABS(n), SIGN(n), SQRT(n), POWER(b,n)
-
ABS : n의 절대값
-
SIGN : n의 부호에 따라 음수이면-1, 0이면 0, 양수이면 1을 반환
-
SQRT : n의 제곱근(평방근)
-
POWER : b의 n승 값(b를 n번 거듭 곱셈한 값)
ROUND(n [, m])
-
m이 양수인 경우 : 주어진 자료 n의 소수부분 m+1번째 자리에서 반올림하여 m번째 까지 반환
-
m이 음수인 경우 : 주어진 자료 n의 정수부분 m번째 자리에서 반올림하여 반환
TRUNC(n [, m])
-
m이 양수인 경우 : 주어진 자료 n의 소수부분 m+1번째 자리에서 절삭하여 m번째 까지 반환
-
m이 음수인 경우 : 주어진 자료 n의 정수부분 m번째 자리에서 절삭하여 반환
MOD(n [,m])
- n을 m으로 나눈 나머지 반환 (java의 ‘%’연산자의 기능)
FLOOR(n)
- n과 같거나 작은 수 중에 가장 큰 정수
CEIL(n)
-
n과 같거나 큰 수 중에 가장 작은 정수 소수점 이하의 값이 존재하면 무조건 올림하는 함수
-
급여,세금과 같은 금액 관련 계산 중에 자주 사용된다.
구간 나누는 함수
숫자 함수 사용예)
1) TRUNC 예시
- 회원테이블에서 회원들의 연령대를 조회하시오
SELECT MEM_ID AS 회원번호,
MEM_NAME AS 회원명,
MEM_BIR AS 생년월일,
EXTRACT(YEAR FROM SYSDATE) -
(CASE WHEN SUBSTR(MEM_REGNO2,1,1) IN('1','2') THEN
1900+TO_NUMBER(SUBSTR(MEM_REGNO1,1,2))
ELSE
2000+TO_NUMBER(SUBSTR(MEM_REGNO1,1,2)) -- 이거도 나이
END) AS 나이,
TRUNC((EXTRACT(YEAR FROM SYSDATE) - EXTRACT(YEAR FROM MEM_BIR))
-- 이거도 나이
,-1) || '대' AS 연령대
FROM MEMBER;위처럼 나이를 다양하게 표시할 수 있음
TRUNC를 사용하여 연령대를 구함. 25 라면 -1을 써서 20으로 내림. 그리고 뒤에 '대'를 붙임
- 실행 결과
2) WIDTH_BUCKET 예시 (CASE WHEN 사용)
- 회원테이블의 마일리지를 1000~8000점을 8개의 구간으로 나누고 각 회원이 속한 구간을 조회하여 그 값이 3 이하면'새싹회원',
4~6 '평회원', 7이상이면 'VIP회원' 이라는 메시지를 비고난에 출력하시오.
Alias는 회원번호,회원명,마일리지,구간인덱스,비고
SELECT MEM_ID AS 회원번호,
MEM_NAME AS 회원명,
MEM_MILEAGE AS 마일리지,
WIDTH_BUCKET(MEM_MILEAGE,1000,8000,8) AS 구간인덱스,
CASE WHEN WIDTH_BUCKET(MEM_MILEAGE,1000,8000,8) <= 3 THEN '새싹회원'
WHEN WIDTH_BUCKET(MEM_MILEAGE,1000,8000,8) BETWEEN 4 AND 6 THEN '평회원'
ELSE 'VIP회원'
END AS 비고
FROM MEMBER;- 회원테이블의 마일리지를 1000~8000점을 8개의 구간으로 나누고 각 회원이 속한 구간을 조회하여 많은 마일리지를 가진 회원부터 1,2,3등급을 부여하시오
Alias는 회원번호,회원명,마일리지,등급
SELECT MEM_ID AS 회원번호,
MEM_NAME AS 회원명,
MEM_MILEAGE AS 마일리지,
-- WIDTH_BUCKET(MEM_MILEAGE,8000,1000,8) || '등급' AS 등급 -- 이 방법은 1000을 포함하지 못해서 1000이 다른 등급으로 감.
10 - WIDTH_BUCKET(MEM_MILEAGE,1000,8000,8) AS 등급 -- 이게 훨신 정확(보수를 이용하는 방법)
FROM MEMBER
ORDER BY 등급;표현식
-
웹프로그램의 분기문의 기능을 제공
-
SELECT절에서만 사용가능
-
CASE WHEN ~ THEN과 DECODE가 제공
CASE WHEN
DECODE
