Oracle Tech Talk

All about Oracle Technologies

Friday, 25 September 2015

Cách sử dụng mệnh đề MODEL (Phần 7)

11:46 Posted by NTP , No comments
Bài viết này sẽ giới thiệu cách sử dụng từ khóa ITERATE trong mệnh đề MODEL.

Trong mệnh đề MODEL, chúng ta có thể sử dụng vòng lặp cho các biểu thức tính toán bằng cách sử dụng từ khóa ITERATE.
 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
<prior clauses of SELECT statements>
MODEL [main] [RETURN {ALL|UPDATED} ROWS]
  [reference models]
  [PARTITION BY (<cols>)]
  DIMENSION BY (<cols>)
  MEASURES (<cols>)
    [IGNORE NAV] | [KEEP NAV]
  [RULES
    [UPSERT | UPDATE]
    [AUTOMATIC ORDER | SEQUENTIAL ORDER]
    [ITERATE (n) [UNTIL <condition>]]
    ( <cell_assignment> = <expression> ... )

Chúng ta xét câu lệnh sau:
 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
SELECT prd_type_id,
       year,
       month,
       sales_amount
  FROM all_sales a
MODEL RETURN UPDATED ROWS
    PARTITION BY (prd_type_id)
    DIMENSION BY (month, year)
    MEASURES (amount sales_amount)
    RULES
        ITERATE (3)
        (sales_amount [2, 2003] = sales_amount[1, 2003] + 1000)
ORDER BY prd_type_id, year, month;
(Câu lệnh 1)
Câu lệnh trên sẽ thực thi biểu thức tính toán doanh số bán hàng của tháng 2/2003 3 lần.

1. Sử dụng với hàm ITERATION_NUMBER
Để biết được vòng lặp đang thực thi ở lần thứ mấy, Oracle cung cấp cho chúng ta hàm ITERATION_NUMBER. Giá trị bắt đầu của hàm này là 0 và được tăng dần lên 1 đơn vị. Chúng ta bổ sung vào câu lệnh 1 một cột nữa để kiểm tra sự hoạt động của hàm ITERATION_NUMBER.
Câu lệnh:
 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
SELECT prd_type_id,
       year,
       month,
       sales_amount,
       iterator
  FROM all_sales a
MODEL RETURN UPDATED ROWS
    PARTITION BY (prd_type_id)
    DIMENSION BY (month, year)
    MEASURES (amount sales_amount, CAST (NULL AS VARCHAR2 (20)) AS iterator)
    RULES
        ITERATE (3)
        (sales_amount [2, 2003] = sales_amount[1, 2003] + 1000,
        iterator [2, 2003] = iterator[2, 2003] || ITERATION_NUMBER || ' ')
ORDER BY prd_type_id, year, month;
(Câu lệnh 2)
Kết quả:
(Hình 1)
Chúng ta thấy rằng cột ITERATOR qua các lần lặp sẽ cho chúng ta biết được giá trị hiện tại của các lần lặp đó và có giá trị khởi đầu là 0 ở lần lặp đầu tiên.

Chúng ta cũng lưu ý về cách thức hoạt động của vòng lặp như sau:
  • Lần lặp thứ 1
    • Thực thi biểu thức tính toán 1
    • Thực thi biểu thức tính toán 2
    • ...
    • Thực thi biểu thức tính toán n
  • Lần lặp thứ 2
    • Thực thi biểu thức tính toán 1
    • Thực thi biểu thức tính toán 2
    • ...
    • Thực thi biểu thức tính toán n
  • Lần lặp thứ n
    • Thực thi biểu thức tính toán 1
    • Thực thi biểu thức tính toán 2
    • ...
    • Thực thi biểu thức tính toán n
Xét câu lệnh sau để thấy cách hoạt động của vòng lặp:
 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
SELECT prd_type_id,
       year,
       month,
       sales_amount,
       iterator
  FROM all_sales a
MODEL RETURN UPDATED ROWS
    PARTITION BY (prd_type_id)
    DIMENSION BY (month, year)
    MEASURES (amount sales_amount, CAST (NULL AS VARCHAR2 (20)) AS iterator)
    RULES
        ITERATE (3)
        (sales_amount [2, 2003] = sales_amount[1, 2003] + 1000,
        iterator [2, 2003] = iterator[2, 2003] || 'x',
        iterator [2, 2003] = iterator[2, 2003] || 'y')
ORDER BY prd_type_id, year, month;
(Câu lệnh 3)
Kết quả:

(Hình 2)
2. Sử dụng với từ khóa UNTIL
Từ khóa UNTIL được sử dụng kèm theo mệnh đề điều kiện để kết thúc vòng lặp khi thỏa điều kiện. Chúng ta xét ví dụ sau:
 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
SELECT prd_type_id,
       year,
       month,
       sales_amount,
       iterator
  FROM all_sales a
MODEL RETURN UPDATED ROWS
    PARTITION BY (prd_type_id)
    DIMENSION BY (month, year)
    MEASURES (amount sales_amount, CAST (NULL AS VARCHAR2 (20)) iterator)
    RULES
        ITERATE (12) UNTIL sales_amount[12, 2003] > 4500
        (sales_amount [12, 2003] =
                  sales_amount[ITERATION_NUMBER + 1, 2003]
                + sales_amount[ITERATION_NUMBER + 2, 2003],
        iterator [12, 2003] =
                iterator[12, 2003] || ITERATION_NUMBER || ' ')
ORDER BY prd_type_id, year, month;
(Câu lệnh 4)
Kết quả:
(Hình 3)

Câu lệnh 4 có số lần lặp cho các biểu thức tính toán là 12 lần và sẽ dừng biểu thức tính toán khi thỏa điều kiện của biểu thức điều kiện: sales_amount[12, 2003] > 4500. Kết quả cho thấy với PRD_TYPE_ID là 1 thì ở lần lặp thứ 1 đã thỏa điều kiện trong khi PRD_TYPE_ID là 2 thì ở lần lặp thứ 3 mới thỏa.

Chú ý: các biểu thức tính toán sẽ được thực hiện ít nhất 1 lần trước khi xét đến biểu thức điều kiện được khai báo với từ khóa UNTIL.

3. Sử dụng với hàm PREVIOUS
Để tham chiếu với giá trị trước đó trong vòng lặp, chúng ta có thể dùng hàm PREVIOUS.
Câu lệnh:
 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
SELECT prd_type_id,
       year,
       month,
       sales_amount,
       iterator
  FROM all_sales a
MODEL RETURN UPDATED ROWS
    PARTITION BY (prd_type_id)
    DIMENSION BY (month, year)
    MEASURES (amount sales_amount, CAST (NULL AS VARCHAR2 (20)) iterator)
    RULES
        ITERATE (12) UNTIL PREVIOUS (sales_amount[12, 2003]) > 4500
        (sales_amount [12, 2003] =
                  sales_amount[ITERATION_NUMBER + 1, 2003]
                + sales_amount[ITERATION_NUMBER + 2, 2003],
        iterator [12, 2003] =
                iterator[12, 2003] || ITERATION_NUMBER || ' ')
ORDER BY prd_type_id, year, month;
(Câu lệnh 5)
Kết quả:
(Hình 4)
Chú ý: hàm PREVIOUS chỉ được sử dụng với từ khóa UNTIL, các vị trí khác khi dùng hàm này sẽ bị báo lỗi:
ORA-32618: incorrect use of MODEL PREVIOUS function

4. Các vấn đề khác
  • Số vòng lặp tối đa được cho phép ở phiên bản 12c là: 4,294,967,295 (4Gig - 1) lần.
  • Hàm ITERATION_NUMBER có thể được sử dụng trong biểu thức điều kiện của từ khóa UNTIL nhưng không được sử dụng trong PARTITION BY hoặc DIMENSION BY hoặc MEASURES.
  • Bản chất của việc lặp này là với mỗi giá trị duy nhất trong PARTITION BY Oracle sẽ lặp lại n lần bộ các biểu thức tính toán được khai báo.
Các bài viết có liên quan
  1. Giới thiệu tổng quan
  2. Các cách trả dữ liệu về với từ khóa RETURN
  3. Cách truy xuất dữ liệu (Positional Cell Reference và Symbolic Cell Reference)
  4. Cách sử dụng hàm CV, từ khóa ANY và IS ANY
  5. Cách xử lý các giá trị NULL qua các từ khóa IS PRESENT | KEEP NAV | IGNORE NAV và các hàm PRESENTV | PRESENTNNV
  6. Quy định về trật tự tính toán của các biểu thức qua từ khóa AUTOMATIC ORDER | SEQUENTIAL ORDER
  7. Vòng lặp các biểu thức tính toán với từ khóa ITERATE
  8. Cách sử dụng từ khóa UPSERT | UPSERT ALL và UPDATE với từ khóa RULES
  9. Cách sử dụng từ khóa UNIQUE DIMENSION và UNIQUE SINGLE REFERENCE
  10. Cách sử dụng mệnh đề REFERENCE để tham chiếu dữ liệu

0 comments :

Post a Comment