오라클 기본함수2

    NVL(컬럼, 컬럼이 NULL의 경우 설정할 값) 
     
    NVL2(컬럼, 컬럼이 NULL이 아닐 경우 설정할 값, 컬럼이 NULL일경우 설정 할 값)

 

 

DUAL Table -> 오라클에서 지원하는 결과 확인용 Table

--DUAL Table : 가상 테이블 -> 결과 확인용 Table
SELECT 1 FROM DUAL;      -- 1
SELECT 'A' FROM DUAL;
SELECT '가나' FROM DUAL;
SELECT 23 * 45 FROM DUAL;       -- 1035

 

 

문자 함수

 

-- CHR( N ) : ASCII 코드 값을 문자로 변환하는 문장

SELECT CHR(65) FROM DUAL;       -- A
SELECT CHR(97) FROM DUAL;       -- a

 

 

문자열 + 문자열

-- "안녕하세요" + "건강하세요" = "안녕하세요건강하세요"
--              ||          -> 오라클에서는 + 대신에 ||(OR) 을 씀
SELECT '내 점수는 ' || CHR(65) || '입니다' FROM DUAL;      --내점수는 A입니다

 

 

--LPAD(RPAD) : 나머지를 빈칸(지정문자)으로 채운다

--LPAD(RPAD) : 나머지를 빈칸(지정문자)으로 채운다
SELECT LPAD('BBB', 10) FROM DUAL;   --문자 왼쪽(LPAD)에 빈칸 10개추가
SELECT RPAD('BBB', 10) FROM DUAL;   --오른쪽(RPAD)
SELECT LPAD('BBB', 10, '-') FROM DUAL;  --왼쪽에 '-'특정문자로 채움
SELECT LPAD('123', 10, '0') FROM DUAL;  

 

 

 

--INSTR == indexOf('a') defande => 3출력(번지수)

--INSTR == indexOf('a') defande => 3출력(번지수)
SELECT INSTR('123ABC456DEF', 'A') FROM DUAL;    --DB는 0번지가 없으므로 4가 나온다
SELECT INSTR('123ABC456DEF', '1') FROM DUAL;   

SELECT INSTR('123ABC456DEFABC', 'A', 7) FROM DUAL;   --7번째 이후의 A를 찾아라 13
SELECT INSTR('123ABC456DEFABCABC', 'A', 7, 1) FROM DUAL;    --7번째 이후에 1번째 A를 찾아라 13
SELECT INSTR('123ABC456DEFABCABC', 'A', 7, 2) FROM DUAL;    -- 16
SELECT INSTR('123ABC456DEF', 'Y') FROM DUAL;        --못찾으면(없으면) 0

 

 

 

--REPLACE : 문자열 치환

--REPLACE : 문자열 치환
SELECT REPLACE('AAAAABCD','A') FROM DUAL;   --A 를 빈문자로 바꿔라
SELECT REPLACE('AAAAABCD','A', 'a') FROM DUAL;   --A를 a로 바꿔라
SELECT REPLACE('AAAAABCD','AA', 'a') FROM DUAL; 

 

 

--TRANSLATE : 문자치환

SELECT TRANSLATE('AAAAABCD','A', 'a') FROM DUAL;    
SELECT TRANSLATE('AAAAABCD','A', 'a') FROM DUAL;    
--문자 치환이여서 위 문장과 같은 답이 나옴(문자열은 못읽고 문자하나만 읽음)

 

숫자

 

--올림
SELECT CEIL(13.1) FROM DUAL;    --14 -> 올림함수

--내림
SELECT FLOOR(13.9) FROM DUAL;       -- 13

--나눈 나머지
SELECT MOD(3,2) FROM DUAL;      -- 1

--승수
SELECT POWER(3,2) FROM DUAL;    -- 9 -> 3의 2승

--반올림
SELECT ROUND(13.4) FROM DUAL;   -- 13

--부호 + : 1,  0:0  ,- : -1
--SIGN
SELECT SIGN(13.4) FROM DUAL;    -- 1
SELECT SIGN(0) FROM DUAL;       -- 0
SELECT SIGN(-0.1) FROM DUAL;    -- -1

--버림
--TRUNC
SELECT TRUNC(12.3456) FROM DUAL;        -- 12
SELECT TRUNC(12.3456, 2) FROM DUAL;     --소수점 2번째자리까지만
SELECT TRUNC(12.3456, -1) FROM DUAL;    -- 10   -> -뒷자리수만큼 0으로 표현
SELECT TRUNC(2312.345632, -3) FROM DUAL;        -- 2000

--ASCII <-> CHR
SELECT ASCII('A') FROM DUAL;        -- 65

--변환함수
--TO_CHAR
--DATD -> VARCHAR2
SELECT TO_CHAR(SYSDATE)
FROM DUAL;      -- 20/06/17

SELECT TO_CHAR(SYSDATE, 'YYYY-MM-DD-HH-MI-SS')
FROM DUAL;      -- 현재시분초..

SELECT TO_CHAR(SYSDATE, 'YYYY/MM/DD HH:MI:SS')
FROM DUAL;

SELECT TO_CHAR(100000000000, '$999,999,999,999')
FROM DUAL;      -- $100,000,000,000달러로 표시 (문자열)

 

 

--TODATE

--VARCHAR2 -> DATE 로 바뀔때
SELECT TO_DATE('20200617')
FROM DUAL;      --20/06/17

SELECT TO_DATE('12252020', 'MMDDYYYY')
FROM DUAL;      --20/12/25

--TO_NUMBER     VARCHAR2 -> NUMNBER     parseInt()
SELECT TO_NUMBER('123') + 12
FROM DUAL;      --135

--LAST_DAY
SELECT LAST_DAY('20/06/01')
FROM DUAL;      --20/06/30  -> 해당 월의 마지막 말짜가 나옴 (아래 모두 동일)

SELECT LAST_DAY('20-06-01')
FROM DUAL; 

SELECT LAST_DAY('200601')
FROM DUAL; 

SELECT LAST_DAY( TO_DATE('200601', 'YYMMDD'))       --정석버전
FROM DUAL; 

 

년, 월, 일, 시, 분, 초

--MONTHS_BETWEEN : 두 날짜간의 월 수 (월 간격/차이 수)
SELECT MONTHS_BETWEEN('2020-12-04','2020-05-13')
FROM DUAL;      -->6.70$&^*

--ADD_MONTHS : 특정 날짜 월에 정수를 더한 다음 해당 날짜를 반환
SELECT ADD_MONTHS('2020-06-19',3)   --3개월 후의 날짜가 표시됨
FROM DUAL;

--날짜 정보에서 특정한 연도, 월, 일, 시, 분, 초
SELECT SYSDATE
FROM DUAL;

--EXTRACT
SELECT EXTRACT(YEAR FROM TO_DATE('200619','YYMMDD')) AS 연도,
    EXTRACT(month from TO_DATE('200815','YYMMDD')) AS 월,
    EXTRACT(DAY FROM SYSDATE) AS 일
FROM DUAL;

--시, 분, 초(예매, 경매 등)
SELECT EXTRACT(HOUR FROM CAST(SYSDATE as TIMESTAMP)) AS 시,
    EXTRACT(MINUTE FROM CAST(SYSDATE AS TIMESTAMP)) AS 분,
    EXTRACT(SECOND FROM CAST(SYSDATE AS TIMESTAMP)) AS 초
FROM dual;

SELECT EXTRACT(HOUR FROM CAST(SYSDATE as TIMESTAMP)) AS HOURS,      --> 위와 동일
    EXTRACT(MINUTE FROM CAST(SYSDATE AS TIMESTAMP)) AS MINUTES,
    EXTRACT(SECOND FROM CAST(SYSDATE AS TIMESTAMP)) AS SECONDS
FROM (
    SELECT TO_DATE('2020-06-19 09:42:01', 'YYYY-MM-DD HH24:MI:SS')AS datetime FROM DUAL
);

 

 

 

 

 

--SUBSTR 

 

--str.substring(1, 3) "ABCDE" -> 1번지부터 3번지 전까지 : 자바에서는 BC
SELECT SUBSTR('ABCDE',3)    -- 3번지부터 끝까지 -> CDE
FROM DUAL;

SELECT SUBSTR('ABCDE',3, 2)     -- 3번지부터 2개문자만 -> CD
FROM DUAL;

 

 

 

** NVL (컬럼명, 원하는 값)

 

-- 컬럼이 null이 아니면 컬럼의 값을 출력. null이면 원하는 값.
--월급 + 월급 커미션
SELECT employee_id, first_name, salary, NVL(commission_pct, 0), nvl(salary + (salary * commission_pct), 0)
FROM employees;