[SQL / Database] 저장 프로시저(Stored procedure) 사용해보기

💡저장 프로시저(Stored procedure) 사용해보기

💜 저장 프로시저(Stored procedure) 알아보기

• 여러번 사용해야하는 SQL 문이 있을 때 매번 길게 타이핑하는 것이 불편해서 저장하여 사용하고 싶다면 저장 프로시저를 이용하는 방법이 있다.
• 저장 프로시저는 간단하게 SQL 문 저장 기능이라고 생각하면 된다.

---

💜 DBeaver 에서 저장 프로시저 사용하기

• 현재 데이터베이스의 하위 항목인 Procedures 를 우클릭 후 Create New Procedure 를 클릭한다.

• 그럼 위와 같이 창이 뜨는데 여기서 프로시저명을 정하여 입력한 후 확인 버튼을 클릭한다.

• 그럼 위와 같은 화면으로 전환되는데, Source 탭에서 BEGIN 과 END 사이에 재사용하고 싶은 코드를 작성하면 된다.
• 주의할 점은 SELECT 문 끝에 세미콜론(;) 을 넣어주어야 에러가 나지 않고 잘 동작한다는 것이다.
• 이제 SQL 편집기에서 CALL 프로시저명() 의 형태로 실행하면 BEGIN 과 END 사이 에 저장해둔 코드가 실행된다.
  (혹시 제대로 동작하지 않는다면 CALL 데이터베이스명.프로시저명() 으로 실행해보자!)

---

💜 SQL 명령어로 저장 프로시저 사용하기

// 기존 프로시저 삭제할 때
DROP PROCEDURE IF EXISTS 데이터베이스명.프로시저명;

// 프로시저 저장할 때
DELIMITER $$
$$
CREATE PROCEDURE 데이터베이스명.프로시저명()
BEGIN
  SELECT * FROM 테이블명 where 조건문;
END 
$$
DELIMITER ;

// 프로시저 사용할 때
CALL 데이터베이스명.프로시저명();

• 기존에 같은 이름의 프로시저가 존재하면 프로시저 생성이 불가하므로, 기존의 프로시저를 삭제해야 한다. 그 때 위처럼 DROP PROCEDURE 문을 사용하면 된다.
• SQL 문 작성 시 문장끼리 구분하기 위해 세미콜론(;) 을 사용하는데 이 때문에 프로시저 생성 시 에러가 발생할 수 있으므로 DELIMITER 를 사용한다.
  • DELIMITER 는 줄바꿈 문자를 $$ 이런 걸로 임시로 변환해달라는 의미의 SQL 명령어이다.

---

💜 저장 프로시저 미사용/사용 시 속도 비교하기

출처: 코딩애플

• 작성된 SQL 문은 일반적으로 위와 같은 과정을 거쳐서 실행된다.
  1. 코드의 문법오류 존재 여부 체크
  2. 테이블 확인 및 해당 테이블 열람권한 체크
  3. 이미 실행된 적 있는 쿼리 여부 체크(실행된 적 있는 경우 4, 5번 과정 생략)
  4. 쿼리를 실행할 다양한 방법 중 최적의 방법 선택
  5. 해당 쿼리 및 실행 방법을 임시로 메모리에 저장(재사용 대비)
  6. 컴파일 및 실행 후 테이블에서 데이터 출력
• 저장 프로시저 사용 시 3번 과정에서 체크 후 우측으로 빠져서 실행되므로, 아주 조금 빠르게 동작할 수 있으나 실제 CPU 에서의 SQL 문 작동속도는 직접 SELECT 문을 쓰나 저장된 프로시저를 쓰나 차이가 거의 없다.