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

Bài viết này sẽ giới thiệu về từ khóa UPSERT | UPSERT ALL và UPDATE khi được sử dụng kèm theo với từ khóa RULES trong mệnh đề MODEL.

1. Từ khóa UPSERT

UPSERT là từ khóa viết tắt của UPdate và inSERT được sử dụng trong Oracle - nghĩa là trong tập dữ liệu gốc ban đầu nếu như tồn tại giá trị tính toán thì từ khóa này sẽ thực hiện UPdate dữ liệu, còn nếu như không tồn tại giá trị tính toán thì từ khóa này sẽ inSERT 1 dòng dữ liệu mới.

Chúng ta xét câu lệnh sau:

1 2 3 4 5 6 7 8 9 10 11 12 13 14

SELECT prd_type_id, year, month, sales_amount FROM all_sales a WHERE month IN (11, 12) MODEL PARTITION BY (prd_type_id) DIMENSION BY (month, year) MEASURES (amount sales_amount) RULES UPSERT (sales_amount [12, 2003] = sales_amount[11, 2003] * 1.1, sales_amount [1, 2004] = sales_amount[12, 2003] * 1.1) ORDER BY prd_type_id, year, month;

(Câu lệnh 1)

Kết quả:

(Hình 1)

Chúng ta thấy rằng doanh số bán hàng của tháng 12/2003 được UPdate và doanh số bán hàng của tháng 01/2004 thì được inSERT.

* Kết hợp với từ khóa RETURN UPDATED ROWS
Câu lệnh:

1 2 3 4 5 6 7 8 9 10 11 12 13 14

SELECT prd_type_id, year, month, sales_amount FROM all_sales a WHERE month IN (11, 12) MODEL RETURN UPDATED ROWS PARTITION BY (prd_type_id) DIMENSION BY (month, year) MEASURES (amount sales_amount) RULES UPSERT (sales_amount [12, 2003] = sales_amount[11, 2003] * 1.1, sales_amount [1, 2004] = sales_amount[12, 2003] * 1.1) ORDER BY prd_type_id, year, month;

(Câu lệnh 2)

Kết quả:

(Hình 2)

Khi kết hợp với từ khóa RETURN UPDATED ROWS thì chỉ có các dòng được UPdate hoặc inSERT mới có trong tập kết quả trả về. Với Câu lệnh 1 thì từ khóa RETURN ALL ROWS mặc định được sử dụng (Xem thêm bài viết Các cách trả dữ liệu về với từ khóa RETURN)

Lưu ý: từ khóa UPSERT mặc định sẽ được sử dụng nếu như chúng ta không khai báo

2. Từ khóa UPDATE

Khi sử dụng với từ khóa này thì chỉ có các giá trị nào tồn tại trong tập dữ liệu gốc ban đầu mới được cập nhật. Các giá trị không tồn tại sẽ không được xử lý tính toán.

Câu lệnh:

1 2 3 4 5 6 7 8 9 10 11 12 13 14

SELECT prd_type_id, year, month, sales_amount FROM all_sales a WHERE month IN (11, 12) MODEL PARTITION BY (prd_type_id) DIMENSION BY (month, year) MEASURES (amount sales_amount) RULES UPDATE (sales_amount [12, 2003] = sales_amount[11, 2003] * 1.1, sales_amount [1, 2004] = sales_amount[12, 2003] * 1.1) ORDER BY prd_type_id, year, month;

(Câu lệnh 3)

Kết quả:

(Hình 3)

Tập dữ liệu gốc ban đầu trước khi tính toán chứa doanh số bán hàng của 2 tháng: 11/2003 và 12/2003. Khi tập này được sử dụng với từ khóa UPDATE thì chỉ có biểu thức tính toán sales_amount [12, 2003] = sales_amount[11, 2003] * 1.1 được áp dụng, biểu thức còn lại không được áp dụng nên tập kết quả trả về sẽ không có dữ liệu của tháng 01/2004.

* Kết hợp với từ khóa RETURN UPDATED ROWS
Câu lệnh:

1 2 3 4 5 6 7 8 9 10 11 12 13 14

SELECT prd_type_id, year, month, sales_amount FROM all_sales a WHERE month IN (11, 12) MODEL RETURN UPDATED ROWS PARTITION BY (prd_type_id) DIMENSION BY (month, year) MEASURES (amount sales_amount) RULES UPDATE (sales_amount [12, 2003] = sales_amount[11, 2003] * 1.1, sales_amount [1, 2004] = sales_amount[12, 2003] * 1.1) ORDER BY prd_type_id, year, month;

(Câu lệnh 4)

Kết quả:

(Hình 4)

3. Từ khóa UPSERT ALL
Chúng ta so sánh 2 câu lệnh sau:

SELECT prd_type_id, year, month, sales_amount FROM all_sales a WHERE month IN (11, 12) MODEL RETURN UPDATED ROWS PARTITION BY (prd_type_id) DIMENSION BY (month, year) MEASURES (amount sales_amount) RULES UPSERT (sales_amount [11, 2004] = sales_amount[11, 2003] * 1.1, sales_amount [12, 2004] = sales_amount[12, 2003] * 1.1) ORDER BY prd_type_id, year, month;	SELECT prd_type_id, year, month, sales_amount FROM all_sales a WHERE month IN (11, 12) MODEL  RETURN UPDATED ROWS PARTITION BY (prd_type_id) DIMENSION BY (month, year) MEASURES (amount sales_amount) RULES UPSERT (sales_amount [any, 2004] = sales_amount[cv(), 2003] * 1.1) ORDER BY prd_type_id, year, month;
(Câu lệnh 5)	(Câu lệnh 6)

Kết quả:

(Hình 5)

(Hình 6)

Khi viết câu lệnh 6, chúng ta muốn nó là cách viết gọn của câu lệnh 5 nhưng kết quả lại không như ta mong đợi, câu lệnh 5 cho kết quả như hình 5 còn với câu lệnh 6 thì không có dữ liệu trả về.

Câu lệnh 5 sử dụng Positional Notation trong khi câu lệnh 6 lại sử dụng Symbolic Notation (xem bài Cách truy xuất dữ liệu (Positional Cell Reference và Symbolic Cell Reference)). Trong khi đó từ khóa ANY thì luôn được sử dụng với Symbolic Notation (xem bài Cách sử dụng hàm CV, từ khóa ANY và IS ANY) và sẽ không có dòng mới được insert.

Vì vậy, với câu lệnh 6 muốn kết quả trả về như hình 5 chúng ta phải dùng từ khóa UPSERT ALL.
Câu lệnh:

1 2 3 4 5 6 7 8 9 10 11 12 13 14

SELECT prd_type_id, year, month, sales_amount FROM all_sales a WHERE month IN (11, 12) MODEL RETURN UPDATED ROWS PARTITION BY (prd_type_id) DIMENSION BY (month, year) MEASURES (amount sales_amount) RULES UPSERT ALL (sales_amount [any, 2004] = sales_amount[cv(), 2003] * 1.1) ORDER BY prd_type_id, year, month;

(Câu lệnh 7)

Chú ý: ngoài từ khóa ANY thì các phép so sánh (>, >=, <, <=) hay từ khóa IN cũng cho kết quả như ví dụ trên.

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