SELECT INTO trong PL/SQL

Xem thử

Tình huống mở đầu

Nhà Sách Ông Đồ có 6 đầu sách trong bảng books. Anh Tí giao hàng đứng ở quầy hỏi: "Cuốn mã 1 giá bao nhiêu em?". Khối PL/SQL của bạn cần đưa đúng một con số vào một biến để tính tiếp. Nếu là mã 1 thì dễ. Nhưng chiều nay có người hỏi mã 99, và có người hỏi "sách nhóm db giá bao nhiêu", trong khi nhóm db có tới 3 cuốn.

Hãy đoán trước hai điều. Một, khi mã 99 không có trong bảng, biến nhận giá trị gì: NULL, 0, hay khối dừng với lỗi? Hai, khi điều kiện khớp 3 cuốn, Oracle lấy cuốn đầu tiên hay báo lỗi?

Ôn nhanh bài trước: biến v_book books%ROWTYPE chứa gì?

Đáp án: một record có các trường trùng tên và kiểu với từng cột của bảng books, nên v_book.title là tên sách. Bài này dùng nó để nhận cả hàng.

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

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

Viết SELECT ... INTO đưa một hàng vào biến hoặc vào record %ROWTYPE.
Phân biệt ORA-01403 (không dòng nào) với ORA-01422 (từ hai dòng trở lên) và bắt từng lỗi đúng chỗ.
Giải thích vì sao SELECT INTO với hàm tổng hợp không bao giờ rỗng, còn khi có GROUP BY thì có thể rỗng.
Tránh bẫy biến PL/SQL trùng tên với cột của bảng.

Bức tranh lớn

Xuyên suốt bài, SELECT INTO là người thủ kho được dặn: "ra kệ lấy đúng một cuốn mang về". Kệ có đúng một cuốn thì mang về. Kệ trống thì quay lại báo "không có". Kệ có nhiều cuốn khớp thì quay lại báo "có nhiều quá, em không biết lấy cuốn nào". Anh thủ kho không bao giờ tự chọn hộ bạn, và cũng không bao giờ về tay không mà im lặng. Dự án xuyên suốt cả chương 37 là kho của Nhà Sách Ông Đồ, chạy trên Oracle AI Database 26ai Free (release 23.26.3).

Bạn đã học SELECT ở phần PostgreSQL và PL/pgSQL. Điểm khác lớn nhất là tính nghiêm khắc: theo tài liệu PostgreSQL, SELECT INTO trong PL/pgSQL chỉ nghiêm khắc khi bạn viết thêm STRICT; không có nó thì không dòng nào cho NULL và nhiều dòng thì lấy dòng đầu [Suy luận, từ tài liệu PostgreSQL, bài này không chạy PostgreSQL]. Oracle thì luôn nghiêm khắc, không có tùy chọn nào để nới.

Hình dưới trả lời một câu hỏi: trước khi viết SELECT INTO, bạn nên hỏi mình những gì để chọn đúng công cụ?

Đang tải sơ đồ…

Cách đọc hình: đọc từ ô xanh dương xuống. Ô cam đầu tiên hỏi truy vấn có thể trả nhiều dòng không: có thì đi ô tím bên phải, dùng vòng lặp qua con trỏ hoặc BULK COLLECT, không có thì xuống ô cam thứ hai. Ô cam thứ hai hỏi có thể rỗng không: có thì cần khối con có bộ xử lý ngoại lệ, không thì ra ô xanh lá là SELECT INTO thẳng. Tra theo khóa chính không bao giờ nhiều dòng, nhưng vẫn có thể rỗng; hàm tổng hợp thì không bao giờ rỗng.

Chỗ ẩn dụ không còn đúng: người thủ kho thật có thể nhìn kệ rồi tự hỏi lại bạn. Oracle thì không hỏi lại; nó dừng khối bằng một lỗi và chờ bạn xử lý.

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

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

1. Đúng một dòng, nếu không là lỗi

SELECT cột INTO biến FROM ... WHERE ... yêu cầu truy vấn trả đúng một dòng. Không có dòng nào thì Oracle ném NO_DATA_FOUND (ORA-01403). Có từ hai dòng trở lên thì ném TOO_MANY_ROWS (ORA-01422). Hai ngoại lệ này là tên có sẵn nên bạn bắt trực tiếp bằng WHEN NO_DATA_FOUND THEN và WHEN TOO_MANY_ROWS THEN. Một chi tiết hay bị nhầm: SQLCODE của NO_DATA_FOUND là 100 theo chuẩn ANSI, không phải -1403, còn TOO_MANY_ROWS là -1422.

2. Hàm tổng hợp luôn trả một dòng, trừ khi có `GROUP BY`

SELECT COUNT(*), SUM(...) INTO ... FROM ... WHERE ... luôn trả đúng một dòng, kể cả khi WHERE không khớp gì: COUNT cho 0 còn SUM, MAX cho NULL. Vì thế câu này không bao giờ ném NO_DATA_FOUND. Nhưng khi thêm GROUP BY, tập rỗng cho ra không nhóm nào, tức là không dòng nào, và NO_DATA_FOUND quay lại.

3. Nhiều biến, hoặc một record `%ROWTYPE`

Danh sách sau INTO khớp với danh sách cột theo thứ tự và theo số lượng. Thiếu hoặc thừa là lỗi biên dịch, sai kiểu là lỗi lúc chạy. Với SELECT *, khai báo biến %ROWTYPE để một record nhận cả hàng, ít sai sót hơn khi bảng đổi cột.

4. Biến trùng tên với cột: tên cột thắng

Trong một câu SQL nằm trong khối PL/SQL, nếu một tên vừa là cột vừa là biến thì Oracle hiểu là cột. Khi đó WHERE id = id luôn đúng với mọi dòng có id khác NULL. Cách chữa là đặt tên biến có tiền tố như p_ và v_, hoặc gắn nhãn khối <<lookup>> rồi viết lookup.id.

Ví dụ đầu tiên

Lấy tên và giá của sách mã 1 vào hai biến.

sql

SET FEEDBACK OFF
DECLARE
  v_title books.title%TYPE;
  v_price books.price%TYPE;
BEGIN
  SELECT title, price INTO v_title, v_price FROM books WHERE id = 1;
  DBMS_OUTPUT.PUT_LINE(v_title || ' costs ' || v_price);
END;
/

Dự đoán trước khi xem kết quả: khối in ra dòng nào?

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

text

SQL Basics costs 120000

Dòng code	Máy làm gì	Vì sao viết như vậy
`v_title books.title%TYPE;`	Khai báo biến có cùng kiểu với cột `title`	Cột đổi kiểu thì biến đổi theo, không phải sửa tay
`SELECT title, price INTO v_title, v_price`	Chọn hai cột và đặt vào hai biến theo đúng thứ tự	Số biến phải bằng số cột, kiểu phải tương thích
`FROM books WHERE id = 1;`	Điều kiện trên khóa chính nên tối đa một dòng	Khóa chính là cách chắc nhất để không gặp `TOO_MANY_ROWS`
`DBMS_OUTPUT.PUT_LINE(...)`	In ra một dòng chữ	Cách đơn giản để thấy giá trị biến khi chạy bằng sqlplus

Giờ đổi điều kiện. Mã 99 không có trong bảng [Đã chạy: Oracle 23.26.3]:

sql

SET FEEDBACK OFF
DECLARE
  v_title books.title%TYPE;
BEGIN
  SELECT title INTO v_title FROM books WHERE id = 99;
  DBMS_OUTPUT.PUT_LINE('never printed: ' || v_title);
END;
/

text

DECLARE
*
ERROR at line 1:
ORA-01403: no data found
ORA-06512: at line 4
Help: https://docs.oracle.com/error-help/db/ora-01403/

Khối dừng ở dòng 4, lệnh in phía sau không chạy. Dòng Help: ở cuối là phần Oracle 23.26.3 thêm vào thông báo lỗi. Còn nhóm db có 3 cuốn [Đã chạy: Oracle 23.26.3]:

sql

SET FEEDBACK OFF
DECLARE
  v_title books.title%TYPE;
BEGIN
  SELECT title INTO v_title FROM books WHERE category = 'db';
  DBMS_OUTPUT.PUT_LINE('never printed: ' || v_title);
END;
/

text

DECLARE
*
ERROR at line 1:
ORA-01422: exact fetch returned more than the requested number of rows (1)
Help: https://docs.oracle.com/error-help/db/ora-01422/

Oracle không chọn giúp cuốn đầu tiên. Cách xử lý đúng: bắt hai ngoại lệ trong khối con để khối cha chạy tiếp [Đã chạy: Oracle 23.26.3]:

sql

SET FEEDBACK OFF
DECLARE
  v_title books.title%TYPE;
BEGIN
  BEGIN
    SELECT title INTO v_title FROM books WHERE id = 99;
  EXCEPTION
    WHEN NO_DATA_FOUND THEN
      DBMS_OUTPUT.PUT_LINE('no rows: SQLCODE ' || SQLCODE);   -- mã chuẩn ANSI là 100, không phải -1403
  END;
  BEGIN
    SELECT title INTO v_title FROM books WHERE category = 'db';
  EXCEPTION
    WHEN TOO_MANY_ROWS THEN
      DBMS_OUTPUT.PUT_LINE('many rows: SQLCODE ' || SQLCODE || ' / ' || SQLERRM);
  END;
END;
/

text

no rows: SQLCODE 100
many rows: SQLCODE -1422 / ORA-01422: exact fetch returned more than the requested number of rows

Xem từng bước

Animation dưới đây cho thấy Oracle thử lấy dòng thứ nhất rồi dòng thứ hai khi chạy SELECT INTO. Bấm nút đổi cảnh để so sánh điều kiện id = 99 với category = 'db'. Hãy trả lời câu dự đoán trước khi bấm bước đầu tiên.

Đang tải Animation…

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

Điều kiện	Số dòng khớp	Ngoại lệ	`SQLCODE`	Biến sau câu lệnh
`id = 1`	1	không có	0	nhận giá trị của dòng
`id = 99`	0	`NO_DATA_FOUND`	100	không đổi
`category = 'db'`	3	`TOO_MANY_ROWS`	-1422	không đáng tin cậy

Hàm tổng hợp và `GROUP BY`

Quầy cần biết tổng tồn kho của nhóm không tồn tại. COUNT, SUM và MAX trong cùng một SELECT INTO luôn có một dòng kết quả [Đã chạy: Oracle 23.26.3]:

sql

SET FEEDBACK OFF
DECLARE
  v_count NUMBER;
  v_sum   NUMBER;
  v_max   NUMBER;
BEGIN
  SELECT COUNT(*), SUM(stock), MAX(price) INTO v_count, v_sum, v_max
  FROM books WHERE category = 'none';          -- không có sách nào thuộc nhóm này
  DBMS_OUTPUT.PUT_LINE('count=' || v_count || ' sum=' || NVL(TO_CHAR(v_sum), 'NULL') || ' max=' || NVL(TO_CHAR(v_max), 'NULL'));
END;
/

text

count=0 sum=NULL max=NULL

COUNT cho 0, SUM và MAX cho NULL, và không có ngoại lệ nào. Thêm GROUP BY category vào đúng truy vấn ấy thì bức tranh đổi, vì tập rỗng không tạo ra nhóm nào [Đã chạy: Oracle 23.26.3]:

sql

SET FEEDBACK OFF
DECLARE
  v_sum NUMBER;
BEGIN
  SELECT SUM(stock) INTO v_sum FROM books WHERE category = 'none' GROUP BY category;
  DBMS_OUTPUT.PUT_LINE('never printed');
EXCEPTION
  WHEN NO_DATA_FOUND THEN
    DBMS_OUTPUT.PUT_LINE('GROUP BY on an empty set returns no row at all');
END;
/

text

GROUP BY on an empty set returns no row at all

Nhận cả hàng bằng `%ROWTYPE`

sql

SET FEEDBACK OFF
DECLARE
  v_book books%ROWTYPE;
BEGIN
  SELECT * INTO v_book FROM books WHERE id = 3;     -- một record nhận cả hàng
  DBMS_OUTPUT.PUT_LINE(v_book.title || ' / ' || v_book.category || ' / stock ' || v_book.stock);
END;
/

text

Data Modeling / db / stock 5

Một record nhận cả 5 cột và bạn đọc từng trường bằng dấu chấm. Khi bảng thêm cột, SELECT * kèm %ROWTYPE vẫn khớp, còn danh sách biến rời thì phải sửa tay.

Bẫy trùng tên

Hình dưới trả lời một câu hỏi: khi id là cả biến lẫn cột, Oracle hiểu id là cái nào, và bạn chữa thế nào?

Đang tải sơ đồ…

Cách đọc hình: ô cam hỏi bảng có cột tên id không. Có thì Oracle dùng cột và lặng lẽ bỏ qua biến (ô đỏ). Muốn dùng biến thì đi xuống ô xanh lá, ghi rõ bằng nhãn khối hoặc đổi tên biến. Ô tím cho trường hợp bảng không có cột đó nên tên biến được tìm thấy bình thường.

Chạy thật với biến id có giá trị 2 [Đã chạy: Oracle 23.26.3]:

sql

SET FEEDBACK OFF
DECLARE
  id      NUMBER := 2;                 -- biến trùng tên với cột id
  v_title books.title%TYPE;
BEGIN
  SELECT title INTO v_title FROM books WHERE id = id;
  DBMS_OUTPUT.PUT_LINE(v_title);
END;
/

text

DECLARE
*
ERROR at line 1:
ORA-01422: exact fetch returned more than the requested number of rows (1)
Help: https://docs.oracle.com/error-help/db/ora-01422/

WHERE id = id được hiểu là cột so với cột, nên mọi dòng đều khớp và Oracle báo TOO_MANY_ROWS. Gắn nhãn <<lookup>> cho khối rồi viết lookup.id cho Oracle biết đó là biến [Đã chạy: Oracle 23.26.3]:

sql

SET FEEDBACK OFF
<<lookup>>
DECLARE
  id      NUMBER := 2;
  v_title books.title%TYPE;
BEGIN
  SELECT title INTO v_title FROM books WHERE id = lookup.id;   -- nhãn khối chỉ rõ đây là biến
  DBMS_OUTPUT.PUT_LINE(v_title);
END;
/

text

PL/SQL Intro

Bạn vừa làm được: dự đoán đúng hai kiểu lỗi của SELECT INTO, phân biệt hàm tổng hợp với truy vấn nhóm, và nhận ra bẫy biến trùng tên cột.

Concept Check

Câu 1. SELECT stock INTO v_stock FROM books WHERE id = 99; chạy trong khối không có EXCEPTION. Chuyện gì xảy ra?

A. v_stock nhận NULL và khối chạy tiếp, như PL/pgSQL không có STRICT.
B. v_stock nhận 0 vì cột stock có giá trị mặc định là 0.
C. Khối dừng với ORA-01403 và v_stock không được gán.
D. Khối dừng với ORA-01422 vì không có dòng nào để chọn.

Câu 2. SELECT COUNT(*) INTO v_n FROM books WHERE id = 99; cho v_n bằng bao nhiêu?

A. 0, và khối chạy tiếp bình thường vì hàm tổng hợp luôn có một dòng.
B. NULL, vì không dòng nào khớp để đếm.
C. Khối dừng với ORA-01403, giống truy vấn thường.
D. Khối dừng với ORA-01422 vì kết quả đếm không xác định.

Câu 3. Biến p_title tên khác mọi cột. Điều kiện WHERE title = p_title dùng giá trị nào cho title?

A. Giá trị của biến, vì biến được tìm trước cột.
B. Giá trị của cột title, và p_title là biến được so với nó.
C. Cả hai đều là cột, vì Oracle bỏ qua mọi biến trong WHERE.
D. Lỗi biên dịch, vì không so sánh được cột với biến.

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

Câu 1: C. A sai vì Oracle không có chế độ nới lỏng như vậy. B sai vì mặc định chỉ áp dụng khi chèn. D sai vì mã ORA-01422 dành cho quá nhiều dòng.
Câu 2: A. B sai vì COUNT không cho NULL. C và D sai vì hàm tổng hợp không có GROUP BY luôn trả đúng một dòng.
Câu 3: B. A sai vì tên cột được ưu tiên nhưng chỉ khi trùng tên. Ở đây tên khác nhau nên title là cột còn p_title là biến. C và D không có thật trong Oracle.

Làm cùng mình

Quầy muốn in giá của hai cuốn: mã 3 (có trong bảng) và mã 42 (không có). Khối phải chạy hết cả hai lượt mà không dừng giữa chừng.

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

Mã 42 không có. Nếu bạn không bắt ngoại lệ thì lượt thứ hai làm gì? Ghi dự đoán trước khi viết code.

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

sql

SET FEEDBACK OFF
DECLARE
  v_price books.price%TYPE;
  v_id    NUMBER;
BEGIN
  FOR i IN 1 .. 2 LOOP
    v_id := CASE i WHEN 1 THEN 3 ELSE 42 END;
    BEGIN
      -- TODO 1: lấy price của sách v_id vào v_price
      DBMS_OUTPUT.PUT_LINE('book ' || v_id || ' costs ' || v_price);
    EXCEPTION
      -- TODO 2: bắt trường hợp không có dòng nào và in 'book ... is missing'
    END;
  END LOOP;
END;
/

Gợi ý: bộ xử lý ngoại lệ phải nằm trong khối con bên trong vòng lặp. Nếu đặt ở cuối khối ngoài, vòng lặp đã bị cắt trước khi kịp sang lượt kế.

Chạy xong bạn sẽ thấy [Đã chạy] hai dòng, một dòng giá và một dòng is missing. Đáp án ở mục Lời giải và đáp án.

Bước 3. Thử đổi điều kiện

Đổi điều kiện thành WHERE category = 'db'. Dự đoán trước: khối còn in is missing không, và lượt nào gây lỗi gì?

Bước 4. Ghi lại

Viết một câu: vì sao khối con quan trọng khi bạn dùng SELECT INTO trong vòng lặp.

Thử phá nó đi

Sáu lỗi hay gặp với SELECT INTO [Đã 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
Điều kiện không khớp dòng nào	`ORA-01403: no data found`	`SELECT INTO` đòi đúng một dòng	Bắt `NO_DATA_FOUND` trong khối con
Điều kiện khớp nhiều dòng	`ORA-01422: exact fetch returned more than the requested number of rows`	Không có dòng nào được chọn hộ	Thêm khóa, hoặc `ORDER BY` với `FETCH FIRST 1 ROW ONLY`
Thiếu biến sau `INTO`	`ORA-00947: not enough values`	Số biến ít hơn số cột	Khớp số biến với số cột
Biến quá ngắn	`ORA-06502: character string buffer too small`	`VARCHAR2(5)` không chứa nổi `SQL Basics`	Dùng `%TYPE` của cột
Quên `INTO`	`PLS-00428: an INTO clause is expected in this SELECT statement`	`SELECT` trong PL/SQL cần nơi nhận dữ liệu	Thêm `INTO`
Biến trùng tên cột	Không báo lỗi gì, kết quả sai	Tên cột thắng tên biến	Tiền tố `p_` hoặc nhãn khối

Thiếu biến:

sql

SET FEEDBACK OFF
DECLARE
  v_title books.title%TYPE;
BEGIN
  SELECT stock, title INTO v_title FROM books WHERE id = 1;
END;
/

text

SELECT stock, title INTO v_title FROM books WHERE id = 1;
                                   *
ERROR at line 4:
ORA-06550: line 4, column 36:
PL/SQL: ORA-00947: not enough values
ORA-06550: line 4, column 3:
PL/SQL: SQL Statement ignored
Help: https://docs.oracle.com/error-help/db/ora-06550/

Sai kiểu: nhận chuỗi SQL Basics vào biến số, Oracle cố đổi ngầm sang số và thất bại với ORA-06502 kiểu character to number conversion error:

sql

SET FEEDBACK OFF
DECLARE
  v_number NUMBER;
BEGIN
  SELECT title INTO v_number FROM books WHERE id = 1;
END;
/

text

DECLARE
*
ERROR at line 1:
ORA-06502: PL/SQL: value or conversion error: character to number conversion error
ORA-06512: at line 4
Help: https://docs.oracle.com/error-help/db/ora-06502/

Biến quá ngắn:

sql

SET FEEDBACK OFF
DECLARE
  v_short VARCHAR2(5);
BEGIN
  SELECT title INTO v_short FROM books WHERE id = 1;
END;
/

text

DECLARE
*
ERROR at line 1:
ORA-06502: PL/SQL: value or conversion error: character string buffer too small
ORA-06512: at line 4
Help: https://docs.oracle.com/error-help/db/ora-06502/

Quên INTO:

sql

SET FEEDBACK OFF
BEGIN
  SELECT title FROM books WHERE id = 1;
END;
/

text

SELECT title FROM books WHERE id = 1;
  *
ERROR at line 2:
ORA-06550: line 2, column 3:
PLS-00428: an INTO clause is expected in this SELECT statement
Help: https://docs.oracle.com/error-help/db/ora-06550/

Lỗi nguy hiểm nhất là lỗi không có thông báo. Quầy muốn đếm sách nhóm code bằng biến cùng tên cột category [Đã chạy: Oracle 23.26.3]:

sql

SET FEEDBACK OFF
DECLARE
  category VARCHAR2(20) := 'code';     -- muốn đếm sách nhóm code
  v_n      NUMBER;
BEGIN
  SELECT COUNT(*) INTO v_n FROM books WHERE category = category;
  DBMS_OUTPUT.PUT_LINE('count = ' || v_n);
END;
/

text

count = 5

Kết quả 5 là sai: nhóm code chỉ có 2 cuốn. Điều kiện category = category đúng với mọi dòng có category khác NULL, tức 5 trong 6 sách (cuốn 100% Pure SQL chưa có nhóm nên bị loại vì so sánh với NULL). Không có lỗi nào, chỉ có một con số trông hợp lý. Đổi tên biến thì kết quả đúng [Đã chạy: Oracle 23.26.3]:

sql

SET FEEDBACK OFF
DECLARE
  p_category VARCHAR2(20) := 'code';   -- tiền tố p_ cho tham số hoặc biến đầu vào
  v_n        NUMBER;
BEGIN
  SELECT COUNT(*) INTO v_n FROM books WHERE category = p_category;
  DBMS_OUTPUT.PUT_LINE('count = ' || v_n);
END;
/

text

count = 2

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. Dự đoán hai dòng in. Đầu vào: khối dưới đây trên bảng books mẫu.

sql

DECLARE
  v_a NUMBER;
  v_b NUMBER;
BEGIN
  SELECT MAX(stock), MIN(stock) INTO v_a, v_b FROM books WHERE category = 'code';
  DBMS_OUTPUT.PUT_LINE('max ' || v_a || ' min ' || v_b);
  SELECT SUM(stock) INTO v_a FROM books WHERE price > 1000000;
  DBMS_OUTPUT.PUT_LINE('sum is ' || NVL(TO_CHAR(v_a), 'NULL'));
END;
/

Tiêu chí đạt: viết đúng hai dòng in ra, và nói được vì sao khối không ném NO_DATA_FOUND ở câu thứ hai. Gợi ý: bảng có hai sách nhóm code, tồn kho 8 và 3; không sách nào giá trên một triệu.

Vận dụng

Bài 2. Sách rẻ nhất có giá. Đầu vào: bảng books, trong đó sách 100% Pure SQL chưa có giá. Lấy cả hàng của cuốn rẻ nhất trong số sách đã có giá vào một biến %ROWTYPE và in cheapest: <tên> at <giá>. Tiêu chí đạt: khối không ném TOO_MANY_ROWS dù có hai cuốn cùng nhóm, và không chọn nhầm cuốn chưa có giá. Gợi ý: cần ORDER BY và một cách giới hạn còn đúng một dòng.

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

Bài 3. Sửa bẫy trùng tên. Đầu vào: khối collide_quiet ở mục Thử phá nó đi, giữ nguyên tên biến category. Sửa để count = 2 mà không đổi tên biến. Tiêu chí đạt: in đúng count = 2. Gợi ý: cần một nhãn khối và cách viết tên đầy đủ cho biến, còn cột nên được gọi qua bí danh bảng.

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) SELECT INTO hợp lệ khi truy vấn trả bao nhiêu dòng?
(Mục tiêu 2, giải thích) Vì sao ORA-01403 và ORA-01422 cần hai bộ xử lý ngoại lệ khác nhau?
(Mục tiêu 3, dự đoán) SELECT SUM(stock) INTO v FROM books WHERE 1 = 0 cho v gì, và nếu thêm GROUP BY category thì sao?
(Mục tiêu 4, sửa lỗi) WHERE id = id trả sai kết quả mà không báo lỗi. Hai cách sửa là gì?
(Mục tiêu 1, vận dụng) Khi nào bạn chọn BULK COLLECT thay vì SELECT INTO?

Điều cần nhớ

SELECT INTO đòi đúng một dòng: không dòng nào là ORA-01403, từ hai dòng là ORA-01422.
SQLCODE của NO_DATA_FOUND là 100, của TOO_MANY_ROWS là -1422.
Hàm tổng hợp không có GROUP BY luôn trả một dòng, nên không ném NO_DATA_FOUND; có GROUP BY thì có thể rỗng.
Số biến sau INTO phải khớp số cột; %ROWTYPE giúp nhận cả hàng.
Tên cột thắng tên biến trong câu SQL: đặt tiền tố p_ hoặc dùng nhãn khối.

Cheat sheet:

Muốn	Viết	Ghi chú
Lấy một giá trị	`SELECT c INTO v FROM t WHERE id = 1;`	Điều kiện trên khóa chính
Lấy cả hàng	`SELECT * INTO r FROM t WHERE id = 1;`	`r t%ROWTYPE`
Cho phép không có dòng	Khối con với `EXCEPTION WHEN NO_DATA_FOUND`	Đặt trong vòng lặp thì bắt buộc khối con
Chọn một dòng trong nhiều	`ORDER BY ... FETCH FIRST 1 ROW ONLY`	Phải có `ORDER BY` mới chọn được rõ ràng
Lấy nhiều dòng	`SELECT ... BULK COLLECT INTO tập`	Rỗng thì `COUNT` bằng 0, không có lỗi
Tránh trùng tên	`p_category` hoặc `<<nhãn>>` và `nhãn.biến`	Cột luôn thắng biến

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

Câu hỏi để dành cho ngày mai: nếu muốn quầy ghi thêm một cuốn sách mới vào bảng và biết ngay mã mà Oracle vừa cấp cho nó, bạn sẽ lấy mã đó bằng cách nào mà không phải SELECT lại?

Đào sâu

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

Cơ chế bên trong. SELECT INTO thực chất là một lần lấy dòng có kiểm tra thêm: Oracle thử lấy tối đa hai dòng để biết có đúng một hay không. Dấu vết còn lại là SQL%ROWCOUNT [Đã chạy: Oracle 23.26.3]:

sql

SET FEEDBACK OFF
DECLARE
  v_title books.title%TYPE;
BEGIN
  SELECT title INTO v_title FROM books WHERE id = 1;
  DBMS_OUTPUT.PUT_LINE('after one row: ' || SQL%ROWCOUNT);
  BEGIN
    SELECT title INTO v_title FROM books WHERE id = 99;
  EXCEPTION
    WHEN NO_DATA_FOUND THEN
      DBMS_OUTPUT.PUT_LINE('after no row: ' || SQL%ROWCOUNT);
  END;
  BEGIN
    SELECT title INTO v_title FROM books WHERE category = 'db';
  EXCEPTION
    WHEN TOO_MANY_ROWS THEN
      DBMS_OUTPUT.PUT_LINE('after many rows: ' || SQL%ROWCOUNT);
  END;
END;
/

text

after one row: 1
after no row: 0
after many rows: 1

Sau khi lỗi TOO_MANY_ROWS, SQL%ROWCOUNT vẫn là 1 chứ không phải 3, vì Oracle dừng ngay khi thấy dòng thứ hai. Đừng dựa vào nó để đếm số dòng khớp.

Chọn một dòng có chủ đích. Khi nhiều dòng là chuyện bình thường nhưng bạn chỉ cần một, thêm ORDER BY để có tiêu chí và FETCH FIRST 1 ROW ONLY để giới hạn. Câu này lấy cuốn nhóm db đắt nhất [Đã chạy: Oracle 23.26.3]:

sql

SET FEEDBACK OFF
DECLARE
  v_title books.title%TYPE;
BEGIN
  SELECT title INTO v_title
  FROM books
  WHERE category = 'db'
  ORDER BY price DESC
  FETCH FIRST 1 ROW ONLY;                       -- chọn rõ cuốn nào, và chỉ một cuốn
  DBMS_OUTPUT.PUT_LINE('most expensive db book: ' || v_title);
END;
/

text

most expensive db book: PL/SQL Intro

Nhiều dòng thì dùng BULK COLLECT. Nó nạp cả tập kết quả vào một collection và không bao giờ ném NO_DATA_FOUND hay TOO_MANY_ROWS; tập rỗng chỉ có COUNT bằng 0 [Đã chạy: Oracle 23.26.3]:

sql

SET FEEDBACK OFF
DECLARE
  TYPE t_titles IS TABLE OF books.title%TYPE;
  v_titles t_titles;
BEGIN
  SELECT title BULK COLLECT INTO v_titles
  FROM books WHERE category = 'db' ORDER BY id;
  DBMS_OUTPUT.PUT_LINE('collected ' || v_titles.COUNT);
  FOR i IN 1 .. v_titles.COUNT LOOP
    DBMS_OUTPUT.PUT_LINE(i || ': ' || v_titles(i));
  END LOOP;
  SELECT title BULK COLLECT INTO v_titles FROM books WHERE category = 'none';
  DBMS_OUTPUT.PUT_LINE('empty result collected ' || v_titles.COUNT);
END;
/

text

collected 3
1: SQL Basics
2: PL/SQL Intro
3: Data Modeling
empty result collected 0

Với tập rất lớn, nạp cả vào bộ nhớ là không khôn ngoan; lúc đó cần LIMIT hoặc vòng lặp qua con trỏ, thuộc chương sau.

FOR UPDATE. Thêm FOR UPDATE vào cuối SELECT INTO để khóa hàng vừa đọc cho tới khi COMMIT hoặc ROLLBACK, tránh người khác sửa chen giữa lúc bạn đọc và lúc bạn ghi [Đã chạy: Oracle 23.26.3]:

sql

SET FEEDBACK OFF
DECLARE
  v_stock books.stock%TYPE;
BEGIN
  SELECT stock INTO v_stock FROM books WHERE id = 1 FOR UPDATE;   -- khóa hàng cho tới khi COMMIT hoặc ROLLBACK
  v_stock := v_stock - 1;
  UPDATE books SET stock = v_stock WHERE id = 1;
  DBMS_OUTPUT.PUT_LINE('book 1 now has ' || v_stock);
  ROLLBACK;
END;
/

text

book 1 now has 9

Cơ chế khóa được nói kỹ ở bài UPDATE.

Hiểu lầm nâng cao. Nhiều người nghĩ WHERE ROWNUM = 1 là cách chọn dòng đầu tiên theo ý mình. Nó chỉ lấy một dòng bất kỳ nên không thay được ORDER BY kèm FETCH FIRST; bài ORDER BY sẽ nói rõ cái bẫy này.
Giới hạn của bài. Bài chưa nói về con trỏ tường minh (OPEN, FETCH, CLOSE), SELECT INTO với collection nhiều tầng, hay truy vấn động bằng EXECUTE IMMEDIATE ... INTO.
Đọc thêm. Oracle Database PL/SQL Language Reference, mục về câu lệnh SELECT INTO, bắt đầu từ trang chủ tài liệu: https://docs.oracle.com/en/database/oracle/oracle-database/23/lnpls/

Bài tiếp theo

Bài sau là INSERT trong khối PL/SQL: ghi một dòng mới từ biến, lấy ngay mã Oracle vừa cấp bằng RETURNING INTO, chèn cả record bằng %ROWTYPE, và xử lý lỗi trùng khóa ORA-00001. Mini-dự án nối tiếp: nhập sách mới vào kho của Nhà Sách Ông Đồ và ghi sổ nhập hàng.

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

Làm cùng mình, Bước 2. Hai dòng cần điền:

sql

SELECT price INTO v_price FROM books WHERE id = v_id;
...
WHEN NO_DATA_FOUND THEN
  DBMS_OUTPUT.PUT_LINE('book ' || v_id || ' is missing');

sql

SET FEEDBACK OFF
DECLARE
  v_price books.price%TYPE;
  v_id    NUMBER;
BEGIN
  FOR i IN 1 .. 2 LOOP
    v_id := CASE i WHEN 1 THEN 3 ELSE 42 END;
    BEGIN
      SELECT price INTO v_price FROM books WHERE id = v_id;
      DBMS_OUTPUT.PUT_LINE('book ' || v_id || ' costs ' || v_price);
    EXCEPTION
      WHEN NO_DATA_FOUND THEN
        DBMS_OUTPUT.PUT_LINE('book ' || v_id || ' is missing');
    END;
  END LOOP;
END;
/

text

book 3 costs 150000
book 42 is missing

Lượt đầu in giá của sách 3, lượt hai gặp mã 42 không có nên bộ xử lý trong khối con chạy, rồi vòng lặp kết thúc bình thường. Nếu bạn đặt EXCEPTION ở khối ngoài, lượt hai ném lỗi và khối thoát luôn.

Làm cùng mình, Bước 3. Với category = 'db', cả hai lượt đều khớp 3 dòng nên cả hai ném ORA-01422, mà khối con chỉ bắt NO_DATA_FOUND, nên lỗi thoát ra ngoài và dừng khối ở lượt đầu. Khối con chỉ bắt đúng ngoại lệ bạn khai báo.

Ghi chú nhỏ: dùng COUNT(*) thay cho SELECT INTO trực tiếp là một cách khác để hỏi "có hay không" mà không cần bộ xử lý ngoại lệ [Đã chạy: Oracle 23.26.3]:

sql

SET FEEDBACK OFF
DECLARE
  v_n NUMBER;
BEGIN
  SELECT COUNT(*) INTO v_n FROM books WHERE id = 42;
  DBMS_OUTPUT.PUT_LINE('count of book 42 = ' || v_n);
END;
/

text

count of book 42 = 0

Bài 1.

sql

SET FEEDBACK OFF
DECLARE
  v_a NUMBER;
  v_b NUMBER;
BEGIN
  SELECT MAX(stock), MIN(stock) INTO v_a, v_b FROM books WHERE category = 'code';
  DBMS_OUTPUT.PUT_LINE('max ' || v_a || ' min ' || v_b);
  SELECT SUM(stock) INTO v_a FROM books WHERE price > 1000000;
  DBMS_OUTPUT.PUT_LINE('sum is ' || NVL(TO_CHAR(v_a), 'NULL'));
END;
/

text

max 8 min 3
sum is NULL

Hai sách nhóm code có tồn kho 8 và 3 nên MAX là 8 và MIN là 3. Không sách nào giá trên một triệu nên SUM là NULL và dòng thứ hai in sum is NULL; hàm tổng hợp không có GROUP BY luôn có một dòng nên không có ngoại lệ.

Bài 2.

sql

SET FEEDBACK OFF
DECLARE
  v_book books%ROWTYPE;
BEGIN
  SELECT * INTO v_book
  FROM books
  WHERE price IS NOT NULL
  ORDER BY price
  FETCH FIRST 1 ROW ONLY;
  DBMS_OUTPUT.PUT_LINE('cheapest: ' || v_book.title || ' at ' || v_book.price);
END;
/

text

cheapest: SQL Basics at 120000

Điều kiện price IS NOT NULL loại cuốn chưa có giá, vì NULL xếp cuối khi sắp tăng dần nhưng bạn vẫn nên loại tường minh. ORDER BY price đặt SQL Basics (120000) lên đầu và FETCH FIRST 1 ROW ONLY giữ đúng một dòng.

Bài 3.

sql

SET FEEDBACK OFF
<<shop>>
DECLARE
  category VARCHAR2(20) := 'code';
  v_n      NUMBER;
BEGIN
  SELECT COUNT(*) INTO v_n FROM books b WHERE b.category = shop.category;
  DBMS_OUTPUT.PUT_LINE('count = ' || v_n);
END;
/

text

count = 2

Nhãn shop gắn cho khối, shop.category chỉ biến, còn b.category với bí danh b chỉ cột. Hai tên giống nhau nhưng được phân biệt rõ nên kết quả là 2.

Tự kiểm tra.

Đúng một dòng. Không dòng hoặc từ hai dòng đều là lỗi.
Vì hai tình huống có nguyên nhân và cách chữa khác nhau: rỗng thì xử lý như "không có", còn nhiều dòng thì điều kiện sai hoặc cần chọn rõ.
NULL, không có ngoại lệ vì hàm tổng hợp luôn một dòng. Thêm GROUP BY category thì tập rỗng không có nhóm nào và Oracle ném NO_DATA_FOUND.
Đổi tên biến có tiền tố p_, hoặc gắn nhãn khối và viết nhãn.id.
Khi cần nhiều dòng một lần và tập kết quả đủ nhỏ để nạp vào bộ nhớ.

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. Đúng một dòng, nếu không là lỗi

2. Hàm tổng hợp luôn trả một dòng, trừ khi có GROUP BY

3. Nhiều biến, hoặc một record %ROWTYPE

4. Biến trùng tên với cột: tên cột thắng

Ví dụ đầu tiên

Xem từng bước

Hàm tổng hợp và GROUP BY

Nhận cả hàng bằng %ROWTYPE

Bẫy trùng tên

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ử đổi điều kiện

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

Bài tiếp theo

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

2. Hàm tổng hợp luôn trả một dòng, trừ khi có `GROUP BY`

3. Nhiều biến, hoặc một record `%ROWTYPE`

Hàm tổng hợp và `GROUP BY`

Nhận cả hàng bằng `%ROWTYPE`