마리아db프로시저 실행

마리아 db를 사용하다보면 프로시저를 만들일이 생긴다.

인터넷 검색하면 잡다한게 많이 나와서 프로시저 만들고 실행하는 잘나와 있는 사이트가 있어서 소개한다.

1. Procedure에서 transaction 처리

BOOK테이블에는 초기책에 대한 정보가 입력되고 그리고 BOOK_SELL에 판매될 책의 정보가 입력된다. 프로시저를 이용해서 BOOKS, BOOKS_SELL 테이블에 입력하도록 한다.

/* DELIMITER는 프로시저 앞,뒤의 위치하여 안에 있는 부분은  한번에 실행될 수 있게 하는 역할을 한다. */
DELIMITER $$
CREATE PROCEDURE INSERT_BOOK
(IN _BOOKID CHAR(5), IN _BOOKNAME VARCHAR(20), _PRICE DOUBLE, _BOOKTYPE VARCHAR(10), OUT RESULT INT)
/*
@DESCRIPTION
	BOOKS 테이블에  인서트하고 BOOKS_SELL에 추가된 금액으로 인서트한다.
@PARAM
	_BOOKID: 고유키
	_BOOKNAME : 제목
	_PRICE: 원가
	_BOOKTYPE : 책종류
@RETURN
	RESULT : 실패(-1), 성공 (0)
*/

BEGIN
	/* 가격을 변경할 변수를 선언한다. */
	DECLARE _SELLPRICE DOUBLE;

	/* 만약 SQL에러라면 ROLLBACK 처리한다. */
	DECLARE exit handler for SQLEXCEPTION
	  BEGIN
		ROLLBACK;        
		SET RESULT = -1;  
	END;

	/* 트랜젝션 시작 */
	START TRANSACTION;
		/* BOOK에 인서트 */
		INSERT INTO BOOKS(bookID, bookName, bookOriginPrice, bookType)
		VALUE(_BOOKID, _BOOKNAME, _PRICE, _BOOKTYPE);		

		/* 책종류에 맞게 가격조정 */
		IF _BOOKTYPE = 'novel' THEN
			SET _SELLPRICE = _PRICE + _PRICE * (10/100);
		ELSEIF _BOOKTYPE = 'art' THEN
			SET _SELLPRICE = _PRICE + _PRICE * (15/100);
		ELSE
			SET _SELLPRICE = _PRICE + _PRICE * (20/100);
		END IF;

		/* 조정된 값을 BOOKS_SELL에 저장한다. */
		INSERT INTO BOOKS_SELL(bookID, bookSellPrice, bookType)
		VALUE(_BOOKID, _SELLPRICE, _BOOKTYPE);

	/* 커밋 */
	COMMIT;
	SET RESULT = 0;
END$$
DELIMITER ;

작성된 프로시저를 실행해보자.

CALL INSERT_BOOK('00001','AAA',10000,'novel',@RESULT);
SELECT @RESULT;

CALL INSERT_BOOK('00002','AAB',15000,'art',@RESULT);
SELECT @RESULT;

CALL INSERT_BOOK('00003','AAC',20000,'novel',@RESULT);
SELECT @RESULT;

@RESULT 는 결과값이 리턴된다. (성공시:0, 실패시 -1) 각 테이블을 조회해보면 정상적으로 레코드가 보일 것이다.

원문에는 커서도 있는데 커서가 필요한 분은 원문을 보기 바란다.

처음으로 마리아 db 프로시저를 만들고 실행을 하려고 하는데 실행을 어떻게 하는지 몰라서 구글링 하다가 알게 되었다.

파라미터로 @RESULT를 넣어주면 리턴값이 담겨서 결과가 리턴된다.

마이바티스를 사용할 경우에는

<parameterMap id="paramMap" type="hashMap">
  <parameter property="RESULT" mode="OUT" jdbcType="INTEGER" javaType="int" />
</parameterMap>


<select id="procUser" statementType="CALLABLE" parameterMap="paramMap">
{ call proc_user(?) }
</select>

이런식으로 하면 위에서 실행한 효과와 비슷하게 된다.

그리고 하이디sql에서 프로시져를 선택한 후 CREATE코드 탭을 눌러서 create를 하려고 하면 에러가 난다.

상단에 DELIMITER $$를 추가해야 에러가 안난다.

그리고 끝에는 END$$ 만해주면 끝나는듯.

mysql -uUSER_ID -pPASSWORD

치고 들어가서

show databases;

해서 db를 보고 원하는 db를 선택

use your_db;

그리고 프로시저 복사 붙여넣기 하면 됨. 잘 끝나면

Query OK, 0 rows affected (0.01 sec)

이런식으로 나온다.

권한이 없어서 안된다고 나올 수도 있다. 이건 다다음 글에서...

그리고 조회는

show procedure status where db='your_db_name';

그리고 삭제는

drop procedure '스키마명'.'테이블명'

예) drop procedure aa.tbl_aa

참고:

https://yookeun.github.io/database/2015/04/10/mysql-procedure/