24일차 수업 정리(Index, Synonym, Procedure, Trigger)

**INDEX

    => 데이터를 빠르게 검색할 수 있도록 해주는 데이터베이스 객체

    => PRIMARY KEY와 UNIQUE 제약조건은 자동으로 인덱스를 생성

1. 인덱스 생성

    CREATE INDEX 인덱스명

    ON 테이블 명(컬럼명 나열);

    => 하나의 컬럼으로 만들수 있지만 여러개의 컬럼을 이용해서도 생성 가능

    => 하나의 컬럼으로 구성된 인덱스 : 단일 인덱스, 다수의 컬럼으로 구성된 인덱스 : 복합 인덱스

    => 인덱스는 계산식에도 생성 가능, 이러한 인덱스를 함수 기반 인덱스라고 함

2. 인덱스 제거

    DROP INDEX 인덱스명

3. 인덱스를 생성해야 하는 경우

    => 기본키나 UNIQUE 속성이 아닌데 검색에 자주 이용하는 컬럼에 생성

    => 게시판의 경우 주로 검색은 글번호가 아닌 제목이나 내용

 

**SYNONYM

    => 데이터베이스 객체에 별명을 붙이는 것

1. 생성

    CREATE SYNONYM 별명

    FOR 원본이름;

2. 삭제

    DROP SYNONYM 별명;

3. 사용

    => 데이터베이스 연동하는 프로그램을 만들 때 SYNONYM을 이용하면 유지보수에 유리

 

**INLINE VIEW

    => 오라클에서 TON-N을 구현할 때 사용

    => FROM 절에 SELECT구문을 사용하는 것

1. ROWNUM

    => 오라클에서 데이터를 조회할 때 부여하는 일련번호로 WHERE 절을 수행하기 전에 임시로 번호 부여 후, WHERE절의 조건을 만족하면 확정되고, 다음 행에는 이전 번호에 +1을 해서 부여

 

2. 앞에서 3개의 데이터만 EMP 테이블에서 조회

SELECT *
FROM EMP
WHERE ROWNUM <= 3;

 

3. 앞에서 3개의 데이터를 제외한 데이터를 EMP 테이블에서 조회 - 데이터 미조회

SELECT *
FROM EMP
WHERE ROWNUM > 3;

 

조회 방법

--정렬을 한 후데이터를 5개 추출

--이렇게 하려면 FROM절에서 정렬를 먼저 해야 함

--FROM절에 아래처럼 SELECT 구문을 사용하는 것을 INLINE VIEW라고 함

--데이터를 원하는 순서대로 정렬을 하고 행 번호를 만들고 그 행번호를 가지고 조건을 생성

SELECT ENAME, HIREDATE
FROM (SELECT ROWNUM RNUM, ENAME, HIREDATE
           FROM(SELECT *
                     FROM EMP
                     ORDER BY HIREDATE))
WHERE RNUM <= 5;

--입사일이 빠른 6번째 부터 5개의 데이터

SELECT ENAME, HIREDATE
FROM (SELECT ROWNUM RNUM, ENAME, HIREDATE
           FROM (SELECT *
                     FROM EMP
                     ORDER BY HIREDATE))
WHERE RNUM >= 6 AND RNUM <= 10;

<나중에 써먹을 것 - 게시판>

--페이지 번호는 PAGENO 변수에

--페이지당 데이터 갯수는 PAGECNT에 저장된 경우

--페이지 번호에 해당하는 데이터를 가져오는 SQL을 작성

--PAGENO가 1이고 PAGECNT가 10이라면 1~10까지 가져와야 함

--PAGENO가 2라면 11~20번 까지 가져와야 함

SELECT ENAME, HIREDATE
FROM (SELECT ROWNUM RNUM, ENAME, HIREDATE
           FROM (SELECT *
                     FROM EMP
                     ORDER BY HIREDATE))
WHERE RNUM >= (PAGENO-1)*PAGECNT +1 AND RNUM <= (PAGENO)*PAGECNT;

**PROCEDURE

    => 프로그래밍 언어의 함수처럼 자주 사용하는 SQL을 하나의 이름으로 묶어둔것

    => PROCEDURE 만드는 문법 - 오라클 : PL/SQL, MS SQL SERVER : T-SQL

1. 생성

CREATE [OR REAPLACE] PROCEDURE 프로시저 명
(변수 자료형....)
IS
BEGIN
           SQL 문장;
END;
/
    => DBEAVER에서는 /는 제외

2. 실행

    => DBEAVER에서 실행
BEGIN
           프로시저명(매개변수);
END; 

    => SQL PLUS의 경우
EXCUTE 프로시저명(매개변수);

3. 삭제

DROP PROCEDURE 프로시저명;

 

4. 실습

  1) DEPT 테이블에 데이터를 삽입
  
INSERT INTO DEPT(DEPTNO, DNAME, LOC)
VALUES(11, '영업', '춘천');

  2) 프로시저 이용

CREATE OR REPLACE PROCEDURE DEPT_INSERT
(VDEPTNO IN DEPT.DEPTNO%TYPE,
VDNAME INDEPT.DNAME%TYPE,
VLOC IN DEPT.LOC%TYPE)
IS
BEGIN
           INSERT INTO DEPT(DEPTNO, DNAME, LOC)
           VALUES(VDEPTNO, VDNAME, VLOC);
END;
/

BEGIN
           DEPT_INSERT(12, '기획', '대전');
END;

 

5. 프로시저를 사용하는 이유

    => 보안 : 유저는 테이블의 구조를 알 필요가 없음

    => 속도 : 일반 SQL은 실행시 마다 보조 기억 장치에서 호출, 프로시저와 뷰는 호출시 메모리에 적재된 상태로 존재

 

**TRIGGER

    => 삽입, 삭제 작업과 같은 DML 문장 수행시 다른 동작을 같이 할 수 있도록 해주는 객체

    => 삽입, 삭제 및 갱신 작업 발생시 로그를 기록하거나 유효성 검사를 수행하여 작업을 수행하지 않도록 하는 등의            작업을 만들때 주로 이용

1. 생성

CREATE TRIGGER 트리거명
[BEFORE | AFTER] [INSERT | DELETE | UPDATE - OR로 묶어서 여러개 사용가능]
ON 테이블명
FOR EACH ROW
BEGIN
           수행할 내용;
END;
    => INSERT시 :NEW, 삭제시 :OLD, 갱신시 :NEW와 :OLD를 이용하여 데이터 추가, 삭제 이용 가능

ex)트리거 생성, DEPT테이블에 INSERT, UPDATE, DELETE 후에 발생

CREATE TRIGGER DEPTTASK
AFTER INSERT OR UPDATE OR DELETE
ON DEPT
FOR EACH ROW
BEGIN
           IF INSERTING THEN INSERT INTO DEPTLOG(DEPTNO, LOGCONTENT)
           VALUES(:NEW.DEPTNO, '삽입');
           END IF;
           IF UPDATING THEN INSERT INTO DEPTLOG(DEPTNO, LOGCONTENT)
           VALUES(:NEW.DEPTNO, '수정');
           END IF;
           IF DELETING THEN INSERT INTO DEPTLOG(DEPTNO, LOGCONTENT)
           VALUES(:OLD.DEPTNO, '삭제');
           END IF;
END;

 

**JDBC

1. JDBC(Java Database Connectivity)

    => 자바를 이용해서 데이터 베이스를 만드는 것

    => 자바로 만든 SQL을 JDBC 드라이버를 통하여 데이터베이스에 전달, 수행결과를 JDBC드라이버를 통해 반환

    => 자바로 데이터베이스 작업을 수행시 반드시 JDBC드라이버가 Build Path에 추가되어야 함

 

2. JDBC 프로그래밍을 위한 준비

  1) 사용하려는 데이터베이스 드라이버

  2) 사용할 데이터 베이스의 URL : 192.168.0.200 : 1521 : xe

    => 1521은 포트번호

    => xe는 데이터베이스 이름(sid)

  3) 계정이 있어야 함(계정이 필요없는 경우도 있음 - access, sqlite3)

 

3. 작업 순서

  1) 드라이버 클래스 로드 : 처음 1회만 수행

  2) Database 연결 - Connectoin 클래스의 인스턴스 생성

  3) SQL을 실행가능한 인스턴스 생성 - PreparedStatement 또는 CallableStatement를 Connection을 이용하여 생성

  4) SQL 실행 - Statement의 executeUpdate(select외) 또는 executeQuery(select) 메소드를 호출

  5) 결과를 사용

    - executeUpdate는 정수를 리턴 : 정수는 영향받은 행의 갯수

    - executeQuery는 ResultSet 인스턴스를 리턴 : select 구문의 결과

  6) 사용한 인스턴스들의 close() 호출

    => 자바에서 데이터베이스 관련된 작업은 예외처리를 강제

 

4. 데이터베이스 연결작업[처음이니까 해보는 거]

  1) Application 생성

  2) 드라이버 파일을 Application에 복사

  3) 복사한 jar파일을 선택하고, 마우스 오른쪽을 클릭 후, [Build Path] - [Add To Build Path]를 선택

    - 이 파일을 애플리케이션 build시 추가해달라는 요청

  4) 드라이버 클래스 로드

    - Class.forName("드라이버 클래스 이름");

    => 오라클 : oracle.jdbc.driver.OracleDriver - 대부분 복사해서 사용

  5) 데이터베이스 접속

    => 오라클의 이름 : jdbc:oracle:thin:@ip:포트번호:sid

        - 오라클에서 service 이름을 주면 ':sid' 대신 '/서비스명' 으로 설정해야 함

        - 우리 데이터베이스는 192.168.0.200에 위치, 1521번 포트를 사용, sid는 xe

    => 계정 - user08 : user08

        - Connection 변수명 = DriverManager.getConnection(String url, String userid, String passward);

    => url이 잘못되면 Network Adapter가 연결을 생성 할 수 없다는 메시지

    => user id 나 passward가 잘못되면 Login Denied

  6) SQL 실행

    => PreparedStatement : 일반 SQL을 실행하고자 하는 경우 생성

    => CallableStatement : 프로시저를 실행하고자 하는 경우 생성

  7) PreparedStatement를 이용한 select 이외의 구문 실행

    - PreparedStatement 변수명 = Connection 인스턴스.prepareStatement(String sql);

    - int 결과 = 변수명.executeUpdate();

    - 결과는 영향받은 행의 갯수

    - insert : 성공시 1이상의 숫자가 리턴, update, delete : 성공시 0이상의 숫자가 리턴

    - update, delete : where 절이 있기 때문에 구문은 정상 수행되었으나 조건에 맞는 데이터가 없어 아무일도 발생하지

                           않을 수 있음

  8) PreparedStatement의 데이터 바인딩

    => PreparedStatement 인스턴스 생성시, SQL을 완성하지 않고 ?로 설정후 나중에 데이터 바인딩 가능

    => 사용하는 메소드는 set자료형(?번호, 실제 데이터)

    => ?번호는 1부터 시작

PreparedStatement pstmt = con.prepareStatement("INSERT INTO ITEM(NUM, NAME, MANUFACTURE, PRICE, RECEIVEDATE) " + "VALUES(?, ?, ?, ?, ?)");
    => 숫자 데이터 : setInt, setDouble, 문자열 : setString, 날짜 : SetDate, setTimestamp, setTime을 호출

pstmt.setInt(1, 4);
pstmt.setString(2, "오징어");
pstmt.setString(3, "속초");
pstmt.setInt(4, 3500);
java.sql.Date date = new java.sql.Date(System.getCurrentTimeInMillis());
pstmt.setDate(5, date);