국비지원 36일차 - PreparedStatement, MVC패턴 사원관리 프로그램, CURSOR, 암시적 CURSOR, PL/SQL 오류의 종류, function

2020. 6. 4. 12:52

Prepared Statement

PreparedStatement는 미리 컴파일 시켜놓을 sql문을 먼저 작성한 뒤에 해당 sql문을 넘겨 할당받는다.
Connection의 prepareStatement(String sql)메소드를 이용

package jdbc.util;

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

public class DBUtil {
	
	private static String url="jdbc:oracle:thin:@localhost:1521:XE";
	private static String user="scott",pwd="tiger";
	
	
	static {
		//static 블럭은 클래스 로딩 타입에 가장 먼저 메모리에 올라가는 영역
		try {
			Class.forName("oracle.jdbc.driver.OracleDriver");
			System.out.println("Driver Loading Success...");
		} catch (ClassNotFoundException e) {
			e.printStackTrace();
		}
	}//static initializer
	
	public static Connection getCon() throws SQLException
	{
		Connection con = DriverManager.getConnection(url,user,pwd);
		return con;
	}
}

ㅡㅡㅡㅡㅡㅡㅡㅡㅡㅡㅡㅡㅡㅡㅡㅡㅡㅡㅡㅡㅡㅡㅡㅡㅡㅡㅡㅡㅡㅡㅡㅡㅡㅡㅡ
package jdbc.util;

import jdbc.util.*;
import java.sql.*;


public class PreparedStatementTest{
	public static void main(String[] args) throws SQLException{
		Connection con = DBUtil.getCon();
		System.out.println("con :"+con);
		String sql ="INSERT INTO memo(idx,name,msg,wdate)"
				+" VALUES(memo_seq.nextval, ?,?,sysdate)";
		//? : in parameter
		PreparedStatement pstmt = con.prepareStatement(sql);
		
		//? 인 파라미터 값 셋팅
		pstmt.setString(1, "김개똥");
		pstmt.setString(2, "즐거운 하루");	
		
		//dml문장==> executeUpdate()
		int cnt = pstmt.executeUpdate();
		System.out.println(cnt+"개의 레코드가 삽입되었습니다.");
		pstmt.close();
		con.close();
	}
}

PrepareStatement를 이용한 UPDATE문 예시

package jdbc.util;

import jdbc.util.*;
import java.sql.*;
import java.util.*;

public class PreparedStatementTest2 {
	public static void main(String[] args) 
	throws Exception{
		Scanner sc = new Scanner(System.in);
		System.out.println("수정할 글번호 입력=>");
		int idx = sc.nextInt();
		System.out.println("작성자=>");
		String name=sc.next();
		sc.skip("\r\n");
		System.out.println("메모 내용=>");
		String msg=sc.nextLine();
		System.out.println(idx+"/"+name+"/"+msg);
		
		Connection con = DBUtil.getCon();
		
		String sql = "UPDATE MEMO SET NAME=?, MSG=? WHERE IDX=?";
		PreparedStatement pstmt = con.prepareStatement(sql);
		
		pstmt.setString(1, name);
		pstmt.setString(2, msg);
		pstmt.setInt(3, idx);
		
		int cnt = pstmt.executeUpdate();
		System.out.println(cnt+"정상적으로 수정되었습니다.");
		
		pstmt.close();
		con.close();
	}
}

EMP테이블에서 이름을 입력하면 해당 사원의 사번, 이름, 업무, 급여, 보너스, 부서명, 근무지를 가져와 출력하는 프로그램을 작성
- JOIN문 활용
- PreParedStatement
select문이면 ResultSet executeQuery()

package jdbc.util;

import jdbc.util.*;
import java.sql.*;
import java.util.*;


public class EmpSelect {
	public static void main(String[] args) 
			throws Exception
	{
		Scanner sc = new Scanner(System.in);
		System.out.println("검색할 사원명을 입력하세요=>");
		String name = sc.next();
		
		Connection con = DBUtil.getCon();
		String sql="select e.empno, e.ename, e.job, e.sal, e.comm, d.dname, d.loc\r\n" + 
				"from emp e left join dept d \r\n" + 
				"on e.deptno=d.deptno where e.ename=upper(?)";
		
		PreparedStatement pstmt = con.prepareStatement(sql);
		pstmt.setString(1, name);
		
		ResultSet rs = pstmt.executeQuery();
		System.out.println("사번\t사원명\t업무\t급여\t보너스\t직무\t근무지");
		while(rs.next()) {
			for(int i=1;i<=7;i++) {
				System.out.print(rs.getString(i)+"\t");
			}
			System.out.println();
		}
		
		rs.close();
		pstmt.close();
		con.close();
		System.out.println("종료");
	}
}

MVC패턴으로 JDBC
실제 업무에서는 main에서 다 만들지 않고 메소드로 만들고 이용하는 것이 더 중요하다.
MVC패턴으로 작성하면 유지보수하기 좋다.
Model ==> 데이터를 가지고 있는 클래스
View ==> UI ==> Swing, JSP, Android UI
Contriller ==> 핸들러, 제어하는 역할의 클래스

사원관리 프로그램
1) Model
- EmpVO ==> Value Object
사용자가 입력한 값을 담거나, 데이터베이스에서 가져온 값을 담고 있는 객체
- EmpDAO ==> Data Access Object
데이터베이스에 접근하여 CRUD 기능을 수행하는 클래스
( C : insert, R: select, U : update, D : delete)
2) View ==> Swing (일단은 swing)
3) Controller ==> 이벤트 핸들러, 컨트롤러 클래스

DMPVO

package jdbc.emp;

import java.sql.Date;
//Domain객체 -> 값을 담고있는 객체.(Value Object)
public class EmpVO 
{
	private int empno;
	private String ename;
	private String job;
	private int mgr;
	private Date hiredate;
	private int sal;
	private int comm;
	private int deptno;
	
	public EmpVO() {
		this(0,"김사원",null,0,null,0,0,0);
	}
	public EmpVO(int empno, String ename, String job, int mgr,
			Date hdate, int sal, int comm, int deptno) {
		this.setEmpno(empno); this.setEname(ename); this.setJob(job);
		this.setMgr(mgr); setHiredate(hdate); this.setSal(sal);
		this.setComm(comm); this.setDeptno(deptno);
	}
	public int getEmpno() {
		return empno;
	}
	public void setEmpno(int empno) {
		this.empno = empno;
	}
	public String getEname() {
		return ename;
	}
	public void setEname(String ename) {
		this.ename = ename;
	}
	public String getJob() {
		return job;
	}
	public void setJob(String job) {
		this.job = job;
	}
	public int getMgr() {
		return mgr;
	}
	public void setMgr(int mgr) {
		this.mgr = mgr;
	}
	public Date getHiredate() {
		return hiredate;
	}
	public void setHiredate(Date hiredate) {
		this.hiredate = hiredate;
	}
	public int getSal() {
		return sal;
	}
	public void setSal(int sal) {
		this.sal = sal;
	}
	public int getComm() {
		return comm;
	}
	public void setComm(int comm) {
		this.comm = comm;
	}
	public int getDeptno() {
		return deptno;
	}
	public void setDeptno(int deptno) {
		this.deptno = deptno;
	}
}

EMPDAO

package jdbc.emp;
import java.sql.*;
import java.util.*;

import jdbc.util.DBUtil;
/*DAO ==> Data Access Object
 * CRUD기능을 수행 
 */
public class EmpDAO {
	private Connection con;
	private PreparedStatement ps;
	private ResultSet rs;
	
	//사원 정보를 등록하는 메소드 - INSERT 문을 수행
	public boolean insertEmp(EmpVO e){
		try {
			con = DBUtil.getCon();
			String sql = "insert into emp2(empno, ename, job, deptno, sal, comm, mgr, hiredate)";
			sql+=" values(emp2_seq.nextval,?,?,?,?,?,?,sysdate)";
			ps=con.prepareStatement(sql);
			ps.setString(1, e.getEname());
			ps.setString(2, e.getJob());
			ps.setInt(3, e.getDeptno());
			ps.setInt(4, e.getSal());
			ps.setInt(5, e.getComm());
			ps.setInt(6, e.getMgr());
			int n = ps.executeUpdate();
			boolean b=(n>0) ? true:false;
			return b;
		} catch (SQLException ex) {
			ex.printStackTrace();
			return false;
		} finally {
			close();
		}
	}
	//모든 사원정보를 db에서 가져오는 메소드
	public ArrayList<EmpVO> selectEmpAll(){
		try {
			con = DBUtil.getCon();
			String sql ="SELECT * FROM EMP2 ORDER BY EMPNO ASC";
			ps=con.prepareStatement(sql);
			rs=ps.executeQuery();
			ArrayList<EmpVO> arr = makeList(rs);
			return arr;
		} catch (SQLException e) {
			e.printStackTrace();
			return null;
		} finally {
			close();
		}
	}
	//사원명으로 사원정보를 검색하는 메소드
	public ArrayList<EmpVO>	selectByEname(String ename){
		try {
			con=DBUtil.getCon();
			String sql="SELECT * FROM EMP2 WHERE ENAME=upper(?)";
			ps=con.prepareStatement(sql);
			ps.setString(1, ename);
			rs=ps.executeQuery();
			ArrayList<EmpVO> arr = makeList(rs);
			return arr;
		} catch (SQLException e) {
			e.printStackTrace();
			return null;
		} finally {
			close();
		}
	}
	public ArrayList<EmpVO> selectByEname(int empno) {
		try {
			con=DBUtil.getCon();
			String sql="SELECT * FROM EMP2 WHERE EMPNO=?";
			ps=con.prepareStatement(sql);
			ps.setInt(1, empno);
			rs=ps.executeQuery();
			ArrayList<EmpVO> arr = makeList(rs);
			return arr;
		} catch (SQLException e) {
			e.printStackTrace();
			return null;
		} finally {
			close();
		}
	}
	private ArrayList<EmpVO> makeList(ResultSet rs) throws SQLException{
		ArrayList<EmpVO> arr= new ArrayList<>();
		while(rs.next()) {
			int empno=rs.getInt("empno");
			String ename=rs.getString("ename");
			String job=rs.getString("job");
			int mgr=rs.getInt("mgr");
			java.sql.Date hdate=rs.getDate("hiredate");
			int sal=rs.getInt("sal");
			int comm=rs.getInt("comm");
			int deptno=rs.getInt("deptno");
			EmpVO emp=new EmpVO(empno,ename,job,mgr,hdate,sal,comm,deptno);
			arr.add(emp);
		}
		return arr;
	}
	//DB연결 자원을 반납하는 메소드
	public void close() {
		try {
			if(rs!=null)	rs.close();
			if(ps!=null)	ps.close();
			if(con!=null)	con.close();
		} catch (SQLException e) {
			e.printStackTrace();
		}
	}
}

EmpManagerApp => 이후 EmpManagerGui로 바꿔주면 되는 것이다.

package jdbc.emp;
//인사관리 애블리케이션 - 콘솔 모드

import java.util.ArrayList;
import java.util.Scanner;

public class EmpManagerApp {
	EmpDAO dao = new EmpDAO();
	public void menu() {
		System.out.println("-----EmpManagerApp v1.1-----");
		System.out.println("1. 사원 정보 등록");
		System.out.println("2. 사원 정보 출력 [모든 사원]");
		System.out.println("3. 사원 정보 검색 [이름]");
		System.out.println("4. 사원 정보 검색 [번호]");
		System.out.println("9. 종료");
		System.out.println("----------------------------");
		System.out.println("메뉴 번호를 선택하세요=>");
		System.out.println("----------------------------");
	}
	/*모든 사원 정보를 가져와 출력하는 메소드*/
	public void printEmpAll() {
		ArrayList<EmpVO> arr=dao.selectEmpAll();
		showEmp(arr);
	}
	
	public static void main(String[] args) {
		EmpManagerApp app = new EmpManagerApp();
		Scanner sc = new Scanner(System.in);
		while(true) {
			app.menu();
			int no = sc.nextInt();
			if(no==9) {
				System.out.println("Bye bye~");
				System.exit(0);
			}
			switch(no) {
			case 1:
				app.register();
				break;
			case 2://모든 사원 정보 출력
				app.printEmpAll();
				break;
			case 3:
				System.out.println("검색할 사원의 이름을 입력하세요");
				String ename=sc.next();
				app.findEmp(ename);
				break;
			case 4:
				System.out.println("검색할 사원의 사번을 입력하세요");
				int empno=sc.nextInt();
				app.findEmp(empno);
				break;
			default:
				System.out.println("없는 메뉴 번호");
			}
		}
	}
	private void findEmp(int empno) {
		ArrayList<EmpVO> arr=dao.selectByEname(empno);
		showEmp(arr);
	}
	private void findEmp(String ename) {
		ArrayList<EmpVO> arr=dao.selectByEname(ename);
		showEmp(arr);
	}
	private void showEmp(ArrayList<EmpVO> arr) {
		if(arr!=null&&arr.size()!=0) {
			System.out.println("사번\t사원명\t업무\t부서번호\t급여\t보너스\t입사일");
			for(EmpVO evo:arr) {
				System.out.println(evo.getEmpno()+"\t"+evo.getEname()+"\t"+evo.getJob()+"\t"
						+evo.getDeptno()+"\t"+evo.getSal()+"\t"+evo.getComm()+"\t"+evo.getHiredate());
			}
		}else {
			System.out.println("검색 정보가 없습니다.");
		}
	}
	public void register() {
		EmpVO e = new EmpVO();
		Scanner sc = new Scanner(System.in);
		//사원명, 업무, 부서번호, 급여, 보너스, 관리자 번호
		System.out.println("사원명");
		String ename=sc.next();
		e.setEname(ename);
		System.out.println("업무");
		String job=sc.next();
		e.setJob(job);
		System.out.println("부서번호");
		int deptno=sc.nextInt();
		e.setDeptno(deptno);
		System.out.println("급여");
		int sal=sc.nextInt();
		e.setSal(sal);
		System.out.println("보너스");
		int comm=sc.nextInt();
		e.setComm(comm);
		System.out.println("관리자 번호");
		int mgr=sc.nextInt();
		e.setMgr(mgr);
		System.out.println("위 정보를 등록할까요? 1.예   2.아니오");
		int yn = sc.nextInt();
		if(yn==2)	return;
		boolean result = dao.insertEmp(e);
		System.out.println(result);
	}
}

ㅡㅡㅡㅡㅡㅡㅡㅡㅡㅡㅡㅡㅡㅡㅡㅡㅡㅡㅡㅡㅡㅡㅡㅡㅡㅡㅡㅡㅡㅡㅡㅡㅡㅡㅡㅡㅡㅡㅡㅡㅡㅡㅡ

CURSOR
오라클 서버에서는 SQL문을 실행할 때마다 처리(PARSE, EXECUTION)를 위한 메모리 공간을 사용한다.
즉 사용자가 요청하는 데이터를 데이터베이스 버퍼 캐쉬에서 커서(메모리공간)로 복사해 온 후 커서에서 원하는 데이터를
추출하여 후속 작업을 하게된다. 이 메모리 공간을 PRIVATE SQL AREA 라고 부른다.

--EMP에서 모든 사원 정보 사원명, 업무를 사져와 출력해주는 프로시저를 작성하세요
CREATE OR REPLACE PROCEDURE EMPALL
IS
  VNAME EMP.ENAME%TYPE;
  VJOB EMP.JOB%TYPE;
BEGIN
  SELECT ENAME, JOB INTO VNAME, VJOB FROM EMP;
  DBMS_OUTPUT.PUT_LINE(VNAME||' '||VJOB);
  EXCEPTION
  WHEN TOO_MANY_ROWS THEN
    DBMS_OUTPUT.PUT_LINE('요청한 데이터가 2건 이상입니다');
END;
/
set serveroutput on

EXECUTE EMPALL;

-- 요청한 데이터가 2건 이상입니다. 출력

---------------------------------------------------------


--EMP에서 모든 사원 정보 사원명, 업무를 사져와 출력해주는 프로시저를 작성하세요
CREATE OR REPLACE PROCEDURE EMPALL
IS
  VNAME EMP.ENAME%TYPE;
  VJOB EMP.JOB%TYPE;
BEGIN
  SELECT ENAME, JOB INTO VNAME, VJOB FROM EMP;
  DBMS_OUTPUT.PUT_LINE(VNAME||' '||VJOB);
  EXCEPTION
  WHEN TOO_MANY_ROWS THEN
    DBMS_OUTPUT.PUT_LINE('요청한 데이터가 2건 이상입니다');
END;
/
set serveroutput on

EXECUTE EMPALL;

select * from emp;


--다중행일 경우 커서를 이용하자
CREATE OR REPLACE PROCEDURE EMPALL2
IS
  VNAME EMP.ENAME%TYPE;
  VJOB EMP.JOB%TYPE;
  --커서 선언
  CURSOR empCr is
  SELECT ENAME, JOB FROM EMP ORDER BY JOB;
BEGIN
  --커서를 오픈
  open empCr;
  LOOP --반복문 돌면서 데이터를 fetch (인출)
  fetch empCr into vname, vjob;
  EXIT WHEN empCr%notfound; --커서에 더이상 참조하는 레코드가 없을 경우 exit
  DBMS_OUTPUT.PUT_LINE(lpad(vname,12,' ')||lpad(vjob,12,' '));
  END LOOP;
  close empCr; --커서를 close
END;
/

EXECUTE EMPALL2;

FOR LOOP 문에서 커서사용
--MEMO 테이블에 등록된 모든 정보를 가져와 출력

CREATE OR REPLACE PROCEDURE MEMOALL
IS
 CURSOR MCR IS
  SELECT * FROM MEMO ORDER BY IDX DESC;
BEGIN
  DBMS_OUTPUT.PUT_LINE('글번호'||' '||'작성자  '||'메모내용 '||'작성일');
  FOR K IN MCR LOOP
    DBMS_OUTPUT.PUT_LINE(K.IDX||' '||K.NAME||' '||K.MSG||' '||K.WDATE);
  END LOOP;
END;
/

execute memoall;

암시적 CURSOR
--입력한 사원의 급여를 10퍼센트 올려주는 프로시저를 생성

CREATE OR REPLACE PROCEDURE IMPLICIT_CURSOR
(PNO IN EMP.EMPNO%TYPE)
IS
  VNAME EMP.ENAME%TYPE;
  VSAL EMP.SAL%TYPE;
  VCNT NUMBER;
BEGIN
  SELECT ENAME, SAL INTO VNAME, VSAL
  FROM EMP WHERE EMPNO =PNO;
  IF SQL%FOUND THEN
    DBMS_OUTPUT.PUT_LINE('검색한 데이터가 존재합니다.');
    DBMS_OUTPUT.PUT_LINE('사원명: '||VNAME||', 급여: '||VSAL);
    UPDATE EMP SET SAL=SAL*1.1 WHERE EMPNO=PNO;
    VCNT:=SQL%ROWCOUNT;
    DBMS_OUTPUT.PUT_LINE('급여 인상된 사원 수: '||VCNT);
  END IF;
  EXCEPTION 
  WHEN NO_DATA_FOUND THEN
    DBMS_OUTPUT.PUT_LINE('검색된 데이터가 존재하지 않습니다.');
END;
/

EXECUTE IMPLICIT_CURSOR(7000);
rollback;

PL/SQL오류의 종류
------------------------------------------------------------------------------------------------
예  외 설  명 처  리
------------------------------------------------------------------------------------------------
미리 정의된 오라클 서버 오류 PL/SQL에서 자주 발생하는 선언할 필요도 없고, 발생시에
(Predefined Oracle Server) 약20개의 오류 예외 절로 자동 트랩(Trap)된다.
------------------------------------------------------------------------------------------------
  미리 정의되지 않은 오라클 미리 정의된 오라클 서버 선언부에서 선언해야 하고 발생시
  서버 오류 오류를 제외한 자동 트랩된다.
  (Non-Predefined Oracle   Server) 모든 오류
------------------------------------------------------------------------------------------------
  사용자 정의 오류 개발자가 정한 조건에 선언부에서 선언하고 실행부에서
  (User-Defined) 만족하지 않을경우 RAISE문을 사용하여 발생시켜야
발생하는 오류 한다
------------------------------------------------------------------------------------------------

사용자 정의 오류

CREATE OR REPLACE PROCEDURE User_Exception 
        (v_deptno IN emp.deptno%type ) 
  IS

  -- 예외의 이름을 선언
  user_define_error EXCEPTION;     -- STEP 1
  cnt     NUMBER;

  BEGIN

  DBMS_OUTPUT.ENABLE;   

  SELECT COUNT(empno) 
  INTO cnt
  FROM emp
  WHERE deptno = v_deptno;

  IF cnt < 5 THEN
  -- RAISE문을 사용하여 직접적으로 예외를 발생시킨다
  RAISE user_define_error;         -- STEP 2
  END IF;

  EXCEPTION
  -- 예외가 발생할 경우 해당 예외를 참조한다.
  WHEN user_define_error THEN      -- STEP 3
  RAISE_APPLICATION_ERROR(-20001, ’부서에 사원이 몇명 안되네요..’);

  END; 
  /

FUNCTION
- 구문----------------------------------------------------------
CREATE [OR REPLACE] FUNCTION function_name
[(arg1 [mode1] datatype [{:= | default} expression]
[,arg2 [mode2] datatype [{:= | default} expression],....])]

RETURN data_type
{IS | AS}
BEGIN
PL/SQL_BLOCK;
END;
----------------------
function_name: 함수명
mode1 : IN(입력 매개변수), OUT(출력 매개변수), IN OUT (입출력 매개변수)
data_type : 반환되는 값의 데이터타입
--------------------------------------------------------------

--사원명을 인 파라미터로 넘기면 해당 사원의 사번을 알려주는 함수를 구성해보자

CREATE OR REPLACE FUNCTION GET_NO(VNAME IN EMP.ENAME%TYPE)
RETURN NUMBER
IS
  VEMPNO EMP.EMPNO%TYPE;
BEGIN
  SELECT EMPNO INTO VEMPNO FROM EMP WHERE ENAME=VNAME;
    RETURN VEMPNO;
EXCEPTION
  WHEN TOO_MANY_ROWS THEN
    DBMS_OUTPUT.PUT_LINE(VNAME||'은 2명 이상');
    RETURN -1;
  WHEN NO_DATA_FOUND THEN
    DBMS_OUTPUT.PUT_LINE(VNAME||'사원은 없어요');
    RETURN -2;
  WHEN OTHERS THEN
    DBMS_OUTPUT.PUT_LINE('기타 에러');
    RETURN -3;
END;
/
VARIABLE ENUM NUMBER

EXECUTE :ENUM := GET_NO('SCOTT');
PRINT ENUM;

저작자표시

'개발자 > 국비지원 SW' 카테고리의 다른 글

국비지원 38일차 - NetBean, JavaScript, 자료형 (0)	2020.06.08
국비지원 37일차 - CallableStatement, NetBeans, SQL PACKAGE, Trigger (0)	2020.06.05
국비지원 35일차 - JDBC INSERT, UPDATE, SELECT ,PL/SQL 테이블 타입, 레코드 타입 (0)	2020.06.03
국비지원 34일차 - LCD, PIEZO, JDBC, PL/SQL, DECLARE, 반복문, EXIT조건 (0)	2020.06.02
국비지원 33일차 - DC 모터, Servo 모터, 초음파 센서, PL/SQL (0)	2020.06.01

찬쿤

국비지원 36일차 - PreparedStatement, MVC패턴 사원관리 프로그램, CURSOR, 암시적 CURSOR, PL/SQL 오류의 종류, function

'개발자 > 국비지원 SW' 카테고리의 다른 글

+ Recent posts

티스토리툴바