39.9. 와이즈 토토 절차

예 39-3. 에이pl/pgsql와이즈 토토 프로 시저

이 예제 와이즈 토토는 행이 삽입 될 때마다 또는 테이블에서 업데이트 된 현재 사용자 이름과 시간은 줄에 찍혔습니다. 그리고 직원의 이름이 있는지 확인합니다 주어지고 급여는 긍정적 인 가치입니다.

Table Emp 만들기 (
    Empname 텍스트,
    급여 정수,
    last_date timestamp,
    last_user 텍스트
);

function emp_stamp ()를 $ emp_stamp $로 와이즈 토토로 반환합니다
    시작하다
        - empname과 월급이 주어 졌는지 확인하십시오
        new.empname이 null이라면 그렇다면
            'empname은 null이 될 수 없다'는 예외를 높이십시오.
        끝 IF;
        New.salary가 null이라면
            '%는'%가 널 급여를 가질 수 없다 ', new.empname;
        끝 IF;

        - 그녀가 돈을 지불해야 할 때 누가 우리를 위해 일합니까?
        새로운.salary <0이라면
            '%는'%가 부정적인 급여를 가질 수 없습니다 ', new.empname;
        끝 IF;

        - 누가 급여를 바꾸 었는지 기억하십시오
        new.last_date : = current_timestamp;
        new.last_user : = current_user;
        새로운 반환;
    끝;
$ emp_stamp $ language plpgsql;

EMP에 삽입하거나 업데이트하기 전에 Trigger Emp_stamp를 만듭니다
    각 행에 대해 Execute Procedure Emp_stamp ();

예 39-4. 에이pl/pgsql와이즈 토토 절차 감사

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

Table Emp 만들기 (
    embname 텍스트가 null,
    급여 정수
);

테이블 작성 emp_audit (
    char (1) null이 아닌 작전,
    스탬프 타임 스탬프가 널 있지 않음,
    userID 텍스트는 null이 아닙니다.
    embname 텍스트가 null,
    급여 정수
);

함수 process_emp_audit ()를 $ emp_audit $로 반환합니다
    시작하다
        -
        - EMP에서 수행 된 작업을 반영하기 위해 EMP_AUDIT에서 행을 만듭니다.
        - 특수 변수 TG_OP를 사용하여 작업을 수행하십시오.
        -
        if (tg_op = 'delete') 그러면
            emp_audit에 삽입 'd', now (), user, old.*;
            옛날 복귀;
        elsif (tg_op = 'update')
            emp_audit에 삽입 'u', now (), 사용자, 새로운.*;
            새로운 반환;
        elsif (tg_op = 'insert')
            emp_audit에 삽입 'i', now (), user, new.*;
            새로운 반환;
        끝 IF;
        널 리턴; - 결과는 방아쇠 이후에 무시됩니다.
    끝;
$ emp_audit $ 언어 plpgsql;

와이즈 토토 emp_audit을 만듭니다
EMP에 삽입 또는 업데이트 또는 삭제 후
    각 행에 대해 프로 시저 Execute process_emp_audit ();

예 39-5. 에이pl/pgsql와이즈 토토 절차보기 감사

이 예제는보기에서 와이즈 토토를 사용하여 그것을 만들었습니다. 업데이트 가능하고 모든 인서트, 업데이트 또는 삭제 보기에있는 행은에 기록됩니다 (즉, 감사)emp_audit테이블. 현재 시간과 사용자 이름 수행 된 작업 유형과 함께 기록됩니다. 그리고보기는 각 행의 마지막 수정 시간을 표시합니다.

테이블 emp 생성 (
    empname 텍스트 기본 키,
    급여 정수
);

테이블 작성 emp_audit (
    char (1) null이 아닌 작전,
    userID 텍스트는 null이 아닙니다.
    embname 텍스트가 null,
    급여 정수,
    스탬프 타임 스탬프가 널 있지 않습니다
);

View emp_view as를 만듭니다
    e.empname 선택,
           E.Salary,
           last_updated로 max (ea.stamp)
      emp e
      eam.empname = e.empname에서 emp_audit ea에 가입합니다
     1, 2에 의해 그룹;

기능을 만들거나 바꾸십시오. update_emp_view () 와이즈 토토를 $$로 반환합니다
    시작하다
        -
        - EMP에서 필요한 작업을 수행하고 EMP_AUDIT에서 행을 만듭니다.
        - EMP에 대한 변화를 반영합니다.
        -
        if (tg_op = 'delete') 그러면
            empname = old.empname;
            찾을 수없는 경우 NULL을 반환하십시오. 끝 IF;

            old.last_updated = now ();
            emp_audit 값 ( 'd', user, old.*)에 삽입;
            옛날 복귀;
        elsif (tg_op = 'update')
            EMP 업데이트 SET SALARY = NEW.SALARY WHER EMPNAME = OLD.EMPNAME;
            찾을 수없는 경우 NULL을 반환하십시오. 끝 IF;

            new.last_updated = now ();
            emp_audit 값에 삽입 ( 'u', user, new.*);
            새로운 반환;
        elsif (tg_op = 'insert')
            EMP 값에 삽입 (New.empname, New.salary);

            new.last_updated = now ();
            emp_audit 값 ( 'i', user, new.*)에 삽입;
            새로운 반환;
        끝 IF;
    끝;
$$ 언어 plpgsql;

와이즈 토토 emp_audit을 만듭니다
emp_view에 삽입하거나 업데이트하거나 삭제하는 대신
    각 행에 대해 Execute Procedure update_emp_view ();

예 39-6. 에이PL/PGSQL유지 관리 절차 요약 테이블

여기에 자세히 설명 된 스키마는 부분적으로입니다.식료품 점예제데이터웨어 하우스 툴킷Ralph Kimball의

-
- 메인 테이블 - 시간 차원 및 판매 사실.
-
테이블 생성 time_dimension (
    time_key 정수가 null,
    day_of_week 정수 NOT NULL,
    day_of_month Integer Not Null,
    월 정수가 널이 아닌
    쿼터 정수는 null이 아닙니다.
    연도 정수는 null이 아닙니다
);
time_dimension (time_key)에서 고유 한 색인 time_dimension_key를 만듭니다.

테이블 생성 sales_fact (
    time_key 정수가 null,
    Product_key Integer Not Null,
    store_key 정수 NOT NULL,
    득
    Unit_Sold Integer Not Null,
    숫자 숫자 숫자 (12,2)는 널 not
);
sales_fact (time_key)에서 색인 sales_fact_time을 만듭니다.

-
- 요약 테이블 - 시간별 판매.
-
테이블 생성 sales_summary_bytime (
    time_key 정수가 null,
    득
    Unit_SOLD 숫자 (12) NOT NULL,
    aMond_cost 숫자 (15,2)는 null이 아닙니다
);
sales_summary_bytime (time_key)에서 고유 한 인덱스 sales_summary_bytime_key 만들기;

-
- 기능 및 와이즈 토토는 업데이트, 삽입, 삭제시 요약 된 열을 수정합니다.
-
기능을 만들거나 교체합니다
$ maint_sales_summary_bytime $입니다
    선언하다
        delta_time_key 정수;
        delta_amount_sold 숫자 (15,2);
        delta_units_sold 숫자 (12);
        delta_amount_cost 숫자 (15,2);
    시작하다

        - 증분/감소량을 계산하십시오.
        if (tg_op = 'delete') 그러면

            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) 그러면
                'Time_key의 업데이트 : % - % 허용되지 않음', 예외 제기,
                                                      old.time_key, new.time_key;
            끝 IF;

            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;

        끝 IF;

        - 새 값으로 요약 행을 삽입하거나 업데이트하십시오.
        << insert_update 
        고리
            SALLE_SUMMARY_BYTIME 업데이트
                set aveS_SOLD = aMber_sold + delta_amount_sold,
                    units_sold = units_sold + delta_units_sold,
                    aMber_cost = aMber_cost + delta_amount_cost
                여기서 time_key = delta_time_key;

            발견되면 insert_update를 종료합니다.

            시작하다
                sales_summary_bytime에 삽입 (
                            time_key,
                            aMber_sold,
                            Unit_Sold,
                            aMber_cost)
                    값 (
                            delta_time_key,
                            delta_amount_sold,
                            delta_units_sold,
                            delta_amount_cost
                           );

                insert_update 종료;

            예외
                그럼에도 불구하고
                    - 아무것도하지 않습니다
            끝;
        엔드 루프 insert_update;

        널 리턴;

    끝;
$ maint_sales_summary_bytime $ 언어 plpgsql;

와이즈 토토 maint_sales_summary_bytime을 만듭니다
sales_fact에서 삽입 또는 업데이트 또는 삭제 후
    각 행에 대해 실행 절차 maint_sales_summary_bytime ();

sales_fact 값 (1,1,1,10,3,15)에 삽입;
Sales_Fact 값 (1,2,1,20,5,35)에 삽입;
sales_fact 값에 삽입 (2,2,1,40,15,135);
Sales_Fact 값 (2,3,1,10,1,13)에 삽입;
sales_summary_bytime에서 *를 선택하십시오.
sales_fact에서 삭제하는 곳에서 product_key = 1;
sales_summary_bytime에서 *를 선택하십시오.
Sales_fack Set units_sold = units_sold * 2 업데이트;
sales _summary_bytime에서 * 선택 *