Sử dụng các chức năng chuyển đổi

Bên cạnh các hàm tiện ích SQL, thư viện hàm sẵn có của Oracle chứa các hàm chuyển đổi kiểu. Có thể có các tình huống trong đó truy vấn yêu cầu đầu vào trong một kiểu dữ liệu cụ thể, nhưng nó lại nhận nó trong một kiểu dữ liệu khác. Trong những trường hợp như vậy, Oracle ngầm cố gắng chuyển đổi giá trị không mong muốn sang một kiểu dữ liệu tương thích có thể được thay thế tại chỗ và tính liên tục của ứng dụng không bị ảnh hưởng. Việc chuyển đổi kiểu có thể được thực hiện ngầm bởi Oracle hoặc được thực hiện một cách rõ ràng bởi lập trình viên.

Chuyển đổi kiểu dữ liệu ngầm định hoạt động dựa trên một ma trận cho thấy sự hỗ trợ của Oracle cho việc truyền kiểu nội bộ. Bên cạnh các quy tắc này, Oracle cung cấp các hàm chuyển đổi kiểu có thể được sử dụng trong các truy vấn để chuyển đổi và định dạng rõ ràng. Trên thực tế, bạn nên thực hiện chuyển đổi rõ ràng thay vì dựa vào trí tuệ phần mềm. Mặc dù chuyển đổi ngầm hoạt động tốt, nhưng để loại bỏ các cơ hội sai lệch trong đó các đầu vào xấu có thể khó nhập nội bộ.

Chuyển đổi loại dữ liệu ngầm định

Giá trị VARCHAR2 hoặc CHAR có thể được Oracle chuyển đổi hoàn toàn thành giá trị kiểu NUMBER hoặc DATE. Tương tự, một giá trị kiểu NUMBER hoặc DATA có thể được máy chủ Oracle tự động chuyển đổi thành dữ liệu ký tự. Lưu ý rằng sự chuyển đổi ẩn ý chỉ xảy ra khi ký tự đại diện cho giá trị số hoặc kiểu ngày hợp lệ tương ứng.

Ví dụ: kiểm tra các truy vấn SELECT bên dưới. Cả hai truy vấn sẽ cho cùng một kết quả vì nội bộ Oracle xử lý 15000 và '15000' giống nhau.

Truy vấn-1

SELECT employee_id,first_name,salary
FROM employees
WHERE salary > 15000;

Truy vấn-2

SELECT employee_id,first_name,salary
FROM employees
WHERE salary > '15000';

Chuyển đổi loại dữ liệu rõ ràng

Các hàm Chuyển đổi SQL là các hàm hàng đơn có khả năng đánh máy giá trị cột, chữ hoặc một biểu thức. TO_CHAR, TO_NUMBER và TO_DATE là ba hàm thực hiện sửa đổi chéo các kiểu dữ liệu.

Hàm TO_CHAR

Hàm TO_CHAR được sử dụng để nhập một đầu vào số hoặc ngày thành kiểu ký tự với mô hình định dạng (tùy chọn).

Cú pháp

TO_CHAR(number1, [format], [nls_parameter])

Để chuyển đổi số thành ký tự, tham số nls có thể được sử dụng để chỉ định các ký tự thập phân, dấu phân tách nhóm, mô hình nội tệ hoặc mô hình tiền tệ quốc tế. Đây là một thông số kỹ thuật tùy chọn - nếu không có sẵn, cài đặt nls cấp phiên sẽ được sử dụng. Để chuyển đổi ngày thành ký tự, tham số nls có thể được sử dụng để chỉ định tên ngày và tháng, nếu có.

Ngày có thể được định dạng theo nhiều định dạng sau khi chuyển đổi sang kiểu ký tự bằng hàm TO_CHAR. Hàm TO_CHAR được sử dụng để hiển thị ngày tháng trong Oracle 11g ở một định dạng cụ thể. Các mô hình định dạng có phân biệt chữ hoa chữ thường và phải được đặt trong dấu ngoặc kép.

Hãy xem xét truy vấn SELECT bên dưới. Truy vấn định dạng cột HIRE_DATE và SALARY của bảng EMPLOYEES bằng cách sử dụng hàm TO_CHAR.

SELECT first_name,
       TO_CHAR (hire_date, 'MONTH DD, YYYY') HIRE_DATE,
	   TO_CHAR (salary, '$99999.99') Salary
FROM employees
WHERE rownum < 5;

FIRST_NAME           HIRE_DATE          SALARY
-------------------- ------------------ ----------
Steven               JUNE      17, 2003  $24000.00
Neena                SEPTEMBER 21, 2005  $17000.00
Lex                  JANUARY   13, 2001  $17000.00
Alexander            JANUARY   03, 2006   $9000.00

TO_CHAR đầu tiên được sử dụng để chuyển đổi ngày thuê sang định dạng ngày tháng MONTH DD, YYYY tức là tháng được viết và đệm bằng dấu cách, tiếp theo là ngày có hai chữ số trong tháng và sau đó là năm bốn chữ số. Nếu bạn muốn hiển thị tên tháng trong trường hợp hỗn hợp (nghĩa là "Tháng 12"), chỉ cần sử dụng trường hợp này trong đối số định dạng: ('Tháng DD, YYYY').

Hàm TO_CHAR thứ hai trong Hình 10-39 được sử dụng để định dạng SALARY để hiển thị ký hiệu tiền tệ và hai vị trí thập phân.

Oracle cung cấp bộ mô hình định dạng toàn diện. Bảng dưới đây hiển thị danh sách các mô hình định dạng có thể được sử dụng để nhập các giá trị ngày và số dưới dạng ký tự bằng TO_CHAR.

Định dạng mô hình Sự miêu tả
, (dấu phẩy) Nó trả về một dấu phẩy ở vị trí được chỉ định. Bạn có thể chỉ định nhiều dấu phẩy trong mô hình định dạng số. Hạn chế: Phần tử dấu phẩy không thể bắt đầu mô hình định dạng số. Dấu phẩy không được xuất hiện ở bên phải ký tự thập phân hoặc dấu chấm trong mô hình định dạng số.
.(giai đoạn = Stage) Trả về dấu thập phân, là dấu chấm (.) Ở vị trí đã chỉ định. Hạn chế: Bạn chỉ có thể chỉ định một dấu chấm trong mô hình định dạng số
$ Trả về giá trị bằng ký hiệu đô la đứng đầu
0 Trả về các số không ở đầu. Trả về các số không ở cuối.
9 Trả về giá trị với một số chữ số được chỉ định với khoảng trắng ở đầu nếu dương hoặc với dấu trừ ở đầu nếu âm. Các số không ở đầu trống, ngoại trừ giá trị 0, giá trị này trả về số 0 cho phần nguyên của số điểm cố định.
B Trả về khoảng trống cho phần nguyên của một số điểm cố định khi phần nguyên bằng 0 (bất kể "0" trong mô hình định dạng).
C Trả về biểu tượng tiền tệ ISO ở vị trí đã chỉ định (giá trị hiện tại của tham số NLS_ISO_CURRENCY).
D Trả về ở vị trí đã chỉ định ký tự thập phân, là giá trị hiện tại của tham số NLS_NUMERIC_CHARACTER. Giá trị mặc định là dấu chấm (.). Hạn chế: Bạn chỉ có thể chỉ định một ký tự thập phân trong mô hình định dạng số.
EEE Trả về một giá trị bằng cách sử dụng ký hiệu khoa học.
FM Trả về một giá trị không có khoảng trống ở đầu hoặc cuối.
G Trả về ở vị trí đã chỉ định dấu phân tách nhóm (giá trị hiện tại của tham số NLS_NUMERIC_CHARACTER). Bạn có thể chỉ định nhiều dấu phân tách nhóm trong mô hình định dạng số. Hạn chế: Dấu phân tách nhóm không thể xuất hiện ở bên phải ký tự thập phân hoặc dấu chấm trong mô hình định dạng số
L Trả về biểu tượng nội tệ ở vị trí đã chỉ định (giá trị hiện tại của tham số NLS_CURRENCY).
MI Trả về giá trị âm có dấu trừ ở cuối (-). Trả về giá trị dương với một ô trống ở cuối. Hạn chế: Phần tử định dạng MI chỉ có thể xuất hiện ở vị trí cuối cùng của mô hình định dạng số.
PR Trả về giá trị âm trong. Nó chỉ có thể xuất hiện ở cuối mô hình định dạng số.
RN, rm Trả về một giá trị dưới dạng chữ số La Mã viết hoa. Trả về một giá trị dưới dạng chữ số La Mã ở dạng chữ thường. Giá trị có thể là một số nguyên từ 1 đến 3999.
S Trả về giá trị âm với dấu trừ ở đầu hoặc ở cuối (-). Trả về giá trị dương bằng dấu cộng ở đầu hoặc ở cuối (+). Hạn chế: Phần tử định dạng S chỉ có thể xuất hiện ở vị trí đầu tiên hoặc cuối cùng của mô hình định dạng số.
TM "Văn bản tối thiểu". Trả về (ở đầu ra thập phân) số ký tự nhỏ nhất có thể. Phần tử này không phân biệt chữ hoa chữ thường.
U Trả về ở vị trí đã chỉ định biểu tượng tiền tệ kép "Euro" (hoặc khác) (giá trị hiện tại của tham số NLS_DUAL_CURRENCY).
V Trả về giá trị nhân với 10n (và nếu cần, hãy làm tròn nó lên), trong đó n là số 9 sau chữ "V".
X Trả về giá trị thập lục phân của số chữ số đã chỉ định.

TO_NUMBER hàm

Hàm TO_NUMBER chuyển đổi giá trị ký tự thành kiểu dữ liệu số. Nếu chuỗi được chuyển đổi chứa các ký tự không phải số, hàm sẽ trả về lỗi.

Cú pháp

TO_NUMBER (string1, [format], [nls_parameter])

Bảng dưới đây hiển thị danh sách các mô hình định dạng có thể được sử dụng để nhập các giá trị ký tự dưới dạng số bằng TO_NUMBER.

Định dạng mô hình Sự miêu tả
CC Thế kỷ
SCC Thế kỷ trước Công nguyên với -
YYYY Năm có 4 số
SYYY Năm TCN có tiền tố là -
IYYY Năm ISO với 4 số
YY Năm có 2 số
RR Năm có 2 số với khả năng tương thích Y2k
NĂM Năm ký tự
SYEAR Năm tính bằng ký tự, BC có tiền tố -
BC Chỉ báo BC / AD
Q Quý bằng số (1,2,3,4)
MM Tháng trong năm 01, 02 ... 12
THÁNG Tháng tính bằng ký tự (tức là tháng 1)
THỨ HAI JAN, FEB
WW Số tuần (tức là 1)
W Số tuần của tháng (tức là 5)
IW Số tuần của năm trong tiêu chuẩn ISO.
DDD Ngày trong năm theo số (tức là 365)
DD Ngày trong tháng bằng số (tức là 28)
D Số ngày trong tuần (tức là 7)
NGÀY Ngày trong tuần bằng ký tự (tức là thứ Hai)
FMDAY Ngày trong tuần bằng ký tự (tức là thứ Hai)
DY Ngày trong tuần trong mô tả ký tự ngắn gọn (tức là SUN)
J Ngày Julian (số ngày kể từ ngày 1 tháng 1 năm 4713 trước Công nguyên, trong đó ngày 1 tháng 1 năm 4713 trước Công nguyên là 1 trong Oracle)
HH, H12 Số giờ trong ngày (1-12)
HH24 Số giờ trong ngày với ký hiệu 24 giờ (0-23)
SA, CH SA hoặc CH
Số phút và giây (tức là 59),
SSSSS Số giây trong ngày này.
DS Định dạng ngày ngắn. Phụ thuộc vào cài đặt NLS. Chỉ sử dụng với dấu thời gian.
DL Định dạng ngày dài. Phụ thuộc vào cài đặt NLS. Chỉ sử dụng với dấu thời gian.
E Tên thời đại viết tắt. Chỉ áp dụng cho các lịch: Hoàng gia Nhật Bản, Chính phủ Trung Hoa Dân quốc, Phật Thái Lan.
EE Tên thời đại đầy đủ
FF Giây phân số. Sử dụng với dấu thời gian.
FF1..FF9 Giây phân số. Sử dụng với dấu thời gian. Chữ số kiểm soát số chữ số thập phân được sử dụng cho giây phân số.
FM Chế độ điền: loại bỏ khoảng trống trong đầu ra từ chuyển đổi
FX Định dạng Chính xác: yêu cầu khớp mẫu chính xác giữa dữ liệu và mô hình định dạng.
IYY HOẶC IY HOẶC TÔI 3,2,1 chữ số cuối cùng của năm tiêu chuẩn ISO. Chỉ đầu ra
RM Biểu diễn số La Mã của tháng (I .. XII)
RR 2 số cuối của năm.
RRRR 2 chữ số cuối cùng của năm khi được sử dụng cho đầu ra. Chấp nhận năm có chữ số sau khi được sử dụng cho đầu vào.
SP Định dạng chính tả. Có thể xuất hiện ở cuối phần tử số. Kết quả luôn là tiếng anh. Ví dụ: tháng 10 ở định dạng MMSP trả về "mười"
SPTH Định dạng chính tả và thứ tự; 1 kết quả đầu tiên.
THỨ TỰ Chuyển đổi một số thành định dạng thứ tự của nó. Ví dụ 1 trở thành 1.
TS Định dạng thời gian ngắn. Phụ thuộc vào cài đặt NLS. Chỉ sử dụng với dấu thời gian.
TZD Tên múi giờ viết tắt. tức là PST.
TZH, TZM Múi giờ dịch chuyển phút / giờ.
TZR Múi giờ khu vực
X Ký tự cơ số cục bộ. Ở Mỹ, đây là một thời kỳ (.)

Các truy vấn SELECT bên dưới chấp nhận các số làm đầu vào ký tự và in chúng theo mã định dạng.

SELECT  TO_NUMBER('121.23', '9G999D99') 
FROM DUAL

TO_NUMBER('121.23','9G999D99')
------------------------------
                        121.23

SELECT  TO_NUMBER('1210.73', '9999.99') 
FROM DUAL;

TO_NUMBER('1210.73','9999.99')
------------------------------
                       1210.73

Hàm TO_DATE

Hàm nhận các giá trị ký tự làm đầu vào và trả về ngày tháng được định dạng tương đương. Hàm TO_DATE cho phép người dùng nhập ngày tháng ở bất kỳ định dạng nào, sau đó nó chuyển mục nhập sang định dạng mặc định được Oracle 11g sử dụng.

Cú pháp:

TO_DATE( string1, [ format_mask ], [ nls_language ] )

Đối số format_mask bao gồm một loạt các phần tử biểu thị chính xác dữ liệu sẽ trông như thế nào và phải được nhập trong dấu ngoặc kép.

Định dạng mô hình Sự miêu tả
NĂM Năm, đánh vần
YYYY Năm 4 chữ số
YYY, YY, Y 3, 2 hoặc 1 chữ số cuối cùng của năm.
IYY, IY, tôi 3, 2 hoặc 1 chữ số cuối cùng của năm ISO.
IYYY Năm 4 chữ số dựa trên tiêu chuẩn ISO
RRRR Chấp nhận năm có 2 chữ số và trả về năm 4 chữ số.
Q Quý trong năm (1, 2, 3, 4; JAN-MAR = 1).
MM Tháng (01-12; JAN = 01).
THỨ HAI Tên viết tắt của tháng.
THÁNG Tên tháng, được đệm bằng khoảng trống có độ dài 9 ký tự.
RM Tháng số La Mã (I-XII; JAN = I).
WW Tuần trong năm (1-53) trong đó tuần 1 bắt đầu vào ngày đầu tiên của năm và tiếp tục đến ngày thứ bảy của năm.
W Tuần trong tháng (1-5) trong đó tuần 1 bắt đầu vào ngày đầu tiên của tháng và kết thúc vào ngày thứ bảy.
IW Tuần trong năm (1-52 hoặc 1-53) dựa trên tiêu chuẩn ISO.
D Ngày trong tuần (1-7).
NGÀY Tên của ngày.
DD Ngày trong tháng (1-31).
DDD Ngày trong năm (1-366).
DY Tên viết tắt của ngày.
J Ngày Julian; số ngày kể từ ngày 1 tháng 1 năm 4712 trước Công nguyên.
HH12 Giờ trong ngày (1-12).
HH24 Giờ trong ngày (0-23).
Phút (0-59).
SSSSS Giây trước nửa đêm (0-86399).
FF Phân số giây. Sử dụng giá trị từ 1 đến 9 sau FF để biểu thị số chữ số trong giây phân số. Ví dụ: 'FF4'.
SA, CH Chỉ báo kinh tuyến
AD, BC Chỉ báo AD, BC
TZD Thông tin tiết kiệm ánh sáng ban ngày. Ví dụ: 'PST'
TZH, TZM, TZR Múi giờ giờ / phút / khu vực.

Ví dụ sau chuyển đổi một chuỗi ký tự thành một ngày tháng:

SELECT TO_DATE('January 15, 1989, 11:00 A.M.',  'Month dd, YYYY, HH:MI A.M.',  'NLS_DATE_LANGUAGE = American')
FROM DUAL;

TO_DATE('
---------
15-JAN-89

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