JAVA 풀스택 36일차 - Oracle DB 6 / JDBC

Oracle DB

1. INDEX (인덱스)

데이터를 더 빨리 찾기 위한 기

- 검색 속도 향상용 기능

1-1 장점

- 검색 속도 향상

- SELECT 성능 개선

1-2 단점

- 데이터를 수정할 때마다 다시 계산해야 함

- 저장 공간 추가 사용

 

 

1-3 인덱스 정보 조회

SELECT *
FROM USER_IND_COLUMNS;

2. 고유 인덱스 (UNIQUE INDEX)

중복 없는 값

CREATE UNIQUE INDEX IDX_EMP_NO
ON EMPLOYEE(EMP_NO);

- 중복값 허용 안함

- PRIMARY KEY / UNIQUE 생성 시 오라클이 자동 생성하기도 함

3. 일반 INDEX

자주 조회하는 컬럼 기준 인덱스

CREATE INDEX IDX_DEPT_CODE
ON EMPLOYEE(DEPT_CODE);

4. INDEX 삭제

DROP INDEX IDX_DEPT_CODE;

5. PL / SQL

오라클의 절차형 프로그래밍 언어

- SQL 안에서 변수, 조건문, 반복문 사용 가능

5-1 특징

- 변수 사용 가능

- 조건문 사용 가능

- 반복문 사용 가능

- 예외 처리 가능

5-2 PL/SQL 기본 구조

DECLARE
BEGIN
EXCEPTION
END;

영역	의미
DECLARE	변수 선언
BEGIN	실행 영역
EXCEPTION	예외 처리
END	종료

5-3 예외 처리

EXCEPTION
WHEN 예외명
THEN 실행코드;

EXCEPTION
WHEN DUP_VAL_ON_INDEX
THEN DBMS_OUTPUT.PUT_LINE('이미 존재');

- 중복 값 오류 발생 시 메시지 출력

5-4 DBMS_OUTPUT.PUT_LINE()

출력 함수

DBMS_OUTPUT.PUT_LINE('HELLO');

5-5 변수 사용

값을 저장하는 공간

DECLARE
	vid NUMBER;

5-6 SELECT INTO

조회 결과를 변수에 저장

SELECT EMP_ID
INTO vid
FROM EMPLOYEE
WHERE EMP_NAME='레몬팡';

6. %TYPE / %ROWTYPE

6-1 %TYPE

컬럼 자료형 그대로 가져오기

ID EMPLOYEE.EMP_ID%TYPE;

- EMP_ID와 같은 타입으로 변수 생성

6-2 %ROWTYPE

테이블 한줄 전체 저장

myrow EMPLOYEE%ROWTYPE;

- EMPLOYEE 한 행 전체 저장 가능

7. IF문

조건에 따라 다르게 실행

IF 조건 THEN
	실행문
END IF;

7-1 여러 조건

IF 조건1 THEN
ELSIF 조건2 THEN
ELSE
END IF;

7-2 ELSIF

IF 조건 THEN
	실행문
ELSIF 조건 THEN
	실행문
ELSE
	실행문
END IF;

8. LOOP (반복문)

같은 작업 여러 번 반복

8-1 기본 구조

LOOP
	반복내용
END LOOP;

- 무한 반복 가능

8-2 EXIT (종료)

조건 만족 시 종료

LOOP
	DBMS_OUTPUT.PUT_LINE(N);
 	N := N-1;
	IF N = 0 THEN 
		EXIT;
	END IF;
END LOOP;

8-3 EXIT WHEN

조건 만족 시 반복문 종료

LOOP
	DBMS_OUTPUT.PUT_LINE(N);
 	N := N-1;
	EXIT WHEN N=0;
END LOOP;

8-4 FOR LOOP 

조건 반복문

FOR N IN 1..5 LOOP
	DBMS_OUTPUT.PUT_LINE(N);
END LOOP;

8-5 FOR문 INSERT

FOR X IN 1..10 LOOP
	INSERT INTO TEST_FOR
	VALUES(X, CURRENT_DATE);
END LOOP;

8-6 REVERSE

FOR X IN REVERSE 1..10 LOOP
	INSERT INTO TEST_FOR
	VALUES(X, CURRENT_DATE);
END LOOP;

9. PROCEDURE (프로시저)

자주 사용하는 SQL을 저장하고 필요할 때 실행하는 기능

9-1 기본 구조

CREATE OR REPLACE PROCEDURE 프로시저명
IS
BEGIN
    실행할 코드
END;

코드	의미
CREATE OR REPLACE	이미 있으면 덮어쓰고 없으면 생성
PROCEDURE	프로시저를 생성하겠다는 의미
IS	변수 선언 영역
BEGIN	실행 코드 시작
END	종료

CREATE OR REPLACE PROCEDURE DEL_ALL_EMP
IS
BEGIN
    DELETE FROM EMP_TMP;
END;

- DEL_ALL_EMP를 실행하면 EMP_TMP 테이블 전체 삭제

9-2 실행 방법

BEGIN
    DEL_ALL_EMP;
END;

EXEC DEL_ALL_EMP;

10. FUNCTION (함수)

계산 후 결과값을 돌려주는 기능

10-1 기본 구조

CREATE OR REPLACE FUNCTION 함수명(매개변수)
RETURN 자료형
IS
BEGIN
    실행 코드

    RETURN 값;
END;

CREATE OR REPLACE FUNCTION BONUS_CALC(V_EMP_ID IN EMPLOYEE.EMP_ID%TYPE)
RETURN NUMBER
IS

10-2 사용 방법

SELECT EMP_NAME, BONUS_CALC(EMP_ID)
FROM EMPLOYEE;

11. 프로시저, 함수 차이

프로시저	함수
작업 실행	값 반환
RETURN 없어도 됨	RETURN 반드시 필요

12. TRIGGER (트리거)

특정 일이 발생하면 자동 실행되는 기능

12-1 기본 구조

CREATE OR REPLACE TRIGGER 트리거명
실행시점
BEGIN
    실행 코드
END;

CREATE OR REPLACE TRIGGER TRG_01
AFTER INSERT ON PRODUCT_DETAIL
FOR EACH ROW
BEGIN

12-1-1 AFTER INSERT

- INSERT 이후 실행

12-1-2 ON PRODUCT_DETAIL

- PRODUCT_DETAIL 테이블에서 발생 시

12-1-3 FOR EACH ROW

-  행 하나 추가될 때마다 실행

13. :NEW

:NEW.STATUS

- 새로 들어온 데이터

- 방금 INSERT 된 값

 

JDBC

1. JDBC

JAVA와 DB를 연결해주는 기술

Java 프로그램 → Oracle DB 접속 → SQL 실행 → 결과 가져오기

2. JDBC 흐름

// 1. 드라이버 등록
Class.forName("oracle.jdbc.driver.OracleDriver");

// 2. DB 연결
Connection con = DriverManager.getConnection(url, id, pw);

// 3. SQL 실행
Statement stmt = con.createStatement();
ResultSet rs = stmt.executeQuery(sql);

// 4. 결과 처리
while(rs.next()) {
    ...
}

// 5. 종료
rs.close();
stmt.close();
con.close();

2-1 드라이버 등록

Class.forName("oracle.jdbc.driver.OracleDriver");

- JAVA가 Oracle DB가 연결할 수 있게 불러오는 코드

2-2 DB 연결

String url = "jdbc:oracle:thin:@localhost:1521/orcl";
String id = "ID";
String pw = "PW";

Connection con = DriverManager.getConnection(url, id, pw);

DB에 로그인하는 영역

- url : DB 주소

- id : DB 계정

- pw : DB 비밀번호

- Connection : DB와 연결된 통로

2-3 SQL 작성

Statement stmt = con.createStatement();
ResultSet rs = stmt.executeQuery(sql);

- Statement : SQL 실행 도구

- ResultSet : SELECT 결과를 담는 상자

  executeQuery()는 SELECT문 실행 시 사용

2-4 결과 출력

while(rs.next()) {
    System.out.println(rs.getInt(1) + "\t" + rs.getString(2));
}

- rs.next() : 결과를 한 줄씩 이동화면서 읽는 코드

3. ResultSet 값 꺼내기

3-1 컬럼 번호로 꺼내기

rs.getInt(1)
rs.getString(2)

- rs.getInt(1) : 1번째 컬럼 꺼내기

3-2 컬럼 이름으로 꺼내기

rs.getInt("SALARY")
rs.getString("DEPT_TITLE")

- 컬럼 이름으로 값을 꺼냄

4. 연결 종료

rs.close();
stmt.close();
con.close();

 

 

코드	의미
Class.forName()	DB 드라이버 등록
DriverManager.getConnection()	DB 연결
Connection	DB 연결 통로
Statement	SQL 실행 도구
executeQuery()	SELECT 실행
ResultSet	조회 결과 저장
rs.next()	다음 행으로 이동
getString()	문자 데이터 꺼내기
getInt()	숫자 데이터 꺼내기
close()	자원 반납