오라클 2021-02-08

유튜브 (뉴렉처 오라클22강 시작)

오라클의 함수기능 (뉴렉처 23~28강)

 

문자열 함수	숫자 함수
날짜 함수	변환 함수
NULL 관련 함수	집계함수

 

 

문자열 함수

 

문자열 추출함수 SUBSTR(문자열,시작위치,길이)

SELECT SUBSTR('HELLO',1,3)FROM DUAL

SELECT SUBSTR('컬럼명',3,2)FROM 테이블명;

SELECT*FROM 테이블명 WHERE SUBSTR('컬럼명',1,3)='조건'   //패턴으로 사용하는것이 조금더 효과적 (LIKE '조건%')

 

SUBSTRB : byte 단위로 추출 (문자열 , 바이트)

SELECT SUBSTRB('문자열',3)FROM DUAL

 

*필터링을 어떤식으로 사용해야할지 잘 선택해야함

 

 

 

 

 

문자열 덧셈 함수 CONCAT

SELECT CONCAT('홍','길동')FROM DUAL

||  연산함수와 성능이 동일하며 || 연산함수를 더 많이 사용함

 

 

문자열 트림 함수

LTRIM(왼쪽) RTRIM(오른쪽) TRIM(양쪽)

SELECT TRIM ('      HELLO      ') FROM DUAL

 

문자열 소문자 또는 대문자 변경 함수

LOWER(소문자) UPPER(대문자)

SELECT LOWER('NewLeC') FROM DUAL

*  대/소 문자를 가리지않고 조회할때 많이사용 된다.

ex) SELECT * FROM 테이블명 WHERE UPPER(컬럼명) = 'NEWLEC' 

 

문자열 대치 함수 REPLACE(문자열,찾는문자열,대치할 문자열), TRANSLATE()

REPLACE : 통째로 바뀜

TRANSLATE : 각각의 문자가 다른문자로 대치되어서 바뀌는것

 

문자열 패딩 함수 LPAD('문자열'/컬럼명,길이,빈공백을 매꿀것),RPAD(채워질 문자열이 오른쪽에 나타남)

문자열을 채우는함수 (한글일 경우에는 길이를 *2 해줘야한다)

SELECT LPAD('HELLO',10,'0') FROM DUAL

 

첫글자를 대문자로 바꾸는 함수 INITCAP(문자열)

SELECT INITCAP('the most import') FROM DUAL

모든단어의 첫글자가 전부 대문자로 바뀐다. 

(한글다음에 사용할경우에도 바로 대문자로 바뀐다)

 

문자열 검색 함수 INSTR(문자열/컬럼명 , 검색문자열 , 위치 , 찾을 수)

SELECT INSTR('ALL WE NEED TO IS JUST TO...' , 'TO') FROM DUAL

 

찾는문자열이 두개일시 인자값을 하나주어서 검색하는방법 

SELECT INSTR('ALL WE NEED TO IS JUST TO...' , 'TO',15) FROM DUAL

 

첫번째 문자에서 찾기시작해서 2번째 문자열의 위치를 찾는 방법

SELECT INSTR('ALL WE NEED TO IS JUST TO...' , 'TO',1,2) FROM DUAL

 

 

 

문자열의 길이를 얻는 함수 LENGTH

SELECT LENGTH('WHERE WE ARE') FROM DUAL

 

코드값을 반환한느 함수 ASCII('문자')

SELECT ASCII('A') FROM DUAL

 

코드 값으로 문자를 반환하는 함수 CHR(코드값)

SELECT CHR(65) FROM DUAL

 

 

 

 

숫자 함수

 

절대값을 구하는 함수 ABS(n)

SELECT ABS(35),ABS(-35) FROM DUAL;             //35 , 35 

 

음수/양수를 알려주는 함수 SIGN(n)

SELECT SIGN(35),SIGN(-35),SIGN(0) FROM DUAL             // 1 , -1  , 0

 

숫자의 반올림 값을 알려주는 함수 ROUND(n,i)

SELECT ROUND(34.456789),ROUND(34.56789)FROM DUAL   // 34   ,  35

 

소수점을 기반으로 몇번째 자리를 반올림할것인지

SELECT ROUND(12.3456789,2),ROUND(12.3456789,3)FROM DUAL  // 12.35  , 12.346

 

숫자의 나머지 값을 반환하는 함수 MOD(n1,n2)

SELECT TRUNC(17/5) 몫,  MOD(17,5) 나머지 FROM DUAL;   // 3몫 ,  2나머지

 

숫자의 제곱을 구하는 함수와 제곱근을 구하는 함수 POWER(n1,n2)/SQRT(n)

SELECT POWER(5,2), SQRT(25) FROM DUAL  // 25 , 5

 

 

날짜 함수

 

현재 시간을 얻는 함수 SYSDATE , CURRENT_DATE , SYSTIMESTAMP , CURRENT_TIMESTAMP

SELECT SYSDATE , CURRENT_DATE , SYSTIMESTAMP , CURRENT_TIMESTAMP FROM DUAL

오라클 설정의 시간은 SYS

세션 설정에 따른 시간은 CURRENT

세션 시간설정은 ALTER SESSION SET TIME_ZONE = '타임존값' 으로 수정 해주면 된다 (09:00 한국 , -08:00 LA , ....) 

 

날짜 추출 함수 EXTRACT(YEAR/MONTH/DAY/HOUR/MINUTE/SECOND FROM ...)

SELECT EXTRACT(YEAR FROM SYSDATE)FROM DUAL

SELECT EXTRACT(MONTHFROM SYSDATE)FROM DUAL

SELECT EXTRACT(DAY FROM SYSDATE)FROM DUAL

SELECT EXTRACT(MINUTE FROM SYSDATE)FROM DUAL

SELECT EXTRACT(SECOND FROM SYSDATE)FROM DUAL

 

SELECT EXTRACT(MONTH FROM SYSDATE) || '월' FROM DUAL 

값에 '월'이란 문자열을 붙여 결과 값을 나오게 할 수 있다.

SELECT * FROM 테이블명 WHERE EXTRACT (MONTH FROM REGDATE) IN (2,3,11,12);

2월3월11월12월을 WHERE을 통해 조회하는방법

 

날짜를 누적하는 함수 ADD_MONTH(날짜,정수)

SELECT ADD_MONTH(SYSDATE, 1) FROM DUAL

SELECT ADD_MONTH(SYSDATE, -1)FROM DUAL

 

 

날짜의 차이를 알아내는 함수 MONTHS_BETWEEN(날짜,날짜)

SELECT MONTHS_BETWEEN(SYSDATE,TO_DATE('2013-12-25'))FROM DUAL

 

다음 요일을 알려주는 함수 NEXT_DAY(현재날짜,다음요일)

요일에 해당하는 날짜를 조회해줌

SELECT NEXT_DAY(SYSDATE,'토요일')FROM DUAL;

SELECT NEXT_DAY(SYSDATE,'토')FROM DUAL;

SELECT NEXT_DAY(SYSDATE,7)FROM DUAL;

 

월의 마지막 일자를 알려주는 함수 LAST_DAY(날짜)

SELECT LAST_DAY(SYSDATE) FROM DUAL

다음달이 알고싶을때

SELECT LAST_DAY(ADD_MONTH(SYSDATE,1))FROM DUAL

 

지정된 범위에서 날짜를 반올림/자르는 함수 ROUND/TRUNC(날짜,포멧)

SELECT ROUND(SYSDATE,'CC'), TRUNC(SYSDATE,'CC')FROM DUAL;

 

 

 

 

변환 함수

뉴렉처(오라클) 27강 캡쳐

 

NUMBER 형식을 문자열(VARCHAR2)로 변환 TO_CHAR(NUMBER)

SELECT TO_CHAR(12345678.34, '99,999,999,999.99')FROM DUAL

* 변환시킬 형식이 더 짧을수는 없다 (여유있게 설정후 TRIM으로 해주는 방법도 있다.

뉴렉처(오라클) 27강 캡쳐

 

DATE 형식을 문자열(VARCHAR2)로 변환 TO_CHAR(DATETIME)

SELECT TO_CHAR(SYSDATE,'YYY-MM-DD AM HH24:MI:SS') FROM DUAL

* ( ) 안에는 포맷문자만 사용가능

* 기본적이 날짜 년 월 일 은 TO_DATE 사용

뉴렉처(오라클) 27강 캡쳐

 

문자열을 숫자형식으로 변환하는 함수 TO_NUMBER(문자열)

SELECT TO_NUMBER('1994') FROM DUAL

 

연산시

SELECT TO_NUMBER('2')+3 FROM DUAL  이 방법이 바람직한 방법

 

*변환함수는 TO_ 로 시작한다.

 

 

 

NULL 관련 함수

 

반환값이 NULL일 경우에 대체 값을 제공하는 NVL(NULL,대체값) 함수

SELECT NVL(컬럼명, NULL일경우 대체값) FROM 테이블명

 

 

NVL에서 조건을 하나더 확장한 NVL2 (입력값 , NOTNULL 대체값 , NULL 대체값) 함수

SELECT NVL2(컬럼명,NULL이아닐경우 대체값, NULL일경우 대체값) FROM 테이블명

 

두 값이 같은 경우 NULL 그렇지 않은 경우 첫번 째 값 반환 NULLIF(값1,값2)함수

SELECT NULLIF(컬렴명,19)FROM 테이블명

*NULL값을 만들기위해 사용되는 함수

 

 

기타 함수

 

 

조건에 따른 값 선택하기 DECODE(기준값,비교값,출력값,비교값,출력값)

SELECT DECODE(GENDER,'남성',1,2)FROM MEMBER

*GENDER의 값이 '남성'일경우에는 1 출력 아닐경우에는 2 출력

*경우에따라 인자값을 계속추가할수있다. 

EX) SELECT DECODE(SUBSTR(PHONE,1,3),

                                 '010' , '신규번호'

                                 '011' , '구번호'

                                 '기타' )FROM MEMBER

 

 

느낀점 : 문자열 추출할수 있는 함수를 알고있었다면 팀프로젝트 DAO부분이 좀더 깔끔하게 만들수 있었겠다는 생각을했다.  연 , 월 , 일 로 나누어서 효과적으로 데이터를 분류 할 수 있었을 것 , 데이터를 다양한 방법으로 추출하기위해선 함수가 필요하다. 개인프로젝트는 최대한 함수를사용해서 다양한방법으로 추출해봐야겠다. 데이터 함수 포함