24일차 공부 select, procedure, JDBC
1 교시
** 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 테이블에서 조회
2~3 교시
--EMP 테이블에서 입사일(HIREDATE)이 가장 빠른 5개의 데이터의 ENAME, HIREDATE를 조회
CLARK, TURNER, MARTIN, KING, JAMES
--페이지 번호는 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을 하나의 이름으로 묶어 둔것
=> 오라클에서 PROCUDURE 만드는 문법을 PL/SQL 이라고 한다.
MS/SQL SERVER에서 PROCEDURE 만드는 문법을 T-SQL 이라고 한다.
1. 생성
CREATE OR REPLACE PROCEDURE 프로시져이름
(변수자료형..)
IS
BEGIN
SQL문장
END;
/
=> DBEAVER에서는 / 는 제외
2. 실행
=> DBEAVER에서 실행
BEGIN
프로시저이름(매개변수);
END;
=> SQL PLUS의 경우
EXECUTE 프로시져이름(매개변수);
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 IN DEPT.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 문장을 수행할 때 다른 동작을 같이 할 수 있도록 해주는 객체
=> 삽입이나 삭제 및 갱신 작업이 일어날 때 로그를 기록하거나 유효성 검사를 수행해서 작업을 수행하지 않도록 하는 등의 작업을 만들 때 주로 이용
CREATE TRIGGER 트리거이름
[BEFORE | AFTER] [INSERT | DELETE | UPDATE - OR 로 묶어서 여러개 사용 가능]
ON 테이블이름
FOR EACH ROW
BEGIN
수행할 내용;
END;
/
=> INSERT 할 때는 :NEW 그리고 삭제할 때는 :OLD 갱신할때는 :NEW와 :OLD를 이용해서 새로 추가되는 데이터나 삭제되는 데이터를 이용할 수 있다.
--DEPT 테이블에 삽입, 삭제, 갱신 작업이 일어나면
--DEPTNO 테이블에 작업 내용과 작업이 발생항 DEPTNO를 기록하는 트리거를 생성
--로그를 기록할 테이블 생성
CREATE TABLE DEPTLOG(
DEPTNO NUMBER(3),
LOGCONTENT VARCHAR2(20));
--트리거를 생성
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, '삽입');
IF UPDATING THEN
INSERT INTO DEPTLOG(DEPTNO, LOGCONTENT)
VALUES(:NEW.DEPTNO, '수정');
IF DELETING THEN
INSERT INTO DEPTLOG(DEPTNO, LOGCONTENT)
VALUES(:NEW.DEPTNO, '삭제');
END IF;
END;
/
4 교시
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) 데이터베이스 연결 - Connection 클래스의 인스턴스를 생성
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이다.
=> 계정 - scott : tiger
Connection 변수이름 = Drivermanager.getConnection(String url. Strin userid, String password);
=>url이 잘못되면 Network Adapter 가 연결을 생성할 수 없다는 메시지
=>userid 나 password가 잘못되면 Login Denied
6) SQL 실행
=> PreparedStatement : 일반 SQP을 실행하고자 하는 경우 생성
=> CallableStatement : 프로시저 실행하고자 하는 경우 생성
7) PreparedStatement를 이용한 select 이외의 구문 실행
PreparedStatement 변수명 = Connection인스턴스.prepareStatement(String sql);
int 결과 = 변수명.executeUpdate();
결과는 영향받는 행의 개수
insert의 경우는 성공하면 1 이상의 숫자가 리턴
update나 delete의 경우는 성공하면 0이상의 숫자가 리턴
update와 delete의 경우는 where절이 있기 때문에 구문은 정상 수행되었는데 조건에 맞는 데이터가 없어서 아무일도 하지 않는 경우가 발생할 수 있다.
5 교시
실습
--실습에 사용할 테이블 생성
--테이블 이름 : ITEM
--번호(정수 4자리) : primary KEY
--이름(한글 10자리 - 변하지 않음) : not null
--원산지(한글20자리 - 자주 변함) : not null
--가격 (정수 10자리) : 기본값 0
--입고날짜(날짜)
CREATE TABLE ITEM(
NUM NUMBER(4) PRIMARY KEY,
NAME VARCHAR2(30) NOT NULL,
MANUFACTURE CHAR(50),
PRICE NUMBER(10) DEFAULT 0,
RECEIVEDATE DATE);
INSERT INTO ITEM(NUM, NAME, MANUFACTURE, PRICE, RECEIVEDATE)
VALUES(1, '사과', '대구', 3000, SYSDATE-1);
INSERT INTO ITEM(NUM, NAME, MANUFACTURE, PRICE, RECEIVEDATE)
VALUES(2, '감귤', '제주', 1000, SYSDATE-2);
SELECT *
FROM ITEM;
COMMIT;
8) PreparedStatement의 데이터 바인딩
=> PreparedStatement 인스턴스를 생성할 때 sql을 완성하지 않고 ?로 설정한 후 나중에 데이터를 바인딩할 수 있다.
=> 사용하는 메소드는 set자료형(?번호, 실제데이터)
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.setStrind(3, "속초");
pstmt.setInt(4, 3500);
java.sql.Date date = new java.sql.Date(System.getCurrentTimeInMillis());
pstmt.setDate(5, date);
6 교시
** ITEM 테이블에 데이터 삽입하기
1. 프로젝트에 ojdbc6.jar 파일을 복사하고 build path
