오라클 명령어-(2)

• SELECT ename, upper(ename), lower(ename),initcap(ename) FROM emp; (upper은 대문자로 바꿔주는 함수, lower는 소문자로 바꿔주는 함수, initcap은 첫글자는 대문자로 나머지는 소문자로 바꿔주는 함수)
• SELECT * from emp WHERE upper(ename) LIKE upper('%smith%'); 사원이름에 smith 단어를 포함한 데이터를 출력
• SELECT ename, length(ename) FROM emp; (문자열 길이를 출력하는 함수 length)
• SELECT ename, length(ename) FROM emp WHERE length(ename)>=5; (사원이름의 길이가 5이상인 사원 출력)
• SELECT length('한글'), lengthb('한글') FROM dual ; (lengthb함수는 문자열의 바이트 길이를 나타내는 함수, 영문은 1글자당 1바이트 한글은 1글자당 2바이트)
• SELECT job, substr(job,1,2), substr(job,3,2),substr(job,5)  FROM emp; (시작위치 1번째에서 2글자, 시작위치 3번째에서 2글자, 시작위치 5번째에서 끝까지 가져오는 문자열 일부 추출

-length()함수는 오른쪽 끝에서부터 왼쪽으로 인덱스 부여

-기본 상태는 왼쪽부터 1,2,3,4,5 방식으로 인덱스 부여

• SELECT job, substr(job,-length(job)) FROM emp; ( 처음 인덱스부터 끝까지 출력)
• SELECT job, substr(job,length(job)) FROM emp;  (-length(job) 인덱스부터 끝까지 출력)
• SELECT job, substr(job,-5,2) FROM emp; (-5인덱스부터 2글자 출력)
• SELECT instr('HELLO, ORACLE!','L')AS INSTR_1,  ( L의 위치는 3번째)
      instr('HELLO, ORACLE!','L',5) AS INSTR_2, ( 5번째글자 이후에 나오는 L의 위치는 12번째)
      INSTR('HELLO, ORACLE!', 'L',2,2) AS INSTR_3, (2번째글자 이후에 나오는 L의 2번째 위치는 4)
      INSTR('HELLo, ORACLE!', 'L',2,3) AS INSTR_4  ( 2번째글자 이후에 나오는 L의 3번째 위치는 12)
      FROM dual;
• SELECT * FROM emp WHERE instr(ename,'S')>0; (instr함수로 사원이름에 s가 있는 데이터 출력)
• SELECT '010-1111-1111' AS re_before, 
  replace ('010-1111-1111', '-', ' ') as re_1, (치환 문자를 공백으로 지정)
  replace ('010-1111-1111', '-') as re_2 (치환 문자를 지정하지 않을 시 붙어서 나옴)
  from dual; 
• SELECT 'ORACLE', LPAD('ORACLE',10,'#')as LPAD_1, -- 전체 10자리를 만들고 왼쪽의 빈공간을 '#'으로 채움
      RPAD('ORACLE',10,'*') as RPAD_1, -- 전체 10자리를 만들고 오른쪽의 빈공간을 '#'으로 채움
      LPAD('ORACLE',10) as LPAD_2, -- 전체 10자리를 만들고 왼쪽의 빈공간을 공백으로 채움
      RPAD('ORACLE',10) as RPAD_2 -- 전체 10자리를 만들고 오른쪽의 빈공간을 공백으로 채움
      FROM dual;
• SELECT RPAD('970101-',14,'*') as RPAD_1,
      RPAD('010-1234-',13,'*') as RPAD_2
      FROM dual; (실제 사용 예로 주민번호와 전화번호 가리기)
• SELECT concat(empno, ename), 
      concat(empno, concat(' : ', ename))
      FROM emp WHERE ename = 'SMITH'; (문자열을 합치는 함수 concat)
• SELECT empno || ename,
      empno || ' : ' || ename
      FROM  emp
      WHERE ename = 'SMITH'; (문자열을 연결하는 연산자 ||  결과는 concat과 동일)

삭제할 문자가 없을때

• SELECT '[' || trim(' _ _Oracle_ _ ') || ']' as trim_trim, (양쪽의 공백 하나씩제거)
         '[' || trim(LEADING FROM ' _ _Oracle_ _ ') || ']' as trim_leading, (왼쪽의 공백 하나제거)
         '[' || trim(TRAILING FROM ' _ _Oracle_ _ ') || ']' as trim_trailing, (오른쪽의 공백 하나제거)
         '[' || trim(BOTH FROM ' _ _Oracle_ _ ') || ']' as trim_both (양쪽의 공백 하나씩제거)
      FROM dual;

삭제할 문자가 있을때

• SELECT '[' || trim(' _  _Oracle_  _ ') || ']' as trim_trim, (양쪽의 '-'문자 하나씩 제거)
         '[' || trim(LEADING '_' FROM ' _  _Oracle_  _ ') || ']' as trim_leading, (왼쪽의 '-'문자 하나제거)
         '[' || trim(TRAILING '_' FROM ' _  _Oracle_  _ ') || ']' as trim_trailing, (오른쪽의 '-'문자 하나제거)
         '[' || trim(BOTH '_' FROM ' _  _Oracle_  _ ') || ']' as trim_both (양쪽의 '_'문자 하나씩 제거)
      FROM dual;

 

• SELECT '[' || trim('  --Oracle--  ') || ']' as trim_trim,
         '[' || ltrim('  --Oracle--  ') || ']' as trim_l,
         '[' || ltrim('<--Oracle-->', '-<') || ']' as trim_l2, --(-, <)를 조합해서 모든 경우를 삭제해준다.
         '[' || rtrim('  --Oracle--  ') || ']' as trim_r,
         '[' || rtrim('<--Oracle-->', '>-') || ']' as trim_r2 -- (>, -)를 조합해서 모든 경우를 삭제해준다.
         FROM dual;
• SELECT round(1234.5678) as r_1, 범위지정을 안할땐 반올림해서 일의자리까지 나타냄(소수점 첫번째 자리에서 반올림)
  round(1234.5678,0) as r_2, 범위지정이 0 일땐 반올림해서 일의자리까지 나타냄(소수점 첫번째자리에서 반올림)
  round(1234.5678,1)as r_3, -- 반올림해서 소수점 첫째 자리까지
  round(1234.5678,2) as r_4, -- 반올림해서 소수점 둘째 자리까지
  round(1234.5678,-1) as r_5, --  -기호는 정수부분을 의미( 반올림해서 일의자리까지)
  round(1234.5678,-2) as r_6 --   반올림 해서 10의 자리까지
  FROM dual;
• SELECT trunc(1234.5678) as r_1,
  trunc(1234.5678,0) as r_2,
  trunc(1234.5678,1)as r_3, -- 소수점 둘째자리에서 버리고 첫째자리까지 표현
  trunc(1234.5678,2) as r_4, -- 소수점 셋째자리에서 버리고 둘째자리까지 표현
  trunc(1234.5678,-1) as r_5, --  -기호는 정수부분을 의미( 1의자리에서 버림)
  trunc(1234.5678,-2) as r_6 -- 10의자리에서 버림
  FROM dual;
• SELECT ceil(3.14), 
      floor(3.14),
      ceil(-3.14),
      floor(-3.14)
      FROM dual;
• SELECT mod(15,6),
      mod(10,2)
      FROM dual; (나머지 계산함수)
• SELECT sysdate as now, sysdate - 1 as yesterday, sysdate + 1 as tomorrow FROM dual; (시스템 날짜 출력)
• SELECT sysdate, add_months(sysdate,3) from dual; (몇 개월 이후 출력)
• SELECT empno,ename, hiredate, sysdate FROM emp
  WHERE add_months(hiredate,500) > sysdate; (실제 사용 예시)
• SELECT empno,ename, hiredate,sysdate,months_between(hiredate,sysdate) as m1,
  months_between(sysdate,hiredate) as m2, 
  trunc(months_between(sysdate,hiredate)) as m3 (소수점은 버림)
  FROM emp; (두날짜간의 개월 수 차이)
• SELECT sysdate, next_day(sysdate, '월요일'), last_day(sysdate) FROM dual; (돌아오는 월요일은 next_day함수, 이번달의 마지막 날짜는 last_day함수)
• SELECT sysdate,
  round(sysdate, 'CC') as f_cc, -- 2050년을 기준으로 년도 이하이면 2001년으로 처리
  round(sysdate, 'YYYY') as f_yyyy, -- 모든년도를 7월1일을 기준으로 7월1일 이상이면 1년을 더해줌
  round(sysdate, 'Q') as f_q, -- 각 분기의 두번째 달의 16일 기준 
  round(sysdate, 'DDD') as f_ddd, -- 해당일의 정오를 기준
  round(sysdate, 'HH') as f_hh -- 해당일의 시간을 기준으로 반올림
  FROM dual;
• SELECT sysdate,
  trunc(sysdate, 'CC') as f_cc,
  trunc(sysdate, 'YYYY') as f_yyyy, 
  trunc(sysdate, 'Q') as f_q,
  trunc(sysdate, 'DDD') as f_ddd, 
  trunc(sysdate, 'HH') as f_hh 
  FROM dual;
• SELECT to_char(sysdate, 'YYYY/MM/DD HH24:MI:SS') FROM dual;
  SELECT to_char(sysdate, 'YYYY-MM-DD HH24:MI:SS') FROM dual;
• SELECT 1300 - '1500', '1300'+'1500' FROM dual; -- 묵시적형변환(자동으로 형변환)
• SELECT '1,300'-'1,500' FROM dual; --  이 경우는 작동 x
• SELECT to_number('1,300', '999,999') - to_number('1,500','999,999') FROM dual;  (999,999로 천단위로 숫자를 구분한다는 포맷을 지정해줘야 계산이 가능하다)
• SELECT to_date('2023-07-25','YYYY-MM-DD')as date_1,
         to_date('20230725', 'YYYY-MM-DD')as date_2,
         to_date('2023/07/25','YYYY-MM-DD')as date_3,
         to_date('2023-07-25','YY-MM-DD')as date_4
         FROM dual; (문자를 날짜로 바꾸는 함수)
• SELECT empno, ename, sal, comm, sal+comm, nvl(comm,0), sal+ nvl(comm,0) FROM emp; (comm의 null값을 0으로 대체해서 계산)
• SELECT empno, ename, sal, comm,nvl2(comm,'o','x'), nvl2(comm,sal*12 + comm,sal*12) as annsal FROM emp;