Database
11.24.(금) OracleDB(20): Eclipse+PL/SQL
콜라든포비
2022. 11. 29. 23:31
Eclipse에서 프로시저 실행
DB를 호출하는 방식은 이전과 동일하게 미리 만들어둔 DBConn 클래스를 상속받아서 연결할 예정이다.
마지막으로 생성했던 employee_insert 프로시저를 이클립스에서 실행시켜서 DB에 데이터를 추가해보자.
CREATE OR REPLACE PROCEDURE employee_insert(
i_username IN employee.username%TYPE,
i_password IN employee.password%TYPE,
i_tel IN employee.tel%TYPE,
i_dept_code IN employee.dept_code%TYPE,
i_pos_code IN employee.pos_code%TYPE,
o_result OUT NUMBER) -- insert문 처리결과 내보내기
IS
BEGIN
o_result := 1; -- o_result변수에 1대입
INSERT INTO employee(empno, username, password, tel, dept_code, pos_code)
VALUES (emp_seq.nextval, i_username, i_password, i_tel, i_dept_code, i_pos_code);
-- 예외처리
EXCEPTION
WHEN OTHERS THEN
o_result := 0; -- 예외발생 시 데이터추가 실패
END;Eclipse에서 DB연결
DB에 데이터를 INSERT하기
package javaJDBCProcedure;
import java.sql.Types;
import java.util.Scanner;
import javaJDBC.DBConn;
public class InsertProcedure extends DBConn{
Scanner sc = new Scanner(System.in);
public InsertProcedure() {}
public void start() {
try {
// 데이터 준비
// 사원명, 비밀번호, 연락처, 부서코드, 직급코드를 입력받아
System.out.print("사원명 : ");
String username = sc.nextLine();
System.out.print("비밀번호 : ");
String password = sc.nextLine();
System.out.print("연락처 : ");
String tel = sc.nextLine();
System.out.print("부서코드 : ");
int dept_code = Integer.parseInt(sc.nextLine());
System.out.print("직급코드 : ");
int pos_code = Integer.parseInt(sc.nextLine());
// 1. 드라이버 로딩 - DBConn 상속하면서 이미 처리됨
// 2. DB연결
dbConn();
// 3. Statement 생성
// 프로시저 호출은 {}내에 표시한다.
sql = "{CALL employee_insert(?,?,?,?,?,?)}";
cstmt = conn.prepareCall(sql);
// 데이터를 statement객체에 할당
cstmt.setString(1, username);
cstmt.setString(2, password);
cstmt.setString(3, tel);
cstmt.setInt(4, dept_code);
cstmt.setInt(5, pos_code);
// 프로시저 처리결과를 out받기 위한 위치에 데이터형을 정의한다
cstmt.registerOutParameter(6, Types.INTEGER);
// 4. 실행
cstmt.executeUpdate();
if(cstmt.getInt(6)>0) { // 사원등록 성공
System.out.println(username + "사원을 등록하였습니다.");
}else { // 사원등록 실패
System.out.println("사원등록을 실패하였습니다.");
}
}catch(Exception e) {
e.printStackTrace();
}finally {
// 5. 종료
dbClose();
}
}
public static void main(String[] args) {
new InsertProcedure().start();
}
}여기서 주목해야할 점은, 이전과는 달리 PL/SQL의 OUT PARAMETER, 즉 쿼리문의 리턴값을 담아줄 변수를 추가해줘야 한다는 것이다.
입력값은 CallStatement의 setString()과 setInt()를 통해 할당해주면 된다. 하지만 맨 마지막 ?는 OUT PARAMETER이기 때문에,
cstmt.registerOutParameter(int index, data_type);우리는 java.sql.Types 클래스를 통해서 o_result의 데이터타입을 정해줄 수 있다.
o_result의 데이터타입은 NUMBER이기 때문에, Types.INTEGER로 정했다.
o_result는 employee_insert()프로시저가 데이터입력 성공여부를 알려주는 정수이므로,
0이면 실패, 1이면 성공이라는 조건에 따라 사원등록 성공/실패를 콘솔에 출력시켰다.
DB에서 데이터를 SELECT하기
모든 데이터(사번, 이름, 연락처, 성별, 입사일)를 선택하는 프로시저를 만들자.
SELECT empno, username, tel, startwork FROM employee;
CREATE OR REPLACE PROCEDURE employee_select_all(o_result OUT sys_refcursor)
IS
BEGIN
OPEN o_result FOR
SELECT empno, username, tel, gender, startwork FROM employee ORDER BY empno;
END;2개 이상의 필드에서 SELECT할때는 커서를 이용해야한다.
커서의 데이터타입은 sys_refcursor이다. 또한 OPEN 커서 FOR으로 어떠한 데이터를 가리킬지 정해준다.
이제 Eclipse로 넘어와서 테이블을 출력해보자.
package javaJDBCProcedure;
import java.sql.ResultSet;
import javaJDBC.DBConn;
import oracle.jdbc.OracleTypes;
public class SelectAllProcedure extends DBConn {
public SelectAllProcedure() {}
public void start() {
try {
dbConn();
sql = "{CALL employee_select_all(?)}";
cstmt = conn.prepareCall(sql);
// ?에 데이터타입 커서를 담을 수 있게 설정
cstmt.registerOutParameter(1, OracleTypes.CURSOR);
cstmt.executeUpdate();
// cstmt에서 ResultSet객체 얻어오기
rs = (ResultSet) cstmt.getObject(1);
// 사번, 이름, 연락처, 성별, 입사일
while(rs.next()) {
System.out.printf("%d\t %s\t %s\t %s\t %s\n",
rs.getInt(1),
rs.getString(2),
rs.getString(3),
rs.getString(4),
rs.getString(5));
}
}catch(Exception e) {
e.printStackTrace();
}finally {
dbClose();
}
}
public static void main(String[] args) {
new SelectAllProcedure().start();
}
}커서는 java.sql.Types클래스에 없기 때문에 다른 곳에서 import해와야한다. 커서의 데이터형은 Build Path에 추가한 ojdbc.jar라이브러리에 존재한다.
import oracle.jdbc.OracleTypes;OracleTypes.CURSOR로 OUT PARAMETER에 담아주자.
그리고 쿼리문 실행 이후엔 커서가 가리키는 열의 데이터 집합을 ResultSet rs에 담아주고,
커서가 가리키는게 없을 때 까지 while문으로 하나하나씩 콘솔에 출력해주었다.
데이터 검색하기
이번엔 이름에 특정 알파벳이 있는 사람들만 골라서 SELECT해볼 예정이다.
CREATE OR REPLACE PROCEDURE employee_search(
name_search IN employee.username%TYPE,
r_employee OUT sys_refcursor)
IS
BEGIN
OPEN r_employee FOR
SELECT empno, username, tel, startwork, dept_name, pos_name
FROM employee
JOIN department ON employee.dept_code=department.dept_code
JOIN position ON employee.pos_code=position.pos_code
WHERE username LIKE '%'||name_search||'%'; -- A가 이름에 포함된 사원들
END;package javaJDBCProcedure;
import java.io.BufferedReader;
import java.io.InputStreamReader;
import java.sql.ResultSet;
import javaJDBC.DBConn;
import oracle.jdbc.OracleTypes;
public class SelectSearchProcedure extends DBConn{
BufferedReader br = new BufferedReader(new InputStreamReader(System.in));
public SelectSearchProcedure() {
// 이름을 입력받아 해당 사원을 선택하는 프로시저 만들기
// 홍을 입력하면 홍길동을 출력
}
public void start() {
try {
dbConn();
System.out.print("검색할 이름 입력 -> ");
String searchName = br.readLine();
sql = "{CALL employee_search(?,?)}";
cstmt = conn.prepareCall(sql);
cstmt.setString(1, searchName);
cstmt.registerOutParameter(2, OracleTypes.CURSOR);
cstmt.executeUpdate();
rs = (ResultSet)cstmt.getObject(2);
// empno, username, tel, startwork, dept_name, pos_name
while(rs.next()) {
System.out.printf("%d\t %s\t %s\t %s\t %s\t %s\n",
rs.getInt(1),
rs.getString(2),
rs.getString(3),
rs.getString(4),
rs.getString(5),
rs.getString(6));
}
}catch(Exception e) {
e.printStackTrace();
}finally {
dbClose();
}
}
public static void main(String[] args) {
SelectSearchProcedure ssp = new SelectSearchProcedure();
ssp.start();
}
}employee_select_all()과 거의 비슷하다. BufferedReader로 검색값을 받아서 employee_search()의 입력값으로 넣어줬다.
데이터 UPDATE하기
CREATE OR REPLACE PROCEDURE employee_edit(
i_empno IN employee.empno%TYPE,
i_password IN employee.password%TYPE,
i_tel IN employee.tel%TYPE,
i_endwork IN VARCHAR2, -- yyyymmdd 형식으로 받기
r_data OUT NUMBER)
IS
BEGIN
r_data:=1;
UPDATE employee SET
password=i_password,
tel=i_tel,
endwork=to_date(i_endwork, 'yyyymmdd')
WHERE empno=i_empno;
EXCEPTION
WHEN OTHERS THEN
r_data:=0;
END;수정 성공 여부를 r_data로 리턴받아서 잘 되었는지 안 되었는지 알 수 있다.
package javaJDBCProcedure;
import java.sql.Types;
import java.util.Scanner;
import javaJDBC.DBConn;
public class UpdateProcedure extends DBConn {
Scanner sc = new Scanner(System.in);
public UpdateProcedure() {
//
}
public void start() {
try {
System.out.print("수정 대상 사원번호 -> ");
int empno = Integer.parseInt(sc.nextLine());
System.out.print("비밀번호 -> ");
String password = sc.nextLine();
System.out.print("새 연락처 (ex.010-1234-5678)-> ");
String tel = sc.nextLine();
System.out.print("퇴사일 (ex.20220425) -> ");
String endwork = sc.nextLine();
dbConn();
sql = "{CALL employee_edit(?,?,?,?,?)}";
cstmt = conn.prepareCall(sql);
cstmt.setInt(1, empno);
cstmt.setString(2, password);
cstmt.setString(3, tel);
cstmt.setString(4, endwork);
cstmt.registerOutParameter(5, Types.INTEGER);
cstmt.executeUpdate();
if(cstmt.getInt(5)>0) {
System.out.println("사원 정보가 수정되었습니다.");
}else {
System.out.println("사원 정보 수정을 실패했습니다.");
}
}catch(Exception e) {
e.printStackTrace();
}finally {
dbClose();
}
}
public static void main(String[] args) {
new UpdateProcedure().start();
}
}데이터 DELETE하기
CREATE OR REPLACE PROCEDURE employee_del(
i_empno IN NUMBER,
o_result OUT NUMBER)
IS
BEGIN
o_result:=1;
DELETE FROM employee
WHERE empno=i_empno;
EXCEPTION
WHEN OTHERS THEN
o_result:=0;
END;package javaJDBCProcedure;
import java.sql.Types;
import java.util.Scanner;
import javaJDBC.DBConn;
public class DeleteProcedure extends DBConn {
Scanner sc = new Scanner(System.in);
public DeleteProcedure() {
// 사원번호를 입력받아 해당 사원 삭제하는 프로시저 실행
}
public void start() {
try {
System.out.print("삭제할 사원의 사원번호 -> ");
int empno = Integer.parseInt(sc.nextLine());
dbConn();
sql = "{CALL employee_del(?,?)}";
cstmt = conn.prepareCall(sql);
cstmt.setInt(1, empno);
cstmt.registerOutParameter(2, Types.INTEGER);
cstmt.executeUpdate();
if(cstmt.getInt(2)>0) {
System.out.printf("사원번호 %d 삭제되었습니다.\n", empno);
}else {
System.out.println("사원 삭제를 실패했습니다.");
}
}catch(Exception e) {
e.printStackTrace();
}finally {
dbClose();
}
}
public static void main(String[] args) {
new DeleteProcedure().start();
}
}