[SQL&Database] Stored Procedure, 변수, 날짜

Comely·2025년 6월 9일
sql

SQL

목록 보기
6/6

Stored Procedure - 코드 재사용의 핵심

Stored Procedure란?

자주 사용하는 SQL 코드 덩어리를 저장해두고 필요할 때마다 호출하여 사용할 수 있는 기능입니다. 프로그래밍의 함수와 비슷한 개념입니다.

Stored Procedure 생성 방법

DBeaver에서 생성:
1. 데이터베이스의 Procedures 메뉴에서 우클릭
2. 새로운 Procedure 생성 선택
3. source 메뉴에서 BEGIN/END 사이에 코드 작성

SQL로 직접 생성:

DROP PROCEDURE IF EXISTS 데이터베이스명.get_all;

DELIMITER $$
CREATE PROCEDURE 데이터베이스명.get_all()
BEGIN
  SELECT * FROM product WHERE 가격 > 5000;
END 
$$
DELIMITER ;

주요 구성 요소:

  • DROP PROCEDURE IF EXISTS: 기존 프로시저가 있으면 삭제
  • DELIMITER $$: 구분자를 $$로 변경 (내부 ; 때문)
  • BEGIN/END: 실행할 코드 블록
  • DELIMITER ;: 구분자를 다시 ;로 복원

Stored Procedure 실행

CALL procedure이름();
-- 또는
CALL 데이터베이스명.procedure이름();

성능 및 생산성 향상

성능 측면:

  • 캐싱된 실행 계획 재사용으로 약간의 속도 향상
  • 하지만 실제 실행 속도는 직접 SQL과 큰 차이 없음

생산성 측면:
1. 코드 재사용: 반복되는 긴 코드를 간단히 호출
2. 팀 협업: 개발자가 아닌 사용자도 복잡한 쿼리 활용 가능
3. 유지보수: 중앙 집중식 코드 관리

변수 (Variables) - 데이터 임시 저장

User Variable (@변수)

세션이 종료될 때까지 유지되는 전역 변수입니다.

-- 변수 생성 및 할당
SET @age = 20;
SELECT @age := 20;  -- 동일한 결과

-- 변수 사용
SELECT @age;

-- 계산과 함께 사용
SET @price = 6000;
SELECT * FROM product WHERE 가격 = @price;

-- 서브쿼리 결과 저장
SET @count = (SELECT COUNT(*) FROM product WHERE 가격 = 5000);

-- 변수 값 증가
SET @age = @age + 1;

Local Variable (DECLARE 변수)

Procedure 내부에서만 사용 가능한 지역 변수입니다.

CREATE PROCEDURE var_test() 
BEGIN 
  DECLARE 변수1 INT;
  DECLARE 변수2 VARCHAR(100);
  DECLARE 변수3 INT DEFAULT 123;  -- 기본값 설정
  
  SET 변수1 = 10;
  SET 변수1 = 변수1 + 1;
  SELECT 변수1;  -- 결과: 11
END

User Variable vs Local Variable 비교

특징User Variable (@변수)Local Variable (DECLARE)
사용 범위전역 (모든 SQL에서 사용)Procedure 내부만
생존 기간세션 종료까지Procedure 실행 종료까지
선언 위치어디서나BEGIN 바로 다음
안전성중복 선언 위험안전함

Parameter - 유연한 Procedure 만들기

입력 Parameter (IN)

Procedure 실행 시 외부에서 값을 전달받을 수 있습니다.

-- 단일 파라미터
CREATE PROCEDURE get_products(price_limit INT)
BEGIN
    SELECT * FROM product WHERE 가격 > price_limit;
END;

-- 사용법
CALL get_products(6000);  -- 6000원 이상 상품 조회
CALL get_products(8000);  -- 8000원 이상 상품 조회

-- 다중 파라미터
CREATE PROCEDURE search_products(price_min INT, name_part VARCHAR(100))
BEGIN
    SELECT * FROM product 
    WHERE 가격 > price_min OR 상품명 LIKE CONCAT('%', name_part, '%');
END;

-- 사용법
CALL search_products(5000, '김치');

출력 Parameter (OUT)

Procedure 내부의 값을 외부로 전달할 수 있습니다.

CREATE PROCEDURE calculate_total(OUT total_price INT)
BEGIN
    SET total_price = (SELECT SUM(가격) FROM product);
END;

-- 사용법
CALL calculate_total(@result);
SELECT @result;  -- 총 가격 출력

날짜/시간 데이터 처리

날짜 데이터 타입

데이터 타입형식범위용도
DATEYYYY-MM-DD1000-9999년날짜만 저장
DATETIMEYYYY-MM-DD HH:MM:SS1000-9999년날짜와 시간 저장
TIMESTAMPYYYY-MM-DD HH:MM:SS1970-2038년현재 시간 기록용

정밀도 설정:

DATETIME(6)  -- 마이크로초(6자리)까지 저장

날짜 조회 및 필터링

기본 날짜 조회:

-- 특정 날짜/시간 이후
SELECT * FROM blog WHERE 발행일 > '2022-03-10 08:24:25';

-- 정확한 날짜/시간
SELECT * FROM blog WHERE 발행일 = '2022-03-10 08:24:25';

특정 날짜의 모든 데이터 조회:

-- 2022년 3월 10일 하루 전체
SELECT * FROM blog 
WHERE 발행일 >= '2022-03-10 00:00:00' 
  AND 발행일 < '2022-03-11 00:00:00';

-- 현재 시간까지의 범위
SELECT * FROM blog 
WHERE 발행일 > '2022-03-10 00:00:00' 
  AND 발행일 <= NOW();

날짜 함수 활용

현재 시간 조회:

SELECT NOW();           -- 현재 날짜와 시간
SELECT NOW(6);          -- 마이크로초까지
SELECT CURDATE();       -- 현재 날짜만

날짜 형식 변경:

SELECT DATE_FORMAT(NOW(), '%Y년 %m월 %d일');
SELECT DATE_FORMAT(NOW(), '%H:%i:%s');

주요 형식 지정자:

  • %Y: 4자리 년도, %y: 2자리 년도
  • %m: 월(01-12), %d: 일(01-31)
  • %H: 시간(00-23), %i: 분(00-59), %s: 초(00-59)

날짜 연산:

-- 1년 추가
SELECT DATE_ADD(NOW(), INTERVAL 1 YEAR);
SELECT NOW() + INTERVAL 1 YEAR;

-- 다양한 간격
SELECT DATE_ADD('2022-01-01', INTERVAL 1 MONTH);
SELECT DATE_ADD('2022-01-01', INTERVAL 7 DAY);

날짜 부분 추출:

SELECT YEAR(NOW());     -- 년도
SELECT MONTH(NOW());    -- 월
SELECT DAY(NOW());      -- 일
SELECT HOUR(NOW());     -- 시간

날짜 데이터 삽입

INSERT INTO blog (제목, 발행일) 
VALUES ('새 글', '2024-01-15 14:30:00');

-- 현재 시간으로 삽입
INSERT INTO blog (제목, 발행일) 
VALUES ('새 글', NOW());

실전 활용 예제

월간 활성 사용자(MAU) 계산

-- 2022년 11월 MAU
SELECT COUNT(*) FROM login_record 
WHERE last_login >= '2022-11-01 00:00:00' 
  AND last_login < '2022-12-01 00:00:00';

조건부 날짜 조회

-- 9월의 짝수일 데이터 조회
SELECT * FROM login_record 
WHERE MONTH(last_login) = 9 
  AND DAY(last_login) % 2 = 0;

복합 Procedure 예제

CREATE PROCEDURE analyze_sales(
    IN start_date DATE,
    IN end_date DATE,
    OUT total_sales INT,
    OUT avg_daily_sales DECIMAL(10,2)
)
BEGIN
    DECLARE day_count INT;
    
    -- 총 매출 계산
    SELECT SUM(가격) INTO total_sales
    FROM sales s
    JOIN product p ON s.상품id = p.id
    WHERE s.구매날짜 BETWEEN start_date AND end_date;
    
    -- 일수 계산
    SET day_count = DATEDIFF(end_date, start_date) + 1;
    
    -- 일평균 매출 계산
    SET avg_daily_sales = total_sales / day_count;
END;

-- 사용법
CALL analyze_sales('2022-01-01', '2022-01-31', @total, @avg);
SELECT @total as 총매출, @avg as 일평균매출;

시간대별 분석 Procedure

CREATE PROCEDURE hourly_analysis(analysis_date DATE)
BEGIN
    SELECT 
        HOUR(구매시간) as 시간대,
        COUNT(*) as 구매건수,
        SUM(가격) as 시간대매출
    FROM sales s
    JOIN product p ON s.상품id = p.id
    WHERE DATE(구매시간) = analysis_date
    GROUP BY HOUR(구매시간)
    ORDER BY 시간대;
END;

CALL hourly_analysis('2022-03-15');

이러한 Stored Procedure, 변수, 날짜 처리 기능들을 조합하면 복잡한 비즈니스 로직도 효율적으로 구현할 수 있으며, 코드의 재사용성과 유지보수성을 크게 향상시킬 수 있습니다.

profile
Comely
App, Web Developer
이전 포스트

[SQL&Database] JOIN과 데이터 조작

0개의 댓글