SQL - Routine, Stored Procedure

Introduction to Stored Routines

• 저장 루틴 개요:
  • 저장 프로시저와 함수는 SQL 명령어 집합을 서버에 저장하여 필요할 때 호출할 수 있는 기능입니다. 이는 반복적인 작업을 자동화할 때 유용합니다​.
  • 저장 프로시저는 계산을 수행하고 데이터를 처리하여 결과를 반환합니다.

주요 특징:

• 프로시저는 여러 번 호출 가능.(반복작업 최소화)
  • 예시 : 사용자가 100명 이상 DB에 동일한 쿼리를 실행하는 경우, 효율적인 방법으로 저장 프로시저를 사용하는 것이 좋습니다. 이를 통해 쿼리 로직을 데이터베이스 내에 미리 저장해두고, 사용자들은 프로시저만 호출함으로써 성능을 최적화할 수 있습니다.
• 입력 매개변수를 받아 계산을 수행할 수 있음.
  • 파라미터(매개변수)를 사용하여 작업을 할 수 있습니다. 코딩에서 input 데이터를 넣는 것처럼 DB에서도 똑같이 가능합니다.

---

The MySQL Syntax for Stored Procedures

• MySQL 저장 프로시저 문법:
  • 세미콜론(;)은 기본적으로 구문 종료 기호로 사용되며, 프로시저를 작성할 때는 구분자를 변경해야 합니다​.
    • 이 때 구분자는 사용자가 하고 싶은 기호로 정하시면 됩니다. ex) '$$', '//', 등..
  • DELIMITER 명령을 사용해 새로운 구분자를 설정한 후, 프로시저를 작성하고 다시 기본 구분자(;)로 변경합니다.
• 사용 예시

DELIMITER $$
CREATE PROCEDURE select_employees()
BEGIN
    SELECT * FROM employees LIMIT 1000;
END$$
DELIMITER ;

# 프로시저 호출
call employees.select_employees();
call select_employees();
call select_employees;

• 저장 프로시저를 사용하여 매개변수를 처리하고, 데이터베이스 작업을 자동화하는 방법을 설명하고 있습니다.
• 호출 방법은 위의 3가지 중 하나만 사용하시면 됩니다. 그리고 mysql기준으로 옆에 보시면 stored procedure를 보시면 저희가 만든 프로시저가 생성 되어있을 것이고 그 칸에 마우스를 올리시면 번개 모양을 누르셔도 실행이 가능 합니다.
• 생성 방법 또한 해당 stored procedure 칸으로 가셔서 마우스 우클릭 후 create 를 누르셔도 가능합니다!

---

Stored Procedures with an Input Parameter

• 입력 매개변수를 사용한 저장 프로시저:
  • 저장 프로시저는 입력된 값을 쿼리에서 활용할 수 있습니다. 이 값은 IN 매개변수로 전달되며, 프로시저 내에서 계산이 이루어지고, 결과를 반환합니다​.
• 사용 예시

DELIMITER $$
 USE employees $$
 CREATE PROCEDURE emp_avg_salary(IN p_emp_no INTEGER)
 BEGIN
	SELECT
		e.first_name, e.last_name, avg(s.salary)
	FROM
		employees e
			join
		salaries s ON e.emp_no = s.emp_no
	WHERE
		e.emp_no = p_emp_no;
END $$

DELIMITER ;

# 프로시저 호출
call emp_avg_salary(11300);

• 이번에는 p_emp_no라는 매개변수를 추가하였습니다. 이렇게 되면 프로시저 호출을 할 때 괄호 안에 파라미터 타입과 같은 값(11300)을 넣어주게 되면 where절이 emp_no = 11300 인 값을 찾습니다.
• 또는 위에서 말했던 방식인 마우스로 호출 하는 방법을 사용하시면 파라미터 값을 추가하라는 조그마한 창이 생기고 그 안에 값을 넣으면 원하는 데이터가 출력 되게 됩니다.

---

Stored Procedures with an Output Parameter

• 저장 프로시저 출력 매개변수:
  • OUT 매개변수는 쿼리가 실행된 후 결과 값을 저장합니다.
  • 프로시저의 본문에서 쿼리를 작성할 때, SELECT INTO 구문을 사용하여 값을 출력 매개변수로 전달해야 합니다

 DELIMITER $$
 USE employees $$
 CREATE PROCEDURE emp_avg_salary_out(IN p_emp_no INTEGER, OUT p_avg_salary DECIMAL(10, 2))
 BEGIN
	SELECT
		avg(s.salary)
    # INTO 사용
	INTO p_avg_salary FROM
		employees e
			join
		salaries s ON e.emp_no = s.emp_no
	WHERE
		e.emp_no = p_emp_no;
END $$

• 해당 구문은 e.emp_no = p_emp_no(input 값)에 해당하는 평균 salary 값을 출력하기 전 output 파라미터를 이용하여 값을 출력할 때 가능한 값 최대 10자리 숫자 중 소수점 이하 2자리를 허용합니다.
• 따라서 출력할 떄 소수점 앞에 8자리가 허용 되고 소수점은 2자리인 값이 출력하게 됩니다.

---

이상입니다.