Hôm nay làm việc thì có chị trong team đố về việc viết 1 thủ tục Oracle như sau:
Có một bảng dữ liệu gồm 4 cột Họ Tên, Số điện thoại, Số CMT và Email. Viết thủ tục đếm xem có bao nhiêu người thỏa mãn 3 tiêu chí mà người dùng nhập vào là Số điện thoại, Số CMT và Email (người dùng có thể nhập hoặc không nhập bất cứ thông tin ở tiêu chí nào cả).
Ban đầu tiếp cận cũng thấy dễ nhưng dần dần cũng khoai. Hướng triển khai ban đầu sử dụng cách check điều kiện có nhập hay không trước sau đó tiến hành nối thành một query cuối cùng.
Phần check điều kiện null:
condition:='';
if P_SDT is not null then
condition:= condition || ' AND SDT='||P_SDT;
end if;
if P_EMAIL is not null then
condition:= condition || ' AND EMAIL='||P_EMAIL;
end if;
if P_CMT is not null then
condition:= condition || ' AND CMT='||P_CMT;
end if;
Phần nối câu lệnh để run:
select count(*) into w_count
from NGUOI_DUNG from 1=1 || condition;
Câu lệnh này không thể thực thi được, Oracle không hiểu được để xử lý khi nối chuỗi vào câu lệnh SQL.
hoặc áp dụng chính câu lệnh kiểm tra trong mệnh đề Where:
select count(*) into w_count
from NGUOI_DUNG from 1=1
AND (P_CMT is not null AND CMT=P_CMT)
AND (P_EMAIL is not null AND EMAIL=P_EMAIL)
AND (P_SDT is not null AND SDT=P_SDT)
;
Câu lệnh này không thực thi được do nó luôn thực thi cả phần ngoặc trong AND (Ban đầu mình ngây thơ là trong cả cái AND to, cái CMT=P_CMT sẽ được thực thi khi cái P_CMT is not null đúng) 🙁
Xử nhiều cách khác nhau nhưng đều không được, tra Google thì không tìm được giải pháp khả dĩ do chưa có được từ khóa phù hợp.
Cuối cùng cũng tìm ra một phương pháp khả dĩ và sử dụng được như sau:
select count(*) into w_count
from NGUOI_DUNG from 1=1
and (
SDT=(CASE
WHEN P_SDT is not null THEN P_SDT
ELSE SDT
END
)
)
and (
EMAIL=(CASE
WHEN P_EMAIL is not null THEN P_EMAIL
ELSE EMAIL
END
)
)
and (
CMT=(CASE
WHEN P_CMT is not null THEN P_CMT
ELSE CMT
END
)
)
;
Phần này quan trong ở điểm nếu P_CMT is not null đúng thì CMT=P_CMT còn ngược lại thì mệnh đề CASE WHEN trả về chính CMT để CMT=CMT, 2 cột dữ liệu trung tên thì như điều kiện 1=1 nên nó luôn đúng.
Từ phần này có thể đơn giản hóa bằng câu lệnh NVL, CMT=NVL(P_CMT,CMT,P_CMT). Nhưng viết thế này người nào không rõ về NVL sẽ không hiểu lắm. Cách trên tuy dài nhưng dễ hiểu hơn.