Trong phần này chúng ta sẽ tiếp tục tìm hiểu về các hàm chuyển đổi dữ liệu, một số hàm thông dụng khác cũng như cách sử dụng biểu thức điều kiện trong mệnh đề SELECT.

 

Vấn đề chuyển đổi ngầm định và tường minh

 

Ngoài kiểu dữ liệu sẵn có, Oracle còn cho phép khai báo các kiểu dữ liệu theo chuẩn ANSI, DB2, hay SQL/DS. Tuy vậy Oracle tự chuyển đổi các kiểu dữ liệu đó về các kiểu dữ liệu của Oracle.

Việc chuyển đổi ngầm định được thực hiện khi Oracle nhận vào 1 lượng dữ liệu kiểu này, trong khi Oracle lại cho nó là kiểu khác. Trong khi đó việc chuyển đổi tường minh được thực hiện bởi user nhờ vào các conversion functions. Oracle khuyến khích thực hiện việc chuyển đổi tường minh để đảm bảo sự tương thích kiểu dữ liệu.

Ở phần trước mình có 2 vd thế này, không biết bạn nào có thắc mắc không:

 

 

Ở vd thứ nhất, khi mình lấy sysdate trừ đi 1 ngày, mình phải bỏ ngày đó vào trong hàm chuyển đổi, mặc dù như mình ghi thì rõ ràng là chuỗi đó đã theo kiểu ngày tháng. Vậy nếu mình không bỏ vào hàm chuyển đổi thì sao:

 

 

Báo lỗi invalid number! Đó là do khi nhận vào dữ liệu sysdate – ’19-JAN-2013′, Oracle tưởng rằng user muốn lấy sysdate trừ đi 1 số lượng ngày nào đó, nên ngầm định chuyển đổi ’19-JAN-2013′ sang kiểu số. Mà rõ ràng đây là 1 chuỗi có chữ cái thì nó đâu thể convert sang số được.

Ở ví dụ thứ hai, mình đưa thẳng ’15-FEB-2013′ vào trong hàm MONTHS_BETWEEN mà không sử dụng hàm chuyển đổi. Đó là do hàm MONTHS_BEETWEEN nhận vào 2 giá trị ngày tháng, do đó nó sẽ convert chuỗi ’15-FEB-2013′ sang dạng ngày tháng, và tất nhiên do đúng format nên nó có thể convert chuỗi này sang ngày tháng. boring

Từ 2 ví dụ trên, ta thấy Oracle có thể chuyển đổi ngầm định từ dạng VARCHAR2 hay CHAR sang dạng NUMBER hay DATE. Và ngược lại, Oracle cũng có thể chuyển đổi ngầm định từ dạng NUMBER hay DATE sang VARCHAR2 hay CHAR. Điều kiện tất nhiên là phải đúng định dạng của kiểu dữ liệu đó.

 

Trong khi đó, việc chuyển đổi tường minh được thực hiện theo sơ đồ sau:

conversion_diagram

 

Ta thấy có 3 hàm chuyển đổi:

TO_CHAR(number|date [,fmt] [,nlsparam]): chuyển đổi number hay date sang chuỗi varchar2 với format fmt và theo tiêu chuẩn ngôn ngữ nlsparam (về định dạng số, tiền tệ, ngày tháng). Nếu bỏ qua nlsparam thì Oracle sử dụng giá trị mặc định trong hệ thống.

TO_NUMBER(char [,fmt] [,nlsparam]): convert 1 chuỗi sang số

TO_DATE(char [,fmt] [,nlsparam]): convert 1 chuỗi sang dạng ngày tháng theo format fmt. Nếu không ghi format thì mặc định là kiểu ‘DD-MON-YY’

 

Conversion Functions – Các hàm chuyển đổi dữ liệu

 

– Hàm TO_CHAR: chuyển đổi kiểu số hay ngày tháng sang chuỗi ký tự

TO_CHAR(date, ‘format’): định dạng phải đặt trong dấu nháy đơn, phân biệt hoa thường, tên thứ và tháng đầy đủ được tự động thêm khoảng trắng để đủ chiều rộng column, có thể thêm fm phía trước để xóa khoảng trắng thừa.

 

 

Định dạng ngày tháng cơ bản:

– YEAR: năm phát âm theo tiếng Anh

– YYYY: năm 4 chữ số

– MM: tháng 2 chữ số

– MON: tên tháng viết tắt

– MONTH: tên tháng đầy đủ

– DDD: ngày trong năm

– DD: ngày trong tháng

– D: ngày trong tuần

– DY: thứ viết tắt

– DAY: thứ đầy đủ

– Q: quý

– RM: tháng theo kiểu La mã

– W: tuần trong tháng

– WW (IW): tuần trong năm (IW theo chuẩn ISO)

– J: julian day

– AM, PM, A.M., P.M.

– HH, HH12: giờ hệ 12

– HH24: giờ hệ 24

– MI: phút

– SS: giây

– SSSSS: số giây từ 0 giờ

– SP: chuyển số sang dạng phát âm tiếng Anh (chữ số)

– SPTH: chuyển số sang dạng phát âm tiếng Anh (số thứ tự)

– Có thể thêm chuỗi ký tự trong dấu nháy kép.

 

 

TO_CHAR(number, ‘format’)

Một số ký tự định dạng:

– 9: đại diện cho 1 chữ số

– 0: hiển thị 1 số 0

– $: hiển thị dấu $

– L: hiển thị ký tự tiền tệ

– .: hiển thị dấu thập phân tại vị trí xác định

– ,: hiển thị dấu , phần ngàn tại vị trí xác định

– G: hiển thị dấu phần ngàn tại vị trí xác định

– S: hiển thị dấu +/- xác định số dương/âm

 

 

Nếu độ dài của chuỗi số dài hơn chuỗi định dạng thì SQL hiển thị 1 chuỗi dấu #. Trường hợp số chữ số thập phân trong định dạng ít hơn trong chuỗi số thì Oracle tự động làm tròn số.

 

– Hàm TO_NUMBER(char [,format]): chuyển 1 chuỗi chữ số sang số

 

 

– Hàm TO_DATE(char [,format]): chuyển 1 chuỗi dạng ngày tháng sang kiểu ngày tháng. Nếu muốn đảm bảo chuỗi nhập vào đúng với format kể cả khoảng trắng, dấu câu, sử dụng fx ở đầu chuỗi format. Khi phát hiện chuỗi nhập vào không đúng thì sẽ báo lỗi không convert được. Còn không dùng fx thì Oracle sẽ tự bỏ qua.

 

 

Qua các ví dụ ta có thể thấy được format trong hàm TO_CHAR là format để định dạng chuỗi kết quả xuất ra, còn format trong hàm TO_NUMBER hay TO_DATE là để nhận dạng chuỗi nhập vào.

 

Hàm lồng nhau

 

Các Single-row functions có thể lồng vào nhau. Khi đó dữ liệu sẽ được xử lý từ trong ra ngoài.

 

 

1 số hàm khác làm việc với giá trị NULL

– Hàm NVL(expr1, expr2): trả về expr2 nếu expr1 là NULL

– Hàm NVL2(expr1, expr2, expr3): trả về expr2 nếu expr1 không NULL, trả về expr3 nếu expr1 NULL

 

 

– Hàm NULLIF(expr1, expr2): so sánh 2 giá trị, trả về NULL nếu 2 giá trị bằng nhau, trả về expr1 nếu không bằng nhau.

– Hàm COALESCE(): trả về giá trị đầu tiên không NULL trong danh sách, các giá trị phải cùng kiểu dữ liệu.

 

 

Các hàm rẽ nhánh

 

Khi lập trình ta thường dùng 1 hàm rẽ nhánh phổ biến nhất là IF (IF – THEN – ELSE). Trong SQL, ta có thể sử dụng 2 lệnh CASEDECODE để thực hiện rẽ nhánh tương tự như hàm IF.

 

– Hàm Case

 

CASE expr WHEN comparision_expr1 THEN return_expr1

WHEN comparision_expr2 THEN return_expr2

….

ELSE else_expr

END

 

So sánh expr với comparision_expr, nếu đúng thì trả về return_expr. expr và comparision_expr phải cùng kiểu dữ liệu, các return_expr phải cùng kiểu dữ liệu.

 

 

– Hàm Decode

 

DECODE(col|expression, search1, result1, search2, result2, …, default)

 

So sánh col|expression với mỗi giá trị search, nếu đúng với search1 thì trả ra result1, đúng với search2 thì trả ra result2,…, nếu sai hết thì trả ra default. Nếu không có giá trị default thì trả ra NULL.

 

 

Như vậy là qua bài này, chúng ta đã nắm được thêm các hàm xử lý trong Oracle để sử dụng trong các câu lệnh. Nhìn cũng đơn giản nhưng thỉnh thoảng dùng cũng rắc rối lắm ^^.