SP

SP : Store Procedure

 

• * 일련의 쿼리를 마치 하나의 함수처럼 실행하기 위한 쿼리 집합.
• * 쿼리문의 함수화 버전.
• * 최초 실행시 최적화된 상태로 DB에 캐쉬되어 저장된다. 하나의 SP가 여러번 쓰일 때 성능향상을 기대할 수 있다.
• * 따라서 속도적인 측면, 관리적인 측면, 유지보수 적인 측면에서 장점이 된다.
• * 한번의 요청으로 여러가지 SQL문을 실행할 수 있다. ( 네트워크 통신 부하 감소 가능)
• * 데이터의 참조무결성 유지가 가능하다.
• * 비지니스 로직과 SQL이 분리되어 가독성이 좋아진다.
• * 외부에서 쿼리문 조작 시도가 발생했을 때 보안적인 측면에서 보호받을 수 있다. SP 에서 참고하는 테이블 접근이 허용되지 않는다.
• * SP는 Create Procedure 쿼리문을 통해 생성할 수 있다.
• * '@' 를 붙여서 매개변수로 사용한다. 매개변수는 이미 선언하여 변수와 타입을 지정해줘야 한다.

예시)

DELIMITER $$

USE `mySchemaName`$$

DROP PROCEDURE IF EXISTS `users_register`$$

CREATE DEFINER=`root`@`localhost` PROCEDURE `users_register`(	
	`userName` VARCHAR(32),	
	`userGameId` VARCHAR(64),
	`userSocialId` VARCHAR(64)
)
TASK: BEGIN

    DECLARE existGameId VARCHAR(64);

	DECLARE EXIT HANDLER FOR SQLEXCEPTION
	BEGIN
		GET DIAGNOSTICS CONDITION 1
			@p1 = RETURNED_SQLSTATE, @p2 = MESSAGE_TEXT;				
			SELECT -9999 AS `result`, @p1 AS `state`  , @p2 AS `message`;
		ROLLBACK;		
	END;
	
    -- 이름 null 체크
	IF userName IS NULL THEN
		SELECT -1 AS result;
		LEAVE TASK;
	END IF;

    -- 게임 아이디 null 체크
    IF userGameId IS NULL THEN
		SELECT -2 AS result;
		LEAVE TASK;
	END IF;

    -- 소셜 아이디 null 체크
    IF userSocialId IS NULL THEN
		SELECT -3 AS result;
		LEAVE TASK;
	END IF;

    -- 게임 아이디가 이미 존재하는지 체크
    SELECT `game_id` INTO existGameId FROM `users` WHERE `game_id`=userGameId;

    -- 게임 아이디가 이미 존재한다면 예외처리
    IF existGameId IS NOT NULL THEN 
        SELECT 1 AS result;
		LEAVE TASK;
    END IF;
	
    -- 정상 등록 처리
	INSERT INTO `users` SET `game_id`=userGameId, `social_id`=userSocialId, `name`=userName;
	
    -- 정상 결과값 반환
	SELECT 0 AS `result`;

END$$

DELIMITER ;

 

 

---

Ref.

- https://genesis8.tistory.com/183