[6/15] 오라클 함수사용

[지난주 복습]

• 오라클서버가 쓰는 tns포트는 1521  <-외우자!

 

[함수]

• 함수 : 값을 전달받아 처리하여 결과값을 반환하는 기능을 제공
• 단일함수 : 하나의 값을 전달받아 처리하여 결과값을 반환하는 함수
• ==>문자함수, 숫자함수, 날짜함수, 변환함수, 일반함수
• 그룹함수 : 여러 개의 값을 전달받아 처리하여 결과값을 반환하는 함수

 

[문자함수]

문자함수	문자형 상수를 전달받아 처리하여 결과를 반환하는 함수
upper(문자상수)	전달받은 문자형 상수를 모두 대문자로 변환하는 함수
lower(문자상수)	전달받은 문자형 상수를 모두 소문자로 변환하는 함수
INITCAP(문자상수)	문자형 상수를 전달받아 첫문자만 대문자로 변환하고 나머지는 소문자로 변환하는 함수
CONCAT(문자상수, 문자상수)	두개의 문자형 상수를 전달받아 결합하여 반환하는 함수
SUBSTR(문자상수, 시작위치, 갯수)	문자형 상수에서 시작위치(INDEX:1부터)부터 갯수만큼의 문자들을 분리하여 반환하는 함수
LENGTH(문자상수)	문자형 상수의 문자 갯수를 반환하는 함수
INSTR(문자상수, 검색문자, 시작위치, 검색갯수)	(인덱스스트링) 문자형 상수에서 검색문자를 시작위치부터 검색하여 원하는 검색갯수의 시작 위치를 반환하는 함수 -검색문자가 없는 경우 0을 반환
LPAD(문자상수, 자릿수, 채움문자)	문자형 상수를 자릿수만큼의 길이로 오른쪽 정렬하여 검색하되, 왼쪽 남은 자릿수는 채움문자로 채워 반환하는 함수
RPAD(문자상수, 자릿수, 채움문자)	문자형 상수를 자릿수만큼의 길이로 왼쪽 정렬하여 검색하되, 오른쪽 남은 자릿수는 채움문자로 채워 반환하는 함수
TRIM(LEADING /TRAILING) 제거문자 FROM 문자상수	문자형 상수의 앞 OR 뒤에 존재하는 제거문자를 모두 없애고 반환하는 함수
REPLACE(문자상수, 검색문자, 변환문자)	문자형 상수에서 검색문자를 찾아 변환문자로 변환하여 반환하는 함수

 

//문자형 상수는 대소문자를 구문하므로 smith 말고 SMITH라고 검색해야한다.
SELECT ENAME, UPPER(ENAME), LOWER(ENAME) FROM EMP WHERE ENAME = 'SMITH'; 
--------------------------------------------------------------------------
SELECT EMPNO, ENAME, SAL FROM EMP WHERE UPPER(ENAME) = ('SMITH');

SELECT EMPNO, INITCAP(ENAME), SAL FROM EMP;

SELECT EMPNO, CONCAT(ENAME, JOB) FROM EMP;

SELECT EMPNO, ENAME, JOB, SUBSTR(JOB,5,3) FROM EMP WHERE EMPNO = 7499;

SELECT EMPNO, ENAME, LENGTH(ENAME) FROM EMP WHERE EMPNO = 7499;

SELECT EMPNO, ENAME, JOB, INSTR(JOB,'A',1,2) FROM EMP, WHERE EMPNO = 7499;

SELECT EMPNO, ENAME, SAL, LPAD(SAL, 8,'*') , RPAD(SAL, 8,'*') FROM EMP WHERE EMPNO = 7499;

//SSSALE이면 S에 모두 삭제
SELECT EMPNO, ENAME, JOB, TRIM(LEADING 'S' FROM JOB) , TRIM(TRAILING 'N' FROM JOB) FROM EMP WHERE EMPNO = 7499;

SELECT EMPNO , ENAME, JOB, REPLACE(JOB, 'MAN', 'PERSON') FROM EMP WHERE EMPNO = 7499;

 

---

[숫자함수]

숫자함수	숫자형 상수를 전달받아 처리하여 결과값을 반환하는 함수
ROUND(숫자상수, 소수점자리수)	숫자형 상수를 소숫점 자리수만큼 검색되도록 반올림하여 반환하는 함수
DUAL	테이블없이 SELECT명령을 작성할 경우, 사용되는 가상의 테이블
TRUNC(숫자상수, 소수점자리수)	숫자형 상수를 소숫점 자리수만큼 검색되도록 절삭하여 반환하는 함수
CEIL(숫자상수)	숫자형 상수에서 소숫점 이하 값이 존재할 경우 증가하여 정수값으로 반환하는 함수
FLOOR(숫자상수)	숫자형 상수에서 소수점 이하 값이 존재할 경우 감소시켜 정수값으로 반환하는 함수
MOD(숫자상수, 숫자상수)	숫자형 상수를 나누어 나머지를 반환하는 함수
POWER(숫자상수, 숫자상수)	숫자형 상수의 제곱근을 계산하여 반환하는 함수

 

 SELECT 45.582, ROUND(45.582, 2),ROUND(45.582, -1) FROM DUAL;

 

 SELECT 45.582, TRUNC(45.582, 0),TRUNC(45.582, -1) FROM DUAL;

 

 SELECT 15.3, CEIL(15.3), CEIL(-15.3) FROM DUAL;
 SELECT 15.3, FLOOR(15.3), FLOOR(-15.3) FROM DUAL;

 

SELECT 20/8 , MOD(20,8) FROM DUAL;

 

---

[날짜함수]

날짜함수	날자형 상수를 전달받아 처리하여 변환하는 함수
SYSDATE	시스템의 현재 날짜와 시간정보를 제공하는 키워드 -날짜형은 표면적으로 RR/MM/DD -> 내부적으로는 날짜와 시간정보도 저장된다
ADD_MONTHS(날짜상수, 숫자상수)	날짜형 상수에 숫자형 상수만큼의 개월수를 더한 결과를 반환하는 메소드
NEXT_DAY(날짜상수, 요일)	날짜형 상수에서 받아오는 특정요일의 날짜 상수를 반환하는 함수
TRUNC(날짜 상수, 표현단위)	날짜형 상수를 원하는 단위까지만 표현하여 나머지는 최초값으로 변환하는

참고) 오라클에 접속된 사용자 환경(세션)에따라 날짜와 시간정보를 다르게 표현

 SELECT SYSDATE, ADD_MONTHS(SYSDATE,5) FROM DUAL;
 --이번주 다가오는 토요일은 6/20일이라고 출력됨
  SELECT SYSDATE, NEXT_DAY(SYSDATE,'토') FROM DUAL;

--세션이 바뀌어 한글-> 영어로 변경된다.
ALTER SESSION SET NLS_LANGUAGE = 'AMERICAN';
--세션을 바꿨으니 영어로 검색해야된다.
SELECT SYSDATE, NEXT_DAY(SYSDATE,'SAT') FROM DUAL;

SELECT SYSDATE, TRUNC(SYSDATE,'MONTH'), TRUNC(SYSDATE, 'YEAR')FROM DUAL;

 

• 날짜형 상수는 연산가능하다.
• 날짜상수 + 숫자상수 = 날짜상수 >> 일 증가
• 날짜상수 + 숫자상수/24 = 날짜상수 >> 시간증가
• 날짜 상수 - 숫자상수/23 = 날짜상수 >> 시간감소
• 날짜 상수 - 숫자상수 = 날짜 상수 >> 일 감소
• 날짜 상수 - 날짜상수 = 숫자상수 >> 실수값으로 며칠이 지났는지 알려줌

 SELECT SYSDATE, SYSDATE + 7 FROM DUAL;
 SELECT SYSDATE, SYSDATE + 100/24 FROM DUAL;
 --뒤에 소수점이 찍히는 것은 시간 때문이므로, CEIL함수를 쓰면 깔끔하게 출력할 수 있다.
 SELECT SYSDATE, CEIL(SYSDATE - HIREDATE) "근속일수" FROM EMP WHERE EMPNO = 7499;

 

---

[변환함수]

변환함수	전달받은 상수를 원하는 자료형의 상수로 변환하여 반환하는 함수
TO_NUMBER(문자상수)	문자형 상수를 숫자형 상수로 변환하여 반환하는 상수 -문자형 상수가 숫자가 아닌 경우 에러발생 -비교컬럼의 자료형이 숫자형인 경우 비교값이 문자형 상수면 숫자형 상수로 변환하여 비교해야한다. -비교컬럼의 자료형이 숫자형인 경우 비교값이 문자형 상수라면 자동으로 숫자형 상수로 변환해준다. -문자형 상수를 연산할 경우 자동으로 숫자형 상수로 변환한다.
TO_DATE(문자상수,패턴문자)	문자형 상수를 날짜형 상수로 변환하여 반환하는 함수 -문자형 상수가 날짜 형식이 아닌 경우 에러발생 -비교컬럼의 자료형이 날짜형인 경우 비교값이 문자형 상수면 날짜형 상수로 변환하여 비교(강제 형변환) -RR/MM/DD 대신 YYYY-MM-DD로 표현도 가능 -패턴문자를 이용하여 원하는 형식의 날짜형 상수로 표현가능하다. ( TO_DATE( '03-11-1995' , 'MM-DD-YYYY' ) )
TO_CHAR(숫자상수,패턴문자)	숫자형 상수 또는 날짜형 상수를 원하는 패턴의 문자형상수로 변환하여 반환하는 함수 - YYYY(년) or RR(년) MM(월) DD(일) HH24 , HH12(시) AM, PM , MI(분) SS(초) -날짜형 상수를 원하는 형식의 문자형 상수로 변환하여 비교
숫자패턴문자	9(숫자 또는 공백), 0(숫자), L(화폐기호), $(달러) -패턴문자가 모자란 경우 #으로 표현된다.

 --자동으로 숫자형으로 변환된다
 SELECT EMPNO, ENAME, SAL FROM EMP WHERE EMPNO =TO_NUMBER('7499');
 SELECT EMPNO, ENAME, SAL FROM EMP WHERE EMPNO =('7499');
  
 --자동으로 숫자형으로 변환되어 계산해준다.
  SELECT '30' + '10' FROM DUAL; --결과값 : 40
  
  --위의 경우보다는 아래의 경우가 맞는 형식이다.
  SELECT EMPNO, ENAME, HIREDATE FROM EMP WHERE HIREDATE LIKE '%81%';
  SELECT EMPNO, ENAME, HIREDATE FROM EMP WHERE TO_CHAR(HIREDATE, 'YYYY') = '1981';
  
  SELECT 100000000, TO_CHAR(100000000,'999,999,990') FROM DUAL;

 SELECT EMPNO, ENAME, SAL ,TO_CHAR(SAL, '999,990') SALAR FROM EMP WHERE EMPNO = 7844;

 

 

---

[일반함수]

일반함수	컬럼값이 특정 조건인 경우 처리하여 결과값을 반환하는 함수
NVL(컬럼명, 변경값)	컬럼값이 NULL인 경우 변경값으로 변환하여 반환하는 함수(변경값은 변경값의 자료형이 동일해야한다.) -값이 NULL인경우 연산이 불가능하므로  NULL로 검색이 된다 - 검색오류
NVL2(컬럼명, 변경값1, 변경값2)	컬럼값이 NULL이 아닌 경우 변경값 1로 변환하고, NULL인경우, 변경값2로 변환하여 반환하는 함수
DECODE(컬럼명, 비교값1, 변경값1, 비교값2, 변경값2...기본변경값)	컬럼값을 비교값과 차례대로 비교하여 같은 경우 변경값으로 변환하여 반환하는 함수(SWITCH문과 비슷하다)  -컬럼값이 비교값과 모두 다른경우, 기본변경값으로 변환하여 반환한다. -만약, 기본변경값이 생략된경우, NULL을 반환한다.

--COMM이 NULL인사람도 있으므로 NULL -> 0으로 변경해서 계산.
--다 같은 표현
SELECT EMPNO, ENAME,(SAL + NVL(comm,0)) * 12 ANNUAL FROM EMP;
SELECT EMPNO, ENAME,(SAL + NVL2(COMM, COMM, 0)) * 12 ANNUAL FROM EMP;
SELECT EMPNO, ENAME, NVL2(COMM, SAL+COMM , SAL*12) FROM EMP;

--#1
SELECT EMPNO, ENAME, JOB, SAL, DECODE(JOB, 'ANALYST', SAL * 1.1, 'CLERK', SAL * 1.2, 
			'SALESMAN', SAL * 1.5, 'PRESIDENT', SAL * 1.4, 'MANAGER', SAL * 1.3) SALS FROM EMP;
            
--#2
SELECT EMPNO, ENAME, JOB, SAL, DECODE(JOB, 'ANALYST', SAL) ANALYST, DECODE(JOB, 'CLERK', SAL) CLERK, DECODE(JOB, 'SALESMAN', SAL ) SALESMAN 
    , DECODE(JOB, 'PRESIDENT', SAL)PRESIDENT, DECODE(JOB,'MANAGER', SAL) MANAGER  FROM EMP;

 

#1

#2

 

---

 

[그룹함수]

그룹함수	값을 여러개 전달받아 처리하여 결과를 반환하는 함수 -그룹함수는 다른 검색대상과 같이 사용불가능 : 검색대상의 검색행의 갯수가 다르므로 에러발생 -그룹함수는 NULL을 값으로 인식하지 않고 미처리한다.
COUNT(컬럼명)	컬럼값의 갯수를 반환하는 함수 - 검색행의 갯수 반환 - 컬럼명 대신 *을 사용하여 모든 컬럼으로 저장행에 갯수를 반환할 수 있음
MAX(컬럼명)	컬럼값 중 최대값을 반환하는 함수
MIN(컬럼명)	컬럼값 중 최소값을 반환하는 함수
SUM(컬럼명)	숫자형 컬럼값을 전달받아 합계를 계산하여 반환하는 함수
AVG(컬럼명)	숫자형 컬럼값들을 전달받아 평균을 계산하여 반환하는 함수 -NULL이 아닌 값들의 평균을 구해줌 -NVL함수와 함께 사용하면 정확히 구할 수 있음

 

---

[GROUP BY] 

• 그룹함수가 꼭 존재해야 사용할 수 있다
• 그룹함수를 사용할 셩우 컬럼값으로 그룹을 구분하여 검색하기 위해 사용
• 컬럼값이 같은 경우 같은 그룹으로 처리되어 검색
• GROUP BY구문에서 사용된 그룹지정 표현식은 검색대상으로 사용이 가능함
• 별칭은 GROUP BY구문에서 사용 불가능하다.(처리되는 순서때문이다)

--형식
SELECT [그룹함수] [검색대상...] FROM [테이블명] WHERE [조건식] GROUP BY [컬럼명 | 연산식 | 함수 | ALIAS 불가능]
			... ORDER BY [컬럼명|연산식|함수|별칭|인덱스...] 

SELECT DEPTNO ,COUNT(*) FROM EMP GROUP BY DEPTNO;
SELECT JOB, CEIL(AVG(SAL)) AVG_SAL FROM EMP GROUP BY JOB;

SELECT JOB, CEIL(AVG(SAL)) 평균급여 FROM EMP WHERE JOB <> 'PRESIDENT' GROUP BY JOB;

 

[HAVING]

• GROUP BY 구문으로 그룹화된 검색 결과의 그룹조건을 제공하여 검색

--형식
SELECT [그룹함수] [검색대상...] FROM [테이블명] WHERE [조건식] GROUP BY [컬럼명 | 연산식 | 함수| ALIAS 불가능]
				HAVING [그룹조건식 ] ... ORDER BY [컬럼명|연산식|함수|별칭|인덱스...] 

SELECT DEPTNO, SUM(SAL) 급여합계 FROM EMP GROUP BY DEPTNO HAVING SUM(SAL) >= 9000;

--같은 결과가 나온다
--HAVING을 쓸 수 있었던것은 JOB이 앞에 검색대상으로 놓여있기때문이다.
SELECT JOB, CEIL(AVG(SAL)) 평균급여 FROM EMP WHERE JOB <> 'PRESIDENT' GROUP BY JOB;
SELECT JOB, CEIL(AVG(SAL)) 평균급여 FROM EMP GROUP BY JOB HAVING JOB <> 'PRESIDENT';