[MYSQL] 프로시저(스토어드 프로그램)

💻프로시저(스토어드 프로그램)

프로시저는 일련의 쿼리를 하나의  함수처럼 실행하기 위한 쿼리의 집합이다.  매개 변수를 받을 수 있고, 반복적으로 사용할 수 있는  BLOCK이다.
일반적으로 연속 실행 또는 구현이 복잡한 트랜잭션을 수행하는  PL/SQL BLOCK을 데이터베이스에 저장하기 위해 생성한다.

 

💻프로시저(스토어드 프로그램)를 사용하는 이유

🔑데이터베이스의 보안의 향상

MYSQL의 스토어드 프로그램은 자체적인 보안 설정 기능을 가지고 있기 때문에 스토어드 프로그램 단위로 실행 권한을 부여할 수 있다. 이러한 보안 기능을 조합해서 특정 테이블의 읽기와 쓰기 또는 특정 칼럼에 대해서만 권한을 설정하는 등 세밀한 권한 제어가 가능하다.

주요 기능을 스토어드 프로그램으로 작성한다면 SQL 인젝션과 같은 기본적인 보안 사고는 피할 수 있을 것이다.

 

🔑기능의 추상화

일련번호와 같은 복잡한 방식으로 생성되는 경우 생성 방식이 복잡하여 , MYSQL의 Auto_increment를 이용할 수가 없다. 만약 애플리케이션에서 일련번호 생성용 모듈을 개발한다면 개발하는 언어별로 호환되지 않을뿐더러 직접 SQL 클라이언트에서는 사용이 불가능하다. 일련번호 생성용 프로그램을 MYSQL 서버의 스토어드 프로그램으로 구현한다면 애플리케이션이나 SQL 클라이언트에서도 쉽게 사용할 수 있다. 

 

🔑절차적 기능 구현 

DBMS 서버에서 사용하는 SQL쿼리는 절차적인 기능을 제공하지 않는다. 즉, SQL 쿼리에서는 IF나 WHILE과 같은 제어 문장을 사용할 수 없다. 그러나 스토어드 프로그램은 DBMS 서버에서 절차적인 기능을 실행할 수 있는 제어 기능을 제공한다. 가끔 SQL 문장으로 절대 처리할 수 없는 것들이 있다. 일반적으로 이런 상황에서는 데이터를 애플리케이션에서 가공한 후 다시 데이터 베이스에 저장하는 형태로 개발을 진행을 하지만  스토어드 프로그램을 이용해 절차적인 기능을 구현한다면 최소한 네트워크 경유에 걸리는 시간만큼은 줄일 수 있고 불필요한 애플리케이션 코드를 줄일  수 있다.

 

🔑개발 업무의 구분

순수하게 애플리케이션을 개발하는 조직과 DBMS 관련 코드를 개발하는 조직이 별도로 구분되어 있다면 DBMS코드를 개발하는 조직에서는 트랜잭션 단위로 데이터베이스를 처리를 하는 스토어드 프로그램을 만들어 API처럼 만들어 제공하고 애플리케이션 개발자는 스토어드 프로그램을 호출해서 사용하는 형태로 역할을 구분해서 개발을 진행할 수도 있다.

🔑네트워크 소요시간 절감

일반적으로 애플리케이션과 데이터베이스 서버는 같은 네트워크 구간에 존재하므로 SQL의 실행 성능에서 네트워크를 경유하는 데 걸리는 시간은 많은 신경을 쓰지 않는다. 그러나 하나하나 쿼리가 가볍고 빠르게 처리될 수 있다면 네트워크를 경유하는 데 걸리는 시간에서 이슈가 발생할 수 있다. 하나의 프로그램에서 100번, 200번씩 실행해야 하는 쿼리를 스토어드 프로그램으로 구현한다면 스토어드 프로그램을 호출할 때 한 번만 네트워크를 경유하면 되기 때문에 네트워크 소요 시간을 줄이고 성능을 개선할 수 있다.

 

💻프로시저(스토어드 프로그램)의 단점

 

🔨낮은 처리 성능 

스토어드 프로그램은 MYSQL 엔진에서 해석되고 실행된다. 하지만 MYSQL 서버는 스토어드 프로그램과 같은 절차적 코드 처리에 중점을 두고 처리하는 것이 아니라서 다른 프로그램 언어에 비해 성능이 떨어진다.

 

 

🔨애플리케이션 코드의 조각화

애플리케이션의 설치나 배포 작업이 갈수록 복잡해진다. 각 기능을 담당하는 프로그램 코드가 자바와 MYSQL스토어드 프로그램으로 분산된다면 애플리케이션의 설치나 배포가 복잡해 서지고 유지보수 또한 어려워질 수 있다.

 

 

💻프로시저(스토어드 프로그램)의 사용 예제

1. 프로시저 생성

• DEFINER : 접근 권한
• PROCEDURE : 프로시저 이름
• 매개변수 + 타입
• SET 변수 설정
  • 기호가 없을 때 프로시저 실행이 끝나면 초기화
  • @가 있으면 프로시저가 끝나도 계속 유지
• CONCAT() : 문자열 합치기
• PREPARE : 실행 준비
• EXECUTE : PREPARE 된 것을 실행 

 

⚒프로시저 기본 형태

CREATE DEFINER=`DB권한이 있는 아이디`@`%` PROCEDURE `프로시저이름`
BEGIN
  SELECT * 
  FROM 테이블명
END

DB에 접속하는 ID명을 적어준다. 여기에 적는 ID는 무조건 권한이 있는 ID를 넣어야 한다.

 

⚒프로시저 인자 값 및 파라미터를 받을 때

CREATE DEFINER=`DB권한이 있는 아이디`@`%` PROCEDURE `프로시저이름`(
   IN 변수명 자료형   
   OUT 변수명 자료형 
)
BEGIN
    SELECT *
    FROM 테이블명
    WHERE 컬럼명 = 변수명
END

받을 변수명과 자료형 (INT, VARCHAR(40) 등)을 입력해준다. 받을 때는 IN을 사용하고 반환하는 변수의 자료형은 OUT

 

 

⚒프로시저 변수 할당

 

CREATE DEFINER=`DB권한이 있는 아이디`@`%` PROCEDURE `프로시저이름`(
    IN 변수명 자료형
)
BEGIN
    SET @v_code = '123';
    SELECT *
    FROM 테이블명
    WHERE 컬럼 = 변수명 AND
    	  컬럼 = @v_code
EN

변수를 할당할 때 바로 값을 적어도 되지만 서브 쿼리를 사용하여 (@v_code = (select,,,,from...))할당할 수 있다.

⚒프로시저 조건문 사용하기 IF, ELSEIF, OR, AND..

CREATE DEFINER=`DB아이디`@`%` PROCEDURE `프로시저명`(
    IN 변수명 자료형
)
BEGIN
    SET @v_code = '123';
    IF @v_code!='' THEN
      SELECT *
      FROM 테이블명
      WHERE 컬럼 = 변수명 AND
    	    컬럼 = @v_code
    END IF;
END

IF 조건문 OR 조건문 THEN
실행 문구
ELSE IF 조건문 THEN
실행 문구
END IF;
등의 형태로 쓸 수 있으며
NULL 체크는 IS NOT NULL / IS NULL을 쓸 수 있다.

⚒프로시저 생성 예제

CREATE DEFINER='root'@'%' PROCEDURE test_prc(id int(10))
BEGIN
SET @t1 = CONCAT('SELECT * FROM test_', id);
PREPARE s FROM @t1;
EXECUTE s;
END

이런 식의 프로시저는 테이블 이름을 배열 변수에 담아서 LOOPING 돌려 한번에 처리 가능하다. 

 

---

​

REFERENCE

https://unlimitedcoding.tistory.com/15

https://velog.io/@ym1085/MySQL-%ED%94%84% EB% A1% 9C% EC% 8B% 9C% EC% A0%80% EB% 9E%80