การใช้ฟังก์ชันการแปลง

นอกเหนือจากฟังก์ชันยูทิลิตี้ SQL แล้วไลบรารีฟังก์ชัน Oracle inbuilt ยังมีฟังก์ชันการแปลงประเภท อาจมีสถานการณ์จำลองที่แบบสอบถามคาดว่าจะมีการป้อนข้อมูลในชนิดข้อมูลเฉพาะ แต่จะได้รับในประเภทข้อมูลอื่น ในกรณีเช่นนี้ Oracle จะพยายามแปลงค่าที่ไม่คาดคิดให้เป็นชนิดข้อมูลที่เข้ากันได้โดยปริยายซึ่งสามารถทดแทนได้และความต่อเนื่องของแอปพลิเคชันจะไม่ถูกบุกรุก การแปลงประเภทสามารถทำได้โดยปริยายโดย Oracle หรือกระทำโดยโปรแกรมเมอร์อย่างชัดเจน

การแปลงประเภทข้อมูลโดยนัยทำงานบนพื้นฐานของเมทริกซ์ซึ่งแสดงการสนับสนุนของ Oracle สำหรับการหล่อประเภทภายใน นอกเหนือจากกฎเหล่านี้แล้ว Oracle ยังมีฟังก์ชันการแปลงประเภทซึ่งสามารถใช้ในแบบสอบถามสำหรับการแปลงและการจัดรูปแบบอย่างชัดเจน ตามความเป็นจริงขอแนะนำให้ทำการแปลงอย่างชัดเจนแทนที่จะใช้ซอฟต์แวร์อัจฉริยะ แม้ว่าการแปลงโดยปริยายจะทำงานได้ดี แต่เพื่อลดโอกาสในการเบ้ที่อินพุตที่ไม่ถูกต้องอาจเป็นเรื่องยากที่จะพิมพ์คาสต์ภายใน

การแปลงประเภทข้อมูลโดยนัย

ค่า VARCHAR2 หรือ CHAR สามารถแปลงเป็นค่าประเภท NUMBER หรือ DATE โดยปริยายโดย Oracle ในทำนองเดียวกันค่าประเภท NUMBER หรือ DATA สามารถแปลงเป็นข้อมูลอักขระโดยอัตโนมัติโดยเซิร์ฟเวอร์ Oracle โปรดทราบว่าการแปลงระหว่างอิมพิซิทจะเกิดขึ้นเฉพาะเมื่ออักขระแสดงถึงตัวเลขหรือค่าประเภทวันที่ที่ถูกต้องตามลำดับ

ตัวอย่างเช่นตรวจสอบแบบสอบถาม SELECT ด้านล่าง ทั้งสองแบบสอบถามจะให้ผลลัพธ์เหมือนกันเนื่องจาก Oracle ปฏิบัติภายใน 15000 และ '15000' เหมือนกัน

แบบสอบถาม -1

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

แบบสอบถาม -2

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

การแปลงประเภทข้อมูลที่ชัดเจน

ฟังก์ชันการแปลง SQL เป็นฟังก์ชันแถวเดียวที่สามารถพิมพ์ค่าคอลัมน์ลิเทอรัลหรือนิพจน์ได้ TO_CHAR, TO_NUMBER และ TO_DATE เป็นฟังก์ชันสามอย่างที่ดำเนินการแก้ไขข้ามประเภทข้อมูล

TO_CHAR ฟังก์ชัน

ฟังก์ชัน TO_CHAR ใช้ในการพิมพ์ป้อนข้อมูลตัวเลขหรือวันที่เป็นประเภทอักขระด้วยรูปแบบรูปแบบ (ทางเลือก)

ไวยากรณ์

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

สำหรับการแปลงตัวเลขเป็นอักขระพารามิเตอร์ nls สามารถใช้เพื่อระบุอักขระทศนิยมตัวคั่นกลุ่มโมเดลสกุลเงินท้องถิ่นหรือโมเดลสกุลเงินสากล เป็นข้อกำหนดทางเลือก - หากไม่มีให้ใช้การตั้งค่าระดับเซสชัน nls จะถูกใช้ สำหรับการแปลงวันที่เป็นอักขระสามารถใช้พารามิเตอร์ nls เพื่อระบุชื่อวันและเดือนได้ตามความเหมาะสม

วันที่สามารถจัดรูปแบบได้หลายรูปแบบหลังจากแปลงเป็นประเภทอักขระโดยใช้ฟังก์ชัน TO_CHAR ฟังก์ชัน TO_CHAR ใช้เพื่อให้ Oracle 11g แสดงวันที่ในรูปแบบเฉพาะ โมเดลรูปแบบเป็นแบบคำนึงถึงขนาดตัวพิมพ์และต้องอยู่ภายในเครื่องหมายคำพูดเดียว

พิจารณาคำค้นหา SELECT ด้านล่าง แบบสอบถามจัดรูปแบบคอลัมน์ HIRE_DATE และ SALARY ของตาราง EMPLOYEES โดยใช้ฟังก์ชัน 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 แรกใช้ในการแปลงวันที่จ้างเป็นรูปแบบวันที่ MONTH DD YYYY คือเดือนที่สะกดออกและเติมช่องว่างตามด้วยวันสองหลักของเดือนแล้วตามด้วยปีสี่หลัก หากคุณต้องการแสดงชื่อเดือนในกรณีผสม (นั่นคือ "ธันวาคม") ให้ใช้กรณีนี้ในอาร์กิวเมนต์รูปแบบ: ('Month DD, YYYY')

ฟังก์ชัน TO_CHAR ที่สองในรูปที่ 10-39 ใช้ในการจัดรูปแบบ SALARY เพื่อแสดงเครื่องหมายสกุลเงินและตำแหน่งทศนิยมสองตำแหน่ง

Oracle นำเสนอชุดรูปแบบรูปแบบที่ครอบคลุม ตารางด้านล่างแสดงรายการรูปแบบรูปแบบที่สามารถใช้เพื่อพิมพ์ค่าวันที่และตัวเลขเป็นอักขระโดยใช้ TO_CHAR

รูปแบบรูปแบบ	คำอธิบาย
, (ลูกน้ำ)	ส่งคืนเครื่องหมายจุลภาคในตำแหน่งที่ระบุ คุณสามารถระบุจุลภาคหลายรายการในรูปแบบตัวเลข ข้อ จำกัด : องค์ประกอบลูกน้ำไม่สามารถเริ่มรูปแบบรูปแบบตัวเลขได้ เครื่องหมายจุลภาคไม่สามารถปรากฏทางด้านขวาของอักขระทศนิยมหรือจุดในรูปแบบตัวเลข
. (คาบ)	ส่งคืนจุดทศนิยมซึ่งเป็นจุด (.) ในตำแหน่งที่ระบุ ข้อ จำกัด : คุณสามารถระบุช่วงเวลาเดียวในรูปแบบตัวเลข
$	ส่งคืนค่าด้วยเครื่องหมายดอลลาร์นำหน้า
0	ส่งคืนค่าศูนย์นำหน้า ส่งกลับค่าศูนย์ต่อท้าย
9	ส่งคืนค่าด้วยจำนวนหลักที่ระบุโดยมีช่องว่างนำหน้าถ้าเป็นบวกหรือนำหน้าลบหากเป็นลบ เลขศูนย์นำหน้าว่างเปล่ายกเว้นค่าศูนย์ซึ่งส่งคืนศูนย์สำหรับส่วนจำนวนเต็มของจำนวนจุดคงที่
ข	ส่งคืนช่องว่างสำหรับส่วนจำนวนเต็มของจำนวนจุดคงที่เมื่อส่วนจำนวนเต็มเป็นศูนย์ (โดยไม่คำนึงถึง "0" ในโมเดลรูปแบบ)
ค	ส่งกลับในตำแหน่งที่ระบุสัญลักษณ์สกุลเงิน ISO (ค่าปัจจุบันของพารามิเตอร์ NLS_ISO_CURRENCY)
ง	ส่งกลับในตำแหน่งที่ระบุอักขระทศนิยมซึ่งเป็นค่าปัจจุบันของพารามิเตอร์ NLS_NUMERIC_CHARACTER ค่าเริ่มต้นคือจุด (.) ข้อ จำกัด : คุณสามารถระบุอักขระทศนิยมเพียงตัวเดียวในรูปแบบตัวเลข
EEE	ส่งคืนค่าโดยใช้สัญกรณ์วิทยาศาสตร์
เอฟเอ็ม	ส่งคืนค่าโดยไม่มีช่องว่างนำหน้าหรือต่อท้าย
ช	ส่งกลับในตำแหน่งที่ระบุตัวคั่นกลุ่ม (ค่าปัจจุบันของพารามิเตอร์ NLS_NUMERIC_CHARACTER) คุณสามารถระบุตัวคั่นกลุ่มหลายตัวในรูปแบบรูปแบบตัวเลข ข้อ จำกัด : ตัวคั่นกลุ่มไม่สามารถปรากฏทางด้านขวาของอักขระทศนิยมหรือจุดในรูปแบบตัวเลข
ล	ส่งคืนสัญลักษณ์สกุลเงินท้องถิ่นในตำแหน่งที่ระบุ (ค่าปัจจุบันของพารามิเตอร์ NLS_CURRENCY)
MI	ส่งคืนค่าลบโดยมีเครื่องหมายลบต่อท้าย (-) ส่งคืนค่าบวกพร้อมช่องว่างต่อท้าย ข้อ จำกัด : องค์ประกอบรูปแบบ MI สามารถปรากฏได้เฉพาะในตำแหน่งสุดท้ายของโมเดลรูปแบบตัวเลข
ประชาสัมพันธ์	ส่งคืนค่าลบใน สามารถปรากฏได้เฉพาะในส่วนท้ายของโมเดลรูปแบบตัวเลข
RN, rm	ส่งคืนค่าเป็นตัวเลขโรมันเป็นตัวพิมพ์ใหญ่ ส่งคืนค่าเป็นตัวเลขโรมันเป็นตัวพิมพ์เล็ก ค่าสามารถเป็นจำนวนเต็มระหว่าง 1 ถึง 3999
ส	ส่งคืนค่าลบโดยมีเครื่องหมายลบนำหน้าหรือต่อท้าย (-) ส่งคืนค่าบวกด้วยเครื่องหมายบวกนำหน้าหรือต่อท้าย (+) ข้อ จำกัด : องค์ประกอบรูปแบบ S สามารถปรากฏได้เฉพาะในตำแหน่งแรกหรือตำแหน่งสุดท้ายของโมเดลรูปแบบตัวเลข
TM	"ข้อความขั้นต่ำ" ส่งกลับ (ในเอาต์พุตทศนิยม) จำนวนอักขระที่น้อยที่สุดที่เป็นไปได้ องค์ประกอบนี้ไม่คำนึงถึงขนาดตัวพิมพ์
ยู	ส่งกลับในตำแหน่งที่ระบุสัญลักษณ์สกุลเงินคู่ "ยูโร" (หรืออื่น ๆ ) (ค่าปัจจุบันของพารามิเตอร์ NLS_DUAL_CURRENCY)
V	ส่งคืนค่าคูณด้วย 10n (และถ้าจำเป็นให้ปัดเศษขึ้น) โดยที่ n คือจำนวน 9 หลัง "V"
X	ส่งคืนค่าฐานสิบหกของจำนวนหลักที่ระบุ

TO_NUMBER ฟังก์ชัน

ฟังก์ชัน TO_NUMBER แปลงค่าอักขระเป็นประเภทข้อมูลที่เป็นตัวเลข หากสตริงที่แปลงมีอักขระที่ไม่ใช่ตัวเลขฟังก์ชันจะส่งกลับข้อผิดพลาด

ไวยากรณ์

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

ตารางด้านล่างแสดงรายการรูปแบบรูปแบบที่สามารถใช้พิมพ์ค่าอักขระเป็นตัวเลขโดยใช้ TO_NUMBER

รูปแบบรูปแบบ	คำอธิบาย
ซีซี	ศตวรรษ
SCC	คริสตศักราชขึ้นต้นด้วย -
ปปปปปป	ปีที่มีตัวเลข 4 ตัว
SYYY	ปีคริสตศักราชขึ้นต้นด้วย -
อ๊ายยยย	ปี ISO ที่มีตัวเลข 4 ตัว
ปปป	ปีที่มีตัวเลข 2 ตัว
RR	ปีที่มีตัวเลข 2 ตัวที่เข้ากันได้กับ Y2k
ปี	ปีในตัวอักษร
SYEAR	ปีในอักขระ BC ขึ้นต้นด้วย -
พ.ศ.	ตัวบ่งชี้ BC / AD
ถาม	ไตรมาสในตัวเลข (1,2,3,4)
MM	เดือนปี 01, 02 ... 12
เดือน	เดือนเป็นอักขระ (เช่นมกราคม)
จันทร์	ม.ค. ก.พ.
WW	หมายเลขสัปดาห์ (เช่น 1)
ว	จำนวนสัปดาห์ของเดือน (เช่น 5)
IW	จำนวนสัปดาห์ของปีในมาตรฐาน ISO
DDD	วันของปีเป็นตัวเลข (เช่น 365)
DD	วันของเดือนเป็นตัวเลข (เช่น 28)
ง	วันในสัปดาห์เป็นตัวเลข (เช่น 7)
วัน	วันในสัปดาห์เป็นอักขระ (เช่นวันจันทร์)
FMDAY	วันในสัปดาห์เป็นอักขระ (เช่นวันจันทร์)
DY	วันของสัปดาห์ในคำอธิบายตัวอักษรสั้น ๆ (เช่น SUN)
เจ	Julian Day (จำนวนวันตั้งแต่ 1 มกราคม 4713 ปีก่อนคริสตกาลโดยที่ 1 มกราคม 4713 เป็น 1 ใน Oracle)
HH, H12	จำนวนชั่วโมงของวัน (1-12)
HH24	จำนวนชั่วโมงของวันพร้อมสัญกรณ์ 24 ชั่วโมง (0-23)
AM, PM	AM หรือ PM
นางสาว	จำนวนนาทีและวินาที (เช่น 59)
สสส	จำนวนวินาทีในวันนี้
DS	รูปแบบวันที่สั้น ขึ้นอยู่กับการตั้งค่า NLS ใช้เฉพาะกับการประทับเวลา
DL	รูปแบบวันที่ยาว ขึ้นอยู่กับการตั้งค่า NLS ใช้เฉพาะกับการประทับเวลา
จ	ชื่อย่อยุค. ใช้ได้เฉพาะปฏิทิน: Japanese Imperial, ROC Official, Thai Buddha
ศ	ชื่อศักราชเต็ม
FF	วินาทีเศษส่วน ใช้กับการประทับเวลา
ก FF1..FF9	วินาทีเศษส่วน ใช้กับการประทับเวลา ตัวเลขนี้ควบคุมจำนวนหลักทศนิยมที่ใช้เป็นเศษส่วนของวินาที
เอฟเอ็ม	Fill Mode: ระงับช่องว่างในเอาต์พุตจากการแปลง
FX	รูปแบบที่แน่นอน: ต้องการรูปแบบที่ตรงกันระหว่างข้อมูลและรูปแบบรูปแบบ
IYY หรือ IY หรือ I	ตัวเลข 3,2,1 หลักสุดท้ายของปีมาตรฐาน ISO เอาต์พุตเท่านั้น
RM	การแสดงตัวเลขโรมันของเดือน (I .. XII)
RR	เลข 2 หลักสุดท้ายของปี
RRRR	ตัวเลข 2 หลักสุดท้ายของปีเมื่อใช้สำหรับเอาต์พุต ยอมรับปีที่เป็นตัวเลขหลักเมื่อใช้สำหรับการป้อนข้อมูล
SP	รูปแบบการสะกด สามารถปรากฏที่ส่วนท้ายขององค์ประกอบตัวเลข ผลลัพธ์จะเป็นภาษาอังกฤษเสมอ ตัวอย่างเช่นเดือน 10 ในรูปแบบ MMSP จะส่งกลับ "สิบ"
SPTH	รูปแบบการสะกดและลำดับ; 1 ผลลัพธ์ในครั้งแรก
TH	แปลงตัวเลขเป็นรูปแบบลำดับ ตัวอย่างเช่น 1 จะกลายเป็น 1
TS	รูปแบบเวลาสั้น ๆ ขึ้นอยู่กับการตั้งค่า NLS ใช้เฉพาะกับการประทับเวลา
TZD	ชื่อโซนเวลาแบบย่อ เช่น PST.
TZH, TZM	การเคลื่อนที่ของโซนเวลาชั่วโมง / นาที
TZR	ภูมิภาคโซนเวลา
X	อักขระ radix ท้องถิ่น ในอเมริกานี่คือช่วงเวลา (.)

แบบสอบถาม SELECT ด้านล่างยอมรับตัวเลขเป็นอินพุตอักขระและพิมพ์ออกมาตามตัวระบุรูปแบบ

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

ฟังก์ชัน TO_DATE

ฟังก์ชันรับค่าอักขระเป็นอินพุตและส่งกลับค่าวันที่ที่จัดรูปแบบเทียบเท่ากับค่าเดียวกัน ฟังก์ชัน TO_DATE ช่วยให้ผู้ใช้ป้อนวันที่ในรูปแบบใดก็ได้จากนั้นจะแปลงรายการเป็นรูปแบบเริ่มต้นที่ใช้โดย Oracle 11g

ไวยากรณ์:

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

อาร์กิวเมนต์ format_mask ประกอบด้วยชุดขององค์ประกอบที่แสดงว่าข้อมูลควรมีลักษณะอย่างไรและต้องใส่ในเครื่องหมายคำพูดเดี่ยว

รูปแบบรูปแบบ	คำอธิบาย
ปี	ปีสะกดออก
ปปปปปป	ปี 4 หลัก
ปปปปปปปปป	3, 2 หรือ 1 หลักสุดท้ายของปี
IYY, IY, I	3, 2 หรือ 1 หลักสุดท้ายของปี ISO
อ๊ายยยย	ปี 4 หลักตามมาตรฐาน ISO
RRRR	ยอมรับปี 2 หลักและส่งคืนปี 4 หลัก
ถาม	ไตรมาสของปี (1, 2, 3, 4; JAN-MAR = 1)
MM	เดือน (01-12; JAN = 01)
จันทร์	ชื่อย่อของเดือน.
เดือน	ชื่อเดือนเติมช่องว่างให้มีความยาว 9 อักขระ
RM	เลขโรมันเดือน (I-XII; JAN = I)
WW	สัปดาห์ของปี (1-53) โดยสัปดาห์ที่ 1 เริ่มในวันแรกของปีและต่อเนื่องไปถึงวันที่เจ็ดของปี
ว	สัปดาห์ของเดือน (1-5) โดยสัปดาห์ที่ 1 เริ่มในวันแรกของเดือนและสิ้นสุดในวันที่เจ็ด
IW	สัปดาห์ของปี (1-52 หรือ 1-53) ตามมาตรฐาน ISO
ง	วันในสัปดาห์ (1-7)
วัน	ชื่อวัน
DD	วันของเดือน (1-31)
DDD	วันของปี (1-366)
DY	ชื่อย่อของวัน
เจ	วันจูเลียน; จำนวนวันนับตั้งแต่ 1 มกราคม 4712 ปีก่อนคริสตกาล
HH12	ชั่วโมงของวัน (1-12)
HH24	ชั่วโมงของวัน (0-23)
นางสาว	นาที (0-59).
สสส	วินาทีที่ผ่านมาเที่ยงคืน (0-86399)
FF	เศษส่วนของวินาที ใช้ค่าตั้งแต่ 1 ถึง 9 หลัง FF เพื่อระบุจำนวนหลักในเสี้ยววินาที ตัวอย่างเช่น 'FF4'
AM, PM	ตัวบ่งชี้ Meridian
ค. ศ	AD, BC ตัวบ่งชี้
TZD	ข้อมูลการประหยัดเวลากลางวัน ตัวอย่างเช่น "PST"
TZH, TZM, TZR	โซนเวลาชั่วโมง / นาที / ภูมิภาค

ตัวอย่างต่อไปนี้แปลงสตริงอักขระเป็นวันที่:

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

ฟังก์ชั่นทั่วไป

ฟังก์ชันทั่วไปใช้เพื่อจัดการกับค่า NULL ในฐานข้อมูล วัตถุประสงค์ของฟังก์ชันการจัดการ NULL ทั่วไปคือการแทนที่ค่า NULL ด้วยค่าอื่น เราจะดูสั้น ๆ ผ่านฟังก์ชั่นเหล่านี้ด้านล่าง

NVL

ฟังก์ชัน NVL แทนที่ค่าทางเลือกสำหรับค่า NULL

ไวยากรณ์:

NVL( Arg1, replace_with )

ในไวยากรณ์จำเป็นต้องใช้พารามิเตอร์ทั้งสอง โปรดทราบว่าฟังก์ชัน NVL ใช้งานได้กับประเภทข้อมูลทุกประเภท และชนิดข้อมูลของสตริงต้นฉบับและการแทนที่จะต้องอยู่ในสถานะที่เข้ากันได้เช่นเดียวกันหรือโดยปริยายแปลงได้โดย Oracle

ถ้า arg1 เป็นค่าอักขระ oracle จะแปลงสตริงแทนที่เป็นชนิดข้อมูลที่เข้ากันได้กับ arg1 ก่อนที่จะเปรียบเทียบและส่งคืน VARCHAR2 ในชุดอักขระของ expr1 ถ้า arg1 เป็นตัวเลข Oracle จะกำหนดอาร์กิวเมนต์ที่มีลำดับความสำคัญของตัวเลขสูงสุดโดยปริยายจะแปลงอาร์กิวเมนต์อื่นเป็นชนิดข้อมูลนั้นและส่งคืนชนิดข้อมูลนั้น

คำสั่ง SELECT ด้านล่างจะแสดง 'n / a' หากพนักงานยังไม่ได้รับมอบหมายให้ทำงานใด ๆ กล่าวคือ JOB_ID เป็น NULL มิฉะนั้นจะแสดงค่า JOB_ID จริง

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

NVL2

ในการปรับปรุง NVL Oracle ได้แนะนำฟังก์ชันเพื่อทดแทนค่าไม่เพียง แต่สำหรับค่าคอลัมน์ NULL เท่านั้น แต่ยังรวมถึงคอลัมน์ NOT NULL ฟังก์ชัน NVL2 สามารถใช้แทนค่าทางเลือกสำหรับค่า NULL เช่นเดียวกับค่าที่ไม่ใช่ค่า NULL

ไวยากรณ์:

NVL2( string1, value_if_NOT_null, value_if_null )

คำสั่ง SELECT ด้านล่างจะแสดง 'Bench' หาก JOB_CODE สำหรับพนักงานเป็น NULL สำหรับค่า JOB CODE ที่ไม่แน่นอนแน่นอนจะแสดงค่าคงที่ 'Job Assigned'

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

NULLIF

ฟังก์ชัน NULLIF เปรียบเทียบสองอาร์กิวเมนต์ expr1 และ expr2 ถ้า expr1 และ expr2 เท่ากันจะคืนค่า NULL มิฉะนั้นจะคืนค่า expr1 ไม่เหมือนกับฟังก์ชันการจัดการ null อื่น ๆ อาร์กิวเมนต์แรกไม่สามารถเป็น NULL ได้

ไวยากรณ์:

NULLIF (expr1, expr2)

โปรดสังเกตว่าอาร์กิวเมนต์แรกอาจเป็นนิพจน์ที่ประเมินค่าเป็น NULL แต่ไม่สามารถเป็น NULL ตามตัวอักษรได้ พารามิเตอร์ทั้งสองจำเป็นสำหรับฟังก์ชันที่จะดำเนินการ

แบบสอบถามด้านล่างส่งคืนค่า NULL เนื่องจากทั้งค่าอินพุต 12 เท่ากัน

SELECT	NULLIF (12, 12)
FROM DUAL;

ในทำนองเดียวกันข้อความค้นหาด้านล่างจะส่งคืน 'SUN' เนื่องจากสตริงทั้งสองไม่เท่ากัน

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

COALESCE

ฟังก์ชัน COALESCE ซึ่งเป็นรูปแบบทั่วไปของ NVL จะส่งคืนนิพจน์ที่ไม่ใช่ค่าว่างแรกในรายการอาร์กิวเมนต์ ต้องใช้พารามิเตอร์บังคับขั้นต่ำสองตัว แต่อาร์กิวเมนต์สูงสุดไม่มีขีด จำกัด

ไวยากรณ์:

COALESCE (expr1, expr2, ... expr_n )

พิจารณาคำค้นหา SELECT ด้านล่าง เลือกค่าแรกที่ไม่ใช่ค่าว่างที่ป้อนลงในช่องที่อยู่สำหรับพนักงาน

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

ที่น่าสนใจคือการทำงานของฟังก์ชัน COALESCE นั้นคล้ายกับการสร้าง IF..ELSIF..ENDIF ข้อความค้นหาด้านบนสามารถเขียนซ้ำได้ว่า -

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;

ฟังก์ชันตามเงื่อนไข

Oracle มีฟังก์ชันตามเงื่อนไข DECODE และ CASE เพื่อกำหนดเงื่อนไขแม้ในคำสั่ง SQL

ฟังก์ชัน DECODE

ฟังก์ชันคือความเทียบเท่า SQL ของคำสั่งขั้นตอนเงื่อนไข IF..THEN..ELSE DECODE ทำงานกับค่า / คอลัมน์ / นิพจน์ของข้อมูลทุกประเภท

ไวยากรณ์:

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

ฟังก์ชัน DECODE จะเปรียบเทียบนิพจน์กับค่าการค้นหาแต่ละค่าตามลำดับ หากมีความเท่าเทียมกันระหว่างนิพจน์และอาร์กิวเมนต์การค้นหาจะส่งคืนผลลัพธ์ที่สอดคล้องกัน ในกรณีที่ไม่มีการจับคู่ค่าเริ่มต้นจะถูกส่งกลับหากกำหนดไว้มิฉะนั้นจะเป็นโมฆะ ในกรณีที่ความเข้ากันได้ของประเภทใด ๆ ไม่ตรงกัน oracle จะทำการแปลงโดยนัยที่เป็นไปได้ภายในเพื่อส่งกลับผลลัพธ์

ตามความเป็นจริง Oracle ถือว่าค่าว่างสองค่าเท่ากันในขณะที่ทำงานกับฟังก์ชัน DECODE

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

DECOD
-----
EQUAL

ถ้านิพจน์เป็นโมฆะ Oracle จะส่งคืนผลลัพธ์ของการค้นหาครั้งแรกที่เป็นโมฆะด้วย จำนวนส่วนประกอบสูงสุดในฟังก์ชัน DECODE คือ 255

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

นิพจน์กรณี

นิพจน์ CASE ทำงานบนแนวคิดเดียวกับ DECODE แต่แตกต่างกันที่ไวยากรณ์และการใช้งาน

ไวยากรณ์:

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

การค้นหา Oracle เริ่มจากซ้ายและเลื่อนไปทางขวาจนกว่าจะพบเงื่อนไขที่แท้จริงจากนั้นส่งคืนนิพจน์ผลลัพธ์ที่เกี่ยวข้อง หากไม่พบเงื่อนไขที่เป็นจริงและมีคำสั่ง ELSE อยู่ดังนั้น Oracle จะส่งคืนผลลัพธ์ที่กำหนดด้วย else มิฉะนั้น Oracle จะคืนค่า null

จำนวนอาร์กิวเมนต์สูงสุดในนิพจน์ CASE คือ 255 นิพจน์ทั้งหมดจะนับรวมในขีด จำกัด นี้รวมถึงนิพจน์เริ่มต้นของนิพจน์ CASE แบบง่ายและนิพจน์ ELSE ที่เป็นทางเลือก แต่ละคู่เมื่อ ... แล้วจะนับเป็นสองอาร์กิวเมนต์ เพื่อหลีกเลี่ยงการเกินขีด จำกัด นี้คุณสามารถซ้อนนิพจน์ CASE เพื่อให้ return_expr เป็นนิพจน์ 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