توابع مرتبط با مقدار NULL

توابع مرتبط با مقدار NULL

دراین مبحث خلاصه‌ای از توابع مرتبط با بررسی مقادیر NULL آورده شده است.

پیش‌زمینه

جداول زیر در مثال‌های این مبحث مورد نیاز است.

DROP TABLE null_test_tab;

CREATE TABLE null_test_tab (

id NUMBER,

col1 VARCHAR2(10),

col2 VARCHAR2(10),

col3 VARCHAR2(10),

col4 VARCHAR2(10)

);

INSERT INTO null_test_tab values (1, ‘ONE’, ‘TWO’, ‘THREE’, ‘FOUR’);

INSERT INTO null_test_tab values (2, NULL, ‘TWO’, ‘THREE’, ‘FOUR’);

INSERT INTO null_test_tab values (3, NULL, NULL, ‘THREE’, ‘FOUR’);

INSERT INTO null_test_tab values (4, NULL, NULL, ‘THREE’, ‘THREE’);

COMMIT;

در صورت اجرای query نتیجه در جدول خروجی زیر مشاهده می‌نمایید.

SQL> SELECT * FROM null_test_tab ORDER BY id;

ID COL1 COL2 COL3 COL4

———- ———- ———- ———- ———-

۱ ONE TWO THREE FOUR

۲ TWO THREE FOUR

۳ THREE FOUR

۴ THREE THREE

۴ rows selected.

SQL>

دقت نمایید که نتیجه مقایسه‌گرها در برابر مقادیر NULL همیشه برابر NULL است، بنابراین query ها امکان استفاده از عملگرهایی مانند “=” یا “=!” را ندارند.

SQL> SELECT * FROM null_test_tab WHERE col1 = NULL ORDER BY id;

no rows selected

SQL>

در عوض باید از عملگرهای IS NULL یا IS NOT NULL استفاده شود.

SQL> SELECT * FROM null_test_tab WHERE col1 IS NULL ORDER BY id;

ID COL1 COL2 COL3 COL4

———- ———- ———- ———- ———-

۲ TWO THREE FOUR

۳ THREE FOUR

۴ THREE THREE

۳ rows selected.

SQL>

NVL
تابع NVL به شما این امکان را می‌دهد تا مقادیر NULL را با مقادیر پیش‌فرض جایگزین نمایید. در صورتی که مقدار متغیر اول برابر NULL باشد تابع مقدار متغیر دوم را بازمی‌گرداند.
اگر اولین پارامتر برابر هر مقداری غیر از NULL باشد بدون تعییر بازمی‌گردد.
می‌دانیم که COL1 در جدول تست به استثناء سطر اول حاوی مقدار NULL است. با استفاده از تابع NVL مقادیر حاوی NULL را با ZERO جایگزین می‌نماییم.

SQL> SELECT id, NVL(col1, ‘ZERO’) AS output FROM null_test_tab ORDER BY id;

ID OUTPUT

———- ———-

۱ ONE

۲ ZERO

۳ ZERO

۴ ZERO

۴ rows selected.

SQL>

DECODE
این تابع تنها برای بررسی مقادیر NULL نمی‎باشد، اما می‌تواند مشابه تابع NVL همانطور که در مثال پایین مشاهده می‌نمایید مورد استفاده قرار گیرد.
SQL> SELECT id, DECODE(col1, NULL, ‘ZERO’, col1) AS output FROM null_test_tab ORDER BY id;

ID OUTPUT

———- ———-

۱ ONE

۲ ZERO

۳ ZERO

۴ ZERO

۴ rows selected.

SQL>

NVL2
تابع NVL2 سه پارامتر می‌پذیرد. اگر پارامتر اول برابر NULL نباشد مقدار پارامتر دوم را برمی‌گرداند. اما در صورتی که پارامتر اول NULL باشد مقدار پارامتر سوم بازگردانده می‌شود.
Query زیر تابع NVL2 را در عمل نشان می‌دهد.

SQL> SELECT id, NVL2(col1, col2, col3) AS output FROM null_test_tab ORDER BY id;

ID OUTPUT

———- ———-

۱ TWO

۲ THREE

۳ THREE

۴ THREE

۴ rows selected.

SQL>

در جدول تست COL1 دارای مقدار NULL نمی‌باشد پس مقدار COL2 باز گردانده می‌شود، همچنین تمامی سطرهای بعدی در COL1 حاوی مقدار NULL هستند پس مقدار COL3 باز می‌گردد.

COALESCE
تابع COALESCE در اراکل نسخه ۹i معرفی گردید. این تابع دو یا تعداد بیشتری پارامتر را گرفته و اولین مقدار غیر NULL در یک لیست را باز می‌گرداند. اگر تمام پارامترها غیر NULL بودند مقدار NULL بازگردانده می‌شود.

SQL> SELECT id, COALESCE(col1, col2, col3) AS output FROM null_test_tab ORDER BY id;

ID OUTPUT

———- ———-

۱ ONE

۲ TWO

۳ THREE

۴ THREE

۴ rows selected.

SQL>

NULLIF
تابع NULLIF در اراکل نسخه ۹i معرفی گردید. این تابع دو پارامتر می‌پذیرد و مقدار NULL را در صورتی که دو پارامتر برابر باشند باز می‌گرداند. در حالتی که آن‌ها NULL نباشند مقدار پارامتر اول بازگردانده می‌شود.
در جدول تست ما مقدار COL3 و COL4 در سطر ۴ برابر است پس تنها مقدار NULL قابل انتظار در این سطر در query زیر می‌باشد.

SQL> SELECT id, NULLIF(col3, col4) AS output FROM null_test_tab ORDER BY id;

ID OUTPUT

———- ———-

۱ THREE

۲ THREE

۳ THREE

۴

۴ rows selected.

SQL>

LNNVL
تابع LNNVL از نسخه ۹i اراکل وجود دارد اما تا نسخه ۱۱g در اسناد اراکل وارد نشده است و به جهت ارزیابی حالت خاص مورد نظر در یک عبارت WHERE مورد استفاده قرار می‌گیرد. در صورتی که ارزیابی شرایط FALSE و یا غیر مشخص باشد مقدار TRUE بازگردانده می‌شود و در صورتی که این ارزیابی از شرایط TRUE باشد مقدار FALSE باز می‌گردد.

SQL> SELECT id, col3 FROM null_test_tab WHERE LNNVL(col1 IS NULL) ORDER BY id;

ID COL3

———- ———-

۱ THREE

۱ row selected.

SQL> SELECT id, col3 FROM null_test_tab WHERE LNNVL(col2 = ‘TWO’) ORDER BY id;

ID COL3

———- ———-

۳ THREE

۴ THREE

۲ rows selected.

SQL> SELECT id, col3 FROM null_test_tab WHERE LNNVL(col2 != ‘TWO’) ORDER BY id;

ID COL3

———- ———-

۱ THREE

۲ THREE

۳ THREE

۴ THREE

۴ rows selected.

SQL>

NANVL
تابع NANVL در نسخه ۱۰g اراکل برای استفاده به همراه datatype های BINARY_FLOAT و BINARY_DOUBLE معرفی شد که می‌تواند شامل یک مقدار خاص “Not a Number” یا “NaN” باشد. این تابع مشابه تابع NVL است اما به جای تست NULL این تابع تست مقدار NaN را انجام می‌دهد.
در جدول زیر مشاهده می‌نمایید:

DROP TABLE nanvl_test_tab;

CREATE TABLE nanvl_test_tab (

id NUMBER,

col1 BINARY_DOUBLE

);

INSERT INTO nanvl_test_tab VALUES (1, 1234.5678);

INSERT INTO nanvl_test_tab VALUES (2, ‘INF’);

INSERT INTO nanvl_test_tab VALUES (3, ‘-INF’);

INSERT INTO nanvl_test_tab VALUES (4, ‘NaN’);

COMMIT;

در صورت اجرای query داده‌های زیر مشاهده می‌شود.

SELECT * FROM nanvl_test_tab ORDER BY id;

ID COL1

———- ———-

۱ ۱٫۲۳۵E+003

۲ Inf

۳ -Inf

۴ Nan

۴ rows selected.

SQL>

در ادامه بار دیگر از داده‌ها query می‌گیریم ولی با تبدیل تمام مقادیر NaN به “۰” توسط تابع NANVL.

SELECT id, col1, NANVL(col1, 0) AS output FROM nanvl_test_tab;

ID COL1 OUTPUT

———- ———- ———-

۱ ۱٫۲۳۵E+003 1.235E+003

۲ Inf Inf

۳ -Inf -Inf

۴ Nan 0

۴ rows selected.

SQL>

SYS_OP_MAP_NONNULL
همانطور که مشاهده نمودید مقایسه عبارت NULL=NULL همواره مقدار FALSE را بر می‌گرداند. ولی گاهاً شما انتظار بازگشت مقدار TRUE را دارید. این امر با توجه به نحوه استفاده شما از آن‌ها وابسته به تبدیل مقدار NULL به مقدار دیگری که شما احتمال می‌دهید مشابه آن در ستون وجود ندارد است و به وسیله توابع NVL و DECODE امکان‌پذیر است.

SELECT id, ‘col1=col2’

FROM null_test_tab

WHERE NVL(col1, ‘!null!’) = NVL(col2, ‘!null!’);

ID ‘COL1=COL

———- ———

۳ col1=col2

۴ col1=col2

۲ rows selected.

SQL>

SELECT id, ‘col1=col2’

FROM null_test_tab

WHERE DECODE(col1, NULL, ‘!null!’, col1) = DECODE(col2, NULL, ‘!null!’, col2);

ID ‘COL1=COL

———- ———

۳ col1=col2

۴ col1=col2

۲ rows selected.

SQL>

SELECT id, ‘col1=col2’

FROM null_test_tab

WHERE DECODE(col1, col2, ‘!match!’, col1) = ‘!match!’;

ID ‘COL1=COL

———- ———

۳ col1=col2

۴ col1=col2

۲ rows selected.

SQL>

به عنوان جایگزین می‌توان از تابع غیر رسمی اراکل Undocumented)، SYS_OP_MAP_NONNULL) به منظور تطبیق عبارات حاوی مقدار NULL بهره گرفت.

SELECT id, ‘col1=col2’

FROM null_test_tab

WHERE SYS_OP_MAP_NONNULL(col1) = SYS_OP_MAP_NONNULL(col2);

ID ‘COL1=COL

———- ———

۳ col1=col2

۴ col1=col2

۲ rows selected.

SQL>

به این نکته توجه نمایید که تابع فوق Undocumented است و اصولاً نباید در فرآیند تولید Application مورد استفاده قرار گیرد.