프로시저와 함수 생성을 위한 권한 설정
// 권한 설정되어 있지 않은 경우 에러메시지
ERROR 1418: This function has none of DETERMINISTIC, NO SQL, or READS SQL DATA in its declaration and binary logging is enabled (you *might* want to use the less safe log_bin_trust_function_creators variable)
// 권한 설정여부 체크하는 쿼리
**SHOW GLOBAL VARIABLES LIKE 'LOG_BIN_TRUST_FUNCTION_CREATORS';
//** LOG_BIN_TRUST_FUNCTION_CREATORS 값이 OFF이면 생성 불가
// ON 값으로 변경
**SET GLOBAL LOG_BIN_TRUST_FUNCTION_CREATORS = 1;**
- MySQL에서 프로시저(Procedure)를 사용해 주면 여러 쿼리를 프로시저 하나로 실행시킬 수 있다
- 함수는 쿼리를 수행한 후 값을 가져오는 것이 중점이지만 프로시저는 여러 쿼리를 한번에 수행하는 것이 중점이다
- 속도는 프로시저가 더 빠르기에 프로시저 같은 경우 실행, 처리를 할 때 주로 사용되고, 함수는 간단한 계산이나 수치 결과를 나타낼 때 주로 사용한다.
프로시저
- 프로시저를 호출하면 MySQL은 DataBase카탈로그에서 프로시저 이름을 찾아 SQL문을 컴파일하고 메모리 공간(Cache)에 저장한 뒤 프로시저를 실행시킨다.</aside>
- <aside> 💡 DB카탈로그 -> SQL컴파일 -> 메모리저장 -> 실행
- 장점
- 하나의 요청으로 여러 SQL문을 실행 가능
- 네트워크 소요 시간을 줄일 수 있음(여러개의 쿼리를 처리하는 시점에서 네트워크 부하 줄임)
- 보수성이 뛰어나다.
- 개발 업무를 구분하여 개발할 수 있다. (DB관련 처리를 API처럼 만들어 제공)
- If, case, loop 같은 제어,반복문을 사용해 보다 향상된 SQL코드를 작성할 수 있고 프로시저 내에서 다른 프로시저를 호출할 수도 있다.
- 단점
- 프로시저는 처리성능과 재사용면에서 좋지 않다.
예시1 (프로시저 정의)
DELIMITER $$ -- 구분자
CREATE PROCEDURE `PROC_TEST` ( -- 프로시저명
-- 파라미터 선언
PARAM_NAME VARCHAR(20)
, PARAM_AGE INTEGER
)
BEGIN
DECLARE PARAM_NUM INTEGER; -- 변수 선언
-- SELECT 사용 시에는 조회한 컬럼을 반드시 INTO로 변수 안에 넣어줘야 에러가 나지 않는다
SELECT COUNT(*) + 1
INTO PARAM_NUM -- 변수에 값 할당
FROM TB1;
-- INSERT 문 수행
INSERT
INTO TB1(NUM, NAME, AGE)
VALUES
(PARAM_NUM, PARAM_NAME, PARAM_AGE);
END $$
DELIMITER ;
CALL PROC_TEST('테스트',30); -- 프로시저 실행
SELECT NAME, AGE, NUM FROM TB1; -- 프로시저로 삽입된 내용 확인IN,OUT,INOUT
- IN
- 프로시저에 값을 전달하며, 프로시저 내부에서 값을 수정할 수는 있지만 프로시저가 반환되고 나서 호출자가 수정은 불가능하다. 즉, 원본 값은 프로시저가 끝난 후에도 유지되며, 프로시저는 IN 파라미터의 복사본을 사용한다
- OUT
- 프로시저의 값을 호출자에게 다시 Return한다. 초기값은 프로시저 내에서 NULL이며 프로시저가 반환될 때 새로운 값이 호출자에게 Return되고 프로그램이 시작될 때, OUT 파라미터의 초기값에 접근할 수 없다.
- INOUT
- 호출자에 의해 하나의 변수가 초기화되고 프로시저에 의해 수정된다. 간단하게 IN+OUT이라고 생각하면 될 것 같다. 프로시저가 Return될 때 프로시저가 변경한 사항은 호출자에게 Return된다.
예시 2( IN,OUT,INOUT 사용)
DELIMITER $$
CREATE PROCEDURE 'TEST_PROC2'(
IN loopCount1 INT, -- input : 10
IN loopCount2 INT, -- input : 20
OUT rst1 INT,
OUT rst2 INT,
INOUT rst3 INT
)
BEGIN
DECLARE NUM1 INTEGER DEFAULT 0; -- DEFAULT : 초기값 설정
DECLARE NUM2 INTEGER DEFAULT 0;
DECLARE NUM3 INTEGER DEFAULT 0;
WHILE NUM1<loopCount1 DO -- NUM1은 0~9까지 10번반복
WHILE NUM2<loopCount2 DO -- NUM2는 0~19까지 20번반복
SET NUM3 = NUM3 + 1;
SET NUM2 = NUM2 + 1;
END WHILE; -- NUM2가 19가 되면 나옴
SET NUM1 = NUM1 + 1;
SET NUM2 = 0;
END WHILE;
SET rst1 = NUM1; -- 첫번째 WHILE에서 10
SET rst2 = NUM3; -- WHILE 2개가 마칠 때까지 더해져서 200
SET rst3 = rst1 + rst2 + rst3; -- 240 = 10+200+(호출 시 @NUM3에 할당한 값) 30
END $$
DELIMITER ;
이번에는 파라미터에 IN, OUT을 사용하고 프로시저 내부에서 반복문도 사용해보았다. IN, OUT, INOUT 이외 문법에 대해서는 아래에 추가적으로 설명할 예정이다.
예시 2 (호출)
-- 변수 초기화
DECLARE @NUM1 = 0;
DECLARE @NUM2 = 0;
DECLARE @NUM3 = 0;
-- NUM3에 값 30 할당(@는 전역변수, 프로시저가 끝나도 계속 유지되는 값)
SET @NUM3 = 30;
CALL TEST_PROC2(10, 20, @NUM1, @NUM2, @NUM3);
SELECT @NUM1, @NUM2, @NUM3; -- RESULT => @NUM1 : 10, @NUM2 : 200, @NUM3 : 240
추가 문법 및 용어 정리
프로시저 목록 확인
SHOW PROCEDURE STATUS;
프로시저 내용 확인
SHOW CREATE PROCEDURE 프로시저이름;
프로시저 삭제
DROP PROCEDURE 프로시저이름;
ex ) DROP PROCEDURE IF EXISTS procedure_name; (이미 프로시저가 정의 되어 있다면 삭제)
조건문(IF)예시
IF total >= 95 AND total <= 100 THENSET grade = 'A+';
ELSE
IF total >= 90 THENSET grade = 'A';
END IF;
END IF;
조건문(CASE)예시
CASE NUM
WHEN 'Apple' THENSET 'result_Str' = 'apple_cookie';
WHEN 'Banana' THENSET 'result_Str' = 'banana_cookie';
ELSESET 'result_Str' = 'orange_cookie';
END CASE;
함수
CREATE FUNCTION 'GET_NAME' ( -- 함수명
NAME VARCHAR(20) -- 파라미터
) RETURNS VARCHAR(20) -- 반환할 데이터타입
BEGIN
-- 변수 선언
DECLARE NAME_TITLE VARCHAR(20);
DECLARE RETURN_VALUE VARCHAR(20);
-- 수행할 쿼리
SELECT CONCAT(NAME, ' IS ') INTO NAME_TITLE; -- 조회한 컬럼은 INTO로 변수에 넣어야 함
SET RETURN_VALUE = NAME_TITLE; -- 변수에 값을 넣을 때 SET을 사용한다
RETURN RETURN_VALUE; -- 반환할 값
END
// 함수 호출 시
SELECT GET_NAME('철수');
레퍼런스
https://wakestand.tistory.com/518
https://wakestand.tistory.com/503