[Oracle/SQL] DML, Sequence

💬 기억하고 싶거나 공부한 내용을 기록중입니다 :)   개인적으로 기억하고 싶은 내용들을 적어두고 있습니다.

💬 내용이 수정될 수 있습니다.

 

1️⃣ DML in Oracle 

 

1. SELECT(조회)

<문법> SELECT * 또는 [distinct] columnName [as Alias], ... ,columnName FROM tableName WHERE조건                                         -- 예) where columnName < 30 GROUB BY 기준                                       ORDER BY 기준 방법                              -- 예) order by columnName ASC/DESC

예) guitar라는 테이블에서, 2000년 1월 1일 이전에 출시된 Fender 기타 중에서 가격이       300에서 500 사이 중 높은 순으로 정렬해 기타 가격과 기타 색을 출력하시오. SELECT guitarPrice, guitarColor FROM guitar WHERE guitarMakeDate < '2000-01-01' and guitarPrice between 300 and 500 ORDER BY guitarPrice DESC;

예) guitar라는 테이블에서, guitarBrand라는 컬럼의 별칭을 gBrand라고 출력하면서,       중복된 브랜드들명이 나오지 않게 출력하시오. SELECT * distinct guitarBrand as gBrand FROM guitar;

2. INSERT

<문법> INSERT INTO tableName[(columnName,..,columnName)] VALUES(value1,...,valueN) WHERE 조건;

예) INSERT INTO guitar(guitarNo, guitarBrand, guitarColor, guitarPrice) VALUES(90, 'Fender', 'Black', 300);

3. UPDATE

<문법> UPDATE tableName SET columnName=newValue, ... , columnName=newValue WHERE 조건;

예) UPDATE guitar SET guitarBrand = Gibson WHERE guitarNo = 90;

4. DELETE

<문법> DELETE FROM tableName WHERE 조건;

예) DELETE FROM guitar WHERE guitarNo = 90;

2️⃣ DB와 Java를 연동해 DML 실행하기

 

1. JDBC DRIVER

try {
	Class.forName("oracle.jdbc.OracleDriver");
} catch (ClassNotFoundException e) {
	e.printStackTrace();
}

2. CONNECTION

String url = "jdbc:oracle:thin:@localhost:port번호/xe";	// localhost 대신 ip주소 가능
String user = "USERNAME";
String password = "PASSWORD";
Connection conn = null;

try {
	conn = DriverManager.getConnection(url, user, password);
} catch (SQLException e) {
	e.printStackTrace();
}

3. 객체 및 쿼리

1) Statement

Statement stmt = null;
String sql = "insert into dept(guitarNo, guitarBrand, guitarColor)" + 
             " values(90,'Fender','Black')";

try {
	stmt = conn.createStatement();
} catch (SQLException e1) {
	e1.printStackTrace();
}

try {
	int cnt = stmt.executeUpdate(sql);			// 반환값이 있는 경우
} catch (SQLException e1) {
	e1.printStackTrace();
}

2) PreparedStatement

PreparedStatement stmt = null;
String sql = "insert into dept(guitarNo, guitarBrand, guitarColor)" +
             " values(?,?,?)";				// main method에서 값을 받아주는 경우

try {
	stmt = conn.prepareStatement(sql);
} catch (SQLException e1) {
	e1.printStackTrace();
}

try {
	stmt.setInt(guitarNo);
	stmt.setString(guitarBrand);
	stmt.setString(guitarColor);
	int cnt = stmt.executeUpdate();			// 반환값이 있는 경우
} catch (SQLException e1) {
	e1.printStackTrace();
}

3) 쿼리문별로 메소드를 준비한다면?

public void getGuitarBrand(String guitarBrand) {
	String sql = "SELECT guitarNo, guitarColor FROM guitar"
					+ " WHERE guitarBrand = ?" + " ORDER BY guitarNo ASC";

	try {
		stmt = conn.prepareStatement(sql);
	} catch (SQLException e1) {
		e1.printStackTrace();
	}

	try {
		stmt.setString(1, guitarBrand);			// ?의 순서대로 숫자 입력
		eq = stmt.executeQuery();				// console에서 출력할 때
		while(eq.next()) {						// while문 통해서 출력
			int guitarNo = eq.getInt("guitarNo");
			String guitarColor = rs.getString("guitarColor");
			System.out.printf("%10d\t%10s\r\n",guitarNo,guitarColor);
		}
	} catch (SQLException e1) {
		e1.printStackTrace();
	}

	try {										// 메소드에서 사용한 자원 반납
		if(stmt != null) {stmt.close();}
	} catch (SQLException e) {
		e.printStackTrace();
	}
}

4. 자원 반납

try {
	if(stmt != null) {stmt.close();}
	if(conn != null) {conn.close();}
    if(rs != null) {rs.close();}
} catch (SQLException e) {
	e.printStackTrace();
}

3️⃣ Sequence

- Sequence(시퀀스)는 자동으로, 순차적으로 증감하는 순번을 반환하는 데이터베이스 객체이다

 

1. 시퀀스 생성

1) 생성

CREATE SEQUENCE sequenceName  -- 사용할 시퀀스 이름 INCREMENT BY 2                                  -- 증감하는  start with 1                                               -- 시작하는 번호 (시퀀스의 시작값이다) minvalue 1                                               -- 최솟값 (시퀀스가 시작되는 최솟값이다) maxvalue 99                                            -- 최댓값 (시퀀스가 끝나는 최댓값이다) nocycle | cycle                                         -- 최댓값 도달시, 시작값부터 다시 시작하는지 여 nocache | cache;                                     -- cache 옵션 사용 여부

2) 생성 후 조회

SELECT sequenceName                       FROM user_sequences;

2. 시퀀스 사용

- sequenceName.NEXTVAL

INSERT INTO guitar(guitarNo, guitarBrand, guitarColor) VALUES(sequenceName.NEXTVAL, 'Fender', 'Black');

3. 시퀀스 수정

ALTER SEQUENCE sequenceName                      INCREMENT BY 1                                                  start with 1                                                               minvalue 1                                                               maxvalue 85                                                            nocycle | cycle                                                         nocache | cache;

4. 시퀀스 삭제

DROP SEQUENCE sequenceName;