[MySQL] Stored Procedure

 

<설명>

  - Database 안에 저장된 pre-complie 된 SQL statement 모음

  - 일반적인 computing 언어의 subroutine || subprogram

  - Procedure 는 항상 name, parameter list, SQL statment 를 가짐.

  - Trigger, 다른 Procedure, Application(Java, Python, PHP, etc) 등을 통해 Procedure를 호출(Invoke).

  - 하나 이상의 Command, Search for, Insert, Database의 Update or Delete data 를 이행하는데 쓰임.

  - 거의 모든 관계형 DB 시스템에서 지원함.

  - 기업형 Application 처럼 정기적으로 반복하는 DB Cleanup, Processing payroll 등 많은 task를 합쳐 Single Task로 수행.

 

---

 

<특징>

  - Application 성능을 향상

  - Stored Procedure가 생성되면, DB 안에 컴파일되어 저장됨.

  - Application 과 DB 사이의 Traffic을 줄임. SQL statement 덩어리를 전부 보내는 대신 Procedure 의 name과 parameter만 보내므로.

  - 재사용 가능 && 어떤 Application에서든 data를 정확한 sequence에 따라 전달

  - 항상 보안성을 가짐. DB 관리자가 다른 DB table의 권한을 줄 필요 없이 Stored Procedure 권한을 자유롭게 줄 수 있음.

 

---

 

<Procedure 생성>

<기본 구조>
DELIMITER &&
CREATE PROCEDURE [Procedure 이름] ([선택할 mode : IN | OUT |INOUT] [Parameter 이름] [Datatype])
BEGIN
	[선언문]
    [실행문]
END &&
DELIMITER;

 

 

  [Parameter Mode 설명]

    1) IN Parameter :

      - Default 모드.

      - Parameter를 Attribute 처럼 Input 으로 받음. User가 Parameter를 정의하면, 호출 Program이 argument를 Stored Procedure로 전달.

      - Parameter 값은 항상 보호됨.

---

    2) OUT Paramter :

      - Parameter 를 output 처럼 전달.

      - Stored Procedure 안에서 값이 변할 수 있으며, 변한 값은 호출 Program으로 다시 전달.

      - 호출 Program 시작시, Procedure는 OUT Parameter의 초기 값에 접근 불가.

---

    3) INOUT Paramter :

      - IN 과 OUT Parameter 의 조합.

      - 호출 Program이 argument를 전달할수도 있고, INOUT Parameter를 변경하여 새로운 값을 호출 프로그램에 전달도 가능.

 

---

 

  [예시_1]

<이건 예시 - Parameter 없을 때>
DELIMITER &&
CREATE PROCEDURE get_merit_student ()
BEGIN
	SELECT * FROM student_info 	WHERE marks > 70;
    SELECT COUNT(stud_code) AS Total_Student FROM student_info;
END &&
DELIMITER;

 

  [결과_1]

이미지 출처 : JavaPoint

 

---

 

  [예시_2]

<이건 예시 - INOUT Parameter>
DELEMITER &&
CREATE PROCEDURE displace_marks (INOUT var1 INT)
BEGIN
	SELECT marks INTO var1 FROM student_info WHERE stud_id = var1;
END &&
DELIMITER;

 

  [결과_2]

이미지 출처 : JavaPoint

 

---

 

<List 보기 및 삭제>

  [탐색]

<기본 구조>
SHOW PROCEDURE STATUS [[LIKE 'patern'] 또는 [WHERE [탐색 조건]]];

<이건 예시>
SHOW PROCEDURE STATUS WHERE db='mystudentdb';

 

  [탐색 예시 결과]

이미지 출처 : JavaPoint

 

---

 

 [삭제]

DROP PROCEDURE [Procedure 이름];

 

---

 

<출처 1> https://www.javatpoint.com/mysql-procedure

<출처 2> https://www.computerhope.com/jargon/p/procedure.htm

<출처 3>