ORACLE

 

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>

 

 

 

 

 

 

about author

PHRASE

Level 60  머나먼나라

마술은 자기 마음에 있다. 마음이 지옥을 천국으로 만들 수도 있고, 천국을 지옥으로 만들 수도 있다. 자기 마음을 지옥으로 만들고 싶은 사람은 아마도 없을 것이다. 마음을 천국으로 만들고 싶은 이들이여! 자기 마음속에 마술을 부려 즐겁고 찬란한 하루를 만들자. - T.A. 에디슨

댓글 ( 1)

댓글 남기기

작성
  •    
  •