Study Web Development

11월 30일

이전으로

PL/SQL

6. PL/SQL 서브프로그램

6-2 내장 프로시저(Stored Procedure)

• 부서 번호를 통해서 부서명과 부서의 위치 구하기

CREATE OR REPLACE PROCEDURE output_department(deptno_i IN dept.deptno%TYPE)
AS
	-- 변수 선언
	l_dname dept.dname%TYPE;
	l_loc dept.loc%TYPE;
BEGIN
	-- 파라미터 변수로부터 부서 번호를 받아서 해당 부서의 정보 얻기
	SELECT dname, loc INTO l_dname, l_loc FROM dept WHERE deptno=deptno_i;
	DBMS_OUTPUT.PUT_LINE(l_dname || ', '|| l_loc);
END;

EXEC OUTPUT_DEPARTMENT(10);

• SELECT INTO v. CURSOR

-- SELECT INTO
-- SELECT로 반환되는 행이 단일 행인 경우에만 처리 가능
CREATE OR REPLACE PROCEDURE info_hiredate(p_year IN VARCHAR2)
AS
	-- %ROWTYPE으로 emp 테이블의 한 행이 갖는 모든 컬럼의 자료형을 가져옴
	e_emp emp%ROWTYPE;
BEGIN
	SELECT empno, ename, sal INTO e_emp.empno, e_emp.ename, e_emp.sal
	FROM emp WHERE TO_CHAR(hiredate, 'YYYY')=p_year;
	
	DBMS_OUTPUT.PUT_LINE(e_emp.empno || ',' || e_emp.ename || ',' || e_emp.sal);
END;

-- 하나의 행이 반환되어 에러가 발생하지 않음
EXEC INFO_HIREDATE('1980');
-- 여러 개의 행이 반환되어 에러 발생
EXEC INFO_HIREDATE('1981');

-- 커서(CURSOR)
-- 질의 수행 결과 반환되는 여러 행을 처리 가능
CREATE OR REPLACE PROCEDURE info_hiredate(p_year IN VARCHAR2)
IS
	-- %ROWTYPE으로 데이터 타입이 지정되어 있는 사원 테이블의 하나의 행이 가지는 모든 컬럼의 데이터 타입을 가져옴
	e_emp emp%ROWTYPE;
	-- 커서 선언
	CURSOR emp_cur IS
		SELECT empno, ename, sal FROM emp WHERE TO_CHAR(hiredate, 'YYYY')=p_year;
BEGIN
	-- 커서 열기
	OPEN emp_cur;
	
	LOOP
		-- 커서로부터 데이터 읽기
		FETCH emp_cur INTO e_emp.empno, e_emp.ename, e_emp.sal;
		
		-- 더 이상 읽을 행이 없으면 루프를 빠져나감
		EXIT WHEN emp_cur%NOTFOUND;
		
		-- 읽어온 데이터 출력
		DBMS_OUTPUT.PUT_LINE(e_emp.empno || ', ' || e_emp.ename || ', ' || e_emp.sal);
	END LOOP;
	
	-- 커서 닫기
	CLOSE emp_cur;
END;

EXEC INFO_HIREDATE('1980');
EXEC INFO_HIREDATE('1981');

• SALES 부서에 속한 사원의 정보 보기

CREATE OR REPLACE PROCEDURE emp_info(dname_i dept.dname%TYPE)
AS
	-- 커서 선언
	CURSOR emp_csr IS
		SELECT empno, ename FROM emp
		WHERE deptno=(SELECT deptno FROM dept WHERE dname=UPPER(dname_i));
	-- 변수 선언
	l_empno emp.empno%TYPE;
	l_ename emp.ename%TYPE;
BEGIN
	-- 커서 열기
	OPEN emp_csr;
	
	LOOP
		FETCH emp_csr INTO l_empno, l_ename;
		
		-- 더 이상 읽을 행이 없으면 루프를 빠져나감
		EXIT WHEN emp_csr%NOTFOUND;
		
		-- 읽어온 데이터 출력
		DBMS_OUTPUT.PUT_LINE(l_empno || ', ' || l_ename);
	END LOOP;
	
	-- 커서 닫기
	CLOSE emp_csr;
END;

EXEC EMP_INFO('sales');

• 실습 문제

-- 1) 업무를 입력하면 해당 업무를 수행하는 사원들의 사원 번호, 이름, 급여, 업무를 출력하시오
-- 프로시저 정의
-- %ROWTYPE을 사용하는 경우
CREATE OR REPLACE PROCEDURE job_info(job_i IN emp.job%TYPE)
AS
	CURSOR c_emp IS
		SELECT empno, ename, sal FROM emp
		WHERE job=UPPER(job_i);
	r_emp emp%ROWTYPE;
BEGIN
	OPEN c_emp;
	LOOP
		FETCH c_emp INTO r_emp.empno, r_emp.ename, r_emp.sal;
		EXIT WHEN c_emp%NOTFOUND;
		DBMS_OUTPUT.PUT_LINE(r_emp.empno || ', ' || r_emp.ename || ', ' ||
			r_emp.sal || ', ' || UPPER(job_i));
	END LOOP;
	CLOSE c_emp;
END;
-- %TYPE을 사용하는 경우
CREATE OR REPLACE PROCEDURE job_info(job_i IN emp.job%TYPE)
AS
	CURSOR c_emp IS
		SELECT empno, ename, sal FROM emp
		WHERE job=UPPER(job_i);
	l_empno emp.empno%TYPE;
	l_ename emp.ename%TYPE;
	l_sal emp.sal%TYPE;
BEGIN
	OPEN c_emp;
	LOOP
		FETCH c_emp INTO l_empno, l_ename, l_sal;
		EXIT WHEN c_emp%NOTFOUND;
		DBMS_OUTPUT.PUT_LINE(l_empno || ', ' || l_ename || ', ' ||
			l_sal || ', ' || UPPER(job_i));
	END LOOP;
	CLOSE c_emp;
END;
-- 프로시저 실행
EXEC JOB_INFO('clerk');
-- 2) 사원 번호와 새 업무를 입력하면 emp 테이블의 해당 사원의 업무를 갱신할 수 있는 프로시저 CHANGE_JOB을 작성하시오
-- 프로시저 정의
CREATE OR REPLACE PROCEDURE change_job(empno_i emp.empno%TYPE, job_i emp.job%TYPE)
AS
BEGIN
	UPDATE emp SET job=UPPER(job_i) WHERE empno=empno_i;
	COMMIT;
EXCEPTION WHEN OTHERS THEN
	DBMS_OUTPUT.PUT_LINE(empno_i || ' 갱신 실패');
	ROLLBACK;
END;
-- 프로시저 실행
EXEC CHANGE_JOB(7369, 'driver');
-- 3) 부서 이름을 입력받으면 해당 부서의 사원에 대해 급여가 많은 순으로 정보를 제공하는 프로시저 EMP_SALARY_INFO를 작성하시오
-- 프로시저 정의
CREATE OR REPLACE PROCEDURE emp_salary_info(dname_i dept.dname%TYPE)
AS
	CURSOR c_emp IS
		SELECT empno, ename, sal FROM emp JOIN dept USING(deptno)
		WHERE dname=UPPER(dname_i) ORDER BY sal DESC;
	r_emp emp%ROWTYPE;
BEGIN
	OPEN c_emp;
		LOOP
			FETCH c_emp INTO r_emp.empno, r_emp.ename, r_emp.sal;
			EXIT WHEN c_emp%NOTFOUND;
			DBMS_OUTPUT.PUT_LINE(r_emp.empno || ', ' ||
			r_emp.ename || ', ' || r_emp.sal);
		END LOOP;
	CLOSE c_emp;
END;
-- 프로시저 실행
EXEC EMP_SALARY_INFO('accounting');

자바

17. JDBC

17-2 JDBC(Java Database Connectivity)

• 자바 프로그램 내에서 데이터베이스로 접근하여 SQL문을 수행할 수 있도록 하는 API
• JDBC 인터페이스
  • DBMS의 종류와 관계없이 동일한 방식으로 데이터베이스 관련 작업을 처리할 수 있도록 표준화한 규격
  • java.sql 패키지와 javax.sql 패키지에 포함된 인터페이스
• JDBC 드라이버
  • 자바 프로그램과 DBMS 사이의 통신을 담당(=번역)하는 클래스로, JDBC 인터페이스를 구현
  • 각각의 DBMS별로 JDBC 드라이버를 제공하며, 일반적으로 .jar 파일
  • Oracle
    • oracle.jdbc.OracleDriver
    • jdbc:oracle:thin:@호스트명[:1521][:DB명]
  • SQL Server
    • com.microsoft.jdbc.sqlserver.SQLServerDriver
    • jdbc:microsoft:sqlserver://호스트명[:1433];DatabaseName=DB명
  • MySQL
    • com.mysql.jdbc.Driver
    • jdbc:mysql://호스트명[:3306][/DB명]
  • MariaDB
    • org.mariadb.jdbc.Driver
    • jdbc:mariadb://호스트명[:3306][/DB명]

1. 새 자바 프로젝트 ch15-jdbc 생성하고, 프로젝트를 오른쪽 클릭하여 New-Folder 선택 후 libs 폴더 생성
2. libs 폴더로 ojdbc8.jar 이동
3. 프로젝트를 오른쪽 클릭하여 Build Path-Configure Build Path... 선택 후 Libraries 탭 선택
4. Add JARs 선택하고 ojdbc8.jar 찾아 선택 후 OK, Apply and Close
5. 프로젝트에서 Referenced Libraries 하위에 ojdbc8.jar이 추가된 것 확인
6. 새 패키지 kr.s01.basic 생성하고 새 클래스 DriverMain 생성

package kr.s01.basic;

public class DriverMain {
	public static void main(String[] args) {
		try {
			// JDBC 수행 1단계 : 드라이버 로드
			Class.forName("oracle.jdbc.OracleDriver"); // 입력한 문자열을 이름으로 갖는 클래스를 찾아 메모리에 로드
			System.out.println("드라이버가 정상적으로 설치되었습니다.");
		}
		catch(ClassNotFoundException e) {
			e.printStackTrace();
		}
	}
}

7. 새 클래스 ConnectionMain 생성

package kr.s01.basic;

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

public class ConnectionMain {
	public static void main(String[] args) {
		String db_driver = "oracle.jdbc.OracleDriver";
		String db_url = "jdbc:oracle:thin:@localhost:1521:xe"; // Oracle로 연결, Type 4 드라이버인 thin 사용, @호스트명(=IP 주소나 도메인으로 대체 가능), 포트 번호 1521, Oracle Database 설치시 부여되는 SID명 XE
		String db_id = "scott";
		String db_password = "tiger";

		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();
		}
	}
}

8. 새 패키지 kr.s02.statement 생성하고 새 클래스 CreateTableMain 생성

package kr.s02.statement;

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

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 = "scott";
		String db_password = "tiger";
		
		Connection conn = null;
		Statement stmt = null;
		String sql = null;
		// 데이터베이스 연동시에는 메서드에서도 throws 선언하지 않고 항상 try~catch~finally문을 사용하여 자원 정리
		try {
			// JDBC 수행 1단계 : 드라이버 로드
			Class.forName(db_driver);
			// JDBC 수행 2단계 : Connection 객체 생성(ID, 비밀번호 인증)
			conn = DriverManager.getConnection(db_url, db_id, db_password);
			System.out.println("데이터베이스에 연결되어 test1 테이블을 생성합니다.");
			// JDBC 수행 3단계 : Statement 객체 생성
			stmt = conn.createStatement();

			/*
			 * 테이블을 생성하는 SQL문
			 * 접속한 계정에 테이블명이 중복되면 에러가 발생하기 때문에 동일 계정에서는 한 번만 수행해야 함
			 */
			sql = "CREATE TABLE test1 (id VARCHAR2(10), age NUMBER)"; // SQL문을 세미콜론으로 종결하면 에러

			// 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) {}
		}
	}
}

9. 새 클래스 InsertMain 생성

package kr.s02.statement;

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

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 = "scott";
		String db_password = "tiger";
		
		Connection conn = null;
		Statement stmt = null;
		String sql = null;
		
		try {
			// JDBC 수행 1단계 : 드라이버 로드
			Class.forName(db_driver);
			// JDBC 수행 2단계 : Connection 객체 생성(ID, 비밀번호 인증)
			conn = DriverManager.getConnection(db_url, db_id, db_password);
			// JDBC 수행 3단계 : Statement 객체 생성
			stmt = conn.createStatement();
			
			// SQL문 작성
			// sql = "INSERT INTO test1 (id, age) VALUES ('se't', 10)"; // 문자열 내에 ' 포함시 에러
			sql = "INSERT INTO test1 (id, age) VALUES ('se''t', 10)"; // 문자열 내에 ' 포함하고 싶으면 '' 사용
			
			// JDBC 수행 4단계 : SQL문을 실행해서 테이블에 행을 삽입
			int count = stmt.executeUpdate(sql); // 삽입 작업 후 삽입한 행의 수를 반환
			System.out.println(count + "개 행이 삽입되었습니다.");
		}
		catch(Exception e) {
			e.printStackTrace();
		}
		finally {
			if(stmt!=null) try {stmt.close();} catch(SQLException e) {}
			if(conn!=null) try {conn.close();} catch(SQLException e) {}
		}
	}
}

10. 새 클래스 SelectMain 생성

package kr.s02.statement;

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

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 = "scott";
		String db_password = "tiger";
		
		Connection conn = null;
		Statement stmt = null;
		ResultSet rs = null;
		String sql = null;
		
		try {
			// JDBC 수행 1단계 : 드라이버 로드
			Class.forName(db_driver);
			// JDBC 수행 2단계 : Connection 객체 생성(ID, 비밀번호 인증)
			conn = DriverManager.getConnection(db_url, db_id, db_password);
			// JDBC 수행 3단계 : Statement 객체 생성
			stmt = conn.createStatement();
			
			// SQL문 작성
			sql = "SELECT * FROM test1";
			
			// JDBC 수행 4단계 : SQL문을 실행해서 테이블로부터 행을 전달받아서 결과 집합을 만들고, ResultSet 객체에 담아서 반환
			rs = stmt.executeQuery(sql);

			System.out.println("ID\t나이");
			while(rs.next()) { // ResultSet에서 결과 집합의 행에 접근할 때 커서를 사용하는데, next() 메서드는 커서를 다음 행으로 이동시키고 다음 행이 존재하면 true 반환
				// 컬럼명을 통해 데이터를 반환
				System.out.print(rs.getString("id")); // getString() 메서드는 입력한 컬럼명과 매칭되어 있는 데이터를 String 자료형으로 반환
				System.out.print("\t");
				System.out.println(rs.getInt("age")); // getInt() 메서드는 입력한 컬럼명과 매칭되어 있는 데이터를 int 자료형으로 반환
				
				// 컬럼 인덱스를 통해 데이터를 반환
				// System.out.print(rs.getString(1)); // SELECT문에서 전체(*)를 반환한 경우 테이블 생성시의 컬럼 인덱스를 사용할 수 있지만, 그 외의 경우 컬럼 인덱스가 변동될 수 있으므로 주의
				// 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) {}
			if(conn!=null) try {conn.close();} catch(SQLException e) {}
		}
	}
}

다음으로