20230823 (수)

오늘은 Oracle SQL 마지막날~

 

<금일 교육 내용>

사전, 인덱스,시퀀스, 뷰

 

<주요 일정>

8/18~8/29

- 오라클

- JDBC

- MySQL

 

8/30~9/5

- JSP&Servlet

 

<복습>

- Transaction : 하나의 논리적인 실행단위로 처리 (SQL문 -> Java 코드 실행 -> SQL문)

- Commit: Transaction 이후에 반영하는 것.

- Rollback : Transaction 이전 상태로 되돌리는 것.

- Checkpoint : 라벨을 붙여서 Transaction -> Commit  사이에서 잘 반영이 됐는지 체크해주는 기능

자바코드를 이용하면 체크포인트를 거의 사용하지 않음.

- DDL : CRUD 연산에 이용되는 SQL은 DML이라고 부르고(select, insert, update, delete)(Data의 내용을 핸들링함)(Transaction의 관리대상),

객체 자체(Table, User, View, Index 등)를 핸들링 하는 것을 DDL이라고 부름.

Data Definition Language의 약어임.

가장 대표적인 예시 : create 객체종류 대상이름, alter 객체종류 대상이름, drop 객체종류 대상이름

alter user scott identified by ~~; => 비밀번호 변경 예시

- 컬럼의 데이터 타입 : 제품별로 데이터 타입이 다르므로 따로 정리하는 것이 좋음.

* NUMBER(w,d) -> 숫자 길이에 제한이 없음. 제한하면 13자리

* CHAR() : 필수정보. 고정길이를 가짐. 주로 우리가 다루는 데이터의 길이가 정해진 경우에 사용한다.

* VARCHAR2() : 필수정보. 길이에 제한이 없음. 조금이라도 길이가 길어질 수 있는 것들은 모두 varchar2()를 사용한다. 최대 값은 2000이며, byte 단위이다. 한글을 저장한다고 가정한다면, 한글은 한자당 3byte이므로 700자밖에 넣을 수 없다.

* DATE() : 날짜 정보를 넣을 때 사용.

* LONG() : 아주 긴 데이터를 넣을 때 사용.

* LOB() : 아주 긴 데이터를 넣을 때 사용.

- 제약조건 ☆☆☆

* NOT NULL : 생략할 수 없음. 필수 데이터.

* NULL : 비어있는 데이터, Default 값

* UNIQUE : 중복이 안됨. NULL 허용. Key값에 반드시 들어감. Key는 검색작업이 빈번하기 때문에 검색 속도를 낮추기 위해서 INDEX가 자동으로 구축됨.

* PRIMARY KEY(컬럼1, 컬럼2, ...) : NOT NULL + UNIQUE의 기능이 합쳐짐. INDEX가 자동으로 구축됨. 테이블을 만들 때 반드시 프라이머리키를 정의하는 것이 권장됨.

* FOREIGN KEY : 참조를 설정할 떄 씀. child -> parent. 이때 반드시 참조하는 키는 Parent 테이블에서 UNIQUE 속성이거나, PRIMARY KEY 속성이어야 함. 참조하는 키의 값이 반드시 존재하는 데이터여야만 참조된 키에서 해당 데이터를 사용할 수 있음. ON DELETE SET NULL 등의 기능 추가 가능함.

 

<<본론>>

<데이터 형태와 범위를 정하는 CHECK>

1. CHECK 제약조건

- 테이블을 생성할 때 CHECK 제약조건 설정하기

* true : 성공, false : 실해

* CHECK( 컬럼 IN ('F', 'M')) 이런식으로도 사용 가능

CREATE TABLE TABLE_CHECK(

LOGIN_ID VARCHAR2(20) CONSTRAINT TBLCK_LGNID_PK PRIMARY KEY,

LOGIN_PWD VARCHAR2(20) CONSTRAINT TBLCK_LGNPW_CK CHECK(LENGTH(LOGIN_PWD) > 3),

TEL VARCHAR2(20)

)

- CHECK 제약조건에 맞지 않는 예

INSERT INTO TABLE_CHECK

VALUES('TEST_ID', '123', '010-1234-5678');

- CHECK 제약조건에 맞는 예

INSERT INTO TABLE_CHECK

VALUES('TEST_ID', '1234', '010-1234-5678');

 

- CHECK 제약조건 확인하기

SELECT OWNER, CONSTRAINT_NAME, CONSTRAINT_TYPE, TABLE_NAME

FROM USER_CONSTRAINTS

WHERE TABLE_NAME LIKE 'TABLE_CHECK';

 

<기본 값을 정하는 DEFAULT>

1. DEFAULT 제약조건

- 테이블을 생성할 때 DEFAULT 제약조건 설정하기

CREATE TABLE TABLE_DEFAULT(

LOGIN_ID VARCHAR2(20) CONSTAINT TBLCK2_LGNID_PK PRIMARY KEY,

LOGIN_PWD VARCHAR(20) DEFAULT '1234',

TEL VARCHAR2(20),

REG_DATE DATE DEFAULT SYSDATE,

UPDATE_DATE DATE DEFAULT SYSDATE

);

=> 보통은 default를 날짜에 자주 이용함

REG_DATE DATE DEFAULT SYSDATE

UPDATE_DATE DATE DEFAULT SYSDATE

 

- DEFAULT로 지정한 기본값이 입력되는 INSERT문 확인하기

INSERT INTO TABLE_DEFAULT(LOGIN_ID, LOGIN_PWD,TEL) VALUES('TEST_ID', NULL, '010-1234-5678');

INSERT INTO TABLE_DEFAULT(LOGIN_ID,TEL) VALUES('TEST_ID2', '010-1234-5678');

 

- 디비에서 결국 가장 중요한 것은 PK, FK 이다.

* 1:1

* 1:N

* N:M

=> N:M 관계는 A - 중개테이블 - B 의 구조일때 이뤄질 수 있다.

==> 주로 해시태그, 상품, 카테고리 테이블에서 많이 쓰이는 구조이다.

 

<데이터베이스를 위한 데이터를 저장한 데이터 사전>

1. 데이터 사전이란?

- DDL 작업 : 관리정보 (관리정보할때 생성되는 특수한 테이블 - 데이터 사전)

- 함부로 만지면 안됨.

- 데이터베이스를 구성하고 운영하는 데 필요한 모든 정보를 저장하는 특수한 테이블

- 데이터베이스가 생성되는 시점에 자동으로 만들어짐

- 데이터베이스 메모리, 성능, 사용자, 권한,

 

- 데이터 사전 뷰, 용도에 따라 접두어를 달리함

* USER_XXXX : 현재 데이터베이스에 접속한 사용자가 소유한 객체 정보

* ALL_XXXX : 현재 데이터베이스에 접속한 사용자가 소유한 객체 또는 다른 사용자가 소유한 객체 중 사용허가를 받은 객체, 즉 사용 가능한 모든 객체 정보

* DBA_XXXX : 데이터베이스 관리를 위한 정보 (데이터베이스 관리 권한을 가진 SYSTEM, SYS 사용자만 열람 가능)

* V$_XXXX : 데이터베이스 성능 관련 정보(X$_XXXX)

 

- SCOTT 계정에서 사용 가능한 데이터 사전 살펴보기(DICT 사용)

SELECT * FROM DICT;

=> 약 300개

 

- SCOTT 계정에서 사용 가능한 데이터 사전 살펴보기(DICTIONARY 사용)

SELECT * FROM DICTIONARY;

=> 약 50개

 

2. USER 접두어를 가진 데이터 사전

- 사용자가 소유한 객체 정보가 보관

 

3. ALL_ 접두어를 가진 데이터 사전

- 접속한 계정으로 사용가능한 테이블에 관한 정보

- SCOTT 계정이 사용할 수 있는 객체 정보 살펴보기(ALL_ 접두어 사용)

SELECT OWNER, TABLE_NAME FROM ALL_TABLES;

 

4. USER_TABLE

 

5. DBA_ 접두어를 가진 인덱스

 

<더 빠른 검색을 위한 인덱스>

1. 인덱스란?

- SCOTT 계정이 소유한 인덱스 정보 알아보기(SCOTT 계정일 떄)

select * from USER_IDEXES;

- SCOTT 계정이 소유한 인덱스 컬럼 정보 알아보기(SCOTT 계정일 떄)

select * from user_ind_columns;

- TREE 구조를 자주 사용

* 자식에 대한 참조를 운영

* 새로운 값이 발생했을 때 비교를 통해서 작으면 왼쪽, 크면 오른쪽에 붙인다.

** 맨 오른쪽에 간 값과는 더이상 비교하지 않음. (버림..?)

** 계속해서 값을 비교 비교 비교...

** 알고리즘 수업에서 계속...

 

2. 인덱스 생성

create index 인덱스 이름

on 테이블 이름(열이름1 asc or desc, 열이름1 asc or desc, ...);

튜닝작업

 

- EMP 테이블의 SAL 열에 인덱스를 생성하기

create index idx_emp_sal on emp(sal);

- 생성된 인덱스 살펴보기(user_ind_columns 사용)

select * from user_ind_columns;

 

4. 인덱스 삭제

drop index 인덱스_이름;

 

- 인덱스 삭제하기

drop index idx_emp_sal;

 

- 생성된 인덱스 살펴보기(user_ind_columns 사용)

select * from user_ind_columns;

 

<테이블처럼 사용하는 뷰>

1. 뷰(View) 란?

- 가상 테이블

- 하나 이상의 테이블을 조회하는 select 문을 저장한 객체

- 물리적 데이터를 따로 저장하지 않음

- 복잡한 select문을 간결히 처리하기 위함

- 뷰 : select * from vw_emp20;

- 서브쿼리 : select * from(select...)

 

2. 뷰의 사용 목적

- 편리성 : select 문의 복잡도를 완화

- 보안성 : 테이블의 특정 열을 노출하고 싶지 않을 경우

 

3. 뷰 생성

- 뷰 생성 권한이 없는 경우, 권한 부여 필요(SYS 계정으로 작업)

GRANT CREATE VIEW TO SCOTT;

 

create [or replace] [force | noforce] view 뷰 이름 (열 이름1, 열 이름2, ...)

 

- 뷰 생성하기

create view vm_emp20

as (select empno, ename, job, deptno

from emp

where deptno = 20);

select * from user_views;

 

- 생성한 뷰 조회하기

select * from vw_emp20;

 

4. 인라인 뷰를 사용한 TOP-N SQL문

- 인라인 뷰 inline view

* SQL문에서 일회성으로 만들어서 사용하는 뷰

 

- ROWNUM

* SELECT 문의 결과 행에 순서대로 붙이는 정수

* 가상 컬럼, pseudo column

 

- ROWNUM을 추가로 조회하기

select rownum, e.* from emp e;

select rownum, e.* from emp e order by sal;

==> 오름차순

===> 기대했던 값이 안나옴. 왜? : select로 먼저 리스트를 뽑고 난 뒤에 오름차순 정렬을 했기 때문에.

 

- order by 결과에 rownum 을 붙이면 순위(등수)로 해석 가능

- 인라인 뷰(서브쿼리 사용)

select rownum, e.*

from (select * from emp e order by sal desc) e;

 

- 상위 n개 추출하기 -> top-n 문제

 

- 인라인 뷰로 top-n 추출하기(서브쿼리 사용)

select rownum, e.* from (select * from emp e order by sal desc) e WHERE rownum <=3;

그러나, 다음과 같은 쿼리문은 안됨.

select rownum, e.* from (select * from emp e order by sal desc) e WHERE rownum >3;

select rownum, e.* from (select * from emp e order by sal desc) e WHERE BETWEEN 3 AND 5;

- 페이지네이션을 만들 때 사용됨. : 1페이지는 나올 수 있지만, 이런식으로 하면 2페이지 후부터는 안나옴.

==> 이럴때는 뷰를 만들어서 사용하면 페이지네이션 기능을 사용할 수 있음.

select row_number() over(order by sal desc) seq, e.* from emp e;

 

create view emp_sal_rank

as

select row number() over(order by sal desc) seq, e.* from emp e;

 

SELECT * FROM EMP_SAL_RANK WHERE seq BETWEEN 3 AND 5;

==> 이렇게 하면 2페이지도 뽑아낼 수 있다. 그리고 그 다음 페이지도 가능하다.

SELECT * FROM EMP_SAL_RANK WHERE seq > 5;

==> 5 이후의 숫자도 뽑아낼 수 있다.

 

====> 이 방법은 oracle만의 독특한 운영방식이다. 하지만 mysql로 하면 좀 더 편하게 구간을 뽑아낼 수 있다.

 

만약에 db에서 페이지네이션 처리를 해줘야 한다면

SELECT * FROM EMP_SAL_RANK WHERE seq BETWEEN 4 AND 4+2;

이런식으로 계산식이 들어가서 페이지네이션 처리를 하게 될 것이다.

 

같은 테이블명으로 새로 테이블을 만들면 에러가 남.

그래서 drop하고 다시 만들어야 하는데,

테이블을 일일히 다 못보는 경우가 많으므로

create or replace view emp_sal_rank ~~~

이런식으로 or replace를 안전장치로 넣어줘서

'만약에 이 테이블이 있다면 이 테이블을 다음처럼 바꿔서 재생산 해달라~' 라는 의미로

테이블을 재생산 할 수 있다.

 

<문제>

-- SAL로 내림차순 정렬

-- SEQ, EMPNO, EMPNAME, DEPTNO, DNAME을 가지는 뷰

 

1) 일단, 오름차순과 내림차순 모두 가능하도록 뷰를 생성함.

CREATE OR REPLACE VIEW EMP_DETAIL

AS

SELECT ROW_NUMBER() OVER(ORDER BY SAL DESC) SEQ, E.EMPNO, E.ENAME, E.DEPTNO, D.DNAME

FROM EMP E JOIN DEPT D ON (E.DEPTNO = D.DEPTNO);

 

2) 뷰를 내림차순으로 select 함

SELECT * FROM EMP_DETAIL

ORDER BY SEQ DESC;

 

3) 끝

 

<규칙에 따라 순번을 생성하는 시퀀스>

1. 시퀀스란?

- sequence (객체임.. 테이블, 인덱스, 뷰와 같은 레벨 -> 오라클에만 있는 개념)

- 특정 규칙에 맞는 연속 숫자를 생성하는 객체

- 은행의 대기 순번표와 유사

 

create sequence 시퀀스 이름 //명명관계 : 테이블명_SEQ 이런식으로

[increments by n]

[start with n] //몇번부터 시작할거냐

[maxvalue n | nomaxvalue] //최대값지정, 일반적으로는 max 값을 안줌

[minvalue n | nominvalue] //최소값지정

[cycle | nocycle] //최대값까지 도달한 후 그 값을 넘었을 때 다시 최소값으로 감

[cache n | nocache] //최대값까지 도달한 후 그 값을 넘었을 때  에러가 발생하게끔 함

 

2. 시퀀스 생성

- 실습 준비

create table dept_sequence as select * from dept where 1 <> 1;

 

- 시퀀스 생성하기

CREATE SEQUENCE SEQ_DEPT_SEQUENCE

INCREMENT BY 10

START WITH 10

MAXVALUE 90

MINVALUE 0

NOCYCLE

CACHE 2;

 

SELECT * FROM USER_SEQUENCES;

3. 시퀀스 사용

- 시퀀스이름.CURRVAL

* 마지막으로 생성한 번호를 반환

 

- 시퀀스이름.NEXTVAL

* 다음 번호를 생성

 

- 시퀀스에 생성한 순번을 사용한 INSERT문 실행하기

INSERT INTO DEPT_SEQUENCE(DEPTNO, DNAME, LOC)

VALUES(SEQ_DEPT_SEQUENCE.NEXTVAL, 'DATABASE', 'SEOUL');

 

SELECT * FROM DEPT_SEQUENCE ORDER BY DEPTNO;

 

- 가장 마지막으로 생성된 시퀀스 확인하기

SELECT SEQ_DEPT_SEQUENCE.CURRVAL FROM DUAL;

 

4. 시퀀스의 마지막까지 오면 더이상 발급되지 않음 -> 발급하면 에러 발생

 

5. 시퀀스 수정

-시퀀스 옵션 수정하기

ALTER SEQUENCE SEQ_DEPT_SEQUENCE

INCREMENT BY 3

MAXVALUE 99

CYCLE;

 

SELECT * FROM USER_SEQUENCES;

 

6. 시퀀스 삭제

drop sequence seq_dept_sequence;

 

- STORED PROCEDURE : 함수 만드는 것. 데이터베이스마다 자체적인 프로그램이 있음. (오라클에서는 PLSQL)

데이터베이스 내에서만 돌릴 수 있는 프로그램 랭귀지

주로 DB개발자들이 많이 사용하므로 웹개발자들이 알 필요는 없는 내용들임.

 

<<JDBC>>

<JDBC 프로그래밍>

1. 데이터베이스 준비

- sys 계정 작업

CREATE USER jdbc_ex IDENTIFIED BY jdbc_ex
DEFAULT TABLESPACE USERS
TEMPORARY TABLESPACE TEMP;

GRANT CONNECT, RESOURCE, DBA TO jdbc_ex;

 

1. JDBC Java Database Connectivity

- 데이터베이스와 연결해서 입출력을 지원

- 데이터베이스 관리시스템(DBMS)의 종류와 상관없이 동일하게 사용할 수 있는 클래스와 인터페이스로 구성

2. JDBC

- JDBC 개념

- JDBC 드라이버 안에 구현클래스가 있음.

 

3. ojdbc 드라이버 연결

- C:\sqldeveloper\jdbc\lib\ojdbc11.jar

 

4. JDBC의 핵심 인터페이스/클래스

5. DB연결

- 드라이버 확인

Class.forName("oracle.jdbc.OracleDriaver");

=> 없으면 ClassNotFoundException 발생

- Connection 객체

* 데이터베이스에 연결 세션을 만듦

* Connection conn = DrivaerManager.getConnection("연결문자열", "사용자", "비밀번호);

* 연결문자열

** jdbc:oracle:thin:@localhost:1521/orcl

 

6. Statement

- SQL문 실행 클래스

- Connection 객체를 통해 생성

Statement stmt = conn.createStatement();

- SQL 실행 메서드

* ResultSet executeQuery(SQL문) : select문(=query문) 실행

* int executeUpdate(SQL문) : insert, update, delete문 실행 // int(=영향받은 row 갯수)

 

7. ResultSet (Iterator 패턴)

- 컬럼 값 추출

* getXxxx("컬러명)

- Xxx : 추출하고자 하는 데이터 타입명

- getString(), getInt(), getLong(), getDouble()

 

8. Statement로 Inser문 실행하기

String sql = "INSERT INTO USERS(ID, PASSWORD, NAME, ROLE)" + "VALUES('member2', 'member123', '일반회원', 'USER')";

int count = stmt.executeUpdate(sql);

 

- 값을 변수로 대체한다면?

String userId = "member2";

String password = "member123";

String name = "일반회원";

String role = "USER";

...

=> PreparedStatement(=실행계획이 이미 다 잡혀있다는 의미임)로 처리

 

- SQL문에 값을 넣을 때 파라미터화 해서 처리

String sql = "INSERT INTO USERS(ID, PASSWORD, NAME, ROLE) " + "VALUES(?, ?, ?, ?)";

//주의사항 : "INSERT INTO USERS(ID, PASSWORD, NAME, ROLE) " 에서 큰 따옴표로 끝맺기 전에 반드시 스페이스(" ")를 해줘야 함.

 

- Connection 객체를 통해 생성

PreparedStatement pstmt = conn.preparedStatement(sql);

 

- 파라미터 설정

* pstmt.setXxxx(파라미터번호, 값)

** setString(), setInt(), setLong(), setDouble()

 

- SQL문 실행

int count = pstmt.executeUpdate();

 

8. VO 패턴

- VO 객체

* Value Object

* 특정 테이블의 한 행을 맵핑하는 클래스

Table 정의 = 데이터 클래스 정의(VO)

클래스 정의 -> 테이블

필드들 -> 칼럼들

인스턴스 -> 한 행

 

9. DAO 패턴 적용

- DAO 클래스

 

<JUnit>

1. JUnit (메서드검증용)

- 단위 테스트 도구

- 외부 테스트 프로그램(케이스)을 작성하여 System.out으로 번거롭게 디버깅하지 않아도 되며, 테스트에 걸린 시간도 관리 가능

- 보이지 않고 숨겨진 단위 테스트를 끌어내어 정형화시켜 단위 테스트를 쉽게 해주는 테스트용 Framework 입니다.

- JUnit의 특징

* 단위테스트 Framework 중 하나

* 어노테이션으로 간결하게 지원함

* 단정문으로 테스트 케이스

*

 

2. JUnit관련 어노테이션

- @Test

* 메서드 위에 선언, 테스트 대상 메소드임을 의미

- @Test(timeout=5000)

* 테스트 결과를 반환하는데 5초를 넘기면 테스트 실패로 간주

* 단위는 ms

- @Test(expected=RumtimeException.class)

* RuntimeException이 발생해야 테스트 성공

* 그렇지 않으면 실패로 간주

- @BeforeClass, @AfterClass

* 해당 테스트 클래스

 

3. 단정문

- assertArrayEquals(expected, actual, message)

- assertEquals(expected, actual, message)

- assertSame(expected, actual, message)

- assetTrue(actual, message)

- assertNotNull(actual, message)

 

* 실제값과 기대값을 조사하고