27일차 - ALTER/DECODE/COMMIT/뷰/JDBC연동

ALTER/NVL/DECODE/COMMIT/ROLLBACK/뷰/JDBC연동/INSERT

 

ALTER TABLE

 

→ 테이블을 수정합니다.

→ 컬럼의 추가, 제거 또는 컬럼의 속성(타입, 길이, 제약조건 등)을 변경합니다.

형식:

→ 컬럼 추가: ALTER TABLE 테이블_이름 ADD(열_이름 타입) ;

→ 컬럼 변경: ALTER TABLE 테이블_이름 MODIFY(열_이름 타입) ;

→ 컬럼 제거: ALTER TABLE 테이블_이름 DROP COLUMN(열_이름) ;

 

예시

→ acol 열의 제약조건을 NOT NULL로 하고 테이블을 생성합니다.

 

→ acol의 값을 입력하지 않으면 오류가 발생합니다.

→ dcol 컬럼을 추가합니다.

→ ccol의 속성(제약조건 : ccol 컬럼 default 설정)을 변경합니다.

→ dcol 컬럼을 제거합니다.

→ acol에 값을 대입합니다.

→ acol에 기본키 제약조건을 추가합니다.

→ acol이 'main', ccol이 '0'인 행을 삭제합니다.

→ 기본키 제약조건을 제거합니다.

 

제약조건 : CHECK

 

→ 입력되는 데이터의 조건을 설정합니다.

 

→ age 컬럼을 추가하고, 16이상 90이하의 값만 입력되도록 CHECK 제약조건을 설정합니다.

→ BETWEEN A AND B : A 이상 B 이하

 

→ age 컬럼에 16~90이 아닌 값을 입력하면 오류가 발생합니다.

→ gender 컬럼에 'M' 또는 'F'가 아닌 값을 입력하면 오류가 발생합니다.

 

SELECT 함수 NVL, DECODE, AS(Alias,별칭)

 

NVL(NULL VALUE)

→ NVL(열_이름, A) : 열의 값이 NULL일 때 값을 A로 대체합니다.

DECODE

→ DECODE(열_이름,'A1','A2','B1','B2') : 열의 값이 'A1'이면 'A2'로, 'B1'이면 'B2'로 대체합니다.

 

→ NVL(age,0) : age 컬럼의 값이 NULL일 때 값을 0으로 대체합니다.

→ DECODE(gender,'M','남성','F','여성') : gender 컬럼의 값이 'M'이면 '남성', 'F'이면 '여성'으로 대체합니다.

→ 테이블명/컬럼명 별칭은 AS 키워드를 사용하고, 생략할 수도 있습니다.

 

COMMIT / ROLLBACK

 

COMMIT

→ 이전 COMMIT 또는 ROLLBACK 이후의 모든 작업(데이터의 변경)을 확정합니다.

 

ROLLBACK

→ 이전 COMMIT 또는 ROLLBACK 이후의 모든 작업(데이터의 변경)을 전의 상태로 복구합니다.

 

디비버는 auto commit을 사용합니다.

→ COMMIT/ROLLBACK을 사용하려면 auto commit 설정을 변경해야 합니다.

 

방법 1) 데이터베이스 메뉴 - 트랜잭션 모드 - manual commit 클릭

→ 커밋, 롤백 메뉴가 활성화되고 Auto에서 None으로 바뀌었습니다.

 

방법 2) 윈도우 메뉴 - 설정 - 연결 - 연결 유형 - auto-commit by default 체크 해제 - 적용

예시

 

→ DELETE 문이 ROLLBACK됩니다.

→ 데이터의 삭제가 취소됩니다.

→ DELETE 문 후에 COMMIT을 하였으므로 ROLLBACK할 수 없습니다.

→ 데이터의 삭제가 취소되지 않습니다.

→ INSERT 문이 ROLLBACK됩니다.

→ 데이터의 추가가 취소됩니다.

→ 앞의 두 INSERT 문은 COMMIT을 하였으므로 ROLLBACK되지 않습니다.

→ COMMIT 뒤의 UPDATE 문과 DELETE 문은 ROLLBACK됩니다.  

 

뷰(VIEW)

 

→ 물리적으로 존재하지 않고 논리적으로 만들어진 '가상의 테이블'입니다.

→ 물리적인 테이블를 이용하여 생성합니다.

→ 뷰는 실제로 SELECT 문으로 이루어져 있습니다.

→ 사용자가 테이블처럼 SELECT를 조회할 수 있습니다.

→ 보안에 도움이 되는 장점이 있습니다.

 

뷰를 만들기 위해서는 권한을 부여해야 합니다. (SQL PLUS에서 실행)

→ idev 유저에게 create view 권한을 줍니다.

 

뷰 생성문 형식:

CREATE VIEW 뷰_이름

AS

 SELECT 문

 

뷰를 이용한 데이터 조회

→ SELECT 열_이름 FROM 뷰_이름 [WHERE 행에 대한 조건] ;

 

예시

→ 1) JOIN하여 뷰를 생성합니다.

→ 2) SELECT문으로 뷰의 데이터를 조회합니다.

 

 

JDBC(Java DataBase Connectivity)

 

자바 프로그램 안에서 SQL을 실행하기 위한 API(응용프로그램 인터페이스)입니다.

 

Oracle과 Eclipse 연동하기

 

오라클일 때 ojdbcxx.jar (라이브러리 추가, 자바와 오라클을 연동하기 위한 프로그램의 일종)

오라클 서버와 연결하기 위한 정보(URL, port, user, password)가 필요합니다.

 

1) ojdbc6.jar의 위치를 확인합니다. (찾기 쉬운 곳으로 복사합니다.)

→ C:\oraclexe\app\oracle\product\11.20\server\jdbc\lib

 

2) 이클립스에서 프로젝트 우클릭한 후 Build Path - Configure Build Path...을 클릭합니다.

 

3) Libraries 탭 클릭 후 Add External JARs...를 클릭합니다.

 

4) ojdbs6.jar 파일을 선택하고 열기를 클릭합니다.

 

5) ojdbs6.jar 파일이 추가된 것을 확인하고 Apply and Close를 누릅니다.

 

6) Referenced Libraries에 ojdbs6.jar 파일이 추가되었습니다.

 

7) 오라클에서 user, URL을 확인합니다. 왼쪽 창 스키마에 마우스를 올려놓으면 확인할 수 있습니다.

→ User : idev

→ URL : jdbc:oracle:thin:@//localhost:1521/XE    → port 번호 : 1521

 

8) 오라클의 Driver는 oracle.jdbc.driver.OracleDriver입니다.

 

9) 이클립스에서 코드를 작성합니다. 

→ JDBCConnectTest 클래스: JDBC를 이용하여 오라클 데이터베이스에 연결합니다.

→ Connection 객체를 생성합니다.

Class.forname(driver)

→ 데이터베이스와 연결할 드라이버를 찾아서 로드합니다.

→ 드라이버 없을 시 오류가 발생하므로 반드시 예외 처리를 해야한다.

conn=DriverManager.getConnection()

→ DriverManager 클래스의 getConnection 메소드로 드라이버에 연결할 수 있는 Connection 구현 객체를 생성합니다.

→ conn에 Connection 객체를 저장합니다.

conn.close()

→ Connection 객체의 사용이 종료되면 닫아줍니다.

 

유틸 클래스 만들기 : OracleConnectUtil 클래스

 

→ 오라클 데이터베이스 연결/종료를 위한 util 클래스를 만듭니다.

 

connect() 메소드

→ 오라클 데이터베이스와 연결합니다. (JDBCConnectTest 클래스와 유사합니다.)

 

close() 메소드

→ 오라클 데이터베이스와 연결을 종료합니다.

 

JDBC - INSERT

 

PreparedStatement 인터페이스

→ 자바에서 데이터베이스로 SQL 문을 전송할 때 사용합니다.

→ 사용시 예외처리가 반드시 필요합니다.

PreparedStatement 객체는 Connection 객체의 preparedStatement() 메소드로 생성합니다.

→ preparedStatement() 메소드는 SQL 문을 저장한 String 타입 변수를 인자로 갖습니다.

→ SQL 문 값에 ?를 쓰면 변수를 대입할 수 있습니다. ?인덱스는 1부터 시작합니다.

→ 값을 지정할 때 setString(?인덱스, String 값), setInt(?인덱스, Int 값) 등 setXXX 메소드를 사용합니다.

→ setXXX 메소드는 execute() 메소드, executeQuery() 메소드 앞에 호출합니다.

→ execute(), executeQuery() 메소드는 SQL문을 실행하는 메소드입니다.

→ 실행 뒤에 close() 메소드로 사용을 종료합니다.

 

예제1: InsertTest

→ Connection 객체로 오라클과 연결합니다.

→ 변수 sql에 INSERT문을 대입합니다.

 

→ Connection 객체의 preparedStatement() 메소드로 PreparedStatement 객체를 생성합니다.

→ SQL 문의 ?에 해당하는 값을 setXXX 메소드로 전달합니다.

→ execute() 메소드로 SQL 문을 실행합니다.

→ close() 메소드로 PreparedStatement 객체 사용을 종료합니다.

 

→ 실행 결과 tbl_custom# 테이블에 새로운 행이 추가되었습니다.

 

예제2: InsertTest2

→ Scanner 클래스로 변수에 입력 값을 저장합니다.

 

→ setXXX 메소드로 입력 값을 INSERT 문에 전달합니다.

 

→ 데이터베이스에 저장할 값을 입력합니다.

 

→ 실행 결과 tbl_custom# 테이블에 새로운 행이 추가되었습니다.