프로시저의 개념:

• 데이터베이스에 저장되는 실행 가능한 프로그램 단위
• 여러 SQL문을 하나의 논리적 작업 단위로 묶어서 실행
• 재사용이 가능하며 매개변수를 통해 유연한 실행 가능

프로시저 작성 순서:

1. 프로시저 선언

CREATE OR REPLACE PROCEDURE procedure_name (
    parameter1 IN VARCHAR2,
    parameter2 OUT NUMBER
) AS

1. 변수 선언부 작성 (필요한 경우)

DECLARE
    v_variable1 NUMBER;
    v_variable2 VARCHAR2(100);

1. 실행부 작성

BEGIN
    -- SQL 문장들
    SELECT column1 INTO v_variable1
    FROM table_name
    WHERE condition;
    
    -- 비즈니스 로직
    IF v_variable1 > 0 THEN
        INSERT INTO table_name VALUES (v_variable1);
    END IF;
    
    -- 예외처리
    EXCEPTION
        WHEN NO_DATA_FOUND THEN
            -- 예외처리 로직
END;
/

예시로 context의 PROCESS_DAILY_SALES 프로시저는 이러한 구조를 따라 일별 매출을 정산하는 기능을 구현하고 있습니다.

실습예제: 카페 매출을 처리하고 매일 정산하는 프로시저를 구현

1. 테이블 설계

• ORDERS: 개별 주문을 저장하는 테이블
• DAILY_SALES: 일별 매출을 정리하는 테이블

-- 주문 테이블 (ORDERS)
CREATE TABLE ORDERS (
    ORDER_ID NUMBER PRIMARY KEY,       -- 주문 ID
    ORDER_DATE TIMESTAMP DEFAULT SYSTIMESTAMP,   -- 주문 날짜
    PRODUCT_NAME VARCHAR2(100),        -- 상품명
    QUANTITY NUMBER,                   -- 수량
    PRICE NUMBER,                       -- 단가
    TOTAL_AMOUNT NUMBER GENERATED ALWAYS AS (QUANTITY * PRICE) VIRTUAL -- 총 금액 (가상 컬럼)
);

-- 일별 매출 정산 테이블 (DAILY_SALES)
CREATE TABLE DAILY_SALES (
    SALES_DATE DATE PRIMARY KEY,    -- 정산 날짜
    TOTAL_ORDERS NUMBER,            -- 총 주문 수
    TOTAL_SALES NUMBER              -- 총 매출 금액
);

2. 정산 프로시저 구현

CREATE OR REPLACE
PROCEDURE PROC_DAILY_SALES AS 
    --변수선언구간
    V_TOTAL_ORDERS NUMBER:=0;
    V_TOTAL_SALES DAILY_SALES.TOTAL_SALES%type;
    V_TODAY DATE:=trunc(systimestamp);-- 시스템의 현재날짜를 
    V_CHECK number:=0;
BEGIN
    
    --처리할 내용을 작성하는 구간
    --상황따라 분기해야함.
    --오늘자 마감정보가 없으면 insert or 있으면 update
    select count(*) into v_check 
        from DAILY_SALES where sales_date=V_TODAY;
    -- 오늘 총주문수 계산->변수   
    select count(*) into V_TOTAL_ORDERS
        from ORDERS where trunc(ORDER_DATE) = V_TODAY;
    -- 오늘 총매출액 계산->변수
    select sum(TOTAL_AMOUNT) into V_TOTAL_SALES
            from ORDERS where trunc(ORDER_DATE) = V_TODAY;
    ----------------------------------------------------------------------------   
    BEGIN
        if V_CHECK=0 then--마감정보 없음
            insert into DAILY_SALES 
            values(V_TODAY , V_TOTAL_ORDERS, V_TOTAL_SALES);
            --SET SERVEROUTPUT ON;
            dbms_output.put_line('마감정보가 저장 되었습니다.');
           
        else--존재하는경우
            update DAILY_SALES
            set TOTAL_ORDERS=V_TOTAL_ORDERS
                ,TOTAL_SALES=V_TOTAL_SALES
            where SALES_DATE=V_TODAY;
            dbms_output.put_line('마감정보가 수정 되었습니다.');
        end if;
        commit;-->사용할지 말지 트리거 사용하면 commit이 있으면 않됨.
    
    EXCEPTION
        WHEN OTHERS THEN
            dbms_output.put_line('정상적으로 처리되지 않았습니다..');
            rollback;
    end;
    
    
END PROC_DAILY_SALES;