[SQL] 07-1 스토어드 프로시저 사용 방법

🧐기본 개념

• MySQL의 스토어드 프로시저는 SQL에 프로그래밍 기능을 추가해서 일반 프로그래밍 언어와 비슷한 효과를 낼 수 있음.

  SQL + 프로그래밍 기능 = 스토어드 프로시저

---

🐥스토어드 프로시저 기본

스토어드 프로시저의 개념과 형식

• 스토어드 프로시저란 MySQL에서 제공하는 프로그래밍 기능이며, 쿼리 문의 집합으로도 볼 수 있음.

• 어떠한 동작을 일괄 처리하기 위한 용도로도 사용함.

• 자주 사용하는 일반적인 쿼리를 반복하는 것보다는 스토어드 프로시저로 묶어놓고, 필요할 때마다 간단히 호출만 하면 편리하게 운영 가능

필수적인 형식

DELIMITER $$
CREATE PROCEDURE 스토어드_프로시저_이름(IN 또는 OUT 매개변수)
BEGIN

	-- 이 부분에 SQL 프로그래밍 코드를 작성

END $$
DELIMITER ;

• DELIMITER $$와 END $$ 부분은 필수항목으로 스토어드 프로시저를 묶어주는 기능을 함.

• CREATE PROCEDURE는 스토어드 프로시저를 만든 것뿐이며, 아직 실행(호출)한 것은 아님.

스토어드 프로시저를 호출하는 형식

-> CALL 다음에 스토어드 프로시저의 이름과 괄호를 붙여주면 됨.

CALL 스토어드_프로시저_이름();

스토어드 프로시저의 생성

• 인터넷 마켓 데이터베이스를 사용하도록 지정함.
• 기존에 user_proc 라는 이름의 스토어드 프로시저가 있다면 삭제하도록 함.
• user_proc 라는 이름의 스토어드 프로시저를 만듦.

스토어드 프로시저의 삭제

• 앞에서 생성한 user_proc의 내용을 삭제하려면 다음과 같이 DROP PROCEDURE를 사용할 수 있음.

• 주의할 점은 CREATE PROCEDURE에서는 스토어드 프로시저 이름 뒤에 괄호를 붙이지만, DROP PROCEDURE에서는 괄호를 붙이지 않아야 함.
  

---

👩🏻‍🔬스토어드 프로시저 실습

매개변수의 사용

• 스토어드 프로시저에서는 실행 시 입력 매개변수를 지정할 수 있음.

입력 매개변수를 지정하는 형식

IN 입력_매개변수_이름 데이터_형식

입력 매개변수가 있는 스토어드 프로시저를 실행하는 형식

CALL 프로시저_이름(전달_값);

• 스토어드 프로시저에서 처리된 결과를 출력 매개변수를 통해 얻을 수도 있음.

출력 매개변수의 형식

OUT 출력_매개변수_이름 데이터_형식

출력 매개변수가 있는 스토어드 프로시저를 실행하는 형식

CALL 프로시저_이름(@변수명);
SELECT @변수명;

입력 매개변수의 활용

• 입력 매개변수가 있는 스토어드 프로시저를 생성하고 실행하기
  

• 코드 내용

  • 에이핑크를 입력 매개변수로 전달함.
  • userName 매개변수에 대입함.
  • 에이핑크에 대한 조회를 수행함.

• 2개의 입력 매개변수가 있는 스토어드 프로시저를 만들기
  

• 코드 내용

  • 모두 한 줄에 써도 상관 없음.
  • 인원을 6으로, 평균 키를 165로 전달함. userNumber에는 6이, userHeight에는 165가 대입됨.
  • 인원이 6을 초과하고, 키가 165를 초과하는 가수 그룹이 조회됨. 소녀시대와 트와이스가 조회되었음.

출력 매개변수의 활용

1. 출력 매개변수가 있는 스토어드 프로시저 생성하기

   • 다음 스토어드 프로시저는 noTable이라는 이름의 테이블에 넘겨 받은 값을 입력하고, id 열의 최대값을 알아내는 기능을 함.
   • id 열의 최대값은 결국 방금 입력한 행의 순차번호임.
     
   • 코드 내용
     • 출력 매개변수인 outValue를 지정함.
     • INTO outvalue 구문으로 outvalue에 id 열의 최대값을 저장함.
     • 아직 noTable을 만든 적이 없으나, 스토어드 프로시저를 만드는 시점에는 아직 존재하지 않는 테이블을 사용해도 됨.

2. noTable 만들기

   • 간단히 id 열과 txt 열 만들기
     

3. 스토어드 프로시저 호출하기

   • 출력 매개변수의 위치에 @변수명 형태로 변수를 전달해주면, 그 변수에 결과가 저장됨.
   • 다음 두 줄을 계속 실행하면 값이 2, 3, 4 ...로 증가함.
     

SQL 프로그래밍의 활용

스토어드 프로시저 안에 SQL 프로그래밍을 활용하기

1. 조건문의 기본인 IF ~ ELSE 문을 사용하기

   • 가수 그룹의 데뷔 연도가 2015년 이전에미녀 '고참 가수', 2015년 이후(2015년 포함)이면 '신인 가수'를 출력하는 스토어드 프로시저 작성하기
     
   • 코드 내용
     • 매개변수로 가수 그룹의 이름을 넘겨 받음.
     • 데뷔 연도를 저장할 변수를 준비함.
     • 넘겨 받은 가수 이름으로 조회함.
     • 데뷔 일자(debut_date) 중에서 YEAR() 함수로 연도만 추출해서 변수 debutYear에 저장함.
     • IF ~ ELSE 문으로 데뷔 연도에 따라서 필요한 내용을 출력함.
     • '오마이걸'로 스토어드 프로시저를 테스트함.

2. 여러 번 반복하는 while 문 활용하기

   • 1부터 100까지 합계 계산하기
     

동적 SQL 활용하기

1. 다음 예제는 테이블을 조회하는 기능을 함.
   • 그런데 테이블은 고정된 것이 아니라, 테이블 이름을 매개변수로 전달받아서 해당 테이블을 조회함.
     
   • 코드 내용
     • 넘겨받은 테이블 이름을 @sqlQuery 변수에 SELECT 문으로 문자열을 생성해놓음.
       -> 결국 SELECT * FROM member가 생성된 것
     • SELECT 문자열을 준비하고 실행하기
     • 사용한 myQuery를 해제함.