MariaDB에서 CREATE PROCEDURE 사용법

# MariaDB의 CREATE PROCEDURE: 저장 프로시저의 모든 것 데이터베이스 관리에서 반복적이고 복잡한 작업을 간소화하기 위해 사용되는 저장 프로시저는 효율성과 재사용성을 극대화하는 중요한 도구입니다. MariaDB의 `CREATE PROCEDURE` 명령어는 이러한 저장 프로시저를 생성하는 데 사용됩니다. 이 글에서는 저장 프로시저의 기본 개념부터 상세한 구문 설명, 그리고 활용법까지 폭넓게 다룹니다.

 

 

 

---

MariaDB의 CREATE PROCEDURE는 저장 프로시저를 생성하기 위해 사용됩니다. 저장 프로시저는 데이터베이스에 저장된 SQL 코드의 집합으로, 재사용 가능하고 복잡한 작업을 효율적으로 처리할 수 있도록 도와줍니다.

 

---

 

구문:	CREATE [OR REPLACE] [DEFINER = { user | CURRENT_USER | role | CURRENT_ROLE }] PROCEDURE [IF NOT EXISTS] sp_name ([proc_parameter[,...]]) [characteristic ...] routine_body
proc_parameter	는 IN, OUT, 또는 INOUT 유형을 지정하며, 데이터 타입도 설정 가능합니다.
routine_body	는 유효한 SQL 프로시저 문(statement)입니다.
특징:	IN/OUT/INOUT 매개변수: IN은 값만 전달하며 수정되지 않습니다. OUT은 프로시저 실행 결과를 호출자에게 반환합니다. INOUT은 호출자가 값을 전달하고, 프로시저가 값을 수정하여 반환합니다.

사용 예제

DELIMITER //
CREATE PROCEDURE simpleproc (OUT param1 INT)
BEGIN
  SELECT COUNT(*) INTO param1 FROM t;
END;
//
DELIMITER ;
CALL simpleproc(@a);
SELECT @a;

---

1. CREATE PROCEDURE란?

 

저장 프로시저(Stored Procedure)는 미리 정의된 SQL 문장의 집합으로, 데이터베이스에 저장된 프로그램 코드라고 볼 수 있습니다. 이를 통해 일련의 SQL 작업을 자동화할 수 있습니다.

저장 프로시저의 주요 이점:

• 반복 작업의 자동화: 동일한 작업을 반복적으로 실행할 때 유용.
• 성능 향상: 서버에서 직접 실행되므로 네트워크 왕복 비용 절감.
• 유지관리 용이: 중앙에서 코드 변경 가능.

 

---

2. 구문 상세 설명

`CREATE PROCEDURE`의 기본 구문은 다음과 같습니다.

CREATE [OR REPLACE]
  [DEFINER = { user | CURRENT_USER | role | CURRENT_ROLE }]
  PROCEDURE [IF NOT EXISTS] sp_name ([proc_parameter[,...]])
  [characteristic ...] routine_body

주요 요소

• `OR REPLACE`: 기존 프로시저가 있다면 삭제 후 재생성.
• `DEFINER`: 프로시저의 소유자를 지정.
• `sp_name`: 저장 프로시저 이름.
• `proc_parameter`: 매개변수 목록으로, `IN`, `OUT`, `INOUT` 중 하나를 사용.
• `routine_body`: 실제 SQL 작업을 포함한 프로시저의 본문.

IN/OUT/INOUT 매개변수

• IN: 호출자가 입력값을 전달.
• OUT: 프로시저가 호출자에게 결과값 반환.
• INOUT: 입력값을 전달하고, 수정된 값 반환.

---

3. IN/OUT/INOUT 매개변수의 차이

`CREATE PROCEDURE`에서 매개변수는 프로시저의 동작에 중요한 역할을 합니다.

1. IN 매개변수

입력값으로 사용되며, 호출자가 값을 전달합니다. 프로시저 내에서 변경되더라도 호출자에게 영향을 미치지 않습니다.

2. OUT 매개변수

프로시저 실행 결과를 호출자에게 반환합니다. 프로시저 내부에서만 값을 설정할 수 있으며 초기값은 NULL입니다.

3. INOUT 매개변수

호출자가 입력값을 전달하고, 프로시저가 값을 수정하여 반환합니다. 호출자와 프로시저 간 상호작용이 필요한 경우 유용합니다.

---

 

구문 요소	설명
CREATE	새 저장 프로시저 생성
OR REPLACE	기존 프로시저를 삭제 후 재생성
DEFINER	실행 권한을 가진 사용자 정의
proc_parameter	매개변수 유형(IN, OUT, INOUT) 설정

---

4. SQL에서 사용하는 특징 옵션

MariaDB는 프로시저 작성 시 다음 옵션들을 지원합니다.

• CONTAINS SQL: SQL 문장을 포함하지만 데이터베이스 읽기나 쓰기를 수행하지 않음.
• NO SQL: SQL 문장을 포함하지 않음.
• READS SQL DATA: 데이터베이스를 읽지만 쓰지 않음.
• MODIFIES SQL DATA: 데이터베이스의 데이터를 수정.

5. MariaDB에서 CREATE OR REPLACE 사용법

`CREATE OR REPLACE` 구문은 기존 프로시저를 삭제한 후 재생성합니다. 이 과정에서 기존 권한은 유지됩니다.

사용 예제

DELIMITER //
CREATE OR REPLACE PROCEDURE update_count (OUT count INT)
BEGIN
  SELECT COUNT(*) INTO count FROM users;
END;
//
DELIMITER ;

이 코드는 `users` 테이블의 행 수를 반환하는 프로시저를 생성하거나 업데이트합니다.

---

6. 실제 사용 예제

다음은 저장 프로시저의 실제 활용 사례입니다.

기본 저장 프로시저

DELIMITER //
CREATE PROCEDURE add_user (IN username VARCHAR(50))
BEGIN
  INSERT INTO users (name) VALUES (username);
END;
//
DELIMITER ;

프로시저 호출

CALL add_user('John Doe');

---

 

저장 프로시저 기능	예제 코드
데이터 삽입	INSERT INTO 사용
데이터 조회	SELECT 및 반환값 설정
데이터 수정	UPDATE 문 사용

---

7. 문제 해결 및 고려 사항

1. 충돌 방지

• 기존 SQL 함수와 동일한 이름을 피하세요.
• 프로시저 이름은 명확하고 고유하게 설정합니다.

2. 권한 관리

• `CREATE ROUTINE` 권한이 필요하며, 프로시저 작성자는 기본적으로 `EXECUTE` 권한을 부여받습니다.

---

8. 성능 최적화 팁

저장 프로시저를 사용할 때 성능 최적화를 고려하는 것이 중요합니다. 아래는 성능을 최적화하는 몇 가지 팁입니다.

1. 복잡한 쿼리 최적화

저장 프로시저 내에서 복잡한 쿼리를 사용하면 성능에 영향을 미칠 수 있습니다. 쿼리 성능을 분석하고, 필요한 인덱스를 추가하거나 쿼리 구조를 단순화하세요.

2. 임시 테이블 사용

저장 프로시저 내에서 임시 테이블을 사용하여 중간 결과를 저장하고, 이후 계산이나 조인을 처리하면 성능을 높일 수 있습니다.

3. 매개변수 최적화

프로시저의 매개변수를 적절하게 설정하고, 불필요한 데이터 타입을 피하세요. 데이터 타입이 너무 크면 성능에 부정적인 영향을 미칠 수 있습니다.

---

9. 에러 처리

저장 프로시저에서는 오류가 발생할 수 있습니다. 이를 처리하기 위한 방법을 아래와 같이 설명합니다.

1. 기본적인 오류 처리

MariaDB는 기본적으로 저장 프로시저 내에서 오류를 처리할 수 있는 메커니즘을 제공합니다. 오류가 발생하면, 이를 처리하여 더 나은 사용자 경험을 제공할 수 있습니다.

2. 사용자 정의 오류 메시지

저장 프로시저 내에서 오류가 발생한 경우, SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'Error message'; 명령을 사용하여 사용자 정의 오류 메시지를 전달할 수 있습니다.

BEGIN
    -- 오류 발생 예시
    SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'Custom error occurred';
END

3. 트랜잭션과 오류 처리

저장 프로시저에서 트랜잭션을 사용할 때는 오류가 발생하면 롤백하여 데이터 일관성을 유지할 수 있습니다. 예를 들어, ROLLBACK과 COMMIT을 사용하여 트랜잭션을 관리할 수 있습니다.

---

10. 보안 고려사항

저장 프로시저는 데이터베이스에 직접 접근하는 중요한 부분이므로 보안에 신경 써야 합니다. 보안을 고려한 저장 프로시저 설계 방법을 아래와 같이 설명합니다.

1. 최소 권한 원칙

저장 프로시저를 사용하는 사용자에게 최소한의 권한만 부여해야 합니다. 불필요한 권한을 부여하면 보안에 취약해질 수 있습니다.

2. 사용자 정의 함수와 프로시저 권한 분리

저장 프로시저와 사용자 정의 함수(UDF)에 대한 권한을 분리하여 관리하는 것이 좋습니다. 이는 보안 관리의 복잡성을 줄이고, 각 객체에 대한 권한을 보다 정확하게 제어할 수 있게 해줍니다.

3. SQL 인젝션 방지

SQL 인젝션 공격을 방지하기 위해서는 저장 프로시저 내에서 매개변수를 안전하게 처리해야 합니다. 사용자 입력 값을 적절히 검증하고, 동적 SQL을 사용할 경우 PREPARE와 EXECUTE 구문을 활용하여 보안성을 강화하세요.

---

11. 성능 모니터링 도구

저장 프로시저의 성능을 모니터링하는 다양한 도구들이 존재합니다. 이러한 도구를 사용하여 프로시저의 실행 성능을 평가하고 최적화할 수 있습니다.

1. EXPLAIN PLAN

EXPLAIN 명령을 사용하여 쿼리 실행 계획을 분석할 수 있습니다. 이를 통해 저장 프로시저에서 사용되는 쿼리의 성능을 개선할 수 있는 부분을 식별할 수 있습니다.

2. PROFILING

MariaDB에서 SHOW PROFILE을 사용하여 쿼리 실행 시간과 자원 소비를 분석할 수 있습니다. 이를 통해 어떤 쿼리가 느리게 실행되는지 확인하고 최적화 작업을 진행할 수 있습니다.

SHOW PROFILE FOR QUERY 1;

3. PERCONA TOOLKIT

Percona Toolkit은 MariaDB 및 MySQL의 성능 분석 및 최적화를 위한 유용한 도구입니다. 이 도구를 사용하면 저장 프로시저의 실행을 모니터링하고 문제를 해결할 수 있습니다.

---

 

저장 프로시저는 복잡한 SQL 작업을 효율적으로 자동화하고, 성능을 향상시키는 중요한 도구입니다. 이 문서에서 설명한 다양한 기법들을 활용하여 MariaDB에서의 저장 프로시저 사용을 최적화하고, 보다 안전하고 성능이 뛰어난 시스템을 구축할 수 있습니다.