빅데이터 Java 개발자 교육 - 22일차 [Oracle 7번째 시간 (프로시저)]

Jun_Gyu·2023년 2월 28일

oracle 프로시저

빅데이터 Java 개발자 교육

목록 보기

22/71

금일 수업에는 PL/SQL에 이어서 '프로시저'에 대해서 학습을 하는 시간을 가졌다.

그렇다면 프로시저는 무엇일까??

🪐 프로시저

특정한 로직을 처리하기만 하고 결과 값을 반환하지 않는 서브 프로그램이다.

데이터베이스에 대한 일련의 작업을 정리한 절차를 관계형 데이터베이스 관리 시스템에 저장한 것으로 영구저장모듈(Persistent Storage Module)이라고도 불린다.

보통 저장 프로시저를 프로시저라고 부르며, 일련의 쿼리를 마치 하나의 함수처럼 실행하기 위한 쿼리의 집합이다.

즉, 특정 작업을 위한 쿼리들의 블록이다.
(함수와 기능들이 매우 비슷함.)

장점

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

단점

유지보수가 어렵다.(프로시져가 앱의 어디에 사용되는지 확인하기 어려움)
문자나 숫자열 연산에 사용하면 java보다 느린 성능을 보일 수 있다.

생성하기

-- 프로시저 구조
CREATE OR REPLACE PROCEDURE 프로시저명( 파라미터 IN, OUT, INOUT 세가지 중 선택하여 사용 )
IS
변수

BEGIN
쿼리문
END 프로시저명 ;
/

조회하기

DECLARE
출력될 변수 선언;
실행할 프로시저;

--(출력문.ex)
DBMS_OUTPUT.PUT_LINE(Optional);
END;
/

수정하기

CREATE OR REPLACE PROCEDURE 프로시저명(파라미터 IN ,OUT, INOUT)

IS
변수
BEGIN
쿼리문

EXCEPTION
예외처리

END 프로시저명;
/

삭제하기

DROP PROCEDURE 프로시저명;

출처

https://fomaios.tistory.com/entry/PLSQL-%ED%94%84%EB%A1%9C%EC%8B%9C%EC%A0%80Procedure%EB%9E%80-feat-CRUD

https://logical-code.tistory.com/48

이번에는 직접 실습을 진행하도록 하겠다.

프로시저 실습

시험등급 전달 프로시저

CREATE OR REPLACE PROCEDURE PROC_IF_EXAM( IN_NUM IN NUMBER -- 전달되는 파라미터)

IS
    TMP_GRADE VARCHAR2(2); -- 등급 보관용 문자변수
BEGIN
    IF IN_NUM >=90 THEN
        TMP_GRADE := 'A';
    ELSIF IN_NUM >=80 THEN
        TMP_GRADE := 'B';
    ELSIF IN_NUM >=70 THEN
        TMP_GRADE := 'C';
    ELSE
        TMP_GRADE := 'D';
    END IF;
    DBMS_OUTPUT.PUT_LINE('등급은' || TMP_GRADE || '입니다.');
END;
/
-- 프로시저 실행
EXEC PROC_IF_EXAM(96);

회원가입 프로시저

-- 회원가입 프로시저
CREATE OR REPLACE PROCEDURE PROC_MEMBER_INSERT(
IN_MEM_ID IN MEMBERTB.MEM_ID%TYPE,  -- 멤버 아이디 타입(VARCHAR2를 받아옴)
IN_MEM_PW IN MEMBERTB.MEM_ID%TYPE,
IN_MEM_NAME IN MEMBERTB.MEM_ID%TYPE,
IN_MEM_PHONE IN MEMBERTB.MEM_ID%TYPE,
IN_MEM_ADDRESS IN MEMBERTB.MEM_ID%TYPE,
IN_MEM_BLOCK_CHK IN MEMBERTB.MEM_ID%TYPE,
IN_MEM_QUIT_CHK IN MEMBERTB.MEM_ID%TYPE,
OUT_RET OUT NUMBER -- 성공여부 출력용 변수 0,1
)
IS
BEGIN
INSERT INTO MEMBERTB (MEM_ID, MEM_PW, MEM_NAME, MEM_PHONE, MEM_ADDRESS, MEM_BLOCK_CHK, MEM_QUIT_CHK, MEM_REGDATE )
            VALUES(IN_MEM_ID, IN_MEM_PW, IN_MEM_NAME, IN_MEM_PHONE, IN_MEM_ADDRESS, IN_MEM_BLOCK_CHK, IN_MEM_QUIT_CHK, CURRENT_DATE );
            COMMIT;
            OUT_RET := 1;
EXCEPTION WHEN OTHERS THEN
            ROLLBACK;
            OUT_RET := 0;
END;
/

-- 회원가입 프로시저 테스트
DECLARE
    OUT_RET NUMBER(1) := -1;
BEGIN
-- 생성한 프로시저 호출
    PROC_MEMBER_INSERT('TEST_ID', 'P', '가나다', '010-3333', 'ADDRESS', 1, 1, OUT_RET); -- 마지막에 선언한 OUT_RET 변수 추가
    DBMS_OUTPUT.PUT_LINE('결과값은 ' || OUT_RET || '입니다.');
END;
/

회원정보를 전달할때 회원아이디가 존재하면 이름과 나이, 연락처, 성별 업데이트

(새로운 정보라면 추가하도록 설정.)

-- 명칭은 proc_member_upsert

CREATE OR REPLACE PROCEDURE proc_member_upsert(
IN_MEM_ID IN MEMBERTB.MEM_ID%TYPE,
IN_MEM_PW IN MEMBERTB.MEM_ID%TYPE,
IN_MEM_NAME IN MEMBERTB.MEM_ID%TYPE,
IN_MEM_PHONE IN MEMBERTB.MEM_ID%TYPE,
IN_MEM_ADDRESS IN MEMBERTB.MEM_ID%TYPE,
IN_MEM_BLOCK_CHK IN MEMBERTB.MEM_ID%TYPE,
IN_MEM_QUIT_CHK IN MEMBERTB.MEM_ID%TYPE,
OUT_RET OUT NUMBER --성공여부 출력용 임시변수 0,1
)
IS
 TMP_CHK NUMBER(1) := 0; -- 존재유무 확인용 변수
BEGIN
    -- 존재유무 확인 TMP_CHK에 숫자가 들어감.
    SELECT COUNT(*) INTO TMP_CHK FROM MEMBERTB M WHERE M.MEM_ID = IN_MEM_ID;
         IF TMP_CHK = 0 THEN
            INSERT INTO MEMBERTB (MEM_ID, MEM_PW, MEM_NAME, MEM_PHONE, MEM_ADDRESS, MEM_BLOCK_CHK, MEM_QUIT_CHK, MEM_REGDATE )
            VALUES(IN_MEM_ID, IN_MEM_PW, IN_MEM_NAME, IN_MEM_PHONE, IN_MEM_ADDRESS, IN_MEM_BLOCK_CHK, IN_MEM_QUIT_CHK, CURRENT_DATE );
         ELSE   
            UPDATE MEMBERTB SET MEM_NAME = IN_MEM_NAME, MEM_PHONE = IN_MEM_PHONE, MEM_ADDRESS = IN_MEM_ADDRESS WHERE MEM_ID = IN_MEM_ID;
         END IF;
         COMMIT;
         OUT_RET := 1;
EXCEPTION WHEN OTHERS THEN
            ROLLBACK;
            OUT_RET := 0;
END;
/


DECLARE
    OUT_RET NUMBER(1) := -1;
BEGIN
-- 생성한 프로시저 호출
    PROC_MEMBER_UPSERT('TEST_ID123123', 'P', '가나다라입니다', '010-3333', 'ADDRESS', 1, 1, OUT_RET); -- 마지막에 선언한 OUT_RET 변수 추가
    DBMS_OUTPUT.PUT_LINE('결과값은 ' || OUT_RET || '입니다.');
END;
/

차단유무(MEM_BLOCK_CHK)를 기준으로 멤버 정보 불러오기

-- 프로시저 생성
CREATE OR REPLACE PROCEDURE proc_member_select(
    IN_MEM_BLOCK_CHK IN NUMBER,
    OUT_CURSOR OUT SYS_REFCURSOR  -- CURSOR는 뭘까?? 여기서 CURSOR는 List<MemberTB>와 동일!
 )
IS
BEGIN
    OPEN OUT_CURSOR FOR
        SELECT M.* FROM MEMBERTB M WHERE M.MEM_BLOCK_CHK = IN_MEM_BLOCK_CHK;
END;
/

-- 프로시저 테스트
DECLARE
    IN_BLOCK_CHK NUMBER(1) := 1;
    OUT_CURSOR SYS_REFCURSOR; 
     TYPE MEMBERTYPE IS RECORD ( -- 원래 생성된 테이블의 타입 순서대로 지정해주어야 함.(record)
    MEM_NAME VARCHAR(50),  MEM_ID VARCHAR(50), MEM_PW VARCHAR(50),
    MEM_ADDRESS VARCHAR(50), MEM_PHONE VARCHAR(50), MEM_BLOCK_CHK NUMBER(1),
    MEM_QUIT_CHK NUMBER(1), MEM_REGDATE TIMESTAMP
    );
    -- MEMBER타입으로 변수생성
    MEMBEROBJ MEMBERTYPE;
BEGIN
    -- 프로시저 호출
    proc_member_select(IN_BLOCK_CHK, OUT_CURSOR);
    LOOP
        FETCH OUT_CURSOR INTO MEMBEROBJ;
        EXIT WHEN OUT_CURSOR%NOTFOUND;
        DBMS_OUTPUT.PUT_LINE(MEMBEROBJ.MEM_ID || ' :  ' || MEMBEROBJ.MEM_PW);
        END LOOP;
        CLOSE OUT_CURSOR;
END;
/

하지만 위의 방법은 'SELECT ~ FROM ~ WHERE ~ '로도 충분히 가능한 부분이니 알고만 넘어가도록 하자.

시퀀스 없이 다수 데이터 추가

멤버테이블

--시퀀스가 없는경우
INSERT ALL 
    INTO MEMBERTB(MEM_ID, MEM_PW, MEM_NAME, MEM_PHONE, MEM_ADDRESS, MEM_BLOCK_CHK, MEM_QUIT_CHK, MEM_REGDATE )
        VALUES('AAAA3', 'PWPW', 'MN', '010-1111-1111', '부산', 1, 1, CURRENT_DATE)
    INTO MEMBERTB(MEM_ID, MEM_PW, MEM_NAME, MEM_PHONE, MEM_ADDRESS, MEM_BLOCK_CHK, MEM_QUIT_CHK, MEM_REGDATE )
        VALUES('AAAA4', 'PWPW', 'MN', '010-1111-1111', '부산', 1, 1, CURRENT_DATE)
    INTO MEMBERTB(MEM_ID, MEM_PW, MEM_NAME, MEM_PHONE, MEM_ADDRESS, MEM_BLOCK_CHK, MEM_QUIT_CHK, MEM_REGDATE )
        VALUES('AAAA5', 'PWPW', 'MN', '010-1111-1111', '부산', 1, 1, CURRENT_DATE)
    SELECT * FROM DUAL;
COMMIT;

자바 연동코드 Mapper

(dto도 미리 만들어줄것!)

package mapper;

import java.util.List;
import java.util.Map;

import org.apache.ibatis.annotations.Insert;
import org.apache.ibatis.annotations.Mapper;
import org.apache.ibatis.annotations.Options;
import org.apache.ibatis.annotations.Param;
import org.apache.ibatis.annotations.ResultType;
import org.apache.ibatis.annotations.Results;
import org.apache.ibatis.annotations.Select;
import org.apache.ibatis.mapping.StatementType;

import dto.MemberTB;

@Mapper
public interface MemberMapper {

	@Select({ " { CALL PROC_MEMBER_INSERT ( ", " #{ map.MEM_ID, mode = IN, jdbcType=VARCHAR, javaType=String }, ",
			" #{ map.MEM_PW, mode = IN, jdbcType=VARCHAR, javaType=String }, ",
			" #{ map.MEM_NAME, mode = IN, jdbcType=VARCHAR, javaType=String }, ",
			" #{ map.MEM_PHONE, mode = IN, jdbcType=VARCHAR, javaType=String }, ",
			" #{ map.MEM_ADDRESS, mode = IN, jdbcType=VARCHAR, javaType=String }, ",
			" #{ map.MEM_BLOCK_CHK, mode = IN, jdbcType=NUMERIC, javaType=Integer }, ",
			" #{ map.MEM_QUIT_CHK, mode = IN, jdbcType=NUMERIC, javaType=Integer }, ",
			" #{ map.ret, mode = OUT, jdbcType=NUMERIC, javaType=Integer } ", " )} "

	})

	@Options(statementType = StatementType.CALLABLE)
	public void callProcMemberInsert(@Param("map") Map<String, Object> map); // 회원가입 프로시저

	// -----------------------------------------------------------------------

	@Select({ " { CALL PROC_MEMBER_UPSERT ( ", " #{ map.MEM_ID, mode = IN, jdbcType=VARCHAR, javaType=String }, ",
			" #{ map.MEM_PW, mode = IN, jdbcType=VARCHAR, javaType=String }, ",
			" #{ map.MEM_NAME, mode = IN, jdbcType=VARCHAR, javaType=String }, ",
			" #{ map.MEM_PHONE, mode = IN, jdbcType=VARCHAR, javaType=String }, ",
			" #{ map.MEM_ADDRESS, mode = IN, jdbcType=VARCHAR, javaType=String }, ",
			" #{ map.MEM_BLOCK_CHK, mode = IN, jdbcType=NUMERIC, javaType=Integer }, ",
			" #{ map.MEM_QUIT_CHK, mode = IN, jdbcType=NUMERIC, javaType=Integer }, ",
			" #{ map.ret, mode = OUT, jdbcType=NUMERIC, javaType=Integer } ", " )} " })
	@Options(statementType = StatementType.CALLABLE)
	public void callProcMemberUpsert(@Param("map") Map<String, Object> map); // 중복 회원가입 업데이트 프로시저

	// ----------------------------------------------------------------------- 

	@Results(id = "memberMap")
	@ResultType(MemberTB.class)
	@Select({ " { CALL PROC_MEMBER_SELECT( ", " #{ map.MEM_QUIT_CHK, mode = IN, jdbcType=NUMERIC, javaType=Integer }, ",
			" #{ map.CURSOR, mode = OUT, jdbcType=CURSOR, javaType=java.sql.ResultSet, resultMap = memberMap } ",
			")}" })
	@Options(statementType = StatementType.CALLABLE)
	public void callProcMemberSelect(@Param("map") Map<String, Object> map); // 멤버조회 차단유무

	
	//------------------------------------------------------------------------
	
//	for(MemberTB obj : list) {} 반복문
	@Insert({  
		" <script> ",
		" INSERT ALL ",
			" <foreach collection = 'list' item = 'obj' separator=' ' > ", // separator -> 하나의 쿼리문 끝에 붙어있는 구분자
				"  INTO MEMBERTB(MEM_ID, MEM_PW, MEM_NAME, MEM_PHONE, MEM_ADDRESS, MEM_BLOCK_CHK, MEM_QUIT_CHK, MEM_REGDATE ) ",
				" VALUES(#{obj.MEM_ID}, #{obj.MEM_PW}, #{obj.MEM_NAME}, #{obj.MEM_PHONE}, #{obj.MEM_ADDRESS}, #{obj.MEM_BLOCK_CHK}, #{obj.MEM_QUIT_CHK}, CURRENT_DATE)", 
			" </foreach> ",
			" SELECT * FROM DUAL ",
	    " </script> "
	})
	public int memberInsertBatch(@Param("list") List<MemberTB> list); // 시퀀스 없이 멤버 한번에 추가하기
}

JUnit Test

package test;

import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

import org.junit.jupiter.api.Test;

import connection.MyBatisContext;
import dto.MemberTB;
import mapper.MemberMapper;

class MemberTest {

	MemberMapper mapper = MyBatisContext.getSqlSession().getMapper(MemberMapper.class);

	@Test
	void procMemberInsert() {
		// 전달한 map 객체 생성
		Map<String, Object> map = new HashMap<String, Object>();
		map.put("MEM_ID", "Java_ProcTest");
		map.put("MEM_PW", "Pw");
		map.put("MEM_NAME", "가나다");
		map.put("MEM_PHONE", "010-111");
		map.put("MEM_ADDRESS", "부산시");
		map.put("MEM_BLOCK_CHK", 1);
		map.put("MEM_QUIT_CHK", 1);
		map.put("ret", -1);

		// 프로시저 호출하기 => 변경된 ret 값 확인
		mapper.callProcMemberInsert(map);

		// 변경된 ret값을 확인함. => 1이면 성공 0이면 실패
		System.out.println(map.get("ret"));
	}

	@Test
	void callProcMemberUpsert() {
		// 전달한 map 객체 생성
		Map<String, Object> map = new HashMap<String, Object>();
		map.put("MEM_ID", "업데이트");
		map.put("MEM_PW", "Pw");
		map.put("MEM_NAME", "변경된 이름");
		map.put("MEM_PHONE", "010-111");
		map.put("MEM_ADDRESS", "부산시");
		map.put("MEM_BLOCK_CHK", 1);
		map.put("MEM_QUIT_CHK", 1);
		map.put("ret", 2);

		// 프로시저 호출하기 => 변경된 ret 값 확인
		mapper.callProcMemberUpsert(map);

		// 변경된 ret값을 확인함. => 1이면 성공 0이면 실패
		System.out.println(map.get("ret"));
	}

	@Test
	void callProcMemberSelect() {
		Map<String, Object> map = new HashMap<String, Object>();
		map.put("MEM_QUIT_CHK", 1);
		map.put("CURSOR", null);

		// 프로시저 호출하기 cursor 부분 결과값 변경됨.
		mapper.callProcMemberSelect(map);
		System.out.println(map.get("CURSOR").toString());

	}

	@Test
	void memberInsertBatch() {
		List<MemberTB> list = new ArrayList<>();
		for (int i = 0; i < 3; i++) {
			MemberTB member = new MemberTB();
			member.setMEM_ID("aaa100" + i);
			member.setMEM_PW("12341234");
			member.setMEM_NAME("Mapper_이름");
			member.setMEM_PHONE("010-2345-294" + i);
			member.setMEM_ADDRESS("부산시");
			member.setMEM_BLOCK_CHK(1);
			member.setMEM_QUIT_CHK(1);
			list.add(member);
		}
		int ret = mapper.memberInsertBatch(list);
		System.out.println(ret); // 숫자 3이 출력됨?
	}
    
}

시퀀스로 다수 데이터 추가(함수사용 X)

아이템 테이블

SELECT SEQ_ITEM_CODE.NEXTVAL FROM DUAL;

INSERT INTO ITEM (CODE, NAME, PRICE, QUANTITY, CONTENT, REGDATE) -- item 테이블에 해당하는 값들 입력 
SELECT SEQ_ITEM_CODE.NEXTVAL CODE, T1.* FROM (
    SELECT '이름' NAME, 1234 PRICqweE, 4500 QUANTITY, '내용' CONTENT, CURRENT_DATE REGDATE FROM DUAL
    UNION ALL
    SELECT '이름' NAME, 1234 PRICE, 4500 QUANTITY, '내용' CONTENT, CURRENT_DATE REGDATE FROM DUAL
    UNION ALL
    SELECT '이름' NAME, 1234 PRICE, 4500 QUANTITY, '내용' CONTENT, CURRENT_DATE REGDATE FROM DUAL
) T1;
COMMIT;

Java Mapper

package mapper;

import java.util.List;

import org.apache.ibatis.annotations.Insert;
import org.apache.ibatis.annotations.Mapper;
import org.apache.ibatis.annotations.Param;

import dto.Item;

@Mapper
public interface ItemMapper {

	@Insert({
		" <script> ",
		" INSERT INTO item(code, name, price, quantity, content, regdate) ", 
		" SELECT SEQ_ITEM_CODE.NEXTVAL code, T1.* FROM ( ",
			" <foreach collection='list' item='obj' separator=' UNION ALL '> ",
		    	" SELECT '${obj.name}' name, '${obj.price}' price, '${obj.quantity}' quantity, '${obj.content}' content, CURRENT_DATE regdate FROM DUAL ",
		    " </foreach> ",
		" ) T1 ",
		" </script> "
	})
	public int itemInsertBatch(@Param("list") List<Item> list);
}

JUnit Test

package test;

import java.util.ArrayList;
import java.util.List;

import org.junit.jupiter.api.Test;

import connection.MyBatisContext;
import dto.Item;
import mapper.ItemMapper;

class ItemMapperTest {

	ItemMapper mapper = MyBatisContext.getSqlSession().getMapper(ItemMapper.class);
	
	@Test
	void itemInsertBatch() {
		List<Item> list = new ArrayList<>();
		for(int i = 1; i<=5; i++) {
			Item item = new Item();
			item.setContent("초록색");
			item.setName("아보카도");
			item.setPrice(12000);
			item.setQuantity(2543);
			list.add(item);
		}
		int ret = mapper.itemInsertBatch(list);
		System.out.println(ret); // 5가 출력되어야 함.
		
	}

}

Jun_Gyu

시작은 미약하지만, 그 끝은 창대하리라

이전 포스트

빅데이터 Java 개발자 교육 - 21일차 [Oracle 6번째 시간 (PL/SQL)]

다음 포스트

빅데이터 Java 개발자 교육 - 22일차 [Oracle 7번째 시간 (프로시저)]