[MySQL] 6. 프로시저(stored procedure)와 추억

프로시저에는 개인적으로 특별한 추억이 있다. 대학교 3학년, 전과를 결심하고 처음으로 신청한 수업 중 하나가 DB수업이었다. 오라클 SQL Developer를 사용한 수업이었는데 나만 3학년이고 나머지는 모두 1,2학년이었다. 게다가 과도 조경과라고 되어있으니(전과 신청 학기라 전과는 다음학기에 실반영되었다) 교수님이 출석 부를때마다 나를 아주 신기하게 쳐다봤던 기억이 있다.ㅎㅎ 당시의 나는 설치부터 막히는 사람이었기 때문에.. 질문을 마구마구 쏟아부었는데 가끔 너의 열정에 지친다고 하셨었다(ㅋㅋㅋ). 그때가 안식년 직전이셨어서 지금은 은퇴하셔서 학교에 찾아가도 이제는 뵐 수 없게 되었다. 올해 7월에 서울에 있는 개발사 면접 제안을 주셨을때 갈 수 있었다면 참 좋았을걸, 망할 코로나로 2달내내 입원해있느라 못간게 너무 아쉽다.

 

내가 헐 나 전과해야지, 하고 불안해 하던 마음을 종착시키도록 도와주신게 바로 이 교수님이시다. 솔직히 SQL 수업에서 진행하는 예시는 뻔할 뻔자라 좀 지루해하고 있었는데, 어느날 갑자기 날 부르시더니 현업에서 사용하시던 프로시저를 보여주셨었다.

윈폼 프로그램에서 사용되는 프로시저로 로그인, 회원가입, 게시글, 무슨.. 회원 권한 조회? 같은 것들이었다. 버튼을 클릭하면 동작한다며 양쪽으로 뭔가를 틀어놓고 하나하나 설명해 주시던게 그날따라 왜 그렇게 멋있게 보였던지.. 수업이 있었던 날도 아닌데 학교에서 마주쳤다는 이유로 뜬금없이 불려가서 이것저것 실제로 동작하는 DB 프로그램을 구경한 그 하루가 나에게 아주 좋은 기억으로 남아있다. 왜 부르셨을까 돌이켜보니 어느 수업이 끝나고 제가 이걸 실제로 쓸 일은 아직 잘 없겠네요.. 이러고 실망했어서 그랬었던것 같다(그땐 앱 팔때라 파이어베이스만 했음..ㅠ)

언젠가 교수님께서 우연히 내 블로그를 보시게 된다면 감사하다는 말씀을 전하고 싶다. 교수님,, 보고 계시나요?? 4학년때 첫 면접을 보고 겁먹어서 개발 취업을 접었던 제가 다시 코드를 잡았답니다. 돌고 돌아 다시 돌아온 이느낌.. 직장인 개발자가 되면 꼭 찾아갈게요

---

 

stored procedure

프로시저는 자주 실행하는 SQL 쿼리 문장들을 저장해주는 기능이다. js 코딩에서의 function과 같다. CREATE문과 DROP문을 통해 생성하고 삭제할 수 있다. 일반 CREATE문과 다른 점은 DELEMITER이라는 키워드를 사용해야 한다는 점이다.

 

DELEMITER는 직역하자면 '구문 문자' 인데, SQL에서는 각 문장 끝에 붙는 세미콜론(;)을 가리킨다. DELEMITER는 이 '문장이 끝남을 뜻하는 문자를 설정하는 역할'을 한다. DELEMITER $$ 라고 쓰게 되면, 각 문장의 끝에 세미콜론 대신 $$ 이라는 문자열이 붙게 된다.  

-- 만일 동일한 이름의 프로시저가 해당 DB에 있다면 DROP(삭제)하라
DROP PROCEDURE IF EXISTS DB명.프로시저명; 

-- 세미콜론(;)을 $$ 이라는 문자로 바꾸어라.
-- (세미콜론을 만나면 프로시저가 도중에 중단될 수도 있기 때문)
DELIMITER $$ : 
$$
-- 지정한 데이터베이스에 프로시저를 생성한다.
CREATE PROCEDURE DB명.프로시저명()  
-- BEGIN과 END 사이에 들어간 SQL문이 순차적으로 실행된다.
BEGIN 
  SELECT * FROM ....
END 
$$
DELIMITER ;

위의 프로시저를 생성하는 문법 위에 붙은 DELIMITER $$ 은 BEGIN과 END 사이에 작성되는 세미콜론을 $$으로 치환한다. 이유는 프로시저 저를 만드는 코드 안에 세미콜론이 들어가면 중간에 실행이 중단될 위험이 있기 때문이다. 그러나 프로시저 작성을 마치면 다시 세미콜론이 필요하게 된다. 따라서 프로시저 생성을 마친 END문 뒤에 $$를 다시 세미콜론으로 바꾸는, $$ DELIMITER ;을 작성하였다.

 

 

디비버(Dbeaver)를 사용하면 GUI로 프로시저를 생성할 수 있다. Procedures에서 우클릭을 하고, get_user이라는 프로시저를 만들어 본다. Source에 프로시저 생성 문법의 틀이 자동으로 작성되므로, BEGIN과 END 사이에 내가 수행할 쿼리 문장을 작성해 주기만 하면 된다. Persist를 클릭해 반영한다.

 

작성한 프로시저를 사용하기 위해서는 CALL을 사용해야 한다.

CALL 프로시저이름();

모든 유저를 출력하는 get_user() 프로시저를 실행하였다.

 

 

SET, @변수

 

SQL은 변수를 만들 수 있도록, 변수 생성 문법인 SET을 제공한다. SET을 사용하면 변수를 생성하고 초기화하며, 값을 할당해 줄 수 있다. SET을 사용할 때에는 ' SET @변수이름 ='값 ' 으로 사용하는데, SET이 아닌 SELECT로 변수를 생성하고자 할 때에는 등호 기호를 := 으로 사용해 주어야 한다.

 

SET @변수이름 = '값' 
-- 또는 
SELECT @변수이름 := '값'

변수를 생성하고 출력해 보았다. SET = 과, SELECT := 을 사용할 수 있다.

 

만일 이미 생성한 변수의 값을 변경하려면, 한번 더 변수를 @변수이름 으로 호출하여 값을 넣어주면 된다. 이렇게 만든 @변수는 전역 변수로 모든 곳에서 사용 가능하다. 또한 DB접속을 종료하면 사라진다.

 

DECLARE

 

이와 비슷한 변수 생성 문법으로 DECLARE가 있다. DECLARE는 프로시저 문법 안에서 사용하는 변수 생성 문법이다.

DECLARE 변수 = 값 은 변수를 만든 프로시저 안에서만 사용할 수 있는 지역 변수이고, 프로시저 실행을 종료하면 사라진다.

 

 

 

프로시저와 파라미터

프로시저는 함수와 같은 역할을 한다고 했으므로, 파라미터를 사용할 수 있다.

IN은 프로시저를 사용할 때 들여보낼 파라미터를 뜻하고, OUT은 프로시저에서 바깥으로 보낼 파라미터를 뜻한다.

IN/OUT 파라미터이름 파라미터타입 으로 작성하며, 값을 할당할 때에는 SET을 사용한다.

DROP PROCEDURE IF EXISTS DB명.프로시저명; 

DELIMITER $$ : 
$$
-- 프로시저에 IN(넣어줄)파라미터와 OUT(내보낼)파라미터를 작성한다.
CREATE PROCEDURE DB명.프로시저명(IN 파라미터1 타입, OUT 파라미터2 타입) 
BEGIN // BEGIN과 END 사이에 들어간 SQL문이 순차적으로 실행된다.
  SELECT * FROM 테이블명 WHERE 조건 = 파라미터1
  SET 파라미터2 = '값'
END 
$$
DELIMITER ;

 

예시로 2021년에 가입한 유저의 데이터를 불러오기 위하여, creDate라는 파라미터를 받아 joinDate와 creDate가 일치하는 레코드를 조회해 보았다.

IN 파라미터로 2021 이라는 값을 받아와 조회한다.

 

 

이제 파라미터를 받아오는 법을 알게 되었으므로, 본격적으로 로그인 프로시저를 만들어보기 위한 준비를 해보자.

우선 사용자가 입력한 email이 테이블에 존재하는지 검사하는 프로시저를 만들어본다. 만일 사용자가 입력한 이메일이 테이블에 존재하면 isSuccess에 1을, 그렇지 않으면 -1을 할당한다. 순서를 정리하자면, 다음과 같다.

 

1. 사용자가 입력한 이메일을 파라미터로 받는다. 2. 이메일이 테이블에 존재하는지 확인한다. 3. 확인한 결과를 저장한다. 4. 확인한 결과가 참이면 isSuccess에 1을, 그렇지 않으면 -1을 저장하고 내보낸다.

 

'확인한 결과를 저장' 하는 것은 'SELECT 쿼리 조회 결과를 저장하라' 는 뜻이다. 이를 수행하기 위해서는 쿼리의 결과를 변수에 저장해주는 INTO문법이 필요하다.

 

INTO

쿼리의 결과를 변수에 저장한다.

DROP PROCEDURE IF EXISTS chalishop.get_phonenumber; 
DELIMITER $$
$$
CREATE PROCEDURE chalishop.get_phonenumber(IN 파라미터 VARCHAR(100))  
BEGIN 

	-- 결과를 저장하기 위해, 변수를 선언해 놓는다.
    DECLARE 변수명 VARCHAR(100);
    
    -- IN 파라미터로 받은 값이 테이블에 있는지 세아린다. 
    -- INTO 문으로 SELECT 조회 쿼리가 반환한 값을 저장한다.
    SELECT COUNT(*) 
	INTO 변수명 
	FROM chalishop.`user` 
	WHERE 조건 = 파라미터    
    
END 
$$
DELIMITER ;

 

이렇게 하면 SELECT문의 결과가 INTO 변수명 의 '변수명' 에 저장된다. email은 PK이므로 중복이 불가능하다. 따라서 출력 가능한 최대 숫자는 1이다. 유저가 보낸 이메일이 테이블에 존재할 경우, '변수명' 의 값은 1이 되게 된다.

 

 

 

이제 4번인 '4. 확인한 결과가 참이면 isSuccess에 1을, 그렇지 않으면 -1을 저장하고 내보낸다.' 를 실행하기 위해 IF문을 사용해 주어야 한다. SQL에서는 프로시저나 뒤에서 실습할 함수(function) 를 작성할 때 IF문을 사용할 수 있다. IF, THEN, ELSEIF, ELSE, END IF로 구성되어 있다.

 

IF (조건식1) THEN 
  조건식1이 참일 때 실행되는 쿼리;
ELSEIF (조건식2) THEN 
  조건식2가 참이 때 실행되는 쿼리;
ELSE 
  상위 조건식들에 모두 해당하지 않을 때 실행되는 쿼리문;
END IF;

 

 

이 세 가지를 사용하여, '테이블에 이메일이 존재하는지 검사해주는 프로시저'를 작성하였다.  userIdCount라는 변수를 선언해두고, SELECT문의 결과를 해당 변수에 저장한다. 만일 userIdCount가 1이면 OUT으로 내보낼 isSuccess에 1을, 그렇지 않으면 -1을 저장하고 프로시저를 종료한다.

call에 파라미터를 전해주니 성공을 뜻하는 1이 출력되었다.

 

성공적으로 작동하는 것을 확인했으니 password를 전해주고, 아이디가 존재하며 비밀번호가 일치하면 변수로 지정한 '성공'이, 그렇지 않으면 '실패'가 OUT 파라미터에 저장되도록 get_login 로그인 프로시저를 작성하였다. AND를 이용해 조건문을 걸어주었고, DECLARE로 성공과 실패를 미리 정의하여 IF문으로 조회 결과에 따라 성공과 실패를 나누어 저장하도록 작성하였다.

 

isSuccess를 출력해보니 1 이라는 값이 출력되었다. (로그인 성공)

 

직접 예시를 만들며 사용해보니 현업에서 프로시저를 애용한다면 큰 효과를 낼 수 있을 것 같다. 아이디가 있는지 검사하고, 있으면 아이디와 비밀번호가 일치하는지 검사하고, 또 그 검사 결과를 반환하는 백엔드의 길고 긴 문장을 짧게 축약해 주는것에 큰 도움이 되지 않을까 싶다.