116일차-Procedure사용

 

create or replace procedure 프로시저명(    

    매개변수 in 데이터형,,,

    매개변수 out SYS_REPRCURSOR //커서의 제어권을 외부로 보내는 데이터형

)

is

begin

end;

/

*조회를 하는 Procedure(반환형이 없는)를 호출

• Parameter처리
• IN : #{getter명, mode=IN}
• OUT : 단일형 : #{setter명, mode=OUT, jdbcType=DB데이터형(number->NUMERIC), JavaType=Java의 데이터형(int)}
• CURSOR : #{setter명, mode=OUT, jdbcType=CURSOR, JavaType=ResultSet, resultMap=mapID}   
• //mapID가 오는 부분(조회되는 컬럼과 setter Mapping하는 node) : <resultMap id="mapID" type="객체화클래스명"><result column="컬럼명" property(메소드의 다른이름)="setter명"/>...</resultMap>
• //객체화할 클래스명에는 Domain을 가진 List가 들어가진다.
• 
  

create or replace procedure 프로시저명(    

    deptno [in] number, emp_cur out SYS_RERCURSOR)

is

begin

end;

/

• parameter처리
• <resultMap id="00" type="Domain">
• <result column="컬럼명" property="setter 명"/>
• </resultMap>
• -----------------------------------------------------------------------------------
• <select id=" " parameterType="VO-프로시저에서 사용할  조회 where절에 사용될 값 | 조회결과 :List<Domain=>조회결과를 저장할 객체>" statementType="CALLABLE">
• {
•    call 프로시저명( #{deptno, mode=IN},
•                              #{list, mode=OUT, jdbcType=CURSOR, javaType=ResultSet, resultMap=00}
•                             )
• }
• </select>
•  ------------------------------------------------------------------------------------
• SqlSession.selectOne("selectID" , VO);
• =>vo.getList() //조회결과를 가진List를 받을 수 있다.
  

//EmpProcedure.java

package kr.co.sist.exam.domain;

public class EmpProcedure {

     private int empno,sal,deptno;

     private String ename,dname,loc;

     

     public int getEmpno() {

           return empno;

     }

     public void setEmpno(int empno) {

           this.empno = empno;

     }

     public int getSal() {

           return sal;

     }

     public void setSal(int sal) {

           this.sal = sal;

     }

     public int getDeptno() {

           return deptno;

     }

     public void setDeptno(int deptno) {

           this.deptno = deptno;

     }

     public String getEname() {

           return ename;

     }

     public void setEname(String ename) {

           this.ename = ename;

     }

     public String getDname() {

           return dname;

     }

     public void setDname(String dname) {

           this.dname = dname;

     }

     public String getLoc() {

           return loc;

     }

     public void setLoc(String loc) {

           this.loc = loc;

     }

}

//CursorVO.java

package kr.co.sist.exam.vo;

import java.util.List;

import kr.co.sist.exam.domain.EmpProcedure;

public class CursorVO {

     private int deptno; //조회할 부서번호 - 사용자가 입력하는 값

     private List<EmpProcedure> empList; //CURSOR로 조회한 값 -  MyBatis가 입력하는 값

     

     public int getDeptno() {

           return deptno;

     }

     public void setDeptno(int deptno) {

           this.deptno = deptno;

     }

     public List<EmpProcedure> getEmpList() {

           return empList;

     }

     public void setEmpList(List<EmpProcedure> empList) {

           this.empList = empList;

     }

     

}

//select_procedure.jsp

<%@page import="kr.co.sist.exam.vo.CursorVO"%>

<%@page import="kr.co.sist.exam.dao.MyBatisDAO1"%>

<%@page import="kr.co.sist.exam.domain.Emp"%>

<%@page import="java.util.List"%>

<%@page import="kr.co.sist.exam.service.MyBatisService"%>

<%@page import="kr.co.sist.exam.dao.MyBatisDAO"%>

<%@ page language="java" contentType="text/html; charset=UTF-8"

    pageEncoding="UTF-8"%>

    <%@ taglib prefix="c"  uri="http://java.sun.com/jsp/jstl/core" %>

<%

     String deptno=request.getParameter("deptno");

     if(deptno!=null && !"".equals(deptno)){

           MyBatisDAO1 mb_dao=new MyBatisDAO1();

           CursorVO cvo=new CursorVO();

           cvo.setDeptno(Integer.parseInt(deptno));

           mb_dao.selectProc(cvo); //프로시저를 호출하여 실행 후  결과가 저장

           pageContext.setAttribute("empList",  cvo.getEmpList()); //model2에는 requestScope객체에 넣어야 한다.

     }//end if

%>

<form name="frm" action="main.jsp">

<input type="hidden" name="page"  value="day0411/select_procedure"/>

<label>부서번호</label>

<input type="text" name="deptno" class="inputBox"/>

<input type="submit" value="부서별 사원 조회", class="btn"/>

</form>

<div>

<c:if test="${not empty param.deptno}">

     <table border="1">

           <tr>

                <th width="40">번호</th>

                <th width="80">사원번호</th>

                <th width="120">사원명</th>

                <th width="80">연봉</th>

                <th width="80">부서번호</th>

                <th width="100">부서명</th>

                <th width="100">위치</th>

           </tr>

           <c:if test="${empty empList}">

           <tr>

                <td colspan="7" align="center">

                <strong>${param.deptno}</strong>번 부서에는  사원이 존재하지 않습니다.

                </td>

           </tr>

           </c:if>

           <c:forEach var="emp" items="${empList}">

           <c:set var="i" value="${i+1}"/>

           <tr>

                <td><c:out value="${i}"/></td>

                <td><c:out value="${emp.empno}"/></td>

                <td><c:out value="${emp.ename}"/></td>

                <td><c:out value="${emp.sal}"/></td>

                <td><c:out value="${emp.deptno}"/></td>

                <td><c:out value="${emp.dname}"/></td>

                <td><c:out value="${emp.loc}"/></td>

           </tr>

           </c:forEach>

     </table>

</c:if>

</div>

*Transaction

//TransactionVO.java

package kr.co.sist.exam.vo;

public class TransactionVO {

     private String subject, writer;

     public TransactionVO(String subject, String writer) {

           this.subject = subject;

           this.writer = writer;

     }

     public String getSubject() {

           return subject;

     }

     public String getWriter() {

           return writer;

     }

     public void setSubject(String subject) {

           this.subject = subject;

     }

     public void setWriter(String writer) {

           this.writer = writer;

     }

     

}