DB_SQL_단일행함수03

BBBeom·2022년 8월 10일

DB

목록 보기

8/18

형변환 함수

명시적 형변환

TO_CHAR() 숫자,날짜데이터를 문자데이터로 변환
TO_NUMBER() 문자데이터를 숫자데이터로 변환
TO_DATE() 문자데이터를 날짜 데이터로 변환

형변환 함수의 변환순서
숫자 -> 문자 -> 날짜
날짜 -> 문자 -> 숫자

변환순서가 있기때문에 형변환이 필요할경우 순서에 맞게 변경시켜야한다
마음대로 건너뛸수가 없는것이다
항상 문자가 가운데에 오기때문에 일단 문자데이터로 변환후에
숫자라던지 날짜데이터로 변환시키면 된다

임시적 형변환은 프로그램에서 자동으로 알잘딱해서 형변환 시키는것
명시적 형변환은 사용자가 직접 형변환 시키는것

1. TO_CHAR( )

날짜 OR 숫자데이터를 문자데이터로 변환

날짜형식 지정하여 출력하기

SELECT SYSDATE,
TO_CHAR(SYSDATE, 'YYYY/MM/DD HH24:MI:SS') AS 현재날짜시간
TO_CHAR(SYSDATE,'MM') AS MM,
TO_CHAR(SYSDATE,'MON') AS MON,
TO_CHAR(SYSDATE,'MONTH') AS MONTH,
TO_CHAR(SYSDATE,'DD') AS DD,
TO_CHAR(SYSDATE,'DY') AS DY,
TO_CHAR(SYSDATE,'DAY') AS DAY
FROM DUAL;

/*출력값*/
22/08/05	
2022/08/05 
11:27:52	
08	
8월 	
8월 	
05	
금	
금요일

SYSDATE 함수를 DEFAULT로 사용하는 방법도 있지만
TO_CHAR를 같이 활용하면 다양한 결과 값을 받을수있다

다른 언어로 출력하는 방법

'NLS_DATE_LANGUAGE = KOREA'  /*[선택사항] 받을 데이터값의 언어 명시하기*/

TO_CHAR(SYSDATE,'DAY', 'NLS_DATE_LANGUAGE = ENGLISH') AS DAY

/*금요일로 리턴 받던 값이 FRIDAY로 바뀌었다*/

시간형식 지정하여 출력하기

SELECT SYSDATE,
TO_CHAR(SYSDATE, 'HH24:MI:SS') AS HH24MISS,
TO_CHAR(SYSDATE, 'HH12:MI:SS') AS HH12MISS,
TO_CHAR(SYSDATE, 'HH:MI:SS P.M.') AS HHMISSPM,
TO_CHAR(SYSDATE, 'HH:MI:SS AM') AS HHMISSAM
FROM DUAL;

22/08/05
11:23:55	
11:23:55	
11:23:55 오전	
11:23:55 오전

결과값은 이렇게 나온다
24와 12는 시간표시하는 형식을 바꿔준다

숫자형식 지정하여 출력하기

9 -> 숫자의 한자리를 의미(빈자리는 채우지 않음)
0 -> 빈자리를 0으로 채움
$ -> 달러표시
L -> LOCAL 지역화폐 단위
. -> 소수점 표시
, -> 천단위의 구분기호 표시

/*SCOTT 계정 접속*/
SELECT SAL,
TO_CHAR(SAL, '$999,999') AS SAL_$,
TO_CHAR(SAL, 'L999,999') AS SAL_L,
TO_CHAR(SAL, '999,999.00') AS SAL_1,
TO_CHAR(SAL, '000,999,999') AS SAL_2,
TO_CHAR(SAL, '000999999') AS SAL_3,
TO_CHAR(SAL, '$999,999,00') AS SAL_4
FROM EMP;

2. TO_NUMBER( )

문자데이터를 숫자데이터로 변환

SELECT TO_NUMBER('1,300', '999,999') - TO_NUMBER('1,500', '999,999')
FROM DUAL;

SELECT 1300 - '1500', '1300' + 1500 /*프로그램이 임시적 형변환해서*/
FROM DUAL;							/*계산이 가능하다*/

SELECT '1,300' + '1,500'		/*숫자안에 ,가 있어서 문자데이터라고 인식해서*/
FROM DUAL;						/*계산이 불가능하다*/

자동 형변환을 해주는경우도 있지만
, 가 들어간다던지 계산이 불가능할 경우
TO_NUMBER 함수로 형변환을 시키면 계산이 가능해 진다

3. TO_DATE( )

문자데이터를 날짜데이터로 변환

SELECT TO_DATE('2022-08-05', 'YYYY-MM-DD')

/*SCOTT 계정 접속*/
/*EMP테이블에 1981년 6월 1일 이후에 입사한 사원정보를 출력? */

SELECT *
FROM EMP
WHERE HIREDATE > TO_DATE('1981/06/01', 'YYYY-MM-DD');

SELECT * 는 안써주는게 맞다 원칙은 모든 컬럼명을 전부 명시해야된다
OS별로 날짜데이터를 쓰는 표기방법이 다를수 있는데 TO DATE를 활용하면
그런 오류를 방지할 수 있다

NULL 처리 함수 (NVL, NVL2)

데이터가 NULL이면 산술연산자, 비교연산자가 동작하지 않는다
특정열의 데이터가 NULL일 경우 다른값으로 대체가 필요한 상황이 올 수 있다

1. NVL( )

NULL 데이터를 원하는 값으로 치환한다

SELECT EMPNO,
ENAME,
SAL, 
COMM, 
SAL+COMM, 
NVL(COMM,0), 
SAL+NVL(COMM,0)
FROM EMP;

7369	
SMITH	
800			
(null)
(null)
0	
800

2.NVL2( )

NVL 함수와 비슷하지만, 데이터가 NULL이 아닐때
반환할 데이터를 추가로 지정할수 있다(실무에선 쓸일이 별로 없긴하다)

SELECT ENAME,
COMM,
NVL2(COMM,'O', 'X'), /*마치 JAVA의 삼항연산자 같은 논리 NULL이면 O 아니면 X 를 출력*/
NVL2(COMM, SAL*12 + COMM, SAL*12) AS ANNSAL
FROM EMP;

DECODE함수, CASE문

오라클에서는 어떤 데이터를 반환할지 정할때
DECODE함수, CASE문 을 사용한다

1. DECODE( )

EMP테이블에서 
직책이 MANAGER인 사람은 급여 10% 인상
SALESMAN -> 급여 5% 인상
ANALYST -> 동결
나머지 직책 -> 3%인상 
인 결과값을 출력하라?

SELECT EMPNO, ENAME, JOB, SAL,
DECODE(JOB, 
'MANAGER', SAL*1.1,
'SALESMAN', SAL*1.05,
'ANALYST', SAL,
SAL*1.03) AS UPSAL
FROM EMP;

하나의 행을 입력받아서 조건에 맞는 데이터가 있을때
원하는 데이터로 리턴
(JAVA의 IF문처럼 만약 JOB에 MANAGER이 있으면 SAL*1.1을 리턴해라)

2. CASE 문

SELECT EMPNO, ENAME, JOB, SAL,
CASE JOB
WHEN 'MANAGER' THEN SAL*1.1
WHEN 'SALESMAN' THEN SAL*1.05
WHEN 'ANALYST' THEN SAL
ELSE SAL*1.03
END AS UPSAL
FROM EMP;

결과값이 DECODE 예제와 똑같다

SELECT EMPNO, ENAME, JOB, SAL,
CASE
WHEN COMM IS NULL THEN '해당사항 없음'
WHEN COMM = 0 THEN '수당없음'
WHEN COMM > 0 THEN '수당 :'||COMM
END AS COMM_TEXT
FROM EMP;