이것이MySQL이다 - 01) 스토어드 프로그램

스토어드 프로시저

 

스토어드 프로시저의 개요

스토어드 프로시저(Stored Procedure, 저장 프로시저)

• MySQL에서 제공되는 프로그래밍 기능
• 쿼리문의 집합으로 어떠한 동작을 일괄 처리하기 위한 용도로 사용
• 쿼리 모듈화
  • 필요할 때마다 호출만 하면 훨씬 편리하게 MySQL 운영
  • CALL 프로시저_이름( ) 으로 호출

 

 

기본 형식

 

 

 

스토어드 프로시저 생성 예

 

 

 

스토어드 프로시저의 수정과 삭제

• 수정 : ALTER PROCEDURE
• 삭제 : DROP PROCEDURE

매개 변수의 사용

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

• 입력 매개 변수가 있는 스토어드 프로시저 실행 방법

 

 

 

 

매개 변수의 사용

• 출력 매개 변수 지정 방법

• 출력 매개 변수에 값 대입하기 위해 주로 SELECT… INTO문 사용
• 출력 매개 변수가 있는 스토어드 프로시저 실행 방법

 

프로그래밍 기능

• ‘SQL 프로그래밍’의 내용 대부분이 스토어드 프로시저에 적용 가능
• 더 강력하고 유연한 기능 포함하는 스토어드 프로시저 생성

 

스토어드 프로시저 내의 오류 처리

• 스토어드 프로시저 내부에서 오류가 발생했을 경우
• DECLARE 액션 HANDLER FOR 오류조건 처리할_문장 구문

 

 

스토어드 프로시저 실습

• 입력 매개 변수 1개의 스토어드 프로시저 생성

 

 

• 입력 매개 변수 2개의 스토어드 프로시저 생성

 

 

• 출력 매개변수 설정
  • 스토어드 프로시저 생성 및 테스트로 사용할 테이블 생성

 

 

 

출력 매개변수 설정

• 스토어드 프로시저 생성 및 테스트로 사용할 테이블 생성

 

 

• 스토어드 프로시저 안에 SQL 프로그래밍 활용
  • While문 활용한 2중 반복문

 

 

 

• 스토어드 프로시저 안에 SQL 프로그래밍 활용
  • While문 활용한 2중 반복문

 

 

• 현재 저장된 프로시저의 이름 및 내용 확인
• INFORMATION_SCHEMA 데이터베이스의 ROUTINES 테이블을 조회하면 내용 확인 가능

SELECT routine_name, routine_definition FROM INFORMATION_SCHEMA.ROUTINES
   WHERE routine_schema = 'sqldb' AND routine_type = 'PROCEDURE';

 

 

 

 

• 파라미터를 확인하려면 INFORMATION_SCHEMA 데이터베이스의 ROUTINES 테이블 조회

SELECT parameter_mode, parameter_name, dtd_identifier
    FROM INFORMATION_SCHEMA.PARAMETERS
    WHERE specific_name = 'userProc3';

 

 

 

• SHOW CREATE PROCEDURE문으로도 스토어드 프로시저의 내용 확인 가능

SHOW CREATE PROCEDURE sqldb.userProc3;

 

 

 

 

스토어드 프로시저의 특징

MySQL의 성능 향상

• 긴 쿼리가 아니라 짧은 프로시저 내용만 클라이언트에서 서버로 전송
  • 네트워크 부하 줄임으로 MySQL의 성능 향상

 

유지관리가 간편

• 응용 프로그램에서는 프로시저만 호출
  • 데이터베이스에서 관련된 스토어드 프로시저의 내용 수정/유지보수

 

모듈식 프로그래밍 가능

• 언제든지 실행 가능
• 스토어드 프로시저로 저장해 놓은 쿼리의 수정, 삭제 등의 관리 수월
• 모듈식 프로그래밍 언어와 동일한 장점 갖음

 

보안 강화

• 사용자 별로 테이블 접근 권한 주지 않고 스토어드 프로시저에만 접근 권한을 주어 보안 강화
• 뷰 또한 스토어드 프로시저와 같이 보안 강화 가능