Oracle 기본 함수 모음 맥가이버

단일행 함수

-- LOWER : 소문자로 변환
-- UPPER : 대문자로 변환
-- INITCAP : 첫글자만 대문자
-- CONCAT : 두 문자열을 연결
-- SUBSTR : 문자열 중 특정 문자 또는 문자열의 일부분을 선택
-- LENGTH : 문자열의 길이를 구함
-- INSTR : 명명된 문자의 위치를 구함
-- LPAD : 왼쪽 문자 자리 채움
-- RPAD : 오른쪽 문자 자리 채움
-- LTRIM : 왼쪽 문자를 지움
-- RTRIM : 오른쪽 문자를 지움
-- TRANSLATE : 특정 문자열을 대체
-- REPLACE : 특정 문자열을 대신

-- LOWER
SELECT LOWER(ename) FROM emp;

-- UPPER
SELECT UPPER(ename) FROM emp;

-- INITCAP
SELECT INITCAP(ename) FROM emp;

-- CONCAT
SELECT CONCAT(empno, ename) FROM emp;

-- SUBSTR : 대상 문자열 지정 인덱스에서 시작하여 지정 개수 만큼 출력
SELECT *
FROM EMP
WHERE substr(ename, 1, 1) > 'K' AND substr(ename, 1, 1) < 'S';

-- LENGTH
SELECT LENGTH(ename) FROM emp;

-- INSTR : 'L'문자를 찾아 인덱스 번호 출력
SELECT INSTR(ename, 'L') FROM emp;

-- INSTR : 대상 문자열 1번째부터 시작해서 'L'문자가 두번재나오는 번호
SELECT INSTR(ename, 'L', 1, 2) FROM emp;

-- LPAD : 전체 길이 중 남는 부분 좌측에 주어진 문자로 체움
-- LPAD(대상 문자열, 출력될 문자열 길이 지정, 채울 문자)
SELECT LPAD(ename, 10, '*') FROM emp;

-- RPAD : LPAD와 반대

-- LTRIM : 문자열 지우기
SELECT LTRIM(ename, 'L') FROM EMP;

-- RTRIM : LTRIM과 반대

-- TRANSLATE
SELECT TRANSLATE(sal, '0123456789', '영일이삼사오육칠팔구') FROM EMP;

-- REPLACE
SELECT REPLACE(sal, '0', '$') FROM emp;

-- DECODE : 특정 조건에 대한 연산을 포함한 조건적 조회를 가능하게 합니다.

-- 숫자형 함수 : 거의 round만 사용

-- ROUND : 숫자를 지정한 자리에서 반올림
-- TRUNC : 숫자를 지정한 자리에서 절삭
-- MOD : 나머지를 구합니다
-- POWER : 거듭제곱을 구합니다
-- SQRT : 제곱근을 구합니다
-- SIGN : 양수, 음수, 0인지 구분합니다
-- CHR : ASCII 값에 해당하는 문자를 구합니다

-- ROUND
SELECT round(456.789, 2) FROM dual;

-- 날짜 구하기
SELECT SYSDATE FROM dual;
SELECT to_char(SYSDATE) FROM dual;

-- 달 출력
SELECT TO_CHAR(SYSDATE, 'MM') FROM dual

-- 달 이름 출력
SELECT TO_CHAR(SYSDATE, 'MONTH') FROM dual

-- 현재 월의 몇번째 주인지 출력
SELECT TO_CHAR(SYSDATE, 'W') FROM dual

-- 올해의 몇번째 주인지 출력
SELECT TO_CHAR(SYSDATE, 'WW') FROM dual

-- 요일 출력 : 요일
SELECT TO_CHAR(SYSDATE, 'D') FROM dual;

-- 요일 출력 : 이번달 날수
SELECT TO_CHAR(SYSDATE, 'DD') FROM dual;

-- 요일 출력 : 금년 날 수
SELECT TO_CHAR(SYSDATE, 'DDD') FROM dual;

-- 요일 설정
SELECT TO_CHAR(TO_DATE('20080601'), 'D') FROM dual;

-- 그달의 마지막 날
SELECT LAST_DAY(SYSDATE) FROM dual;

-- 날짜 출력 예제
SELECT ename, hiredate, to_char(hiredate, 'fmDD Month YYY') to_hiredate,
to_char(hiredate, 'YYYY"년"MM"월"DD"일"') to_kor
FROM EMP
WHERE deptno='10'
ORDER BY hiredate DESC ;
SELECT ename, hiredate
FROM EMP
WHERE to_char(hiredate, 'YYYYMMDD') > 19811010

SELECT ename, job, to_char(hiredate, 'month dd, yyyy') t_hire
FROM EMP;
SELECT ename, job, to_char(hiredate, 'MONTH DD, YYYY') t_hire
FROM EMP;

-- 오라클에서 유닉스 timestamp를 변환하기
SELECT to_date('19700101090000' 'YYYMmDDHH24MISS') + NUMTODSINTERVAL(컬럼, 'SECOND') FROM dual;

-- NVL : 널 값을 정의된 형식으로 출력
SELECT NVL(comm, '0010101010') FROM EMP;

-- 그룹함수 : 여러 행 또는 테이블 전체에 대해 함수가 적용되어 하나의 결과를 가져오는 함수

-- AVG : NULL 값을 제외한 N개 행의 평균값
-- COUNT : NULL이 아닌 행의 개수
-- MAX : 최대값
-- MIN : 최소값
-- STDDEV : NULL 값을 제외한 N의 표준편차
-- SUM : NULL 값을 제외한 N의 합계
-- VARIANCE : NULL 값을 제외한 N의 분산

-- AVG NULL 값을 제외하고 계산한다.
SELECT AVG(COMM) FROM EMP;

-- AVG NULL 값을 포함하여 계산한다.
SELECT AVG(NVL(COMM, 0)) FROM EMP;
-- EX)
SELECT * FROM EMP
WHERE (SELECT AVG(NVL(SAL, 0)) FROM EMP) < SAL
SELECT DEPTNO, COUNT(*), AVG(SAL), MIN(SAL), MAX(SAL), SUM(SAL)
FROM EMP
GROUP BY DEPTNO;
SELECT COUNT(EMPNO), MAX(SAL), MIN(SAL), SUM(SAL)
FROM EMP
SELECT COUNT(EMPNO), MAX(SAL), MIN(SAL), SUM(SAL)
FROM EMP
GROUP BY JOB
SELECT MAX(SAL) - MIN(SAL) AS RESULT
FROM EMP
SELECT TO_CHAR(HIREDATE, 'YY'), COUNT(EMPNO), MAX(SAL), MIN(SAL), SUM(SAL)
FROM EMP
WHERE TO_CHAR(HIREDATE, 'YY') <= 83
GROUP BY TO_CHAR(HIREDATE, 'YY');
-- DECODE : 특정 조건에 대한 연산을 포함한 조건적 조회를 가능하게 합니다.
-- DECODE(대상, 조건, 결과[1,2,...], [기본값])

-- P.73 NO.6 ????????
-- 내가 푼거 ㅜㅜ;
SELECT (SELECT COUNT(*) FROM EMP),
COUNT(TO_CHAR(HIREDATE, 'YYYY')), TO_CHAR(HIREDATE, 'YYYY')
FROM EMP
GROUP BY TO_CHAR(HIREDATE, 'YYYY')
-- 쌤이 푼거
SELECT COUNT(*) TOTAL,
COUNT(DECODE(TO_CHAR(HIREDATE, 'YYYY'), '1980', HIREDATE)) "1980",
COUNT(DECODE(TO_CHAR(HIREDATE, 'YYYY'), '1981', HIREDATE)) "1981",
COUNT(DECODE(TO_CHAR(HIREDATE, 'YYYY'), '1982', HIREDATE)) "1982",
COUNT(DECODE(TO_CHAR(HIREDATE, 'YYYY'), '1987', HIREDATE)) "1987"
FROM EMP;
-- P.73 NO.7
-- 내가 푼거 ㅜㅜ;
SELECT JOB, DEPTNO, SUM(SAL)
FROM EMP
GROUP BY JOB, DEPTNO
-- 쌤이 푼거
SELECT JOB,
NVL(SUM(DECODE(DEPTNO, '10', SAL)), 0) AS "DEPTNO 10",
NVL(SUM(DECODE(DEPTNO, '20', SAL)), 0) AS "DEPTNO 20",
NVL(SUM(DECODE(DEPTNO, '30', SAL)), 0) AS "DEPTNO 30",
SUM(SAL) AS "TOTAL"
FROM EMP
GROUP BY JOB
ORDER BY JOB
SELECT * FROM EMP;
-- sql PLUS 만의 명령어
save c:test.sql
@C:test.sql

이 게시물을..

• Oracle 함수 모음.ppt (452.0KB)(31)