[MySQL] Stored Procedure와 Stored Function

마이 SQL에서는 강력한 DB관리 기능을 제공할 뿐만 아니라

파이썬, 자바처럼 자료에 대한 프로그래밍 기능도 제공합니다.

MySQL안에서의 동작과 계산, 함수와 같은 기능을 사용할 수 있습니다.

 

파이썬과 약간 다른점이라면 Type을 항상 지정해야 한다는 점입니다.

 

 

설명 전 MySQL 기본준비

 

 

데이터베이스는 아무렇게나 만들어도 무방합니다.

(적당하게 MYDB로 지정)

 

그리고 데이터베이스에 접근해서 테이블을 만들어줍니다.

 

CREATE TABLE usertbl
( userID CHAR(8) NOT NULL PRIMARY KEY,
name VARCHAR(10) NOT NULL,
birthYear INT NOT NULL,
addr CHAR(2) NOT NULL,
mobile1 CHAR(3) NULL,
mobile2 CHAR(8) NULL,
height SMALLINT NULL,
mDate DATE NULL
);

 

8개의 column을 갖고 있는 usertbl 테이블입니다.

 

row의 값을 가상의 인간 '원빈' 군으로 넣어줍니다.

 

INSERT INTO usertbl VALUES ('WB', '원빈', 1977, '강릉', '019', '9876543', 176, '2023.2.7');

 

이렇게 보이면 준비 완료

 

 

1) 스토어드 프로시저(Stored Procedure)

 

 

프로시져가 시작한다는 선언은 DELIMITER 로 시작합니다.

 

DELIMITER $$

DELIMITER ;

 

그리고 CREATE PROCEDURE로 프로지셔 이름을 정의해줄 수 있습니다.

 

DELIMITER $$
CREATE PROCEDURE proc1()

 

이름에는 () 를 붙여줍니다.

프로시져에 들어갈 parameter도 넣어드리겠습니다.

 

DELIMITER $$
CREATE PROCEDURE proc1() (IN username VARCHAR(10))

 

프로시져가 동작할 영역은 BEGIN으로 시작선언을 해주고, END로 종료 선언을 해줍니다.

 

DELIMITER $$
CREATE PROCEDURE proc1() (IN username VARCHAR(10))
BEGIN
  SELECT * FROM userTbl WHERE name = userName;
END $$

 

종료는 앞에서 언급한바와 같이 DELIMITER;로 마무리해줍니다.

 

DELIMITER $$
CREATE PROCEDURE proc1(IN userName VARCHAR(10))
BEGIN
  SELECT * FROM userTbl WHERE name = userName;
END $$
DELIMITER ;

 

---

혹시나 오류가 뜰 가능성도 있기 때문에 이미 만들어져 있는 프로시져의 삭제 처리도 언급해보겠습니다.

 

DROP PROCEDURE IF EXISTS proc1;

 

프로시져가 이미 있을 경우 삭제시켜 줍니다.

 

---

 

이렇게 만든 프로시져는 어떻게 불러올수 있을까요.

 

CALL proc1('원빈');

 

바로 CALL문을 사용하여 불러옵니다.

 

뷰나 테이블에서 행을 불러오는 방법과 차이가 있습니다.

 

SELECT * FROM 테이블이름

 

이 아니라 , 

 

CALL 프로시져이름 ('해당 패러미터')

 

이렇게 저장되어 있는 형식을 불러오게 됩니다.

 

좋습니다. 그러면 프로시져를 사용하는 이유나 장점이 따로 있을까요?

 

자주 사용하는 쿼리를 필요할때마다 입력하는 것보다, 이를 미리 만들어놓고(모듈화)

 

필요할때마다 꺼내서 쓰면 훨씬 편할 것입니다.

 

그때문에 MySQL에서 미리 제공하고 있는 스토어드 프로시져를 사용하는 것입니다.

이는 스토어드 함수에서도 그대로 이어지는 장점입니다.

 

2) 스토어드 함수(Stored Function)

스토어드 프로시져와 이번에 소개할 스토어드 함수의 중요한 차이점이라고 한다면

 

RETURNS 문

 

바로 return이 존재하냐, 존재하지 않느냐 입니다.

 

파이썬의 def 함수와 유사합니다.

간단하게 아래의 형태와 똑같이 작용하는 스토어드 함수를 만들어보겠습니다.

 

def userfunc(a, b):
    return (a+b)*a

( 대충 (a+b)와 a를 곱하는 함수 )

 

 

마찬가지로 DELIMITER로 시작합니다.

 

DELIMITER $$
CREATE FUNCTION userFunc3(value1 INT, value2 INT)
	RETURNS INT
BEGIN
	RETURN (value1 + value2)*value1;
END $$
DELIMITER ;

 

사용하는 방법은 프로시져와 달리 SELECT를 사용해서 호출합니다.

 

SELECT userFunc3(1, 2)

 

결과값

 

 

---

최근 국내에서 만 나이의 허가 법안이 시행되면서, 나이 계산법이 이슈가 되고 있습니다.

2023년 6월부터, 전통적인 한국식 나이가 아니라, 생일에 따라서 나이를 하나 빼주는

'만나이 계산법'이 시행되죠?

 

 

 

간단하게, 만 나이 계산법은 아니고, 출생 연도만을 사용해서 

 

연 나이 계산을 진행하는 함수를 만들어 보겠습니다.

 

DELIMITER $$
CREATE FUNCTION getAgeFunc(bYear INT)
	RETURNS INT
BEGIN
	DECLARE age INT;
    SET age = YEAR(CURDATE()) - bYear;
    RETURN age;
END $$
DELIMITER;

 

int 형식인 출생연도를 bYEAR로 받아서, age로 선언합니다.

그리고 현재 연도인 YEAR(CURDATE())를 와 빼줍니다.

 

그리고 age 반환

 

SELECT문을 통해서 결과를 보겠습니다.

 

SELECT getAgeFunc(1994)

 

1994년생의 연 나이 29살로 제시됩니다.

 

스토어드 함수는 파이썬 보다는 강력한 함수 기능을 제공하진 않지만

MySQL에서 상상하는 기능이 없을 때 직접 만들 수 있어 편리합니다.

 

이상으로 프로시져 기능 설명은 마치겠습니다.