이전 과제들 쉽게 끝냈지? 그럼 이제 좀 더 어려운 걸로 가보자. 이제 PL-SQL 지식을 다져보고 프로시저랑 트리거도 직접 만들어볼 거야. 준비됐지?
프로시저 만들기
아래는 네 마켓플레이스 데이터베이스에 추가하는 걸 추천하는 프로시저들이야. 각 프로시저마다 어떤 업무랑 비즈니스 프로세스를 해결하는지, 왜 구현해야 하는지 설명도 같이 있어. 이 프로시저들은 핵심 운영 자동화, 사용자 편의성 향상, 쇼케이스, 물류, 지원, 마케팅, 분석 업무 최적화를 다루고 있어.
1. 주문 생성과 동시에 상품 자동 예약
주문 생성은 어떤 마켓플레이스든 핵심 작업이지. 프로시저는 단순히 주문과 주문 아이템을 만드는 것뿐 아니라, 창고에서 상품을 예약하고, 수량 차감, 상태 기록, 결제 시작, 후속 프로세스(알림, 물류 등)까지 자동으로 처리해야 해. 이 시나리오를 자동화하면 수동 입력 실수 줄이고, over-selling 방지, 실시간 재고 일치도 보장할 수 있어.
2. 상품 재고 자동 보충
out-of-stock이나 판매 손실을 막으려면, 재고가 임계치 이하로 떨어졌을 때 제때 창고를 보충하는 게 중요해. 이 프로시저는 모든 상품의 재고를 체크해서 최소치와 비교하고, 자동으로 구매 요청이나 내부 재공급을 생성해. 자동화 덕분에 반응 속도 빨라지고, 운영팀 수동 업무도 줄어들지.
3. 주문 상태 대량 업데이트 및 고객 알림
카탈로그에서는 종종 주문 상태를 대량으로 바꿔야 할 때가 있어(예: “paid” → “shipped” 또는 “shipped” → “completed”). 프로시저는 해당 주문들의 상태를 한 번에 바꿔주고, 변경 이력도 남기고(감사용), 필요하면 고객 알림도 보낼 수 있어. 백오피스 업무 자동화되고, 수동 실수도 최소화할 수 있지.
4. 상품에 대량 할인/프로모코드 적용
상품 프로모션이나 이벤트를 할 때, 카테고리, 브랜드, 특정 상품군 전체에 프로모코드나 할인을 대량 적용해야 할 때가 많아. 프로시저가 자동으로 할인 적용, 제한(날짜, 한도) 체크, 사용 횟수 업데이트, 중복 방지까지 해줘.
5. 사용자에게 자동 환불 처리
환불 및 환불 처리는 고객 신뢰에 엄청 중요한 프로세스야. 프로시저는 주문 상태, 환불 유효성 체크, 결제 환불 시작, 상태 업데이트, 트랜잭션 로깅, 사용자 알림까지 한 번에 처리해야 해. 모든 조건을 한 트랜잭션에서 처리하면 실수나 악용 위험도 줄어들지.
6. 상품/판매자 평균 평점 재계산
새 리뷰가 달리거나 수정, 삭제될 때마다 상품이나 사용자의 평균 평점이 항상 최신이어야 해. 프로시저가 “avg_rating”/“review_count” 필드를 바로 재계산해서 프론트 쿼리 속도도 빠르고, 분석 데이터 일관성도 보장할 수 있어.
7. 재고 0인 상품 자동 비활성화
마켓플레이스 쇼케이스가 제대로 동작하고, 사용자에게 안 좋은 경험을 주지 않으려면, 재고가 0인 상품은 자동으로 숨기거나(비활성화) 해야 해. 프로시저가 주기적으로 창고와 상품 상태를 체크해서 “inactive”로 바꿔주고, 변경 로그도 남겨서 카탈로그가 항상 최신 상태가 되도록 도와줘.
8. 주문에 자동으로 배달기사 할당 및 배송 상태 업데이트
물류에서는 주문에 빠르고 투명하게 배달기사를 할당하고, 배송 상태를 바꾸고, 모든 작업을 추적하는 게 중요해. 프로시저가 이 과정을 자동화해서 매니저 수동 업무를 없애줘.
9. 사용자 대량 알림 발송 (이벤트 트리거, 리마인더 등)
세일, 환불, 상태 변경, 마케팅 이벤트 등 알림은 조건에 따라(활성 사용자, 오래 구매 안 한 사람, 장바구니 이탈 등) 대량으로 보내야 해. 프로시저가 지정된 세그먼트에 push/email 알림을 자동으로 발송할 수 있어.
10. 오래된 데이터 아카이빙 (예: 완료된 주문, 비활성 상품 등)
데이터베이스 성능 유지와 “핫” 테이블 용량 감소를 위해, 오래된 레코드(옛날 주문, 아카이브 상품, 오래된 지원 티켓 등)는 주기적으로 옮기거나 “아카이브”로 표시해야 해. 프로시저가 데이터 삭제/이동 작업을 쉽게 해주고, 관리자 수동 업무도 줄여줘.
트리거 만들기
1. 주문 상태 변경 로그 기록
주문 상태 변경 전체 이력을 남기는 건 감사, 지원, 분석, 사용자 알림 자동화에 필수야. 트리거가 "order".order_status_log에 주문 상태가 바뀔 때마다 자동으로 기록을 남겨서, 개발자가 앱에서 직접 이력 관리할 필요가 없어.
2. 상품 옵션 가격 변경 이력 자동 기록
SKU 가격 변경 이력은 분석, “이전 가격” 표시, 이벤트 추적, 할인 알림 자동화에 필요해. 트리거가 product.variant 테이블의 price 필드가 바뀔 때마다 product.price_history에 기록해. 이렇게 하면 가격 변동 내역을 빠짐없이 저장할 수 있어.
3. 창고 상품 변경 시 재고 동기화
창고 재고가 바뀔 때마다(재고조사, 입고, 출고 등) last_updated 필드를 자동으로 갱신해서 분석과 데이터 최신성 관리가 쉬워져. 또, 이 트리거가 최소치 도달 시 자동 주문도 시작할 수 있어.
4. 재고 0인 상품 옵션 자동 비활성화
고객이 없는 상품을 사는 상황을 막으려면, 모든 창고에서 옵션 재고가 0이 되면 is_active 필드를 FALSE로 자동 변경해야 해. 이렇게 하면 부정적 리뷰나 주문 취소도 줄일 수 있지.
5. 관리자 로그인 시도 로그 기록(보안)
관리자 로그인 관리는 정보보안의 핵심이야. 모든 로그인 시도(성공/실패)는 admin.login_attempt에 BEFORE INSERT 트리거로 자동 기록돼. 이걸로 공격, 해킹, 수상한 활동을 빠르게 잡아낼 수 있어.
6. 상품 주요 변경사항 로그 및 이력 자동 기록
상품의 주요 수정(상태, 설명, 이름 변경 등)은 직원 행동 감사, 오류 복구, 악용 방지에 꼭 기록해야 해. 트리거가 상태 변경 시 product.status_history에 기록을 남기고, 다른 주요 필드도 확장 가능해.
7. 프로모코드 사용 횟수 자동 업데이트
프로모코드 사용 횟수 정확한 집계는 이벤트 제한, 악용 방지에 중요해. marketing.promo_usage에 insert될 때마다 marketing.promo_code의 used_count를 증가시켜서 데이터 불일치도 막아줘.
8. 사용자 지갑 잔액 트랜잭션 시 자동 업데이트
보너스/캐시백 지갑 잔액이 정확히 표시되려면, 트랜잭션마다 payment.wallet의 최종 잔액이 자동으로 갱신돼야 해. 새 트랜잭션 insert 트리거로 앱 오류로 인한 데이터 손실/왜곡도 줄일 수 있어.
9. "기본" 주소, 이메일, 전화번호 자동 지정
사용자에게 기본 email/전화/주소가 없으면(접근 복구, 커뮤니케이션에 필수), 트리거가 첫 레코드에 is_primary=TRUE를 자동 지정하고, 한 사용자 내에서 유일성도 보장해.
10. 리뷰 추가 시 상품 평균 평점 자동 계산
상품 카드나 검색에서 “평균 평점”을 빠르게 보여주려면, 새 리뷰 추가 때마다 값을 업데이트하는 게 쿼리로 계속 집계하는 것보다 효율적이야. 트리거가 product.product의 avg_rating와/또는 review_count 필드 캐시를 유지해줘.
11. 컨텐츠 게시일 자동 지정
기사, 페이지 등 게시물의 상태가 “published”로 바뀔 때 published_at 필드를 정확히 채워야 해. 이러면 CMS 일관성도 보장되고, 사용자/관리자가 정확한 게시일을 볼 수 있고, 프론트에서 추가 수동 업데이트도 필요 없어.
12. 지원 이벤트 전체 로그 기록(티켓 상태 변경)
지원 요청과 상태 변경 전체 이력을 남기면, 지원팀 업무 평가, 분석, 사용자 투명성 보장에 좋아. 트리거가 티켓 상태가 바뀔 때마다 support.ticket_status_log에 자동으로 기록해.
참고
이 트리거들을 추가하면 마켓플레이스의 핵심 비즈니스 프로세스 신뢰성, 투명성, 자동화가 크게 올라가고, 앱 코드 부담도 줄고, 데이터 일관성도 데이터베이스 레벨에서 보장할 수 있어. 이건 대형 e-commerce용 관계형 시스템 설계의 베스트 프랙티스야.
GO TO FULL VERSION