SQL(MySQL) - 프로시저(Procedure)

Jobmania·2023년 3월 13일
0

SQL 잘쓰기

목록 보기
11/16

프로시저(Procedure)?

Procedure 처음 사용 이유 : 더미 데이터를 넣는 과정에서 일일히 넣는 것은 너무나 비효율적인 작업이 됨. SQL을 FOR문 처럼 반복작업을 수행할 필요가 있었음. -> 여러 작업을 절차적 및 일괄적으로 실행할 수 있는 프로시저

SQL 프로시저는 SQL 쿼리를 저장하고 재사용 가능한 코드 블록으로 만들어 데이터베이스 작업을 효율적으로 수행할 수 있습니다.

장점

  1. 재사용성: SQL 프로시저는 일련의 SQL 문을 묶어서 하나의 논리적 단위로 만들어 코드를 재사용할 수 있습니다. 이를 통해 반복적인 작업을 줄이고 유지보수성을 높일 수 있습니다. -> 가장중요

  2. 성능 향상: SQL 프로시저는 데이터베이스 서버에 미리 컴파일되어 저장됩니다. 이렇게 되면 매번 쿼리를 실행할 때마다 SQL 문을 분석하고 컴파일하는 오버헤드를 줄일 수 있습니다. 또한, 프로시저를 사용하면 데이터베이스 서버와 클라이언트 간의 네트워크 트래픽이 줄어들어 성능을 향상시킬 수 있습니다.

  3. 보안성: SQL 프로시저를 사용하면 데이터베이스에 대한 권한을 정확히 제어할 수 있습니다. 일반 사용자는 프로시저를 실행할 수 있지만 프로시저 내부의 데이터베이스 객체에 대한 직접적인 액세스는 불가능합니다. -> 캡슐화와 비슷하다고 생각한다.?

  4. 일관성: SQL 프로시저는 중복 코드를 제거하고 일관된 실행 방법을 적용할 수 있어 데이터 일관성을 유지할 수 있습니다.

  5. 유연성 : 기능 변경이 편하며 (특정 기능을 변경할 때 프로시저만 변경하면 됨) 여러 어플리케이션과 공유가 가능합니다. (API처럼 제공가능)

단점

  1. 복잡성: SQL 프로시저는 일반적으로 SQL 쿼리보다 더 복잡합니다. 이는 코드를 작성하고 디버그하기 위한 시간과 노력이 많이 필요할 수 있다는 것을 의미합니다.

  2. 유지보수성: SQL 프로시저를 변경하는 것은 복잡한 프로시저에 대해 수작업으로 변경할 필요가 있습니다. 이는 유지보수성을 떨어뜨릴 수 있습니다.

  3. 테스트: SQL 프로시저는 코드 테스트를 위해 특수 도구를 사용해야 합니다. 이는 추가적인 작업과 시간이 필요하다는 것을 의미합니다.

  4. 이식성: SQL 프로시저는 데이터베이스 제품 간에 호환되지 않을 수 있으므로 이식성이 제한될 수 있습니다.

생성 문법

CREATE OR REPLACE PROCEDURE 프로시져이름 (
매개변수명1 [ IN || OUT || INOUT ] 데이터타입,
매개변수명2 [ IN || OUT || INOUT ] 데이터타입,
파라미터3, 파라미터4...);

IS[AS]
변수, 상수 등을 선언

BEGIN  <------ 실행부
쿼리문

END 프로시져 이름;

※인수의 타입 선언부분정리

  1. IN => 내부 프로그램에 제공
    변수이름 IN VARCHAR2; --인수선언할때 byte수 지정안함 (인수는 크기를 주지 않습니다.)
    변수이름 IN 테이블이름.컬럼명%TYPE;
    변수이름 IN 테이블이름.컴럼명%TYPE := 값;
    변수이름 IN 테이블이름.컬럼명%TYPE DEFAULT 값;
  1. OUT => 호출자에게 제공
    (프로시저 실행 시점에 OUT 매개변수를 변수 형태로 전달하고, 프로시저 실행부에서 이 매개변수에 특정 값을 할당, )
    변수이름 IN VARCHAR2;
    변수이름 IN 테이블이름.컬럼명%TYPE;
  • OUT파라미터는 프로시저에서 로직 수행후, 해당 매개변수에 값을 할당해서 프로시저 호출부분에서 이 값을 참조할 수 있다. → 에러코드, 에러메세지를 전달하는 목적으로 사용
  1. IN OUT => 입력과 동시에 출력용으로 사용할 수 있다.
    변수이름 IN VARCHAR2;
    변수이름 IN 테이블이름.컬럼명%TYPE;

예시1) - no parameter

CREATE PROCEDURE procedureName(
		-- 파라미터 입력
        )
BEGIN
    DECLARE i INT DEFAULT 1;
        
    WHILE i <= 100 DO
        INSERT INTO emp(name, dob, department)
          VALUES( concat('익명',i), '1988-08-08', CASE FLOOR(RAND() * 4)
        WHEN 0 THEN '생산부'
        WHEN 1 THEN '인사부'
        WHEN 2 THEN '개발부'
        ELSE '기술사업부'
);
        SET i = i + 1;
    END WHILE;
END

CALL procedureName;   -- 프로시저 실행.
간단한 100개의 유저 더미 테이블 생성!!

예시2) - exist parameter

DELIMITER $$
DROP PROCEDURE IF EXISTS insert_emp_data$$ 

CREATE PROCEDURE insert_emp_data(
  IN emp_name VARCHAR(255),
  IN emp_dob VARCHAR(255),
  IN emp_department VARCHAR(255)
)
BEGIN
  INSERT INTO emp (name, dob, department)
  VALUES (emp_name, emp_dob, emp_department);
END;

CALL insert_emp_data('John Smith', '1990-01-01', '개발부');
-> 한개씩 특정 데이터를 넣는 procedure예시

SQL 프로시저의 강점은 아무래도 재사용성에 이라고 생각한다. 결국엔 SQL 문법에 실수를 피할 수 있지만 유지보수 측면에서는 좋지 않을 수도 있다고 생각한다.

참고

profile
HelloWorld에서 RealWorld로

0개의 댓글