[혼자 공부하는 SQL] Chapter 7-1. 스토어드 프로시저 사용 방법

스토어드 프로시저(Stored Procedure) : SQL에 프로그래밍 기능을 추가하여 프로그래밍 언어와 비슷한 효과를 낸 것

​

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

​

DELIMITER $$

CREATE PROCEDURE 스토어드_프로시저_이름 (IN 또는 OUT)

BEGIN

~~

END $$

DELIMITER ;

​

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

​

<스토어드 프로시저 생성과 삭제>

 

1) 앞서 작성한 스토어드 프로시저 문법대로 간단한 스토어드 프로시저인 user_proc을 작성

member 테이블에 모든 데이터를 출력하는 명령을 저장하였고, CALL user_proc으로 간단하게 실행 완료

 

2) 스토어드 프로시저의 삭제는 DROP PROCEDURE 문으로 간단하게 수행 가능

이 때 CREATE 문과 다르게 괄호를 붙이지 않음

​

<스토어드 프로시저 실습 - 매개변수>

​

스토어드 프로시저는 강화된 기능인 매개변수를 사용할 수 있음

매개변수는 프로시저에 입력값을 주는 입력 매개변수와

프로시저가 출력될 때 결과값으로 출력되는 출력 매개변수로 구분 가능

 

1) IN userName VARCHAR(10)이라는 구문으로 문자형 매개변수 userName을 적용한 후

SELECT 문의 WHERE 문에서 입력 매개변수와 조건을 비교하는 명령을 삽입

그 결과 CALL 문에서 준 조건인 에이핑크에 해당하는 데이터만 출력 완료

 

2) 여러 개의 입력 매개변수를 설정하는 것도 가능

위 코드는 각각 멤버가 6명을 초과하면서 신장이 165을 초과하는 그룹만을 출력하는 명령

 

3) 출력 매개변수는 OUT outValue INT처럼 입력 매개변수와는 IN과 OUT을 제외하면 문법적으로 동일

현재는 프로시저에 사용되는 noTable 테이블이 없기 때문에 CALL 문 사용 시 오류가 발생

하지만 테이블이 존재하지 않아도 프로시저 생성에는 문제가 없음

 

4) noTable 테이블을 생성한 후 CALL 문 사용 시 프로시저가 정상적으로 작동

​

<SQL 프로그래밍 활용>

 

1) 스토어드 프로시저를 활용한 SQL 프로그래밍은 다양하게 사용할 수 있음

위 코드는 member 테이블에서 2015년 이후 데뷔 그룹은 신인, 그 이전은 고참가수로 구분하여 출력하는 명령

 

2) 위 코드는 반복문인 WHILE 문을 활용해 1부터 100까지의 자연수의 합을 구하는 명령

 

3) PREPARE 문과 EXECUTE 문을 활용한 동적 SQL도 사용 가능