Sử dụng biểu thức có điều kiện

Chức năng chung

Các hàm chung được sử dụng để xử lý các giá trị NULL trong cơ sở dữ liệu. Mục tiêu của các hàm xử lý NULL chung là thay thế các giá trị NULL bằng một giá trị thay thế. Chúng ta sẽ xem qua các chức năng này dưới đây.

NVL

Hàm NVL thay thế một giá trị thay thế cho một giá trị NULL.

Cú pháp:

NVL( Arg1, replace_with )

Trong cú pháp, cả hai tham số đều là bắt buộc. Lưu ý rằng hàm NVL hoạt động với tất cả các loại kiểu dữ liệu. Ngoài ra, kiểu dữ liệu của chuỗi gốc và chuỗi thay thế phải ở trạng thái tương thích, tức là giống nhau hoặc có thể chuyển đổi hoàn toàn bởi Oracle.

Nếu arg1 là một giá trị ký tự, thì oracle sẽ chuyển đổi chuỗi thay thế thành kiểu dữ liệu tương thích với arg1 trước khi so sánh chúng và trả về VARCHAR2 trong tập ký tự của expr1. Nếu arg1 là số, thì Oracle xác định đối số có mức ưu tiên số cao nhất, chuyển đổi ngầm đối số khác thành kiểu dữ liệu đó và trả về kiểu dữ liệu đó.

Câu lệnh SELECT bên dưới sẽ hiển thị 'n / a' nếu một nhân viên chưa được giao cho bất kỳ công việc nào tức là JOB_ID là NULL. Nếu không, nó sẽ hiển thị giá trị JOB_ID thực.

SELECT  first_name, NVL(JOB_ID, 'n/a')
FROM employees;

NVL2

Là một cải tiến so với NVL, Oracle đã giới thiệu một chức năng thay thế giá trị không chỉ cho các giá trị cột NULL mà còn cho các cột NOT NULL. Hàm NVL2 có thể được sử dụng để thay thế một giá trị thay thế cho giá trị NULL cũng như giá trị không NULL.

Cú pháp:

NVL2( string1, value_if_NOT_null, value_if_null )

Câu lệnh SELECT bên dưới sẽ hiển thị 'Bench' nếu JOB_CODE cho một nhân viên là NULL. Đối với giá trị xác định không phải null của MÃ CÔNG VIỆC, nó sẽ hiển thị giá trị không đổi 'Công việc được giao'.

SQL> SELECT NVL2(JOB_CODE, 'Job Assigned', 'Bench')
FROM employees;

NULLIF

Hàm NULLIF so sánh hai đối số expr1 và expr2. Nếu expr1 và expr2 bằng nhau, nó trả về NULL; nếu không, nó trả về expr1. Không giống như hàm xử lý null khác, đối số đầu tiên không thể là NULL.

Cú pháp:

NULLIF (expr1, expr2)

Lưu ý rằng đối số đầu tiên có thể là một biểu thức đánh giá là NULL, nhưng nó không thể là NULL theo nghĩa đen. Cả hai tham số là bắt buộc để hàm thực thi.

Truy vấn dưới đây trả về NULL vì cả hai giá trị đầu vào, 12 đều bằng nhau.

SELECT	NULLIF (12, 12)
FROM DUAL;

Tương tự, truy vấn bên dưới trả về 'SUN' vì cả hai chuỗi không bằng nhau.

SELECT NULLIF ('SUN', 'MOON')
FROM DUAL;

THAN

Hàm COALESCE, một dạng NVL chung chung hơn, trả về biểu thức không rỗng đầu tiên trong danh sách đối số. Nó có tối thiểu hai tham số bắt buộc nhưng đối số tối đa không có giới hạn.

Cú pháp:

COALESCE (expr1, expr2, ... expr_n )

Hãy xem xét truy vấn SELECT bên dưới. Nó chọn giá trị không null đầu tiên được đưa vào các trường địa chỉ cho một nhân viên.

SELECT COALESCE (address1, address2, address3) Address
FROM  employees;

Điều thú vị là hoạt động của hàm COALESCE tương tự như cấu trúc IF..ELSIF..ENDIF. Truy vấn trên có thể được viết lại thành -

IF address1 is not null THEN
   result := address1;
ELSIF address2 is not null THEN
   result := address2;
ELSIF address3 is not null THEN
   result := address3;
ELSE
   result := null;
END IF;

Các chức năng có điều kiện

Oracle cung cấp các hàm có điều kiện DECODE và CASE để áp đặt các điều kiện ngay cả trong câu lệnh SQL.

Hàm DECODE

Hàm tương đương với SQL của câu lệnh thủ tục có điều kiện IF..THEN..ELSE. DECODE hoạt động với các giá trị / cột / biểu thức của tất cả các kiểu dữ liệu.

Cú pháp:

DECODE (expression, search, result [, search, result]... [, default])

Hàm DECODE so sánh biểu thức với từng giá trị tìm kiếm theo thứ tự. Nếu tồn tại sự bình đẳng giữa biểu thức và đối số tìm kiếm, thì nó trả về kết quả tương ứng. Trong trường hợp không khớp, giá trị mặc định được trả về, nếu được xác định, giá trị khác NULL. Trong trường hợp bất kỳ loại nào không khớp về khả năng tương thích, oracle thực hiện chuyển đổi ngầm định có thể xảy ra để trả về kết quả.

Trên thực tế, Oracle coi hai null là tương đương khi làm việc với hàm DECODE.

SELECT DECODE(NULL,NULL,'EQUAL','NOT EQUAL') 
FROM DUAL;

DECOD
-----
EQUAL

Nếu biểu thức là null, thì Oracle trả về kết quả của tìm kiếm đầu tiên cũng là null. Số thành phần tối đa trong hàm DECODE là 255.

SELECT	first_name, salary, DECODE (hire_date, sysdate,'NEW JOINEE','EMPLOYEE')
	FROM employees;

Biểu thức CASE

Biểu thức CASE hoạt động trên cùng một khái niệm như DECODE nhưng khác về cú pháp và cách sử dụng.

Cú pháp:

CASE  [ expression ]
   WHEN condition_1 THEN result_1
   WHEN condition_2 THEN result_2
   ...
   WHEN condition_n THEN result_n
   ELSE result
END

Tìm kiếm Oracle bắt đầu từ trái và di chuyển sang phải cho đến khi tìm thấy điều kiện đúng, rồi trả về biểu thức kết quả được liên kết với nó. Nếu không tìm thấy điều kiện nào là đúng và tồn tại mệnh đề ELSE, thì Oracle trả về kết quả được xác định với hàm khác. Nếu không, Oracle trả về null.

Số đối số tối đa trong một biểu thức CASE là 255. Tất cả các biểu thức được tính vào giới hạn này, bao gồm biểu thức ban đầu của biểu thức CASE đơn giản và biểu thức ELSE tùy chọn. Mỗi cặp WHEN ... THEN được tính là hai đối số. Để tránh vượt quá giới hạn này, bạn có thể lồng các biểu thức CASE để bản thân return_expr là một biểu thức CASE.

SELECT first_name, CASE	WHEN salary < 200 THEN 'GRADE 1'
			WHEN salary > 200 AND salary < 5000 THEN 'GRADE 2'
			ELSE 'GRADE 3'
		   END CASE
FROM employees;	

ENAM    CASE
----    -------
JOHN    GRADE 2
EDWIN   GRADE 3
KING    GRADE 1