[MariaDB] 저장 프로시저

저장 프로시저

• 저장 프로시저(Stored Procedure)는 데이터베이스에 저장되어 실행될 수 있는 하나 이상의 SQL문의 집합

• 특성
  • SQL문을 미리 컴파일하여 저장함으로써 데이터베이스 서버의 부하를 줄이고 성능을 향상
  • 한 번 작성된 저장 프로시저는 여러번 재사용
  • 복잡한 조건문 반복문 등을 프로시저에서는 프로시저 문법에 맞게 사용할 수 있음
    • SQL은 본질적으로 선언문으로서 제어문에 대한 유연성 낮음(반복문 불가)
    • 프로시저는 절차적 언어로서 제어문 사용 가능
  • 데이터베이스 수준에서 직접 접근 권한을 제어할 수 있으므로 보안을 강화
  • 특정 사용자에게 프로시저 실행권한 부여
    • GRANT EXECUTE ON PROCEDURE 데이터베이스명.프로시저명 TO ‘testuser’@’localhost’;

• 목적
  • 개발자가 자주 사용하는 쿼리를 저장하고, 호출해서 사용하기 위함
  • 프로그램(ex.Spring)이 호출해서 사용하기 위함

• 프로시저 생성
  • DELIMIER //

    CREATE PROCEDURE 프로시저명 (parameters)

    BEGIN

    —SQL 문법

    END //

    DELIMITER ;

    • 파라미터의 기본 형식은 (IN 변수명 변수타입)
    • 생성문의 파라미터는 생략가능하고, 함수와 같이 parameter를 전달하여 실행하는 것도 가능

• 프로시저 호출
  • CALL 프로시저명();

• 생성된 프로시저 내용 조회
  • show create procedure 프로시저명;

• 실습
  • SELECT하는 author관련 프로시저 생성, 호출

  DELIMITER //
  CREATE PROCEDURE getUser(IN userID INT)
  BEGIN
  SELECT * FROM author WHERE id = userID;
  END //
  DELIMITER ;
  
  CALL getUser(2); -- id = 2

  

  author테이블에서 id가 2인 것 조회

  • post 테이블에 쉽게 INSERT할 수 있는 post관련 프로시저 생성
  • 사용자에게 title, contents, author_id만 입력받아 insert하도록

  DELIMITER //
  CREATE PROCEDURE postInsert(IN in_title VARCHAR(255), IN in_contents VARCHAR(3000), IN in_author_id INT)
  BEGIN
  insert into board.post(title, contents, author_id) values(in_title, in_contents, in_author_id);
  END //
  DELIMITER ;
  
  CALL postInsert('안녕','하세요', 3);

  

  post에 삽입된 것 확인 가능

• 변수 선언
  • DECLARE 변수명 변수타입 [DEFAULT default_value];
  • 반드시 프로시저나 함수의 본문 시작 부분, 즉 BEGIN 바로 밑에 위치

• 변수 수정
  • SET 변수명 = 수정할 값;

• 제어문
  • if문
    • IF 조건식 THEN

      — 조건이 참일 때 실행할 명령

      ELSE

      —조건이 거짓일 때 실행할 명령

      END IF;

      ⇒ ‘SELECT 컬럼명 INTO 변수’ 문과 함께 많이 사용

  • while문
    • WHILE 조건식 DO

      —조건이 참일 동안 반복 실행할 명령

      END WHILE;

• 실습
  • post 테이블에 if문 활용하여 고액 원고료 작가 조회(author_id에 따른 price 평균으로 IF조건)

  
  DELIMITER //
  CREATE PROCEDURE my_procedure(IN in_author_id INT)
  BEGIN
  DECLARE avg_price INT DEFAULT 0;
  SELECT AVG(price) INTO avg_price FROM post WHERE author_id = in_author_id;
  IF avg_price > 5000 THEN
  	SELECT '고액 원고료 작가입니다.' AS message;
  ELSE
  	SELECT '고액 원고료 작가가 아닙니다.' AS message;
  END IF;
  END //
  DELIMITER ;
  
  call my_procedure(5);

  

  • post 테이블에 while문으로 데이터 대량 insert 100

  DELIMITER //
  CREATE PROCEDURE add_data()
  BEGIN
  DECLARE a INT DEFAULT 0;
  WHILE a < 100 DO
  INSERT INTO post(title) VALUES(concat('qoth', a));
  SET a = a + 1;
  END WHILE;
  END //
  DELIMITER ;
  
  CALL add_data();

  

  qoth0~qoth99 확인