اوراکل 23c – بهبودی برای دستور CASE در PL/SQL

اوراکل در نسخه 23c قابلیت جدیدی را برای دستور CASE در محیط PL/SQL ارائه کرده است که در این متن با آن آشنا خواهیم شد. در ابتدا شکل ساده دستور CASE در PL/SQL را با یک مثال مرور می کنیم:

declare
  VALUE number := 3;
  text  varchar2(50);
begin
  text := case VALUE
            when 1 then
             'ONE'
            when 2 then
             'TWO'
            when 3 then
             'THREE'
            else
             'NOT ONE, TWO NOR THREE'
          end;
  dbms_output.put_line(text);
end;
/
THREE

در این قطعه کد صرفا از عملگر مساوی استفاده شده است و برای استفاده از عملگرهای دیگر نظیر “>”، “<“، BETWEEN و IN باید از فرمت زیر که Searched CASE syntax نامیده می شود، استفاده کنیم:

(بیشتر…)

اوراکل 23c – پشتیبانی توابع CEIL و FLOOR از دیتاتایپهای DATE، TIMESTAMP و INTERVAL

همانطور که می دانید توابع CEIL و FLOOR برای round کردن(گرد کردن) اعداد استفاده می شوند به این صورت که تابع CEIL اعداد را به سمت بالا گرد می کند و تابع FLOOR هم برای گردن کردن به سمت پایین استفاده می شود.

SQL> select floor(6.45) from dual;
FLOOR(6.45)
-----------
          6
SQL> select CEIL(6.45) from dual;
CEIL(6.45)
----------
         7

از نسخه 23c می توان از توابع CEIL و FLOOR برای انواع داده DATE، TIMESTAMP و INTERVAL هم استفاده کرد. برای مثال می توانیم برای تاریخ 2024/02/05 ماه را به سمت بالا گرد کنیم که در این صورت این تاریخ به 2024/03/01 رند خواهد شد.

(بیشتر…)

اوراکل 23c – بهبودهایی در زمینه DML RETURNING INTO

از نسخه های قدیمی اوراکل امکان استفاده از عبارت RETURNING INTO به همراه دستورات DMLای وجود داشت که در قسمت زیر نحوه استفاده از آن را می بینید:

SQL*Plus: Release 10.1.0.4.2 - Production on Tue Aug 15 14:08:30 2023
SQL> select * from tbl1;
        ID NAME
---------- ----------
         1 Vahid
         2 Usef
SQL>declare
    var_id number;
    var_name varchar2(10);
  begin
  --Update
    update tbl1 set name='ALI' where id=2 returning name into var_name;
   	dbms_output.put_line('After_Update==>' || name='||var_name);
  --Delete
    delete tbl1 where id=1 returning name into var_name;
    dbms_output.put_line('Before_Delete==>' || name='||var_name);
  --Insert	
    insert into tbl1 values(3,'Reza') returning id,name into var_id,var_name;
    	 dbms_output.put_line('INSERT==> id='||var_id||' , name='||var_name);
    commit;
  end;
/        
After_Update==>name=ALI
Before_Delete==> name=Vahid
INSERT==> id=3 , name=Reza
SQL> select * from tbl1;
        ID NAME
---------- ----------
         2 ALI
         3 Reza

(بیشتر…)

اوراکل 23c – قابلیت Table Values Constructor

Table Values Constructor قابلیتی است که در بیشتر دیتابیسهای رابطه ای وجود داشته و اوراکل امکان استفاده از این قابلیت را در نسخه 23c فراهم کرده است.

بر اساس این قابلیت، می توانیم با اجرای یک دستور insert ساده(Insert به همراه عبارت Values) چندین رکورد را در یک جدول درج کنیم البته استفاده از کلمه کلیدی Values به دستور insert محدود نمی شود و از این عبارت می توانیم برای دستورات DMLای دیگر نظیر Select و Merge هم استفاده کنیم.

ابتدا مثالی از نحوه استفاده از این قابلیت را به همراه دستور insert مشاهده می کنید.

SQL*Plus: Release 23.0.0.0.0 - Developer-Release on Tue Aug 29 22:41:17 2023
SQL> insert into Irani values(1,'Vahid'),(2,'Usef');
2 rows created.
SQL> commit;
Commit complete.

(بیشتر…)

اوراکل 23c – امکان استفاده از ماژولهای JavaScript در دیتابیس اوراکل

در نسخه 21c، اوراکل امکان اجرای کد JavaScript در داخل دیتابیس را فراهم کرده است. این کار با کمک بسته DBMS_MLE امکان پذیر است:

DECLARE
   ctx varchar2(50) ;
BEGIN
   ctx := DBMS_MLE.create_context();
   DBMS_MLE.eval(ctx, 'JAVASCRIPT', q'~console.log("www.usefzadeh.com");~');
   DBMS_MLE.drop_context(ctx);
END;
/
www.usefzadeh.com

در نسخه 23c قابلیتهای دیگری هم در این زمینه ارائه شد که یکی از آنها، امکان بارگذاری و استفاده از ماژولهای JavaScript در دیتابیس اوراکل است این قابلیت می تواند در زمینه های مختلفی راهگشا باشد و حجم کدنویسی را کاهش دهد.

به عنوان مثال، در مطلب “قابلیت SQL domain در اوراکل 23c” در مورد محدود کردن فرمت ورودی ها از طریق check constraint، trigger و sql domain نکاتی را ارائه کردیم و توضیح دادیم که چگونه می توانیم ورودی های ستونی مثل email را کنترل کنیم تا در قالبی مشخص درج شوند.

در این قبیل چالشها، ماژولهای JavaScript هم می توانند به کمک ما بیایند و با استفاده از آنها هم می توانیم بسیاری از این دست نیازمندیها را در دیتابیس مرتفع کنیم. برای مثال، ماژول validator در مورد کنترل ساختار ورودی ها می تواند مورد استفاده قرار بگیرد.

(بیشتر…)

اوراکل 23c- قابلیت DEFAULT ON NULL برای update و insert

از نسخه های قدیمی اوراکل این امکان را داشتیم که برای ستونهای جداول، مقدار پیش فرض و یا همان Default Value تعریف کنیم.

با تنظیم Default Value برای یک ستون، اگر در زمان اجرای دستور insert مقداری را برای آن ستون لحاظ نکرده باشیم، Default Value برای آن ستون اعمال خواهد شد:

SQL*Plus: Release 10.1.0.4.2 - Production on Sat Aug 5 16:28:29 2023
SQL> create table tbl1(id number,tarikh date default sysdate+1);
Table created.
SQL> insert into tbl1(id) values(1);
1 row created.
SQL> select * from tbl1;
        ID TARIKH
---------- ---------
         1 06-AUG-23

در این شرایط اگر به صراحت مقدار NULL را برای ستون tarikh در نظر بگیریم چه اتفاقی می افتد؟

SQL> insert into tbl1 values(2,null); 
1 row created.
SQL> select * from tbl1;
        ID TARIKH
---------- ---------
         1 06-AUG-23
         2

همانطور که می بینید، Default value در این حالت اعمال نشده است!

اوراکل در نسخه 12cR1 قابلیتی را اضافه کرده تا بتوان در این حالت هم مقدار Default Value به جای NULL برای ستون مورد نظر اعمال شود:

(بیشتر…)

اوراکل 23c – قابلیت Automatic SQL Transpiler

همانطور که می دانید استفاده از تابع در یک دستور sqlای، context switch بین SQL engine و PL/SQL engine را در پی خواهد داشت و رخ دادن متعدد context switch می تواند اثرات منفی بر روی performance دیتابیس داشته باشد.

اوراکل در نسخه 21c برای افزایش سرعت اجرای این دسته از پرس و جوها، SQL Macros را معرفی کرده است که با استفاده از این قابلیت، متن پرس و جوی حاوی function به فرم ساده و بدون استفاده از function بازنویسی می شود.

در نسخه 23c هم اوراکل قابلیت Automatic SQL Transpiler را در این زمینه معرفی کرده است که در صورت تنظیم پارامتر sql_transpiler به مقدار ON، به صورت خودکار و بدون مداخله کاربر، تابع استفاده شده در متن دستور SQL «در صورت امکان» به SQL expression تبدیل خواهد شد تا از سربار ناشی از اجرای function در SQL کاسته شود.

(بیشتر…)

اوراکل 23c – استفاده از Direct Join در دستورات Delete و Update

برای حذف و یا بروزرسانی رکوردهای یک جدول «در بسیاری از مواقع» نیاز به join آن جدول با جداول دیگر داریم تا تعیین کنیم کدام یک از رکوردها باید delete و یا update شوند تا قبل از اوراکل 23c در این شرایط به ناچار می بایست از دستور select هم همزمان استفاده می کردیم و امکان استفاده از Direct Join را نداشتیم اما در نسخه 23c، اوراکل این قابلیت را اضافه کرده است.

برای مثال دستور زیر با join بین دو جدول employees و departments مشخص می کند کدام رکوردهای جدول employees باید بروزرسانی شوند:

SQL> update employees e
  2     set e.salary = e.salary * 2
  3     from departments d
  4   where d.department_id = e.department_id
  5     and d.department_name = 'IT';
5 rows updated

از این قابلیت برای حذف رکوردها هم می توانیم استفاده کنیم:

SQL> delete employees e
  2     from departments d
  3   where d.department_id = e.department_id
  4     and d.department_name = 'IT'
  5     and e.employee_id!=d.manager_id;
4 rows deleted

 

امکان استفاده از عبارت if [not] exists در هنگام ایجاد و یا حذف یک object

در زمان ساخت یک object، اگر آن object از قبل موجود باشد، با خطای already exist مواجه خواهیم شد همچنین اگر قصد حذف کردن شی ای که موجود نیست را داشته باشیم، خطای does not exist رخ خواهد داد. برای جلوگیری از این دو خطا می توان از عبارت if [not] exists استفاده کرد.

برای مثال، جدول TB از قبل وجود دارد اگر از این مسئله مطمئن نیستیم، می توانیم دستور ساخت این جدول را با استفاده از عبارت if not exists اجرا کرده تا از خطای احتمالی ORA-00955 جلوگیری کنیم:

SQL> create table tb (c1 number(10));
ORA-00955: name is already used by an existing object
SQL> create table if not exists tb (c1 number(10));
Table created
SQL> create table if not exists tb (c1 number(10));
Table created

این عبارت برای دستورات DDLای دیگر هم کاربرد دارد:

SQL> drop sequence ss;
ORA-02289: sequence does not exist
SQL> drop sequence if exists ss;
Sequence dropped

(بیشتر…)

استفاده از Column Alias در قسمت Group by  و Having در اوراکل 23c

در نسخه 23c می توان از Column Alias در قسمت GROUP BY و HAVING استفاده کرد. این امکان تا قبل از نسخه 23c وجود نداشت:

Oracle Database 21c Enterprise Edition Release 21.0.0.0.0 - Production
SQL> select lower(owner) as Malek, sum(bytes / 1024 / 1024) as SIZE_MB
  2    from dba_segments
  3   group by Malek
  4  having SIZE_MB>100;
ERROR at line 4:
ORA-00904: "SIZE_MB": invalid identifier

در نسخه 21c برای جلوگیری از خطای invalid identifier فوق، می بایست Alias ستون را حذف و کوئری را به صورت زیر بازنویسی کرد:

select lower(owner) as Malek, sum(bytes / 1024 / 1024) as SIZE_MB
  from dba_segments
 group by owner
having sum(bytes / 1024 / 1024) > 10;

(بیشتر…)