การใช้นิพจน์เงื่อนไข

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

ฟังก์ชันทั่วไปใช้เพื่อจัดการกับค่า 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