함수(Conversion)
SELECT TO_CHAR(1234.6, '99.999.00')
FROM DUAL;
SELECT TO_CHAR(-1234.6, 'L9999.00PR')
FROM DUAL;
SELECT TO_CHAR(255, 'XXX') FROM DUAL;TO_DATE(char [, fmt] )
: 날짜 형식의 문자열을 DATE형으로 반환
: fmt는 TO_CHAR에서 사용했던 날짜형식과 동일
SELECT TO_DATE('2009-03-05') + 3
FROM DUAL;
SELECT TO_DATE('200803101234', 'YYYYMMDDHH24MI')
FROM DUAL;AVG(column)
:조회 범위 내 해당 컬럼 들의 평균값
- DISTINCT : 중복된 값은 제외
- ALL : Default로써 모든 값을 포함
- Column명 : NULL값은 제외
- : NULL값도 포함( COUNT함수만 사용)
SELECT PROD_LGU
, ROUND(AVG(PROD_COST), 2) AS "분류별 매입가격 평균"
FROM PROD
GROUP BY PROD_LGU;COUNT(col) / COUNT(*)
:조회 범위 내 해당 컬럼 들의 자료 수/선택된 자료의 수
SELECT COUNT(DISTINCT BUYER_CHARGER) "자료수(DISTINCT)"
, COUNT(BUYER_CHARGER) "자료수"
, COUNT(*) "자료수(*)"
FROM BUYER;MAX(col), MIN(col)
원리 : 여러 건의 데이터를 순서대로 정렬 후 그 중에서 최댓값/최솟값 추출. 따라서 시간이 오래 걸리는 함수 중 한 가지이므로 사용 시 주의가 필요함. 대신 인덱스를 활용하는 것이 좋음.
- 오늘이 2020년도7월11일이라 가정하고 장바구니테이블에 발생될 추가주문번호를 검색
SELECT MAX(CART_NO) AS "최고치주문번호"
, MAX(CART_NO) + 1 AS "추가주문번호"
FROM CART
WHERE SUBSTR(CART_NO,1,8) = '20200711';SUM(column)
:조회 범위 내 해당 컬럼 들의 합계
- 장바구니테이블의 상품분류별 판매수량의 합계 값
SELECT SUBSTR(CART_PROD, 0, 4) AS "상품분류"
, SUM(CART_QTY) AS "판매수량 합계"
FROM CART
GROUP BY SUBSTR(CART_PROD, 0, 4);소 GROUP
- 집계함수를 제외한 select절에 기술된 column명
들은 모두 GROUP BY절에 기술 - GROUP BY절에 기술된 column명들은 select절에
기술되지 않아도 무방 - 하지만 결과를 파악하기 위해서는 select절에 기술
해 주는 것이 타당 - GROUP BY절을 기술하면 GROUP BY절에 기술된
column값으로 1개의 Table이 소GROUP으로 나눠
진다. - Oracle 10g R1 버전까지는 GROUP BY 결과는 늘 Sort 되어서 출력되었음. 그러나 10g R2 버전부터 Hash 기반의 New in-Memory Sort Algorithm 방식으로 변경되면서 더 이상 정렬이 되지 않고 출력 됨.
ㄴ이전 방식 사용 방법 => 초기화 파라미터 파일(pfile이나 spfile)에 "_gby_hash_aggregation_enabled"=FALSE 로 설정
- 회원테이블에서 지역(주소1의 2자리),생일년도별로 마일리지평균, 마일리지합계, 최고마일리지, 최소마일리지, 자료수를 검색
SELECT SUBSTR(MEM_ADD1,1,2) AS "지역"
, SUBSTR(MEM_BIR,1,2) AS "생일연도"
, AVG(MEM_MILEAGE) AS "마일리지평균"
, SUM(MEM_MILEAGE) AS "마일리지합계"
, MAX(MEM_MILEAGE) AS "최고마일리지"
, MIN(MEM_MILEAGE) AS "최소마일리지"
, COUNT(MEM_MILEAGE) AS "자료수"
FROM MEMBER
GROUP BY SUBSTR(MEM_ADD1,1,2)
, SUBSTR(MEM_BIR,1,2);함수(ROLLUP)
: 주어진 데이터들의 소계, 소그룹간의 합계를 계산하는 함수. ROLLUP을 사용하면 GROUP BY로 묶은 각각의 소그룹 합계와 전체 합계를 모두 구할 수 있음
PROD_BUYER 컬럼으로 ROLLUP
SELECT PROD_LGU
, PROD_BUYER
, COUNT(*)
, SUM(PROD_COST)
FROM PROD
GROUP BY PROD_LGU, ROLLUP(PROD_BUYER);
상품분류별, 거래처별 입고수와 입고가격의 합을 구하는 쿼리
SELECT PROD_LGU
, PROD_BUYER
, COUNT(*)
, SUM(PROD_COST)
FROM PROD
GROUP BY PROD_LGU, PROD_BUYER;
ROLLUP 함수를 사용하여 GROUP BY 절에 주어진 조건으로 소계값을 구해줌.
SELECT PROD_LGU
, PROD_BUYER
, COUNT(*)
, SUM(PROD_COST)
FROM PROD
GROUP BY ROLLUP(PROD_LGU, PROD_BUYER);함수(CUBE)
: ROLLUP 함수와 같이 각 소계도 출력하고 전체 총계까지 출력
SELECT PROD_LGU
, PROD_BUYER
, COUNT(*)
, SUM(PROD_COST)
FROM PROD
GROUP BY CUBE(PROD_LGU, PROD_BUYER);함수(NULL관련)
IS NULL, IS NOT NULL
NVL(c, r) :c가 Null이 아니면 c값으로, Null이면 r 반환
NVL2(c, r1, r2) : c가 Null이 아니면 r1값으로, Null이면 r2 반환
NULLIF(c, d) : c와 d를 비교하여 같으면 NULL을 다르면 c값 반환
COALESCE( p [, p …]) : 파라미터중 Null이 아닌 첫 번째 파라미터 반환
EX.부서코드(부서 배치 전)
SELECT COALESCE(NULL, NULL, 'HELLO', NULL, 'WORLD')
--SELECT COALESCE(NULL, NULL, NULL, NULL, NULL)
FROM DUAL;함수(Miscellaneous)
DECODE
:IF문과 같은 기능을 함
(expr {[,search, result]} [, default] )
SELECT PROD_NAME 상품명
, PROD_SALE 판매가
, DECODE(SUBSTR(PROD_LGU, 1, 2)
, 'P1', PROD_SALE + (PROD_SALE*0.1)
, 'P2', PROD_SALE + (PROD_SALE*0.15)
, PROD_SALE) 변경판매가
FROM PROD;CASE WHEN
:연속적인 조건 문 (표준)
CASE WHEN ~ THEN ~ ELSE ~ END
SELECT CASE WHEN '나' = '나‘ THEN '맞다'
ELSE '아니다'
END AS "RESULT"
FROM DUAL;
SELECT PROD_NAME 상품
, PROD_PRICE 판매가
, CASE WHEN(100000 - PROD_PRICE) >= 0 THEN '10만원 미만'
WHEN(200000 - PROD_PRICE) >= 0 THEN '10만원대'
WHEN(300000 - PROD_PRICE) >= 0 THEN '20만원대'
WHEN(400000 - PROD_PRICE) >= 0 THEN '30만원대'
WHEN(500000 - PROD_PRICE) >= 0 THEN '40만원대'
WHEN(600000 - PROD_PRICE) >= 0 THEN '50만원대'
WHEN(700000 - PROD_PRICE) >= 0 THEN '60만원대'
WHEN(800000 - PROD_PRICE) >= 0 THEN '70만원대'
WHEN(900000 - PROD_PRICE) >= 0 THEN '80만원대'
WHEN(1000000 - PROD_PRICE) >= 0 THEN '90만원대'
ELSE '100만원 이상'
END 가격대
FROM PROD
WHERE PROD_PRICE > 100000;
SELECT MEM_NAME 회원명
, MEM_REGNO1 || '-' || MEM_REGNO2 주민등록번호
, CASE WHEN SUBSTR(MEM_REGNO2, 1, 1) = '1' THEN '남자'
WHEN SUBSTR(MEM_REGNO2, 1, 1) = '2' THEN '여자'
WHEN SUBSTR(MEM_REGNO2, 1, 1) = '3' THEN '남자'
WHEN SUBSTR(MEM_REGNO2, 1, 1) = '4' THEN '여자'
ELSE '오류'
END 성별
FROM MEMBER;함수(Regular Expression)
Regular Expression 함수는 10g에서 추가됨
REGEXP_LIKE( str, pattern [, opt] )
: 패턴을 사용하여 str문자열을 검증 (WHERE 절)
:[opt] c: 대소문자 구분, i : 대소문자 구분 안함, m : 다중 행 검색
--회원 중에 성이 '김' 이고, 성 다음에 '성' 또는 '형' 이 있는 회원을 검색
SELECT MEM_ID 회원ID
, MEM_NAME 회원이름
FROM MEMBER
WHERE REGEXP_LIKE(MEM_NAME, '^김(성|형)');
--상품 이름 중에 '삼성' 이라는 말이 있고, 숫자 두 개가 같이 있는 상품의 상품코드, 상품명, 판매가를 검색
SELECT PROD_ID AS "상품ID"
, PROD_NAME AS "상품명"
FROM PROD
WHERE REGEXP_LIKE(PROD_NAME, '^삼성.*\d\d');삼성으로 시작하고(any) 오는 것 중() 숫자가 2회(\d\d) 반복되는 것
삼성.칼라 : 어떤한글자(.)가 0개 이상
삼성.+칼라 : 어떤한글자(.)가 1개 이상
삼성칼라 : 어떤한글자(.)가 0개 이상
삼성+칼라 : 어떤한글자(.)가 1개 이상
삼성.+칼라 : 어떤한글자(.)가 1개 이상
삼성.?칼라 : 삼성칼라 , 삼성.칼라
REGEXP_SUBSTR( str, pattern [, pos [, occur [, opt]]] )
:(대상 문자, 패턴, 시작 위치(최소값1), 매칭순번)
: 패턴에 일치하는 하위 문자열 반환
: pos -> 시작위치, occur -> 몇번째
SELECT MEM_NAME 회원이름
, MEM_MAIL 회원이메일
, REGEXP_SUBSTR(MEM_MAIL, '[^@]+') 이메일아이디 --'[^@]+, 1, 1') 똑같음
, REGEXP_SUBSTR(MEM_MAIL, '[^@]+',1,2) 이메일서버
FROM MEMBER;
SELECT REGEXP_SUBSTR('C-01-02', '[^-]+', 1, 1) FROM DUAL; -- 결과 = C
SELECT REGEXP_SUBSTR('C-01-02','[^-]+',1,2) FROM DUAL; -- 결과 = 01
SELECT REGEXP_SUBSTR('C-01-02','[^-]+',1,3) FROM DUAL; -- 결과 = 03대괄호 [] 안의 ^ 는 NOT의 의미를 나타냄
^ 문자가 대괄호 밖에서 사용되면 문자열의 시작을 의미
- 는 문자패턴이 1개이상 연결될 때를 나타냄, 위 예제에서 01,03등 2개이상 나타내기 위함
+ 시작위치 & 매칭 순번
앞의 대상문자와 패턴에 의해 나누어진 문자들을 몇번째 INDEX에서 시작하여 몇번째의 나누어진 문자를 가져올것인지에 대한 PARAMETER
즉 위 예제에서 1,2는 C // 01 // 02 의 나누어진 문자중 1번째 INDEX부터 시작하는 2번째 문자를 가져오라는 뜻
REGEXP_REPLACE( str, pattern [,replace [, pos [, occur [,opt]]]])
: 패턴에 일치하는 문자열을 다른 문자로 변환, 패턴에 맞지 않는 경우 원본을 리턴
: replace -> 바꿀 문자열
SELECT REGEXP_REPLACE('Java Flex Oracle', '[^ ]+', 'C++') --공백으로 시작하지 않은 곳 바꿈
FROM DUAL;
SELECT REGEXP_REPLACE('Java Flex Oracle', '[^ ]+', 'C++', 1, 2)
FROM DUAL;REGEXP_INSTR( str, pattern [, pos [, occur [,retopt [, opt]]]] )
: 패턴에 일치하는 문자열의 위치 반환,패턴을 찾지 못한 경우 0 반환
: [retopt] 0 -> 시작 위치, 1 -> 일치하는 마지막 위치
SELECT --REGEXP_INSTR('JAVA Flex Oracle', '[ae]', 1, 1, 0, 'i') RESULT
REGEXP_INSTR('JAVA Flex Oracle','[ae]', 3, 2, 1, 'i') RESULT
FROM DUAL;
-- a나 e가 대소문자구분없이(i) 첫번째 오는 위치로 부터 첫번째 오는 위치 => 2
-- a나 e가 대소문자구분없이(i) 세번째부터 두번째 오는 마지막 위치 => 9
SELECT PROD_ID 상품코드
, PROD_NAME 상품명
, PROD_SALE 판매가
, REGEXP_INSTR(PROD_NAME, '\d') 포함된_숫자위치--(없으면0)
FROM PROD
WHERE REGEXP_INSTR(PROD_NAME, '\d') = 0;-- 0626 --
--INNER JOIN
--INNER JOIN 유무 차이 >> 교집합
SELECT * FROM A INNER JOIN B ON A.NO = B.NO;
SELECT * FROM A, B WHERE A.NO = B.NO;
--LEFT OUTER JOIN
SELECT * FROM A LEFT OUTER JOIN B ON(A.NO = B.NO);
--LEFT TABLE => A / 값 같지 않은 멘토스, 정다비는 NULL값 나옴
SELECT * FROM A, B WHERE A.NO = B.NO(+);
--RIGHT OUTER JOIN
SELECT * FROM A RIGHT OUTER JOIN B ON (A.NO = B.NO);
SELECT * FROM A, B WHERE A.NO(+) = B.NO;
--FULL OUTER JOIN : 같지 않은 값도 전부 조회?
SELECT * FROM A FULL OUTER JOIN B ON (A.NO=B.NO);
SELECT * FROM A, B WHERE A.NO = B.NO(+) UNION
SELECT * FROM A, B WHERE A.NO(+) = B.NO;
기록