7)REPLACE(c1,c2[,c3])
- 문자나 문자열을 치환하기 위한 함수
- 주어진 문자열 c1에서 c2를 찾아 c3으로 치환
- c3이 생략되면 c2를 제거
- 주로 문자열 내부의 공백을 제거할 때 사용
사용예) 상품테이블의 상품명 중 '대우'를 찾아 '대덕'으로 치환하시오
Alias는 상품번호, 상품명, 거래처코드, 적정재고량이다.
SELECT PROD_ID AS 상품번호,
PROD_NAME AS 상품명,
REPLACE(PROD_NAME,'대우','대덕') AS 상품명,
PROD_BUYER AS 거래처코드,
PROD_PROPERSTOCK AS 적정재고량
FROM PROD
WHERE PROD_NAME LIKE '%대우%';
SELECT REPLACE('APPLE PERSIMON BANANA','N','y'), --N을 찾아서 y로 대체
REPLACE('APPLE PERSIMON BANANA','N'), --N을 찾아서 제거(공백으로 대체되는 것이 아님)
REPLACE('APPLE PERSIMON BANANA',' ') --공백을 제거
FROM DUAL;
8)INSTR(c1,c2[,m[,n]]) -- INDEX OF STRING(문자열의 특정 위치값을 알고싶을 때 사용)
- 주어진 문자열 c1에서 c2 문자열이 처음 나온 위치값(index)을 반환
- m은 검색 시 시작위치를 1 이외의 다른 값으로 지정할 때 사용
- n은 출현횟수를 지정하여 검색할 때 사용 -- ex. 3번째로 나온 k를 찾으라고 할 때 사용
사용예)
SELECT INSTR('무궁화 꽃이 피었습니다. 무궁화 꽃은...','화') AS COL1,
INSTR('무궁화 꽃이 피었습니다. 무궁화 꽃은...','화',4) AS COL2, --시작위치가 4
INSTR('무궁화 꽃이 피었습니다. 무궁화 꽃은...','화',1,2)
-시작위치 1, 2번째로 나온 '화' 찾기
- 숫자함수
1)수학적함수(ABB(n),SIGN(n),POWER(n,y),SQRT(n),...)
- ABB(n) : n의 절댓값 -- Absolute
- SIGN(n) : n의 부호에 따라 -1(음수), 0(0), 1(양수)의 값을 반환 -- 크기에 관계없이 부호를 숫자로
- POWER(n,y) : n의 y승 반환
- SQRT(n) : n의 평방근 값을 반환(루트값)
사용예)
SELECT ABS(-10.999),ABS(23.5),SIGN(10000),SIGN(0.00001),SIGN(-123)
,SIGN(0),POWER(2,10),ROUND(SQRT(85),2)
FROM DUAL;
2)GREATEST(n1,n2[,n3...]),LEAST(n1,n2[,n3...])
--행에 대한, 알고있는(나열된)값 중 최소값, 최대값 / MIN, MAX는 열에서 알려지지 않은 값 중 최대값, 최소값
- 주어진 데이터 중 최대값(GREATEST)와 최소값(LEAST)을 반환
사용예)회원테이블에서 회원들의 마일리지가 1000미만인 회원들의 마일리지를
1000로 바꾸시오.
Alias는 회원번호,회원명,원래마일리지,변경마일리지
SELECT MEM_ID AS 회원번호,
MEM_NAME AS 회원명,
MEM_MILEAGE AS 원래마일리지,
GREATEST(MEM_MILEAGE,1000) AS 변경마일리지
--마일리지와 1000을 비교해서 1000이 더 크면 1000을 출력
FROM MEMBER;3)ROUND(n1[,i1]), TRUNC(n1[,i])
- ROUND
. 주어진 수 n에서 i가 양수인 경우 소숫점 이하 i+1번째 자리에서 반올림하여 i번째까지 반환
. i가 음수이면 주어진 수 n의 정수부분에서 -i번째자리에서 반올림
. i가 생략되면 0으로 간주 - TRUNC : ROUND와 같은 방법으로 연산하나 반올림하지 않고 절삭하여 반환
사용예)사원테이블(EMP)에서 보너스와 세금을 계산하여 이번달 지급액을 조회하는 Query를 작성하시오
Alias는 사원번호, 사원명, 급여, 영업실적, 보너스, 세금, 지급액
보너스 = 급여 + 영업실적의 25%
세금 = (급여 + 보너스)의 13%
지급액 = 급여 + 보너스 - 세금이며 소수 1자리까지 출력하시오
SELECT EMPLOYEE_ID AS 사원번호,
EMP_NAME AS 사원명,
SALARY AS 급여,
COMMISSION_PCT AS 영업실적,
NVL(ROUND((SALARY+COMMISSION_PCT)*0.25,1),0) AS 보너스,
ROUND(SALARY+NVL(SALARY*COMMISSION_PCT*0.25,0)*0.13),1) AS 세금,
SALARY+NVL(SALARY*COMMISSION_PCT*0.25,1)
-(SALARY+NVL(SALARY*COMMISSION_PCT)*0.25,1)*0.13,1) AS 지급액
FROM EMP;
사용예)장바구니 테이블에서 2005년도 회원들이 구매정보를 이용하여 마일리지를 계산하시오.
상품별 마일리지 = 상품의 마일리지 * 구매수량이다.
1)상품별 마일리지 설정 : 상품 판매가의 0.05%(0.0005)
UPDATE PROD
SET PROD_MILEAGE=PROD_PRICE*0.0005
COMMIT;
2)회원테이블의 보유마일리지를 0으로 변경
UPDATE MEMBER
SET MEM_MILEAGE = 0;
COMMIT;
3)구매내용에 따라 회원별 마일리지 결정 -- 00별: GROUP BY를 사용
SELECT CART_MEMBER AS 회원번호,
SUM(PROD_MILEAGE*CART_QTY) AS 마일리지 합계
FROM CART, PROD
WHERE CART_PROD=PROD_ID
GROUP BY CART_MEMBER
ORDER BY 1;
4)회원테이블에 3)에서 구한 마일리지 반영
UPDATE MEMBER B
SET MEM_MILEAGE=(
SELECT NVL(A.MILE,0)
FROM (SELECT CART_MEMBER AS CID,
SUM(PROD_MILEAGE*CART_QTY) AS MILE --회원별 상품구매 * 마일리지
FROM CART, PROD
WHERE CART_PROD=PROD_ID
AND CART_NO LIKE '2005%'
GROUP BY CART_MEMBER)A --FROM 안에 들어갈 값은 독립된 개체
(FROM 뒤에는 테이블이나 뷰만 올 수 있음)
WHERE B.MEM_ID=A.CID)
WHERE B.MEM_ID IN (SELECT DISTINCT CART_MEMBER -- 구매한 흔적이 있어야 안을 수행함
FROM CART
WHERE CART_NO LIKE '2005%');--2005년도에 MEMBER 테이블에 구매이력이 있는 사람
COMMIT;
4)FLOOR(n),CEIL(c)
- FLOOR : n과 같거나 n작은 수 중 가장 큰 정수(작은 쪽에서 가까운 정수)
- CEIL : n과 같거나 n큰 수 중 가장 큰 정수(큰 쪽에서 가장 가까운 정수)
무조건 자리올림을 하는 결과로 급여, 세금 등 금액과 관련된 계산에 주로 사용
사용예)키보드로 년도(4자리 정수)를 입력받아 그 해가 윤년인지 평년인지 판별하는 코드를 작성하시오
윤년 - (4의 배수이면서 100의 배수가 아니거나) 또는 (400의 배수가)되는 해
ACCEPT P_YEAR PROMPT '년도 입력 :'
DECLARE
V_YEAR NUMBER := TO_NUMBER('&P_YEAR');
V_RES VARCHAR2(100);
BEGIN
IF (MOD(V_YEAR,4)=0 AND MOD(V_YEAR,100)!=0) OR MOD(V_YEAR,400)=0 THEN
--4의배수이고 100의배수가 아님 / 400의배수
V_RES:=V_YEAR||'은 윤년입니다!.';
ELSE
V_RES:=V_YEAR||'은 평년입니다.';
END IF;
DBMS_OUTPUT.PUT_LINE(V_RES);
END;
=> 이 중 많이 사용하는 함수는 SUBSTR, REPLACE, TRIM 정도
