[SQL] 프로시저(Procedure)

SET SERVEROUTPUT ON;

프로시저(Procedure)

• 프로시저 : 자주사용하는 SQL문 작성 -> 필요시 호출 -> 반환값이 없음.
  예시) 회원아이디가 있으면 UPDATE 없으면 INSERT 수행.
• 즉, 특정 작업을 위한 쿼리들의 블록

SELECT(아이디확인) -> INSERT OR UPDATE(둘 중 하나 수행)

장점

• 하나의 요청으로 여러 SQL문을 실행시킬 수 있습니다. (네트워크 부하를 줄일 수 있음)
• 네트워크 소요 시간을 줄여 성능을 개선할 수 있습니다.
• 여러 어플리케이션과 공유가 가능합니다. (API처럼 제공가능)
• 기능 변경이 편합니다. (특정 기능을 변경할 때 프로시저만 변경하면 됨)

단점

• 문자나 숫자열 연산에 사용하면 오히려 C,Java보다 느린 성능을 보일 수 있습니다.
• 유지보수가 어렵습니다.(프로시져가 앱의 어디에 사용되는지 확인이 어려움)

프로시저(Procedure)란?
이건 내일한다는 트리거

프로시저 구조

CREATE OR REPLACE PROCEDURE 프로시저명(
파라미터 IN, OUT 사용
)
IS
BEGIN

END;
/

예제1 : 숫자를 받으면 등급을 전달해주는 Procedure

예제를 확인해보자.

-- 숫자를 받으면 등급을 전달해주는 프로시저
CREATE OR REPLACE PROCEDURE proc_if_exam(
    in_num IN NUMBER --전달되는 파라미터 :숫자형 in_num에 담김
)
IS
    tmp_grade VARCHAR(2); -- 등급보관용 문자변수
BEGIN
    IF in_num >=90 THEN
        tmp_grade := 'A';
    ELSIF in_num >=80 THEN
        tmp_grade := 'B';
    ELSE 
        tmp_grade := 'C';
    END IF;
    DBMS_OUTPUT.PUT_LINE('등급은 ' || tmp_grade || '입니다.');
END;
/
-- 프로시저 실행1
EXEC proc_if_exam(90);

-- 프로시저 실행2
BEGIN
    --생성한 프로시저 호출
    EXEC proc_if_exam(90);
END;

실행방법은 두가지가 있다. 2번이 더 정식이다.

이 예제는 함수의 예시에 더 가깝긴 하다. 그래서 하나 더 만들었다.

회원가입 Procedure

oracle

--회원가입 프로시저(SQL이 더 편하긴 함 걍 예시)
CREATE OR REPLACE PROCEDURE proc_nnn_insert(
    in_id IN NNN.ID%TYPE,
    in_name IN NNN.NAME%TYPE,
    in_pw IN NNN.PW%TYPE,
    in_age IN NNN.AGE%TYPE,
    out_ret OUT NUMBER -- 완료가 되면 성공과 실패를 확인. NUMBER. 0또는 1로 확인가능
)
IS
BEGIN
    INSERT INTO nnn (id, name, pw, age, mdate)
    VALUES(in_id, in_name, in_pw, in_age, CURRENT_DATE);
    COMMIT;
    out_ret := 1;
EXCEPTION WHEN OTHERS THEN
    ROLLBACK;
    out_ret := 0;
END;
/

-- 프로시저 테스트
DECLARE 
    out_ret NUMBER(1) := -1; -- 프로시저 성공하면 1이 나온다. 왜냐하면 프로시저가 일을 수행했기 때문에
BEGIN
    --생성한 프로시저 호출
    proc_nnn_insert('D1','SBC1','PWPW1',35,out_ret);
    DBMS_OUTPUT.PUT_LINE('결과값은 ' || out_ret);
END;
/
COMMIT;

-- 조회도 해봄
SELECT * FROM nnn;

• %TYPE : 참조할 테이블 컬럼의 데이터 타입을 자동으로 가져온다.

  in_id IN NNN.ID%TYPE,

java

이제
자바에 호출을 해보자.

NNNMapper

자바에 mapper만들어서 호출하기

package mapper;

import java.util.Map;

import org.apache.ibatis.annotations.Mapper;
import org.apache.ibatis.annotations.Options;
import org.apache.ibatis.annotations.Param;
import org.apache.ibatis.annotations.Select;
import org.apache.ibatis.mapping.StatementType;
@Mapper
public interface NNNMapper {

	@Select({
		" { call proc_nnn_insert( ",
			" #{ map.id, mode=IN, jdbcType=VARCHAR, javaType=String }, ",
			" #{ map.name, mode=IN, jdbcType=VARCHAR, javaType=String }, ",
			" #{ map.pw, mode=IN, jdbcType=VARCHAR, javaType=String }, ",
			" #{ map.age, mode=IN, jdbcType=NUMERIC, javaType=Integer }, ",
			" #{ map.ret, mode=OUT, jdbcType=NUMERIC, javaType=Integer } ",
			" )} "
	})
	@Options(statementType = StatementType.CALLABLE)
	public void callProcNNNInsert(@Param("map") Map<String, Object> map);
	//반환값이 없음
}

MyBatisContext

MyBatisContext에도 추가해야함

config.addMapper(NNNMapper.class);

NNNTest

NNNTest 에서 실행

package test;

import java.util.HashMap;
import java.util.Map;

import org.junit.jupiter.api.Test;

import connection.MyBatisContext;
import mapper.NNNMapper;

class NNNTest {
	NNNMapper mapper = MyBatisContext.getSqlSession().getMapper(NNNMapper.class);	

	@Test
	void procNNNInsert() {
	// 전달할 map객체 생성
		Map<String, Object> map = new HashMap<String, Object>();
		map.put("id", "A2");
		map.put("name", "히히");
		map.put("pw", "mm");
		map.put("age", 15);
		
		map.put("ret", -1); //ret값은 임의로 -1
		//프로시저 호출하기 => ret값이 변경되어 있음
		mapper.callProcNNNInsert(map);
		//변경된 ret값을 확인함 => 1이면 성공 0이면 실패
		System.out.println(map.get("ret"));
	}
}