[Oracle] SQL FUNCTION_01 : 문자열 함수
문자열 함수
숫자 함수
날짜 함수
변환 함수
Null처리 함수
집계 함수
순위 함수
1. 문자열 함수
CONCAT (문자열 결합)
LOWER(), UPPER(), INITCAP() (소문자, 대문자, 첫 글자만 변환)
LPAD(), RPAD() (주어진 문자열의 크기만큼 지정된 문자를 채워넣기)
LTRIM(), RTRIM() (왼쪽, 오른쪽의 공백을 제거)
TRIM() (양 옆에 존재하는 무효의 공백을 제거)
SUBSTR() (문자열을 지정된 만큼 자름)
REPLACE() (지정된 문자열을 치환)
INSTR() (지정된 문자가 처음 나온 INDEX값을 반환)
2. 숫자함수
ABS(), SIGN(), POWER(), SQRT() (수학적 함수)
GREATEST(), LEAST() (주어진 데이터의 최대. 최소값을 반환)
ROUND(), TRUNC() (반올림, 올림)
FLOOR(), CEIL() (내림함수, 올림함수 FLOOR은 바닥이니까 7.6 을 7로 CEIL은 천장이니까 8로)
MOD() (나머지 함수)
WIDTH_BUCKET() (주어진 값을 구간으로 나누고 그 구간의 순번을 반환)
3. 날짜함수
SYSDATE, ADD_MONTHS() (시스템시간 반환, 주어진 날짜에 ADD한 만큼 반환)
NEXT_DAT, LAST_DAY (주어진 날짜 이후 가장 빠른 요일을 반환, 가장 마지막날을 반환)
MONTHS_BETWEEN() (두 날짜자료 사이의 달수를 반환)
EXTRACT() (주어진 날짜에서 원하는 부분만 출력)
4. 변환함수
CAST() (일시적으로 타입을 변경)
TO_CHAR() (문자타입으로 변환)
TO_DATE(), (날짜타입으로 변환)
TO_NUMBER() (숫자타입으로 변환)
5. NULL처리 함수
IS NULL, IN NOT NULL (NULL인지 여부 판단)
NVL() (NULL이면 VAL을 반환하고 아니면 자신의 값을 반환)
NVL2() (NULL이면 VAL2을 아니면 VAL1을 반환)
NULLIF() (둘을 비교하여 같으면 NULL 아니면 첫 번째 값 반환)
6. 집계함수
SUM() (변수를 더함)
AVG() (컬럼의 평균을 구함)
COUNT() (행 수를 반환)
MAX, MIN (각각 최대값, 최소값을 반환)
ROLLUP() (다양한 집계를 반환 보통 : 각 그룹의 총액을 계산시 사용)
CUBE() (주어진 컬럼의 조합 경우의 수 반환 특수한 경우가 아니라면 2^n 개 이상 나온다.)
7. 순위 함수
- RANK() 중복 값은 순위가 오르지 않고 개수만 증가
- DENSE_RANK() 중복 값은 순위가 중복되고 다음 값은 정상적으로 증가한다.
- ROW_NUMBER() 중복 값의 상관없이 정상적/순차적인 순위를 반환한다.
1. 문자열 함수
1. CONCAT (c1, c2)
- 주어진 문자열 c1과 c2를 결합하여 새로운 문자열 반환
- 결합 연산자 '||'로 대치
사용예) 회원테이블에서 주민등록번호의 표현을 'xxxxxx-xxxxxxx'으로 출력하시오
Alias는 회원번호, 회원명, 주민등록번호
SELECT MEM_ID AS 회원번호,
MEM_NAME AS 회원명,
CONCAT(CONCAT(MEM_REGNO1,'-'),MEM_REGNO2) AS 주민등록번호
-- MEM_REGNO1 || '-' || MEM_REGNO2
FROM MEMBER;
사용예) 거래처테이블(BUYER)에서 거래처 정보를 조회하시오, 단, 주소는
기본주소와 상세주소 사이에 공백 2개를 삽입하여 출력하시오
Alias는 거래처번호, 거래처명, 주소, 담당자
SELECT BUYER_ COMTEL AS 거래처번호,
BUYER_NAME AS 거래처명,
CONCAT(CONCAT(BUYER_ADD1,' '),BUTER_ADD2) AS 주소,
BUYER_BANKNAME AS 담당자
FROM BUYER;
2. LOWER(c1), UPPER(C1), INTICAP(C1)
- 주어진 문자열 C1의 정차를 대문자로(UPPER), 소문자로(LOWER),
단어의 첫 글자만 대문자로(INITCAP) 바꾸는 함수
3. LPAD(C1,n[,C2]), RPAD(C1,n[,C2])
- 주어진 문자열 C1을 n자리만큼 확보된 기억공간에서
오른쪽부터 저장하고 남는 왼쪽을(LPAD), 문자열 'C2'를 모두 채움
문자열 'C2'가 생략되면 공백으로 채움
- 주어진 문자열 C1을 n자리만큼 확보된 기억공간에서
왼쪽부터 저장하고 남는 오른쪽을(RPAD), 문자열 'C2'를 모두 채움
문자열 'C2'가 생략되면 공백으로 채움
4. LTIRM(C1 [,C2]), TRIM(C1 [,C2])
- 주어진 문자열 C1에서 우측(LTRIM) 또는 촤측(RTRIM)에서 C2를 찾아 제거한
- C2가 생략되면 공백을 제거함
- 문자열 내부의 공백은 제거하지 못함
5. TRIM(C1)
- C1문자열의 왼쪽과 오른쪽에 존재하는 무효의 공백을 제거함
6. SUBSTR(C1, m[,n])
- 주어진 [문자열 C1에서 m번째 문자부터 n개의 문자를 추출함
- n이 생략되거나 기술한 문자의 갯수보다 큰 값이면 m부터 나머지 모든 문자가 추출
- n 음수이면 오른쪽부터 계산하여 처리함
7. REPLACE(C1, C2 [C3])
- 문자나 문자열을 치환하기 위한 함수
- C1 문자열에서 C2를 찾아 C3으로 치환
- C3이 생략되면 찾은 C2를 제거함(C2가 공백이면 공백을 제거)
사용예) 상품테이블에서 'P301'분류에 속한 상품정보를 조회하시오
Alias는 상품코드, 상품명, 분류코드 매출단가
SELECT PROD_ID AS 상품코드,
PROD_NAME AS 상품명,
PROD_LGU AS 분류코드,
PROD_PRICE AS 매출단가
FROM PROD
WHERE LOWER(PROD_LGU) = 'p301';
사용예) 키보드로 회원번호를 입력 받아 해당 회원정보를 조회
Alias는 회원번호, 회원면, 주소, 마잉리지
--일반 SQL에서 사용할 수 없음 오라클은 사용안함
ACCEPT P_PID PROMPT '회원번호 : '
DECLARE
L_NAME MEMBER.MEM_NAME%TYPE;
L_ADDR VARCHAR2(200);
L_MILEAGE NUMBER:=0;
BEGIN
SELECT MEM_NAME,MEM_ADD1 || ' ' || MEM_ADD2, MEM_MILEAGE
INTO L_NAME, L_ADDR, L_MILEAGE
FROM MEMBER
WHERE MEM_ID=LOWER(RTRIM('&P_PID'));
DBMS_OUTPUT.PUT_LINE ('-----------------------');
DBMS_OUTPUT.PUT_LINE ('회원번호 :' || '&P_PID' );
DBMS_OUTPUT.PUT_LINE ('회원명 : ' || L_NAME);
DBMS_OUTPUT.PUT_LINE ('주소 : ' || L_ADDR);
DBMS_OUTPUT.PUT_LINE ('마일리지 : ' || L_MILEAGE);
DBMS_OUTPUT.PUT_LINE ('-----------------------');
END;
사용예) 상품테이블에서 분류코드 'P102'에 속한 상품 정보를 조회하시오
Alias는 상품코드, 상품명, 매입단가 , 매출단가이며
매입/매출 단가는 10자리(byte)에 오른쪽 정렬 후 왼쪽에 '*' 문자열을 삽입
SELECT PROD_ID AS 상품코드,
PROD_NAME AS 상품명,
LPAD(PROD_COST,10,'*') AS 매입단가 ,
LPAD(LTRIM(TO_CHAR(PROD_PRICE,'9,999,999')),15,'*') AS 매출단가
FROM PROD
WHERE UPPER(PROD_LGU)='P102';
사용예) 회원테이블에서 충남에 거주하는 회원정보를 조회하시오
Alias는 회원번호, 회원명, 주민증록번호, 주소이며
두번째 주민등록번호 중 첫 자리를 제외한 나머지 6글자는 모두 '*'로 출력
SELECT MEM_ID AS 회원번호,
MEM_NAME AS 회원명,
MEM_REGNO1 || '-' || RPAD(SUBSTR(MEM_REGNO2,1,1),7,'*') AS 주민등록번호,
-- SUBSTR(MEM_REGNO2,1,1) || '******'
MEM_ADD1 || ' ' || MEM_ADD2 AS 주소
FROM MEMBER
WHERE MEM_ADD1 LIKE '충남%';
사용예) 상품테이블에서 상품명 중 '대우'를 찾아 'DW'로 치환하고(변경상품명1),
또 상품명에 포함된 모든 공백(변경상품명1)을 삭제하여 출력하시오
Alias는 상품코드, 상품명, 변경상품명1, 변경상품명2,
SELECT PROD_ID AS 상품코드,
PROD_NAME AS 상품명,
REPLACE(PROD_NAME,'대우','DW') AS 변경상품명1,
REPLACE(PROD_NAME,' ') AS 변경상품명2
FROM PROD