[SQL] 쿼리테스트 4 - MySQL 저장 프로시저로 userType 매핑 후 사용자 삽입하기

Hyunjun Kim·2026년 2월 9일

SQL

목록 보기
94/98

MySQL 저장 프로시저로 참조 테이블의 id 매핑 후 사용자 삽입하기 — DECLARE, SELECT INTO, SIGNAL, AUTO_INCREMENT 해설

목표: userTypes 참조 테이블의 userType 문자열을 입력 파라미터로 받아 해당 id를 찾아 users 테이블에 email과 함께 삽입하는 저장 프로시저를 구현하고, 내부 동작 원리(DECLARE, SELECT ... INTO, SIGNAL, AUTO_INCREMENT 등)를 완전히 이해한다.

요약: 프로시저는 프로시저 입력 파라미터(IN userType VARCHAR(50))를 받아, 저장 프로시저 내부에서 쓰는 지역변수(DECLARE v_type_id INT;)에 userTypes.id를 할당한다. 만약 userType이 존재하지 않으면 SIGNAL로 사용자 정의 예외(SQLSTATE '45000')를 던지거나(옵션), userTypes에 새로 추가하거나(옵션 A), 기본 userType으로 대체(옵션 B)할 수 있다. INSERT 문에서 id 컬럼을 명시적으로 넣지 않아도 AUTO_INCREMENT가 자동으로 동작하여 users.id가 증가된다.

문제

문제
다음 두 테이블이 있다.

-- reference table
CREATE TABLE user_types (
  id INT PRIMARY KEY AUTO_INCREMENT,
  user_type VARCHAR(50) NOT NULL
);

-- target table
CREATE TABLE users (
  id INT PRIMARY KEY AUTO_INCREMENT,
  email VARCHAR(50) NOT NULL,
  user_type_id INT NOT NULL,
  FOREIGN KEY (user_type_id) REFERENCES user_types (id)
);

저장 프로시저 insert_user를 작성하라. 요구사항은 다음과 같다.

  • 프로시저는 입력 파라미터 user_type(문자열)과 email(문자열)을 받는다.

  • user_types에서 user_type과 일치하는 행의 id를 찾아 users.user_type_id로 넣는다.

  • users.id는 AUTO_INCREMENT로 자동 생성된다.

  • 만약 user_type이 존재하지 않으면(옵션 중 하나 선택) 다음 중 하나를 수행하라:

    1. 명시적 예외를 발생시킨다.
    2. user_types에 해당 user_type을 새로 삽입한 뒤 그 id를 사용한다.
    3. 기본 user_type(예: 'guest')의 id를 사용한다.

정답(모범 구현) — 기본 버전 (존재하지 않으면 예외)

DELIMITER //
CREATE PROCEDURE insert_user(
  IN p_user_type VARCHAR(50),
  IN p_email VARCHAR(50)
)
BEGIN
  DECLARE v_type_id INT;

  -- user_types에서 파라미터로 들어온 user_type에 해당하는 id를 가져온다.
  SELECT ut.id
  INTO v_type_id
  FROM user_types ut
  WHERE ut.user_type = p_user_type
  LIMIT 1;

  -- 찾지 못하면 명시적 에러 발생
  IF v_type_id IS NULL THEN
    SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'user_type not found';
  END IF;

  -- users에 삽입 (id는 AUTO_INCREMENT)
  INSERT INTO users (email, user_type_id)
  VALUES (p_email, v_type_id);
END //
DELIMITER ;

설명: 위 코드는 프로시저의 입력 파라미터와 테이블 컬럼을 명확히 구분하기 위해 파라미터에 p_ 접두사(p_user_type)를 사용했다. LIMIT 1을 사용해 SELECT ... INTO의 결과가 다중 행일 때 발생할 수 있는 오류를 방지한다. SIGNAL SQLSTATE '45000'은 사용자 정의 오류를 발생시키는 표준 코드로, SIGNAL이 실행되면 프로시저는 즉시 에러를 던지고(롤백 여부는 트랜잭션 설정에 따름), 호출자에게 예외가 전달된다.

해설

1) 프로시저와 파라미터

  • CREATE PROCEDURE insert_user(IN p_user_type VARCHAR(50), IN p_email VARCHAR(50))는 프로시저의 입력 파라미터를 정의한다.
  • IN은 외부에서 값을 전달받는 입력임을 나타낸다. 파라미터는 프로시저 호출 시 CALL insert_user('ADMIN', 'a@b.com')처럼 전달된다.
  • 실무 관례로 파라미터에 p_ 또는 in_ 접두사를 붙여 테이블 컬럼과 구분한다.

2) DECLARE — 저장 프로시저 내부에서 쓰는 지역변수

  • DECLARE v_type_id INT;는 저장 프로시저 내부에서 쓰는 지역변수를 선언한다.
  • 지역변수는 프로시저 블록 안에서만 사용되며 초기값은 NULL이다. 변수명에 v_ 접두사(변수임을 뜻함)를 붙이는 관례도 자주 사용된다.
  • v_type_id는 user_types.id를 임시로 담아두는 그릇이다.

3) SELECT ... INTO — 조회 결과를 변수에 담기

  • SELECT ut.id INTO v_type_id FROM user_types ut WHERE ut.user_type = p_user_type LIMIT 1;는 다음을 의미한다.

    • 왼쪽 ut.user_type은 테이블 컬럼이다.
    • 오른쪽 p_user_type은 프로시저 파라미터이다.
    • 실행 시 WHERE ut.user_type = 'ADMIN'과 같이 평가되어 일치하는 행을 찾는다.
    • 결과의 id 값을 v_type_id 변수에 저장한다.

  • SELECT ... INTO는 0행일 경우 변수는 NULL로 남고, 2행 이상이면 오류가 발생하므로 LIMIT 1을 두어 안전성을 확보한다.

4) userType 파라미터와 테이블 컬럼이 동일한 이름일 때

  • 만약 파라미터 이름을 user_type로 하고 테이블 컬럼도 user_type이라면 WHERE ut.user_type = user_type처럼 모호해진다.
  • 실제로 SQL 엔진은 왼쪽을 컬럼, 오른쪽을 파라미터(스코프)로 구분한다. 그러나 혼동을 방지하기 위해 파라미터에 p_ 접두사를 붙이는 것이 권장된다.

5) LIMIT 1은 왜 쓰는가

  • SELECT ... INTO는 반드시 0개 또는 1개의 행만 반환해야 정상이다. 다중 행이면 오류(Result consisted of more than one row)가 발생한다.
  • LIMIT 1은 유니크 제약이 깔끔하게 보장되지 않는 상황에서 안전장치로 사용된다. 결과가 여러 행일 경우 첫 행만 취한다.

6) v_type_id가 NULL이면 어떻게 처리하는가

  • v_type_id는 기본적으로 NULL이다. user_type이 존재하지 않으면 SELECT ... INTO 후에도 v_type_id는 NULL이다.
  • 이후 IF v_type_id IS NULL THEN SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'user_type not found'; END IF;로 예외를 던질 수 있다.
  • SQLSTATE '45000'은 사용자 정의 오류를 나타내는 표준 코드(‘45000’은 사용자 정의 에러에 흔히 사용됨)이다.
  • SIGNAL이 실행되면 프로시저는 즉시 에러를 던지고(롤백 여부는 트랜잭션 설정에 따름), 호출자에게 예외가 전달된다.

7) INSERT와 AUTO_INCREMENT

  • users 테이블의 DDL에 id INT PRIMARY KEY AUTO_INCREMENT가 명시되어 있으면, INSERT문에서 id 컬럼을 명시적으로 넣지 않아도 MySQL이 자동으로 다음 번호를 채워준다.
  • 즉 INSERT INTO users (email, user_type_id) VALUES (p_email, v_type_id); 하면 id는 자동 증가된다.
  • 방금 삽입된 AUTO_INCREMENT 값을 가져오려면 LAST_INSERT_ID()를 사용한다. 같은 세션에서 호출하면 삽입 직후의 id를 얻을 수 있다.

8) 프로시저가 실제로 하는 일(실행 순서 — 엔진 관점)

  1. 호출: CALL insert_user('ADMIN', 'a@b.com'); → 프로시저 파라미터가 세팅된다.
  2. 지역변수 선언: v_type_id 선언되어 NULL 상태이다.
  3. 조회: SELECT ut.id INTO v_type_id ... → user_types에서 user_type = 'ADMIN' 행을 찾고 v_type_id에 값을 저장한다.
  4. 검사: IF v_type_id IS NULL → 값이 없으면 예외 또는 옵션 처리.
  5. 삽입: INSERT INTO users (email, user_type_id) VALUES (p_email, v_type_id); → users.id는 AUTO_INCREMENT로 자동 채워진다.
  6. (선택) SELECT LAST_INSERT_ID()로 새로 생성된 id를 얻어 반환 또는 로깅한다.

9) 예시 호출

CALL insert_user('ADMIN', 'alice@example.com');
  • 성공 시: users에 새 행이 들어가고 id는 자동으로 증가한다.
  • 실패 시(존재하지 않는 user_type): SIGNAL로 에러가 발생한다.

변형 A) userType이 없으면 자동으로 user_types에 새로 추가하고 삽입

DELIMITER //
CREATE PROCEDURE insert_user_auto(
  IN p_user_type VARCHAR(50),
  IN p_email VARCHAR(50)
)
BEGIN
  DECLARE v_type_id INT;

  SELECT id INTO v_type_id
  FROM user_types
  WHERE user_type = p_user_type
  LIMIT 1;

  IF v_type_id IS NULL THEN
    INSERT INTO user_types (user_type) VALUES (p_user_type);
    SET v_type_id = LAST_INSERT_ID();
  END IF;

  INSERT INTO users (email, user_type_id) VALUES (p_email, v_type_id);
END //
DELIMITER ;
  • 설명: user_type이 없으면 참조 테이블에 추가한 뒤 LAST_INSERT_ID()로 새 id를 취득해 사용한다.

변형 B) 에러 대신 기본 userType(예: 'guest')로 삽입

DELIMITER //
CREATE PROCEDURE insert_user_default(
  IN p_user_type VARCHAR(50),
  IN p_email VARCHAR(50)
)
BEGIN
  DECLARE v_type_id INT;

  SELECT id INTO v_type_id
  FROM user_types
  WHERE user_type = p_user_type
  LIMIT 1;

  IF v_type_id IS NULL THEN
    -- 기본 user_type id를 미리 알고 있다고 가정(예: guest id = 3)
    SET v_type_id = 3;
  END IF;

  INSERT INTO users (email, user_type_id) VALUES (p_email, v_type_id);
END //
DELIMITER ;
  • 설명: user_type이 존재하지 않으면 기본 user_type_id를 사용한다.

SELECT ... INTO vs 일반 SELECT — 비교로 이해하기

  • 일반 SELECT id FROM user_types WHERE user_type='ADMIN';는 결과셋을 반환한다.
  • 프로시저 내부의 SELECT id INTO v_type_id FROM user_types ...;는 출력 대신 변수에 대입한다.
  • 프로시저 내부에서 변수에 값을 담아 이후 로직에서 재사용해야 할 때 SELECT ... INTO 패턴을 사용한다.

디버깅·주요 실무 팁

  1. 파라미터명과 컬럼명을 동일하게 쓰지 말 것: WHERE ut.user_type = user_type처럼 모호하면 가독성 저하. p_user_type 사용을 권장한다.
  2. LIMIT 1을 사용하라: 데이터 품질 이슈로 중복 행이 존재할 수 있으므로 SELECT ... INTO에 LIMIT 1을 붙여 다중행 오류를 방지한다.
  3. SIGNAL 사용 시 트랜잭션 주의: SIGNAL이 발생하면 호출자가 예외를 받아 트랜잭션을 롤백할지 여부를 결정할 수 있다. 애플리케이션과의 트랜잭션 경계를 설계해라.
  4. AUTO_INCREMENT와 LAST_INSERT_ID(): 동시성 상황에서 같은 세션에서 LAST_INSERT_ID()를 사용하면 안전하다.
  5. 권한: 프로시저 생성/실행 권한, SIGNAL 사용 권한 등을 사전에 확인하라.
  6. 로깅: 실패 시 왜 실패했는지 로그를 남기면 디버깅에 도움이 된다.

요약 정리

  • DECLARE v_type_id INT;는 저장 프로시저 내부에서 쓰는 지역변수 선언이다.
  • SELECT ut.id INTO v_type_id ... WHERE ut.user_type = p_user_type LIMIT 1;에서 왼쪽 ut.user_type은 테이블 컬럼이고 오른쪽 p_user_type은 프로시저의 입력 파라미터이다.
  • v_type_id가 NULL이면 user_type이 없는 것이므로 SIGNAL SQLSTATE '45000'로 사용자 정의 예외를 던질 수 있다. SIGNAL이 실행되면 프로시저는 즉시 에러를 던지고(롤백 여부는 트랜잭션 설정에 따름), 호출자에게 예외가 전달된다.
  • INSERT문에서 id 컬럼을 명시적으로 넣지 않아도 MySQL이 자동으로 다음 번호를 채워준다. 즉 INSERT INTO users (email, user_type_id) VALUES (...); 하면 id는 자동 증가된다.
profile
Hyunjun Kim
Data Analytics Engineer 가 되
이전 포스트

[SQL] 쿼리테스트 3

다음 포스트

[SQL] 쿼리테스트5 - LIMIT + OFFSET 기반 페이징 방식 알고 가기

0개의 댓글