Materialized View (MVIEW) - 구체화된 뷰

원본 테이블에 데이터가 10억건이고 View로 가져오는 내용이 1억건일 경우 일반적으로는 View는 원본 테이블에 가서 데이터를 가져온 후 사용자에게 반환하기 때문에 만약 사용자가 4명일 경우 원본테이블에 접근을 4번 발생시켜야 하기에 부하도 많이 발생할 수 있다.

Mview를 사용하면 사용자가 요청하는 데이터를 가지고 있다가 요청이 들어오면 즉시 사요앚에게 보내는 형태로 처리. 그래서 View는 데이터가 없기 때문에 실체가 없는 테이블이라고하며 Mview를 데이터를 가지고 있기 때문에 "실체화된 View"라고 한다. 사용자가 많고 데이터가 많을수록 Mview를 사용하는 것이 더 효율적이고 성능도 좋다.

Mview를 사용하기 위해서는 Query REWRITE라는 권한과 CREATE MARTERIALIZED VIEW 권한이 필요하다.
GRANT QUERY REWRITE TO HR;
GRANT CREATE MATERIALIZED VIEW TO HR;

Mview 실행 방법

CREATE MATERIALIZED VIEW M_EMP
BUILD IMMEDIATE -- 서브쿼리 부분을 수행해서 데이터를 가져오라는 의미
REFRESH
ON DEMAND -- 원본테이블 변경시 동기화 여부 결정.사용자가 수동으로 동기화명령을 수행해서 설정.
--ON COMMIT 옵션은 원본 테이블에 데이터 변경 후 COMMIT이 발생하면 자동으로 동기화시키라는
의미.
COMPLETE -- Mview 내의 데이터 전체가 원본 테이블과 동기화 되는 방법,
ENABLE QUERY REWRITE
AS
SELECT EMPLOYEE_ID, FIRST_NAME, SALARY FROM EMP01;
현재 Mview를 생성시 ON COMMIT를 설정하면 원본 테이블에서 데이터가 변경된 후 COMMIT이 수행되면
즉시 동기화를 할 수 있다. 이 옵션은 대량의 데이터가 변경될 경우 동기화 때문에 아주 큰 부하를 유발할 수 있어서 권장하는 부분이 아님.

VIEW는 데이터가 없어서 매번 실행될때마다 데이터를 가져와야 하지만
MVIEW는 데이터가 있어서 가지고 있는 데이터를 실행시키는 것이다.

원본데이터와 동기화

BEGIN
DBMS_MVIEW.REFRESH('M_EMP');
END;
/

원본테이블과 MVIEW 의 데이터를 동기화시키는것

위 코드를 실행하면 원본 테이블과 MVIEW테이블의 데이터가 일치화된다.

MVIEW를 조회하려면

--Mview 삭제하기

drop materialized view m_emp;

사용자 계정 권한 주는 코드 정리
HR - 서비스 이름 : XE(CDB) = > SYS(XE) 접속이름 : CDB_SYS
JAVAUSER - 서비스이름 : XEPDB1(PDB) = > SYS(XEPDB1) 접속이름 : PDB_SYS

사용자권한을 주는건 최고 권한자인 sys 만 가능함

사용자 계정 생성
CREATE USER 아이디 IDENTIFIED BY 비밀번호

비밀번호 변경 시
ALTER USER 아이디 DIENTIFIED BY 비밀번호

사용자 권한 부여
GRANT CREATE SESSION TO 아이디;

접속권한, 생성권한 부여
GRANT CONNECT , RESOURCE TO 아이디;

USERS에 대해서 권한을 한정하지 않고 주기
ALTER USER 아이디
DEFAULT TABLESPACE USERS QUOTA UNLIMITED ON USERS;

사용자에게 권한 부여 (VIEW)
GRANT CREATE VIEW TO 아이디;

PL/SQL

1)PL/SQL은 Oracle's Procedural Language extension to SQL의 약자이다

SQL문장에서 변수정의, 조건처리(IF), 반복처리(LOOP,WHILE,FOR)등을 지원한다.
다른 프로그래밍 언어와 다른 점은 PL/SQL은 DB에 직접 탑재되어 컴파일되고 실행되어 성능 면에서 우수하고, DB관련 처리를 할 떄 수많은 기능을 제공한다. 일반 프로그래밍 언어적인 요소를 거의 다 가지고 있어서 실무에서 요구되는 절차적인 데이터 처리를 다 할수 있음. 특히 SQL과 연동되어서
막강한 기능을 구현할 수 있다.

ㆍPL/SQL은 SQL에 없는 기능을 제공한다.

-변수 선언을 할 수 있다,
-비교 처리를 할 수 있다.
-반복 처리를 할 수 있다.
PL/SQL 소스 프로그램의 기본 단위를 블록(BLOCK)이라고 하는데, 블록은 선언부,실행부,예외처리부로 구성된다. 이 블록은 다시 이름이 없는 블록과 이름이 있는 블록으로 구분할 수 있는데 전자에 속하는 것이 익명블록이며, 함수 , 프로시저, 패키지 등이 후자에 속한다.

장점

프로그램의 모듈화

특정 프로그램을 Procedure, function, package 등의 프로그램으로 만들어 여러 응용 프로그램을 만들 시에 공통으로 이용 및 관리 할 수 있다.

관리의 용이

네트워크 입출력 감소

PL/SQL문을 사용하게 되면 모든 SQL문 및 논리적 프로그램을 프로시저 등으로 작성하며 클라이언트는 네트워크 연결을 통한 SQL문 및 프로그램 언어를 보내는 것이 아니라 프로시저 등을 실행하는 구문만 데이터베이스에 보낼 수 있어 네트워크 입출력을 현격하게 줄일 수 있다.

DECLARE
선언부(DECLARE SECTION)
- 변수나 상수를 선언
BEGIN
실행부(EXECUTABLE SECTION)
SQL 문
제어문, 반복문
커서
EXCEPTION
예외 처리부(EXCEPTION SECTION)
END;
/

-PL/SQL 블록 내에서는 한 문장이 종료할 때 마다 세미콜론(;)을 사용한다.

-END 뒤에 ;를 사용하여 하나의 블록이 끝났다는 것을 명시한다.

-DECLARE나 BEGIN이라는 키워드로 PL/SQL 블록이 시작하는 것을 알 수 있다.

-단일 행 주석은 --이고 여러 행 주석/* */이다.

-PL/SQL 블록은 행에 /가 있으면 종료된다.

모든 문장의 종결 기호는 세미콜론으로 명시해야한다. 대입 연산자로는 :=을 사용한다. 대입 연산자는 변수의 선언 시 및 변수의 대입에 이용된다. SELECT문에 의해 추출되는 DATA는 INTO절의 변수에 저장해서 처리한다.

대입 연산자 자바와 오라클 차이점

INT A = 값 NO NUMBER
자바는 자료형 변수 = 값; 이지만 오라클에서는 변수 자료형 := 값; 이다.
자바의 비교연산자 == 오라클의 비교연산자 =

보기 설정에 DBMS출력 클릭후 아래 DBMS창에 +버튼 접속 클릭 후

그리고

BEGIN
DBMS_OUTPUT.PUT_LINE('Hello World!');
DBMS_OUTPUT.PUT_LINE('Hello World!');
END;
코드 실행시

이렇게 값이 나오는 것을 확인할 수 있다.

DECLARE
VEMPLOYEE_ID NUMBER(6);
VFIRST_NAME VARCHAR2(20);
BEGIN
VEMPLOYEE_ID := 105;
VFIRST_NAME := 'David';

DBMS_OUTPUT.PUT_LINE('사번 / 이름 ');
DBMS_OUTPUT.PUT_LINE('------------------------');
DBMS_OUTPUT.PUT_LINE(VEMPLOYEE_ID || '  /  ' || VFIRST_NAME);

END;
/

1) 변수

identifier [CONSTANT] datatype [NOT NULL] {:=| DEFAULT expression];
identifier 변수의 이름
CONSTANT 변수의 값을 변경할 수 없도록 제약
datatype 자료형(데이터 타입)을 기술
-변수 데이터 타입: SQL 타입과 PL/SQL 타입
-PL/SQL 데이터 타입: BOLLEAN, BINARY_INTEGER
NOT NULL 값을 반드시 포함하도록 하기 위해 변수를 제약
expression Literal, 다른변수, 연산자나 함수를 포함하는 표현식

PL/SQL에서 변수를 선언할 떄 사용되는 자료형은 SQL에서 사용하던 자료형과 거의 유사하다.
숫자를 저장하려면 NUMBER를 사용하고 문자를 저장하려면 VARCHAR2를 사용해서 선언한다.
상수는 다음과 같이 선언하면 된다. 변수와는 달리 한 번 값을 할당하면 변하지 않는다.
상수명 CONSTANT 데이터타입 := 상수값;

2) 변수의 값 대입

변수의 값을 지정하거나 재지정 하기 위해 pl/sql의 지정문자를 사용한다
대입(지정) 연산자(:=)는 좌측에 존재하는 변수에 우측 값을 대입한다는 의미이다
identifier := expression;

DECLARE
val_num NUMBER;
BEGIN
val_num := 100;
DBMS_OUTPUT.PUT_LINE(val_num);
END;
/

DECLARE
num NUMBER := 246060;
BEGIN
DBMS_OUTPUT.PUT_LINE('num = '|| TO_CHAR(num));
END;
/

DECLARE
VEMPLOYEE_ID NUMBER(6);
VFIRST_NAME VARCHAR(20);
BEGIN
VEMPLOYEE_ID := 105;
VFIRST_NAME := 'David';

DBMS_OUTPUT.PUT_LINE('사번 / 이름');
DBMS_OUTPUT.PUT_LINE('-----------------');
DBMS_OUTPUT.PUT_LINE(VEMPLOYEE_ID || ' / ' VFIRST_NAME);

END
/

3) 스칼라 변수 / 레퍼런스 변수

PL/SQL에서 변수를 선언하기 위해 사용할 수 있는 데이터형은 크게 스칼라와 래퍼런스로 나눈다.

ㆍ스칼라

SQL에서의 자료형 지정과 거의 동일하다

VEMPLOYEE_ID NUMBER(6);
VFIRST_NAME VARCHAR2(20);

ㆍ레퍼런스: %TYPE 속성과 %ROWTYPE 속성 사용한다

이전에 선언된 다른 변수 또는 데이터베이스의 칼럼에 맞추어 변수를 선언하기 위해 %TYPE 속성을 사용한다.

VEMPLOYEE_ID EMPLOYEES.EMPLOYEE_ID%TYPE; // %TYPE은 컬럼의 자료형을 참고하겠다
VFIRST_NAME EMPLOYEES.FIRST%TYPE; // VARCHAR2(20)bYTE를 참조하겠다라는 뜻

%TYPE 속성을 사용하여 선언한 VEMPLOYEE_ID 변수는 EMPLOYEES 테이블의 EMPLOYEE_ID 칼럼의 자료형과 크기를 그대로 참조해서 정의한다.
%ROWTYPE은 로우 단위로 참조한다

VEMPLOYEES EMPLOYEES% ROWTYPE;

%ROWTYPE을 사용 시 장점은 특정 테이블의 칼럼의 개수와 데이터 형식을 모르더라도 지정 할 수 있다. SELECT 문장으로 로우를 검색할 떄 유리하다.
//레코드 하나만 참조

4) PL/SQL에서 SQL문장

PL/SQL의 SELECT 문은 INTO절이 필요한데, INTO절에는 데이터를 저장할 변수를 기술한다.
SELECT 절에 있는 칼럼은 INTO절에 있는 변수와 1대1대응을 하기에 개수와 데이트어의 형, 길이가 일치하여야 한다.
SELECT select list
INTO{variable_name1[.variable_name2,..] | record_name}
FROM table_name
WHERE condition;
구문 설명

VEMPLOYEE_ID, VFIRST_NAME 변수는 칼럼(EMPLOYEE_ID,FIRST_NAME)과 동일한 데이터형을 갖도록 하기 위해서 %TYPE을 사용한다. INTO 절의 변수는 SELECT에서 기술한 칼럼의 데이터형 뿐만 아니라 칼럼의 수와도 일치해야 한다.

DECLARE
        --%TYPE 속성으로 칼럼 단위로 데이터를 저장할 수 있는 레퍼런스 변수 선언
        vemployee_id employees.employee_id%TYPE;
        vfirst_name employees.first_name%TYPE;
BEGIN
        DBMS_OUTPUT.PUT_LINE('사번 / 이름');
        DBMS_OUTPUT.PUT_LINE('------------');
        --SELECT 문을 수행한 결과 값이 INTO 뒤에 기술한 변수에 저장된다.
        SELECT employee_id, first_name INTO vemployee_id, vfirst_name
        FROM employees
        WHERE first_name = 'Susan';
        
        --레퍼런스 변수에 저장된 값을 출력한다.
        DBMS_OUTPUT.PUT_LINE(vemployee_id || ' / ' || vfirst_name);
END;
/

DECLARE은 선언부이다.

BEGIN END 부분은 중괄호라고 생각하면 된다.

-- 전체 레코드를 참조하기 위해서는 %rowtype으로 선언
DECLARE
    --레코드로 변수 선언
    vemployees employees%rowtype;
BEGIN
    --Lisa사원의 정보를 레코드 변수에 저장
    SELECT *
    INTO vemployees
    FROM employees
    WHERE first_name = 'Lisa';
    
    --레코드 변수에 저장된 사원 정보를 출력
    DBMS_OUTPUT.PUT_LINE('사원번호 : ' || TO_CHAR(vemployees.employee_id));
    DBMS_OUTPUT.PUT_LINE('이   름 : ' || vemployees.first_name);
    DBMS_OUTPUT.PUT_LINE('급   여 : ' || vemployees.salary);
    DBMS_OUTPUT.PUT_LINE('입사일자 : ' || TO_CHAR(vemployees.hire_date,'YYYY-MM-DD'));
END;
/

--<예제> employees 테이블에 등록된 총사원의 수와 급여의 합, 급여의 평균을 변수에 대입하여 출력하여 보자.

DECLARE 
    vcnt number;
    vsum number;
    vavg number(10,2);
BEGIN
    SELECT COUNT(*), SUM(salary), AVG(salary) into vcnt, vsum, vavg
    FROM employees;
    
    DBMS_OUTPUT.PUT_LINE('총사원의 수 : ' || vcnt);
    DBMS_OUTPUT.PUT_LINE('급여의 합 : ' || vsum);
    DBMS_OUTPUT.PUT_LINE('급여의 평균 : ' || vavg);
END;
/

--<예제> JACK 사원의 직무,급여,입사일자,부서명을 변수에 대입하여 출력해보자

DECLARE 
        vjob_id employees.job_id%type;
        vsalary employees.salary%type;
        vhire_date employees.hire_date%type;
        vdepartment_id departments.department_name%type;
BEGIN
    select e.job_id,e.salary,e.hire_date,d.department_name into vjob_id,vsalary,vhire_date,vdepartment_id
    from employees e inner join departments d
    on e.department_id = d.department_id
    where first_name ='Jack';

    DBMS_OUTPUT.PUT_LINE('직무 : ' || vjob_id);
    DBMS_OUTPUT.PUT_LINE('급여 : ' || vsalary);
    DBMS_OUTPUT.PUT_LINE('입사일자 : ' || vhire_date);
    DBMS_OUTPUT.PUT_LINE('부서명 : ' || vdepartment_id);
END;
/

--<예제> 사원 테이블(EMPLOYEE01)에서 사원번호가 제일 큰 사원을 찾아낸 뒤,
-- 이 번호 +3번으로 아래의 사원을 사원테이블에 신규 입력하는 PL/SQL을 만들어 보자.
DECLARE 
    max_employee_id employee01.employee_id%type;
BEGIN 
    select MAX(employee_id) INTO max_employee_id FROM employees01;
    insert into employees01( employee_id, first_name, last_name, email, salary, hire_date,
                            manager_id, job_id, department_id)
    VALUES( max_employee_id + 3, 'Olivia','Gee','Spring',2800,SYSDATE,100,'PR_REP',20);
    COMMIT;
END;
/
    
DROP TABLE EMPLOYEES01;

CREATE TABLE EMPLOYEES01
AS
SELECT * FROM EMPLOYEES;

SELECT * FROM EMPLOYEES01;
DESC EMPLOYEES01;

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

여기서 E.HIRE_DATE에 별칭을 꼭 주어야 한다.

---

자바 SQL 데이터 연동

--JAVA 연동하여 실행할 뷰
--eclips로 사원번호, 사원명, 급여, 입사일(2001.12.02형태로), 부서명을 조회
--뷰명: VIEW_EMP_DEPT02

package exam_jdbc;

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

/* 사원번호, 사원명, 급여, 입사일(2001.12.02형태로), 부서명을 출력해 주세요.*/
public class EmployeeSelectTest {
	public static void main(String[] args) {
		/*StringBuffer sql = new StringBuffer();
		sql.append("select employee_id, first_name, to_char(hire_date,'YYYY.MM.DD')  ,department_name ");
		sql.append("from employees e inner join departments d ");
		sql.append("on e.department_id = d.department_id ");
		sql.append("order by e.employee_id desc " );
		
		try(Connection con = ConnectDatabase.makeConnection("hr","hr1234");
			Statement stmt = con.createStatement();
			ResultSet rs = stmt.executeQuery(sql.toString());){
			System.out.println("사원번호, 사원명, 급여, 입사일(2001.12.02형태로), 부서명을 출력.");
			while(rs.next()){
				System.out.print(rs.getInt("employee_id") + "\t");
				System.out.print(rs.getString("first_name") + "\t");
				System.out.print(rs.getString("to_char(hire_date,'YYYY.MM.DD')") + "\t");
				System.out.println(rs.getString("department_name"));
				
			}
			
		}catch(SQLException s) {
			System.err.println("[쿼리문] ERROR \n" + s.getMessage());
			s.printStackTrace();
		}*/
		
		
		Connection con = null;
		Statement stmt = null;
		ResultSet rs = null;
		int employee_id;
		int salary;
		String first_name, hire_date, department_name;
		
		try {
				con=ConnectDatabase.makeConnection("hr", "hr1234");
				stmt = con.createStatement();
				StringBuffer sql = new StringBuffer();
				/*sql.append("select e.employee_id, e.first_name, to_char(e.hire_date,'YYYY.MM.DD') hire_date ,salary,d.department_name ");
				sql.append("from employees e inner join departments d ");
				sql.append("on e.department_id = d.department_id ");
				sql.append("order by e.employee_id  " );
				rs = stmt.executeQuery(sql.toString());
				*/
				
				/* 뷰 작성 */
				sql.append("SELECT EMPLOYEE_ID, FIRST_NAME, SALARY, HIRE_DATE, DEPARTMENT_NAME ");
				sql.append("FROM VIEW_EMP_DEPT02");
				
				rs=stmt.executeQuery(sql.toString());
				
				System.out.println("***** EMPLOYEES 테이블 데이터 출력 *****\n");
				System.out.printf("%s\t%s\t%6s\t%8s\t%7s\n","사원번호", "사원이름","급여", "입사일", "부서명");
				
				while(rs.next()) {
					employee_id = rs.getInt("employee_id");
					first_name = rs.getString("first_name");
					salary = rs.getInt("salary");
					hire_date = rs.getString("hire_date");
					department_name = rs.getString("department_name");
					System.out.printf("%-7d %-11s %-6d %s\n",
							employee_id,first_name,salary,hire_date,department_name);
				
				}
	}catch(SQLException s) {
		System.out.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");
			sqle.printStackTrace();
			
		}
		
	}

	}
}

SQL을 위한 객체 생성

이제 반환된 Connection 객체를 이용해서 SQL문을 실행하고 그 결과를 반환 받을 수 있는 Statement,PreparedStatement, CallableStatement
객체를 생성하는 단계이다.

인터페이스인 Statement,PreparedStatement,CallableStatement는 질의 문장인 SQL문을 추상화 시킨 인터페이스다. 이들은 Connection 객체인 메소드 createStatement()를 호출하여 statement객체를 얻어온다.

싱클톤(Singleton)

ㆍ애플리케이션 전체에서 단 한개의 객체만 생성해서 사용하고 싶다면 싱글톤 패턴을 적용할 수 있다.

ㆍ싱글톤 얻는 방법

클래스 변수 1 = 클래스.getInstance();
클래스 변수 2 = 클래스.getInstance();

ㆍ싱글톤을 만드는 방법

-외부에서 new 연산자로 생성자를 호출할 수 없도록 막기
private 접근제어자를 생성자 앞에 붙임

-클래스 자신의 타입으로 정적 필드 선언
ㆍ자신의 객체를 생성해 초기화
ㆍprivate 접근제어자 붙여 외부에서 필드 값 변경 불가하도록

(싱글톤 패턴이 제공하는 정적 메서드를 통해
간접적으로 객체를 얻을 수 있다.)
-외부에서호출할 수 있는 정적메소드인 getInstance() 선언
ㆍ정적 필드에서 참조하고 있는 자신의 객체 리턴