뷰

보안과 복잡한 쿼리문을 대신할 뷰를 생성하고 조회
뷰를 제거 변경
인라인 뷰를 이용하여 top-n을 구한다.

1)뷰의 개념

뷰(View)는 한마디로 물리적인 테이블을 근거한 논리적인 가상 테이블이라고 정의
기본 테이블에서 파생된 객체로서 기본테이블에 대한 하나의 쿼리문
뷰란 '보다' 라는 의미를 갖고있는 점을 감안해 보면 알 수 있듯이 실제 테이블에 저장된 데이터를 뷰를 통해서 볼 수 있도록 함.

물리적인 구조인 테이블과는 달리 데이터 저장 공간이 없다. 뷰는 단지 쿼리문을 저장하고 있는 객체라고
표현할 수 있다.
뷰의 권한이 없다면

최고관리자로 접속후 권한을 주어야한다.
GRANT CREATE VIEW TO hr;
DCL 데이터 제어

HR로 접속 후
CREATE TABLEEMP_COPY
AS
SELECT * FROM EMPLOYEES;

CREATE VIEW VIEW_EMP01
AS
SELECT EMPLOYEE_ID, FIRST_NAME, SALARY, DEPARTMENT_ID
FROM EMP_COPY
WHERE DEPARTMENT_ID=10;

ㆍ뷰의 사용 목적

직접적인 테이블 접근을 제한하기 위해서 사용된다.(보안성)
복잡한 질의를 쉽게 만들기 위해서 사용된다.(편의성)

ㆍ뷰는 테이블에 대한 제한을 가지고 테이블의 일정한 부분만 보일 수 있는 가상의 테이블
실제 자료를 갖진 않지만, 뷰를 통해 테이블을 관리할 수 있다. 하나의 테이블에 뷰의 개수는 제한이 없다.

뷰 생성

CREATE [OR REPLACE][FORCE|NOFORCE] VIEW view_name
[alias,alias,alias,...]
AS subquery
[WITH CHECK OPTION][WITH READ ONLY]; --해당 뷰를 통해서는 SELECT만 가능하며
--insert/update/delete를 할 수 없다

ㆍ OR REPLACE VIEW

새로운 뷰를 만들 수 있을 뿐만 아니라 기존에 뷰가 존재하더라도 삭제하지 않고 새로운 구조의 뷰로 변경(REPLACE)할 수 있다

ㆍFORCE

기본 테이블의 존재 여부에 상관없이 뷰를 생성

ㆍWITH CHECK OPTION

해당 뷰를 통해서 볼 수 있는 범위 내에서만 UPDATE 또는 INSERT가 가능

1)뷰에 관련된 데이터 딕셔너리

데이터 딕셔너리 USER_VIEWS에 사용자가 생성한 모든 뷰에 대한 정의가 저장되어 있다.
뷰의 이름을 위한 VIEW_NAME이란 칼럼과 뷰를 작성할 때 기술한 서브 쿼리문이 저장되어있는
TEXT칼럼에 대해서 살펴보면
SELECT VIEW_NAME,TEXT
FROM USER_NIEWS;

뷰의 동작 원리

조회/수정/삭제 > 뷰[SEELCT문] > 쿼리실행 > 기본테이블

-사용자가 뷰에 대해서 질의를 하면 USER_VIEWS에서 뷰에 대한 정의를 조회한다.
-기본 테이블에 대한 뷰의 접근 권한을 살핀다.
-뷰에 대한질의를 기본 테이블에 대한 질의로 변환
-기본 테이블에 대한 질의를 통해 데이터를 검색
-검색된 결과를 출력

뷰의 종류( 뷰를 정의하기 위해서 사용되는 기본 테이블의 수에 따라 단순 뷰와 복합 뷰로 나뉜다.)

단순뷰

하나의 테이블로 생성
그룹 함수의 사용이 불가능
DISTINCT 사용이 불가능
DML(INSERT/UPDATE/DELETE) 사용가능

복합뷰

여러개의 테이블로 생성
그룹 함수의 사용이 가능
DISTINCT 사용이 가능
DML(INSERT/UPDATE/DELETE) 사용 불가능

단순 뷰에 대한 데이터 조작
단순 뷰에서는 INSERT/UPDATE/DELETE 문을 사용 가능

단순 뷰를 대상으로 실행한 DML 명령문의 치리 결과는 뷰를 정의할 때 사용한 기본 테이블에 적용된다.
INSERT INTO VIEW_EMP01
VALUES(250,'ANGEL',7000,30);

SELECT * FROM VIEW_EMP01;

단순 뷰를 통해 인서트를 하여
값을 넣은 결과를
위 에서 볼 수 있다.

단순 뷰의 칼럼에 별칭 부여하기
사원번호, 사원명, 급여 , 부서번호로 구성된 뷰를 작성하되 기본 테이블은 EMP01로 하고 칼럼명은
한글화 한다

CREATE OR REPLACE VIEW VIEW_EMP02
AS
SELECT EMPLOYEE_ID 사원번호, FIRST_NAME 사원명,SALARY 급여, DEPARTMENT_ID 부서번호
FROM EMP01;

또는
CREATE OR REPLACE VIEW VIEW_EMP02(사원번호, 사원명, 급여, 부서번호)
AS
SELECT EMPLOYEE_ID, FIRST_NAME, SALARY, DEPARTMENT_ID
FROM EMP01;

이렇게 만들 수도 있다.

칼럼의 별칭을 사용해서 뷰를 생성하면 VIEW_EMP02의 칼럼 이름만 별칭으로 데이터 구조에 반영되고
EMP01 테이블의 칼럼 이름에는 전혀 영향을 주지 못한다. 오류발생

SELECT * FROM VIEW_EMP02
WHERE 부서번호=10;

그룹 함수를 사용한 단순 뷰
부서별 급여 총액과 평균을 구하는 작업을 자주 한다면 이를 뷰로 생성해 놓고 가져다 사용하면 편리하다.

CREATE OR REPLACE VIEW VIEW_SALARY
AS
SELECT DEPARTMENT_ID,SUM(SALARY) AS "SalarySum", TRUNC(AVG(SALARY)) AS "SalaryAvg"
--반드시 별칭이 필요함 ORA-00998: 이 식은 열의 별명과 함께 지정해야 합니다.
FROM EMP01
GROUP BY DEPARTMENT_ID
ORDER BY DEPARTMENT_ID;

SELECT * FROM VIEW_SALARY;

특정 자릿수에서 반올림하는 ROUND 함수

ROUND(대상, 표시할 자릿수)

특정 자릿수에서 잘라내는 TRUNCK 함수
지정한 자릿수 이하를 버린 결과를 구해주는 함수이다 . 두 번째 전달 인자가 생략되면 0으로 간주

나머지 값을 반환하는 MOD함수

--사원번호, 사원이름, 급여, 부서번호, 부서명 조회를 할 수 있는 VIEW_EMP_DEPT 생성을 생성해 주세요.
CREATE OR REPLACE VIEW VIEW_EMP_DEPT
AS
SELECT E.EMPLOYEE_ID 사원번호,E.FIRST_NAME 사원이름,E.SALARY 급여,E.DEPARTMENT_ID 부서번호,D.DEPARTMENT_NAME 부서명
FROM EMPLOYEES E INNER JOIN DEPARTMENTS D
ON E.DEPARTMENT_ID = D.DEPARTMENT_ID
ORDER BY E.DEPARTMENT_ID DESC;

ㆍ단순 뷰에 DML 명령어로 조작 불가능한 경우
-뷰 정의에 포함되지 않은 칼럼 중에 기본테이블의 칼럼이 NOT NULL 제약 조건이 지정되어 있는
경우 INSERT 문이 사용 불가능함.
-SALARY*12와 같이 산술표현식으로 정의된 가상 칼럼이 뷰에 정의되면 INSERT나 UPDATE가 불가능하다.
-DISTINCT를 포함한 경우에도 DML 명령을 사용할 수 없다.
-그룹 함수나 GROUP BY 절을 포함한 경우에도 DML 명령을 사용할 수 없다.

복합뷰
두 개 이상의 기본 테이블에 의해 정의한 뷰
--위 예제에서 ON 대신 USING으로 변경시 공통 컬럼에 대해 별칭을 명시하지 않고 작성
CREATE OR REPLACE VIEW VIEW_EMP_DEPT
AS
SELECT E.EMPLOYEE_ID ,E.FIRST_NAME ,E.SALARY ,DEPARTMENT_ID ,D.DEPARTMENT_NAME
FROM EMPLOYEES E INNER JOIN DEPARTMENTS D
USING(DEPARTMENT_ID)
ORDER BY DEPARTMENT_ID DESC;

/
CREATE OR REPLACE VIEW VIEW_EMP_DEPT
AS
SELECT EMPLOYEE_ID 사원번호,FIRST_NAME 사원이름,SALARY 급여,DEPARTMENT_ID 부서번호,DEPARTMENTS.DEPARTMENT_NAME 부서명
FROM EMPLOYEES INNER JOIN DEPARTMENTS
USING (DEPARTMENT_ID)
ORDER BY DEPARTMENT_ID DESC;
/

-- 뷰 실행
SELECT * FROM VIEW_EMP_DEPT;

뷰 삭제

뷰는 실체가 없는 가상 테이블이기 때문에 뷰를 삭제한다는 것은 user_views 데이터 딕셔너리에 저장되어 있는 뷰의 정의를 삭제하는 것을 의미한다.

뷰 수정을 위한 or replace 옵션

create view 대신 create or replace view를 사용하면 존재하지 않은 뷰이면 새로운 뷰를 생성하고
기존에 존재한 뷰이면 그 내용을 변경한다.

스칼라 서브 쿼리

:스칼라 서브 쿼리는 SELECT 절에 오는 서브 쿼리로 한번에 결과를 1행씩 반환한다.
[형식]
SELECT 컬럼,(SELECT 컬럼 FROM 내부테이블 WHERE 내부테이블.컬럼=외부테이블.컬럼)
FROM 외부테이블;

CREATE OR REPLACE VIEW VIEW_STUDENT
AS
SELECT SD_NUM 학번,SD_NAME 학생명,SU.S_NAME 학과명
FROM subject sb INNER JOIN student st
ON sb.s_num = st.s_num;

SELECT * FROM VIEW_STUDENT;
DESC VIEW_STUDENT;

CREATE OR REPLACE VIEW view_student
AS
SELECT sd_num 학번, sd_name 학생명, (SELECT s_name FROM subject sb where sb.s_num = st.s_num) 학과명
from student st;

select 학번, 학생명, 학과명 from view_student;

기본 테이블 없이 뷰를 생성하기 위한 FORCE옵션

기본 테이블이 존재하지 않더라도 뷰를 생성하려면 FORCE 옵션을 추가해야 한다.
CREATE OR REPLACE FORCE VIEW VIEW_NOTABLE
AS
SELECT EMPLOYEE_ID,FIRST_NAME,DEPARTMENT_ID
FROM EMP15
WHERE EMPLOYEE_ID=10;

뷰가 생성은 되지만 오류메세지와 같이 뜸.

WITH CEHCK OPTION

옵션은 뷰 생성시 조건으로 지정한 칼럼 값을 변경하지 못하도록 하는 것이다.

CREATE OR REPLACE VIEW VIEW_CHK
AS
SELECT EMPLOYEE_ID,FIRST_NAME,SALARY, DEPARTMENT_ID
FROM EMP01
WHERE DEPARTMENT_ID=20 WITH CHECK OPTION;

SELECT* FROM VIEW_CHK;

UPDATE VIEW_CHK
SET DEPARTMENT_ID = 10
WHERE SALARY >= 5000;

WITH READ ONLY

뷰를 통해서는 기본 테이블의 어떤 컬럼에 대해서도 내용을 절대 변경할 수 없도록 하는 것이다.

CREATE OR REPLACE VIEW VIEW_READ
AS
SELECT EMPLOYEE_ID, FIRST_NAME, SALARY, DEPARTMENT_ID
FROM EMP01
WHERE DEPARTMENT_ID = 30 WITH READ ONLY;

UPDATE VIEW_READ
SET DEPARTMENT_ID=1000; --오류발생

-WITH CHECK OPTION- 조건에 사용한 컬럼의 값을 수정하지 못하게 하는 것
-WITH READ ONLY-기본 테이블의 모두를 수정하지 못하게 하는것

---

eclips sql develope 데이터베이스 연동

ResultSet

SELECT문을 사용한 질의 성공 시 Result Set 반환
SQL질의에 의해 생성된 테이블을 담고 있으며 커서(cursor)로 특정 행에 대한 참조 조작

1)Driver 등록

class.forname()
2)public static Connection getConnection(String url,String user, String password) throws SQLException

2)DBMS 연결

3)Statement 생성
Statement stmt = conn.createStatement();

4)SQL 전송,결과 받기

5)닫기 (인스턴스 얻었던 역순)

rs.close() // ResultSet 사용한 경우 반환 처리
stmt.close();
conn.close();
*반드시 SQLException 처리해야함

에러가 날 경우

자동 리소스 닫기

-try -with - resources
예외 발생 여부와 상관없음
try-with-resources 문장은 문장의 끝에서 리소스들이 자동으로 닫혀지게 한다.
즉 사용했던 리소스 객체의 close()메소드 호출해 리소스 닫는다
try-with-resources 문장은 Java SE 7 버전부터 추가되었다.
리소스 객체
각종 입출력 스트림 등
java.lang.AutoClosealbe 인터페이스 구현하고 있어야함.
try(리소스 자료형1 참조변수 = new 리소스자료형1();
리소스 자료형2 참조변수 = new 리소스자료형2()){
...
}catch(예외타입 변수){
...
}

if문을 줌으로써 서비스네임을 자유롭게 사용할수 있도록 한다.

eclips로 sql develope db연동해서 검색하기

package exam_jdbc;

import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;

public class BookSelectTest {
	public static void main(String[] args) {
	/*	Connection con = null;
		Statement stmt = null;
		ResultSet rs = null;
		try {
			con=ConnectDatabase.makeConnection("javauser","java1234");
			stmt = con.createStatement();
			StringBuffer sql = new StringBuffer();
			sql.append("select book_id, title, publisher, year, price "); // 끝에 공백 중요
			sql.append("from books ");
			rs = stmt.executeQuery(sql.toString());
			System.out.println("**** BOOKS 테이블 정보 출력 ****\n");
			while(rs.next()) {
				System.out.print(rs.getInt("book_id") + "\t");
				System.out.print(rs.getString("title") + "\t");
				System.out.print(rs.getString("publisher") + "\t");
				System.out.print(rs.getString("year") + "\t");
				System.out.println(rs.getInt("price"));
			}
		}catch(SQLException s) {
			System.err.println("[쿼리문 ERROR] \n" + s.getMessage());
			//s.printStackTrace(); // 디버깅을 위해 작성
		}finally {
			try {
				if(rs != null) rs.close();
				if(stmt != null) stmt.close();
				if(con != null) con.close();
			}catch(SQLException sqle) {
				System.out.println("CLOSE ERROR");
			}
		}*/
		StringBuffer sql = new StringBuffer();
		sql.append("select book_id, title, publisher, year, price "); // 끝에 공백 중요
		sql.append("from books ");
		try(Connection con = ConnectDatabase.makeConnection("javauser", "java1234");
			Statement stmt = con.createStatement();
			ResultSet rs = stmt.executeQuery(sql.toString());){
			System.out.println("**** BOOKS 테이블 정보 출력 ****\n");
			while(rs.next()) {
				System.out.print(rs.getInt("book_id") + "\t");
				System.out.print(rs.getString("title") + "\t");
				System.out.print(rs.getString("publisher") + "\t");
				System.out.print(rs.getString("year") + "\t");
				System.out.println(rs.getInt("price"));
			}
		}catch(SQLException s) {
			System.err.println("[쿼리문 ERROR] \n" + s.getMessage());
			//s.printStackTrace(); // 디버깅을 위해 작성
		}
	}

}

eclips로 sql develope db연동해서 insert로 데이터 삽입하기

package exam_jdbc;



import java.sql.Connection;

import java.sql.SQLException;

import java.sql.Statement;



public class BooksInsertTest {

//입력수정삭제는 resultset이없음

	public static void main(String[] args) {

		Connection conn = null;

		Statement stmt = null;

		try {

			conn = ConnectDatabase.makeConnection("javauser","java1234");

			stmt = conn.createStatement();

			

			StringBuffer sb = new StringBuffer();

			sb.append("INSERT INTO Books (book_id, title, publisher, year, price) " );

			sb.append("VALUES (books_seq.nextval, '이것이 우분투 리눅수다.', '한빛미디어', '2020', 32000)");

			

			int insertCount = stmt.executeUpdate(sb.toString()); // 쿼리무 실행하여 적용된 행의 반환

			

			if ( insertCount ==1) { //입력이 정상적으로 완료되면 반환값 1

				System.out.println("레코드 추가 성공");

		   }else {

			   System.out.println("레코드 추가 실패");

		   }

	}catch(SQLException e) {

		System.err.println("[쿼리문 ERROR] \n" + e.getMessage());

	}finally{

		try {

			if(stmt != null) stmt.close();

			if(conn != null) conn.close();

		}catch(Exception en) {

			en.printStackTrace();

		}

	}



}

}

레코드가 추가된 것을 볼 수 있다.