ORACLE

 

 < 저장 프로시저 >


* 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 출력  >

 

출력 화면

 

이름 :  메모 :   
이름 :  

번호 이름 메모 날짜
1 / 12 sdfsdf dfsdf 2017-03-29 16:26:01
2 / 11 sdfsdf dfsdf 2017-03-29 16:26:01
3 / 10 dsad sdsd 2017-03-29 11:52:29
4 / 9 www www 2017-03-29 11:52:11
5 / 8 ds sdsd 2017-03-29 11:51:43
6 / 7 ds sdsd 2017-03-29 11:51:43
7 / 6 ds sdsd 2017-03-29 11:51:43
8 / 5 ds sdsd 2017-03-29 11:51:42
9 / 4 ds sdsd 2017-03-29 11:51:40
10 / 3 김순신 테스타 2017-03-28 19:48:13
11 / 2 김철수 테스트 2017-03-28 19:46:10
12 / 1 김철수 테스트 2017-03-28 19:21:09

 

 

- 검색 및 리스트 저장 프로시저

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>









 

 

 

 

 

 

about author

PHRASE

Level 60  머나먼나라

독 안에 든 쥐 , 피할 수 없는 운명에 처해 있음을 비유하여 이르는 말.

댓글 ( 0)

댓글 남기기

작성
  •    
  •