432
No
PL/SQL 이란?
* PL/SQL : Oracle's Procdural Language extension to SQL
* 오라클에 내장되어 있는 절차형 언어
* 프로그램을 논리적인 블록으로 나누는 구조화된 블록 언어
* 변수 선언문, 조건문, 반복문(loop, while, for) 등을 지원
PL/SQL Block Structure
* 선언부(선택), 실행부(필수), 예외 처리부(선택) 으로 구성됨
* BEGIN 과 END 키워드는 반드시 기술
DECLARE
Declarative Section(선언부)
BEGIN
Executable Section(실행부)
EXCEPTION
Exception Handling Section(예외처리)
END :
PL/SQL Block Structure
* Declarative Section(선언부, 선택)
- 변수, 상수, CURSOR 등을 선언
* Executable Section(실행부, 필수)
- SQL문장, 반복문, 조건문 등을 실행
- BEGIN 으로 시작하고 END 로 끝남
* Exception Handling Section(예외처리부, 선택)
- 예외에 대한 처리
Block Type(PL/SQL 블럭의 유형)
* [Anonymous]
* [Procedure]
* [Function]
[DECLARE]
BEGIN -- statements
[EXCEPTION]
END :
------------
PROCEDURE name is BEGIN --statements
[EXCEPTION] END :
--------------------
FUNCTION name
RETURN datatype
IS
BEGIN --statements
RETURN value;
[EXCEPTION]
END :
Block Type(PL/SQL 블럭의 유형)
* Anonymous Block(익명 블록) - 이름이 없는 블록
* Procdure(프로시저)
- 특정 작업을 수행할 수 있고, 이름이 있는 PL/SQL 블록
- 매개 변수를 입력받을 수 있음
- DB에 저장되어 반복적으로 사용할 수 있음.
- 배치 작업 또는 구현이 복잡한 트랜잭션을 수행하는 용도로 사용함
* Function(함수)
- 값을 계산하고 결과값을 반환하기 위해서 사용
- 저장 프로시저와의 차이점 : 입력 매개변수만 사용 할 수 있고 리턴 타입을 반드시 지정해야 함
< 저장 프로시저 >
* Stored Procdure(SP, 저장 프로시저)
- 특정 작업을 수행할 수 있고, 이름이 있는 PL/SQL 블록
- 매개 변수를 입력받을 수 있음
- DB에 저장되어 반복적으로 사용할 수 있음.
- 배치 작업 또는 구현이 복잡한 트랜잭션을 수행하는 용도로 사용함
CREATE OR REPLACE 저장프로시저이름
(매개변수)
IS
변수 선언
BEGIN
문장
END;
<프로시저 작성 예제 >
CREATE OR Replace procedure update_sal (v_empno in number)
is
BEGIN
update emp
set sal =sal* 1.1
where empno =v_empno;
commit;
END update_sal;
/
execute update_sal(7369);
-- create or replace procedure 프로시저이름(매개변수)
-- in 입력매개변수
-- out 출력매개변수
create or replace procedure update_sal (v_empno in number)
is
begin
update emp
set sal = sal*1.1
where empno=v_empno;
commit;
end;
/
select * from emp where empno=7369;
-- 저장프로시저의 실행
-- execute 저장프로시저이름(입력매개변수)
execute update_sal(7369);
select * from emp where empno=7369;
create or replace procedure emp_info
(p_empno in emp.empno%type)
is
v_empno emp.empno%type;
v_ename emp.ename%type;
v_sal emp.sal%type;
begin
select empno, ename, sal
into v_empno, v_ename, v_sal
from emp
where empno=p_empno;
dbms_output.put_line('사번:' || v_empno);
dbms_output.put_line('이름:' || v_ename);
dbms_output.put_line('급여:' || v_sal);
end;
/
-- 사번 번호
execute emp_info(7369);
-- 오류가 날경우
-- show errors;
%TYPE 데이터형
* 테이블의 컬럼 데이터 타입을 모를 경우 사용
* 테이블의 데이터 타입이 변경될 경우 다시 수정할 필요가 없음
* 예제
- v_empno emp.empno%TYPE :=7900;
- v_ename emp.ename%TYPE;
-- 데이터 사전
select * from user_source; -- 프로시저, 함수의 코드
select * from user_procedures;
select * from user_source where name='UPDATE_SAL';
-- 저장 프로시저 & 시퀀스 실습
drop table memo;
create table memo (
idx number primary key,
writer varchar2(50) not null,
memo varchar2(500) not null,
post_date date default sysdate
);
select memo_seq.nextval from dual;
-- 시퀀스.nextval : 다음 번호 발급
insert into memo(idx, writer, memo) values ( memo_seq.nextval, 'kim', 'memo1');
insert into memo(idx, writer, memo) values ( memo_seq.nextval, 'park', 'memo2');
insert into memo(idx, writer, memo) values ( memo_seq.nextval, 'hong', 'memo3');
insert into memo(idx, writer, memo) values ( memo_seq.nextval, 'min', 'memo4');
insert into memo(idx, writer, memo) values ( memo_seq.nextval, 'ket', 'memo5');
select * from memo;
delete from memo;
-- 레코드가 없을 때는 null
select max(idx)+1 from memo;
select nvl(max(idx)+1,1) from memo;
-- 서브쿼리를 이용한 번호 발급 방법
insert into memo(idx, writer, memo ) values ((select nvl(max(idx)+1,1) from memo), 'hong', 'memo');
select * from memo;
insert into memo(idx, writer, memo ) values ((select nvl(max(idx)+1,1) from memo), 'hong', 'memo');
=> 저장프로시저로 변경
-- 저장 프로시저 작성
create or replace procedure memo_insert
(v_writer varchar, v_memo varchar)
is
begin
insert into memo(idx, writer, memo ) values
((select nvl(max(idx)+1, 1) from memo), v_writer, v_memo);
end;
/
-- 저장 프로시저 실행
commit;
execute memo_insert('park', '메모');
select * from memo;
=>출력
| 1 park 메모 17/03/28 |
저장프로시저 memo_insert 를 JSP 에서 데이터삽입하기
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="memo2/mapper/memo.xml" />
<mapper resource=
"board/mapper/board.xml" />
</mappers>
</configuration>
class MemoDAO
package memo2.dao;
import java.util.ArrayList;
import java.util.List;
import org.apache.ibatis.session.SqlSession;
import config.MybatisService;
import memo2.dto.MemoDTO;
public class MemoDAO {
public void insertMemo(MemoDTO dto){
SqlSession session =null;
try{
session=MybatisService.getFactory().openSession();
session.insert("memo2.insertMemo", dto);
session.commit();
}catch(Exception e){
e.printStackTrace();
}finally{
if(session!=null)session.close();
}
}
public List<MemoDTO> listMemo(){
List<MemoDTO> list=new ArrayList<>();
SqlSession session=null;
try{
session=MybatisService.getFactory().openSession();
list =session.selectList("memo2.listMemo");
}catch(Exception e){
e.printStackTrace();
}finally{
if(session!=null)session.close();
}
return list;
}
}
class MemoDTO
package memo2.dto;
public class MemoDTO {
private int idx;
private String writer;
private String memo;
private String post_date;
//getter, setter
//toString()
//생성자(기본생성자, writer+memo)
public MemoDTO() {
}
public MemoDTO(String writer, String memo) {
super();
this.writer = writer;
this.memo = memo;
}
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 String getPost_date() {
return post_date;
}
public void setPost_date(String post_date) {
this.post_date = post_date;
}
@Override
public String toString() {
return "MemoDTO [idx=" + idx + ", writer=" + writer + ","
+ " memo=" + memo + ", post_date=" + post_date + "]";
}
}
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="memo2">
<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>
</mapper>
class MemoController
package memo2;
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 memo2.dao.MemoDAO;
import memo2.dto.MemoDTO;
@WebServlet(name = "Memo2Controller", urlPatterns = { "/memo2_servlet/*" })
public class MemoController extends HttpServlet {
private static final long serialVersionUID = 1L;
public MemoController() {
super();
}
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){
List<MemoDTO> list=dao.listMemo();
request.setAttribute("list", list);
String page ="/sql092/memo_list.jsp";
RequestDispatcher rd=request.getRequestDispatcher(page);
rd.forward(request, response);
}
}
protected void doPost(HttpServletRequest request, HttpServletResponse response)
throws ServletException, IOException {
doGet(request, response);
}
}
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>
</head>
<body>
<table border="1">
<tr>
<th>번호</th>
<th>이름</th>
<th>메모</th>
<th>날짜</th>
</tr>
<c:forEach items="${list }" var="row">
<tr>
<td>${row.idx}</td>
<td>${row.writer }</td>
<td>${row.memo }</td>
<td><fmt:formatDate value="${row.post_date }" pattern="yyy-MM-dd" /></td>
</tr>
</c:forEach>
</table>
</body>
</html>
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(){
$("#btnSave").click(function(){
memo_insert();
memo_list();
});
memo_list();
});
function memo_list(){
$.ajax({
type: "post",
url: "${path}/memo2_servlet/list.do",
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}/memo2_servlet/insert.do",
data :param,
success :function(){
alert("입력되었습니다.");
}
});
}
</script>
</head>
<body>
이름 : <input id="writer">
메모 : <input id="memo" >
<input type="button" id="btnSave" value="확인">
<div id="result"></div>
</body>
</html>
댓글 ( 5)
댓글 남기기