Vòng lặp FOR trong PL/SQL

Xem thử

Tình huống mở đầu

Bạn đã học PL/pgSQL ở chương 26, nơi đếm ngược viết là FOR i IN REVERSE 3..1. Hôm nay quầy sách muốn in nhãn ba tầng kệ theo thứ tự tầng 3, tầng 2, tầng 1. Bạn gõ y hệt sang Oracle, bấm chạy, và màn hình báo PL/SQL procedure successfully completed.. Không lỗi nào, nhưng cũng không có dòng nào được in ra.

Ở bài trước, vòng LOOP buộc bạn tự tăng bộ đếm và tự viết điều kiện dừng. Khi bạn biết trước số lượt, như đúng 5 ngày hay đúng 3 tầng, vòng FOR làm hộ phần đó. Nhưng Oracle viết REVERSE khác PostgreSQL một chút, và cái "một chút" ấy làm vòng của bạn chạy 0 lượt.

Hãy đoán trước hai điều. Một, vì sao vòng REVERSE 3..1 im lặng không in gì, và Oracle muốn bạn viết thế nào. Hai, khi duyệt các dòng của một bảng, vòng FOR có cần bạn khai báo cursor không?

Ôn nhanh bài trước: vòng LOOP có EXIT WHEN v > 10; mà v là NULL thì chuyện gì xảy ra?

Đáp án: vòng không thoát vì điều kiện cho NULL chứ không phải TRUE, và nếu không có chốt chặn thì chạy mãi.

Mục tiêu bài học

Sau bài này bạn có thể:

Viết FOR i IN thấp..cao LOOP và nói được biến i khai báo ngầm, chỉ sống trong vòng, không gán được, và hai cận chỉ được tính một lần.
Viết REVERSE đúng thứ tự của Oracle (cận thấp trước), nhận ra vòng rỗng khi cận bị đảo, và dùng BY để đi theo bước ở Oracle 23ai.
Dùng vòng FOR r IN (SELECT ...) để duyệt từng dòng, và giải thích Oracle tự mở, đọc và đóng cursor giúp bạn.
Giải thích vì sao vòng lặp từng dòng chậm hơn một câu lệnh tập hợp và khi nào vẫn nên dùng vòng lặp.

Bức tranh lớn

Xuyên suốt bài, FOR là băng chuyền có đồng hồ đếm. Bạn cài sẵn "đi từ tầng 1 đến tầng 3" hoặc "đi qua hết khay hàng này", và băng chuyền tự đếm, tự dừng. Bạn không cần nút dừng như ở vòng LOOP. Dự án xuyên suốt vẫn là băng chuyền kiểm hàng của quầy sách books, chạy trên Oracle AI Database 26ai Free (release 23.26.3).

Hình dưới trả lời một câu hỏi: Oracle chạy một vòng FOR số theo những bước nào, và vòng rỗng xuất hiện ở đâu?

Đang tải sơ đồ…

Cách đọc hình: đọc từ ô xanh dương xuống. Hai cận được tính đúng một lần ở ô đầu tiên, dù sau đó bạn có đổi biến chứa cận hay không. Ô cam đầu tiên là chỗ vòng rỗng xuất hiện: cận thấp lớn hơn cận cao thì thân vòng không chạy lần nào. Ô cam thứ hai hỏi giá trị kế tiếp còn trong khoảng không; còn thì quay lại, hết thì ra ô xanh lá và biến i biến mất.

Chỗ ẩn dụ không còn đúng: đồng hồ đếm của băng chuyền thật chỉ đếm tới, còn FOR có thể không chạy một lượt nào mà không phát ra tiếng, và cũng không cho bạn chỉnh tay con số đang đếm.

Khái niệm cốt lõi

Bài này có bốn khái niệm.

1. `FOR` số: biến ngầm, cận một lần

FOR i IN thấp..cao LOOP ... END LOOP; tự khai báo biến i ngay chỗ viết vòng, không cần có trong DECLARE. Biến này chỉ sống trong thân vòng, không gán được giá trị (đọc thì được), và hai cận chỉ được tính một lần trước khi vòng bắt đầu. Cả hai đầu đều được tính: 1..3 chạy với 1, 2 và 3. Cận thấp lớn hơn cận cao thì vòng rỗng. Cận là NULL thì Oracle báo lỗi ORA-06502.

2. `REVERSE` và `BY`

REVERSE chạy từ cận cao xuống cận thấp, nhưng bạn vẫn viết cận thấp trước: FOR i IN REVERSE 1..3. Đây là điểm khác với PL/pgSQL, nơi cận cao đứng trước. Từ Oracle 23ai có thêm BY bước để đi theo bước nhảy, như FOR i IN 1..10 BY 3.

3. Vòng `FOR` duyệt kết quả `SELECT`

FOR r IN (SELECT ...) LOOP ... END LOOP; chạy thân vòng một lần cho mỗi dòng kết quả, và r là biến bản ghi giữ dòng hiện tại. Oracle tự mở cursor, đọc từng dòng và đóng cursor khi hết dòng hoặc khi thoát vòng. Đọc cột bằng r.tên_cột. Câu SELECT phải nằm trong ngoặc, và truy vấn trả về 0 dòng thì thân vòng đơn giản không chạy, không có lỗi NO_DATA_FOUND.

4. Vòng từng dòng và câu lệnh tập hợp

Mỗi câu SQL bên trong vòng làm máy phải chuyển qua lại giữa động cơ PL/SQL và động cơ SQL (chuyển ngữ cảnh). Với 50000 lượt là 50000 lần chuyển. Một câu lệnh tập hợp như INSERT ... SELECT hay UPDATE ... SET ... WHERE xử lý hết các dòng trong một lần gọi. Vòng lặp xứng đáng khi mỗi dòng cần logic mà một câu lệnh không diễn tả được.

Ví dụ đầu tiên

Dự báo tồn kho sách Data Modeling (id bằng 3, đang có 5 cuốn) trong 5 ngày tới, giả sử mỗi ngày bán đúng 1 cuốn.

sql

SET FEEDBACK OFF
DECLARE
  v_stock NUMBER;
BEGIN
  SELECT stock INTO v_stock FROM books WHERE id = 3;
  FOR day_no IN 1..5 LOOP
    v_stock := GREATEST(v_stock - 1, 0);   -- mỗi ngày bán 1 cuốn, không xuống dưới 0
    DBMS_OUTPUT.PUT_LINE('day ' || day_no || ': stock ' || v_stock);
  END LOOP;
END;
/

Dự đoán trước khi xem kết quả: vòng chạy mấy lượt, và dòng cuối in stock bằng bao nhiêu?

Kết quả [Đã chạy: Oracle AI Database 26ai Free 23.26.3]:

text

day 1: stock 4
day 2: stock 3
day 3: stock 2
day 4: stock 1
day 5: stock 0

Dòng code	Máy làm gì	Vì sao viết như vậy
`SELECT stock INTO v_stock FROM books WHERE id = 3;`	Đọc tồn kho của sách 3, giá trị là 5	Lấy dữ liệu thật thay vì gõ số
`FOR day_no IN 1..5 LOOP`	Tạo ngầm biến `day_no` và chạy với 1, 2, 3, 4, 5	Biết trước đúng 5 ngày nên không cần bộ đếm tự viết
`v_stock := GREATEST(v_stock - 1, 0);`	Trừ 1 cuốn, nhưng không để tồn kho xuống dưới 0	Tồn kho âm là vô nghĩa nên chặn ở 0
`DBMS_OUTPUT.PUT_LINE`	In ngày và tồn kho sau ngày đó	Thấy từng lượt của vòng
`END LOOP;`	Đóng vòng, biến `day_no` biến mất	Sau dòng này `day_no` không còn dùng được

Vòng chạy đúng 5 lượt và tồn kho về 0 đúng ngày thứ 5. Bạn không viết dòng nào để tăng day_no hay kiểm tra khi nào dừng: Oracle làm hộ cả hai.

Xem từng bước

Animation dưới đây đi qua hai cách viết đếm ngược: REVERSE 1..3 là cách đúng của Oracle, còn REVERSE 3..1 là thói quen mang từ PL/pgSQL sang. Hãy trả lời câu dự đoán trước khi bấm bước đầu tiên.

Đang tải Animation…

Hai khối chạy thật cho hai cách viết [Đã chạy: Oracle 23.26.3]. Cách đúng:

sql

SET FEEDBACK OFF
BEGIN
  FOR tier IN REVERSE 1..3 LOOP
    DBMS_OUTPUT.PUT_LINE('tier ' || tier);
  END LOOP;
END;
/

text

tier 3
tier 2
tier 1

Cách mang từ PL/pgSQL sang. Khối có thêm một biến đếm để chứng minh thân vòng chạy 0 lượt:

sql

SET FEEDBACK OFF
DECLARE
  v_printed NUMBER := 0;
BEGIN
  FOR tier IN REVERSE 3..1 LOOP
    DBMS_OUTPUT.PUT_LINE('tier ' || tier);
    v_printed := v_printed + 1;
  END LOOP;
  DBMS_OUTPUT.PUT_LINE('lines printed: ' || v_printed);
END;
/

text

lines printed: 0

Bảng trace bằng chữ [Đã chạy: Oracle 23.26.3, hai khối trên]:

Cách viết	Cận thấp	Cận cao	Biến `tier` nhận	Dòng được in
`REVERSE 1..3`	1	3	3, 2, 1	3
`REVERSE 3..1`	3	1	không giá trị nào	0

Ví dụ thứ hai: duyệt từng dòng của bảng

Tính giá trị hàng tồn của quầy: với mỗi đầu sách, nhân giá với tồn kho, rồi cộng lại.

sql

SET FEEDBACK OFF
DECLARE
  v_total NUMBER := 0;
BEGIN
  FOR r IN (SELECT title, price, stock FROM books ORDER BY id) LOOP
    v_total := v_total + r.price * r.stock;
    DBMS_OUTPUT.PUT_LINE(r.title || ': ' || r.price * r.stock);
  END LOOP;
  DBMS_OUTPUT.PUT_LINE('inventory value: ' || v_total);
END;
/

Kết quả [Đã chạy: Oracle 23.26.3]:

text

SQL Basics: 1200000
PL/SQL Intro: 0
Data Modeling: 750000
inventory value: 1950000

Dòng code	Máy làm gì	Vì sao viết như vậy
`FOR r IN (SELECT title, price, stock FROM books ORDER BY id) LOOP`	Mở cursor, đọc dòng đầu tiên vào biến bản ghi `r`	Oracle tự mở, đọc và đóng cursor nên không cần `OPEN` hay `FETCH`
`v_total := v_total + r.price * r.stock;`	Cộng giá trị của dòng hiện tại vào tổng	Đọc cột bằng `r.price` và `r.stock`
`END LOOP;`	Hết dòng thì đóng cursor và rời vòng	Không cần `EXIT`, vòng tự dừng khi hết dữ liệu

Cùng con số đó, một câu SQL tập hợp cho kết quả như sau [Đã chạy: Oracle 23.26.3]:

sql

SET FEEDBACK OFF
SELECT SUM(price * stock) AS inventory_value FROM books;

text

INVENTORY_VALUE
---------------
	1950000

Hai cách ra cùng 1950000. Câu SQL ngắn hơn và để Oracle làm toàn bộ việc cộng; vòng chỉ cần thiết khi mỗi dòng cần xử lý riêng, như in từng dòng ở trên.

Bạn vừa làm được: viết vòng FOR đếm số và vòng FOR duyệt bảng, và nói đúng vòng chạy mấy lượt, kể cả khi vòng rỗng.

Concept Check

Dùng các khối ở trên.

Câu 1. Vòng FOR tier IN REVERSE 3..1 LOOP ... END LOOP; chạy trên Oracle 23.26.3. Chuyện gì xảy ra?

A. Thân vòng chạy với tier 3, 2, 1 như trong PL/pgSQL.
B. Oracle báo lỗi ORA-06502 vì hai cận đứng sai thứ tự.
C. Thân vòng chạy 0 lượt và Oracle không báo lỗi.
D. Thân vòng chạy với tier 1, 2, 3 vì Oracle tự đổi chỗ hai cận.

Câu 2. Trong thân vòng FOR i IN 1..3 LOOP, bạn viết i := i + 1;. Kết quả là gì?

A. Vòng bỏ qua một giá trị của i vì i được cộng thêm 1.
B. Lỗi biên dịch PLS-00363, vì i không dùng làm đích của phép gán.
C. Vòng chạy mãi vì i không bao giờ chạm cận trên.
D. Lỗi lúc chạy ORA-06502 ở lượt đầu tiên.

Câu 3. Bảng books có 3 dòng và bạn chạy FOR r IN (SELECT id FROM books WHERE stock > 1000) LOOP ... END LOOP;. Chuyện gì xảy ra?

A. Lỗi NO_DATA_FOUND, vì truy vấn không có dòng nào.
B. Thân vòng chạy một lần với r.id bằng NULL.
C. Thân vòng chạy ba lượt, vì điều kiện WHERE bị bỏ qua trong vòng FOR.
D. Thân vòng không chạy lần nào và không có lỗi.

Đáp án và giải thích:

Câu 1: C. A đúng với PL/pgSQL nhưng sai với Oracle. B sai vì NULL mới gây ORA-06502, còn cận đảo thì chỉ làm vòng rỗng. D sai vì Oracle không tự đổi chỗ hai cận.
Câu 2: B. A sai vì không có phép gán nào thành công. C sai vì lỗi xảy ra ngay lúc biên dịch. D sai vì khối không được biên dịch nên không chạy.
Câu 3: D. A sai vì NO_DATA_FOUND chỉ thuộc SELECT INTO. B sai vì không có dòng thì thân vòng không chạy. C sai vì WHERE vẫn có hiệu lực.

Làm cùng mình

Quầy lập danh sách những đầu sách sắp hết (dưới 6 cuốn) bằng vòng FOR duyệt bảng.

Bước 1. Đoán trên giấy

Bảng books có tồn kho 10, 0 và 5. Sách nào có dưới 6 cuốn? Ghi dự đoán trước khi viết code.

Bước 2. Hoàn thành khối

sql

SET FEEDBACK OFF
BEGIN
  -- TODO 1: mở vòng FOR duyệt SELECT title, stock FROM books WHERE stock < 6 ORDER BY id
  --         (nhớ đặt câu SELECT trong ngoặc, biến bản ghi tên r)
    -- TODO 2: in một dòng dạng low: <title> (<stock>)
  -- TODO 3: đóng vòng
END;
/

Gợi ý: đọc cột của biến bản ghi bằng r.title và r.stock.

Chạy xong bạn sẽ thấy [Đã chạy] hai dòng, cho PL/SQL Intro và Data Modeling. Đáp án ở mục Lời giải và đáp án.

Bước 3. Thử với 0 dòng

Đổi điều kiện thành WHERE stock > 1000 và thêm một dòng check finished sau vòng. Dự đoán trước: có lỗi không, và dòng check finished có hiện ra không?

Bước 4. Ghi lại

Viết một câu: vì sao vòng FOR duyệt bảng không cần EXIT và không cần OPEN hay FETCH.

Thử phá nó đi

Các lỗi và bẫy hay gặp với vòng FOR [Đã chạy: Oracle 23.26.3]:

Code lỗi	Thông báo hoặc hiện tượng	Nguyên nhân	Cách sửa
Gán lại biến vòng `i := i + 1;`	`PLS-00363: expression 'I' cannot be used as an assignment target`	Biến vòng chỉ đọc	Dùng biến phụ nếu cần thay đổi
Dùng `i` sau `END LOOP;`	`PLS-00201: identifier 'I' must be declared`	Biến ngầm chỉ sống trong vòng	Khai báo biến riêng ở `DECLARE` nếu cần giá trị sau vòng
Cận là `NULL`	`ORA-06502: PL/SQL: value or conversion error`	Hai cận phải là số	Xử lý `NULL` bằng `NVL` trước vòng
`FOR r IN SELECT ...` thiếu ngoặc	`PLS-00103: Encountered the symbol "SELECT" when expecting one of the following`	Câu `SELECT` trong vòng `FOR` phải đặt trong ngoặc	Viết `FOR r IN (SELECT ...)`
`REVERSE 3..1`	Không lỗi, 0 lượt	Cận thấp phải đứng trước	Viết `REVERSE 1..3`
Khai báo `i` ở `DECLARE` rồi kỳ vọng vòng dùng biến đó	Biến ngoài không đổi	Vòng `FOR` tạo biến riêng che biến ngoài	Đặt tên khác cho dễ đọc
Sửa `r.stock` mong cập nhật bảng	Không lỗi, bảng không đổi	`r` chỉ là bản sao dòng đã đọc	Dùng `UPDATE` để ghi vào bảng
Cộng dồn cột có `NULL`	Tổng cuối cùng là `NULL`	`NULL` lan sang mọi phép cộng sau nó	Dùng `NVL` hoặc dùng `SUM`

Gán lại biến vòng:

sql

SET FEEDBACK OFF
BEGIN
  FOR i IN 1..3 LOOP
    i := i + 1;
  END LOOP;
END;
/

text

i := i + 1;
    *
ERROR at line 3:
ORA-06550: line 3, column 5:
PLS-00363: expression 'I' cannot be used as an assignment target
ORA-06550: line 3, column 5:
PL/SQL: Statement ignored
Help: https://docs.oracle.com/error-help/db/ora-06550/

Dùng biến vòng sau khi vòng đã kết thúc:

sql

SET FEEDBACK OFF
BEGIN
  FOR i IN 1..3 LOOP
    NULL;
  END LOOP;
  DBMS_OUTPUT.PUT_LINE(i);
END;
/

text

DBMS_OUTPUT.PUT_LINE(i);
                       *
ERROR at line 5:
ORA-06550: line 5, column 24:
PLS-00201: identifier 'I' must be declared
ORA-06550: line 5, column 3:
PL/SQL: Statement ignored
Help: https://docs.oracle.com/error-help/db/ora-06550/

Cận là NULL:

sql

SET FEEDBACK OFF
BEGIN
  FOR i IN 1..NULL LOOP
    DBMS_OUTPUT.PUT_LINE(i);
  END LOOP;
END;
/

text

BEGIN
*
ERROR at line 1:
ORA-06502: PL/SQL: value or conversion error
ORA-06512: at line 2
Help: https://docs.oracle.com/error-help/db/ora-06502/

Thiếu ngoặc quanh câu SELECT. Oracle liệt kê rất nhiều ký hiệu có thể đứng ở đó; chỉ cần nhìn dòng đầu:

sql

SET FEEDBACK OFF
BEGIN
  FOR r IN SELECT id FROM books LOOP
    NULL;
  END LOOP;
END;
/

text

FOR r IN SELECT id FROM books LOOP
           *
ERROR at line 2:
ORA-06550: line 2, column 12:
PLS-00103: Encountered the symbol "SELECT" when expecting one of the following:
( - + case mod new not null <an identifier>
<a double-quoted delimited-identifier> <a bind variable>
reverse continue avg count current exists max min prior sql
stddev sum values variance execute forall merge
<a SQL statement> time timestamp interval date PAIRS_ REPEAT_
<a string literal with character set specification>
<a number> <a single-quoted SQL string> pipe
<an alternatively-quoted string litera
ORA-06550: line 3, column 5:
PLS-00103: Encountered the symbol "NULL" when expecting one of the following:
) , for group having intersect minus order start union where
connect
Help: https://docs.oracle.com/error-help/db/ora-06550/

Biến i khai báo ở DECLARE và biến i của vòng là hai biến khác nhau:

sql

SET FEEDBACK OFF
DECLARE
  i NUMBER := 99;
BEGIN
  FOR i IN 1..2 LOOP
    DBMS_OUTPUT.PUT_LINE('loop i=' || i);
  END LOOP;
  DBMS_OUTPUT.PUT_LINE('after loop i=' || i);
END;
/

text

loop i=1
loop i=2
after loop i=99

Sửa r.stock trong vòng chỉ sửa bản sao, bảng vẫn nguyên:

sql

SET FEEDBACK OFF
DECLARE
  v_after NUMBER;
BEGIN
  FOR r IN (SELECT id, stock FROM books WHERE id = 1) LOOP
    r.stock := 0;
    DBMS_OUTPUT.PUT_LINE('record now: ' || r.stock);
  END LOOP;
  SELECT stock INTO v_after FROM books WHERE id = 1;
  DBMS_OUTPUT.PUT_LINE('table still: ' || v_after);
END;
/

text

record now: 0
table still: 10

Bẫy nguy hiểm nhất là NULL trong dữ liệu. Thêm sách Indexing với stock là NULL, rồi cộng giá trị kho bằng vòng và bằng SUM [Đã chạy: Oracle 23.26.3]:

sql

SET FEEDBACK OFF
INSERT INTO books VALUES (4, 'Indexing', 'db', 90000, NULL);
DECLARE
  v_total NUMBER := 0;
BEGIN
  FOR r IN (SELECT price, stock FROM books ORDER BY id) LOOP
    v_total := v_total + r.price * r.stock;
  END LOOP;
  DBMS_OUTPUT.PUT_LINE('loop total: ' || NVL(TO_CHAR(v_total), 'NULL'));
END;
/
SELECT SUM(price * stock) AS sql_total FROM books;

text

loop total: NULL

 SQL_TOTAL
----------
   1950000

Vòng cho NULL vì 1950000 + NULL là NULL, và NULL lan sang mọi phép cộng sau đó. SUM thì bỏ qua dòng NULL nên vẫn đúng 1950000. Chữa vòng bằng NVL [Đã chạy: Oracle 23.26.3]:

sql

SET FEEDBACK OFF
INSERT INTO books VALUES (4, 'Indexing', 'db', 90000, NULL);
DECLARE
  v_total NUMBER := 0;
BEGIN
  FOR r IN (SELECT price, stock FROM books ORDER BY id) LOOP
    v_total := v_total + r.price * NVL(r.stock, 0);
  END LOOP;
  DBMS_OUTPUT.PUT_LINE('loop total: ' || v_total);
END;
/

text

loop total: 1950000

Tự tay làm

Mỗi bài nêu đầu vào, kết quả mong đợi và tiêu chí đạt.

Cơ bản

Bài 1. Điền bảng. Đầu vào: bốn vòng FOR ghép một chuỗi các giá trị của i. Điền giá trị được ghép (hoặc ghi "không có") cho mỗi cách viết.

Cách viết	Các giá trị của i
`FOR i IN 1..3`	?
`FOR i IN REVERSE 1..3`	?
`FOR i IN 3..1`	?
`FOR i IN REVERSE 3..1`	?

Tiêu chí đạt: điền đúng cả bốn ô. Gợi ý: cận thấp luôn đứng trước, REVERSE chỉ đổi chiều đi.

Vận dụng

Bài 2. Giá trị kho. Đầu vào: bảng books. Viết khối dùng vòng FOR r IN (SELECT price, stock FROM books) cộng price * stock của mọi dòng rồi in inventory value: .... Tiêu chí đạt: in ra 1950000. Gợi ý: dùng biến cộng dồn khởi tạo 0.

Thử thách (tùy chọn)

Bài 3. Vòng hay một câu lệnh. Đầu vào: bảng books. Tăng giá 10% cho mọi sách còn hàng (stock > 0) bằng hai cách: vòng FOR chạy từng UPDATE, và một câu UPDATE duy nhất. Với mỗi cách, tính tổng price của cả bảng rồi ROLLBACK. Tiêu chí đạt: hai tổng bằng nhau và bạn nói được cách nào nên dùng. Gợi ý: sách hết hàng (stock bằng 0) giữ nguyên giá.

Tự kiểm tra

Trả lời bằng lời của bạn, không nhìn lại bài. Mỗi câu ghi mục tiêu được kiểm tra.

(Mục tiêu 1, nhớ lại) Biến chạy của vòng FOR số có cần khai báo ở DECLARE không, và sống ở đâu?
(Mục tiêu 2, dự đoán) FOR i IN REVERSE 1..3 in những giá trị nào, theo thứ tự nào?
(Mục tiêu 1, giải thích) Nếu thân vòng đổi biến dùng làm cận trên, số lượt của vòng có đổi không? Vì sao?
(Mục tiêu 3, sửa lỗi) Cộng tồn kho bằng vòng ra NULL dù hầu hết dòng có số. Nguyên nhân và cách sửa?
(Mục tiêu 4, vận dụng) Khi nào bạn giữ vòng FOR thay vì viết một câu UPDATE hay INSERT ... SELECT?

Điều cần nhớ

Vòng FOR số tạo biến chạy ngầm, chỉ đọc, chỉ sống trong vòng, và hai cận chỉ được tính một lần.
REVERSE vẫn viết cận thấp trước; viết REVERSE 3..1 kiểu PL/pgSQL làm vòng rỗng mà không có lỗi.
Oracle 23ai có BY bước; bản cũ hơn không có [Suy luận].
Vòng FOR r IN (SELECT ...) tự mở, đọc, đóng cursor; câu SELECT phải nằm trong ngoặc, 0 dòng thì không có lỗi.
Vòng từng dòng chậm hơn câu lệnh tập hợp vì chuyển ngữ cảnh; cộng dồn cột có NULL bằng vòng cho NULL.

Cheat sheet:

Muốn	Viết	Ghi chú
Đếm xuôi	`FOR i IN 1..n LOOP`	Cả hai đầu đều được tính
Đếm ngược	`FOR i IN REVERSE 1..n LOOP`	Cận thấp vẫn đứng trước
Đi theo bước	`FOR i IN 1..10 BY 3 LOOP`	Chỉ từ Oracle 23ai; bước 0 hoặc âm gây lỗi
Duyệt kết quả truy vấn	`FOR r IN (SELECT ...) LOOP`	Ngoặc bắt buộc, đọc cột bằng `r.cột`
Cộng dồn cột có `NULL`	`NVL(r.cột, 0)` hoặc `SUM(cột)`	`SUM` bỏ qua dòng `NULL`
Xử lý hàng loạt	Một câu `UPDATE` hoặc `INSERT ... SELECT`	Ít chuyển ngữ cảnh nhất

Hình tóm tắt của bài là sơ đồ ở mục Bức tranh lớn.

Câu hỏi để dành cho ngày mai: FOR cần biết trước số lượt. Nếu quầy đóng sách vào các thùng 4 cuốn, và số thùng phụ thuộc vào tồn kho thay đổi giữa chừng, thậm chí có sách chưa có tồn kho, bạn dùng vòng nào?

Đào sâu

Phần này dành cho bạn tò mò.

Cơ chế bên trong. Cận được làm tròn thành số nguyên trước khi vòng bắt đầu, nên 1..2.5 chạy với 1, 2, 3 chứ không dừng ở 2 [Đã chạy: Oracle 23.26.3]:

sql

SET FEEDBACK OFF
BEGIN
  FOR i IN 1..2.5 LOOP
    DBMS_OUTPUT.PUT_LINE('i=' || i);
  END LOOP;
END;
/

text

i=1
i=2
i=3

Cận chỉ tính một lần. Đổi biến chứa cận giữa vòng không ảnh hưởng số lượt [Đã chạy: Oracle 23.26.3]:

sql

SET FEEDBACK OFF
DECLARE
  v_n NUMBER := 3;
BEGIN
  FOR i IN 1..v_n LOOP
    v_n := 10;                            -- đổi cận trong thân vòng
    DBMS_OUTPUT.PUT_LINE('i=' || i || ', v_n=' || v_n);
  END LOOP;
END;
/

text

i=1, v_n=10
i=2, v_n=10
i=3, v_n=10

Vòng vẫn chạy 3 lượt dù v_n đã thành 10 ngay lượt đầu.

So sánh với lựa chọn khác. Oracle 23ai mở rộng phần điều khiển vòng FOR: BY để chọn bước, WHEN để lọc giá trị và danh sách giá trị rời [Đã chạy: Oracle 23.26.3]:

sql

SET FEEDBACK OFF
BEGIN
  FOR i IN 1..10 BY 3 LOOP
    DBMS_OUTPUT.PUT_LINE('up ' || i);
  END LOOP;
  FOR i IN REVERSE 1..10 BY 4 LOOP
    DBMS_OUTPUT.PUT_LINE('down ' || i);
  END LOOP;
END;
/

text

up 1
up 4
up 7
up 10
down 10
down 6
down 2

sql

SET FEEDBACK OFF
BEGIN
  FOR i IN 1..8 WHEN MOD(i, 2) = 0 LOOP
    DBMS_OUTPUT.PUT_LINE('even ' || i);
  END LOOP;
  FOR i IN 1, 5, 9 LOOP
    DBMS_OUTPUT.PUT_LINE('listed ' || i);
  END LOOP;
END;
/

text

even 2
even 4
even 6
even 8
listed 1
listed 5
listed 9

Đây là tính năng của 23ai; các bản trước đó, như 19c, không chấp nhận cú pháp này [Suy luận: không chạy được trên máy này].

Cursor có tên. Ngoài FOR r IN (SELECT ...), bạn có thể khai báo cursor ở DECLARE rồi dùng tên của nó trong vòng. Cách này tiện khi cùng một truy vấn dùng nhiều chỗ [Đã chạy: Oracle 23.26.3]:

sql

SET FEEDBACK OFF
DECLARE
  CURSOR c_low IS SELECT title FROM books WHERE stock < 6 ORDER BY id;
BEGIN
  FOR r IN c_low LOOP
    DBMS_OUTPUT.PUT_LINE('low: ' || r.title);
  END LOOP;
END;
/

text

low: PL/SQL Intro
low: Data Modeling

Vòng từng dòng và chuyển ngữ cảnh

Cùng việc chèn 50000 dòng bằng ba cách: vòng từng dòng, FORALL (gom cả lô gửi một lần sang động cơ SQL) và một câu INSERT ... SELECT. Hình dưới nói vì sao cách đầu chậm.

Đang tải sơ đồ…

Cách đọc hình: hai chuỗi tách biệt. Chuỗi đỏ là vòng từng dòng: mỗi lượt là một lần gửi câu INSERT sang động cơ SQL, nên có 50000 lần chuyển. Chuỗi xanh là một câu INSERT ... SELECT: động cơ SQL điền cả 50000 dòng trong một lần gọi.

Kết quả chạy thật; dòng cuối về thời gian thuộc [Có thể khác nhau], vì phụ thuộc máy và tải [Đã chạy: Oracle 23.26.3]:

sql

SET FEEDBACK OFF
DECLARE
  TYPE t_num IS TABLE OF NUMBER INDEX BY PLS_INTEGER;
  v_nums   t_num;
  v_start  NUMBER;
  v_loop   NUMBER;
  v_bulk   NUMBER;
  v_set    NUMBER;
  v_rows   NUMBER;
BEGIN
  v_start := DBMS_UTILITY.GET_TIME;
  FOR i IN 1..50000 LOOP
    INSERT INTO stock_log VALUES (i, MOD(i, 50));
  END LOOP;
  v_loop := (DBMS_UTILITY.GET_TIME - v_start) * 10;
  SELECT COUNT(*) INTO v_rows FROM stock_log;
  DBMS_OUTPUT.PUT_LINE('loop inserted ' || v_rows || ' rows');
  ROLLBACK;

  FOR i IN 1..50000 LOOP v_nums(i) := i; END LOOP;
  v_start := DBMS_UTILITY.GET_TIME;
  FORALL i IN 1..50000
    INSERT INTO stock_log VALUES (v_nums(i), MOD(v_nums(i), 50));
  v_bulk := (DBMS_UTILITY.GET_TIME - v_start) * 10;
  SELECT COUNT(*) INTO v_rows FROM stock_log;
  DBMS_OUTPUT.PUT_LINE('forall inserted ' || v_rows || ' rows');
  ROLLBACK;

  v_start := DBMS_UTILITY.GET_TIME;
  INSERT INTO stock_log SELECT LEVEL, MOD(LEVEL, 50) FROM dual CONNECT BY LEVEL <= 50000;
  v_set := (DBMS_UTILITY.GET_TIME - v_start) * 10;
  SELECT COUNT(*) INTO v_rows FROM stock_log;
  DBMS_OUTPUT.PUT_LINE('one INSERT SELECT inserted ' || v_rows || ' rows');
  ROLLBACK;

  DBMS_OUTPUT.PUT_LINE('ms: loop ' || v_loop || ', forall ' || v_bulk || ', one statement ' || v_set);
END;
/

text

loop inserted 50000 rows
forall inserted 50000 rows
one INSERT SELECT inserted 50000 rows
ms: loop 1320, forall 30, one statement 50

Số dòng được chèn là như nhau ở cả ba cách, còn thời gian thì khác nhau rõ rệt. FORALL là mức nhắc ngắn của chủ đề BULK COLLECT và FORALL: nó gom nhiều lượt vào một lần chuyển ngữ cảnh khi bạn buộc phải giữ logic từng dòng.

Giới hạn và tài liệu

Giới hạn của bài. Bài chưa nói về CURSOR tường minh với OPEN và FETCH, về BULK COLLECT chi tiết, hay về FOR UPDATE.
Đọc thêm. Oracle Database PL/SQL Language Reference, mục "FOR LOOP Statement": https://docs.oracle.com/en/database/oracle/oracle-database/23/lnpls/plsql-control-statements.html

Bài tiếp theo

Bài sau là vòng WHILE: điều kiện được kiểm tra trước mỗi lượt nên thân vòng có thể chạy 0 lần, và điều kiện NULL bỏ qua cả vòng mà không báo lỗi. Mini-dự án nối tiếp: đóng sách vào các thùng 4 cuốn, với số thùng phụ thuộc vào tồn kho.

Lời giải và đáp án

Làm cùng mình, Bước 2. Khối hoàn chỉnh:

sql

FOR r IN (SELECT title, stock FROM books WHERE stock < 6 ORDER BY id) LOOP
  DBMS_OUTPUT.PUT_LINE('low: ' || r.title || ' (' || r.stock || ')');
END LOOP;

sql

SET FEEDBACK OFF
BEGIN
  FOR r IN (SELECT title, stock FROM books WHERE stock < 6 ORDER BY id) LOOP
    DBMS_OUTPUT.PUT_LINE('low: ' || r.title || ' (' || r.stock || ')');
  END LOOP;
END;
/

text

low: PL/SQL Intro (0)
low: Data Modeling (5)

PL/SQL Intro (0 cuốn) và Data Modeling (5 cuốn) có dưới 6 cuốn. SQL Basics có 10 cuốn nên không xuất hiện.

Làm cùng mình, Bước 3. Với WHERE stock > 1000:

sql

SET FEEDBACK OFF
BEGIN
  FOR r IN (SELECT title, stock FROM books WHERE stock > 1000 ORDER BY id) LOOP
    DBMS_OUTPUT.PUT_LINE('low: ' || r.title || ' (' || r.stock || ')');
  END LOOP;
  DBMS_OUTPUT.PUT_LINE('check finished');
END;
/

text

check finished

Vòng không chạy lần nào, không có lỗi, và dòng sau END LOOP vẫn chạy. Vòng FOR duyệt bảng không cần EXIT vì hết dòng thì vòng tự dừng, và không cần OPEN hay FETCH vì Oracle tự mở, đọc, đóng cursor.

Bài 1.

sql

SET FEEDBACK OFF
DECLARE
  v_s VARCHAR2(40);
BEGIN
  FOR i IN 1..3 LOOP v_s := v_s || i || ' '; END LOOP;
  DBMS_OUTPUT.PUT_LINE('FOR 1..3: ' || NVL(v_s, '(none)'));
  v_s := NULL;
  FOR i IN REVERSE 1..3 LOOP v_s := v_s || i || ' '; END LOOP;
  DBMS_OUTPUT.PUT_LINE('FOR REVERSE 1..3: ' || NVL(v_s, '(none)'));
  v_s := NULL;
  FOR i IN 3..1 LOOP v_s := v_s || i || ' '; END LOOP;
  DBMS_OUTPUT.PUT_LINE('FOR 3..1: ' || NVL(v_s, '(none)'));
  v_s := NULL;
  FOR i IN REVERSE 3..1 LOOP v_s := v_s || i || ' '; END LOOP;
  DBMS_OUTPUT.PUT_LINE('FOR REVERSE 3..1: ' || NVL(v_s, '(none)'));
END;
/

text

FOR 1..3: 1 2 3
FOR REVERSE 1..3: 3 2 1
FOR 3..1: (none)
FOR REVERSE 3..1: (none)

Cách viết	Các giá trị của i
`FOR i IN 1..3`	1 2 3
`FOR i IN REVERSE 1..3`	3 2 1
`FOR i IN 3..1`	không có
`FOR i IN REVERSE 3..1`	không có

Bài 2.

sql

SET FEEDBACK OFF
DECLARE
  v_total NUMBER := 0;
BEGIN
  FOR r IN (SELECT price, stock FROM books) LOOP
    v_total := v_total + r.price * r.stock;
  END LOOP;
  DBMS_OUTPUT.PUT_LINE('inventory value: ' || v_total);
END;
/

text

inventory value: 1950000

Tổng là 1200000 cộng 0 cộng 750000, bằng 1950000, cùng con số với câu SUM.

Bài 3.

sql

SET FEEDBACK OFF
DECLARE
  v_loop NUMBER;
  v_set  NUMBER;
BEGIN
  FOR r IN (SELECT id, price FROM books WHERE stock > 0) LOOP
    UPDATE books SET price = r.price * 1.1 WHERE id = r.id;
  END LOOP;
  SELECT SUM(price) INTO v_loop FROM books;
  ROLLBACK;
  UPDATE books SET price = price * 1.1 WHERE stock > 0;
  SELECT SUM(price) INTO v_set FROM books;
  ROLLBACK;
  DBMS_OUTPUT.PUT_LINE('loop total: ' || v_loop || ', set total: ' || v_set);
END;
/

text

loop total: 477000, set total: 477000

Hai tổng đều là 477000: sách 1 thành 132000, sách 3 thành 165000, sách 2 hết hàng giữ 180000. Một câu UPDATE cho cùng kết quả mà chỉ chuyển ngữ cảnh một lần, nên nên dùng nó. Vòng chỉ cần khi mỗi dòng cần logic riêng mà một câu lệnh không diễn tả được.

Tự kiểm tra.

Không cần khai báo ở DECLARE; biến được tạo ngầm và chỉ sống trong thân vòng.
3, 2, 1.
Không đổi, vì hai cận chỉ được tính một lần trước khi vòng bắt đầu.
Một dòng có NULL làm tổng thành NULL rồi lan ra mọi phép cộng sau. Sửa bằng NVL(cột, 0) hoặc dùng SUM, hàm bỏ qua dòng NULL.
Khi mỗi dòng cần logic riêng mà một câu lệnh không diễn tả được, như gọi thủ tục có tác dụng phụ hoặc ghi nhật ký từng dòng.

Tình huống mở đầu

Mục tiêu bài học

Bức tranh lớn

Khái niệm cốt lõi

1. FOR số: biến ngầm, cận một lần

2. REVERSE và BY

3. Vòng FOR duyệt kết quả SELECT

4. Vòng từng dòng và câu lệnh tập hợp

Ví dụ đầu tiên

Xem từng bước

Ví dụ thứ hai: duyệt từng dòng của bảng

Concept Check

Làm cùng mình

Bước 1. Đoán trên giấy

Bước 2. Hoàn thành khối

Bước 3. Thử với 0 dòng

Bước 4. Ghi lại

Thử phá nó đi

Tự tay làm

Cơ bản

Vận dụng

Thử thách (tùy chọn)

Tự kiểm tra

Điều cần nhớ

Đào sâu

Vòng từng dòng và chuyển ngữ cảnh

Giới hạn và tài liệu

Bài tiếp theo

Lời giải và đáp án

1. `FOR` số: biến ngầm, cận một lần

2. `REVERSE` và `BY`

3. Vòng `FOR` duyệt kết quả `SELECT`