TIL 0320

먼지·2024년 3월 20일
TILoraclesql국비쌍용교육센터

Today I Learned

목록 보기
23/89
post-thumbnail

패키지

패키지는 업무와 관련된 Stored Procedure 및 Stored Function을 관리하고, 이를 패키지 단위로 배포할 때 유용하게 사용된다. 패키지는 선언부와 본문으로 구분된다. 선언부는 패키지에 포함될 Stored Procedure 또는 Stored Function을 포함한다.

패키지 선언부

CREATE OR REPLACE PACKAGE employee_pkg AS
   PROCEDURE print_ename(p_empno NUMBER);
   PROCEDURE print_sal(p_empno NUMBER);
END employee_pkg;

패키지 본문

CREATE OR REPLACE PACKAGE BODY employee_pkg AS
   PROCEDURE print_ename(p_empno NUMBER) IS
   e_name emp.ename%TYPE;
BEGIN 
   SELECT ename
   INTO e_name
   FROM emp
   WHERE empno = p_empno;
   DBMS_OUTPUT.PUT_LINE(e_name);
EXCEPTION WHEN NO_DATA_FOUND THEN
   DBMS_OUTPUT.PUT_LINE('Invalid Employee Number');
END print_ename;
   
   PROCEDURE print_sal(p_empno NUMBER) IS
   e_sal emp.sal%TYPE;
BEGIN 
   SELECT sal
   INTO e_sal
   FROM emp
   WHERE empno = p_empno;
   DBMS_OUTPUT.PUT_LINE(e_sal);
EXCEPTION WHEN NO_DATA_FOUND THEN
   DBMS_OUTPUT.PUT_LINE('Invalid Employee Number');
END print_sal;
END employee_pkg;

실행

EXEC EMPLOYEE_PKG.PRINT_ENAME(7369); -- 실행값 : SMITH
EXEC EMPLOYEE_PKG.PRINT_SAL(7369); -- 실행값 : 800
EXEC EMPLOYEE_PKG.PRINT_ENAME(8000); -- 실행값 : 예외 발생으로 'Invalid Employee Number' 출력

트리거

트리거는 데이터의 변경(INSERT, DELETE, UPDATE)문이 실행될 때 자동으로 같이 실행되는 프로시저를 말한다.
오라클은 기본적으로 실행 전(BEFORE)과 실행 후(AFTER) 트리거를 지원한다.

CREATE OR REPLACE TRIGGER print_message
AFTER INSERT ON dept
BEGIN
DBMS_OUTPUT.PUT_LINE('dept 테이블에 정상적으로 데이터가 추가되었습니다.');
END;

트리거를 생성한 후 dept 테이블에 데이터를 추가하면 등록된 트리거가 동작하면서
'dept 테이블에 정상적으로 데이터가 추가되었습니다'를 출력한다.

INSERT INTO dept VALUES (70,'EDUCATION' , 'JEJU');
COMMIT; -- DBMS에 해당 문구가 출력된다

FOR EACH ROW: 매번 추가되는 행의 수만큼 TRIGGER가 발생하도록 하는 함수
:new.컬럼이름 : 추가, 수정할 때 해당 컬럼의 새로운 값을 저장한다.

CREATE OR REPLACE TRIGGER afterinsertbook
AFTER INSERT ON book FOR EACH ROW
BEGIN
INSERT INTO book_log
VALUES(:new.bookid, :new.bookname, :new.publisher, :new.price);
COMMIT;
DBMS_OUTPUT.PUT_LINE('삽입한 데이터를 book_log 테이블에 백업했습니다');
EXCEPTION WHEN OTHERS THEN
   DBMS_OUTPUT.PUT_LINE('ERROR');
END;

Java & Oracle 연동시키기

  1. Eclipse 접속하기
  2. ojdbc8.jar 다운 받기
  3. 현재 사용 중인 폴더 (ch01_Java)에 일반 폴더 libs 생성 하기
  4. libs 폴더 생성 후 ojdbc8.jar 복사하여 폴더에 생성시키기
  5. ch01_Java 폴더의 buildpath 들어가서 Libraries 이동 ModulePath 클릭
  6. ModulePath 클릭 후 활성화 되는 add Jars 버튼 클릭 후 libs폴더에 등록된 ojdbc8.jar 클릭
  7. ModulePath에 등록 확인 후 apply and close 버튼 클릭하면 완료

JDBC

  • JDBC
    JDBC란 자바를 이용하여 데이터베이스에 접근하여 각종 SQL 문을 수행할 수 있도록 제공하는 API를 말한다.

  • JDBC의 구조와 역할
    JDBC는 크게 JDBC 인터페이스와 JDBC 드라이버로 구성되어 있다.

  • JDBC 드라이버 종류
    JDBC 드라이버는 DBMS의 벤더나 다른 연구 단체들에서 만들어진다.

  • JDBC를 이용한 데이터베이스 연결 방법
    1 단계 : 드라이버를 로드 한다.
    2 단계 : Connection 객체를 생성한다.
    3 단계 : Statement 객체를 생성한다.
    4 단계 : SQL문에 결과물이 있다면 ResultSet 객체를 생성한다.
    5 단계 : 모든 객체를 닫는다.

Driver Load

public class DriverMain {

	public static void main(String[] args) {
		try {
			Class.forName("oracle.jdbc.OracleDriver");
			System.out.println("오라클 드라이버가 정상적으로 로드되었습니다.");
		}
		catch(ClassNotFoundException e) {
			e.printStackTrace();
		}
	}

}

Connection

import java.sql.*;

public class ConnectionMain {

	public static void main(String[] args) {
		String db_driver = "oracle.jdbc.OracleDriver";
		String db_url = "jdbc:oracle:thin:@localhost:1521:xe";
		String db_id = "user01";
		String db_password = "1234";
		
		try {	
			//JDBC 수행 1단계 : 드라이버 로드
			Class.forName(db_driver);
			//JDBC 수행 2단계 : connection  객체 생성 (오라클 접속을 위한 인증)
			Connection conn = DriverManager.getConnection(db_url,db_id,db_password);
			System.out.println("Connection 객체가 생성되었습니다.");
			
		}catch(SQLException e) {
			e.printStackTrace();
		}
		catch(ClassNotFoundException e) {
			e.printStackTrace();
		}
	}

}

Create Table by Java

package kr.s31.jdbcstatement;
import java.sql.*;
public class CreateTableMain {

	public static void main(String[] args) {

		String db_driver = "oracle.jdbc.OracleDriver";
		String db_url = "jdbc:oracle:thin:@localhost:1521:xe";
		String db_id = "user01";
		String db_password = "1234";
		
		Connection conn = null;
		Statement stmt = null;
		String sql = null;
		
		try {
			// JDBC 수행 1단계 : DriverLoad
			Class.forName(db_driver);
			//JDBC 수행 2단계 :Connection 객체 생성(오라클 접속을 위한 인증)
			conn = DriverManager.getConnection(db_url, db_id, db_password);
			
			System.out.println("test1 테이블을 생성합니다.");
			
			sql = "CREATE TABLE test1 (id VARCHAR2(10) , age NUMBER)";
			
			/*
			 * 테이블을 생성하는 SQL 문
			 * 접속한 계정에 테이블명이 중복되면 에러가 발생하기 때문에 
			 * 동일 계정에서는 한 번만 수행함
			 */
			
			//JDBC 수행 3단계 : Statement 객체 생성
			stmt = conn.createStatement()	;
			
			//JDBC 수행 4단계 : SQL문을 실행해서 DB에 table 생성
			stmt.executeUpdate(sql);
			
			System.out.println("테이블이 정상적으로 생성되었습니다.");
			
		}catch(SQLException e) {
			e.printStackTrace();
		}catch(ClassNotFoundException e) {
			e.printStackTrace();
		} finally {
			//자원 정리
			if (stmt != null) try {stmt.close();} catch(SQLException e) { }
			if(conn !=null) try {conn.close()	;} catch(SQLException e) { }
		}
		
	}

}

Alter Table by Java

package kr.s31.jdbcstatement;
import java.sql.*;
public class AlterTableMain {

	public static void main(String[] args) {
		String db_driver = "oracle.jdbc.OracleDriver";
		String db_url = "jdbc:oracle:thin:@localhost:1521:xe";
		String db_id = "user01";
		String db_password = "1234";
		
		Connection conn = null;
		Statement stmt = null;
		String sql = null;
		
		try {
			//JDBC 수행 1단계 : 드라이버 로드
			Class.forName(db_driver);
			//JDBC 수행 2단계 : Connection 객체 생성 (오라클 접속을 위한 인증)
			conn = DriverManager.getConnection(db_url, db_id, db_password);
			
			System.out.println("test1 테이블의 컬럼을 수정합니다.");
			
			sql = "ALTER TABLE test1 MODIFY(id VARCHAR2(10) NOT NULL)";
			//JDBC 수행 3단계 : Statement 객체 생성
			stmt = conn.createStatement();
			//JDBC 수행 4단계 : SQL문을 수행해서 테이블에 정보를 변경하는 작업 수행
			stmt.executeUpdate(sql);
			System.out.println("테이블의 컬럼 정보 수정을 완료하였습니다.");
			
		}catch (Exception e) {
			e.printStackTrace();
		}
		finally {
			if (stmt != null) try {stmt.close();} catch(SQLException e) { }
			if (conn != null) try {conn.close();} catch(SQLException e) { } 
			}
	}

}

Drop Table by Java

package kr.s31.jdbcstatement;
import java.sql.*;
public class DropTableMain {

	public static void main(String[] args) {
		String db_driver = "oracle.jdbc.OracleDriver";
		String db_url = "jdbc:oracle:thin:@localhost:1521:xe";
		String db_id = "user01";
		String db_password = "1234";
		
		Connection conn = null;
		Statement stmt = null;
		String sql = null;
		
		try {
			//JDBC 수행 1단계 : 드라이버 로드
			Class.forName(db_driver);
			//JDBC 수행 2단계 : Connection 객체 생성 (오라클 접속을 위한 인증)
			conn = DriverManager.getConnection(db_url, db_id, db_password);
			
			System.out.println("test1 테이블을 삭제합니다.");
			
			sql = "DROP TABLE test1";
			//JDBC 수행 3단계 : Statement 객체 생성
			stmt = conn.createStatement();
			//JDBC 수행 4단계 : SQL문을 수행해서 테이블에 정보를 변경하는 작업 수행
			stmt.executeUpdate(sql);
			System.out.println("테이블 삭제를 완료하였습니다.");
			
		}catch (Exception e) {
			e.printStackTrace();
		}
		finally {
			if (stmt != null) try {stmt.close();} catch(SQLException e) { }
			if (conn != null) try {conn.close();} catch(SQLException e) { } 
			}
	}

}

CRUD 작업
C Create (생성) ---> INSERT 문
R Read (읽기) -----> SELECT 문
U Update (갱신) --> UPDATE 문
D Delete (삭제) ---> DELETE 문

Create Data In Table by Java

package kr.s31.jdbcstatement;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;
import java.sql.Statement;

public class InsertMain {

	public static void main(String[] args) {
		
		String db_driver = "oracle.jdbc.OracleDriver";
		String db_url = "jdbc:oracle:thin:@localhost:1521:xe";
		String db_id = "user01";
		String db_password = "1234";
		
		Connection conn = null;
		Statement stmt = null;
		String sql = null;
		
		try {
			//JDBC 수행 1단계 : Driver load
			Class.forName(db_driver);
			//JDBC 수행 2단계 : Connection 객체 생성
			conn = DriverManager.getConnection(db_url, db_id, db_password);
			//SQL 문 작성
			sql = "INSERT INTO test1(id,age) VALUES('SKY',30)" ;
			//JDBC 수행 3단계 : Statement 객체 생성
			stmt = conn.createStatement()	;
			//JDBC 수행 4단계 : SQL문을 실행해서 하나의 행을 삽입
			//					  : 삽입 작업 후 삽입한 행의 개수를 반환 
			int count = stmt.executeUpdate(sql);
			System.out.println(count + "개의 행을 추가했습니다.");
			
		} catch (Exception e) {
			e.printStackTrace();
		}finally {
			if (conn != null) try {conn.close();} catch(SQLException e) {}
			if (stmt != null) try {stmt.close();} catch(SQLException e) {}
		}
		
		
	}

}

Read Data In Table by Java

package kr.s31.jdbcstatement;

import java.sql.ResultSet;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;
import java.sql.Statement;

public class SelectMain {

	public static void main(String[] args) {
		String db_driver = "oracle.jdbc.OracleDriver";
		String db_url = "jdbc:oracle:thin:@localhost:1521:xe";
		String db_id = "user01";
		String db_password = "1234";
		
		Connection conn = null;
		Statement stmt = null;
		ResultSet rs = null;
		String sql = null;
		
		try {
			//JDBC 수행 1단계 : Driver load
			Class.forName(db_driver);
			//JDBC 수행 2단계 : Connection 객체 생성
			conn = DriverManager.getConnection(db_url, db_id, db_password);
			//SQL 문 작성
			sql = "SELECT * FROM test1";
			//JDBC 수행 3단계 :Statement 객체 생성
			stmt = conn.createStatement();
			//JDBC 수행 4단계: SQL문을 실행해서 테이블로부터 
            // 레코드(행)을 전달받아서 결과 집합을 만들고 RESULT SET객체에 담아서 반환
			rs = stmt.executeQuery(sql);
			
			System.out.println("ID\t나이");
			//ResultSet에 보관된 결과 집합에 접근하여 행 단위로 데이터 추출
			while(rs.next()) {
				//컬럼명을 통해서 데이터 반환
				/*
				System.out.print(rs.getString("id"));
				System.out.print("\t");
				System.out.println(rs.getInt("age"));
				*/
				//컬럼 인덱스를 통해서 데이터를 반환
				System.out.print(rs.getString(1));
				System.out.print("\t");
				System.out.println(rs.getInt(2));
			}
		}catch (Exception e) {
			e.printStackTrace();
		}finally {
			if (rs != null) try {rs.close();} catch(SQLException e) {}
			if (stmt != null) try {stmt.close();} catch(SQLException e) {}
		}
	}

}

Update Data in Table by Java

package kr.s31.jdbcstatement;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;
import java.sql.Statement;

public class UpdateMain {

	public static void main(String[] args) {

		String db_driver = "oracle.jdbc.OracleDriver";
		String db_url = "jdbc:oracle:thin:@localhost:1521:xe";
		String db_id = "user01";
		String db_password = "1234";
		
		Connection conn = null;
		Statement stmt = null;
		String sql = null;
		
		try {
			//JDBC 수행 1단계 : Driver load
			Class.forName(db_driver);
			//JDBC 수행 2단계 : Connection 객체 생성
			conn = DriverManager.getConnection(db_url,db_id, db_password);
			// SQL문 작성
			sql = "UPDATE test1 SET age =90 WHERE id='SKY'";
			stmt = conn.createStatement();
			//JDBC 수행 4단계 : SQL문을 실행해서 테이블의 행을 수정하고
			//					    수정한 행의 개수를 반환 
			int count = stmt.executeUpdate(sql);
			System.out.println(count + "개 행의 정보를 수정했습니다.");
			
		} catch (Exception e) {
			e.printStackTrace();
		}finally {// 자원 정리
			if (conn != null) try {conn.close();} catch(SQLException e) {}
			if (stmt != null) try {stmt.close();} catch(SQLException e) {}
		}
	}

}

Delete Data in Table by Java

package kr.s31.jdbcstatement;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;
import java.sql.Statement;

public class DeleteMain {

	public static void main(String[] args) {
		String db_driver = "oracle.jdbc.OracleDriver";
		String db_url = "jdbc:oracle:thin:@localhost:1521:xe";
		String db_id = "user01";
		String db_password = "1234";

		Connection conn = null;
		Statement stmt = null;
		String sql = null;
		
		try {
			//JDBC 수행 1단계 : Driver load
			Class.forName(db_driver);
			//JDBC 수행 2단계 : Connection 객체 생성
			conn = DriverManager.getConnection(db_url, db_id, db_password);
			//SQL문 작성
			sql = "DELETE FROM test1 WHERE id='SKY'";
			//JDBC 수행 3단계 : Statement 객체 생성
			stmt = conn.createStatement();
			//JDBC 수행 4단계 : SQL 문을 실행해서 테이블에서 행을 삭제한 후 삭제한
			//						행의 개수 반환
			int count = stmt.executeUpdate(sql);
			System.out.println(count + "개 행을 삭제했습니다.");
		}
		catch (Exception e) {
			e.printStackTrace();
		}finally {
			if (conn != null) try {conn.close();} catch(SQLException e) {}
			if (stmt != null) try {stmt.close();} catch(SQLException e) {}
		}
	}

}

PreparedStatement

Test2 테이블 생성

CREATE TABLE test2 (
id VARCHAR2(10) PRIMARY KEY,
name VARCHAR2(30) NOT NULL ,
age NUMBER(3),
rec_date DATE NOT NULL
);

DBUtil 메소드

package kr.util;

import java.sql.Connection;
import java.sql.CallableStatement;
import java.sql.SQLException;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.PreparedStatement;

public class DBUtil {
	
	private static final String DB_DRIVER = "oracle.jdbc.OracleDriver";
	private static final String DB_URl = "jdbc:oracle:thin:@localhost:1521:xe";
	private static final String DB_ID = "user01";
	private static final String DB_PASSWORD = "1234";
	
	//Connection 객체를 생성해서 반환
	public static Connection getConnection() throws ClassNotFoundException, SQLException{
		//JDBC 수행 1단계 : 드라이버 로드
		Class.forName(DB_DRIVER);
		//JDBC 수행 2단계 : Connection 객체 생성
		return DriverManager.getConnection(DB_URl,DB_ID,DB_PASSWORD);
	}
	//자원 정리
	public static void executeClose(ResultSet rs, PreparedStatement pstmt, Connection conn) {
		if(rs != null) try {rs.close();} catch(SQLException e) {}
		if(pstmt != null) try {pstmt.close();} catch(SQLException e) {}
		if(conn != null) try {conn.close();} catch(SQLException e) {}
	}
	public static void executeClose(CallableStatement cstmt, Connection conn) {
		if(cstmt != null) try {cstmt.close();} catch(SQLException e) {}
		if(conn != null) try {conn.close();} catch(SQLException e) {}
	}
}

DBUtil 메소드를 만들었기 때문에 Oracle을 매 파일마다 연결하지 않고 DBUtil을 import만 해줘도 연결이 가능하다.
자원정리 또한 DBUtil에 작성을 했기 때문에 DBUtil에 있는 내용을 불러와주면 된다.

Create Data -- Insert문

DBUtil 메소드를 받고, SQL문을 작성하여 테이블에 데이터 삽입하기

package kr.s32.jdbc.preparedstatement;

import java.sql.Connection;
import java.sql.PreparedStatement;
import kr.util.DBUtil;

public class InsertMain {
	public static void main(String[] args) {
			Connection conn = null;
			PreparedStatement pstmt = null;
			String sql = null;
			
			try {
				//JDBC 수행문 1, 2단계
				conn = DBUtil.getConnection();
				//SQL문 작성						1번? 2번?   3번?  4번SYSDATE  => 자리 잡기
				sql = "INSERT INTO test2 (id, name, age, reg_date) VALUES(?,?,?,SYSDATE)";
				//JDBC 수행 3단계 : PreparedStatement 객체 생성
				pstmt = conn.prepareStatement(sql);
				// ? 에 데이터를 바인딩 
				pstmt.setString(1, "wave"); // SQL문의 1번 물음표에 "star" 데이터를 삽입
				pstmt.setString(2, "이순신"); // SQL문의 2번 물음표에 "홍길동" 데이터를 삽입
				pstmt.setInt(3, 50); //SQL문의 3번 물음표에 20 데이터를 삽입
				
				//JDBC 수행 4단계 : SQL문을 실행해서 테이블에 행을 추가한다.
				int count = pstmt.executeUpdate(); // 이미 위에서 SQL을 전달했기 때문에 여기서는 SQL을 전달하면 안 된다.
				
				System.out.println(count + "개 행을 추가했습니다.");
				
			} catch (Exception e) {
				e.printStackTrace();
			}finally {
				//자원 정리
				DBUtil.executeClose(null, pstmt, conn);
			}
	}

}

Read Data -- Select문

DBUtil 메소드를 import 받고 Select문을 사용하여 Table을 읽어오기

package kr.s32.jdbc.preparedstatement;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import kr.util.DBUtil;

public class SelectMain {

	public static void main(String[] args) {
		Connection conn = null;
		PreparedStatement pstmt = null;
		String sql = null;
		ResultSet rs = null;
		
		try {//JDBC 수행문 1, 2단계
			conn = DBUtil.getConnection();
			
			//SQL문 작성
			sql = "SELECT * FROM test2 ORDER BY reg_date DESC";
			
			//JDBC 수행 3단계 --- SQL문 전달
			pstmt = conn.prepareStatement(sql);
			
			//JDBC 수행 4단계 : SQL 문장을 실행해서 테이블에 반영하고 
		    //			      결과 집합을 ResultSet에 담아서 반환하기
			rs = pstmt.executeQuery();
			
			System.out.println("ID\t이름\t나이\t등록일");
			while(rs.next()) {
				/*
				 * System.out.print(rs.getString(1)); System.out.print("\t");
				 * System.out.print(rs.getString(2)); System.out.print("\t");
				 * System.out.print(rs.getInt(3)); System.out.print("\t");
				 * System.out.println(rs.getDate(4));
				 */
				
				System.out.print(rs.getString("id"));
				System.out.print("\t");
				System.out.print(rs.getString("name"));
				System.out.print("\t");
				System.out.print(rs.getInt("age"));
				System.out.print("\t");
				//System.out.println(rs.getDate("reg_date")); --> 날짜 까지만 표시
				System.out.println(rs.getString("reg_date")); //시:분:초까지 모두 표시
			}
			
		} catch (Exception e) {
			e.printStackTrace();
		}finally {
			//자원 정리
			DBUtil.executeClose(rs, pstmt, conn);
		}
	}

}

Update Data -- Update 문

DBUtil을 import 받고 Update문을 이용하여 테이블의 데이터를 교체시킨다.

package kr.s32.jdbc.preparedstatement;

import java.sql.Connection;
import java.sql.PreparedStatement;
import kr.util.DBUtil;


public class UpdateMain {

	public static void main(String[] args) {
		
		Connection conn = null;
		PreparedStatement pstmt = null;
		String sql = null;

		try {
			//JDBC 수행문 1,2 단계
			conn= DBUtil.getConnection();
			//SQL문 작성
			sql = "UPDATE test2 SET name=?, age=? WHERE id=?";
			//JDBC 수행 3단계 : PreparedStatement 객체 생성
			pstmt = conn.prepareStatement(sql);
			
			//?에 데이터를 바인딩한다
			pstmt.setString(1, "강호동"); 
            // 1번의 물음표 NAME에 해당 => 바꿀 이름 입력
            
			pstmt.setInt(2, 40);
            // 2번의 물음표 AGE에 해당 => 바꿀 나이 입력
            
			pstmt.setString(3, "sky");  
            // 3번의 물음표 ID에 해당 => 위치 지정
			
			//JDBC 수행 4단계 : SQL문을 실행해서 테이블의 데이터를 수정한다.
			int count = pstmt.executeUpdate();
			System.out.println(count + "개 행의 정보를 수정했습니다.");
		} 
		catch (Exception e) {
			e.printStackTrace();
		}finally {
			//자원 정리
			DBUtil.executeClose(null, pstmt, conn);
		}
	}

}
profile
먼지
Lucky Things🍀
이전 포스트

TIL 0319

다음 포스트

TIL 0321

0개의 댓글