수업 42일차 - 뷰, 인라인뷰, 시퀀스

 

-- 뷰 생성
CREATE VIEW emp_view
AS
SELECT empno, ename, job, hiredate
FROM emp;

-- 뷰 조회
SELECT * FROM emp_view;

-- 뷰 삭제
drop view emp_view;

 

 

JOIN / VIEW/ 스칼라서브쿼리 작업방식 비교

package com.sist.dao;

import java.util.*;
import java.sql.*;

public class EmpDeptDAO {

	private Connection conn;
	private PreparedStatement ps;
	private final String URL= "jdbc:oracle:thin:@localhost:1521:XE";
	
	public EmpDeptDAO()
	{
		try
		{
			// 메모리 할당
			Class.forName("oracle.jdbc.driver.OracleDriver");
			
		} catch(Exception ex) {}
	}
	
	// 연결
	public void getConnection()
	{
		try
		{
			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) {}
	}
	

	// 싱글톤
	public static EmpDeptDAO newInstance()
	{
		EmpDeptDAO dao= new EmpDeptDAO();
		
		if(dao==null)
			dao= new EmpDeptDAO();
		return dao;
	}
	
	// ㅡㅡㅡㅡㅡㅡㅡㅡ 공통기능
	// 기능 - sql
	// JOIN
	public List<EmpDeptVO> empDeptListData1()
	{
		List<EmpDeptVO> list= new ArrayList<EmpDeptVO>();
		
		try
		{
			getConnection();
			String sql= "select empno, ename, job, hiredate, sal, dname, loc, grade "
						+ "from emp, dept, salgrade "
						+ "where emp.deptno=dept.deptno "
						+ "and sal between losal and hisal";
			
			ps= conn.prepareStatement(sql);
			ResultSet rs= ps.executeQuery();
			
			while(rs.next())
			{
				EmpDeptVO vo= new EmpDeptVO();
				vo.setEmpno(rs.getInt(1));
				vo.setEname(rs.getString(2));
				vo.setJob(rs.getString(3));
				vo.setHiredate(rs.getDate(4));
				vo.setSal(rs.getInt(5));
				vo.setDname(rs.getString(6));
				vo.setLoc(rs.getString(7));
				vo.setGrade(rs.getInt(8));
				
				list.add(vo);
			}
			rs.close();
		} 
		catch(Exception ex)
		{
			ex.printStackTrace();
		}
		finally
		{
			disConnection();
		}
		
		return list;
	}
	
	// VIEW
	public List<EmpDeptVO> empDeptListData2()
	{
		List<EmpDeptVO> list= new ArrayList<EmpDeptVO>();
		
		try
		{
			getConnection();
			String sql= "select * from empdept_view";
			ps= conn.prepareStatement(sql);
			ResultSet rs= ps.executeQuery();
			
			while(rs.next())
			{
				EmpDeptVO vo= new EmpDeptVO();
				vo.setEmpno(rs.getInt(1));
				vo.setEname(rs.getString(2));
				vo.setJob(rs.getString(3));
				vo.setHiredate(rs.getDate(4));
				vo.setSal(rs.getInt(5));
				vo.setDname(rs.getString(6));
				vo.setLoc(rs.getString(7));
				vo.setGrade(rs.getInt(8));
				
				list.add(vo);
			}
			rs.close();
		} 
		catch(Exception ex)
		{
			ex.printStackTrace();
		}
		finally
		{
			disConnection();
		}
		
		return list;
	}
	
	// 스칼라서브쿼리 - join없음
	public List<EmpDeptVO> empDeptListData3()
	{
		List<EmpDeptVO> list= new ArrayList<EmpDeptVO>();
		
		try
		{
			getConnection();
			String sql= "select empno, ename, job, hiredate, sal, "
						+ "(select dname from dept where deptno=emp.deptno) dname, "
						+ "(select loc from dept where deptno=emp.deptno) loc,"
						+ "(select grade from salgrade where emp.sal between losal and hisal) "
						+ "from emp";
			
			ps= conn.prepareStatement(sql);
			ResultSet rs= ps.executeQuery();
			
			while(rs.next())
			{
				EmpDeptVO vo= new EmpDeptVO();
				vo.setEmpno(rs.getInt(1));
				vo.setEname(rs.getString(2));
				vo.setJob(rs.getString(3));
				vo.setHiredate(rs.getDate(4));
				vo.setSal(rs.getInt(5));
				vo.setDname(rs.getString(6));
				vo.setLoc(rs.getString(7));
				vo.setGrade(rs.getInt(8));
				
				list.add(vo);
			}
			rs.close();
		} 
		catch(Exception ex)
		{
			ex.printStackTrace();
		}
		finally
		{
			disConnection();
		}
		
		return list;
	}
	
	

}

 

결과 값이 모두 같지만 VIEW 가 도출 과정이 가장 간단함.

ㅡㅡㅡㅡ JOIN ㅡㅡㅡㅡ
MILLER ACCOUNTING CLERK NEW YORK 2
CLARK ACCOUNTING MANAGER NEW YORK 4
KING ACCOUNTING PRESIDENT NEW YORK 5
SMITH RESEARCH CLERK DALLAS 1
ADAMS RESEARCH CLERK DALLAS 1
JONES RESEARCH MANAGER DALLAS 4
SCOTT RESEARCH ANALYST DALLAS 4
FORD RESEARCH ANALYST DALLAS 4
JAMES SALES CLERK CHICAGO 1
WARD SALES SALESMAN CHICAGO 2
MARTIN SALES SALESMAN CHICAGO 2
TURNER SALES SALESMAN CHICAGO 3
ALLEN SALES SALESMAN CHICAGO 3
BLAKE SALES MANAGER CHICAGO 4
ㅡㅡㅡㅡ VIEW ㅡㅡㅡㅡ
MILLER ACCOUNTING CLERK NEW YORK 2
CLARK ACCOUNTING MANAGER NEW YORK 4
KING ACCOUNTING PRESIDENT NEW YORK 5
SMITH RESEARCH CLERK DALLAS 1
ADAMS RESEARCH CLERK DALLAS 1
JONES RESEARCH MANAGER DALLAS 4
SCOTT RESEARCH ANALYST DALLAS 4
FORD RESEARCH ANALYST DALLAS 4
JAMES SALES CLERK CHICAGO 1
WARD SALES SALESMAN CHICAGO 2
MARTIN SALES SALESMAN CHICAGO 2
TURNER SALES SALESMAN CHICAGO 3
ALLEN SALES SALESMAN CHICAGO 3
BLAKE SALES MANAGER CHICAGO 4
ㅡㅡㅡㅡ 스칼라서브쿼리 ㅡㅡㅡㅡ
SMITH RESEARCH CLERK DALLAS 1
ALLEN SALES SALESMAN CHICAGO 3
WARD SALES SALESMAN CHICAGO 2
JONES RESEARCH MANAGER DALLAS 4
MARTIN SALES SALESMAN CHICAGO 2
BLAKE SALES MANAGER CHICAGO 4
CLARK ACCOUNTING MANAGER NEW YORK 4
SCOTT RESEARCH ANALYST DALLAS 4
KING ACCOUNTING PRESIDENT NEW YORK 5
TURNER SALES SALESMAN CHICAGO 3
ADAMS RESEARCH CLERK DALLAS 1
JAMES SALES CLERK CHICAGO 1
FORD RESEARCH ANALYST DALLAS 4
MILLER ACCOUNTING CLERK NEW YORK 2

 

rownum을 원하는 개수만큼 자르기

-- 상위 5개 출력
select empno, ename, job, rownum
from emp
where rownum<=5;

 

급여를 많이 받는 사원 5명 출력 (rownum 정리 안됨)

select ename, sal, job, rownum
from emp
order by sal desc;

 

 

인라인뷰 사용해서 rownum 순서 바꾸기, 상위 5명 출력 (rownum 정리 됨)

select ename, sal, job, rownum
from (select ename, sal, job from emp order by sal desc)
where rownum <= 5;

 

 

페이징 (1번부터 14번 자르기)

select ename, sal, job, num
from (select ename, sal, job, rownum as num
from (select ename, sal, job from emp order by sal desc))
where num between 1 and 5;

select ename, sal, job, num
from (select ename, sal, job, rownum as num
from (select ename, sal, job from emp order by sal desc))
where num between 6 and 10;

select ename, sal, job, num
from (select ename, sal, job, rownum as num
from (select ename, sal, job from emp order by sal desc))
where num between 11 and 14;

 

 

자바 & 인라인뷰로 페이징 (비교)

package com.sist.dao;

import java.sql.*;
import java.util.*;

public class BooksDAO {

	private Connection conn;
	private PreparedStatement ps;
	private final String URL= "jdbc:oracle:thin:@localhost:1521:XE";
	private static BooksDAO dao; // 싱글톤
	
	
	public BooksDAO()
	{
		try
		{
			// 메모리 할당
			Class.forName("oracle.jdbc.driver.OracleDriver");
			
		} catch(Exception ex) {}
	}
	
	// 연결
	public void getConnection()
	{
		try
		{
			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) {}
	}
	
	// 싱글톤패턴 - map
	public static BooksDAO newInstance()
	{
		if(dao==null)
			dao= new BooksDAO();
		return dao;
	}
	
	// 페이징 - 인라인뷰 - 자바
	public List<BooksVO> booksListData(int page)
	{
		List<BooksVO> list= new ArrayList<BooksVO>();
		
		long start= System.currentTimeMillis();
		
		try
		{
			getConnection();
			
			String sql= "SELECT no, title FROM books ORDER BY no ASC";
			ps= conn.prepareStatement(sql);
			ResultSet rs= ps.executeQuery();
			
			int i=0; // 10개씩 나눠주는 변수
			int j=0; // while문 돌아가는 횟수
			int rowSize= 10; // 개수
			int pageCnt= (page*rowSize)-rowSize; // 시작위치
			
			while(rs.next())
			{
				if(i<rowSize && j>=pageCnt)
				{
					BooksVO vo= new BooksVO();
					
					vo.setNo(rs.getInt(1));
					vo.setTitle(rs.getString(2));
					
					list.add(vo);
					i++;
				}
				j++;
			}
			rs.close();
		} 
		catch(Exception ex)
		{
			ex.printStackTrace();
		}
		finally
		{
			disConnection();
		}
		
		long end= System.currentTimeMillis();
		
		System.out.println("걸린 시간: " + (end-start));
		return list;
	}
	
	public int booksTotalPage()
	{
		int total= 0;
		
		try
		{
			getConnection();
			
			String sql= "select ceil(count(*)/10.0) from books";
			ps= conn.prepareStatement(sql);
			ResultSet rs= ps.executeQuery();
			rs.next();
			total= rs.getInt(1);
			rs.close();
		}
		catch(Exception ex)
		{
			ex.printStackTrace();
		}
		finally
		{
			disConnection();
		}
		return total;
	}
	
	// 인라인뷰로 페이지 나누기
	public List<BooksVO> booksListData2(int page)
	{
		List<BooksVO> list= new ArrayList<BooksVO>();
		long s= System.currentTimeMillis();
		
		try
		{
			getConnection();
			String sql= "select no, title, num "
						+ "from (select no, title, rownum as num "
						+ "from (select no, title "
						+ "from books order by no asc)) "
						+ "where num between ? and ?";
			
			ps= conn.prepareStatement(sql);
			
			int rowSize= 10;
			int start= (page*rowSize)-(rowSize-1);
			int end= rowSize*page;
			
			ps.setInt(1, start);
			ps.setInt(2, end);
			
			ResultSet rs= ps.executeQuery();
			while(rs.next())
			{
				BooksVO vo= new BooksVO();
				
				vo.setNo(rs.getInt(1));
				vo.setTitle(rs.getString(2));
				
				list.add(vo);
			}
			rs.close();
		}
		catch(Exception ex)
		{
			ex.printStackTrace();
		}
		finally
		{
			disConnection();
		}
		
		long e= System.currentTimeMillis();
		System.out.println("걸린 시간: " + (e-s));
		
		return list;
	}
}

package com.sist.main;

import java.util.*;
import com.sist.dao.*;

public class MainClass2 {

	public static void main(String[] args) {
		
		BooksDAO dao= BooksDAO.newInstance();
		
		Scanner sc= new Scanner(System.in);
		System.out.println("페이지 입력: ");
		int page= sc.nextInt();
		int totalPage= dao.booksTotalPage(); // 총페이지 구하기
		
		final int BLOCK= 5;
		int startPage= ((page-1)/BLOCK*BLOCK)+1;
		int endPage= ((page-1)/BLOCK*BLOCK)+BLOCK;
		
		if(endPage>totalPage)
			endPage= totalPage;
		
		// 1 2 3 4 5
		for(int i=startPage; i<=endPage; i++)
		{
			System.out.print(i + "\t");
		}
		System.out.println("\n");
		
		List<BooksVO> list= dao.booksListData(page);
		
		for(BooksVO vo:list)
		{
			System.out.println(vo.getNo() + "." + vo.getTitle());
		}
	}
}

페이지 입력: 
1
1	2	3	4	5	

걸린 시간: 68
1.시작하세요! Final Cut Pro 10.6
2.생활코딩 머신러닝 with 파이썬 텐서플로(실습편)
3.만들면서 배우는 클린 아키텍처
4.머신러닝 시스템 디자인 패턴
5.모든 기획자와 디자이너가 알아야 할 사람에 대한 100가지 사실(개정판)
6.파이썬 데이터 클리닝 쿡북
7.따라하며 배우는 도커와 CI 환경
8.기획자의 글쓰기
9.세상 모든 디자인, 미리캔버스 하나로!
10.메타버스로 가는 티켓, 게더타운의 모든 것

 

 

시퀀스

drop table student;
drop view student_view;
drop sequence stu_hakbun_seq;

create table student(
    hakbun NUMBER,
    name VARCHAR2(34) CONSTRAINT std_name_nn NOT NULL,
    kor NUMBER(3),
    eng NUMBER(3),
    math NUMBER(3),
    CONSTRAINT std_hak_pk PRIMARY KEY(hakbun),
    CONSTRAINT std_kor_ck CHECK(kor>=0),
    CONSTRAINT std_math_ck CHECK(math>=0),   
    CONSTRAINT std_eng_ck CHECK(eng>=0)
);

-- hakbun -> 자동 증가 번호
create sequence std_hakbun_seq
    start with 1
    increment by 1
    nocache
    nocycle;

-- 총점, 평균, 순위
create or replace view student_view
as
select hakbun, name, kor, eng, math, (kor+eng+math) total,
        round((kor+eng+math)/3,2) avg,
        rank() over(order by(kor+math+eng) desc) rank
from student;

-- 데이터 저장
insert into student values(std_hakbun_seq.nextval, '김', 80, 95, 67);
insert into student values(std_hakbun_seq.nextval, '이', 55, 35, 76);
insert into student values(std_hakbun_seq.nextval, '박', 60, 65, 45);
insert into student values(std_hakbun_seq.nextval, '최', 84, 43, 79);
insert into student values(std_hakbun_seq.nextval, '유', 65, 45, 54);

commit;

-- 데이터 확인(시퀀스로 확인)
select * from student_view;

 

-- 현재 시퀀스 수
select std_hakbun_seq.currval "현재 값" from dual;