433
No
< 저장 프로시저 >
* Stored Procdure(SP, 저장 프로시저)
- 특정 작업을 수행할 수 있고, 이름이 있는 PL/SQL 블록
- 매개 변수를 입력받을 수 있음
- DB에 저장되어 반복적으로 사용할 수 있음.
- 배치 작업 또는 구현이 복잡한 트랜잭션을 수행하는 용도로 사용함
CREATE OR REPLACE 저장프로시저이름
(매개변수)
IS
변수 선언
BEGIN
문장
END;
-- 저장 프로시저 작성
-- in 입력매개변수
-- out 출력매개변수(리턴값을 저장하는 변수)
-- select 프로시저
-- sys_refcursor : 레코드셋을 리턴하는 커서 자료형
-- open 커서변수 for select 문장
create or replace procedure memo_list
(v_row out sys_refcursor)
is
begin
open v_row for
select idx, writer, memo, post_date
from memo
order by idx desc;
end;
/
< 커서의 사용 방법 >
* 커서 열기(OPEN)
- OPEN cursor_name;
* 커서 패치(FETCH)
- FETCH cursor_name INTO variable1, variable2;
- 현재 레코드를 OUTPUT 변수에 저장 (한 라인씩 데이터를 읽음)
- 커서의 SELECT 문의 컬럼수와 OUTPUT 변수의 수와 데이터 타입이 동일해야 함
* 커서 닫기(CLOSE)
- CLOSE cursor_name;
- 사용을 마친 커서는 반드시 닫아 주어야 함
- 커서을 닫은 상태에서 FETCH 를 할 수 없음
-- 실행 콜론은 변수라는 의미 :변수이름
execute memo_list(:param);
< 커서(Cursor) >
* SQL의 작업 영역
* 모든 SQL 문은 연관된 각각의 커서를 쇼유함
* 커서의 종류
- 암시적 커서: 모든 DML과 PL/SQL select 문에 대해 선언됨
- 명시적 커서 : 프로그래머에 의해 선언되며 이름이 있는 커서
<암시적 커서>
* 암시적인 커서 : 오라클이나 PL/SQL 실행 메커니즘에 의해 처리되는 SQL 문장이 처리되는
곳에 대한 익명의 주소값
* 암시적 커서의 속성
- SQL%ROWCOUNT : 해당 SQL 문에 영향을 받는 행의 수
- SQL%FOUND : 해당 SQL 영향을 받는 행의 수가 1개 이상일 경우 TRUE
- SQL%NOTFOUND : 해당 SQL 문에 영향을 받는 행의 수가 없을 경우 TRUE
- SQL%ISOPEN : 항상 FALSE, 암시적 커서가 열려 있는지의 여부 검색
* 암시적 커서는 SQL 문이 실행되는 순간 자동으로 열림과 닫힘 실행
* 저장 프로시저의 장점
- sql 명령어의 트래픽 감소
- 보안성 향상
- 실행 속도 향상
parsing(명령어 해석)
실행계획 수립
run
* 저장 프로시저의 단점
- 개발이 어렵다
- 디버깅이 어렵다
- 유지보수가 어렵다
create or replace procedure memo_list
(v_row out sys_refcursor, v_writer varchar2)
is
begin
open v_row for
select idx, writer, memo, post_date
from memo where writer like '%' || v_writer || '%'
order by idx desc;
end;
/
-- 삭제 저장 프로시저
create or replace procedure memo_del
(v_idx in number)
is
begin
delete from memo
where idx=v_idx;
end;
/
execute memo_del(2);
< JSP 출력 >
출력 화면
이름 : 메모 :
이름 :
- 검색 및 리스트 저장 프로시저
create or replace procedure memo_list
(v_row out sys_refcursor, v_writer varchar2)
is
begin
open v_row for
select idx, writer, memo, post_date
from memo where writer like '%' || v_writer || '%'
order by idx desc;
end;
/
-- 삭제 저장 프로시저
create or replace procedure memo_del
(v_idx in number)
is
begin
delete from memo
where idx=v_idx;
end;
/
execute memo_del(2);
class DB
package config;
import java.sql.Connection;
import javax.naming.Context;
import javax.naming.InitialContext;
import javax.sql.DataSource;
public class DB {
public static Connection dbConn(){
//context.xml에 설정된 dbcp에서 커넥션을 가져옴
DataSource ds=null; //javax.sql
Connection conn=null;
try {
//context.xml을 분석하는 객체
Context ctx=new InitialContext();//javax.naming
// context.xml의 Resource 태그 검색
// ds=
//(DataSource)ctx.lookup("java:comp/env/myDB");
ds=
(DataSource)ctx.lookup("java:comp/env/oraDB");
conn = ds.getConnection(); //커넥션을 할당받음
} catch (Exception e) {
e.printStackTrace();
}
return conn; //커넥션 리턴
}
}
class MybatisService
package config;
import java.io.Reader;
import org.apache.ibatis.io.Resources;
import org.apache.ibatis.session.SqlSessionFactory;
import org.apache.ibatis.session.SqlSessionFactoryBuilder;
public class MybatisService {
// SqlSessionFactoryBuilder => SqlSessionFactory
// => SqlSession
//SqlSession 객체 생성기
private static SqlSessionFactory factory;
static {
try {
// Java Resources의 src
Reader r = Resources.getResourceAsReader(
"config/sqlMapConfig.xml");
//SqlSessionFactory 생성기
factory = new SqlSessionFactoryBuilder()
.build(r);
r.close();
} catch (Exception e) {
e.printStackTrace();
}
}
public static SqlSessionFactory getFactory() {
return factory;
}
}
sqlMapConfig.xml
<?xml version="1.0" encoding="UTF-8"?>
<!-- xml 지시어 -->
<!DOCTYPE configuration
PUBLIC "-//mybatis.org//DTD Config 3.0//EN"
"http://mybatis.org/dtd/mybatis-3-config.dtd">
<configuration>
<!-- 알리아스 설정 -->
<typeAliases>
<!-- typeAlias type="전체경로" alias="별칭" -->
<typeAlias type="emp.dto.EmpDTO" alias="e" />
</typeAliases>
<!-- db연결 참조코드 -->
<environments default="">
<environment id="">
<transactionManager type="JDBC" />
<dataSource type="JNDI">
<property name="data_source"
value="java:comp/env/oraDB" />
</dataSource>
</environment>
</environments>
<!-- 실제 sql query -->
<mappers>
<mapper resource="emp/mapper/emp.xml" />
<mapper resource=
"student/mapper/student.xml" />
<mapper resource=
"student/mapper/dept.xml" />
<mapper resource=
"student/mapper/prof.xml" />
<mapper resource=
"student/mapper/lecture.xml" />
<mapper resource=
"memo/mapper/memo.xml" />
<mapper resource=
"board/mapper/board.xml" />
</mappers>
</configuration>
class MemoDTO
package memo.dto;
import java.sql.Date;
public class MemoDTO {
private int idx;
private String writer;
private String memo;
private Date post_date;
// getter,setter
public int getIdx() {
return idx;
}
public void setIdx(int idx) {
this.idx = idx;
}
public String getWriter() {
return writer;
}
public void setWriter(String writer) {
this.writer = writer;
}
public String getMemo() {
return memo;
}
public void setMemo(String memo) {
this.memo = memo;
}
public Date getPost_date() {
return post_date;
}
public void setPost_date(Date post_date) {
this.post_date = post_date;
}
@Override
public String toString() {
return "MemoDTO [idx=" + idx + ", writer=" + writer + ", memo=" + memo + ", post_date=" + post_date + "]";
}
public MemoDTO() {
}
public MemoDTO(String writer, String memo) {
super();
this.writer = writer;
this.memo = memo;
}
}
class MemoDAO
package memo.dao;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;
import org.apache.ibatis.session.SqlSession;
import config.MybatisService;
import memo.dto.MemoDTO;
public class MemoDAO {
public List<MemoDTO> listMemo(String search){
List<MemoDTO> list=null;
SqlSession session=null;
try {
session
=MybatisService.getFactory().openSession();
//list=session.selectList("memo.listMemo");
Map<String,Object> map
=new HashMap<String,Object>();
//입력매개변수 설정
map.put("search", search);
// 해시맵을 매개변수로 전달, 실행결과가 map에 리턴됨
session.selectList("memo.listMemo", map);
//System.out.println(map);
//System.out.println(list);
// 맵에 저장된 출력매개변수값을 받아옴
list=(List<MemoDTO>)map.get("result");
} catch (Exception e) {
e.printStackTrace();
} finally {
if(session != null) session.close();
}
return list;
}
public void insertMemo(MemoDTO dto){
SqlSession session=null;
try {
session
= MybatisService.getFactory().openSession();
session.insert("memo.insertMemo", dto);
session.commit();
} catch (Exception e) {
e.printStackTrace();
} finally{
if(session != null) session.close();
}
}
public void deleteMemo(int idx){
SqlSession session=null;
try {
session
= MybatisService.getFactory().openSession();
session.delete("memo.deleteMemo", idx);
session.commit();
} catch (Exception e) {
e.printStackTrace();
} finally {
if(session != null) session.close();
}
}
}
Mapper memo.xml
<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE mapper
PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN"
"http://mybatis.org/dtd/mybatis-3-mapper.dtd">
<!-- memo.xml -->
<!-- 저장 프로시저 호출 방법 { call 프로시저이름(매개변수) } -->
<mapper namespace="memo">
<resultMap type="memo.dto.MemoDTO" id="memoMap">
</resultMap>
<insert id="insertMemo">
{ call memo_insert(#{writer},#{memo} ) }
</insert>
<!-- <select id="listMemo"
resultType="memo.dto.MemoDTO">
select * from
memo order by idx desc
</select> -->
<!-- statementType
CALLABLE(저장프로시저용),
PREPARED(PreparedStatement), STATEMENT(Statement)
mode=OUT 출력매개변수
jdbcType 오라클의 자료형
javaType 자바의 자료형
resultMap 해시맵의 자료형(리턴타입)
Map<String,Object> map=new HashMap<String,Object>();
map.put("result", new ArrayList.....);
-->
<resultMap type="memo.dto.MemoDTO"
id="memo_list"></resultMap>
<select id="listMemo" statementType="CALLABLE"
parameterType="hashmap" >
{ call memo_list(
#{result, mode=OUT, jdbcType=CURSOR,
javaType=ResultSet, resultMap=memo_list }
, #{search} ) }
</select>
<delete id="deleteMemo">
{ call memo_del( #{idx} ) }
</delete>
</mapper>
class MemoController
package memo;
import java.io.IOException;
import java.util.List;
import javax.servlet.RequestDispatcher;
import javax.servlet.ServletException;
import javax.servlet.annotation.WebServlet;
import javax.servlet.http.HttpServlet;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
import memo.dao.MemoDAO;
import memo.dto.MemoDTO;
@WebServlet("/memo_servlet/*")
public class MemoController extends HttpServlet {
private static final long serialVersionUID = 1L;
protected void doGet(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
String writer = request.getParameter("writer");
String memo = request.getParameter("memo");
MemoDAO dao=new MemoDAO();
String url=request.getRequestURL().toString();
if(url.indexOf("insert.do") != -1){
dao.insertMemo(new MemoDTO(writer,memo));
}else if(url.indexOf("list.do") != -1){
String search=request.getParameter("search");
List<MemoDTO> list=dao.listMemo(search);
request.setAttribute("list", list);
String page="/sql09/memo_list.jsp";
RequestDispatcher rd=
request.getRequestDispatcher(page);
rd.forward(request, response);
}else if(url.indexOf("del.do") != -1){
int idx=
Integer.parseInt(request.getParameter("idx"));
dao.deleteMemo(idx);
//ajax 페이지로 이동
response.sendRedirect(
request.getContextPath()+"/sql09/memo.jsp");
}else if(url.indexOf("delete_all.do") != -1){
//체크한 체크박스값만 넘어옴
String[] idx=request.getParameterValues("idx");
if(idx != null){
for(int i=0; i<idx.length; i++){
//System.out.println(idx[i]);
dao.deleteMemo(Integer.parseInt(idx[i]));
}
}
//ajax 페이지로 이동
response.sendRedirect(
request.getContextPath()+"/sql09/memo.jsp");
}
}
protected void doPost(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
doGet(request, response);
}
}
memo.jsp
<%@ page language="java" contentType="text/html; charset=UTF-8"
pageEncoding="UTF-8"%>
<!DOCTYPE html>
<html>
<head>
<meta http-equiv="Content-Type" content="text/html; charset=UTF-8">
<title>Insert title here</title>
<%@ include file="../include/header.jsp" %>
<script src="${path}/include/jquery-3.1.1.min.js"></script>
<script>
$(document).ready(function(){
memo_list("search=");
$("#btnSave").click(function(){
memo_insert();
});
$("#btnSearch").click(function(){
var search="search="+$("#search").val();
memo_list(search);
});
});
function memo_list(search){
$.ajax({
type: "post",
url: "${path}/memo_servlet/list.do",
data: search,
success: function(result){
$("#result").html(result);
}
});
}
function memo_insert(){
var writer= $("#writer").val();
var memo=$("#memo").val();
var param="writer="+writer+"&memo="+memo;
$.ajax({
type: "post",
url: "${path}/memo_servlet/insert.do",
data: param,
success: function(){
//alert("입력되었습니다.");
memo_list("search=");
}
});
}
</script>
</head>
<body>
이름 : <input id="writer">
메모 : <input id="memo">
<input type="button" id="btnSave" value="확인">
<br>
이름 : <input id="search">
<input type="button" id="btnSearch" value="검색">
<div id="result"></div>
</body>
</html>
memo_list.jsp
<%@ page language="java" contentType="text/html; charset=UTF-8"
pageEncoding="UTF-8"%>
<%@ taglib prefix="fmt" uri="http://java.sun.com/jsp/jstl/fmt" %>
<!DOCTYPE html>
<html>
<head>
<meta http-equiv="Content-Type" content="text/html; charset=UTF-8">
<title>Insert title here</title>
<%@ include file="../include/header.jsp" %>
<script src="${path}/include/jquery-3.1.1.min.js"></script>
<script>
// 태그.prop("태그의 속성")
$(document).ready(function(){
//모두 선택,해제 체크박스
$("#chkAll").click(function(){
//체크 상태이면
if( $("#chkAll").prop("checked")){
//모두 선택
$("input[name=idx]").prop("checked",true);
}else{ //해제 상태이면
//모두 해제
$("input[name=idx]").prop("checked",false);
}
});
$("#btnAllDel").click(function(){
//폼 데이터를 전송할 주소 지정
document.form1.action=
"${path}/memo_servlet/delete_all.do";
//폼 데이터를 전송
document.form1.submit();
});
});
function memo_del(idx){
location.href="${path}/memo_servlet/del.do?idx="+idx;
}
</script>
</head>
<body>
<form method="post" name="form1">
<table border="1">
<tr>
<th><input type="checkbox" id="chkAll"></th>
<th>번호</th>
<th>이름</th>
<th>메모</th>
<th>날짜</th>
<th><input type="button" value="전체삭제"
id="btnAllDel"></th>
</tr>
<c:forEach var="row" items="${list}"
varStatus="s">
<tr>
<td>
<input type="checkbox" name="idx" value="${row.idx}">
</td>
<td>${s.count} / ${list.size() - s.count + 1}</td>
<td>${row.writer}</td>
<td>${row.memo}</td>
<td><fmt:formatDate value="${row.post_date}"
pattern="yyyy-MM-dd HH:mm:ss"/></td>
<td><input type="button" value="삭제"
onclick="memo_del('${row.idx}')"></td>
</tr>
</c:forEach>
</table>
</form>
</body>
</html>
댓글 ( 4)
댓글 남기기