오라클 단일행 함수

-- INITCAP

select ename, INITCAP(ename) "INITCAP"

from emp 

where deptno = 10;

select name, INITCAP(name) "INITCAP"

from professor

where deptno = 101;

-- LOWER, UPPER

select ename, LOWER(ename) "LOWER", UPPER(ename) "UPPER"

from emp 

where deptno = 10;

-- LENGTH, LENGTHB

select ename, LENGTH(ename) "LENGTH", LENGTHB(ename) "LENGTHB"

from emp

where deptno = 20;

select '서진수' "NAME", LENGTH('서진수') "LENGTH", LENGTHB('서진수') "LENGTHB"

from dual;

set verify off;

select ename, LENGTH(ename)

from emp

where LENGTH(ename) > LENGTH('&ename');

-- CONCAT

select CONCAT(ename, job)

from emp

where deptno=10;

-- SUBSTR

select SUBSTR('abcde', 3, 2) "3,2", 

        SUBSTR('abcde', -3, 2) "-3,2", 

        SUBSTR('abcde', -3, 4) "-3,4"

from dual;

-- SUBSTRB

select '서진수' "NAME", SUBSTR('서진수', 1, 2) "SUBSTR", 

                        SUBSTRB('서진수', 1, 3) "SUBSTRB"

from dual;

-- INSTR

select 'A-B-C-D', INSTR('A-B-C-D', '-', 1, 3) "INSTR"

from dual;

select 'A-B-C-D', INSTR('A-B-C-D', '-', 3, 1) "INSTR"

from dual;

select 'A-B-C-D', INSTR('A-B-C-D', '-', -1, 3) "INSTR"

from dual;

select 'A-B-C-D', INSTR('A-B-C-D', '-', -6, 2) "INSTR"

from dual;

-- LPAD

select name, id, LPAD(id, 10, '*')

from student

where deptno1 = 201;

-- RPAD

select RPAD(ename, 10, '-') "RPAD"

from emp

where deptno = 10;

-- LTRIM

select ename from emp

where deptno = 10;

select LTRIM(ename, 'C') 

from emp

where deptno = 10;

-- RTRIM

select ename, RTRIM(ename, 'R') "RTRIM"

from emp

where deptno = 10;

-- REPLACE

select ename, REPLACE(ename, SUBSTR(ename,1,2),'**') "REPLACE"

from emp

where deptno = 10;

-- ROUND

select ROUND(987.654, 2) "ROUND1", 

        ROUND(987.654, 0) "ROUND2", 

        ROUND(987.654, -1) "ROUND3"

from dual;

-- TRUNC

select TRUNC(987.654, 2) "TRUNC1", 

        TRUNC(987.654, 0) "TRUNC2", 

        TRUNC(987.654, -1) "TRUNC3"

from dual;

-- MOD, CEIL, FLOOR

select MOD(121, 10) "MOD",

        CEIL(123.45) "CEIL",

        FLOOR(123.45) "FLOOR"

from dual;

select rownum "ROWNUM", CEIL(rownum/3) "TEAMNO", ename

from emp;

-- POWER

select power(2,3) from dual;

-- sysdate

select sysdate from dual;

alter session set NLS_DATE_FORMAT="RRRR-MM-DD:HH24:MI:SS";

select sysdate from dual;

-- MONTHS_BETWEEN

select MONTHS_BETWEEN('14/09/30', '14/08/31')

from dual;

select MONTHS_BETWEEN('14/08/31', '14/09/30')

from dual;

select MONTHS_BETWEEN('12/02/29', '12/02/01')

from dual;

select MONTHS_BETWEEN('14/04/30', '14/04/01')

from dual;

select MONTHS_BETWEEN('14/05/31', '14/05/01')

from dual;

select MONTHS_BETWEEN('12/03/01', '12/02/28')

from dual;

select MONTHS_BETWEEN('14/03/01', '14/02/28')

from dual;

select ename, hiredate, 

        ROUND(MONTHS_BETWEEN(TO_DATE('04/05/31'), hiredate), 1) "DATE1",

        ROUND(((TO_DATE('04/05/31') - hiredate)/31), 1) "DATE2"

from emp

where deptno = 10;

        

-- ADD_MONTHS

select sysdate, ADD_MONTHS(SYSDATE,1) from dual;

-- NEXT_DAY

select sysdate, NEXT_DAY(SYSDATE, 'MON') from dual; -- 리눅스용 오라클

select sysdate, NEXT_DAY(SYSDATE, '월') from dual; -- 윈도우용 오라클

-- LAST_DAY

select sysdate, LAST_DAY(SYSDATE), LAST_DAY('14/05/01')

from dual;

-- 날짜의 ROUND, TRUNC

alter session set NLS_DATE_FORMAT='YYYY-MM-DD:HH24:MI:SS';

select sysdate, ROUND(SYSDATE), TRUNC(SYSDATE) from dual;

-- 자동형변환

select 2 + '2' from dual;

-- TO_CHAR (날짜 -> 문자로 형 변환하기)

select sysdate, TO_CHAR(SYSDATE, 'YYYY') "YYYY",

                TO_CHAR(SYSDATE, 'RRRR') "YYYY",

                TO_CHAR(SYSDATE, 'YY') "YYYY",

                TO_CHAR(SYSDATE, 'RR') "YYYY",

                TO_CHAR(SYSDATE, 'YEAR') "YYYY"

from dual;

select sysdate, TO_CHAR(SYSDATE, 'MM') "MM",

                TO_CHAR(SYSDATE, 'MON') "MON",

                TO_CHAR(SYSDATE, 'MONTH') "MONTH"

from dual;

select sysdate, TO_CHAR(SYSDATE, 'DD') "DD",

                TO_CHAR(SYSDATE, 'DAY') "DAY",

                TO_CHAR(SYSDATE, 'DDTH') "DDTH"

from dual;

select sysdate, TO_CHAR(SYSDATE, 'RRRR_MM-DD:HH24:MI:SS') from dual;

-- TO_CHAR (숫자형 -> 문자형으로 형 변환하기)

select empno, ename, sal, comm, 

        TO_CHAR((sal*12)+comm, '999,999') "SALARY" 

from emp

where ename = 'ALLEN';

select name, pay, bonus, 

        TO_CHAR((pay*12)+bonus, '999,999') "TOTAL"

from professor

where deptno = 201;

-- TO_NUMBER

select TO_NUMBER('5') from dual;

select TO_NUMBER('A') from dual; -- 오류

select ASCII('A') from dual;

-- TO_DATE. 리눅스용 오라클에서는 날짜형태 주의. 미리 날짜 모양 설정 후 사용가능

select TO_DATE('14/05/31') from dual;

select TO_DATE('2014/05/31') from dual;

-- NVL

select ename, comm, NVL(comm, 0), NVL(comm, 100)

from emp 

where deptno = 30;

-- NVL2

select empno, ename, sal, comm,

        NVL2(comm, sal+comm, sal*0) "NVL2"

from emp

where deptno = 30;

-- DECODE

select deptno, name, DECODE(deptno, 101, 'Computer Engineering') "DNAME" 

from professor;

select deptno, name, DECODE(deptno, 101, 'Computer Engineering', 'ETC') "DNAME" 

from professor;

select deptno, name, DECODE(deptno, 101, 'Computer Engineering',

                                    102, 'Multimedia Engineering',

                                    103, 'Software Engineering',

                                    'ETC') "DNAME" 

from professor;

select deptno, name, DECODE(deptno, 101, DECODE(name, 'Audie Murphy', 'BEST!')) "ETC" 

from professor;

select deptno, name,

        DECODE(deptno, 101, DECODE(name, 'Audie Murphy', 'BEST!', 'GOOD!')) "ETC" 

from professor;

select deptno, name,

        DECODE(deptno, 101, DECODE(name, 'Audie Murphy', 'BEST!', 'GOOD!'), 'N/A') "ETC" 

from professor;

-- CASE

select name, tel, 

        CASE(SUBSTR(tel,1,INSTR(tel,')')-1)) WHEN '02' THEN 'SEOUL' 

                                             WHEN '031' THEN 'GYEONGGI' 

                                             WHEN '051' THEN 'BUSAN' 

                                             WHEN '052' THEN 'ULSAN' 

                                             WHEN '055' THEN 'GYEONGNAM'

                                                        ELSE 'ETC'

        END "LOC"

from student

where deptno1 = 201;

select name, SUBSTR(jumin, 3, 2) "MONTH",

        CASE WHEN SUBSTR(jumin, 3, 2) BETWEEN '01' AND '03' THEN '1/4'

             WHEN SUBSTR(jumin, 3, 2) BETWEEN '04' AND '06' THEN '2/4'

             WHEN SUBSTR(jumin, 3, 2) BETWEEN '07' AND '09' THEN '3/4'

             WHEN SUBSTR(jumin, 3, 2) BETWEEN '10' AND '12' THEN '4/4'         

        END "Quarter"

from student;