💻 수업정리 (2020)/오라클
[6/15] 오라클 함수사용
주다람
2020. 6. 15. 12:52
[지난주 복습]
- 오라클서버가 쓰는 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;
[그룹함수]
| 그룹함수 | 값을 여러개 전달받아 처리하여 결과를 반환하는 함수 -그룹함수는 다른 검색대상과 같이 사용불가능 : 검색대상의 검색행의 갯수가 다르므로 에러발생 -그룹함수는 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';