Oracle 내장 함수

<문자 함수>

LENGTH : 문자의 길이를 반환한다.

SELECT LENGTH('ABC'), LENGTH('코딩공부블로그') --각각 3, 7 출력
FROM DUAL; --테이블 없이 계산 출력/함수 확인용 연습테이블(오라클)

UPPER : 대문자로 변환 ↔ LOWER : 소문자로 변환

INITCAP : 맨 앞글자만 대문자로 변환

SELECT UPPER('abC'), --ABC
	LOWER('ABc'), --abc
	INITCAP('something good can work') --Something Good Can Work
FROM DUAL;

*REPLACE(문자열, 바꿀 문자, 바뀔 문자) :문자열의 특정 문자를 다른 문자로 변경 

*SUBSTR(문자열, 시작위치, 자를 문자 수) : 문자열을 원하는 위치에서 추출

시작 위치를 음수로 주면 뒤에서부터 위치를 지정하고 오른쪽 방향으로 추출한다.

SELECT REPLACE('something good', 'o', 'O'), --sOmething gOOd
	SUBSTR('something good can work', 11,4) --good
FROM DUAL;

*INSTR(문자열, 찾는 문자, 시작 위치, 몇 번째) : 다수의 특정 문자 중 원하는 순서의 문자 위치를 반환

--abcabcabc의 1번째 글자부터 3번째 a의 위치를 찾는다
SELECT INSTR('abcabcabc','a',1,3), --7
	INSTR('abcabcabc','d',1,3), --찾는 문자가 없으면 0
	INSTR('abcabcabc','a',1,4) --0
FROM DUAL;

LTRIM, RTRIM, TRIM : 문자열에서 원하는 부분을 왼/오른쪽/전체에서 제거

SELECT LTRIM('aaabbbcccaaa','a'), --bbbcccaaa
	RTRIM('aaabbbcccaaa','a'), --aaabbbccc
	TRIM('a' FROM 'aaabbbcccaaa') --bbbccc *FROM 꼭 붙여야 한다.
FROM DUAL;

LPAD, *RPAD : 문자열의 왼/오른쪽에 정해진 칸수만큼 채우는 문자를 추가

SELECT LPAD('Kim',8,'#'), --#####Kim
	RPAD('Kim',8,'#'), --Kim#####
	LPAD('Kim',2,'#'), --Ki *문자열이 잘릴 수도 있다.
	RPAD(SUBSTR('Kimdoodoo',1,3),LENGTH('kimdoodoo'),'*') --Kim******
FROM DUAL;

CONCAT : 문자열 결합

SELECT CONCAT('김두두',' 잔다~') //김두두 잔다~
FROM DUAL;

 

 

<숫자 함수>

MOD : 나머지(%)

*CEIL : 정수로 올림

*ROUND : 반올림 (소수점자리 지정하지 않으면 1의 자리)

TRUNC : 버림 (소수점 자리 지정하지 않으면 1의 자리)

SELECT MOD(10,3), --1
	CEIL(0.54), --1
	CEIL(14), --14
	ROUND(0.135,2), --0.14
	TRUNC(125.23,1) --125.2
FROM DUAL;

 

 

<날짜 함수>

*SYSDATE : 시스템의 날짜&시간 읽기(정수형처럼 계산이 된다)

SELECT SYSDATE-1, --22/06/20
	SYSDATE, --22/06/21
	SYSDATE+1 --22/06/22
FROM DUAL;

*MONTHS_BETWEEN : 기간에 해당하는 개월 수

SELECT MONTHS_BETWEEN('22/06/21','22/02/21'), --4
	MONTHS_BETWEEN('22/06/21','22/02/01') --4.6451...
FROM DUAL;

 

NEXT_DAY(날짜, 요일) : 오늘을 지나 다음에 오는 지정요일이 며칠인지 반환

LAST_DAY(날짜) : 지정날짜 달의 마지막 날짜를 반환

SELECT NEXT_DAY('22/06/21','화'), --22/06/28
	LAST_DAY('22/06/21') --22/06-30
FROM DUAL;

*ADDS_MONTH(날짜, 개월 수) : 등록된 날짜에서 개월 수가 추가된 날짜를 반환

SELECT ADD_MONTHS(SYSDATE, 3) --22/09/21
FROM DUAL;

 

 

<변환 함수>

***TO_CHAR : 숫자나 날짜의 형식을 변경해서 문자열로 반환

SELECT TO_CHAR(SYSDATE,'yyyy-mm-dd hh:mm:ss'), --2022-06-21 15:09:33
	TO_CHAR(SYSDATE,'yyyy"년도"mm"월"dd"일"') --2022년도 06월 21일
FROM DUAL;

SELECT TO_CHAR(13000000,'L99,999,999') --₩13,000,000
FROM DUAL;

 

 

<기타 함수>

*****NVL : Null 값을 다른 값으로 대체

DECODE : 다중 조건문을 구현한다.

--deptno가 10이면 개발부, 20이면 영업부, 30이면 총무부라고 나타내는 컬럼을 나타낸다.
SELECE ename, 
	DECODE(deptno, 
		10, '개발부',
		20, '영업부',
		30, '총무부') dname
FROM emp;

CASE : MySQL과 동일함. 여기 참고

 

JDBC로 오라클과 자바 연결하기

dao.Emp.java

package doodoo.dao;

import java.util.*;
// DB의 컬럼과 데이터형을 일치시킨다.

public class Emp {
	private int empno;
	private String ename;
	private String job;
	private int mgr;
	private Date hiredate; 
	private int sal;
	private int comm;
	private int deptno;
	
	public int getEmpno() {
		return empno;
	}
	public void setEmpno(int empno) {
		this.empno = empno;
	}
	public String getEname() {
		return ename;
	}
	public void setEname(String ename) {
		this.ename = ename;
	}
	public String getJob() {
		return job;
	}
	public void setJob(String job) {
		this.job = job;
	}
	public int getMgr() {
		return mgr;
	}
	public void setMgr(int mgr) {
		this.mgr = mgr;
	}
	public Date getHiredate() {
		return hiredate;
	}
	public void setHiredate(Date hiredate) {
		this.hiredate = hiredate;
	}
	public int getSal() {
		return sal;
	}
	public void setSal(int sal) {
		this.sal = sal;
	}
	public int getComm() {
		return comm;
	}
	public void setComm(int comm) {
		this.comm = comm;
	}
	public int getDeptno() {
		return deptno;
	}
	public void setDeptno(int deptno) {
		this.deptno = deptno;
	}
}

 

dao.EmpDAO.java

package doodoo.dao;
import java.util.*; //ArrayList 
import java.sql.*; //오라클 연결

public class EmpDAO {
  //오라클 연결 객체
  private Connection conn;
  //SQL문장 전송
  private PreparedStatement ps;
  //연결할 오라클 서버 주소(URL)
  private final String URL = "jdbc:oracle:thin:@localhost:1521:XE";

  //드라이버 등록
  public EmpDAO() {
    try {
      Class.forName("oracle.jdbc.driver.OracleDriver");
    } catch (Exception ex) {}
  }
  //오라클 연결
  public void getConnection() {
    try {
      Connection conn = DriverManager.getConnection(URL, "hr", "happy");
    } catch (Exception ex) {}
  }
  //오라클 닫기
  public void disConnection() {
    try {
      if (ps != null) ps.close();
      if (conn != null) conn.close();
    } catch (Exception ex) {}
  }

  //기능1. 목록 출력
  //클래스는 한 명, 한 개에 대한 정보를 담고 있다. -> 모아서 관리하는게 List
  public ArrayList <Emp> empListData() {
    ArrayList <Emp> list = new ArrayList <Emp> ();
    try {
      getConnection(); //연결
      String sql = "SELECT empno, ename, job, hiredate, sal, NVL(comm,0)" +
        "FROM emp"; //SQL문장 만들기
      ps = conn.prepareStatement(sql); //오라클에 전송
      ResultSet rs = ps.executeQuery(); //결과값 받기
      while(rs.next()) { //처음부터 마지막까지
    	  Emp emp = new Emp();
    	  emp.setEmpno(rs.getInt(1));
    	  emp.setEname(rs.getString(2));
    	  emp.setJob(rs.getString(3));
    	  emp.setHiredate(rs.getDate(4));
    	  emp.setSal(rs.getInt(5));
    	  emp.setComm(rs.getInt(6));
    	  
    	  list.add(emp);
      }
      rs.close();
    } catch (Exception ex) {
      ex.printStackTrace();
    } finally {
      disConnection();
    }
    return list;
  }
}

user.MainClass.java

package doodoo.user;
import doodoo.dao.*;
import java.util.*;

public class MainClass {

	public static void main(String[] args) {
		EmpDAO dao = new EmpDAO();
		ArrayList<Emp> list = dao.empListData();
		
		for(Emp e:list) {
			System.out.println(e.getEmpno() + " "
					+ e.getEname() + " "
					+ e.getJob() + " "
					+ e.getHiredate().toString()+ " "
					+ e.getSal() + " "
					+ e.getComm());
		}
	}

}