بررسی کامل Boolean Type در Oracle 23c

با وجود تمام قابلیت‌های جدید و عالی که در نسخه پایگاه داده Oracle 23c معرفی شده‌اند، گاهی اوقات این چیزهای ساده و کوچک هستند که بیشترین سر و صدا را ایجاد می‌کنند.معرفی انواع داده های Boolean در SQL ممکن است یکی از بزرگترین ها باشد.برای سال‌ها در نسخه‌های قبلی، استدلال علیه نوع SQL Boolean این بود که به آن نیازی نبود. و صادقانه بگویم، این یک ضرورت فنی نبود. توسعه دهندگان در سراسر جهان، از Flag های مختلفی از انواع داده های دیگر مانند Y/N، ۱/۰، یا T/F استفاده کردند و برنامه های ما کار کردند.با این حال، هر کسی که با زبان های برنامه نویسی دیگر آشنا باشد، اغلب این کد را ناخوشایند و مصنوعی می داند که چیزی که به سادگی درست یا نادرست است باید با متن یا انواع عددی به جای بولین بومی ارزیابی شود. Oracle 23cاز استاندارد SQL پیروی می کند و Booleans را به زبان Oracle SQL می آورد.مستند آموزش نصب پایگاه داده Oracle 23ستون های بولیبرای شروع، ما یک جدول ایجاد می کنیم که دو Boolean را اعلام می کند، که یکی از آنها NOT NULL است.Oracle Database 23c Free, Release 23.0.0.0.0 – Developer-ReleaseVersion 23.2.0.0.0SQL> create table testtable(b boolean, bnn boolean not null);Table created.SQL> insert into testtable(b,bnn) values (true,true);۱ row created.SQL> insert into testtable(b,bnn) values (false,false);۱ row created.SQL> insert into testtable(b,bnn) values (null,true);۱ row created.SQL> insert into testtable(b,bnn) values (null,false);۱ row created.SQL> insert into testtable(b,bnn) values (null,null);insert into testtable(b,bnn) values (null,null)*ERROR at line 1:ORA-01400: cannot insert NULL into (“SDS”.”TESTTABLE”.”BNN”)ستون ها همانطور که ما انتظار داریم رفتار می کنند. ۴ ردیف پر شد و درج پنجم خطایی ایجاد کرد زیرا ما سعی کردیم یک NULL را در ستونی قرار دهیم که به آنها اجازه نمی دهد.SQL> select * from testtable;B BNN———– ———–TRUE TRUEFALSE FALSETRUEFALSE۴ rows selected.توجه داشته باشید، حروف بولی TRUE و FALSE به حروف بزرگ و کوچک حساس نیستند. در مثال های بالا از حروف کوچک استفاده کردم، اما حروف بزرگ و مختلط به یک اندازه معتبر هستند.SQL> insert into testtable(b,bnn) values (TRUE,True);۱ row created.SQL> insert into testtable(b,bnn) values (FALSE,False);۱ row created.SQL> insert into testtable(b,bnn) values (TRUE,TruE);۱ row created.SQL> insert into testtable(b,bnn) values (FALSE,FaLSe);۱ row created.SQL> select * from testtable;B BNN———– ———–TRUE TRUEFALSE FALSETRUEFALSETRUE TRUEFALSE FALSETRUE TRUEFALSE FALSE۸ rows selected.تبدیل نوع داده۲۳c از بسیاری از literals های رایج برای تبدیل ضمنی به مقادیر بولی پشتیبانی می کند.‘true’، ‘false’، ‘yes’، ‘no’، ‘on’، ‘off’، ‘۱’، ‘۰’، ‘t’، ‘f’، ‘y’، ‘n’ قابل تبدیل هستند به مقادیر Boolean True/False. تمام حروف تحت literals متن به حروف بزرگ و کوچک حساس نیستند و بر اساس کلمات انگلیسی هستند. هیچ معادلی برای زبان های دیگر مانند فرانسوی ‘oui’، ‘non’ وجود ندارد.همچنین توجه داشته باشید، مقادیر عددی نیز ممکن است به طور ضمنی به مقادیر بولی تبدیل شوند. ۰ تبدیل به FALSE و مقادیر غیر صفر True است.SQL> truncate table testtable;Table truncated.SQL> insert into testtable(b,bnn) values (‘true’,’False’);۱ row created.SQL> insert into testtable(b,bnn) values (‘YES’,’NO’);۱ row created.SQL> insert into testtable(b,bnn) values (‘on’,’off’);۱ row created.SQL> insert into testtable(b,bnn) values (‘1′,’0’);۱ row created.SQL> insert into testtable(b,bnn) values (‘t’,’f’);۱ row created.SQL> insert into testtable(b,bnn) values (‘y’,’n’);۱ row created.SQL> insert into testtable(b,bnn) values (12345,0);۱ row created.SQL> insert into testtable(b,bnn) values (-0.234,-0.0000);۱ row created.SQL> select * from testtable;B BNN———– ———–TRUE FALSETRUE FALSETRUE FALSETRUE FALSETRUE FALSETRUE FALSETRUE FALSETRUE FALSEبه جای استفاده از تبدیل ضمنی، می‌توانید با استفاده از تابع TO_BOOLEAN با literals‌ها صریح باشید.SQL> truncate table testtable;Table truncated.SQL> insert into testtable(b,bnn) values (to_boolean(‘true’),to_boolean(‘False’));۱ row created.SQL> insert into testtable(b,bnn) values (to_boolean(‘YES’),to_boolean(‘NO’));۱ row created.SQL> insert into testtable(b,bnn) values (to_boolean(‘on’),to_boolean(‘off’));۱ row creeated.SQL> insert into testtable(b,bnn) values (to_boolean(-0.234),to_boolean(-0.0ated.SQL> insert into testtable(b,bnn) values (to_boolean(‘1’),to_boolean(‘0’));۱ row created.SQL> insert into testtable(b,bnn) values (to_boolean(‘t’),to_boolean(‘f’));۱ row created.SQL> insert into testtable(b,bnn) values (to_boolean(‘y’),to_boolean(‘n’));۱ row created.SQL> insert into testtable(b,bnn) values (to_boolean(12345),to_boolean(0));۱ row cr000));۱ row created.SQL> select * from testtable;B BNN———– ———–TRUE FALSETRUE FALSETRUE FALSETRUE FALSETRUE FALSETRUE FALSETRUE FALSETRUE FALSEتابع TO_BOOLEAN فقط از literals های بالا پشتیبانی می کند. اگر بخواهید یک مقدار غیرقانونی را تبدیل کنید، یک خطای بولی نامعتبر دریافت خواهید کرد.SQL> insert into testtable(b,bnn) values (to_boolean(‘oui’),to_boolean(‘non’));insert into testtable(b,bnn) values (to_boolean(‘oui’),to_boolean(‘non’))*ERROR at line 1:ORA-61800: invalid boolean literal: ouiمنطق بولیمنطق بولی همیشه در عبارات Where یک پرس و جو پشتیبانی می شود و این به نوع داده جدید گسترش می یابد. علاوه بر این، نتایج منطقی را می توان به عنوان یک عبارت SQL نیز برگرداند.SQL> select * from testtable where b and bnn;B BNN———– ———–TRUE TRUETRUE TRUETRUE TRUESQL> select * from testtable where b or bnn;B BNN———– ———–TRUE TRUETRUETRUE TRUETRUE TRUESQL> select b, bnn, b and bnn, b or bnn from testtable;B BNN BANDBNN BORBNN———– ———– ———– ———–TRUE TRUE TRUE TRUEFALSE FALSE FALSE FALSETRUE TRUEFALSE FALSETRUE TRUE TRUE TRUEFALSE FALSE FALSE FALSETRUE TRUE TRUE TRUEFALSE FALSE FALSE FALSEدر این مرحله مهم است که توجه داشته باشید که چگونه مقادیر NULL با عملگرها ارزیابی می شوند. همانطور که در بالا می بینیم، (NULL و TRUE) NULL را برمی گرداند، اما (NULL و FALSE) FALSE را برمی گرداند. این ممکن است در مقایسه با عملگرهای NULL که با انواع داده های دیگر استفاده می شود، عجیب به نظر برسد. به عنوان مثال، افزودن، ضرب، تفریق، تقسیم مقادیر NULL با مقدار غیر NULL بدون در نظر گرفتن مقدار غیر NULL منجر به NULL می شود. اما با یک Boolean، عبارت FALSE باقی می ماند . به طور مشابه، (NULL یا TRUE) به TRUE تبدیل می شود، نه NULL. بنابراین، باز هم، انتشار تهی در مقایسه با اپراتورهایی با انواع داده های دیگر، متفاوت از آنچه انتظار می رود عمل می کند.متأسفانه در ارزیابی ها خلأهایی وجود دارد. در حالی که می‌توانید دو ستون بولی را با AND یا OR همانطور که در بالا نشان داده شده است، یا دو حرف بولی یا یک ستون با یک حرف literalsی ارزیابی کنید، نمی‌توانید از یک literals تبدیل شده به طور ضمنی یا صریح در یک عبارت بولی AND/OR استفاده کنید. حتی تلاش برای قرار دادن عبارت در پرانتز برای اطمینان از ارزیابی آن قبل از سایر عبارات یا شرایط کار نمی کند.SQL> truncate table testtable;Table truncated.SQL> insert into testtable(b,bnn) values (true,true);۱ row created.SQL> insert into testtable(b,bnn) values (false,false);۱ row created.SQL> insert into testtable(b,bnn) values (null,true);۱ row created.SQL> select b and true from testtable;BANDTRUE———–TRUEFALSESQL> select b or true from testtable;BORTRUE———–TRUETRUETRUESQL> select true and false from dual;TRUEANDFALS———–FALSEاین عبارات با literals های تبدیل شده، ضمنی یا صریح شکست می خورند.SQL> select true and ‘true’ from dual; — expression on implicit conversion failsselect true and ‘true’ from dual*ERROR at line 1:ORA-00920: invalid relational operatorSQL> select true and to_boolean(‘true’) from dual; — expression on explicit conversion also failsselect true and to_boolean(‘true’) from dual*ERROR at line 1:ORA-00920: invalid relational operatorSQL> select (true and to_boolean(‘true’)) from dual; — enclosing expression still failsselect (true and to_boolean(‘true’)) from dual*ERROR at line 1:ORA-00907: missing right parenthesisاین محدودیت برای توابع pl/sql خود شما اعمال می شود که نتایج Boolean را برمی گرداند.SQL> create or replace function are_equal(a in number, b in number) return boolean۲ is۳ begin۴ return a=b;۵ end;۶ /Function created.SQL> select are_equal(1,2) f ,are_equal(1,1) t from dual;F T———– ———–FALSE TRUESQL> select b and are_equal(1,1) from testtable;select b and are_equal(1,1) from testtable*ERROR at line 1:ORA-00920: invalid relational operatorارزیابی بولیدر حالی که در بسیاری از موارد می توانید به سادگی از یک مقدار بولی به عنوان شرط خود استفاده کنید، می توانید صریح باشید و با شرط IS بررسی کنید که آیا یک مقدار درست یا نادرست است. بنابراین اگر B TRUE باشد، “B” و “B IS TRUE” باید عباراتی معادل باشند. به طور مشابه، اگر B FALSE باشد، “B” و “B IS TRUE” باید عبارات هم ارز باشند.SQL> truncate table testtable;Table truncated.SQL> insert into testtable(b,bnn) values (true,true);۱ row created.SQL> insert into testtable(b,bnn) values (false,false);۱ row created.SQL> insert into testtable(b,bnn) values (null,true);۱ row created.SQL> select b from testtable where b;B———–TRUESQL> select b from testtable where b is true;B———–TRUESQL> select b from testtable where not b;B———–FALSESQL> select b from testtable where b is false;B———–FALSEالبته توجه داشته باشید، IS NOT، با مقادیر NULL متفاوت کار می کند.SQL> select b, nvl2(b,to_char(b),’~null~’) b_text from testtable where b is not true;B B_TEXT———– ——FALSE FALSE~null~SQL> select b, nvl2(b,to_char(b),’~null~’) b_text from testtable where b is not false;B B_TEXT———– ——TRUE TRUE~null~این عملگرها با توابع PL/SQL و همچنین تبدیل صریح با استفاده از TO_BOOLEAN کار می کنند.SQL> SELECT are_equal(1, 1) is true,۲ to_boolean(‘yes’) is true,۳ are_equal(1, 2) is true,۴ to_boolean(‘no’) is true۵ FROM DUAL;ARE_EQUAL(1 TO_BOOLEAN( ARE_EQUAL(1 TO_BOOLEAN(———– ———– ———– ———–TRUE TRUE FALSE FALSEآنها مستقیماً با literals کار نمی کنند، زیرا به طور ضمنی تبدیل نمی شوند.SQL> select ‘yes’ is true from dual;select ‘yes’ is true from dual*ERROR at line 1:ORA-61803: argument of IS TRUE must be a column or an expression of boolean data typeامیدوارم این به شما کمک کند تا با کار با مقادیر Boolean در SQL با Oracle 23cو فراتر از آن آشنا شویدجهت خدمات مشاوره، آموزش و نگهداری پایگاه داده اوراکل با ما در تماس باشید.