[ORACLE] 숫자함수 문자함수 변환함수 조건함수

CRUD C ->(CREATE) INSERT R ->(READ) SELECT U -> UPDATE D -> DELETE

 

DUAL 테이블
- 오라클에서 제공되는 어느 계정에서나 접근 가능한 테이블 - 산술 연산이나 가상 칼럼 등의 값을 한 번(한 행 ROW)만 출력하고 싶을 때 유용하게 사용할 수 있는 테이블로서 DUMMY라는 칼럼 한 개로 구성되어 있다.	--[DUAL] SELECT DISTINCT 5+10, 6*3 FROM EMP; SELECT 5+10, 6*3 FROM DUAL; DESC DUAL; -- DUAL 확인

 

DISTINCT
	SELECT COUNT (DISTINCT NAME) FROM ANIMAL_INS WHERE NAME IS NOT NULL; ** = NULL X --[DUAL] SELECT DISTINCT 5+10, 6*3 FROM EMP; SELECT 5+10, 6*3 FROM DUAL;

숫자함수
ROUND : 특정 자릿수에서 반올림 - ROUND (값) : 정수값으로 반올림 - ROUND (값, 소수점자리수) : 지정한 소수점자리수까지만 나타나도록 반올림 처리한다. ROUND(AVG(SAL))	--[ROUND] SELECT ROUND(345.678) FROM DUAL;  SELECT ROUND(345.678, 0) FROM DUAL;  --346 SELECT ROUND(345.678, 2) FROM DUAL;  --345.68 SELECT ROUND(345.678, -1) FROM DUAL;  --350 --ROUND(값, 자리수) => ROUND(날짜, YEAR) SELECT HIREDATE, ROUND(HIREDATE, 'MONTH') 일수반올림 ,ROUND(HIREDATE, 'YEAR') 월수반올림 FROM EMP WHERE DEPTNO = 10;
MOD : 나눗셈의 나머지값을 반환 - MOD(피젯수, 젯수) : 피젯수를 젯수로 나눈 값의 나머지 반환	--[MOD] SELECT MOD (34,2), MOD (34, 5) FROM DUAL;  --0 4  --사번이 짝수인 사원들의 EMPNO, ENAME, JOB을 출력 SELECT EMPNO, ENAME, JOB FROM EMP WHERE MOD(EMPNO, 2) =0;
FLOOR : 소숫점자리수를 무조건 버림(정수자리값만, 반올림x) - FLOOR(값)  : 소수점을 지정하는 자리가 아예 없다.	--[FLOOR] SELECT FLOOR (345.678) FROM DUAL;  --345
CEIL : 소수점 자리수를 무조건 버리고, 정수자리값 무조건 올림처리 (정수자리값만 표현되며 일의 자리 값이 무조건 올림처리값으로 반영) - CELIL(값) : 소수점을 지정한 자리가 아예 없다.	--[CEIL] SELECT CEIL (345.118) FROM DUAL; --346
TRUNC : 소숫점기준, 특정 자릿수에서 버림 TRUNC (값) => TRUNC (값, 0) 과 같음 TRUNC (값, 자리수) : 양수(소수점자리 지정), - 음수(정수자리수 지정)	--[TRUNC] 소숫점기준, 특정 자릿수에서 버림 SELECT TRUNC(234.567, 1), TRUNC(234.567), TRUNC(234.567, -1), TRUNC(234.567, -2) FROM DUAL; SELECT HIREDATE, TRUNC(HIREDATE, 'MONTH') FROM EMP WHERE DEPTNO = 10;

 

문자함수
LOWER (소문자로 변환) - LOWER ('문자열값')	--[LOWER] [UPPER] SELECT LOWER(ENAME)소문자로, UPPER ('DataBase') 대문자로, ENAME FROM EMP; SELECT * FROM EMP WHERE ENAME = UPPER('smith'); SELECT * FROM EMP WHERE LOWER(ENAME) = 'smith';
UPPER (대문자로 변환) - UPPER  ('문자열값')
INITCAP : 연결된 단어에 한하여, 첫 글자만 대문자로, 나머지는 소문자로 변환 - INITCAP('문자열값')	--[INITCAP] 첫 글자만 대문자로, 나머지는 소문자로 변환 SELECT INITCAP('DATABASE PROGRAM') FROM DUAL; --Database Program SELECT INITCAP('DATA BASE PROGRAM') FROM DUAL; --Data Base Program
CONCAT (문자를 연결) : 두 개의 단어만 결합가능 - CONCAT ('연결할 첫 번째 문자열 또는 문자열자료형의 컬럼명', '연결할 두 번째 문자열 또는 문자열자료형의 컬럼명')	--[CONCAT] 두 개의 단어만 결합가능,  SELECT 'Oracle' ||'DataBase' FROM DUAL; --OracleDataBase --필요시 중첩활용 SELECT CONCAT (CONCAT('Oracle',' '),'DataBase') FROM DUAL; --Oracle DataBase
LENGTH : 글자의 개수, - LENGTH ('문자열값 또는 문자열자료형의 컬럼명') LENGTHB : 메모리에 차지하는 바이트 수 - LENGTHB ('문자열값 또는 문자열자료형의 컬럼명') 영어, 숫자등은 1자당 1Byte, 한글 : 버전에 따라 1자당 2~3Byte	--[LENGTH] [LENGTHB] SELECT LENGTH ('DATA'), LENGTHB('DATA'), LENGTH ('오라클'), LENGTHB('오라클') FROM DUAL;
SUBSTR : 문자열에서 원하는 문자열만큼 추출할 때 사용하는 함수 - SUBSTR ('문자열값 또는 문자열자료형의 컬럼명', 시작위치, 추출할 개수) **시작위치 : 양수(정방향 왼>오), 음수(역방향(오>왼)	--[SUBSTR] 문자열에서 원하는 문자열만큼 추출할 때 사용하는 함수 SELECT SUBSTR ('DataBase', 2, 3) 양수, SUBSTR ('DataBase', -4, 3) 음수 FROM DUAL; SELECT ENAME, SUBSTR (HIREDATE, 1, 2) FROM EMP WHERE DEPTNO = 20; SELECT ENAME, HIREDATE FROM EMP WHERE SUBSTR (HIREDATE, 1, 2) = '82';
INSTR : 특정 문자의 위치를 반환 - INSTR ('문자열값 또는 문자열자료형의 컬럼명', 찾을글자) - INSTR ('문자열값 또는 문자열자료형의 컬럼명', 찾을글자, 시작위치, 몇 번째 발견)	--[INSTR] 특정 문자의 위치를 반환 SELECT INSTR('DateBase', 'B') FROM DUAL ;  --5 SELECT DEPTNO, ENAME, INSTR(ENAME, 'E') FROM EMP WHERE DEPTNO = 30; ---1번째 'a'값을 출력하세요. SELECT INSTR('DataBase', 'a', 3, 1)FROM DUAL; --4 SELECT INSTR('DataBase', 'a', -5, 1)FROM DUAL; --4
LPAD, RPAD : 특정 기호로 채우기 - LPAD ('문자열값', 총 칸수, '채울문자값 한 개') - RPAD ('문자열값', 총 칸수, '채울문자값 한 개')	--[LPAD] [RPAD] 특정 기호로 채우기 SELECT LPAD('DataBase', 20, '$'), RPAD('DataBase', 20, '$') FROM DUAL; --해당일자 주문번호 출력시  SELECT '20240115'||LPAD('1', 3, '0') FROM DUAL;
TRIM : 특정문자 잘라내기 - TRIM('제거할 문자' FROM '문자열값 또는 문자열자료형 컬럼명' ) - LTRIM('문자열값 또는 문자열자료형 컬럼명', '제거할 문자') - TRIM('문자열값 또는 문자열자료형 컬럼명', '제거할 문자')	--[TRIM] 특정문자 잘라내기 SELECT  LTRIM('aaaaDataBase Programingaaaa', 'a') LTRIM, RTRIM('aaaaDataBase Programingaaaa', 'a') RTRIM, TRIM('a' FROM 'aaaaDataBaseaaa Programingaaaa') TRIM FROM DUAL;

 

변환함수
TO_CHAR : 해당 값을 문자열로 변환해주는 함수 날짜형식의 데이터를 문자열 형식으로 변환하는 경우 - TO_CHAR : (문자열로 변환할 문자열이 아닌 값, '출력형식') 출력형식 포맷 형식 포맷형식은 반드시 문자열값으로 표현해준다. YYYY 또는 YY : 년 4자리 또는 2자리 MM : 월 2자리 DD : 일 2자리 HH24 또는 HH12(HH) : 24시간제 또는 12시간제 MI : 분 SS : 초 FF자리수 : 밀리세컨초 ex)FF3->밀리세컨초 단위 3자리만 표현 AM : 오전 또는 오후  DAY : 요일 D : 요일을 숫자로 반환해줌 (1->월...)	--[TO_CHAR] 해당 값을 문자열로 변환해주는 함수 SELECT SYSDATE, TO_CHAR(SYSDATE, 'YYYY-MM-DD HH12:MI:SS AM DAY') FROM DUAL; SELECT TO_CHAR(SYSDATE, 'AM', 'NLS_DATE_LANGUAGE=AMERICAN') AS AMERICAN,      TO_CHAR(SYSDATE, 'AM', 'NLS_DATE_LANGUAGE=KOREAN') AS KOREAN FROM DUAL; SELECT * FROM EMP WHERE SUBSTR(TO_CHAR(HIREDATE, 'YYYY'), 1, 4) ='1980'; -- 사원들의 사번, 입사일을 출력하되 입사일(HIREDATE별칭) 요일까지 함께 출력 SELECT EMPNO, TO_CHAR(HIREDATE,'YYYY/MM/DD DAY') HIREDATE FROM EMP;
TO_CHAR(숫자값, '000,000') : 총 6칸 준비 + 구분기호 갯수 ->채워지지 않은 공간은 무조건 0으로 채운다. TO_CHAR(숫자값, '999,999') : 총 6칸 준비 + 구분기호 갯수 ->채워지지 않은 공간은 한 칸 공백으로 채운다. **만약 숫자값이라는 명시를 해주는 키워드가 붙을 경우에 9 : 값이 있는 곳은 자리수 인식, 값이 없는 곳은 자리수 무시 0 : 값이 있든 없든 자리수 모두 인식하고 없는 자리수를 0으로 채운다. --L국가통화기호 --FM 좌우 공백제거	SELECT TO_CHAR(123456,'000,000,000') AS "000", TO_CHAR(123456,'999,999')"999"FROM DUAL; SELECT ENAME, SAL, TO_CHAR(SAL, 'L999,999'), TO_CHAR(SAL, '$999,999') FROM EMP WHERE DEPTNO = 10; --L국가통화기호 SELECT TO_CHAR(0.1230) AS NUM1     ,  TO_CHAR(0.1230, 'FM999.9999') AS NUM2     ,  TO_CHAR(0.1230, 'FM999.0000') AS NUM3     ,  TO_CHAR(0.1230, 'FM000.000') AS NUM4     ,  TO_CHAR(0.1230, 'FM000.0000') AS NUM5 FROM DUAL; --FM 좌우 공백제거
TO_DATE : 날짜 자료형이 아닌 값(문자열값)을 날짜자료형으로 변환하는 함수 -TO_DATE(날짜자료형 값 또는 날짜자료형을 가진 컬럼명, '포맷형식') **포맷형식은 TO_CHAR와 동일 **날짜형식의 자료형 : DATE(년/월/일 ->시분초정보까지) TIMESTAMP(년/월/일 시:분:초.밀리세컨초까지)	SELECT TO_DATE('2011-01-01', 'YYYY/MM/DD'),TO_DATE('20110101', 'YYYY-MM-DD') FROM DUAL; SELECT TO_DATE(SYSDATE, 'YYYY/MM/DD') FROM DUAL;  --SYSTIMESTAMP는 이미 날짜자료형으로 사용X SELECT TO_DATE(SYSDATE, 'YYYY-MM-DD HH24:MI:SS'), SYSTIMESTAMP FROM DUAL;  --.FF3사용불가 SYSDATE(밀리세컨X) SELECT TO_CHAR(SYSDATE, 'YYYY-MM-DD HH24:MI:SS'), SYSTIMESTAMP FROM DUAL; SELECT TO_DATE(SYSDATE, 'YYYY-MM-DD HH24:MI.SS')       , TO_CHAR(SYSTIMESTAMP, 'YYYY-MM-DD HH24:MI:SS.FF3') FROM DUAL;
NEXT_DAY	SELECT NEXT_DAY(SYSDATE, '토요일'),NEXT_DAY(SYSDATE, '월요일') FROM DUAL; SELECT NEXT_DAY(SYSDATE, '토'), NEXT_DAY(SYSDATE, 7) FROM DUAL;  --24/01/20 24/01/20 SELECT NEXT_DAY(SYSDATE-8, '토') FROM DUAL;  --24/01/13 SELECT LAST_DAY(SYSDATE) FROM DUAL;  --24/01/31 --연차 SELECT FLOOR(MONTHS_BETWEEN (SYSDATE, TO_DATE('2011-01-01', 'YYYY/MM/DD'))/12)||'년차' AS "연차" FROM DUAL;
TO_NUMBER : 숫자가 아닌 값을 숫자형으로 변환하는 함수 - TO_NUMBER('숫자가 아닌 값 또는 컬럼명', '포맷형식')	SELECT TO_NUMBER('10,000', '999,999'), TO_NUMBER ('20,000', '00,000'),TO_NUMBER('10,000', '999,999')+ TO_NUMBER ('20,000', '999,999') AS SUM FROM DUAL;
NVL : NULL값을 다른 값으로 대체할 때 사용하는 함수 -NVL(컬럼명이나 값, Null대체값)	SELECT ROUND(AVG(NVL(COMM, 0)),3) AS 열값평균, AVG(COMM) AS 널값제외평균 FROM EMP; SELECT ENAME, NVL(SAL,0), NVL(COMM,0), SAL*12+COMM FROM EMP;
NVL2 : NULL값을 다른 값으로 대체할 때 사용하는 함수 중 하나.  -NVL2(컬럼명이나 값, Null이 아닐때 돌려줄값, Null일때 돌려줄값)	SELECT NVL(TO_CHAR(MGR), 'CEO') FROM EMP;  SELECT ENAME, COMM, MGR, SAL, NVL2(COMM, MGR, SAL) FROM EMP; --커미션이 NULL이면 100을 돌려주고, 커미션이 NULL이 아니면 0을 돌려줄 것 SELECT COMM, NVL2(COMM, 0,100) FROM EMP; --커미션을 받지 못한 사람의 인원수를 세어오세요. SELECT COUNT(*) FROM EMP WHERE NVL(COMM,0) = 0;
POWER(N,M) : N의 M제곱승, ex) POWER(5, 3) => 5*5*5 SQRT(N) : N의 루트(제곱근)값 ex) SQRT(9) => 3	SELECT POWER(5,2), ROUND(SQRT(2),4), SQRT(9) FROM DUAL;
REPLACE : 문자열 대체함수 - REPLACE('문자열값 또는 문자열컬럼명', '구분자')  : 대체할 문자는 기본값으로 빈 문자열로 대체됨. - REPLACE('문자열값 또는 문자열컬럼명', '구분자','대체문자')  : 해당 구분자의 문자열이 대체할 문자로 변경됨.	SELECT REPLACE (TO_CHAR(PHONE,'000,0000,0000'), ',','-') PHONE FROM ONECLASS WHERE NM='남태욱';

조건함수
DECODE - JAVA의 switch~case문과 유사 SELECT DECODE( 기준컬럼명, 케이스값1, 실행값1, 케이스값2, 실행값2, ... 케이스값N, 실행값N, 마지막 실행값 -- 마지막 값 부분은 케이스값이 필요없다. -- switch~case의 default의 기능과 동일 ) AS 별칭 FROM 테이블명;	SELECT ENAME, DEPTNO, DECODE (DEPTNO,         10, 'ACCOUNTING',         20, 'RESEARCH',         'ETC' --30 ) AS DNAME FROM EMP; SELECT ENAME, JOB, SAL, DECODE ( JOB,      'ANALYST', SAL * 1.05,     'SALESMAN', SAL * 1.1,     'MANAGER', SAL *1.15,     'CLERK', SAL *1.2,     NVL(SAL,0)     ) AS 급여인상 FROM EMP;
자바로 변경시 switch(기준컬럼명) { case 케이스값1 : 실행값; break; case 케이스값2 : 실행값; break; ... default : 마지막값; }
CASE WHEN함수 --Java if~else와 유사 SELECT CASE [기준이 되는 칼럼] WHEN 조건1 THEN 실행값 WHEN 조건2 THEN 실행값 ... ELSE 실행값 ENDS AS 칼럼별칭 FROM 테이블명; --SELECT절에 오는 모든 함수는 단일값만을 반환하는 함수여야만 가능하다.	SELECT ENAME, DEPTNO, CASE WHEN DEPTNO = 10 THEN 'ACCOUNTING'     WHEN DEPTNO = 20 THEN 'RESEARCH'     ELSE 'OPERATIONS' END AS DNAME FROM EMP; SELECT ENAME, SAL, CASE WHEN SAL>=5000 THEN'KING'      WHEN SAL>=3000 THEN'HIGH'      WHEN SAL>=1500 THEN'MIDDLE'      ELSE 'LOW' END 비고 FROM EMP;

그룹함수
- 테이블 내의 행들에 대해서 연산한 후 단 하나의 결과 값으로 되돌리는 함수 - WHERE절에는 사용불가 - SELECT절, HAVING절에만 사용가능 - 반드시 GROUP BY를 해야만 컬럼과 함께 사용가능 - GROUP BY가 없는 경우는 그룹함수만 사용가능
합계 : SUM(컬럼명),
평균 : AVG(컬럼명),
최대값 : MAX(컬럼명),	가장 최근 시간
최소값 : MIN(컬럼명),
행(줄) 수 : COUNT(컬럼명 또는 *) 등이 있습니다. - 그룹 함수는 NULL값을 제외한다. (COUNT(*) 예외)
NVL : NULL값을 다른 값으로 대체할 때 사용하는 함수 -NVL(컬럼명이나 값, NULL대체값)	NVL(COMM, 0)
' ' 날짜, 문자에 대한 자료형은 문자형, 문자열 상관없이 사용 " " 공백포함시
DESC 테이블명; -> 테이블의 구조보기

 

날짜함수
슈도컬럼(PSEUDO) : 처음에는 값이 정해져있지 않으나 실행이 될 때, 값이 정해지는 컬럼(의사가 존재하는 컬럼)
SYSDATE - 현재 날짜,시간 정보를 가지고 있는 예약어(연산자 컬럼) - 현재 시각의 년/월/일 시:분:초 에 대한 정보를 가지고 있으나 출력시에는 년/월/일까지만 표현이 되는 컬럼 --문자자료형과 DATE자료형 모두 가진다.	SELECT SYSDATE FROM DUAL;  --24/01/15 SELECT SYSDATE AS "오늘" , SYSDATE-1 AS "어제", SYSDATE +1 AS "내일" FROM DUAL;  --24/01/15 24/01/14 24/01/16 --회원정보 테이블에서 10번부서에 해당되는 사람들의 입사한 일차를 구하세요. SELECT DEPTNO, EMPNO, ENAME, SYSDATE, HIREDATE, ROUND((SYSDATE-HIREDATE)/365) AS "년차" FROM EMP WHERE DEPTNO = 10;
SYSTIMESTAMP - 현재 시각의 년/월/일 시:분:초.밀리세컨드초 (UTC+시간정보) 까지 가지고 있음 - 출력시 해당 정보 모두 표현 됨 --DATE자료형만 가진다.	SELECT SYSTIMESTAMP FROM DUAL;

 

 

 

자료형 - 데이터타입, 컬럼(속성)에 들어갈 데이터 유형을 결정한다.

문자형 CHAR( 크기[BYTE] | CHAR ) (고정형 문자 자료형) : 1~2000BYTE ex)CHAR(5)-- 한글 1글자 CHAR(5 CHAR) --5글자 VARCHAR2( 크기[BYTE] | CHAR ) (가변형 문자 자료형) : 1~4000BYTE ex) VARCHAR2 (20 CHAR ) - 20자까지 LONG : 가변형 문자 자료형 : 최대 2GB까지 표현 가능 (권장하지 않음. 이전버전과의 호환성을 위해 유지)

숫자형 - 정수, 실수 포함 NUMBER : 부동(떠다니는) 소수점 자리수 숫자형 NUMBER (1자리수,(생략,0소수점자리 자동)) : 정수값의 자리 수를 지정 NUMBER (소수점 제외 총 자리수, 소수점 자리수) : 고정 소수점 지라수 숫자형. 정수값(총 자리수 - 소수점 자리수) : 지정한 소수점 자리수 (초과된 소수점은 반올림으로 처리됨) ex) NUMBER(7, 2) --12345.56

날짜형 DATE : 년/월/일 시:분:초 까지 입력 가능한 날짜형 자료형 TIMESTAMP : 년/월/일 시:분:초.밀리세컨드초 까지 입력 가능한 날짜형 자료형

BIG DATA TYPE BLOB : BINARY LARGE OBJECT ->이진데이터 전용 큰 사이즈를 기술할 수 있는 자료형 (최대 4GB-1BYTE까지 가능) CLOB : CHARACTER LARGE OBJECT ->가변 길이 문자데이터 전용 큰 사이즈를 기술할 수 있는 자료형 (최대 4GB-1BYTE까지 가능) **데이터 입력과 출력이 쉽지 않아 사용을 권장하지 않는 자료형이다.