[Practice] 저장 프로시저, 저장 함수

1. 저장 프로시저

1) 테이블 생성

DROP DATABASE IF EXISTS bns; 
CREATE DATABASE bns; 
USE bns; 

DROP TABLE IF EXISTS user; 
CREATE TABLE user ( 
  id VARCHAR(20) NOT NULL COMMENT '유저 아이디', 
  name VARCHAR(20) NOT NULL COMMENT '유저 이름', 
  level TINYINT UNSIGNED NOT NULL COMMENT '유저 레벨', 
  money INT UNSIGNED NOT NULL COMMENT '소지금', 
  choo VARCHAR(20) NULL COMMENT '추천인', 
  PRIMARY KEY(id), 
  FOREIGN KEY(choo) REFERENCES user(id)  
) COMMENT '캐릭터정보';  

DROP TABLE IF EXISTS item; 
CREATE TABLE item ( 
  name VARCHAR(50) NOT NULL COMMENT '아이템 이름', 
  cat VARCHAR(32) NOT NULL COMMENT '아이템 종류', 
  level TINYINT UNSIGNED NOT NULL COMMENT '착용 가능 레벨', 
  atk INT UNSIGNED NOT NULL COMMENT '공격력', 
  def INT UNSIGNED NOT NULL COMMENT '방어력', 
  PRIMARY KEY(name) 
) COMMENT '게임아이템';  

DROP TABLE IF EXISTS market; 
CREATE TABLE market ( 
  no INT UNSIGNED NOT NULL COMMENT '거래 번호', 
  seller VARCHAR(20) NOT NULL COMMENT '판매자', 
  item VARCHAR(50) NOT NULL COMMENT '판매 아이템', 
  price INT UNSIGNED NOT NULL COMMENT '판매 가격', 
  PRIMARY KEY(no), 
  FOREIGN KEY(seller) REFERENCES user(id), 
  FOREIGN KEY(item) REFERENCES item(name) 
) COMMENT '아이템거래'; 

2) 데이터 삽입

INSERT INTO user VALUES ('chulsu', '김철수', 70, 100000, NULL); 
INSERT INTO user VALUES ('jrich', '안지환', 7, 1000, NULL); 
INSERT INTO user VALUES ('ruina', '김나라', 47, 49000, 'chulsu'); 
INSERT INTO user VALUES ('jamie', '조아라', 29, 12000, 'ruina'); 
INSERT INTO user VALUES ('yoma', '아리수', 1, 500, NULL); 

INSERT INTO item VALUES ('곤륜검', 'sword', 50, 540, 0); 
INSERT INTO item VALUES ('염화검', 'sword', 36, 147, 0); 
INSERT INTO item VALUES ('요마검', 'sword', 20, 64, 0); 
INSERT INTO item VALUES ('풍뢰검', 'sword', 45, 263, 0); 
INSERT INTO item VALUES ('단검', 'sword', 1, 1, 0); 

INSERT INTO market VALUES (1, 'chulsu', '곤륜검', 75000); 
INSERT INTO market VALUES (2, 'chulsu', '풍뢰검', 40000); 
INSERT INTO market VALUES (3, 'ruina', '염화검', 36000); 
INSERT INTO market VALUES (4, 'yoma', '단검', 10);

3) 저장 프로시저

순서	내용
1	사용자 소지금을 출력하는 프로시저 생성 및 확인
2	ruina가 jrich한테 1000원을 주는 저장 프로시저 생성
3	str 변수로 문자열을 입력받고 그 뒤에 부연적인 정보 반환하는 프로시저 생성 및 확인
4	변수를 이용하여 사용자의 레벨과 소지금을 확인하는 프로시저 생성 및 확인
5	거래를 수행할 때 거래 대상의 소지금이 부족하면 트랜잭션을 수행하지 않고 거래의 결과 값을 알 수 있는 프로시저 생성 및 확인 (0: 성공, -1: 실패)
6	트랜잭션, 예외 핸들러를 이용한 데이터 삽입

- 실습 쿼리문

/* 파라미터 IN을 이용하여 사용자 소지금을 출력하는 프로시저 생성 및 확인 */
DROP PROCEDURE IF EXISTS money_print;
DELIMITER //
CREATE PROCEDURE money_print ( IN user_name VARCHAR(20) )
BEGIN
  SELECT money FROM user WHERE id = user_name;
END //
DELIMITER ;

CALL money_print('ruina');
CALL money_print('jamie');

 

/* ruina가 jrich한테 1000원을 주는 저장 프로시저 생성 */

DROP PROCEDURE IF EXISTS money_trade;
DELIMITER //
CREATE PROCEDURE money_trade ( IN user1 VARCHAR(20), IN user2 VARCHAR(20), IN trade_money INT )
BEGIN
START TRANSACTION;
  UPDATE user SET money = money - trade_money WHERE id = user1;
  UPDATE user SET money = money + trade_money WHERE id = user2;
COMMIT;
END //
DELIMITER ;

 

CALL money_trade('ruina', 'yoma', 1000);
CALL money_trade('ruina', 'jrich', 1000);

 

/* str 변수로 문자열을 입력받고 그 뒤에 부연적인 정보 반환하는 프로시저 생성 및 확인 (1) */

DROP PROCEDURE IF EXISTS print_param;
DELIMITER //
CREATE PROCEDURE print_param ( IN str VARCHAR(20), OUT ret VARCHAR(30) )
BEGIN
  SET ret = concat(str, "1234");
END //
DELIMITER ;

CALL print_param("test", @ret);
SELECT @ret;

 

/* str 변수로 문자열을 입력받고 그 뒤에 부연적인 정보 반환하는 프로시저 생성 및 확인 (2) */

DROP PROCEDURE IF EXISTS print_param;
DELIMITER //
CREATE PROCEDURE print_param ( INOUT str VARCHAR(20) )
BEGIN
  SET str = concat(str, "1234");
END //
DELIMITER ;

SET @ret = "test";
CALL print_param(@ret);
SELECT @ret;

 

/* 변수를 이용하여 사용자의 레벨과 소지금을 확인하는 프로시저 생성 및 확인 */

DROP PROCEDURE IF EXISTS user_check;
DELIMITER //
CREATE PROCEDURE user_check ( IN user_name VARCHAR(30) )
BEGIN
  DECLARE var_level TINYINT UNSIGNED;
  DECLARE var_money INT;
  SELECT level, money INTO var_level, var_money FROM user WHERE id = user_name;
  SELECT concat('User Level: ', var_level);
  SELECT concat('User Money: ', var_money);
END //
DELIMITER ;

CALL user_check('ruina');

 

/* 거래를 수행할 때 거래 대상의 소지금이 부족하면 트랜잭션을 수행하지 않고 거래의 결과 값을 알 수 있는 프로시저 생성 및 확인 (0: 성공, -1: 실패) */

DROP PROCEDURE IF EXISTS user_trade;
DELIMITER //
CREATE PROCEDURE user_trade ( IN dec_user VARCHAR(20), IN inc_user VARCHAR(20), IN trade_money INT, OUT result INT )
BEGIN
  DECLARE var_money INT;
  SELECT money INTO var_money FROM user WHERE id = dec_user;

  IF var_money < trade_money THEN
    SET result = -1;
  ELSE
    START TRANSACTION;
      UPDATE user SET money = money - trade_money WHERE id = dec_user;
      UPDATE user SET money = money + trade_money WHERE id = inc_user;
    COMMIT;
    SET result = 0;
  END IF;
END //
DELIMITER ;

CALL user_trade('yoma', 'ruina', 1000, @ret);
SELECT @ret;

 

/* 트랜잭션, 예외 핸들러를 이용한 데이터 삽입 */

DROP PROCEDURE useradd;
DELIMITER //
CREATE PROCEDURE useradd ( IN var_id VARCHAR(20), IN var_name VARCHAR(20), IN var_level INT, OUT result INT )
BEGIN
  DECLARE EXIT HANDLER FOR SQLEXCEPTION
  BEGIN
    ROLLBACK;
    SET result = -1;
  END;

  START TRANSACTION;
    INSERT INTO user VALUES (var_id, var_name, var_level, 0, NULL);
  COMMIT;
  SET result = 0;
END //
DELIMITER ;

CALL useradd ('test1212', 'test1212', 30, @res);
SELECT @res;

CALL useradd ('test1212', 'test1212', 30, @res);
SELECT @res;

 

 

 

2. 저장 함수

순서	내용
1	yoma가 ruina한테 1000원을 주는 저장 프로시저 생성

- 실습 쿼리문

/* yoma가 ruina한테 1000원을 주는 저장 프로시저 생성 */
DROP PROCEDURE IF EXISTS user_trade;
DELIMITER //
CREATE FUNCTION user_trade( dec_user VARCHAR(20), inc_user VARCHAR(20), trade_money INT ) RETURNS INT
BEGIN
  DECLARE var_money INT;
  SELECT money INTO `var_money FROM user WHERE id = dec_user;
  IF var_money < trade_money THEN
    -- SET result = -1;
    RETURN -1;
  ELSE
    -- START TRANSACTION;
    UPDATE user SET money = money - trade_money WHERE id = dec_user;
    UPDATE user SET money = money + trade_money WHERE id = inc_user;
    -- COMMIT;
    -- SET result = 0;
    RETURN 0;
    END IF;
END //
DELIMITER ;

SELECT user_trade('yoma', 'ruina', 1000);