38.9. 윈 토토 절차

예 38-3. 갑PL/pgSQL윈 토토 절차

이 예시 윈 토토는 행이 삽입될 때마다 또는 테이블에서 업데이트되면 현재 사용자 이름과 시간은 행에 스탬프가 찍혀 있습니다. 그리고 직원의 이름이 다음과 같은지 확인합니다. 주어지며 급여는 양수 값입니다.

테이블 emp 생성(
    엠프 이름 텍스트,
    급여 정수,
    last_date 타임스탬프,
    last_user 텍스트
);

CREATE FUNCTION emp_stamp() $emp_stamp$로 윈 토토를 반환합니다.
    시작
        -- 사원명과 급여가 입력되었는지 확인하세요.
        NEW.empname이 NULL인 경우
            RAISE EXCEPTION 'empname은 null일 수 없습니다.';
        종료하면;
        NEW.salary가 NULL인 경우
            RAISE EXCEPTION '%는 null 급여를 가질 수 없습니다.', NEW.empname;
        종료하면;

        -- 그녀가 비용을 지불해야 하는데 누가 우리를 위해 일합니까?
        NEW.salary < 0이면
            RAISE EXCEPTION '%는 음수 급여를 가질 수 없습니다.', NEW.empname;
        종료하면;

        -- 언제 급여를 변경했는지 기억해 보세요.
        NEW.last_date := 현재_타임스탬프;
        NEW.last_user := 현재_사용자;
        새로운 반품;
    끝;
$emp_stamp$ 언어 plpgsql;

emp를 삽입하거나 업데이트하기 전에 emp_stamp 윈 토토를 생성하세요.
    각 행 실행 절차 emp_stamp();

예 38-4. 갑PL/pgSQL윈 토토 절차 감사

이 예시 윈 토토는 모든 삽입, 업데이트 또는 에서 행 삭제emp테이블은 에 기록(즉, 감사)됩니다.emp_audit테이블. 현재 시간과 사용자 이름 작업 유형과 함께 행에 스탬프가 찍혀 있습니다. 그것에 대해 수행했습니다.

테이블 emp 생성(
    empname 텍스트는 NULL이 아닙니다.
    급여 정수
);

테이블 만들기 emp_audit(
    작업 char(1) NOT NULL,
    스탬프 타임스탬프 NOT NULL,
    사용자 ID 텍스트가 NULL이 아닙니다.
    empname 텍스트는 NULL이 아닙니다.
    급여 정수
);

함수 생성 또는 교체 process_emp_audit()는 $emp_audit$로 윈 토토를 반환합니다.
    시작
        --
        -- emp에서 수행된 작업을 반영하기 위해 emp_audit에 행을 생성합니다.
        -- 특수 변수 TG_OP를 사용하여 작업을 수행합니다.
        --
        IF (TG_OP = '삭제') THEN
            INSERT INTO emp_audit SELECT 'D', now(), 사용자, OLD.*;
            오래된 것을 반환하십시오;
        ELSIF (TG_OP = 'UPDATE') 그런 다음
            INSERT INTO emp_audit SELECT 'U', now(), user, NEW.*;
            새로운 반품;
        ELSIF (TG_OP = 'INSERT') 그런 다음
            INSERT INTO emp_audit SELECT 'I', now(), user, NEW.*;
            새로운 반품;
        종료하면;
        NULL을 반환합니다. -- AFTER 윈 토토이므로 결과는 무시됩니다.
    끝;
$emp_audit$ LANGUAGE plpgsql;

윈 토토 생성 emp_audit
emp에서 삽입, 업데이트 또는 삭제 후
    각 행 실행 절차 process_emp_audit();

예 38-5. 갑PL/pgSQL유지 관리를 위한 윈 토토 절차 요약표

여기에 자세히 설명된 스키마는 부분적으로 다음을 기반으로 합니다.식료품점예제데이터웨어하우스 툴킷작성자: 랄프 킴볼.

--
-- 기본 테이블 - 시간 차원 및 판매 사실.
--
CREATE TABLE time_dimension(
    time_key 정수는 NULL이 아닙니다.
    day_of_week 정수는 NULL이 아닙니다.
    day_of_month 정수는 NULL이 아닙니다.
    월 정수는 NULL이 아닙니다.
    분기 정수는 NULL이 아닙니다.
    연도 정수 NULL이 아님
);
고유 인덱스 생성 time_dimension_key ON time_dimension(time_key);

CREATE TABLE sales_fact (
    time_key 정수는 NULL이 아닙니다.
    product_key 정수는 NULL이 아닙니다.
    store_key 정수는 NULL이 아닙니다.
    amount_sold 숫자(12,2) NOT NULL,
    units_sold 정수는 NULL이 아닙니다.
    amount_cost 숫자(12,2) NOT NULL
);
CREATE INDEX sales_fact_time ON sales_fact(time_key);

--
-- 요약 테이블 - 시간별 판매.
--
CREATE TABLE sales_summary_bytime(
    time_key 정수는 NULL이 아닙니다.
    amount_sold 숫자(15,2) NOT NULL,
    units_sold 숫자(12) NOT NULL,
    amount_cost 숫자(15,2) NULL이 아님
);
고유 인덱스 생성 sales_summary_bytime_key ON sales_summary_bytime(time_key);

--
-- UPDATE, INSERT, DELETE 시 요약된 열을 수정하는 함수 및 윈 토토입니다.
--
함수 생성 또는 교체 maint_sales_summary_bytime()은 $maint_sales_summary_bytime$으로 윈 토토를 반환합니다.
    선언
        delta_time_key 정수;
        delta_amount_sold 숫자(15,2);
        delta_units_sold 숫자(12);
        delta_amount_cost 숫자(15,2);
    시작

        -- 증가/감소 금액을 계산합니다.
        IF (TG_OP = '삭제') THEN

            delta_time_key = OLD.time_key;
            delta_amount_sold = -1 * OLD.amount_sold;
            delta_units_sold = -1 * OLD.units_sold;
            delta_amount_cost = -1 * OLD.amount_cost;

        ELSIF (TG_OP = 'UPDATE') 그런 다음

            -- time_key를 변경하는 업데이트를 금지합니다. -
            -- (아마도 DELETE + INSERT가 대부분의 방법이므로 그다지 번거롭지는 않을 것입니다.
            -- 변경사항이 적용됩니다.)
            IF ( OLD.time_key != NEW.time_key) THEN
                RAISE EXCEPTION 'time_key 업데이트: % - % 허용되지 않음', OLD.time_key, NEW.time_key;
            종료하면;

            delta_time_key = OLD.time_key;
            delta_amount_sold = NEW.amount_sold - OLD.amount_sold;
            delta_units_sold = NEW.units_sold - OLD.units_sold;
            delta_amount_cost = NEW.amount_cost - OLD.amount_cost;

        ELSIF (TG_OP = 'INSERT') 그런 다음

            delta_time_key = NEW.time_key;
            delta_amount_sold = NEW.amount_sold;
            delta_units_sold = NEW.units_sold;
            delta_amount_cost = NEW.amount_cost;

        종료하면;

        -- 새 값으로 요약 행을 삽입하거나 업데이트합니다.
        <<삽입_업데이트
        루프
            업데이트 sales_summary_bytime
                SET 판매_금액 = 판매_금액 + 델타_판매_금액,
                    단위_판매 = 단위_판매 + 델타_단위_판매,
                    amount_cost = amount_cost + delta_amount_cost
                여기서 time_key = delta_time_key;

            insert_update가 발견되면 종료하세요.

            시작
                sales_summary_bytime에 삽입(
                            시간_키,
                            금액_판매,
                            단위_판매,
                            금액_비용)
                    값(
                            delta_time_key,
                            delta_amount_sold,
                            delta_units_sold,
                            delta_amount_cost
                           );

                종료 insert_update;

            예외
                WHEN UNIQUE_VIOLATION THEN
                    -- 아무것도 하지 마세요
            끝;
        END LOOP insert_update;

        NULL을 반환합니다.

    끝;
$maint_sales_summary_bytime$ LANGUAGE plpgsql;

생성 윈 토토 maint_sales_summary_bytime
sales_fact에 대한 삽입, 업데이트 또는 삭제 후
    각 행 실행 절차 maint_sales_summary_bytime();

sales_fact VALUES(1,1,1,10,3,15)에 삽입;
sales_fact VALUES(1,2,1,20,5,35)에 삽입;
sales_fact VALUES(2,2,1,40,15,135)에 삽입;
sales_fact VALUES(2,3,1,10,1,13)에 삽입;
SELECT * FROM sales_summary_bytime;
product_key = 1인 sales_fact에서 삭제합니다.
SELECT * FROM sales_summary_bytime;
UPDATE sales_fact SET 유닛_판매 = 유닛_판매 * 2;
SELECT * FROM sales_summary_bytime;